Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,365
    Thanks
    61
    Thanked 530 Times in 517 Posts

    Mysql has lost all my primary keys and auto increments - help!

    Gang,

    I use uniformserver - a wamp package for my local developing. It is designed like most to be portable so you can run it from a thumb drive etc.

    Yesterday I uninstalled the services and copied the directory off to my external drive so I could reinstall windows.

    Today I've copied it all back, installed the apache and mysql services and got back on with something for someone else.

    I've been hitting glitch after glitch from nowhere for no apparent reason. Anyway eventually I've gone in via phpmyadmin and found that my primary keys have all gone along with the auto_increment setting on most tables. Not one, not two or every four or five but almost every single table (a few have survived). I have around 180 tables spread over two or three databases.

    Do any of the mysql master gurus here know of any mysql magic to find out what has happened or how to undo this or am I stuck with a long hard session via phpmyadmin reinstating it all manually?

    Many thanks..
    Last edited by tangoforce; 05-29-2014 at 02:25 AM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,365
    Thanks
    61
    Thanked 530 Times in 517 Posts
    Well I've solved the problem and feel compelled to share it so that others do not make this mistake.

    A week or two back i exported (via php) a ton of tables from my database to another database while also exporting some to the same database but under a different name.

    The sql i used (in a loop for multiple tables) for this operation was as follows:

    create table [new_db.][new_name] select * from [old_db].[old_name]

    Now this does correctly copy the data and column names however it appears that it does not copy any keys you had created or the auto increment property of the primary key. The reason I used this was because phpmyadmin has a flaw - while you can export tables to a .sql file, you can't directly export them to another database. You must first export to .sql, switch to the other database and then import it (well with my version anyway). Instead I ran the above sql in a php loop to do a mass export both to the current database (in effect a rename but keeping a copy of the old table) and to a new database.

    So be warned all you googlers out there looking for a quick way to cheat phpmyadmin, using that SQL is dangerous. I must suggest that in your loop you grab the tables create sql and run that first before performing a mass insert in order to preserve the structure.
    Last edited by tangoforce; 05-29-2014 at 03:29 PM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #3
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by tangoforce View Post
    The sql i used (in a loop for multiple tables) for this operation was as follows:

    create table [new_db.][new_name] select * from [old_db].[old_name]
    You should use CREATE TABLE LIKE to copy the tables schema. That will keep the keys and such you need:
    Code:
    CREATE TABLE newdb.mytable LIKE olddb.mytable;
    INSERT INTO newdb.mytable SELECT * FROM olddb.mytable;

  • Users who have thanked Inigoesdr for this post:

    tangoforce (05-29-2014)

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,365
    Thanks
    61
    Thanked 530 Times in 517 Posts
    Well in the end I rewrote my code for future use so that it now grabs the create table [table] and then uses that instead but thanks for the tip, I shall definitely be tinkering with that shortly
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    IMO, it would be less effort to do a standard mysqldump and restore. Routine (daily) dumps are or should be a standard step in database management, especially if it's an important db.

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,365
    Thanks
    61
    Thanked 530 Times in 517 Posts
    Quote Originally Posted by FishMonger View Post
    IMO, it would be less effort to do a standard mysqldump and restore.
    You miss the point, all the tables also needed to be renamed dyamically - not something phpmyadmin caters for hence the use of php for the job.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #7
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Yes, I did miss the renaming point.

    Still, I'm willing to bet that it would be more efficient to do that with a 2 dump process. Dump the schema to an .sql file and dump the data only to a separate .sql file. Then use awk or sed or perl or php to change the table names in a copy of the schema dump. Final step would be to import the schema file into the new db then do the same with the data dump.

    If the dumps are done, as they should, as part of the nightly backup process, then most of the work would already be done and you would just need to do the import process.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •