MySQL is the world’s most popular open source database, recognized for its speed and reliability, and is frequently used with PHP to create websites. The Electric Toolbox website uses MySQL as its database backend, and I have personally been using MySQL since 1999. In that time it has proven itself to me to be an extremely fast and reliable database management system, although the earlier versions lacked some features found in other database systems, such as stored procedures, triggers, sub selects and joined table updates. Most of these are available from the 4.1 and 5.0 releases.
If you are looking for MySQL databases – for example geographical databases, I suggest you take a look at SQL Dumpster
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 …
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.
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 …
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.
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.
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 …
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.