For some reason when I use SQL I can never remember how to search for database table fields that are either NULL or NOT NULL. I always try to use the = operator or something else.
So, as a note to myself, here’s an example of how to perform a SQL query and find all records where a field in a database table is NULL:
MySQL FAQ: How do I show the schema of a MySQL database table?
Answer: Use the desc command from the MySQL command line client.
For instance, in my current application I have a database table named orders, and when I need to see the schema for that table I show it with the desc command like this:
desc orders
The MySQL output looks like this:
MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?
This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.
Specifically what I'm doing is this:
MySQL FAQ: How can I drop a series of database tables that have foreign key relationships between them?
If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.
For example, something like this should work:
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:
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.
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:
