home | tech | misc | code | bookmarks (broken) | contact | README


Mysql notes

Basic setup

To insert a database in a MySQL server, just use the mysqladmin command:

$ mysqladmin -u root -p create databasename

Now login as the root user of the server:

$ mysql -u root -p databasename

Then create the user that will own the database:

CREATE USER username IDENTIFIED BY 'userpassword';

Then, give permissions to that user on the database:

GRANT ALL ON databasename.* TO 'username'@'hostname' IDENTIFIED BY 'userpassword';

Then, just connect to the database with your new user:

$ mysql -p -u username databasename

Generic notes

A note on user creation and its privileges

I'm not used to databases, but I use them since I develop applications that must store their data somewhere.

When configuring the database for koha, I did:

CREATE DATABASE koha;
CREATE USER kohaadmin IDENTIFIED BY 'katikoan';
GRANT ALL ON koha.* TO 'kohaadmin'@'localhost';
FLUSH PRIVILEGES;

But, after creating the kohaadmin user, I couldn't connect to the database. Logging in as root and seeing the mysql.User table showed the problem:

mysql> use mysql;
(...)
Database changed

mysql> SELECT Host, User, Password FROM user WHERE User = 'kohaadmin';
+-----------+-----------+-------------------------------------------+
| Host      | User      | Password                                  |
+-----------+-----------+-------------------------------------------+
| %         | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
| localhost | kohaadmin |                                           |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

Nice, so we don't have a password configured for kohaadmin for localhost access? Our last GRANT command was wrong, we should have used clause IDENTIFIED BY in this command:

GRANT ALL ON koha.* TO 'kohaadmin'@'localhost' IDENTIFIED BY 'katikoan';
FLUSH PRIVILEGES;

Now, it looks ok:

mysql> SELECT Host, User, Password FROM user WHERE User = 'kohaadmin';
+-----------+-----------+-------------------------------------------+
| Host      | User      | Password                                  |
+-----------+-----------+-------------------------------------------+
| %         | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
| localhost | kohaadmin | *9BBFD4AD8554387AE1366CC62F4C576DDC495EF1 |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

Creating index in a better way

One can use the following well-known syntax to create an index:

CREATE INDEX indexname ON tablename(columnname);

In MySQL this approach has a problem because MySQL copies the table to a temporary file, create the index and copy it back. So, if you need to create several indexes, a copy of the entire table is needed for each index creation. This can be really slow on huge tables.

The InnoDB Engine has an approach. You can create several index at once, using the ALTER TABLE command:

ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

This will still copy all the table file elsewhere, but only once, create all the index and copy it back.

See InnoDB Fast Index Creation for more information.

Troubleshooting

MySQL don't start up because of permission problems

When seeing the logs of mysql, you might see something like that:

121326 18:13:19  mysqld started
121026 18:13:19  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

You should check the permissions for all the files mysql will access match the user that runs mysql. In my environment, for example, there is a user "mysql" that runs the daemon. So, supposing database is installed in /usr/upkg/var/mysql, we do:

# cd /usr/upkg/var/mysql
chown -R mysql:mysql *

But the error persists. I discovered that the problem was that I forgot to change the ownership of the parent directory. Then:

# chown mysql:mysql /usr/upkg/var/mysql

And it worked.

ERROR 2006 (HY000) at line 76: MySQL server has gone away

I got an error similar to that when trying to import a file generated by mysqldump. The reason was that, on the record located at line 76, a big chunk of data couldn't get inserted to the database and the server just refused. In this link I discovered that it was due the max_allowed_packet variable of the [mysqld]] section on MySQL settings, located at the my.cnf file. I changed it to 64M, restarted the server and the restore operation worked.

Note that, on NetBSD, the file my.cnf doesn't exist by default. Just create it on /usr/pkg/etc/my.cnf with the following contents:

[mysqld]
max_allowed_packet = 64M