Monday, May 28, 2012

Database migration from PostgreSQL to MySql

Since I need to use Amazon RDS for my new application, I needed to migrate from my current PostgreSQL database to MySql.

Luckily I didn't realy in anything specific of postgresql, so migration  of schema and data was a breeze. I used a free downloadable online console tool pg2mysql

Well, in the schema generated my pg2mysql the TIMESTAMP fields in Postgres were converted to TIMESTAMP datatype in MySql (which has a DEFAULT CURRENT_TIMESTAMP by default), which is not what I wanted. So I manually edited my schema dump replacing "timestamp" to "datetime".

I also changed the default MyISAM to InnoDB...

In particular, in my code I had to be careful because certain syntax changes:

  • DISTINCT ON  (field1, field2)  field2 as f1, field2 as f2, field3 -> DISTINCT(CONCAT(field1, field2)), field3



Wednesday, May 16, 2012

error running shared postrotate script for mysql

I was receiving this emails on a daily basis:
/etc/cron.daily/logrotate:
error: error running shared postrotate script for /var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log
run-parts: /etc/cron.daily/logrotate exited with return code 1
I decided to do something about it. After checking this link I determined it was because of a password error in the user debian-sys-maint.


I had deleted this user without noticing it's importance.

So in this link I found how to recover the user, and also I read Ubuntu: Reset debian-sys-maint’s mysql password ...

I hope I don't receive the email tomorrow.

Just for documentation sake, I copy the relevant contents of the previous link I mentioned.

1)
Recently I upgraded Linux on my home server and every day I would get this email:
Subject: Anacron job 'cron.daily' on server.local
/etc/cron.daily/logrotate:
error: error running shared postrotate script for '/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log '
run-parts: /etc/cron.daily/logrotate exited with return code 1
I first examined the /etc/cron.daily/logrotate script.
There was only one executable line: /usr/sbin/logrotate /etc/logrotate.conf
I next examined /etc/logrotate.conf and found this: include /etc/logrotate.d
logrotate.d is a directory of scripts to run.
SInce my error message was for MySQL, I examined the /etc/logrorate.d/mysql-server script.
One line in this script is
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
I examined /etc/mysql/debian.cnf and found the cause of the error message:
user = debian-sys-maint
password = oMhAfEiEiO
It was the PASSWORD! There was a mismatch between the password in debian.cnf and the password for thedebian-sys-maint user in MySQL.
Finally, I changed the password for debian-sys-maint in MySQL to the password listed in debian.cnf and the daily error message email stopped.

2)
Log into MySQL as the root user and run the following SQL query, substituting PASSWORD-HERE for the actual plain text password which is the same as the password in the /etc/mysql/debian.conf file:

INSERT INTO `user` (
 `Host`,
 `User`,
 `Password`,
 `Select_priv`,
 `Insert_priv`,
 `Update_priv`,
 `Delete_priv`,
 `Create_priv`,
 `Drop_priv`,
 `Reload_priv`,
 `Shutdown_priv`,
 `Process_priv`,
 `File_priv`,
 `Grant_priv`,
 `References_priv`,
 `Index_priv`,
 `Alter_priv`,
 `Show_db_priv`,
 `Super_priv`,
 `Create_tmp_table_priv`,
 `Lock_tables_priv`,
 `Execute_priv`,
 `Repl_slave_priv`,
 `Repl_client_priv`,
 `Create_view_priv`,
 `Show_view_priv`,
 `Create_routine_priv`,
 `Alter_routine_priv`,
 `Create_user_priv`,
 `ssl_type`,
 `ssl_cipher`,
 `x509_issuer`,
 `x509_subject`,
 `max_questions`,
 `max_updates`,
 `max_connections`,
 `max_user_connections`
)
VALUES (
 'localhost',
 'debian-sys-maint',
 password('PASSWORD-HERE'),
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'Y',
 'N',
 'N',
 'N',
 'N',
 'N',
 '',
 '',
 '',
 '',
 0,
 0,
 0,
 0
);
FLUSH PRIVILEGES;

3)
On Ubuntu systems there is a (system) mysql user debian-sys-maint that is used by the system’s init scripts to control the mysql database, e.g. to start or stop the mysql server. The password of this user is stored (in clear text) in /etc/mysql/debian.cnf. If this password does not match the the actual password in the mysql server the mysql init scripts will fail:
# /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld     [fail]
 * Starting MySQL database server mysqld     [ OK ]
# /etc/init.d/mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
Moreover trying to update the mysql server will fail with an error like:
Fehler traten auf beim Bearbeiten von:
 /var/cache/apt/archives/mysql-server-5.1_5.1.37-1ubuntu5.1_i386.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)
To fix the problem you have to update the mysql password for the user debian-sys-maint:
  1. Get the password from /etc/mysql/debian.cnf. The clear text password is stored twice in the file (the lines starting with “password =”:
    # Automatically generated for Debian scripts. DO NOT TOUCH!
    [client]
    host = localhost
    user = debian-sys-maint
    password = your-secret-password
    socket = /var/run/mysqld/mysqld.sock
    [mysql_upgrade]
    host = localhost
    user = debian-sys-maint
    password = your-secret-password
    socket = /var/run/mysqld/mysqld.sock
    basedir = /usr
  2. Update the password in the mysql server (you need mysql root access):
    mysql --user root --password
    mysql> SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('your-secret-password');
  3. If an previous mysql-server system upgrade failed, just restart the upgrade.
The (debian) documentation can be found in /usr/share/doc/mysql-server-5.1/README.Debian:
[...] You may never ever delete the special mysql user “debian-sys-maint”. This user together with the credentials in /etc/mysql/debian.cnf are used by the init scripts to stop the server as they would require knowledge of the mysql root users password else. So in most of the times you can fix the situation by making sure that the debian.cnf file contains the right password, e.g. by setting a new one (remember to do a “flush privileges” then). [...]

cancel script completely on ctrl-c

I found this question interesting: basically how to cancel completely a script and all child processes : You do this by creating a subro...