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

[Bob Proulx]

Hi Phil,

Phil Marsh wrote:
> Thanks for your help with this! I found that as you said, I need to
> use the InnoDB storage engine to get the foreign keys into the
> databases themselves as the MyISAM storage engine does not directly
> support foreign keys. Therefore, exported MyISAM database tables will
> not reflect the relationships in the database as seen in the
> phpmyadmin GUI.

That still doesn't sound right because MyISAM fully supports foreign
keys.  I, and I dare say almost everyone, use them all of the time.

Are you actually talking about foreign key *constraints*?  That
additional single word makes a lot of difference about what we are
talking about.  And if so then you are right that MyISAM does not
support constraints while InnoDB does.  But constraints are
intrinsically non-portable between database engines.

> However, the thing that made this deceptive is that phpmyadmin has
> provisions for "artificially" supplying relationship capability while
> using even the MyISAM storage engine for tables in
> databases. Apparently one can use phpmyadmin + MySQL with MyISAM to
> define relationships between table columns!

I can tell by the tone of this last message that you consider the
matter resolved now.  That is great!  But for some of us I think we
are still very confused.  I know I am.  And curious.

Since making a small test case seems impractical it would be great if
you could produce a screenshot of the phpmyadmin interface that you
see both before and after that shows the problems you were having.  It
would help the rest of us to understand what it is that you are if we
could see what you are seeing.

Don't send the screenshot to the mailing list.  That would be much too
large of a message.  The usual thing is to post it somewhere like
picpaste or other and then send a URL.  But for simplicity if you send
a screenshot to me I will reduce the image to a good size and picpaste
it for you and send the URL to the mailing list afterward.  Thanks.

> So how does phpmyadmin allow relationships between table columns while
> using MyISAM?
> 
> As far as I can tell when using tables with the MyISAM engine,
> phpmyadmin stores the relations in a special database that's specified
> in its configuration file namely the pmadb database.

If so then that would in no way be useful to your application that
uses the database.  Because it wouldn't use or reference the pmadb.
So pretty much you should ignore it.

> The pmadb database also apparently contains the saved MIME and LINK
> information that produces clickable links in the phpmyadmin GUI for
> the database data (so you can open files via clicking links in table
> columns).

What version of phpmyadmin are you using?  For me I don't see any of
this when looking at my databases with phpmyadmin.  I double checked
using 4.0.5 and the older 2.9.1.1.

> However, it appears that the MIME and LINK information (properties of
> columns that allow clickable links to files and websites within
> tables) still do not get preserved in the exported backup
> files.

But won't all URLs and files be simple text strings, aka VARCHAR(255)
or some such, in the database?  If phpmyadmin is somehow allowing you
to define that as something different within phpmyadmin that seems
useless outside of phpmyadmin.

Unless the phpmyadmin web interface is actually your application I
suggest ignoring any phpmyadmin feature such as this.  It doesn't help
you with your application that is using the database and it will only
cause confusion.

> Please comment.

Done. :-)

Bob