Help and howto do things in MySQL
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
Post sort order: Post Date (Newest First) | Post Date (Oldest First) | Alphabetical | Date Updated
I covered how to use PHP's fputcsv without writing to a file in yesterday's post and look at a practical example today: fetching data from MySQL using PDO and sending it to the browser as a CSV, using the fputcsv() function.
I recently looked at how to default a column as a UUID/GUID in MySQL using a trigger, but it's not replication safe when using statement based replication. This post looks at an alternative to using triggers by using a variable instead.
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.
When using the mysqldump command to make a back of the mysql database, you might get the message "Warning: Skipping the data of table mysql.event. Specify the --events option explicitly". This post shows how to suppress this message.
When attempting to load a database dumped with mysqldump into an older MySQL database, I got the error message ERROR 1115 (42000) : Unknown character set: 'utf8mb4' in mysql.
If you order a string column that contains only numeric values with MySQL, it will order them as string values, e.g.: 1, 10, 100, 2, 3, 31, 32, 4, etc. If you want to order them as if they were an integer, cast them in the order by clause.
After some maintenance was done to the virtual host server that one of my virtual servers was running on that required a host reboot, my websites weren't running due to the MySQL error "[ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)"
This post shows how to extract the local part and domain name from an email address using MySQL. The local part is the part before the @, for example "chris" in firstname.lastname@example.org.
The events table was introduced to MySQL in 5.1.6 and when upgrading you will start to see the warning "Warning: Skipping the data of table mysql.event" when using mysqldump. This is not an error as such, but more to make it clear to you that something has changed.
MySQL's main configuration file is my.cnf and it is located in different locations depending on the operating system, distribution and version. This post shows how to find out where MySQL will look for the config file.
A few days ago, I posted how to temporarily change the auto increment increment in MySQL. This post has an extension class to PHP's PDO to get the current auto increment value, change it to a new value and then reset it back to the original.
It's possible to temporarily change MySQL's auto increment increment value to something other than the system setting. This might be useful if (like me) you have two MySQL databases set up in a master-master relationship with an increment of 2, but need to increment a table in 1s.
I use phpMyAdmin as a PHP web based manager for MySQL, but find a few of the default configuration options "out of the box" a little annoying. This post shows some useful modifications to the defaults to make phpMyAdmin more usable, in my opinion.
The MySQL query cache enables caching of queries which, in theory, should speed querying the database up. It's possible to disable the MySQL query cache without restarting the server, although you also need to edit the configuration file to make the changes stick on restart.
I mistyped the name of an internal MySQL function in some code this morning and got back the error message "Cannot load from mysql.proc. The table is probably corrupted" which appeared to have no bearing on the actual error. The simple fix to this problem is to run the mysql_upgrade command.
If you have MySQL replication running in either a master-slave or master-master type setup then don't assume everything is running perfectly all the time. Power cuts and other disasters do happen so you need to check periodically to ensure it's all working. This post has a PHP script to check for any MySQL replication errors and then emails if there are any issues
If you've moved a MySQL installation from one Debian server to another and/or replaced the user logins on your system, you might get the error message "ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)" when attempting to restart MySQL.
This post shows how to get a complete list of stored procedures in a MySQL database and then to see what code is used in the stored procedure.
There are a few posts on this blog about the Facebox jQuery plugin, and as I discovered this morning the website URL for the plugin has changed so I needed to update all links to it. The quickest way to do this was to update the MySQL database directly using UPDATE and REPLACE to find the old URLs and replace them with the new URLs. So here's how to find and replace text in a MySQL database.
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.
It is possible to fork a process with PHP and have one or more child processes running. If a connection has already been established to a MySQL database and the child processes run queries it's quite likely a "MySQL server has gone away" or a "Lost connection to MySQL server during query" type error will occur.
MySQL supports multiple storage engines which each have their pros and cons; the two most commonly used are MyISAM and INNODB. It's easy to convert a table from one storage engine to another, and this post has a PHP script which convert all tables in a MySQL database from one engine to another.
I was trying to export the structure of a MySQL database using phpMyAdmin but it kept timing out and not showing me the full create script. I think phpMyAdmin uses the information schema to get this information and its on a host with hundreds of databases and tables so querying the information schema runs very slowly. Instead I knocked together a quick PHP script to dump the structure instead and share it here.
MySQL databases can be replicated using master-slave replication and also with master-master replication. There are plenty of tutorials online showing how to do master-slave but not many showing master-master replication and the most popular one I found was a little hard to follow. Therefore I've written this post to show how to do master-master replication with MySQL.
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.
There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.
I recently showed how to load data into MySQL with foreign key constraint issues when loading a backup from tab delimited text files. This post provides a BASH command line script to generate the SQL to load all the files to save some time.
It's possible to order data randomly with MySQL using ORDER BY RAND() or doing a count on the table and then using LIMIT to choose a random offset. However neither of these are suitable for dealing with large tables with many rows. In this post I present an alternative to these but it does require modifications to the table and additional business logic and is still not perfect.
This post shows how to populate a select box based on the value of the another, by getting JSON data with jQuery from a PHP script that gets the data from a MySQL database.
This post shows how to fetch data from a MySQL database using PHP's PDO library with bound placeholders. This is a fairly basic tutorial and the functions used in it will be used in a post tomorrow titled "Load JSON data with jQuery, PHP and MySQL".
A couple of weeks ago I posted an alternative to ORDER BY RAND() when using MySQL which uses MySQL's LIMIT syntax to jump to a record chosen at random based on the number of records in the table. I received a Tweet earlier this week which pointed out that LIMIT is slow when dealing with large offsets so take a look at this here.
In my MySQL posts I've tended to use fruit in my example tables, so in this post will standardize the table structure and data that appears in future examples. Those future posts will refer back to this post for the table structure and base data.
I've posted previously about how to randomly order a resultset with MySQL using RAND() but the issue with RAND() is it will be inefficient on large tables because each row needs to have the random number calculated before the resultset can be ordered. This post looks at an alternative which requires two queries but will be much more efficient for large tables.
One of the MySQL databases I work with has a couple of hundred INNODB tables set up with foreign key constraints. I needed to copy the database using mysqldump to export data from one server and then load that into another but kept getting foreign key constraint errors. This post looks at how to load data from a file with MySQL and ignore foreign key constraints.
An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents errors from occuring and other queries are still able to be run.
Dates and times can be stored as an integer value as a UNIX timestamp. This isn't a particularly human readable format but can be converted in MySQL to a datetime value using the FROM_UNIXTIME function.
I recently needed to use an IF statment in a WHERE clause with MySQL. This isn't the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.
This post is a summary of MySQL posts from this blog relating to MySQL utility commands, such as describing table structure, dropping columns from a table, and so on; and server settings.
A few months ago I posted about string concatenation with MySQL using the CONCAT function and in this follow up post look at the CONCAT_WS function which glues the fields together with the specified string. CONCAT_WS means CONCATenate With Separator i.e. join the fields together with a specified separator string.
By assigning a value to a variable in MySQL and incrementing the variable as part of a select statement, it's possible to have anumber auto-incremented on the fly. This number has no other reference to the table other than the order the data is selected . It is also possible to auto increment the variable when running an update statement so a new set of incrementing values could be stored into the table.
The MySQL Query Browser is a GUI tool for running queries on MySQL database servers. This post looks at an issue where the query browser reports an issue with memory load being extremely high and prevents the query from running. There are versions of the MySQL Query Browser for Windows, OS/X and Linux (or you can compile from source); I got this error on the Windows version and it may or may not affect the other platforms.
It's probably more common for people to edit MySQL tables using a more visual tool such as phpMyAdmin but it is possible to use a manually written SQL query instead to modify tables (this is what a tool like phpMyAdmin does in the background). This post shows how to drop a column from a MySQL table with a SQL query.
The MySQL command line tool mysqldump is used to create backup copies (or dumps) of databases including the structure or schema and the data itself. There are a number of command line flags which can get MySQL to dump just the data or just the structure instead of everything. This post looks at how to dump just the schema of the MySQL database as a whole, for a single table, or for several tables.
This post shows how to get some MySQL status information from the command line on a Linux/Unix machine using either the mysqladmin command or the mysql init script. Information returned includes the version, connection type, socket file location, uptime and some other stats.
MySQL's CONVERT_TZ function converts datetime values from one timezone to another. If the timezones haven't been set up in the MySQL database then the value returned from the function will be null. This post shows how to fix the problem.
Last week I looked at how to drop a table with MySQL showing how to do this with a query and then using the phpMyAdmin web based interface. This time I will show how to drop multiple tables with a single query and then how to do the same with phpMyAdmin.
I've previously posted how to get the indexes for a MySQL table using a "SHOW INDEXES" SQL query and in this post show an alternative way to get the indexes for a table using MySQL's INFORMATION_SCHEMA.
The MySQL Query Cache enables query results to be cached so if the same query is run multiple times the second and subsequent times the query are run are amost instant. This short post shows how to tell if the MySQL Query Cache is enabled.
There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE (which I have already covered in an earlier post) and the second by querying the INFORMATION_SCHEMA. This post deals with querying the INFORMATION_SCHEMA which has more information available than using DESCRIBE.
There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE and the second by querying the INFORMATION_SCHEMA. This post deals with the DESCRIBE function and next week I'll look at the latter.
MySQL has a slow query log which can be enabled if you need to track down slow queries which are causing issues for a website or application.
It's possible to execute statements in MySQL from a text file from the command line or from the MySQL command line shell. This can be useful, for example, if you've saved data using mysqldump and need to load it back into the database.
The MySQL full text index by default only indexes words which are 4 characters or longer, which means on a blog like mine if you search for "PHP" nothing will be returned. This post shows how to change minimum word length in MySQL so words or 3 characters (or even 2 if you want) can be indexed as well.
By default MySQL's INNODB engine puts all the data for all the INNODB tables for all databases into the same file. This file will grow as it is used but will never shrink, even if you drop a very large INNODB table. This post shows how instead to have a data file per table in the database's directory which is the same as the way the MyISAM engine works.
I recently needed to find which tables across all MySQL databases on a particular server used INNODB as the storage engine. This short post shows the query required to do this.
The MySQL function SUBSTRING allows you to extract a substring from a string. This post looks at how to use substring to extract some information from a field in a database using an example column which contains XML data.
There may be times when you need to find the location of a string within a string with MySQL. The LOCATE() function allows you to do this and I show some examples in this post of how to do it. The next MySQL post on this blog (this time next week) will combine LOCATE() with SUBSTRING() to extract substrings from a string.
MySQL has a number of ways of rounding numbers with the CEILING() FLOOR() ROUND() and TRUNCATE() functions.
Last week I looked at how to find records in a table with MySQL that are not in another table and this week look at how to delete records in a table with MySQL that are not in another table.
A couple of weeks back I posted how to find records in one table that are not in another with MySQL and received an email from Greg Jorgensen with a more efficient way of approaching the same problem and therefore revise my original post with his suggestion.
There may be times you need to check if there are records in one table that are not in another for example to check for data integrity or simply to find out if e.g. there are any products not assigned to a category or webpages to a tag. This post looks at how to do this with MySQL.
MySQL has a SQL query "SHOW INDEX FROM" which returns the indexes from a table. This post looks at some example usage of this query to get a list of indexes and a list of primary keys for a table with MySQL.
There are a couple of ways to export data from MySQL to a CSV file (refer to my using mysqldump to save data to CSV files and export data to CSV from MySQL posts for details) but neither of them supports adding a header row to the CSV which contains the column names. This post looks at how to export the data from MySQL into a CSV file with PHP including a header row.
This post shows how to use the MySQL "SHOW TABLES FROM" SQL query to get a list of tables using PHP. This list could either be stored to an array or echoed out to web browser, command line etc.
MySQL has a couple of options for exporting data: using the command line tool mysqldump (read my using mysqldump to save data to CSV files post for more details) and using a "SELECT ... INTO OUTFILE" SQL query. This post looks at the latter to export data from MySQL into a CSV file.
MySQL is often configured to be accessible from the local host only. Recently I moved all my websites from a CentOS box to a Debian one and needed to be able to connect to the MySQL server on the new box from the old one, but was not able to. This post looks at how to enable MySQL to be accessible from remote servers.
There are times when you might need to randomly order a MySQL resultset, for example when choosing some winners at random for a prize draw. This post looks at how you would do this and also offers some advice if you need to "randomly" order data across multiple pages.
MySQL has a number of string handling functions including functions to concatenate text or database columns into a single column in the resultset. I'm always forgettingthat the function is CONCAT and not CONCATENATE so maybe by writing about it I will actually remember...
14 months ago I posted how to restore data from a tab delimited file to MySQL using the command line tool mysqlimport. When it came to importing multiple files at once I suggested using a for loop but have realised there's a much simpler way to do this using wildcards or mutiple filenames.
Last week I looked at how to copy a table with MySQL using some SQL queries and then on Sunday a PHP script to automate the process. This post looks at how to copy a table with phpMyAdmin so you can easily do the same thing using a webpage GUI.
Yesterday I looked at how to copy a table with MySQL using some SQL queries. This post looks at how to automate the process with a PHP script saving you having to type in and adjust queries each time you want to back up a table.
On Wednesday I wrote a post about how to copy a table in MySQL using the SHOW CREATE TABLE sql query. Since then Paul Williams in the UK emailed me to let me know that there's a much easier way to do this using CREATE TABLE LIKE, a function which was depths of my brain somewhere but I'd since forgotten. This post revises and republishes my previous post using this simpler process.
Sometimes you might need to quickly and easily create a backup copy of a table in MySQL before doing some work to the existing table. That way you can easily copy the data back from the backup table into the original table if something goes wrong. This post looks at how to create a copy of a table in MySQL and then copy the data from the original table into the copy.
MySQL supports 4 TEXT field types (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT) and this post looks at the maximum length of each of these field types.
I recently needed to compare the content of two columns in a MySQL database that stored the large and small images for a blog post. All the small ones start with "small" followed by a number and the large ones "big" followed by a number. Enter the REPLACE() function to get rid of small/large and do the comparison! In this postI show how to use the replace function in MySQL.
The last MySQL post looked at how to tell which storage engine a MySQL table uses by running a SQL query or using phpMyAdmin. This post looks at how to change the storage engine used by a MySQL table using either a SQL query or phpMyAdmin.
MySQL supports multiple storage engines (e.g. MyISAM, INNODB, etc) each with its pros and cons, and each table in a MySQL database can have a different storage engine selected. This post looks at how to work out which table storage engine is used by a MySQL table, using either a SQL query or using the web browser tool phpMyAdmin.
Some time back I posted how to rename a table in MySQL, either using a SQL query or using phpMyAdmin. In this post I will show how to rename multiple MySQL tables from a single query, including how you would swap two table names.
There may be instances when you need to set your MySQL database to a different timezone from the one the server is in, but only for a specific website or application. It is possible to set the timezone on a per connection basis with MySQL and this post looks at how to do it.
If you are getting "too many connections" errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number. This post looks athow to update the max_connections setting in MySQL.
It is possible to insert multiple records into MySQL using a comma separated list of fields. This post looks at how to do this using SQL - if you're using a nice ORM solution instead of writing SQL queries then this won't really apply.
The MySQL command line utility allows you to run queries and view query results etc from an interactive command prompt. It is also possible to run a single query from the command line without actually going into the interactive prompt. This post looks at how to do this.
A few days ago I posted how to hide the information schema in phpMyAdmin and of course using the advice in that post you can hide any database you want. The hide_db phpMyAdmin configuration option is a regular expression so you can use it to hide several databases, and this post looks at how to do this.
mysqldump is a command line tool for outputting table structures and data and can be used for backups etc. By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump. This post looks at how to do this.
When you log into phpMyAdmin you can only see the databases for which you have access rights, as well as the information_schema database. It is possible to hide this database if you want to. This post looks at how to hide the information_schema database in phpMyAdmin.
Like other database management systems, MySQL has a range of date functions which allow you to change the formatting of dates, get day/week/month/etc parts of dates, and add or subtract intervals to a specified date. This post looks at how to add intervals to a date in MySQL.
In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.
Yesterday I looked at how to Optimize a table in MySQL from the command line interface and today will look at how to do the same thing with phpMyAdmin. This means you can just point and click in a web based interface instead of having to remember the commands and type them in.
If you have MySQL tables that grow large and then have a lot of deletes they will become fragmented and larger than they need to be. This post looks at how to look at check to see if a MySQL table is no longer optimal and how to optimize it.
MySQL, as with other database servers, has a variety of text functions including functions for converting a string to upper case or to lower case. This post looks at how to convert a string to lower case or upper case with MySQL.
Sometimes you need to rename a table in MySQL. You either run an SQL query to do this or do it with phpMyAdmin if you don't want to bother remembering the SQL to do so. This post looks at how to rename a table in MySQL either with a SQL query or using phpMyAdmin.
It is possible to reset the auto increment value of a MySQL auto incremental primary key to a new value, either higher or lower than what it would otherwise next be. This post looks at how to do this using a MySQL query and also with phpMyAdmin.
Yesterday I posted how to check if a MySQL table exists using show tables or the MySQL information schema. Today I am posting a simple PHP function I created which you can use to test if a 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.
With SQL queries you can combine the "GROUP BY" syntax with "HAVING" to return rows that match a certain count etc. This post looks at how to return rows based on a count using having specifically tested on MySQL but it should work for other database servers as well.
The MySQL Command Line client allows you to run sql queries from the a command line interface. This post looks at how to show the tables in a particular database and describe their structure. This is the continuation of a series about the MySQL Command Line client. Previous posts include Using the MySQL command line tool and Running queries from the MySQL Command Line.
There are a number of string functions in MySQL for extracting text, working out the position of a substring, calulating the length of text and so on. This post looks at how to work out the length of the longest string in a field in a MySQL table.
The MySQL command line tool allows you to run queries and administer databases from the command line. In previous posts I have looked at the basics of using the MySQL command line tool and executing shell commands. In this post I will look at running queries and the output from these.
MySQL has a useful command line tool which can be used to query and manage databases and users. This command is simply "mysql" and will usually be in the command path on Linux and BSD distributions, although to use it on Windows you would normally first need to change to the directory/folder that the MySQL binary applications are before running "mysql". This post gives a brief overview of logging in and a couple of commands for listing and changing databases, and running queries
The MySQL command line client allows you to quickly and easily run sql queries from a console session, as well as load sql script files etc. Once you have started up the MySQL command line client it is possible to execute shell commands from within the console and even drop out to another bash (or similar) session. This post looks at how to do this.
MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what's going on if there are some big, long queries consuming a lot of CPU cycles, or if you're getting errors like "too many connections".
Yesterday I looked at using mysqldump to save data to CSV files, which by default saves the exported data as tab delimited files. Today we will look at how to restore data from these files into a MySQL database. It's very simple to do, using the mysqlimport command line tool.
Yesterday I looked at basic use of mysqldump for backing up MySQL databases. Today I will look at how to use mysqldump to dump the data from a MySQL database into CSV and tab delimited text files, instead of using SQL insert queries which is the default dump method.
A simple way to back up MySQL databases is with the
mysqldump command line tool. Mysqldump can be used to back up a single database or multiple databases, and can backup MySQL databases into a text file conatining multiple SQL statements, or into CSV or tab delimited text files.
MySQL has a query cache which caches the results of SELECT queries, if enabled. This means that frequently used database queries will run much faster, because the data resultset will be read from the cache instead of having to run the query again. The MySQL query cache is available from MySQL 4.0.1. Whenever tables in the database are modified the relevant entries in the query cache are flushed so you can be certain that even with the query cache enabled only up to date data is returned.
I sell Linux on CD on my Linux CD Mall website, and it has a page showing the most popular Linux and BSD distributions that it sells. For some reason, I'd never set this up to query the information from the database, instead manually updating it periodically. Today I decided to actually get around to making it read the information from the database and have decided to share the SQL queries etc used to get the information for top sellers by category.
I run a number of web servers with PHP and MySQL and have a PHP command line script that runs on a daily basis to back up MySQL databases using the mysqldump command. It would be possible to do this using a simple bash script as well, and I know I used to use a bash script in the past but for some reason switched it to PHP at some stage.
It is possible to log all queries done by the MySQL server. To enable MySQL logging, the MySQL manual indicates you should add --log[=file_name] when starting mysqld. This isn't really the best options, as most Linux distributions use init scripts to start up MySQL, and this means either modifying them, or calling mysqld from the command line, which isn't really the best way of doing this when the init scripts do the job of starting and stopping services so nicely. So instead, it is easier to modify the my.cnf file (located at /etc/my.cnf on many Linux distros) and add the logging option there.
Dates and times are stored in MySQL in the format "YYYY-MM-DD" and "YYYY-MM-DD HH:MM:SS" which is not necessarily the format you want to display in your web page or application. There are two methods to reformat the date and time into the desired format.
This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields. The final section questions whether you really do want to delete all the data from that table.
The article covers the errors that can occur when using mysqlimport to import data into the popular open-source MySQL database server.
Deleting records with MySQL can be done by referencing records in another table with a cross join, allowing data to be deleted from one table based on values in another, or where there are no associated records in the other table.
Using MySQL version 4.0 or higher you can update a table by joining two or more tables together; this allows one table to be updated based on fields in associated records in another table.
The MySQL Control Center is a cross platform GUI for running queries on a MySQL database server. This article shows how to get mysqlcc to return more than the default 1000 rows by editing the server registration preferences or using the MySQL LIMIT syntax
The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.
phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL using a web browser and is one of the most active projects on Sourceforge.