MySQL

From Docunext Technology Wiki

MySQL Database area. I've been using MySQL for quite some time and I really like it. I use the command line client and phpMyAdmin to manage the servers and databases.

Installation

On Debian, installing MySQL is easy:

sudo apt-get install mysql-server

After that, I usually run "sudo mysql_secure_installation" which goes through the process of assigning important passwords.

Replication

One of my favorite things about MySQL is its ability to replicate itself. I've written more on the subject here: MySQL Database Replication

MySQL Errors

1033

ERROR 1033 (HY000): Incorrect information in file:

I got this when I would try the following SQL command:

describe mytable;

The problem was that it was an innodb table, but /etc/mysql/my.cnf had "skip-innodb" set. I commented it out, restarted, and it worked. :-)

2013 and 1129

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

No clue! Its not a version conflict though, I have two clients with the same version, one can connnect, the other cannot.

I rebooted the non-functioning client, tried again and got this:

ERROR 1129 (00000): Host 'dev-91-gl.savonix.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

That seemed to have worked. Why all the errors?

definer/invoker of view lack rights to use them mysql

This is a tough one! It is a cryptic error, and sometimes involves another error saying "SUPER is required". It happens in various circumstances, but for me it happened when I tried to dump and restore a database that has views.

As a workaround, I grepped out the definer statements, like so:

cat mysqldump.sql | grep -v "^\/\*\!50013" > newmysqldump.sql

MySQL Proxy

This project is still very new, but in June 2008, I tried it out and it worked for me. Its now available in Debian Lenny and Etch-Backports, very nice! The version I tried lacks a start-up script, so I'm just using a call from /etc/rc.local to my basic start-up command. I'm now using it on some low-volume sites, so far, so good!

The architecture of this program is interesting - the core is written in C, and it implements its configuration via lua. It reminds me a little of the architectural relationship between Apache and mod_perl.

See mysql-proxy for more information.

Links