NYCPHP Meetup

[David Krings]

On 7/24/2010 10:47, Peter Sawczynec wrote:
> Okay, here is an issue that is not straight PHP related, but close and I
> really could use some good feedback.
>
> ___________
>
> Recently, I have had a DB dump/backup (created using the phpMyAdmin interface)
> fail because there is a single table in this db that needs this exception
> written into the backup file output: "SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;"
>
> It seems, the phpMyAdmin interface does not have an option to create this in a
> backup output.
>
> Is anyone familiar with a db backup/copy tool that can handle this special
> exception state and can backup/copy a db with this need?

Well, what I do (and that is very unconventional and may not work in most 
environments) is to turn the server off, then make file copies of the contents 
of the data folder. The databases and the associated files are easy to 
identify. So far this worked for me. The major drawback is that the server is 
down for the time of copying the files. There are probably also other problems 
that may occur, which I haven't encountered (yet). I only mention it, because 
it is the simplest of all options.

Did you try pulling the backup with the tools provided by MySQL? Their new 
workbench is nice, but you may run into problems like I did where the UI comes 
up either looking like scrambled eggs (they eventually fixed it for my case 
after many many builds) or is drawing excrutiatingly slow (still a bug). The 
workbench app is a combination of the old GUI tools. The old tools work 
flawlessly and I didn't really see the reason why MySQL decided to abandon 
those, but this is free stuff and I can't complain too loudly.


> Two other small things:
>
> 1)  This term:  SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO" << this is part of SQL or
> MySQL?

Based on googling and finding this post:
http://drupal.org/node/164401
it appears to be MySQL specific.
You find it also in the MySQL docs here:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

In summary, a table has 0 as an autoincrement value, which on restore triggers 
normally a reassignment of an autoincrement number. The problem with that is 
that your table is not as backed up and it may cause problems when other 
records reference that 0 (e.g. when it is used as recordID). So, from what I 
understand one way to get around this all is to remove the record with the 
autoincrement value of 0. After that this mode would no longer be needed. IIRC 
you cannot simply edit an autoincrement field (by design), so you'd need to 
take the restrictions of and do some data massaging.


>
> 2) /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; << if I
> see a row like this in a db dump, what is it? is this a comment or what?

Are you asking about the "OLD_CHARACTER_SET_CLIENT" part? That is documenteded 
here:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_character_set_client

Or more about the "/*" part? In this case it appears to be a comment, see here:
http://dev.mysql.com/doc/refman/5.1/en/comments.html


>
> Thanks for any input on the above.


Hopefully I could help at least a little bit.

David