MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts; they keep failing because of all the foreign keys. Is there something I can do to work around this DROP TABLE foreign keys problem?
MySQL FAQ: How do I create a MySQL database table index?
Here's an example of how to create an index on a MySQL database table. In this particular example my table uses the MySQL InnoDB database engine, but that doesn't matter too much -- it just happens to be a MySQL database engine that supports foreign key relationships.
To get started, here's the SQL definition of a MySQL table I've named ftp_files:
If you want to list all of the MySQL database table column names (field names) as a simple list of names, with each column name listed on a separate line, just follow these steps.
First, start MySQL with the -sN options, like this:
$ mysql -sN -u root -p
Then execute a query like this:
MySQL Error 1293 FAQ: Help, I'm getting a MySQL incorrect table definition error message related to a TIMESTAMP column. How do I get past it?
If you ever get this MySQL Error 1293 (like I just did):
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
fear not, it may not be too bad. Let's take a look.
Today I was configuring a MySQL JNDI connection pool on a Glassfish server, when my brain went completely blank, and I couldn't remember what port MySQL listens on by default.
After a few moments of research, including looking at some output from netstat, I found the answer ... the MySQL default port is 3306.
3306
For a long time I couldn't remember how to show the indexes for a database table with MySQL, but I think it's finally sunk in. Just use the MySQL SHOW INDEX command, like this:
show index from node;
That example can be read as, "Show the indexes from the database table named node". It assumes that you have already selected a database table, so really, a full sequence of commands to show a MySQL database table index looks like this:
MySQL table/schema FAQ: How do I show a database table schema in a MySQL database?
Short answer: To show the schema for a MySQL database table, use the MySQL desc command. You issue this command from the MySQL client command line prompt, like this:
I may have shown this before in other ways, but I wanted to take a moment to show how to use a MySQL database from the MySQL command line client. The basic command to work with an existing database is the use command, where you say something like this:
mysql> use my_database
For instance, if you have a database named orders, you would declare that you want to start working with it (use it) like this:
Some time ago I created a brief blog entry that shows an HTML select/option block for displaying all the states in the United States in a dropdown list (combo box). After a few requests I'm now providing that same information here in a database table format.
I had a problem with this website last Wednesday, and as part of troubleshooting the problem, I need to look at one of the Drupal database tables, specifically the watchdog table. This table had thousands of records in it, and I couldn't find what I needed with SQL SELECT queries, so I finally dug through the MySQL documentation, and found that I could save the output from a SELECT query to a text file.
In this tutorial I'll show you both (a) how to save the results of a MySQL query to a text file, and also (b) how to log your entire MySQL session to a text file.
MySQL DATE syntax FAQ: How do I set a MySQL DATE field to default to "now", i.e., the current time?
Unfortunately you can't default a MySQL DATE field to "now", but you can get the "now" behavior with a MySQL TIMESTAMP field. The syntax to create a MySQL TIMESTAMP "now" field is:
After finishing my latest Drupal installation, I installed a MySQL database on my Linux server using yum, and then my mind went blank -- I couldn't remember how to create the initial MySQL “root” user (i.e., the initial MySQL user account).
Still unable to remember how to do this, I went ahead and started the MySQL server from my Linux root prompt just to make sure it was installed okay, like this:
MySQL FAQ: How do I define a MySQL auto increment field?
Here's an example of the way I normally create a MySQL auto increment (auto_increment) field:
Here's a quick look at how to get the generated key from a MySQL database table after performing a SQL INSERT statement on a table that has an auto_increment field. (Some databases also refer to this as an identity field.)
To get the generated key from a MySQL database table, just use the MySQL LAST_INSERT_ID function, calling it as shown below, immediately after performing your INSERT command (and very importantly, also using the same connection):
MySQL shell scripts and port FAQ: Can you share some MySQL examples that show how to start MySQL on a non-standard port (non default port)?
For a variety of reasons you may want or need to run your MySQL server on a different port than the default MySQL port of 3306. In those cases the easiest thing you can do is create a Unix/Linux shell script to start your MySQL server on some other port.
MySQL database FAQ: Can you share an example MySQL database schema (i.e., a MySQL database create script).
For what I do I often need a sample database. For my current writing I decided to use the "coffeebreak" database defined in Sun's JDBC tutorial. If it saves you any time in your work, here are the commands. I've only tested these with MySQL, but they should work with most other databases.
Before worrying about the schema, here are three steps that you'll want to do first:
I don’t know why, but I was unable to get MySQL under MAMP 3.5 to read a my.cnf configuration file. I put that file in all of the usual places, but it wasn’t read. Then I tried to get it to read my configuration file by modifying this MAMP/MySQL startup script:
/Applications/MAMP/bin/startMysql.sh
but all I got then was an error message that these two options don’t work:
--defaults-file=FILE --defaults-extra-file=FILE
I got those errors even though those options are shown in the mysqld_safe help output:
A quick note on a MySQL error: I got the following error when using mysqldump this past weekend:
mysqldump: Got error: 1044: Access denied for user 'USER'@'localhost' to database 'DBNAME' when using LOCK TABLES
The solution to this problem/error was to use one of these mysqldump commands:
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:
