Tuesday, December 31, 2013

A MySQL DBA looks at PostgreSQL

So this is a journey of the/a MySQL DBA looking into PostgreSQL. It is not an attack just observations and examples. 

Using CentOS 6.5 64Bit:

rpm -ivh  http://yum.postgresql.org/9.3/redhat/rhel-6.5-x86_64/pgdg-centos93-9.3-1.noarch.rpm

yum groupinstall "PostgreSQL Database Server 9.3 PGDG"
---> Package postgresql93.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-contrib.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-libs.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

---> Package postgresql93-server.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

yum install postgresql93-server

service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]
chkconfig postgresql-9.3 on

All of the following examples are based on the PostgreSQL Wiki 
# su - postgres
-bash-4.1$ psql

psql (9.3.2)

postgres=# CREATE USER testuser PASSWORD '1234';
postgres=# GRANT ALL ON SCHEMA test TO testuser;

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO testuser;
postgres=# \q
-bash-4.1$ exit
# su testuser

$ pwd
$  psql -d postgres
psql (9.3.2)

Type "help" for help.
postgres=> CREATE TABLE test.test (coltest varchar(20));
postgres=> insert into test.test (coltest) values ('It works!');
postgres=> SELECT * from test.test;
 It works!
(1 row)

postgres=> DROP TABLE test.test;

I did notice that these grant statements worked well with 9.3 but the distro originally installed an 8.* version and some of the commands failed. 

Btw...You do of course still have features you would expect from a stable RDBM system. Granted these are simple examples.  

postgres=> EXPLAIN SELECT * from test.test;
                       QUERY PLAN                       
 Seq Scan on test  (cost=0.00..19.20 rows=920 width=58)

Show databases == \l
postgres-> \l
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

USE is standard SQL: 
postgres-> use postgres  

List all schemas:

postgres-> \dn
 public | postgres

 test   | postgres

Show create table and etc are a little more work. Granted a MySQL DBA will see it as more work because of what we are used to doing. PostgreSQL uses tablespaces more than MySQL users are used to doing. 

postgres=> CREATE TABLE sometable (somefield varchar(255), anotherfield varchar(150), datefield date );
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', '2013-12-30');
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', NOW());
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar2', 'rabuf2', NOW());
postgres=> select * from sometable;
 somefield | anotherfield | datefield  
 fubar     | rabuf        | 2013-12-30
 fubar     | rabuf        | 2013-12-30
 fubar2    | rabuf2       | 2013-12-30
(3 rows)

postgres=> select * from sometable WHERE somefield='fubar'; somefield | anotherfield | datefield -----------+--------------+------------ fubar | rabuf | 2013-12-30 fubar | rabuf | 2013-12-30 (2 rows)

postgres=> \? <-- The help will show you how to navigate around.

Show tables == \dt

postgres=> \dt
           List of relations
 Schema |   Name    | Type  |  Owner   
 public | sometable | table | testuser

postgres=> ALTER TABLE sometable ADD COLUMN intfield int[11]; ALTER TABLE

postgres=> select * from sometable; somefield | anotherfield | datefield | intfield -----------+--------------+------------+---------- fubar | rabuf | 2013-12-30 | fubar | rabuf | 2013-12-30 | fubar2 | rabuf2 | 2013-12-30 | (3 rows)

Show create table == \d+ tablename
postgres=> \d+ sometable
                                 Table "public.sometable"
    Column    |          Type          | Modifiers | Storage  | Stats target | Description 
 somefield    | character varying(255) |           | extended |              | 
 anotherfield | character varying(150) |           | extended |              | 
 datefield    | date                   |           | plain    |              | 
 intfield     | integer[]              |           | extended |              | 
Has OIDs: no

To help more you can install pgadmin 
yum install pgadmin3_93

Getting that to work and how it works could be another entire blog post. 

So far.... I will stick with MySQL but this is just a simplistic example of using PostgreSQL. This can get you started to you can evaluate for yourself. 

Helpful links below. Please refer to these as they have more experience with PostgreSQL than I do. 

Saturday, December 7, 2013

ERROR 1356 (HY000)

The most important thing to understand when coming upon an error is to take a moment and relax. 
Sometimes people run across an error and become flustered and frustrated.  Take a deep breath and relax. Sometimes "Madness is like gravity. All that needs is a little push" and Sometimes the questions are complicated and the answers are simple.”

What is all this about? Well for example I recently was asked to help solve a problem that was troubling another DBA. Nothing meant in disrespect at all to that DBA but being in a rush people just overlook the answer that is in front of them and instead spiral down into madness

Here is the general concept of the situation. 

After doing a mysqldump of a database the user moved it to another database for others to being testing. The import worked just fine. However, the users began to see errors when they tried to query from a prebuilt view. 

mysql> select * from <View_name> limit 1; 

ERROR 1356 (HY000): View '<DB_NAME>.<ViewName>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 

The first reaction was to assume that the something went wrong with the import. Did the view or actually the related tables become corrupt? 


To solve this error the first thing todo was to:    mysql> show create view <View_Name> \G 

You likely will see something that begins like this:
*************************** 1. row *************************** 
View: Visitors 


After you see who  the definer is : `<Some_user>`@`<Some_host_or_IP>`   you can view the grants for that user. 

mysql> show grants for `<Some_user>`@`<Some_host_or_IP>`; 

It turns out the the solution was just what the error said in the first place:  "definer/invoker of view lack rights to use them"

To make it easy you can do a show grants in the database where it already works and copy the grant statement, or you can review what type of permissions you need to implement in the new database to enable the view to gather the required data. 

A simple update of permissions for the user and the error was gone.  

Sometimes the answers are simple.”

Friday, November 29, 2013

A strategy from the community

We have seen the news about MariaDB replacing MySQL in Fedora , SUSE and Red Hat.

While Oracle would not be happy about such news, the open source community supports the focus on a "more" open source solution to be implemented into Linux.

The interesting thing that we all might over look is that, the decision or strategy to move to MariaDB from MySQL was likely not just made by the upper management in Red Hat. This is much more likely to be a movement from the open source community that Red Hat evaluated and they listened.

Consider this, take a look back at Jackie Yeaney's (@jackieyeaney) post about the "Democratizing the Corporate Strategy Process at Red Hat" (posted Nov 10th, 2011) and learn how Red Hat works. "We utilized existing networks in the open source community to "keep our minds open" and socialize ideas outside of Red Hat."  The community wanted openness and a result was a move to MariaDB by Red Hat, is is related to the open strategy at Red Hat, in my humble opinion it is likely yes.

 Jim Whitehurst (@JWhitehurst) appears to embrace the open source community not just because of the financial gains it rewards the company but also because how it revolutionized how they work, make strategic decisions and take input from others: "Given enough eyeballs, all bugs are shallow." 

Take a moment to relate that last statement to MySQL.  If you follow MySQL then you are well aware that Oracle closed (or have a less open version now) the bugs.mysql.com site. While Oracle has their own corporate reasoning for that, the open source community follows "Given enough eyeballs, all bugs are shallow."  

As MariaDB grows and become more imbedded into the Linux distros as the default DB the related bug tracking will be open and it will be interesting to see how the bugs split across Maria and MySQL in the long run.

So now.. the interesting aspect is while MySQL had enterprise and community versions owned by Oracle the move still happened because of MariaDB.  Oracle also owns Java and the related OpenJDK. While I am just asking this as an outsider looking in... If a Java/OpenJDK  was branched out into another software package that was not owned by Oracle, would we soon see an replacement for Java/OpenJDK in Red Hat as well ?  Again I am an outsider to the Java world, so I just ask that because of the similarities it represents with MySQL.


Tuesday, November 19, 2013

MariaDB & the Linux Distributions

So by now many of you have seen the news about Google, SUSE and  Red Hat / Fedora moving to MariaDB as the default database instead of MySQL.

MariaDB and SkySQL have made very productive business moves this year. What does this really mean for MySQL community and the general open source community ?

For starters think back to what made MySQL so popular? It was readily available in all the top Linux distributions.

OpenSuse and Fedora already are moving to MariaDB so the push for an open source focused movement has begun. Following a RHEL migration that has MariaDB as the default database would then also mean that CentOS would soon have MariaDB as the default database. 

Likely soon to follow will be a move by Ubuntu and Debian. I could have missed the news on a move already but I don't think so.

All of this is big news and moves for MariaDB and the open source community. MariaDB will naturally begin to see more user acceptance and use. While Oracle's MySQL is still an open source software package the big issue has been the bugs.mysql.com site and tracking of mysql user bugs. Will people soon start to track more bugs at MariaDB?

MariaDB also has open source features that mimic the enterprise only solutions available in Oracle's MySQL. So many users are going to naturally pick up on those features. 

While Oracle is building great features and code, but how many in the community are taking it? MySQL 5.1 is heavily used in the community used Linux distributions and many users might only know MySQL 5.1 and so soon MariaDB 5.5.   

What will happen next?
Well what Oracle decides to do is still yet to be seen. Oracle already takes Red Hat Linux to build their Oracle Linux (OEL). So ironically now MariaDB, which is forked from Oracle's MySQL, will be in Red Hat Linux for Oracle to remove for their OEL. Will Oracle fight back with Java in some way?

What will Percona do? Percona is also a player in this and has had a respectful relationship with MariaDB and Oracle over the years. Naturally, Percona does lean towards the open-source side of things so it will be curious to see if any move occurs on Percona's part.  Will more tools focused on MariaDB features becoming soon?

What will MariaDB do?  Well, monitoring the growth rate of bugs in the MariaDB Bugs database will help show how many people are starting to take on MariaDB.
Will Maria at some point break away from the MySQL source code updates and continue builds with just the community and their engineers? 

So what happens next?  We do not know. It is up to the open source community. A lot of angry voices had been directed at Oracle over the years. So how do those voices change or show support to MariaDB will soon to be seen. While many of them likely already support MariaDB, it is the adoption of the masses that will show how well MariaDB does in regards to MySQL. Many companies know the name MySQL and will be hesitant to move to MariaDB (This happened to me just the other day.) . So MariaDB's work is not yet done.
And yet after all of this... Oracle,  Red Hat and Google still come together to help the us government

Wednesday, October 30, 2013

MySQL in Google Cloud SQL

If you have not yet seen this.. pretty cool...

can "be accessed using MySQL Client, and other administration and reporting tools that work with MySQL databases."


"Google Cloud SQL is a MySQL database that lives in Google's cloud. It has all the capabilities and functionality of MySQL, with a few additional features and a few unsupported features as listed below. Google Cloud SQL is easy to use, doesn't require any software installation or maintenance and is ideal for small to medium-sized applications."


Friday, October 11, 2013

Percona 5.6 (MariaDB & MySQL) & Debian/Ubuntu

Get up and running easily with Percona 5.6 on Debian.
This example is running Linux Mint 15.

 linuxmint ~ # apt-get install percona-server-server-5.6 percona-server-client-5.6
Reading package lists... Done
Building dependency tree     
Reading state information... Done
The following extra packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libmysqlclient18 libmysqlclient18.1 libnet-daemon-perl libplrpc-perl
Suggested packages:
The following NEW packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libmysqlclient18 libmysqlclient18.1 libnet-daemon-perl libplrpc-perl
  percona-server-client-5.6 percona-server-common-5.6 percona-server-server-5.6
0 upgraded, 10 newly installed, 0 to remove and 3 not upgraded.
Need to get 40.6 MB of archives.
After this operation, 140 MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
linuxmint ~ # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
linuxmint ~ # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" -p
Enter password:
linuxmint ~ # mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" -p
Enter password:
linuxmint ~ # mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" -p
Enter password:
linuxmint ~ #
linuxmint ~ # mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.6.13-rc61.0 Percona Server (GPL), Release 61.0

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

| VERSION()     |
| 5.6.13-rc61.0 |
1 row in set (0.04 sec)

MariaDB 10 & 5.5 both resulted with the following

linuxmint ~ # apt-get install mariadb-server
Reading package lists... Done
Building dependency tree      
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.

So you need to review

linuxmint ~ # apt-cache show mysql-common | grep Version
Version: 5.5.33a+maria-1~raring
linuxmint ~ # apt-cache show libmysqlclient18 | grep Version
Version: 5.5.33a+maria-1~raring

linuxmint ~ # apt-get install mariadb-server-5.5 mariadb-client-5.5  libmysqlclient18=5.5.33a+maria-1~raring  mysql-common=5.5.33a+maria-1~raring
Reading package lists... Done
Building dependency tree      
Reading state information... Done

It is possible to download the debian packages from the dev.mysql.com/downloads site.

Wednesday, October 9, 2013

Moving from MySQL 5.6 to Percona 5.6

Percona 5.6 was recently released (yesterday at the time of this post). So as simple example what all does it take to move to this "drop in replacement?"

Oct  7 06:47 Percona-Server-56-debuginfo-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-client-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-devel-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-server-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-shared-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm

Oct  7 06:47 Percona-Server-test-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
# rpm -qa | grep MySQL

# rpm -Uhv *.rpm

While it would be nice to be able to Move from MySQL to Percona with such a simple command you will get conflicts if you tried it.

Percona-Server-server-56-5.6.13-rel61.0.461.rhel6.x86_64 conflicts with file from package MySQL-server-5.6.13-1.el6.x86_64

So instead you have to do the following.

You should already have backups but just in case create another one.

An extremely simple example below with Xtrabackup
innobackupex ./ 

  • Turn off MySQL 
  • Remove the packages 
  • Install Percona 
  • Start and connect.

# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]

# rpm -e MySQL-server MySQL-devel MySQL-client MySQL-shared MySQL-test mysql-workbench-community

# rpm -ihv Percona-Server-*.rpm
Preparing...                ####################################### [100%]
   1:Percona-Server-shared-5################################### [ 17%]
   2:Percona-Server-client-5#################################### [ 33%]
   3:Percona-Server-server-5################################### [ 50%]
Percona Server is distributed with several useful UDF (User Defined Function) from Maatkit.
Run the following commands to create these functions:
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'"
See http://code.google.com/p/maatkit/source/browse/trunk/udf for more details
   4:Percona-Server-test-56 ###################################### [ 67%]
   5:Percona-Server-devel-56###################################### [ 83%]
   6:Percona-Server-56-debug##################################### [100%]
[root@centos64 Percona]# /etc/init.d/m
mdmonitor   memcached   messagebus  mysql       mysqld     
[root@centos64 Percona]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...............             [  OK  ]

# 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'"

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.13-56-log Percona Server (GPL), Release rel61.0, Revision 461

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

You are now free to explore Percona 5.6. 

Wednesday, September 25, 2013

MySQL a Global Community

I am encouraged by the response to this blog so thank you to all that have read it.

Since MySQL is a global community. I figured I would point out the global interest that I have tracked via this blog. This by no means can determine the only interest in MySQL overall per region. Yet, I have found it interesting to see the different topics that the different countries/languages are focused on. The topics actually do vary.  Maybe you can also find something helpful and maybe it can help direct more support to the non-English community.

I will not break it down by country but instead by language to reflect the different blogs.






Tuesday, September 24, 2013

MySQL YUM Repo (Oracle's, MariaDB's and Percona's)

Lots of people today prefer to stick with the yum package manager when installing their related software over downloading the latest RPM from MySQL for example.

While you can download RPMS from a vendor and install with yum (yum install *.rpm ) you also can update your yum repo to pull directly from the Vendor for MySQL packages. At the time of this post you will only get you up to MySQL 5.5.13 even though MySQL 5.6 GA was released 5 February 2013 via the Oracle repo. Now that MariaDB has released MariaDB-5.5.33 I would hope Oracle will get a move on and update their public repo.

Regardless of what you choose. Here is how to set up vendor repos so you can access what you would like.

All of the instances have pages that I have listed that are easy to follow and set up. I will go ahead and give examples as well.

I will use CentOS 6 64bit for these examples.

In all of the  cases you will be working from the yum.repos.d directory as root.
cd /etc/yum.repos.d

wget https://public-yum.oracle.com/public-yum-ol6.repo
# vi public-yum-ol6.repo
Locate the following and edit enabled to 1 from 0 then save the file.

name=MySQL for Oracle Linux 6 ($basearch)

yum list | grep MySQL
mysql.x86_64                              5.5.34-1.el6                ol6_MySQL
mysql-devel.x86_64                        5.5.34-1.el6                ol6_MySQL
mysql-embedded.x86_64                     5.5.34-1.el6                ol6_MySQL
mysql-embedded-devel.x86_64               5.5.34-1.el6                ol6_MySQL
mysql-libs.x86_64                         5.5.34-1.el6                ol6_MySQL
mysql-libs-compat.x86_64                  5.5.34-1.el6                ol6_MySQL
mysql-server.x86_64                       5.5.34-1.el6                ol6_MySQL
mysql-test.x86_64                         5.5.34-1.el6                ol6_MySQL

vi MariaDB.repo

MariaDB does offer you the choice to pick 5.5 OR 10, I used 5.5 for this example.

# MariaDB 5.5 CentOS repository list - created 2013-09-24 21:59 UTC
# http://mariadb.org/mariadb/repositories/
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64

MariaDB-Galera-server.x86_64              5.5.32-1                    mariadb  
MariaDB-client.x86_64                     5.5.33a-1                   mariadb  
MariaDB-common.x86_64                     5.5.33a-1                   mariadb  
MariaDB-compat.x86_64                     5.5.33a-1                   mariadb  
MariaDB-devel.x86_64                      5.5.33a-1                   mariadb  
MariaDB-server.x86_64                     5.5.33a-1                   mariadb  
MariaDB-shared.x86_64                     5.5.33a-1                   mariadb  
MariaDB-test.x86_64                       5.5.33a-1                   mariadb  
galera.x86_64                             23.2.6-1.rhel6              mariadb

 vi Percona.repo

name = CentOS $releasever - Percona
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1

percona-toolkit.noarch                    2.2.4-1                     @/percona-toolkit-2.2.4-1.noarch
percona-xtrabackup.x86_64                 2.1.3-608.rhel6             @/percona-xtrabackup-2.1.3-608.rhel6.x86_64
Percona-SQL-50-debuginfo.x86_64           5.0.92-b23.89.rhel6         percona  
Percona-SQL-client-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-devel-50.x86_64               5.0.92-b23.89.rhel6         percona  
Percona-SQL-server-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-compat.x86_64          5.0.92-b23.89.rhel6         percona  
Percona-SQL-test-50.x86_64                5.0.92-b23.89.rhel6         percona  
Percona-Server-51-debuginfo.x86_64        5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-55-debuginfo.x86_64        5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-56-debuginfo.x86_64        5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-client-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-client-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-client-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-devel-51.x86_64            5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-devel-55.x86_64            5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-devel-56.x86_64            5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-server-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-server-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-server-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-shared-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-compat.x86_64       5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-compat-51.x86_64    5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-51.x86_64             5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-55.x86_64             5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-test-56.x86_64             5.6.13-rc60.6.427.rhel6     percona  
Percona-XtraDB-Cluster-client.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-debuginfo.x86_64   1:5.5.33-   percona  
Percona-XtraDB-Cluster-devel.x86_64       1:5.5.33-   percona  
Percona-XtraDB-Cluster-galera.x86_64      2.7-1.157.rhel6             percona  
                                          2.7-1.157.rhel6             percona  
Percona-XtraDB-Cluster-server.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-shared.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-test.x86_64        1:5.5.33-   percona  
jemalloc.x86_64                           3.3.1-1.el6                 percona  
jemalloc-devel.x86_64                     3.3.1-1.el6                 percona  
percona-cacti-templates.noarch            1.0.4-1                     percona  
percona-nagios-plugins.noarch             1.0.4-1                     percona  
percona-playback.x86_64                   0.6-2.el6                   percona  
percona-playback-debuginfo.x86_64         0.6-2.el6                   percona  
percona-playback-devel.x86_64             0.6-2.el6                   percona  
percona-xtrabackup.x86_64                 2.1.5-680.rhel6             percona  
percona-xtrabackup-20.x86_64              2.0.8-587.rhel6             percona  
percona-xtrabackup-20-debuginfo.x86_64    2.0.8-587.rhel6             percona  
percona-xtrabackup-20-test.x86_64         2.0.8-587.rhel6             percona  
percona-xtrabackup-test.x86_64            2.1.5-680.rhel6             percona  
qpress.x86_64                             11-1.el6                    percona  
qpress-debuginfo.x86_64                   11-1.el6                    percona

Hopefully this will help you all be able to get updated beyond what might be in your standard repos at the moment.

 I should also point out that MariaDB and Percona also have additional repos for other Linux distros.

Monday, September 23, 2013

ERROR 1146 (42S02): Table doesn't exist

So some of you might have run across the following errors when installing MySQL 5.6 :
  • ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist
You are likely amazed that you see this error on a fresh database install. You are not alone. The issue is fixable though.

The safest thing to do is to reinstall the mysql database via the following command: mysql_install_db
I recently had to do this on every fresh install (yes it happened more than once) of MySQL 5.6 on a Solaris Sparc environment.

You can try to use the following to create the missing tables but I found it best to keep everything clean and ensure all is set up with the mysql_install_db.
Some do recommend the launchpad fix I mentioned above but I like I said I prefer the mysql_install_db to ensure everything is linked installed correctly.

I have other blog posts that include examples on using this command :

Related posts on this topic:
 If you run across this from tables outside of the mysql_install_db scope see Peter's blog post to help get you started:

Wednesday, September 11, 2013


So I was recently working with mysqld_multi  and I realized that this was a feature that  I do not see in very many blog posts these days. They do exist and I have listed some at the bottom of this post for your reference.

Your reasons are likely to vary and also be debatable when it comes to the concept of: should run more than one MySQL instance on the same hardware.

To avoid any confusion, if you want to install another MySQL instance for testing purposes and not as a production instance, then you should just work with MySQL Sandbox.  If that for some reason that does not work, you can execute another server like many people typically do: create new my.cnf files and start the mysql server with the mysqld_safe and custom commands. 

Mysqld_multi makes it a lot easier for you to run multiple servers.

For example:
You have a secondary server running on port 3306. It is a read_only slave and you have a lot of hardware in place waiting to become the new primary server when the current primary fails.  You also would like to take advantage of the Percona toolkit and have a replicated secondary server that is running in a delayed mode. If you could update to MySQL 5.6 then you would not need pt-slave-delay but currently that is not an option.

In either case, you have budget limits and are not allowed another server. So do you give up? You have the disk space to hold another version of the server on your secondary box so why not? The idea of having to start and stop custom versions and etc can be off putting to some. So instead you can set up a new version of the my.cnf file but first you can do the following.

Pick you favorite editor (ie: vi )
vi /etc/multi_my.cnf
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = mysql
log = /var/log/multi_mysql.log

# Port 3306 Server
>socket = /tmp/mysql_3306.sock
port = 3306
pid-file = /var/lib/mysql/mysql_3306.pid
datadir = /var/lib/mysql/
user = mysql
Now you can take the [mysqld] section from your my.cnf file and copy it into this location.

cat /etc/my.cnf >> /etc/multi_my.cnf
If you use the command above edit to clean up so you just have the  [mysqld] section copied over.

You can then create the 3307 port section.
# Port 3307 Server
socket = /tmp/mysql_3307.sock
port = 3307
pid-file = /var/lib/mysql2/mysql_3307.pid
datadir = /var/lib/mysql2/
user = mysql
And example of the configuration can be found here:

For this example I will assume you will create a backup of Port 3306 server with Percona Xtrabackup and place it into the new datadir.
innobackupex --defaults-file=/etc/my.cnf --user=root --password=<password> --port=3306 --no-timestamp  /var/lib/mysql2/
innobackupex --apply-log /var/lib/mysql2/
 Now you can test this now with the mysqld_multi binary (/usr/bin/mysqld_multi ) or set up at start and stop script. A template comes with your MySQL install: /usr/share/mysql/mysqld_multi.server

You can copy this to your init.d directory or test it from the current location.
The script will default to the /etc/my.cnf file. So to start test this with the --default_file=/etc/multi_my.cnf report

The report option is the similar  to the status argument to see if the server is running.  If you choose to run this as the default process you can symlink or copy the /etc/multi_my.cnf as the new /etc/my.cnf
/etc/init.d/mysqld_multi.server report 1,2
/etc/init.d/mysqld_multi.server report 1
/etc/init.d/mysqld_multi.server report 2

The above would then give you running status for each give argument that of course references to a different MySQL instance. You can do the same the all of the following options : {start|stop|report|restart}

If everything went well you can "start 2" which will start the instance on port 3307.  Then log in and change master with the binlog position information provided in the xtrabackup_binlog_info file.
MASTER_LOG_FILE='<log filename>',

Start slave;
By now you have a duplicate copy of your secondary slave server.  If using pt-slave-delay you can execute the following command, the default is an hour delay.
pt-slave-delay   --port=3307 --socket=/tmp/mysql_3307.sock  --host=localhost

Hope this can at least get you started.

Saturday, September 7, 2013

MySQL access and replication blocked by secure_auth

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

If you have tried to connect to a MySQL database and you see this error then you need to have valid 41byte hash password.  If you are unsure which you have execute the SQL below. If you have 16 character passwords they are older passwords.

select Password from mysql.user;

The following is how I solved this as part of a migration from MySQL 5.0 to MySQL 5.6.

The MySQL 5.0 server had a mixture of the older pre 4.1 passwords and valid 41byte passwords.  Because the MySQL 5.0 server had some accounts with the older passwords I decided to not dump the MySQL table as part of setting up replication. I did dump all of the databases except the mysql database. This allow ensured that I would keep the valid MySQL 5.6 table enhancements.

The MySQL 5.6 server installed easily and was up and I loaded the dump data.  Part of the migration was to use replication while they evaluated the new database. While on the MySQL 5.6 server I tested the replication user account. The response I got was the error at the top of this page. Replication will not run of course without a valid user account. This is why the error logs was giving me this error:
[ERROR] Slave I/O: error connecting to master '<user>@<hostname>:3306' - retry-time: 10  retries: 68, Error_code: 2049

A quick review of the account on the MySQL 5.0 server showed that the new account was established with the pre 4.1 password.  So I needed to upgrade the account to a valid 41 byte password.

The following query showed that they did indeed have old passwords enabled. So I have to disable that and update the user account again to set the password as a valid 41 byte hash.

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

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

>GRANT REPLICATION SLAVE ON *.* TO '<user>'@'<ip_address>' IDENTIFIED BY '<Password>';
Query OK, 0 rows affected (0.00 sec)

A check of the password showed the password as the 41byte password now. I was this able to connect to the primary server from the secondary server and avoid the secure_auth error. replication connected easily and problem was solved.

Going forward I needed to get the MySQL 5.0 users accounts onto the MySQL 5.6 server. ( since I skipped them as part of building the secondary server. )

The client needed to set the grants again for each user regardless of valid password or not.
So I instructed them to execute the following sql. I could have done this but I would need to know all of their passwords and that was not needed.

For each user in their system. You do not have to do the root user because you already have a valid root account on the 5.6 system.

>SET @@session.old_passwords = 0;
>show grants for '<User>'@'<Host>';
To gather the sql needed for each user run the following :
SELECT CONCAT("SHOW GRANTS FOR '",User,"'@'",Host,"';") as sql_command from mysql.user;

For each result given execute the "show grants" statement and then execute the statement given.
The statements should be similar to the following:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

Replication then created and populated the MySQL table on the MySQL 5.6 server.

More can be found here:

Also as an update --

Check out http://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html