NYCPHP Meetup

[Peter Sawczynec]

-----Original Message-----
From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
Behalf Of David Krings
Sent: Saturday, July 24, 2010 12:26 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] phpMyAdmin and MySQL DB Backup

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_c
haracter_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.

__________________

Thank you for the feedback. 

On the comments item above according to the MySQL docs:
/*  << this means start a real comment 
/*!  << this means start a comment for other dbs, but MySQL should execute
it
/*! 40101  << this means same as above but now MySQl version must be 4.01.01
or higher to execute 

On the actual backups, I will try MySQL gui tools for ad hoc backups (which
I actually already have installed, but never think to use).


Warmest regards, 
 
Peter Sawczynec 
Technology Dir.
blūstudio 
941.893.0396
ps at blu-studio.com 
www.blu-studio.com