MySQL timestamp FAQ: How can I update a MySQL TIMESTAMP field when I issue an update for a MySQL database table?
MySQL has some crazy rules about how timestamp fields can be created, but one nice "timestamp update" syntax you can use looks like this, assuming that you're declaring a timestamp field named last_updated:
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 FAQ - how do I list all the databases in a MySQL database?
Simple. From the mysql command line, use this command:
show databases;
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 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 backup FAQ - How do I backup/dump a MySQL database schema?
Answer: Use the mysqldump database 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:
