-----Original Message----- From: talk-boun...@lists.nyphp.org [mailto:talk-boun...@lists.nyphp.org] On Behalf Of David Krings Sent: Saturday, July 24, 2010 12:26 PM To: NYPHP Talk Subject: Re: [nyphp-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 p...@blu-studio.com www.blu-studio.com _______________________________________________ New York PHP Users Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/Show-Participation
