ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘db’
No, you should run mysql -u root -p in bash, not at the MySQL command-line. If you are in mysql, you can exit by typing exit.
MySQL
How do I find which transaction is causing a “Waiting for table metadata lock” state?
Works for MySql version < 5.7.3 Look for the Section – We can use INFORMATION_SCHEMA Tables. Useful Queries To check about all the locks transactions are waiting for: A list of blocking transactions: OR A List of locks on particular table: A list of transactions waiting for locks: Reference – MySQL Troubleshooting: What To Do When Queries Don’t Work, … Read more
MySQL date format DD/MM/YYYY select query?
You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result: However, you would be wise to convert the column to the DATE data type instead of using strings.
MySQL IF NOT NULL, then display 1, else display 0
Instead of COALESCE(a.addressid,0) AS addressexists, use CASE: or the simpler: This works because TRUE is displayed as 1 in MySQL and FALSE as 0.
Convert MySQL to SQlite [closed]
Is it possible to convert from MySQL to SQLite with a free tool on windows?
Trying to perform MINUS operation in MySQL
MySql does not recognise MINUS and INTERSECT, these are Oracle based operations. In MySql a user can use NOT IN as MINUS (other solutions are also there, but I liked it lot). Example:
Unknown Column In Where Clause
SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name has not yet occurred.
SQL select only rows with max value on a column
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that … Read more
MySQL > Table doesn’t exist. But it does (or it should)
Just in case anyone still cares: I had the same issue after copying a database directory directly using command If you do this with a database that uses InnoDB tables, you will get this crazy ‘table does not exist’ error mentioned above. The issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1). … Read more
Generating a random & unique 8 character string using MySQL
This problem consists of two very different sub-problems: the string must be seemingly random the string must be unique While randomness is quite easily achieved, the uniqueness without a retry loop is not. This brings us to concentrate on the uniqueness first. Non-random uniqueness can trivially be achieved with AUTO_INCREMENT. So using a uniqueness-preserving, pseudo-random … Read more