MySQL secure_auth error

I addressed the secure_auth errors before when it blocks replication in this blog post.

However, I figured I would make this blog post a more general fix when connecting via MySQL clients. This is for servers before MySQL 5.6.

So if you get a secure_auth error when connection to MySQL the following steps should clear this error.

+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | 34d22ac342c35af2                           |   |+---------------+-------------------------------------------+

SELECT @@session.old_passwords, @@global.old_passwords;                                                            +-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+

mysql> SET @@session.old_passwords = 0;  SET @@global.old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       0 |                      0 |
+-------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'authdtestuser'@'localhost' = PASSWORD('sshthisisasecret');
Query OK, 0 rows affected (0.00 sec)

mysql> select User , Password from mysql.user where User = 'authdtestuser';
+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | *E48BD8BF1B9F29459E5284AD158443B5B33B70E4 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.old_passwords = 1;  SET @@global.old_passwords=1;

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)


MySQL, Ubuntu:: mysqld does not have the access rights

So today I happen to need to restore a MySQL database from backups so I could recover some tables. While I left he production database running via port 3306, I set up the backup via port 3307.

However, when I attempted to start another version via the 3307 port in a mysql_restore directory but  I ran into some errors....


/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf

[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
[Warning] Can't create test file /var/lib/mysql_restore/localhost.lower-test
Can't find file: './mysql/plugin.frm' (errno: 13)

InnoDB: Completed initialization of buffer pool
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

# perror 13
OS error code  13:  Permission denied


So I first checked the permissions on the directory, ensured that it was set to 700 and owned by mysql.  I also ensured that the directories inside the data directory was set to 700 and owned by mysql. The rest of the files all set to 600 ( -rw-rw---- ).  Just to be safe I also ensured that the disk was not full.

So the error log shows permission issues but at first glance all permissions are correct. So what it is?

Well, if I was on RHEL, or related branches,   I would think of SELinux. Was it blocking it somehow.
The Ubuntu Apparmor needed to be reviewed in this case.


# cat /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
...


Notice that the default "var/lib/mysql" is being addressed. The restore directory I created was not though. So by adding that and a restart of apparmor I was then able to successfully start MySQL.


# vi /etc/apparmor.d/usr.sbin.mysqld
...
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql_restore/ r,
  /var/lib/mysql_restore/** rwk,
...
/etc/init.d/apparmor {start|stop|restart|reload|force-reload|status|recache}
/var/lib/mysql_restore# /etc/init.d/apparmor restart
...
/usr/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf
...
# mysql -P 3307 --socket=/var/lib/mysql_restore/mysqld_3307.sock


Additional URLs for review if needed:

• https://help.ubuntu.com/community/AppArmor
• http://ubuntuforums.org/showthread.php?t=782224
• http://www.cyberciti.biz/faq/suse-ubuntu-linux-start-stop-restart-apparmor-command/

MySQL Table error 1064

So I came across an odd situation today.

I have a system that creates memory tables using the PHP  $_COOKIE['PHPSESSID'] value.

Once some work is done it then drops the table.

Two sample tables are below for my example .


@@VERSION: 5.6.19-log
CREATE TABLE `f7a7a8d3a7ba75b5eb1712864c9b27eb` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;

CREATE TABLE `8865e52c7e1bea515e7156f240729275` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;


Now some simple processing occurs on the real tables I used then like I said I dropped them.


DROP TABLE IF EXISTS f7a7a8d3a7ba75b5eb1712864c9b27eb;
Query OK, 0 rows affected (0.09 sec)


Simple enough until I ran across this..


desc 8865e52c7e1bea515e7156f240729275;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8865e52c7e1bea515e7156f240729275' at line 1

 DROP TABLE IF EXISTS 8865e52c7e1bea515e7156f240729275;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8865e52c7e1bea515e7156f240729275' at line 1


Now the fix is actually very simple. But the question is why does one work and one fail?

To fix this just backtick the table name. Which I should have done from the start anyway. That is just a good practice to get into.


DROP TABLE IF EXISTS `8865e52c7e1bea515e7156f240729275`;
Query OK, 0 rows affected (0.02 sec)


Now I first thought that maybe the issue was Alpha-numeric name but we can see that one version  works.  The difference though is the start of the table name. One is an integer and one is a Alpha character. So again .. follow best practices and quote table names to avoid such stupid mistakes.


After all the error code is a parse error..


perror 1064
MySQL error code 1064 (ER_PARSE_ERROR): %s near '%-.80s' at line %d


MySQL random integers

This is not a new feature by any means but it is a question I have happen to see pop up every now and then. So a quick example is following.

To generate a random integer within MySQL you can use the Floor and Rand functions. The MySQL manual documents this here: http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_rand

"To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i))"

So to give an example:

> SET @I = 3; # lower range
> SET @J = 43 - @I; # max range minus lower range

> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 4
1 row in set (0.00 sec)
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 8
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 15
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 16
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 23
1 row in set (0.00 sec
> SELECT FLOOR( @I + (RAND() * @J )) as lottery_numbers\G
*************************** 1. row ***************************
lottery_numbers: 42
1 row in set (0.00 sec


Installing Percona XtraDB Cluster

So of course Percona has documentation that explain the process. The goal of this blog is to go into a little more detail in hopes that can help someone.

Hyperlinks for review:

• CentOS - http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html
• Ubuntu - http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/ubuntu_howto.html
• Installing Percona XtraDB Cluster from Binaries -   
• http://www.percona.com/doc/percona-xtradb-cluster/5.5/installation.html

Prerequisites

• Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568
• Stop iptables for internal local networks or adjust iptable rules.

/etc/init.d/iptables stop


• SELinux is disabled

echo 0 >/selinux/enforce
vi /etc/selinux/config


• Set Up SSH Keys and place into authorized_keys so all the id_rsa.pub values are in authorized_keys on all servers.

# ssh-keygen -t rsa
# cd /root/.ssh/
# cp id_rsa.pub authorized_keys
# chmod 600  /root/.ssh/authorized_keys
# chmod 700  /root/.ssh/


So I started with a basic server install of CentOS 6.5.

# yum -y install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum -y install http://mirror.pnl.gov/epel/6/x86_64/epel-release-6-8.noarch.rpm
# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
# wget http://www.percona.com/downloads/RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
# yum -y install socat


To avoid any conflicts I removed the mysql-libs and related dependencies

# rpm -e mysql-libs postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat


Then I installed the Percona Cluster packages.

# yum -y install Percona-XtraDB-Cluster-full-56
[root@node1 ~]#  /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"


So we can replace the items we removed per node..

yum -y install postfix cronie redhat-lsb-core redhat-lsb-printing redhat-lsb-graphics libcgroup numad redhat-lsb sysstat crontabs cronie-anacron redhat-lsb-compat


So repeating the above steps will install the packages so that we can configure the cluster next.

[root@node2 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!
[root@node3 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........ SUCCESS!


While we have three instances of MySQL running it is not yet a cluster.

Configuring the nodes

• http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html
• http://galeracluster.com/documentation-webpages/configuration.html
• http://galeracluster.com/documentation-webpages/search.html
• http://www.magicposition.com/2012/01/16/installing-galera-mysql-clustering-on-ubuntu-10/

Node 1 /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.33

# SST method
#wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW

# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:<password_here>"

# server_id
server_id=3232235553  #SELECT INET_ATON('192.168.0.33')

#[client]
socket=/var/lib/mysql/mysql.sock


Starting the 1st Cluster Node

 /etc/init.d/mysql start --wsrep-cluster-address="gcomm://"
Starting MySQL (Percona XtraDB Cluster)...................................... SUCCESS!

[root@node1 mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6
seqno:   -1
cert_index:


The cluster is only one node at the moment.

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 1


OK now now one is up and running we can start node 2
Node 2 /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.34

# SST method
#wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW


# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:"

# to enable debug level logging, set this to 1
wsrep_debug=1

# server_id
server_id=3232235554  # SELECT INET_ATON('192.168.0.34')

#[client]
socket=/var/lib/mysql/mysql.sock

 [root@node2 mysql]#/etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!


Now compare our values on each node.

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 2

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node2.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 2
1 row in set (0.18 sec)


Now we add node 3 into the mix.

Node 3 /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# node 1 192.168.0.33
# nod3 2 192.168.0.34
# nod3 3 192.168.0.35
wsrep_cluster_address=gcomm://192.168.0.33,192.168.0.34,192.168.0.35

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB auto increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.0.35

# SST method
# wsrep_sst_method=xtrabackup
 wsrep_sst_method=rsync      #
# wsrep_sst_method=rsync_wan  #
# wsrep_sst_method=mysqldump  # SLOW


# Cluster name
wsrep_cluster_name=percona_cluster

# Authentication for SST method
wsrep_sst_auth="root:"

# to enable debug level logging, set this to 1
wsrep_debug=1

# server_id
server_id=3232235555 # SELECT INET_ATON('192.168.0.35')

#[client]
socket=/var/lib/mysql/mysql.sock

[root@node3 mysql]#/etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)........................... SUCCESS!

[root@node3 mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    97c457f8-f3d2-11e3-9b4e-374ebb7427e6
seqno:   -1
cert_index:


So how do all of our nodes look like now.

 mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node1.localdomain
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node2.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3

mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G
*************************** 1. row ***************************
@@hostname: node3.localdomain
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3


Test the nodes
So now we can load some data and test it out..

[root@node2 ~]# wget http://downloads.mysql.com/docs/world_innodb.sql.gz
[root@node2 ~]# gzip -d world_innodb.sql.gz
[root@node2 ~]# mysql -e "create database world"
[root@node2 ~]# mysql world < world_innodb.sql


SO now that everything is loaded… is it all across the cluster?

     @@hostname: node1.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001

     @@hostname: node2.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001

     @@hostname: node3.localdomain
DATABASE_SCHEMA: world
         ENGINE: InnoDB
   count_tables: 3
    TOTAL_DB_GB: 0.001


Looks like it is working.