Posts in the “mysql” category

Two MySQL error messages

Earlier this week I ran into a problem with a website that I tracked down to a MySQL error message that looked like this:

can't create/write to file /var/lib/mysql/foo.bar.baz
Errcode: 23

(The actual file referenced in that error message doesn't matter, as it will be different on most systems, but the "can't create/write to file" is important.) After digging through my log files even more, I also found this error message related to MySQL:

MySQL date/time FAQ: How do I create a field in a MySQL database table that will default to the current date and time whenever a new record is inserted into my table?

Answer: Just define the field in your table as a timestamp field, and combine that with the default keyword and the MySQL now() function when you define this field in your database table.

The syntax for creating a MySQL timestamp field that defaults to the current date and time when creating a new database table looks like this:

Here's a quick example of how to default a MySQL timestamp field to "now", i.e., the current date and time. The following MySQL table definition shows a field named date_created that uses the now function to record the timestamp for when the row was created:

Here is some code that I use at the beginning of a MySQL database script to (a) create a database, (b) create a local user to access that database ('foo_user'@'localhost'), (c) create a remote user that can access the database ('foo_user'@'%'), and (d) then use that database (which I need to do before starting a bunch of CREATE TABLE statements):

A JDBC MySQL transactions tip: If your transactions aren't working with your new MySQL database you may have the same problem I just had. I created my database tables with the default MySQL storage engine (MyISAM), and guess what? MyISAM doesn't support transactions.

I got a really brutal looking error message from Spring and MySQL yesterday. I've been working on a Java-based web interface for Nagios for a client, and I ran into an error message that basically says "Cannot add or update a child row: a foreign key constraint fails". If you like ugly, gruesome error messages here is the full-blown error:

I got the following MySQL error ("1005 error") earlier today when trying to create a table with a foreign key relationship:

MySQL FAQ - how do I list all the databases in a MySQL database?

Simple. From the mysql command line, use this command:

show databases;

MySQL backup FAQ - How do I backup/dump a MySQL database schema?

Answer: Use the mysqldump database utility.

MySQL dump examples using the mysqldump utility

On a DOS/Windows pc with no name/password protection, you can dump a database named my_db with the following command, but don't do this just yet:

mysqldump my_db

Note that this gets you not only the database schema, but also the current data in the table.

I just ran into a situation where I needed to import some CSV data into a MySQL database table. I already had the data I needed in a CSV file format, and I needed to import the data in that file into my MySQL database table.

A few more specifics about the problem and the solution: