HOW TO: In PHP the MySQL Client API version doesn’t match the MySQL Server version

This is a fairly common situation. The short answer is that you usually don’t need to fix anything. This is a non-issue.  As long as your MySQL and MySQL Client have the same major version, you can and probably should just ignore the issue. There are no compatibility or performance issues involved.

This situation occurs when you use a package installer such as Yum to install a previously compiled version of PHP rather than compiling the version locally yourself. The package creator will have compiled PHP with a version the MySQL client that is compatible with that major release of MySQL.

To quote a 2008 article from the IUS Community project:

That said, I can tell you that in the last 4+ years of providing packages in this ‘mixed’ kind of fashion… we’ve never really come across any major issues of using php built against an older version of mysql… talking to a new version of mysql.

Now if you had to fix this, you could check to see if there is a specially compiled version of PHP with your particular MySQL client installed, but that’s unlikely.  You’re more than likely going to need to recompile php from your command line.  That slightly mismatched version is just the price you pay for convenience.

How do you delete all tables in a database in MySQL?

Sometimes you just have to start over.  I had a database that had a bunch of tables in it, almost 9000. The problem was that I didn’t know if they were the right tables.  I had a mysqldump files that did have the right tables, but I didn’t want any extras around. So I wanted to start over with the fresh import.  In MySQL you would normally just do a drop database x; create database x; and you are done.  However, I didn’t have a user with the rights to create a database.

With a small database you can do a command line like

SELECT group_concat(table_name order by table_name desc separator ‘ ‘) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘DATABASENAME’ ;

and then type drop table  and paste in the resulting table list.  This does not work with anything more than a dozen or so tables. This question gets to be very tricky when you are dealing with a LARGE database.

I found an article from back in 2006 that had a rather unique idea and I’ve expanded it to be faster and to work better with extremely large database.  You will still have to wait a bit for completion, but when you need to remove a large number of tables  from a database, try this command line:

mysqldump -u[USERNAME] -p[PASSWORD] [DATABASE]–add-drop-table –no-data –skip-lock-tables | grep ^DROP | mysql -A -u[USERNAME] -p[PASSWORD] [DATABASE]

MySQL dump will create a list of drop commands for every table in the database, the grep will isolate those and feed them back into mysql for execution.  It’s a rather slick solution and while you may still have to wait for 5 minutes (or even much more) as all the tables are listed and dropped, it will get the job done.

 

HOW TO: Installing MySQL on RedHat/CentOS/Amazon Linux in 5 easy steps.

This seems like it should be a really complicated tasks, but once you have done a few it sinks in that this is really simple…

 

Just run each of the following 5 steps as root.  Answer any questions with the common sense response, and you are done

yum groupinstall “MySQL Database”
/usr/bin/mysql_install_db –user=mysql
service mysqld start
/usr/bin/mysql_secure_installation
chkconfig mysqld on

Details

Step 1 uses the groupinstall  command to install all of the most common mysql daemons.  This one line eliminates perhaps half a dozen steps.

Step 2 does the initial DB configuration for the software.  You can select the defaults for all most all options, but when it asks about security, say no because we are gonna do it in the next two lines.

Step 3 starts the MySQL server for the first time

Step 4 when run with all the defaults secures your server nicely.

Step 5 is one that lots of  the tutorials miss.  When you reboot your DB server, it would be nice if MySQL ran right? Well this line does that.

 

If you want to change your server configuration from the default, run this line:

nano /etc/my.cnf

After saving, if you are done restart the MySQL daemon:

service mysqld start

Simple as that, you are done… Any Questions?

HOW TO: Dump or Backup all MySQL databases to separate files

I needed to transfer about 40 databases from a new clients server over to my hosting platform.

When doing a couple database exports, I might use PHPMyAdmin to do the export. Heck, it’s convenient because I usually want to look around at the same time. For doing a straight export, mysqldump is a great program that is even easier than phpmyadmin. There’s really nothing to it! (NOTE: For large databases with a LARGE number of tables you need to add –skip-lock-tables)

mysqldump –all-databases > dbdump.sql

If you want to get fancy, you can even compress the file

mysqldump –all-databases | gzip -9 > dbdump.sql.gz

But I wanted to go one step further. I wanted every database to have a separate file, compressed and correctly named.

I was shocked how simple it was to write. First I had to ask mysql for a list of the databases. Then I needed extract just the database name, removing the table borders. Then I needed to tell mysqldump to use each database name in the export source and destination.

In no time I had a 3 line script that was incredibly powerful.

I give you backupdbs.sh

#!/bin/bash
for database in $(mysql -e "show databases"|awk -F " " '{print $1}'); do
  mysqldump $database | gzip -9 > $database.sql.gz
done

 

 

Note that you may need to provide the -u and -p parameters (username and password).

That’s all there is to it!

WordPress DB Hacks: Determining the ID of a category parents

When manipulating WordPress databases for exports and merges, sometimes it is helpful to get a list of all of the parents for the categories your posts are in.

For the import I am working on right now.  The category list is being flattened from 30 categories down to 5 categories. The blog has just over 75 thousand posts in it and doing the conversion manually would be a rather arduous process to say the least.

So, I came up with this little query to give me the parent category for each of the child categories on that site.

It’s fairly simple but I’ve had to write it several times now and thought maybe someone else might need it sometime too:

SELECT `tt1`.`term_taxonomy_id` as 'Child Tax ID', `t1`.`term_id` as 'Child Term ID', `t1`.`name` as 'Child Name', `tt2`.`term_taxonomy_id` as 'Parent Tax ID', t2.`term_id` as 'Parent Term ID', `t2`.`name` as 'Parent Name' FROM `wp_term_taxonomy` `tt1`, `wp_term_taxonomy` `tt2`, `wp_terms` `t1`, `wp_terms` `t2` where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`parent` = `t2`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt2`.`term_id` = `t2`.`term_id` and `tt2`.taxonomy = 'category';

Now I can just grab it instead of rewriting it again next time.  After all, this is The Code Cave…

PS – If you just want to see the top level categories, here’s how:

SELECT `tt1`.`term_taxonomy_id`, `t1`.`term_id`, `t1`.`name` FROM `wp_term_taxonomy` `tt1`, `wp_terms` `t1`where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt1`.`parent`=0

MySQL Founder Resigns from Sun over Quality of MySQL 5.1

A picture of MySQL founder Michael "Monty" Widenius

I first laid eyes on Michael Widenius, the original and principle author of the MySQL database software at the 2008 MySQL Conference in San Jose.  Michael, who is more commonly known simply as “Monty”, had recently had his pride and joy, the MySQL AB company purchased by Sun Microsystems. I’d say that just about every attendee was extremely nervous about the future of MySQL, and every (new) Sun employee was eager to say “Oh, the purchase was great thing!”. There certainly was a Sun head hunter at every corner ready to hand out an application form (and a pair of boxer shorts or two).


MySQL_Boxer_shortsI left the conference having learned a lot of the techniques Lee Newton would be soon applying to the b5media database architecture. But far as the Sun purchase was concerned… I felt a little less safe. It was worrying that something that important was not quite as secure as it once was.  There was no indication something bad was about to happen, but the way things were, it was sure to be painful if something did.

So now not a year later, Monty announced today that he has quit because Sun released MySQL 5.1 without first resolving significant flaws despite Monty’s strenuous objections. Monty previously released a detailed list describing some of the “many known and unknown fatal bugs in the new features that are still not addressed.” My take from the article is that we should consider MySQL 5.1 should be considered a 5.0 maintenance release with pre-release beta features included.

Obviously Monty had spoken up to the higher ups at Sun prior to the release, but as he explained this had little affect.  I think that the open source world collided heavily with the corporate reality of “Cost, Schedule, Features, or Quality – Choose 3”. In the open source communities, the choice is simple, the schedule rarely if ever enters the mix. In this corporate battle, it obviously was one of the three chosen. Monty had been seen this coming early on and had been very vocal even back in April 2008 (see page 19) calling for Sun to “Create a release policy and independent release policy board that can’t be manipulated by people in charge of server development (to not allow anyone to sacrifice quality to reach personal goals)” Whoa… “To reach Personal Goals” – even then it sounded to me like he had someone in particular in mind. Additionally, from another comment later in the keynote: “Sun is more opensource/free software friendly than MySQL AB has been lately and is driving MySQL in the right direction” it seems obvious that there was a power struggle going on. After all MySQL AB was co-founded by Michael and he should have had significant influence over the company’s philosophies. I don’t know the rest of this particular sub-plot, but I’m certain there is more to be told.

In any case, in light of MySQL 5.1’s quality issues at time of general availability Michael tells us he immediately quit  but was talked into giving three months months to Sun for reconciliation and putting things right. That stretched into seven months, but the end result was the same. Michael announced today that he’s resigned and will be creating his own version of MySQL called MySQL-Maria which will will incorporate all MySQL updates but include rewrites and additional code to improve stability. It will be primarily developed by a new company he is forming named Monty Program Ab which will be “a true open source company”. I’m still not sure what that means, but I guess I could read up on it in more detail, if I wanted to.

So, what does this mean? Is it a good thing? I guess it is good that someone is out there fixing known bugs in MySQL, but won’t that happen anyway with an open source project? It’s great to see another company formed to further the open source movement, but can a MySQL standards war be beneficial?  Given the adoption rate of new MySQL releases, does it even matter that 5.1 was released? It’s not as if ISPs will install it anytime before 2010 by which time there will be patches.

In the end, from the clues I’ve seen I suspect this episode occurred due to a personal, philosophical dispute that Monty didn’t win. Regardless, I wish him success with his new project and thank him for providing a tool that I use ever day: MySQL.

WordPress Quick Tip: Fixing the number of comments per post

For various reasons, sometimes the number of comments shown under the title of the post may not match the number of actual comments displayed under the post.

Here is a simple SQL statement that will resolve the issue:

update `wp_posts` set comment_count = (select count(*) from wp_comments 
WHERE `comment_post_ID` = `ID` and comment_approved = '1')

You can use this to verify what would change and where your problems may lie:

SELECT ID, `post_title`, `comment_count`, 
    (select count(*) from `wp_comments` 
        WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1')) as NewCC 
    FROM `wp_posts` 
    WHERE `comment_count` <> (select count(*) from `wp_comments` 
        WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1'))