I'm not normally a big stored procedure user or developer, but since stored procedures are supported in MySQL 5.0 and beyond I thought I'd give them a spin. In this blog post I'll show you how to create a very simple MySQL stored procedure. It's not quite a "Hello, world" stored proc, but it's close.
MySQL foreign key FAQ: How do I define a foreign key in MySQL?
Answer: Here's a quick example of how I typically define a foreign key in MySQL.
Diving right into an example, here's the definition for a MySQL database table named nodes that I will link to from a second table:
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:
MySQL FAQ: How can I find all MySQL database tables that have specific column names?
I found the solution on this SO page. Here’s my take on it.
First, assuming that you want to copy and paste those column names after you get them, I recommend starting the MySQL command line client like this:
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:
PHP MySQL socket error FAQ: I'm getting a MySQL error like "Warning: mysql_connect(): Can't connect to local MySQL server through socket '/home/lib/mysql/mysql.sock'" when trying to connect to my MySQL database from a PHP application, what can I do to fix this?
I just ran into this problem on a new server I'm using with A2 Hosting, where the exact error I got in my PHP MySQL program is this:
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 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.
