How do Adminer et al know what to update?

Question

Here is what I am trying to understand. I have the following database table

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `Field0` int(11) NOT NULL,
  `Field1` int(11) NOT NULL,
  `Field2` enum('a','b','c') COLLATE latin2_czech_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO `test` (`Field0`, `Field1`, `Field2`) VALUES
(10,    11, 'a'),
(10,    13, 'b'),
(10,    13, 'a');

Note the absence of any indexes, unique keys... . Before someone tells me "that is not a good idea" I have strung together this example for a good reason.

Now suppose I use Adminer (you could just as easily read phpMyAdmin etc) to edit Row 2

(10,13,b)

Now after the edit if the SQL that were issued were

UPDATE `test` SET Field2 = 'c' WHERE Field0 = '10' AND Field1 = '13';

MySQL would update two rows and the table would read

(10,11,a)
(10,13,c)
(10,13,c)

which is not the desired result and not what Adminer does. It appears to know what row to update and does just that. It is not clear to me how this is done. With many other statements - e.g. CREATE TABLE it is possible to deliberately use invalid assignments (.e.g attempt to assign a length to a TEXT field and then study the SQL on error. However, that option does not exist on row edits since Adminer actively stops you from doing invalid edits (e.g. typing in a char in an integer column).

About the only way I can think of is to tag on a LIMIT 1 at the end of the statement - which would work, kind of. However, in a table with a larger number of fields it would involve putting in a very lengthy WHERE clause when only one or two fields in that row have changed in order to help MySQL identify the right row to alter.

Perhaps there are other techniques to accomplish this that are not quite so crude that I am not aware of? I'd be most grateful to anyone who might be able to help.

To finish this post - please don't tell me that rows should be unique and I should be using indices. I am well aware of that. This is simply the worst case scenario that I am trying to resolve.

I should mention that although this question talks of MySQL I am in fact using MariaDB 10.

I don't understand some of the things you wrote. You edited row 2, but you edited it to the same values it originally had. Your `UPDATE` query refers to columns named `a` and `b`, but the columns are `Field0`, `Field1`, etc. — Barmar, Jun 03 '15 at 20:54
Most tools for editing MySQL tables require that the table have a unique ID column. — Barmar, Jun 03 '15 at 20:57
your sql is flat out wrong. you're filtering on fields `a` and `b`, but those fields don't exist in your table. — Marc B, Jun 03 '15 at 20:59
@Barmar the `a` and `b` were typos as I composed this question. Corrected now. b.t.w. Adminer is happy to edit tables even with no unique column ID. — DroidOS, Jun 04 '15 at 03:32
@ceejayoz - I know it is OS. If I decide to digress into understanding another person's code I would do nothing but that. If you take a look at the Adminer code - he has broken it up into multiple files for ease of development - you will realize that it has quite a steep learning curve. — DroidOS, Jun 04 '15 at 03:34
I just installed Adminer for MySQL, and tested it on a table with no unique key. I created a duplicate row, then edited one of them. It updated both of them. So I think your original question is based on a false understanding of how it works. — Barmar, Jun 04 '15 at 20:18
It might be able to do it in other databases that have a `ROWID` function. This can be used internally as a unique ID for each row, even if there's no unique key in the data. But MySQL doesn't have this. — Barmar, Jun 04 '15 at 20:19
If adminer isn't updating both rows, it's either broken, or you have a `LIMIT 1` on the query. you are mistaken in thinking that it should be doing something else or that you should expect it to do something else. If you issue a query to update rows where x = 1, you want all rows updated, not just some of them. — Anthony, Jun 05 '15 at 08:29
@Barmar - I have edited my question. I used the MySQL tag since MariaDB is supposed to be a plugin replacement for MySQL, I am in fact using MariaDB 10. I have not yet been able to estalish whether MariaDB **does** create an automatic, ROW_NUM, ROW_ID or something of that ilk. — DroidOS, Jun 05 '15 at 08:34
I did a little googling, it apparently has options to do this, but you may need to enable it in the CREATE TABLE command. — Barmar, Jun 05 '15 at 16:53
@Barmar could you point me to what you found. The information at [https://mariadb.com/kb/en/mariadb/create-table/]https://mariadb.com/kb/en/mariadb/create-table/ is voluminous but I hae spotted nothing that suggests how this can be done. — DroidOS, Jun 06 '15 at 03:51
I googled **mariadb rowid** and found this https://mariadb.com/kb/en/mariadb/using-connect-virtual-and-special-columns/ — Barmar, Jun 06 '15 at 05:49

score 0 · Answer 1 · answered Jun 05 '15 at 10:18

You've asked to update a row, but there is no unique identifier that can be used, therefore, the software took as much constant data as it had to update the database. That data, the values of Field0 and Field1 was not unique, and so it changed all the rows it matched. If (Field0, Field1) was a unique key, or there was a (unique) primary key, it could have changed only a single row.

If (Field0, Field1) was NOT unique, and you had added a LIMIT 1, it still may have had the option to change the one other row that matched.

Without a unique way to refer to every separate row (and that would be with a primary key without NULLs), you are effectively breaking Codd's 12 rules, particularly rule #2. Since a 'perfect' relational database is mostly theoretical and impractical for everyday use, DBs can happily exist without strict compliance, but without a unique key in this instance you are just making life hard for yourself.

thank you for the answer. However, you appear to have missed the point of the question. I am trying to design and work with a badly thought out DB schema here. I am just trying to understand how Adminer manages to do what it does with a MariaDB database. — DroidOS, Jun 05 '15 at 11:39

How do Adminer et al know what to update?

1 Answers1