Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.xs4all.nl!newsfeed4a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
In-Reply-To: <85a9087y9c.fsf_-_@benfinney.id.au>
References: <mc1atd$cq0$1@ger.gmane.org> <mailman.18805.1424243139.18130.python-list@python.org> <mc1s1e$feh$1@news.albasani.net> <mailman.18815.1424258499.18130.python-list@python.org> <4154cc37-0bb0-4bf2-a52c-b728c737357c@googlegroups.com> <CAPTjJmogRj_==1wUcLooFwDhUK+saSaAkQEuUo4o30n8aRdB=w@mail.gmail.com> <54E517B4.4000409@stoneleaf.us> <mc37el$ci7$1@ger.gmane.org> <mailman.18844.1424304273.18130.python-list@python.org> <87a908e0zh.fsf@jester.gateway.pace.com> <CAPTjJmpzpjkXtnS=dbNR2WK2WyEzn2T4rDkeF23c4Ypqww08VA@mail.gmail.com> <85a9087y9c.fsf_-_@benfinney.id.au>
Date: Sat, 21 Feb 2015 16:39:03 +1100
Subject: Re: 'Lite' Databases
From: Chris Angelico <rosuav@gmail.com>
Cc: "python-list@python.org" <python-list@python.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.18946.1424497146.18130.python-list@python.org>
Lines: 72
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:86014

On Sat, Feb 21, 2015 at 2:13 PM, Ben Finney <ben+python@benfinney.id.au> wr=
ote:
> In addition, MySQL silently [0] loses data in many common situations.
>
>     <URL:https://dev.mysql.com/doc/refman/5.6/en/silent-column-changes.ht=
ml>
>     <URL:http://www.davidpashley.com/2009/02/15/silently-truncated/>
>     <URL:http://effectivemysql.com/downloads/MySQLIdiosyncrasiesThatBITE.=
pdf>
>

Right, I'd forgotten about silent truncations. Though to be fair, the
first link refers to some changes which aren't a problem:

> Columns that are part of a PRIMARY KEY are made NOT NULL even if not decl=
ared that way.

The requirement that a PK consist of non-nullable columns is part of
other databases, too, and having the words "PRIMARY KEY" imply "NOT
NULL PRIMARY KEY" is fine IMO. Back when I used DB2 version 5, I used
to use "ID SMALLINT NOT NULL PRIMARY KEY" at the beginning of most of
my table definitions, and all this "silent change" does is allow you
to abbreviate that.

> Trailing spaces are automatically deleted from ENUM and SET member values=
 when the table is created.

Which means they're built on top of CHAR columns, not VARCHAR. Not a
huge deal IMO, though definitely something to be aware of.

Some of the other issues are more concerning, but these two don't
bother me, anyway.

The other two links, though? Definitely problems. Silent truncation of
data is a pest, and it gets even worse than that. I tried to put
together a test-case to see if I could do the classic "break two
UTF-16 strings and construct a brand new character out of them" trick,
but found something even worse.

# -*- encoding: UTF-8 -*-
from __future__ import print_function
import MySQLdb
# If you don't explicitly say charset=3D"UTF8", you get some default that y=
ou may
# not be able to trust. On my test box, it gave me Latin-1. Plus, you can't=
 say
# charset=3D"UTF-8" - you have to omit the hyphen. Not good, but not a gotc=
ha as
# you get an immediate exception.
con =3D MySQLdb.connect("localhost","demo","demo","demodb",charset=3D"UTF8"=
)
cur =3D con.cursor()
# Minor nastiness: This produces a warning if the table doesn't exist.
# So automatically displaying warnings will produce annoying noise.
cur.execute("drop table if exists nasty")
# Don't forget, "utf8" doesn't mean UTF-8... no, you have to say "utf8mb4"!
cur.execute("create table nasty (id smallint primary key, payload
char(8) charset utf8mb4 not null)")
strings =3D (u"English: safe", u"=CE=B5=CE=BB=CE=BB=CE=B7=CE=BD=CE=B9=CE=BA=
=CE=AC: safe", u"\U0001F4A9: accurate")
cur.execute("insert into nasty values (1, %s), (2, %s), (3, %s)", strings)
cur.execute("select payload from nasty order by id")
print("Input\t\tOutput")
for input, output in zip(strings, cur):
    print(input,output[0],sep=3D"\t")

Your terminal may or may not be able to display U+1F4A9, but it's an
accurate description of MySQL's handling of astral characters in this
demo. I don't even know what's going on here. Tested on MySQL 5.5 on
Debian Wheezy.

ChrisA