Check if a MySQL table exists

MySQL has a couple of ways (that I know of, there may be more) of working out if a table exists. This post looks at how to check if a table exists in the MySQL database. Using show tables The first way is using the “show tables” function. If your database (called “test” in this …

Read more

How to default a column as a UUID/GUID in MySQL

MySQL does not yet support setting a column’s default value using a function (at least not yet as of version 5.6) but you can use a trigger instead. This post shows how to set a column to a UUID/GUID by default in MySQL using a trigger.

MySQL “Incorrect key file for table /tmp/#sql_xxx_x.MYI” error

I’ve written a post in the past about dealing with the “Incorrect key file for table” in MySQL for regular database tables, but there are instances when you might get the error for a temporary table and see something like this: “Incorrect key file for table ‘/tmp/#sql_xxx_x.MYI’; try to repair it” It’s a temporary table, so …

Read more

How to restore the debian-sys-maint MySQL user

My last post looked at how to fix ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ on MySQL and this article shows how to restore the debian-sys-maint user if you’ve accidentally deleted it. I accidentally deleted the user myself when I reset a MySQL database using the /usr/bin/mysql_install_db script.

The SQL Query

Log into MySQL as the root user and run the following SQL query, substituting PASSWORD-HERE for the actual plain text password which is the same as the password in the /etc/mysql/debian.conf file:

INSERT INTO `user` (
 `Host`,
 `User`,
 `Password`,
 `Select_priv`,
 `Insert_priv`,
 `Update_priv`,
 `Delete_priv`,
 `Create_priv`,
 `Drop_priv`,
 `Reload_priv`,
 `Shutdown_priv`,
 `Process_priv`,
 `File_priv`,
 `Grant_priv`,
 `References_priv`,
 `Index_priv`,
 `Alter_priv`,
 `Show_db_priv`,
 `Super_priv`,
 `Create_tmp_table_priv`,
 `Lock_tables_priv`,
 `Execute_priv`,
 `Repl_slave_priv`,
 `Repl_client_priv`,
 `Create_view_priv`,
 `Show_view_priv`,
 `Create_routine_priv`,
 `Alter_routine_priv`,
 `Create_user_priv`,
 `ssl_type`,
 `ssl_cipher`,
 `x509_issuer`,
 `x509_subject`,
 `max_questions`,
 `max_updates`,
 `max_connections`,
 `max_user_connections`
 )
 VALUES (
 'localhost',
 'debian-sys-maint',
 password('PASSWORD-HERE'),
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'N',
 'N',
 'N',
 'N',
 'N',
 '',
 '',
 '',
 '',
 0,
 0,
 0,
 0
 );
 FLUSH PRIVILEGES;

 And that’s all there is to it. 

MySQL SQL_SELECT_LIMIT

I came across a MySQL setting recently called SQL_SELECT_LIMIT which limits the maximum number of rows returned from a SQL query without having to specify the limit in the query. It applies the limit to all queries from the current connection until reset or changed to a different value. Use of SQL_SELECT_LIMIT I’m not exactly …

Read more

MySQL table is marked as crashed and should be repaired

How do you fix the error when running a query on a MySQL table and get the following error: "ERROR 145 (HY000) at line 1: Table ‘<tablename>’ is marked as crashed and should be repaired". Very easily, but it may take some time depending how big the table is.