Business and Technology with Common Sense

We're Back

March 3, 2006 By Aaron

Finally… back online and on the right server. Welcome back to the chaos that lives here. :)

One thing I discovered, and maybe some MySQL-savvy person can offer insight on this, is that the

mysqldump

command does not necessarily work the way it is supposed to. Maybe that’s actually not a

mysqldump

issue and more a MySQL configuration issue.

# mysqldump --skip-opt --add-drop-table -hmysql.server \

     -umysql.user -p mysql.table > dump.sql

The problem that I had to manually sift through a dozen or more tables to fix is that the following command was stripping every table that had a unique ID that was auto_incremented and making the default 0.

That mean no comments could be added because they were being added with ID 0. No posts could be made because they were getting an ID of 0. Pain in the next to fix manually, but we should be working fine now.

Popularity: 1% [?]

Filed Under: Aaron Brazell Tagged With: mysql, Technology

About Aaron
I am the Lead Editor of Technosailor.com, the Author of the WordPress Bible, a WordPress project core contributor, public speaker and an all around badass. If you're interested in having me speak at your event, contact me.

Comments

  1. Sean says:
    March 3, 2006 at 9:32 pm

    That’s odd. If the column is set to auto_increment, a value of 0 in that field means “next value”. Sounds like the table is not being created correctly.FWIW, when using mysqldump I domysqldump –opt never failed for me.Sean

  2. Sean says:
    March 3, 2006 at 9:32 pm

    That’s odd. If the column is set to auto_increment, a value of 0 in that field means “next value”. Sounds like the table is not being created correctly.FWIW, when using mysqldump I domysqldump –opt never failed for me.Sean

  3. Sean says:
    March 3, 2006 at 9:32 pm

    That’s odd. If the column is set to auto_increment, a value of 0 in that field means “next value”. Sounds like the table is not being created correctly.

    FWIW, when using mysqldump I do

    mysqldump –opt

    never failed for me.

    Sean

  4. Aaron says:
    March 3, 2006 at 10:23 pm

    Sean, it was supposed to be set to auto_increment. That’s how the table structure is. But that is not how the dump came out. it was DEFAULT 0 instead. I’ve seen this happen on Plesk servers before. One of the reasons I’m tired of control panelled servers. Give me a plain jane box (or three) and set things up as they should.

  5. Aaron says:
    March 3, 2006 at 10:23 pm

    Sean, it was supposed to be set to auto_increment. That’s how the table structure is. But that is not how the dump came out. it was DEFAULT 0 instead. I’ve seen this happen on Plesk servers before. One of the reasons I’m tired of control panelled servers. Give me a plain jane box (or three) and set things up as they should.

  6. Aaron says:
    March 3, 2006 at 10:23 pm

    Sean, it was supposed to be set to auto_increment. That’s how the table structure is. But that is not how the dump came out. it was DEFAULT 0 instead. I’ve seen this happen on Plesk servers before. One of the reasons I’m tired of control panelled servers. Give me a plain jane box (or three) and set things up as they should.

  7. Eric says:
    March 4, 2006 at 3:02 am

    Actually…To be honest, I think something changed.I’ve noticed if I use phpmyadmin on my local box (no contorl panel thankyou) and do an export, and change compat. mode to ANYTHING, i get auto_inc. stripped and default set to 0Try it out ;)

  8. Eric says:
    March 4, 2006 at 3:02 am

    Actually…To be honest, I think something changed.I’ve noticed if I use phpmyadmin on my local box (no contorl panel thankyou) and do an export, and change compat. mode to ANYTHING, i get auto_inc. stripped and default set to 0Try it out ;)

  9. Eric says:
    March 4, 2006 at 3:02 am

    Actually…

    To be honest, I think something changed.

    I’ve noticed if I use phpmyadmin on my local box (no contorl panel thankyou) and do an export, and change compat. mode to ANYTHING, i get auto_inc. stripped and default set to 0

    Try it out ;)