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

[phil marsh]

Hi Chris,
Thanks to you and the others for your advice and information.
Looking into this last night, it appears that you're right Chris, when using the MyISAM engine, phpmyadmin appears to store all the database's relations in a special database, the pmadb specified by the configuration command $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
where the rest is specifed via the /etc/phpmyadmin/config.inc.php entries below:
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';


However, I noticed that even if database tables were changed to use the InnoDB engine and the relationships set up via phpmyadmin, phpmyadmin still did not save the relationships in the database export file.
I suspect that besides the above, I also need to change the default storage engine in MySQL to InnoDB. Any other advice is most welcome.

Thanks,
Phil


________________________________
 From: Chris Ernst <penguin-guy at comcast.net>
To: nclug at lists.nclug.org 
Sent: Friday, August 9, 2013 8:39 AM
Subject: Re: [NCLUG] mysql: backing up database - cannot save relationships
 

On 08/08/2013 07:27 PM, phil marsh wrote:
> I also notice that in the exported database file (.sql format) that there are no foreign keys specified. Pardon my ignorance but aren't the relations specified via foreign keys?
> If so, does that mean I need to be using the InnoDB engine instead of the default MyISAM engine?
> If the above are true, then how does mysql even use relationships with the MyISAM engine in phpmyadmin (front end for MySQL) (which I'm doing quite well)?

Hi Phil,

When you use the MyISAM engine, MySQL allows you to specify foreign
keys, but doesn't enforce them or even store them.  Basically, it just
allows you to pretend that you have foreign keys.

See: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

If you want actual foreign keys, you'll need to switch to the InnoDB
engine or, even better IMHO, PostgreSQL.

Cheers!

    - Chris


_______________________________________________
NCLUG mailing list      NCLUG at lists.nclug.org

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