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

[Ben West]

Phil, Bob, Et. Al. I watched this conversation helpless to interject as I
wasn't sure how to close the gap on relationships (vs. Contraints, as they
are called) but the overall conversation confused me in that I've never had
a reason to use the database constraints feature, and I'm interested to
know what factors make it a useful value-add.  I get the impression that
Phil is using PHP as his application, much in the same way one would use
Microsoft Access, but am still confused about the rationale.  I would love
for more information about this to be a future NCLUG talk, and would go way
out of my way to attend.

Signed,
Very Curious (Ben) in Loveland


On Sun, Aug 11, 2013 at 3:46 PM, Bob Proulx <bob at proulx.com> wrote:

> 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
> _______________________________________________
> NCLUG mailing list       NCLUG at lists.nclug.org
>
> To unsubscribe, subscribe, or modify
> your settings, go to:
> http://lists.nclug.org/mailman/listinfo/nclug
>