[NCLUG] mysql: backing up database - cannot save relationships

Thu Aug 8 19:10:29 MDT 2013

Hi Jared,
From the Ubuntu 10.04 machine with the database correct and original:
mysql> SHOW TABLE STATUS FROM library WHERE name='papers'\G;
*************************** 1. row ***************************
           Name: papers
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 880
 Avg_row_length: 203
    Data_length: 179324
Max_data_length: 281474976710655
   Index_length: 11264
      Data_free: 0
 Auto_increment: 2380
    Create_time: 2013-04-29 15:14:20
    Update_time: 2013-07-31 15:38:04
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

And from the Ubuntu 12.04 machine with the database saved from 10.04 (exported) and then imported to the 12.04 machine:
mysql> SHOW TABLE STATUS FROM library WHERE name='papers'\G;
*************************** 1. row ***************************
           Name: papers
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 880
 Avg_row_length: 203
    Data_length: 179104
Max_data_length: 281474976710655
   Index_length: 11264
      Data_free: 0
 Auto_increment: 2380
    Create_time: 2013-08-08 19:04:54
    Update_time: 2013-08-08 19:04:54
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

This is a real mystery to me. When I export the database using phpmyadmin on the 12.04 machine then import it with phpmyadmin on the same machine, the links are gone and the relationships are also gone. And I specified to export both structure an data. It seems this had worked once on the machines some time ago but is not now.
Thanks,
Phil

________________________________
 From: Jared Hall <jrhall at gmail.com>
To: nclug at lists.nclug.org 
Sent: Thursday, August 8, 2013 6:40 PM
Subject: Re: [NCLUG] mysql: backing up database - cannot save relationships

What is the output of:

SHOW TABLE STATUS FROM database WHERE name='table'\G;

For the given tables on both machines?

Jared

On 08/08/2013 04:20 PM, phil marsh wrote:
> Could it be that the relationships are not stored in each database? Does that mean I need to export and import something else too?
> Basically, I'm attempting to transfer all my databases from my Ubuntu 10.04 machine to my Ubuntu 12.04 machine so I can use them while I rebuild my Ubuntu 10.04 machine.
> Thanks,
> Phil
>
>
>
>
> ________________________________
>  From: Bob Proulx <bob at proulx.com>
> To: nclug at lists.nclug.org 
> Sent: Thursday, August 8, 2013 5:20 PM
> Subject: Re: [NCLUG] mysql: backing up database - cannot save relationships
>  
>
> phil marsh wrote:
>> Some of the tables contain links to files on disk.
> Are you saying that the file names are changing?  That doesn't make sense.
>
>> Relationships are database structures that link one table to another
>> in the database. Say if one table contains a person's name, and
>> another table in the database also contains the person's name with
>> other columns containing personal information, a relationship will
>> link the two columns containing the person's name in the two tables
>> of the database.
> Normally that will simply be the id field for that entry.  A persons
> table would have an id field.  Another table would reference it with a
> person_id field.  If the id were 42 then the integer 42 would be in
> that person_id field.
>
> Are you saying that the id field numbers are being changed?  That
> would definitely break the relationship between objects!
>
> That makes me think that when you did the import that you did not
> import into an empty table and the data appended to it and all of the
> ids were given new id numbers instead of restoring the previous
> numbers.  Just guessing here though.
>
>> These above two structures are apparently not being saved in the
>> export database backups I'm trying to make. My Google efforts have
>> been fruitless and it's almost as if nobody else has seen this, so
>> my error is likely obvious and/or simple but I just can't find it.
> What command are you using to dump the database?  And what are you
> using to restore?
>
>   mysqldump --defaults-file=/etc/mysql/debian.cnf foo | gzip > foo.sql.gz
>
> Although normally I use --all-databases --events to ensure I get
> everything and can't forget to back up a new db.  But then doing a
> single db restore is more complicated.
>
> Also MySQL keeps a special database "mysql" for holding account access
> and other related things.  In addition to your normal database you
> will also want to dump the mysql database too.  You can almost always
> recreate this with some work so it isn't as critical.  But if you are
> doing backup then might as well grab it too.
>
>   mysqldump --defaults-file=/etc/mysql/debian.cnf mysql | gzip > mysql.sql.gz
>
> Bob
> _______________________________________________
> NCLUG mailing list      NCLUG at lists.nclug.org
>
> To unsubscribe, subscribe, or modify
> your settings, go to:
> http://lists.nclug.org/mailman/listinfo/nclug
> _______________________________________________
> NCLUG mailing list      NCLUG at lists.nclug.org
>
> To unsubscribe, subscribe, or modify
> your settings, go to:
> http://lists.nclug.org/mailman/listinfo/nclug

_______________________________________________
NCLUG mailing list      NCLUG at lists.nclug.org

To unsubscribe, subscribe, or modify
your settings, go to:
http://lists.nclug.org/mailman/listinfo/nclug