Unable to start MySQL after upgrade to Ubuntu 12.04 LTS

I recently upgraded one of my Ubuntu servers from 10.04 LTS to 12.04 LTS. During the process it warned me about the servers MySQL configuration file being different then the one provided in the package by the maintainer. I selected to keep mine, with it’s customizations, however after the upgrade I was no longer able to start the MySQL service.

To get the list of MySQL packages that were installed and that I needed to remove, I used the following command:

dpkg –get-selections | grep mysql

I removed the MySQL server using the following command:

sudo apt-get remove libmysqlclient16 libmysqlclient18 mysql-client-core–5.5 mysql-common mysql-server–5.1 mysql-server–5.5 mysql-server-core–5.5

I then restarted the server using the following command:

sudo shutdown -r now

I then installed MySQL once again using the following command:

sudo apt-get install mysql-server mysql-client

However that process terminated with the error dpkg: error processing mysql-server (--configure)

This time I removed the MySQL using the following command:

sudo aptitude purge libmysqlclient16 libmysqlclient18 mysql-client-core–5.5 mysql-common mysql-server–5.1 mysql-server–5.5 mysql-server-core–5.5

Then I renamed the /etc/mysql/ directory and restarted the server using the following command:

sudo mv /etc/mysql/ /etc/mysql.old
sudo shutdown -r now

Once the server restarted I re-installed MySQL, which completed correctly, by using the following command:

sudo apt-get install mysql-server mysql-client

In comparing my old MySQL configuration file and the new configuration file I noted the difference seems to be the addition of the following line under the [mysqld] section:

pid-file = /var/run/mysqld/mysqld.pid

Also, I had to remove the following two lines that I had added to the new MySQL configuration, as the server wouldn’t start with these two settings under the [mysqld]that I used with MySQL 5.1:

default-collation=utf8_unicode_ci
default-character-set=utf8

With those lines above removed or commented out, I was able to successfully start the server and all of my databases were intact and working.

After reviewing the MySQL 5.5 documentation it looks like the server option default-collation was deprecated in MySQL 5.5.3 in favor of collation-server. The same is true of the server option default-character-set being deprecated in MySQL 5.5.3 in favor of character-set-server

Incorrect string value: ‘\xCE\x94F508…’ for column ‘value’ at row 1

Problem

Last week I was working on importing data from a vendors database into our database and for, what seemed like, random records I’d receive the error message above. Upon further inspection it turned out that the records that weren’t able to be inserted had special characters, like 'Diagnostic, ΔF508 and R117H with 5T/9T'. I knew I had set our MySQL database to use UTF-8 for the collation of the server by default, I’d even posted about it here.

I was able to resolve this, but first just a little background.

• The vendors database is an Oracle 10g system that uses UTF-8 as the character set.
• They were providing me data dumps of each table as Microsoft Excel files. I would open these using Numbers, then export them as CSV and specify using UTF-8 as the character set.
• The MySQL database I was working with had been set to use UTF-8 for it’s default collation

• I was uploading the CSV files using ColdFusion Markup Language (CFML) running on the Railo application server and the ColdFusion on Wheels framework. I had set the character set in Railo to use UTF-8

  • Railo Web Administrator > Datasource > mydsn > “Use Unicode” to true
  • Railo Web Administrator > Datasource > mydsn > “Charset” = UTF-8

Solution

After researching this and even exporting and importing my database, I found that my tables were being created specifically using the latin1 character set. I was able to determine this when I did MySQL dump of my database and examined the CREATE TABLE syntax, as shown below:

DROP TABLE IF EXISTS `addresstypes`; 
CREATE TABLE `addresstypes` 
( `id` int(11) NOT NULL AUTO_INCREMENT, 
`text` varchar(255) DEFAULT '', 
`createdat` datetime DEFAULT NULL, 
`updatedat` datetime DEFAULT NULL, 
`deletedat` datetime DEFAULT NULL, 
`createdby` varchar(25) DEFAULT NULL, 
`updatedby` varchar(25) DEFAULT NULL, 
`deletedby` varchar(25) DEFAULT NULL, 
`rixid` varchar(255) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

I found three ways to correct this. Here are the first two ways as I found at this blog post.

Single Table

Use the following SQL code block and replace the “database_name” and “table_name” values with your actual database name and table name:

ALTER TABLE `database_name`.`table_name` 
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Multiple Tables

Use the following SQL code block and replace the “database_name” with your actual database name:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` 
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt 
FROM `information_schema`.`TABLES` t 
WHERE 1 AND t.`TABLE_SCHEMA` = 'database_name' ORDER BY 1

I also found that if I performed a MySQL dump of the database, then replaced the latin1 character set command with utf8, deleted the database and then re-imported it this resolved the issue as well.

Use the following code block and replace the “database_name” with your actual database name. You’ll also need to DROP or DELETE the database after you do the MySQL dump in the first step:

mysqldump -u root -p database_name > database_name.sql 
sed 's/utf8_bin/utf8_unicode_ci/' database_name.sql > database_name_fix.sql 
sed 's/CHARSET=latin1;/CHARSET=utf8;/' database_name_fix.sql > database_name.sql 
mysql -u root -p database_name < database_name.sql

Interesting Note

Interestingly if I copied and pasted the SQL INSERT syntax into the MySQL command-line, before I changed the tables to be UTF-8, the command was successful at inserting the data.

Update: MySQL 5.5.3+ Server Options

These options have changed in MySQL 5.5.3 and newer, in particular the "default-collation" and "default-character-set" server options have been deprecated. Instead, the following server options replace these "collation-server" and "character-set-server"

MySQL won’t start after Ubuntu 11.10 upgrade

I upgrade my Ubuntu database server from 10.10 to 11.04 to 11.10 and suddenly my MySQL Server stopped responding.

Turned out the problem was that I didn’t accept one of the new apparmor configurations since it looked minimal and I had customized mine. Because of this my /var/log/mysql/error.log was filled with the following:

111020 17:31:44 [Note] Plugin 'FEDERATED' is disabled.
111020 17:31:44  InnoDB: Initializing buffer pool, size = 896.0M
111020 17:31:44  InnoDB: Completed initialization of buffer pool
111020 17:31:44  InnoDB: Started; log sequence number 0 172373406
111020 17:31:44 [ERROR] Can't start server : Bind on unix socket: Permission denied
111020 17:31:44 [ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?
111020 17:31:44 [ERROR] Aborting

I had to edit the app armor file for MySQL

sudo nano /etc/apparmor.d/usr.sbin.mysqld

changed

  /run/mysqld/mysqld.pid w,
  /run/mysqld/mysqld.sock w,

to

  /{,var/}run/mysqld/mysqld.pid w,
  /{,var/}run/mysqld/mysqld.sock w,

Moving MySQL data and log files to separate disk drives

I’ve setup a new production system for a department here on campus. The back-end system is running MySQL and I wanted to have the OS, MySQL data and MySQL log files all on separate disks. The system I’m running consists of the following:

• Ubuntu Server 10.04 LTS
• MySQL 5.1

I was able to accomplish this by doing the following procedure.

Install the new hard drives

I’m running the system on a VMWare vSphere 4.x cluster, however this should work if you’re running on a physical system as well. You’ll first need to physically (or virtually in my case) install the hard disk drives. Next we’ll need to setup the disks on the system. I followed the instructions for installing a new hard drive on the Ubnutu documentation site. I ended up with the following:

/media/mysql_data = for my data files
/media/mysql_logs = for my log files

Configure MySQL for the different drives

First we’ll need to stop the MySQL database daemon sudo service mysql stop

Give the mysql user ownership of the new directories:

sudo chown -R mysql:mysql /media/mysql_data
sudo chown -R mysql:mysql /media/mysql_logs

Copy (just in case something goes awry) the database data files and directories to the new directory, then make sure the mysql user has ownership of these files:

sudo cp -rp /var/lib/mysql/* /media/mysql_data
sudo chown -R mysql:mysql /media/mysql_data/*

Remove the log files from the data directory

sudo rm -f /media/mysql_data/*log*

Copy (just in case something goes awry) the database log files and directories to the new directory, then make sure the mysql user has ownership of these files:

sudo cp -rp /var/lib/mysql/*log* /media/mysql_logs
sudo chown -R mysql:mysql /media/mysql_logs/*

Update the MySQL daemon configuration with the changed location

sudo nano /etc/mysql/my.cnf

Find the following statement “datadir” and change it to the new location:

datadir = /media/mysql_data

I also added the following in InnoDB area under the [mysqld] section # Default data directory for data files

innodb_data_home_dir = /media/mysql_data

# Set buffer pool size to 50-80% of your computer’s memory

innodb_buffer_pool_size=896M
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /media/mysql_logs

Save the file and exit.

Configure Ubuntu AppArmor protection for MySQL

Next we need to update the apparmor file to tell the system about these new file locations

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Add the new location:

/media/mysql_data/ r,
/media/mysql_data/* rwk,
/media/mysql_logs/ r,
/media/mysql_logs/* rwk,

Modify the following file:

sudo nano /etc/apparmor.d/abstractions/mysql

Add the following line

/media/mysql_data/mysql/mysql.sock rw,

Restart the services

Now we just need to restart the AppArmor and MySQL services

sudo /etc/init.d/apparmor restart
sudo service mysql start