Constraints and MySQL
Russell Keith-Magee
Adrian has finally had a chance to look at, and approve the acceptance
of, the test fixtures patch. I sat down tonight to check in the patch,
did a last little check, and hit a snag - the trunk model tests break
under MySQL. Personally, I'm a Postgres jockey, so I need some
guidance from the MySQL-using community.
The problem seems to have been introduced in [4610], and highlights in
MySQL the same problem that [4610] fixed in Postgres - when using the
InnoDB backend, foreign key constraints are checked per command, not
per-transaction, and as a result, saving forward references causes the
serializer model test to fail. If you have your MySQL setup to use
MyISAM (the default backend), you won't see the problem, because
MyISAM doesn't enforce constraints.
This also ties in with #2720, which identified the fact that many
foreign key constraints aren't being created correctly under MySQL.
Postgres has similar behaviour to InnoDB by default, but [4610]
modified the table declarations to disable constraint checking until
the end of a transaction using DEFFERABLE INITIALLY DEFERRED. However,
apparently MySQL doesn't implement this feature or an equivalent.
One way around the problem would be to use SET FOREIGN_KEY_CHECKS at
the start and end of each transactions - but if I'm reading the docs
right, this wouldn't validate any keys modified while the checks were
disabled.
Or, is there another approach that I am not aware of?
Yours,
Russ Magee %-)
Geert Vanderkelen
On 27 Feb 2007, at 16:16, Russell Keith-Magee wrote:
..
> Postgres has similar behaviour to InnoDB by default, but [4610]
> modified the table declarations to disable constraint checking until
> the end of a transaction using DEFFERABLE INITIALLY DEFERRED. However,
> apparently MySQL doesn't implement this feature or an equivalent.
InnoDB doesn't have somethink like that no.
> One way around the problem would be to use SET FOREIGN_KEY_CHECKS at
> the start and end of each transactions - but if I'm reading the docs
> right, this wouldn't validate any keys modified while the checks were
> disabled.
That's not good indeed. Not to be used to simulate deferred constraints.
> Or, is there another approach that I am not aware of?
No, not currently. Things are on their way, but that's all I can say
for now :)
Cheers,
Geert
--
Geert Vanderkelen
http://some-abstract-type.com
Seattle Daniel
Deviation from SQL standards: Like MySQL in general, in an SQL
statement that inserts, deletes, or updates many rows, InnoDB checks
UNIQUE and FOREIGN KEY constraints row-by-row. According to the SQL
standard, the default behavior should be deferred checking. That is,
constraints are only checked after the entire SQL statement has been
processed. Until InnoDB implements deferred constraint checking, some
things will be impossible, such as deleting a record that refers to
itself via a foreign key.
As I read it, there is not a way to force InnoDB to check constraints
at commit. And once there is it will be quite some time before the
mass of MySQL instances support it.
On Feb 27, 9:16 am, "Russell Keith-Magee" <freakboy3...@gmail.com>
wrote:
Jacob Kaplan-Moss
> As I read it, there is not a way to force InnoDB to check constraints
> at commit. And once there is it will be quite some time before the
> mass of MySQL instances support it.
So... I think that leaves is with two kinda sucky choices:
1. Revert [4610] so that all databases work the same way, and not
allow forward references in serializers (or elsewhere).
2. Leave [4610] in, and not allow forward references in MySQL.
I'd suggest #1 for orthogonality, but does anyone else have any ideas?
Jacob
Sandro Dentella
Why should a limit of a db impact on the others? If MySQL cannot do something
that PostgreSQL can do, that's a reason to be taken into account when
choosing the backend.
*:-)
Geert Vanderkelen
On 27 Feb 2007, at 19:59, Sandro Dentella wrote:
..
>>
>> 1. Revert [4610] so that all databases work the same way, and not
>> allow forward references in serializers (or elsewhere).
>>
>> 2. Leave [4610] in, and not allow forward references in MySQL.
>>
>> I'd suggest #1 for orthogonality, but does anyone else have any
>> ideas?
>
#1 got my vote, because..
> Why should a limit of a db impact on the others? If MySQL cannot do
> something
> that PostgreSQL can do, that's a reason to be taken into account when
> choosing the backend.
Because the other Databases have 'limitations' or 'features' or
'defects' that MySQL might not have or whatever. Django is, as I have
been told, database independent. And Django is working fine with
MySQL, lets keep it that way.
Russell Keith-Magee
>
> On 2/27/07, Seattle Daniel <daniel....@gmail.com> wrote:
> > As I read it, there is not a way to force InnoDB to check constraints
> > at commit. And once there is it will be quite some time before the
> > mass of MySQL instances support it.
>
> So... I think that leaves is with two kinda sucky choices:
>
> 1. Revert [4610] so that all databases work the same way, and not
> allow forward references in serializers (or elsewhere).
I'm -1 on this. Serialization and fixtures are near useless if you
can't do forward references. [4610] introduces a test for forward
references, and fixes a problem with Postgres. [4610] doesn't
introduce any new functionality or break an existing implementation
for MySQL - it just introduces a test that reveals a problem that has
always been there.
> 2. Leave [4610] in, and not allow forward references in MySQL.
If there is no potential for a genuine fix for MySQL, this would be my
preferred option. Reverting [4610] only serves to break Postgres; it
won't return MySQL to working status. MySQL has never allowed forward
references. We just didn't have a test that revealed the problem.
An intermediate option would be to revert/comment out the test as an
interim measure. This is the 'head in the sand' approach, but it would
let the test suite pass for MySQL. Either way, the problem/limitations
with MySQL needs to be mentioned in the documentation (both
serialization and fixtures).
Yours,
Russ Magee %-)
Marc Fargas Esteve
On 2/28/07, Russell Keith-Magee <freakb...@gmail.com> wrote:
[...]
> Either way, the problem/limitations
> with MySQL needs to be mentioned in the documentation (both
> serialization and fixtures).
Yes, in really big red letters, we could add a directive for the
documentation rst for "thinks you should care depending on your setup"
or a "Warning MySQL users" and another for "Warning Windows users" for
the timezone warning already there :)
Could the test be conditional and only run when the database is
postgresql? (or is not MySQL)
-1 on option 1, and +1 in option 2. We should not remove
functionality because MySQL can't deal with that, simply document
"this won't work with mysql" :)
Cheers,
Marc
Jacob Kaplan-Moss
[on reverting [4610]]:
> I'm -1 on this. Serialization and fixtures are near useless if you
> can't do forward references. [4610] introduces a test for forward
> references, and fixes a problem with Postgres. [4610] doesn't
> introduce any new functionality or break an existing implementation
> for MySQL - it just introduces a test that reveals a problem that has
> always been there.
Yeah, as I look into it more, I agree.
> An intermediate option would be to revert/comment out the test as an
> interim measure. This is the 'head in the sand' approach, but it would
> let the test suite pass for MySQL. Either way, the problem/limitations
> with MySQL needs to be mentioned in the documentation (both
> serialization and fixtures).
I know it's "head in the sand", but this is the approach I'd like to
take. From talking to people here -- the creator of MySQLdb, among
others -- I'm afraid there isn't a way to get MySQL to have the
deferrable stuff :(
We don't have to comment it out, though; just don't run that
particular test under MySQL. We can very clearly warn that if using
serialization with MySQL you have to be careful about the order of
your objects.
Right now #2333 is all that 0.96 is waiting on (I *really* want 0.96
to have good testing tools!), so my suggestion is that we check it in
with the test skipped for MySQL, write the doc warning, and then
continue to investigate improvements after we get the release out the
door.
Jacob
Ramiro Morales
>
> Because the other Databases have 'limitations' or 'features' or
> 'defects' that MySQL might not have or whatever. Django is, as I have
> been told, database independent. And Django is working fine with
> MySQL, lets keep it that way.
As Russell has said:
> Reverting [4610] only serves to break Postgres; it
> won't return MySQL to working status. MySQL has never allowed forward
> references. We just didn't have a test that revealed the problem.
Also, as Daniel pointed, MySQL is not following the SQL standard in
this specific issue. And considering Oracle now owns InnoDB I wouldn´t
hold my breath waiting for this being implemented anytime soon.
Regards,
--
Ramiro Morales
Andy Dustman
InnoDB is not dead, and it's not the only game in town for
transactional backends for MySQL:
http://dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html
http://dev.mysql.com/doc/falcon/en/index.html
Also, MySQL with MyISAM does pass the unit test for the same reason
sqlite does: No foreign keys. Not that I'm advising anyone to use
MyISAM in general.
Russell Keith-Magee
>
> Django is, as I have
> been told, database independent. And Django is working fine with
> MySQL, lets keep it that way.
Well, no, MySQL isn't working fine. Like I said, [4610] didn't break
MySQL - it just revealed a problem that has always been there but
wasn't being tested. [4610] introduced a test for the problem, and
fixed the problem for Postgres. SQLite never had the problem. MySQL
still has the problem. Reverting [4610] won't fix MySQL - it only
serves to break Postgres, and hide the problem from both.
Yours,
Russ Magee %-)
Nicola Larosa
> Right now #2333 is all that 0.96 is waiting on
Please don't forget #1984. Thank you.
--
Nicola Larosa - http://www.tekNico.net/
When talking about Security, most people think about something where
"they" attack and "we" defend. If they succeed only once, we have lost.
If we succeed in defending, the next wave of attackers will be ready,
meaner and faster than the first wave. This is not "Security", this is
"Space Invaders". -- Kristian Köhntopp, April 2006
Marc Fargas Esteve
On 2/27/07, Geert Vanderkelen <ge...@kemuri.org> wrote:
> Because the other Databases have 'limitations' or 'features' or
> 'defects' that MySQL might not have or whatever. Django is, as I have
> been told, database independent. And Django is working fine with
> MySQL, lets keep it that way.
I think the problem is that MySQL **has** the limitation, not the
other databases. and anyway as Russel pointed out MySQL is not
working. The difference is that now **we know it doesn't** reverting
4610 would make us **not knowing** again :)
Andy Dustman
keyword on INSERT statements, which causes errors to be treated as
warnings.
http://dev.mysql.com/doc/refman/5.0/en/insert.html
Not sure how hard it would be to incorporate this for this particular
case, though.
--
Patriotism means to stand by the country. It does
not mean to stand by the president. -- T. Roosevelt
This message has been scanned for memes and
dangerous content by MindScanner, and is
believed to be unclean.
Michael Radziej
> Here's another possible solution/workaround: MySQL supports an IGNORE
> keyword on INSERT statements, which causes errors to be treated as
> warnings.
>
> http://dev.mysql.com/doc/refman/5.0/en/insert.html
>
> Not sure how hard it would be to incorporate this for this particular
> case, though.
You're sure? The docs say:
"Specify IGNORE to ignore rows that would cause duplicate-key violations."
The rows are ignored, not the errors.
I haven't tried it out, though.
Michael
--
noris network AG - Deutschherrnstraße 15-19 - D-90429 Nürnberg -
Tel +49-911-9352-0 - Fax +49-911-9352-100
http://www.noris.de - The IT-Outsourcing Company
Vorstand: Ingo Kraupa (Vorsitzender), Joachim Astel, Hansjochen Klenk -
Vorsitzender des Aufsichtsrats: Stefan Schnabel - AG Nürnberg HRB 17689
mario__
> -1 on option 1, and +1 in option 2. We should not remove
> functionality because MySQL can't deal with that, simply document
> "this won't work with mysql" :)
>
+1
Regards!
Geert Vanderkelen
Great! Then I'll relaunch my proposal for 'Choose Storage Engine per
Model' :)
-1 , unless it can be fixed in code and not manual.. Because saying
it 'Does not work with MySQL' is false. It depends.
Andy Dustman
>
> Andy Dustman:
> > Here's another possible solution/workaround: MySQL supports an IGNORE
> > keyword on INSERT statements, which causes errors to be treated as
> > warnings.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/insert.html
> >
> > Not sure how hard it would be to incorporate this for this particular
> > case, though.
>
> You're sure? The docs say:
>
> "Specify IGNORE to ignore rows that would cause duplicate-key violations."
>
> The rows are ignored, not the errors.
>
>
> I haven't tried it out, though.
Neither have I, but the passage quoted above refers to a specific case
with duplicate key errors. The problem here is not duplicate keys but
missing foreign keys, and so I'm hoping what it will do is insert the
row anyway and produce a warning. That is my interpretation of:
"If you use the IGNORE keyword, errors that occur while executing the
INSERT statement are treated as warnings instead."
However, this needs to be tested, and even so, I'm not sure how hard
this is to do within the context of the code.