[Dbix-class] any factual comparisons of Oracle, PostgreSQL, MySQL ?

[Brian E. Lozier]

I think you are mistaken, at least historically.  MySQL indeed has a
history of allowing you to insert NULL values into NOT NULL columns,
but instead of inserting nulls it silently converts the NULL you
specified into an empty string.  This is really bad.

There are a number of places in the MySQL manual that address this:

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

    If strict SQL mode is not enabled, MySQL sets the column to the
implicit default value for the column data type.

    If strict mode is enabled, an error occurs for transactional
tables and the statement is rolled back. For nontransactional tables,
an error occurs, but if this happens for the second or subsequent row
of a multiple-row statement, the preceding rows will have been
inserted.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

If you are not using strict mode (that is, neither STRICT_TRANS_TABLES
nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for
invalid or missing values and produces warnings. In strict mode, you
can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See
Section 12.4.5.37, “SHOW WARNINGS Syntax”.

So, it works as expected if you're in strict mode, which I believe was
only added in version 5.x but please don't quote me on that.  Also, it
was off by default for a really long time (and may still be).


On Thu, Apr 14, 2011 at 10:19 AM, Matija Grabnar <matija at serverflow.com> wrote:
>> If you care about keeping your data consistent, do not use it. You can
>> never rely on MySQL honoring constraints, so you have to do all your data
>> integrity checking in the application. As a trivial demonstration, create a
>> table with a NOT NULL Text column and then insert an empty row. MySQL will
>> happily do as you say, putting an empty string in the Text column. It is
>> possible to bludgeon MySQL into nearly being reliable, but it's not the
>> default behavior and it will vanish at the slightest mistake.
>
> I can see how people can like Postgresql more than MySQL - but this
> statement is really going a bit too far.
>
> Yes, you can put an empty string into a NOT NULL text column. You can also
> put a zero
> into a NOT NULL int column. Both are legitimate values for the type. And no,
> mysql will NOT
> accept a NULL value in a NOT NULL text column.
>
> mysql> create table foo (a text not null);
> Query OK, 0 rows affected (0.05 sec)
> mysql> insert into foo values (NULL);
> ERROR 1048 (23000): Column 'a' cannot be null
> mysql> insert into foo values ('');
> Query OK, 1 row affected (0.00 sec)
>
> There may be areas where Postgresql is better than MySQL, but at various
> times I've found
> MySQL to be vastly faster than Postgresql for the same data: Because I knew
> I would be handling a LOT of data, I benchmarked both with a real-life
> sample of my data and a real-life mix of operations on them. I suggest that,
> if you're serious about picking the right database, you do the same.
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>