Discovered some fun things in MySQL today..
Set the variable @id to 0:SET @id = 0;
Update the column id to be id + 1 – this will re-index your id column in one fell swoop. Very nice:UPDATE tblname SET id = (@id := @id + 1);
Set the first value for an auto_increment column:ALTER TABLE tblname AUTO_INCREMENT = 196;
(this won’t work if there are rows with a higher value already in the table)
All in all, a few nice bits and pieces for cleaning up your tables.
Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /blah/somepage.php on line 123
The reason for this warning is because PHP4 can’t communicate with a MySQL5 server unless it’s using old passwords (set by using the OLD_PASSWORD() function below.)
There are two simple ways to fix this. The first would be to upgrade your version of php. Unless you *really* need php4 (or below?!) upgrade! I’m not sure php4 is supported any more and should be considered a security risk. If you can’t upgrade, just do this in your database:
update mysql.user set password=OLD_PASSWORD('thepassword') where user = 'theusername';
and:flush privileges;
This should do the trick. Be warned, if you issue any GRANT statements after this you will have to update mysql.user again.