Mysql Database: Is it merely a collection of tables?

Question

I was trying to copy data from one of the tables in a database to a new empty table in another databse. Both databases are from a same application, but the new one has higher version. So the two tables have similar structure except the new and empty one has several more columns. I am not very familiar with database, so I was using some awkward way to do this. First I compare the two tables and find out the additional columns, then I backup the old table and added the additional columns to the old table and make sure the newly added columns have exactly the same attributes. So the two tables have exactly same structures... or so I think. Then I export the old table to .sql file. Then I edit the table name occurences in the .sql file and import it to the new table. The above operations were done in phpMyAdmin. After this, the two tables appear to be identical in phpMyAdmin, they have same structure and data. I thought the data migration was successful, however, when I run the application, front-end is showing some warning message. I didn't do anything else to the application that would possibly trigger these error messages, so I suppose it has something to do with the way I migrate the data. Not sure about it, I wrote to the application author, he says "By doing it manually you've messed up the integration between the tables ", so my questions are:

Is mysql database merely a collection of tables? If two databases have exactly same tables with same structure and data as shown in phpMyAdmin, can we call these two databases identical? What is this "integration between the tables" referring to?

I have very limited knowlgedge about database, I think I'd better get these questions answered before I can wrote to the author again and ask him more specific questions.

I am using Joomla 3.4, and the application I mentioned above is an extension called Preachit4.0.

Answer 1

Is mysql database merely a collection of tables?

That is a bit of an understatement, since it's a collection of tables, views, procedures, functions, triggers, events... but, yes, a database is no more than the sum of its parts, including their metadata (character set, collation, foreign key constraints).

If two databases have exactly same tables with same structure and data as shown in phpMyAdmin, can we call these two databases identical?

phpMyAdmin is to MySQL what the Junior Color Encyclopedia of Space is to astrophysics, so that's a little harder to commit to. Assuming it doesn't hide anything from you, in an effort to be "helpful," then that also should be true.

However, that isn't the complete picture, even if it is true.

What is this "integration between the tables" referring to?

That's not a phrase that has a clear and unambiguous meaning when discussing a database, but the likely explanation is this:

Most tables don't operate in a vacuum. They have columns whose value references one or more rows in another table containing the same value. These are called foreign keys. They are supposed to be defined in the table definition, though some developers don't (for some outdated reasons).

So, not only would this table need to have identical structure and values... but all the other tables and their values would need to be identical as well.

When foreign keys are defined in the tables, they enforce consistency among the tables. If, for example, "user_id" in a "comments" table referenced the "id" column in the user table, the database could be configured so that attempting to delete a user would be denied, if the user's id appears in the comments table. Or it could be configured to automatically delete all the "comments" rows for that user when the user was deleted, or to set the user_id to null if the user row was deleted. All three of these actions maintain one or another form of internal consistency among the tables, by preventing a dangling, invalid reference from one table to another

The actual term for this is referential integrity, but it seems like this may be what the author meant.

The problem is, even if the foreign key constraints are defined, the database is typically specifically configured not to validate this while loading a .sql dump file, on the assumption that (a) the tables may not be loaded in order, so invalid references are temporarily acceptable, (b) constraint checking during loading imposes extra work and slows down the load, and should be unnecessary because, (c) the data is assumed to have come from a valid database.

The likely explanation is that you have broken referential integrity because there is at least one other table containing different data.

Unless you can find evidence of this being the case and fix it... or the application is open source and you are prepared to read and understand the code to come to an understanding of the nature of the error... you may be in a bit of a pickle.

Answer 2

If table name is the same - you could try dump table data as inserts and load/import this data to new table. New columns get default values or null.