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
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).
I 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'))
PHPMyAdmin announces drop of PHP 4 support.
I was just over at the PHPMyAdmin site and saw this quote:
Welcome to phpMyAdmin 2.11, which will probably be the last series supporting PHP 4.
Notice the "probably" stuck in there. They are testing the waters.
If phpMyAdmin is dropping the whole PHP 4 line, it just might push the WordPress adoption time a bit further. Where phpMyAdmin goes, I have to think, the ISPs will not be slow to follow.
MySQL Commands Cheat Sheet
I wanted to get a list of the tables in my MySQL database a couple weeks ago. I bookmarked on place that had a list of commands. I'm including that info here for furture reference. I've found dozens of copies of that list elsewhere, so I'm not gonna link to any particular site.
Check back here periodically as I add more and more commands here. There are already 5 good ones that I need to put on later tonight...but I've got a hot date with my wife that takes priority!
Common MySQL Commands
| Description | Command |
|---|---|
| Administrative | |
| To login as root (from shell) | mysql -uroot -p[password] |
| Create a database | create database [db name]; |
| Grant all permissions for a database to a user | grant all privileges on [db name].* to '[user]'@'localhost' identified by '[user password]'; |
| List all databases on the sql server. | show databases; |
| Switch to a database. | use [db name]; |
| To see all the tables in the db. | show tables; |
| To see database's field formats. | describe [table name]; |
| To delete a database | drop database [db name]; |
| Dump all databases for backup.Backup file is sql commands to recreate all db's. | mysqldump --user=root --password=blah --all-databases
>/tmp/sql-01_backup.sql |
| Exit mysql command line | quit |
| Queries (SELECTS) | |
| Show all data in a table. | SELECT * FROM [table name]; |
| Count rows. | SELECT COUNT(*) FROM [table name]; |
| Show certain selected rows with the value "whatever". | SELECT * FROM [table name] WHERE [field name] = "whatever"; |
| Show all records containing the name "Bob" AND the phone number '3444444'. | SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444'; |
| Show all records not containing the name "Bob" AND the phone number '3444444' order by the
phone_number field. |
SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by
phone_number; |
| Show all records starting with the letters 'bob' AND the phone number '3444444'. | SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444'; |
| Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This
finds any record beginning with a. |
SELECT * FROM [table name] WHERE rec RLIKE "^a$"; |
| Show unique records. | SELECT DISTINCT [column name] FROM [table name]; |
| Show selected records sorted in an ascending (asc) or descending (desc). | SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC; |
| Join tables on common columns. | select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id; |
| User Management | |
| Switch to the mysql db. Create a new user. | INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password')); |
| Change a users password.(from unix shell). | [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password
'new-password' |
| Change a users password.(from MySQL prompt). | SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); |
| Switch to mysql db.Give user privilages for a db. | INSERT INTO [table name]
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','db','user','Y','Y','Y','Y','Y','N'); |
| Update database permissions/privilages. | FLUSH PRIVILEGES; |
| Table Alteration | |
| To delete a table. | drop table [table name]; |
| Returns the columns and column information pertaining to the designated table. | show columns from [table name]; |
| To update info already in a table. | UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where
[field name] = 'user'; |
| Delete a row(s) from a table. | DELETE from [table name] where [field name] = 'whatever'; |
| Delete a column. | alter table [table name] drop column [column name]; |
| Add a new column to db. | alter table [table name] add column [new column name] varchar (20); |
| Change column name. | alter table [table name] change [old column name] [new column name] varchar (50); |
| Make a unique column so you get no dupes. | alter table [table name] add unique ([column name]); |
| Make a column bigger. | alter table [table name] modify [column name] VARCHAR(3); |
| Delete unique from table. | alter table [table name] drop index [colmn name]; |
| Load a CSV file into a table. | LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES
TERMINATED BY '\n' (field1,field2,field3); |
| Create Table Example 1. |
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname
VARCHAR(35),suffix VARCHAR(3), |
| Create Table Example 2. |
create table [table name] (personid int(50) not null auto_increment primary key,firstname
varchar(35),middlename varchar(50),lastname varchar(50) default 'bato'); |