MySql

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.

Both the e3 Commerce System and Minerva Mail were coded using MySQL and PHP, as were numerous other websites I have worked on.

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

Send CSV data to the browser from MySQL & PHP with fputcsv

Posted in MySql and PHP -

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.

Setting a column as a UUID/GIUD in MySQL - replication safe

Posted in MySql -

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.

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

Posted 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.

Suppress the "skipping the data of table mysql.event" message

Posted in MySql -

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.

ERROR 1115 (42000) : Unknown character set: 'utf8mb4' in mysql

Posted in MySql -

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.

MySQL: order a string column as an integer

Posted 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.

MySQL error can't open file - errno: 24

Posted in MySql -

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)"

MySQL queries to get the local part and domain from an email address

Posted in MySql -

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 chris@example.com.

Warning: Skipping the data of table mysql.event

Posted in MySql -

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.

Location of MySQL's my.cnf file

Posted in MySql -

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.

PHP PDO class to set the MySQL auto_increment_increment

Posted in MySql and PHP -

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.

Temporarily changing the auto increment increment in MySQL

Posted in MySql -

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.

Configuration options to fix some phpMyAdmin annoyances

Posted in MySql and PHP -

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.

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

Posted in MySql -

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_565_0.MYI'; try to repair it"

Disable the MySQL query cache without restarting MySQL

Posted in MySql -

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.

Cannot load from mysql.proc. The table is probably corrupted

Posted in MySql -

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.

PHP script to check MySQL replication status

Posted in MySql and PHP -

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

ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Posted in MySql -

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.

List stored procedures in MySQL

Posted in 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.

How to find and replace text in a MySQL database

Posted in MySql -

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.

MySQL table is marked as crashed and should be repaired

Posted in MySql -

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.

MySQL connections and PHP forked processes

Posted in MySql and PHP -

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.

PHP script to convert MySQL tables to a new storage engine

Posted in MySql and PHP -

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.

PHP script to export table creation SQL from MySQL

Posted in MySql and PHP -

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.

Master master replication with MySQL

Posted in MySql -

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.

MySQL SQL_SELECT_LIMIT

Posted in 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.

Ordering by specific field values with MySQL

Posted in MySql -

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.

Load data into MySQL with foreign key constraint issues Part 2

Posted in MySql -

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.

Randomly ordering data with MySQL with a random value column

Posted in MySql and PHP -

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.

Load JSON data with jQuery, PHP and MySQL

Posted in Javascript, MySql and PHP -

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.

Fetching data using PHP and PDO with bound placeholders

Posted in MySql and PHP -

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".

MySQL's LIMIT syntax can be slow for large offsets

Posted in 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.

Example table for MySQL

Posted in MySql -

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.

An alternative to ORDER BY RAND() for MySQL

Posted in MySql and PHP -

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.

Load data into MySQL with foreign key constraint issues Part 1

Posted in MySql -

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.

Using INSERT IGNORE with MySQL to prevent duplicate key errors

Posted in MySql -

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.

Convert a UNIX timestamp to a datetime with MySQL

Posted in MySql -

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.

MySQL: Using IF in a WHERE clause

Posted in MySql -

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.

MySQL utility commands

Posted in MySql -

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.

String concatenation with MySQL - Part 2

Posted in MySql -

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.

Auto increment a value on the fly with MySQL

Posted in MySql -

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.

MySQL Query Browser "The memory load of the system is extremely high" error

Posted in MySql -

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.

MySQL: SQL to drop a column from a table

Posted in MySql -

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.

Use mysqldump to get the schema only

Posted in MySql -

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.

Get MySQL status information from the command line

Posted in MySql -

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 CONVERT_TZ returns null

Posted in MySql -

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.

Drop multiple MySQL tables

Posted in MySql -

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.

How to drop a table with MySQL

Posted in MySql -

This post shows the SQL query for dropping a table with MySQL and also some screenshots showing how ot drop a table using phpMyAdmin.

Show indexes for a table with the MySQL INFORMATION_SCHEMA

Posted in MySql -

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.

How to tell if the MySQL Query Cache is enabled

Posted in MySql -

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.

Get a MySQL table structure from the INFORMATION_SCHEMA

Posted in MySql -

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.

Get a MySQL table structure with DESCRIBE

Posted in MySql -

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.

Logging slow queries with MySQL

Posted in MySql -

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.

Execute MySQL statements from a text file

Posted in MySql -

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.

Change the full text index minimum word length with MySQL

Posted in MySql -

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.

MySQL "Incorrect key file for table" error

Posted in MySql -

When saving a record to a MySQL table the other day I got the error message "Incorrect key file for table 'mytable'; try to repair it". I am uncertain why the error occured and how to ensure it doesn't happen again in the future but a quick fix for the time being is simple.

Use one file per table with MySQL's INNODB storage engine

Posted in MySql -

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.

Find which tables use INNODB with MySQL

Posted in MySql -

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.

Selecting substrings with MySQL using LOCATE and SUBSTRING

Posted in MySql -

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.

Finding the location of a string in a string with MySQL

Posted in MySql -

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.

Rounding numbers with MySQL

Posted in MySql -

MySQL has a number of ways of rounding numbers with the CEILING() FLOOR() ROUND() and TRUNCATE() functions.

MySQL: Delete records in one table that are not in another

Posted in MySql -

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.

MySQL: Find records in one table that are not in another - revised

Posted in MySql -

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.

MySQL: Find records in one table that are not in another

Posted in MySql -

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.

Show indexes for a table with MySQL

Posted in 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.

Create a CSV file from MySQL with PHP

Posted in MySql and PHP -

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.

Listing MySQL tables with PHP

Posted in MySql and PHP -

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.

Export data to CSV from MySQL

Posted in MySql -

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.

How to enable remote access to a MySQL database server

Posted in MySql -

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.

"Randomly" order data across multiple pages with MySQL

Posted in MySql -

Last week I looked at how to randomly order a MySQL result set by using ORDER BY RAND(). This is useful for a one-off query in which you need to draw a prize winner or similar but isn't so useful if you need to page through the resultset across several pages, because the order will change as you move from page to page.

Randomly ordering a MySQL result set

Posted in MySql -

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.

String concatenation with MySQL - Part 1

Posted in MySql -

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...

Importing data with mysqlimport from multiple files

Posted in MySql -

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.

Copy a MySQL table with phpMyAdmin

Posted in MySql -

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.

PHP script to make a backup copy of a MySQL table

Posted in MySql and PHP -

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.

Copy a table in MySQL with CREATE TABLE LIKE

Posted in MySql -

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.

Copy a table in MySQL

Posted in MySql -

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.

Maximum length for MySQL TEXT field types

Posted in MySql -

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.

Using SELECT REPLACE with MySQL

Posted in MySql -

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.

How to change the storage engine a MySQL table uses

Posted 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.

How to tell which storage engine a MySQL table uses

Posted in MySql -

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.

Rename multiple tables in MySQL

Posted in MySql -

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.

MySQL queries for article summaries

Posted in MySql -

Some time ago I posted a summary of my posts for 2008 which counted the number of posts for 2008, number of days on which I posted, summary of posts by section. Today I've decided to show the MySQL queries behind the statistics. While your content management system / blogging system (or "off the shelf" one) will have different database structures you should be able to use similar queries to work similar numbers out for your own website.

Setting the MySQL timezone per connection

Posted in MySql -

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.

How to update the max_connections setting in MySQL

Posted in MySql -

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.

Insert multiple records into MySQL with a single query

Posted 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.

Run a single MySQL query from the command line

Posted in MySql -

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.

Hide selected databases in phpMyAdmin

Posted in MySql and PHP -

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.

Selectively dumping data with mysqldump

Posted in MySql -

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.

Hide the information schema in phpMyAdmin

Posted in MySql and PHP -

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.

Using date_add in MySQL to add intervals to dates

Posted in MySql -

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.

Optimize tables in MySQL automatically with PHP

Posted in MySql and PHP -

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.

Optimize a table in MySQL from phpMyAdmin

Posted in MySql -

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.

Optimize a table in MySQL from the command line interface

Posted in MySql -

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.

Upper case and lower case strings with MySQL

Posted in MySql -

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.

Renaming a table in MySQL

Posted in 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.

Reset the auto increment value for a MySQL table

Posted in MySql -

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.

PHP function to check if a MySQL table exists

Posted in MySql and PHP -

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.

Check if a MySQL table exists

Posted in MySql -

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 "group by" and "having" with MySQL

Posted in MySql -

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.

Listing tables and their structure with the MySQL Command Line Client

Posted in MySql -

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.

Find the length of the longest string in MySQL

Posted in MySql -

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.

Running queries from the MySQL Command Line

Posted in MySql -

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.

Using the MySQL command line tool

Posted in MySql -

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

Executing shell commands from within the MySQL command line client

Posted in MySql -

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.

Showing running queries in MySQL

Posted in MySql -

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".

Restore data from a tab delimited file to MySQL

Posted in MySql -

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.

Using mysqldump to save data to CSV files

Posted in MySql -

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.

Backing up MySQL with mysqldump

Posted in MySql -

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 Query Cache

Posted in MySql -

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.

SQL query to work out top selling categories

Posted in MySql -

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.

MySQL Backups with a Command Line PHP Script

Posted in MySql and PHP -

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.

Logging queries with MySQL

Posted in MySql -

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.

Formatting Dates and Times with MySQL

Posted in MySql -

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.

Delete All Data in a MySQL Table

Posted in MySql -

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.

Errors Using mysqlimport to Import Data Into MySQL

Posted in MySql -

The article covers the errors that can occur when using mysqlimport to import data into the popular open-source MySQL database server.

Cross Table Delete with MySQL

Posted in MySql -

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.

Cross Table Update with MySQL

Posted in MySql -

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.

MySQL Control Center Returns 1000 Rows

Posted in MySql -

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

MySQL Control Center

Posted in MySql -

The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.

MySQL Control Center - Source Install Text File

-

The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.

phpMyAdmin

Posted in MySql and PHP -

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.