Posts in the “mysql” category

A MySQL stored procedure example

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.

[toc hidden:1]

MySQL foreign key examples (How to define foreign keys in MySQL)

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:

[toc hidden:1]

The MySQL SHOW INDEX command

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: How to show the schema of a MySQL database table

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:

How do I access a MySQL database with the MySQL command line client?

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:

How to save the output from a MySQL query to a file

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.

How to default a MySQL date (timestamp) field to now

MySQL DATE syntax FAQ: How do I set a MySQL DATE field to default to "now", i.e., the current time?

Setting the date to "now"

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:

Initial MySQL root user account

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:

How to get the generated key from a MySQL auto_increment field after an INSERT

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: How to start a MySQL server and client on a non-standard port

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 example database: The Coffee Break Database from Sun’s JDBC tutorial

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:

How to use a my.cnf configuration file with MAMP/MySQL

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:

Update MySQL timestamp on update

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:

The PHP MySQL mysql_connect() can't connect to local MySQL server through socket error

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:

[toc hidden:1]

Solving the MySQL "can't create/write to file" error message

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:

mysqldump command - how to dump (backup) a MySQL database

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.