Troubleshooting MySQL

After upgrading my FreeBSD ports, I tried to view my (this) WordPress blog, but instead received:

“Error establishing a database connection”

*panic*

When I try to log into MySQL through the CLI, I get “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)”

So we’re off to a bad start.  I’m not too familiar with MySQL, so I’m not sure where to start.

The first thing I did is, as root, was run:  mysqld_safe &

As soon as I did, my websites all came and things are working fine.

I then tried to restart mysql (it helps to tail the mysql log to get more info on what’s going on)

In one session:  /usr/local/etc/rc.d/mysql-server stop

In the other session, I tail the log with:  tail -f /var/db/mysql/servername.err (where “servername” is your server’s local IP, ie:  192.168.0.1.err)

The log correctly tells me that mysql isn’t running.  When I start it back up, I get:

090217 11:00:36 [ERROR] Incorrect definition of table mysql.event: expected column ‘sql_mode’ at position 14 to have type set(‘REAL_AS_FLOAT’,’PIPES_AS_CONCAT’,’ANSI_QUOTES’,’IGNORE_SPACE’,’NOT_USED’,’ONLY_FULL_GROUP_BY’,’NO_UNSIGNED_SUBTRACTION’,’NO_DIR_IN_CREATE’,’POSTGRESQL’,’ORACLE’,’MSSQL’,’DB2′,’MAXDB’,’NO_KEY_OPTIONS’,’NO_TABLE_OPTIONS’,’NO_FIELD_OPTIONS’,’MYSQL323′,’MYSQL40′,’ANSI’,’NO_AUTO_VALUE_ON_ZERO’,’NO_BACKSLASH_ESCAPES’,’STRICT_TRANS_TABLES’,’STRICT_ALL_TABLES’,’NO_ZERO_IN_DATE’,’NO_ZERO_DATE’,’INVALID_DATES’,’ERROR_FOR_DIVISION_BY_ZERO’,’TRADITIONAL’,’NO_AUTO_CREATE_USER’,’HIGH_NOT_PRECEDENCE’,’NO_ENGINE_SUBSTITUTION’,’PAD_CHAR_TO_FULL_LENGTH’), found type set(‘REAL_AS_FLOAT’,’PIPES_AS_CONCAT’,’ANSI_QUOTES’,’IGNORE_SPACE’,’NOT_USED’,’ONLY_FULL_GROUP_BY’,’NO_UNSIGNED_SUBTRACTION’,’NO_DIR_IN_CREATE’,’POSTGRESQL’,’ORACLE’,’MSSQL’,’DB2′,’MAXDB’,’NO_KEY_OPTIONS’,’NO_TABLE_OPTIONS’,’NO_FIELD_OPTIONS’,’MYSQL323′,’MYSQL40′,’ANSI’,’NO_AUTO_VALUE_ON_ZERO’,’NO_BACKSLASH_ESCAPES’,’STRICT_TRANS_TABLES’,’STRICT_ALL_TABLES’,’NO_Z
090217 11:00:36 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
090217 11:00:36 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: ‘5.1.28-rc’  socket: ‘/tmp/mysql.sock’  port: 3306  FreeBSD port: mysql-server-5.1.28_1

At this point, I logged into MySQL and ran:

mysql> show databases; (to see available databases)

mysql> connect mysql; (to connect to the mysql database)

mysql> show tables; (to view what tables are contained in mysql)

mysql> select * from event; (show what is contained in the table ‘event’)

mysql> desc event; (shows datatype for the column)

This entry was posted in Geek and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *