Wordpress you are not allowed to edit this post

Recently, I've met this error when I went create a new article on my Wordpress blog.
I tried to disable all plugins and activate the wordpress default theme but not solved.
Then I checked the php error log and I saw this error:

WordPress database error Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause for the query

                        SELECT COUNT(*) FROM wp_em_events ...

I've searched few days to understand that problem was related with the ONLY_FULL_GROUP_BY sql mode.
I had used the Percona tools web interface to generate my mysql config file and in effect this config file contains parameters for sql modes such as:

sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

Solution for me was simply to disable the ONLY_FULL_GROUP_BY sql_mode by removing the parameter on the config line like this:

sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE

Save the config file and restart the MySQL/MariaDB/Percona server to validate the new config.

Then, I've tried to write a new post on the wordpress blog but same issue again.

Now, this error can be checked into the php error log file:

WordPress database error Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date_gmt' at row 1 for query INSERT INTO wp_posts 

Here, a quick search helped me to find that the NO_ZERO_DATE sql mode was the cause of this error thus I removed this sql mode into the config file again like this:

sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE

Saved and restart mysql server to validate.

After these both edits I can now write articles on the blog, very glad because lot of time was lost for this bullshit !

Chive project

Here is an interesting and beautiful alternative to the famous PhpMyAdmin that permits web-based MySQL administration in a very easy way.

Chive is distributed by the fusonic austrian team and is actually in version 1.0.2
The install is babyish, the chive interface is available in less of one minute ...
The highlight of this project is syntax editors with colored accentuation for SQL queries.
So try it and have fun !

http://www.chive-project.com/

10 essential MySQL tools for admins, toi aussi administre Mysql au top !

10 essential MySQL tools for admins, toi aussi administre Mysql au top !

Move MySQL datadir

We are going to see how to move MySQL datadir without loosing data. This is usefull when you meet a disk space problem or when you want to change your database server.

Operation is very simple, we just have to create a new directory in which we are going to copy the old datadir content. MySQL must be stopped before the copy to be sure that nothing will be modified during the data copy. Then we have to edit the mysql config file to define the new datadir. Finally, restart MySQL server and check that everything is ok. old mysql datadir: /var/lib/mysql new mysql datadir: /opt/mysql/datadir Here is the detail of commands:

# create directory, chown, chmod it
mkdir /opt/mysql/datadir
chown mysql:mysql /opt/mysql/datadir
chmod 0755 /opt/mysql/datadir

# save my.cnf
cp /etc/my.cnf /etc/my.cnf.bak

# stop mysql
/etc/init.d/mysql.server stop

# change datadir in my.cnf
vim /etc/my.cnf

:%s?/var/lib/mysql?/opt/mysql/datadir?g

:wq

# copy old datadir to new datadir
cp -rp /var/lib/mysql/* /opt/mysql/datadir/

# start mysql
/etc/init.d/mysql.server start

Now it's important to check that everything working fine, if all is ok we can remove the old datadir:

rm -rf /var/lib/mysql

Otherwise, we go back like this:

/etc/init.d/mysql.server stop
mv /etc/my.cnf /etc/my.cnf2 && mv /etc/my.cnf.bak /etc/my.cnf
/etc/init.d/mysql.server start

10 essential MySQL tools for admins

I found this very interesting article about MySQL tools on networkworld yesterday, it describes the following tools:

mk-query-digest
mydumper
xtrabackup and xtrabackup-manager
tcprstat
mk-table-checksum
stalk and collect
mycheckpoint
shard-query
mk-archiver
oak-security-audit

Here is the link:

10 essential MySQL tools for admins

MySQL-MHA: HA replication tool

A new MySQL project about high availability is proposed by Yoshinori Matsunobu. This project permits to automating master failover and slave promotion within short downtime.

This is the first time that we can see a serious MySQL failover project into replication environment. I haven't tested it yet but lot of people speak about MySQL-MHA on the web actually. Yoshinori Matsunobu is associate with SkySQL for that they provide the commercial support.

MySQL-MHA brings a lot of advantages such as:

• Automated master monitoring and failover
• Interactive (manual) Master Failover
• Non-interactive master failover
• Online switching master to a different host

Every informations and descriptions can be found on the google code web page of MySQL-MHA.
You can know more about Yoshinori Matsunobu on his blog.

Project is released few days ago and I think that it will no need to wait for a long time to seeing this marvelous tools into your MySQL production replication environment.

Innotop: MySQL InnoDB Monitor

Innotop est un clone de top (ou top-like) pour MySQL, orienté innoDB. Cette application vous permet d'effectuer de la surveillance. Par ailleurs, ce projet propose plus de fonctionnalités et de souplesses par rapport aux autres outils de la même famille.

Cette nouvelle version (1.8.0)  apporte de nouvelles fonctionnalités, comme :

• Une interface en plugins entièrement configurable
• Une surveillance du nombre de serveurs avec la possibilité de les regrouper
• Une trousse à outils variés comme mytop
• De nouvelles fonctionnalités comme l'interrogation de la période
• Analyse et affiches les informations venant de InnoDB

Lien vers le site Innotop: http://code.google.com/p/innotop/

How to compile MySQL 5.5 from sources

MySQL released 5.5 version in December 2010. This version seems to be more efficient with improvements like better improved scalability on multi-core CPU, InnoDB storage engine becomes the default engine for new tables or integration of semisynchronous replication. This version is recommended for production environments. It's interesting for administrator to thinking about upgrading. Much systems executes 5.1 version and sometimes the upgrade can be scary ... Here we are going to see how to install 5.5 version on new systems. First we need to install some packages that are needed by MySQL. So installs (or be sure that they all have been installed): bison, bzr, cmake, gcc-c++ ncurses-devel.

yum install -y bison bzr cmake gcc-c++ ncurses-devel

Then add new mysql account and group:

groupadd mysql
useradd -r -g mysql mysql

Now we need download last mysql 5.5 tar.gz archive, choose the mirror directly on mysql website.

wget http://URL_OF_MIRROR/mysql-5.5.16.tar.gz

Extracting tar.gz archive

tar -xvzf /downloads/mysql-5.5.16.tar.gz

Now go into extracted directory and execute cmake:

cd /downloads/mysql-5.5.16/
cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql \
-DMYSQL_DATADIR=/var/lib/mysql \
-DSYSCONFDIR=/etc \
-DINSTALL_PLUGINDIR=/opt/mysql/lib/mysql/plugin

Note that I use /opt/mysql for basedir /var/lib/mysql for datadir, you can use others directories by specifing them with 'DCMAKE_INSTALL_PREFIX' and '-DMYSQL_DATADIR' options. When cmake finish to work, we can launch the make .

make

Next, if we don't encounter errors, we launch the install:

make install

Now, we create symbolic links to have mysql commands in shell:

ln -s /opt/mysql/bin/* /usr/bin/

Assign owner and group:

cd /opt/mysql/
chgrp -R mysql .
chown -R root .
chown -R mysql data

Default database installation:

scripts/mysql_install_db --user=mysql \
--datadir=/var/lib/mysql/

Copy a mysql default configuration file:

cp support-files/my-medium.cnf /etc/my.cnf

Copy mysql init.d script and make it executable:

cp support-files/mysql.server /etc/init.d/mysql.server
chmod 755 /etc/init.d/mysql.server

Now edit this init.d script for customize both basedir and datadir paths:

vim /etc/init.d/mysql.server
## replace
basedir=
basedir=

## by
basedir=/opt/mysql
datadir=/var/lib/mysql

## save and exit
:wq

Finally, we can launch mysql server and begin to use it:

/etc/init.d/mysql.server start