MySQL: Try to store 4 byte character in a 3 Byte UTF8-Field

[andygrunwald]

If you execute:

/mlstats --no-report --db-driver 'mysql' --db-hostname 'localhost' --db-user 'root' --db-password '' --db-name 'mlstats' 'http://lists.typo3.org/pipermail/typo3-ug-denmark/'

you will get

Analyzing /Users/agrunwald/.mlstats/compressed/lists.typo3.org/pipermail/typo3-ug-denmark/2006-October.txt.gz
Analyzing /Users/agrunwald/.mlstats/compressed/lists.typo3.org/pipermail/typo3-ug-denmark/2006-November.txt.gz
Analyzing /Users/agrunwald/.mlstats/compressed/lists.typo3.org/pipermail/typo3-ug-denmark/2006-December.txt.gz
Analyzing /Users/agrunwald/.mlstats/compressed/lists.typo3.org/pipermail/typo3-ug-denmark/2007-January.txt.gz
Traceback (most recent call last):
  File "./mlstats", line 38, in <module>
    pymlstats.start()
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/__init__.py", line 166, in start
    quiet, force, web_user, web_password)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 173, in __init__
    t, s, np = self.__analyze_mailing_list(mailing_list)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 225, in __analyze_mailing_list
    total, stored, non_parsed = self.__analyze_list_of_files(mailing_list, archives_to_analyze)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 393, in __analyze_list_of_files
    mailing_list.location)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/db/session.py", line 154, in store_messages
    self.insert_people(name, email)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/db/session.py", line 81, in insert_people
    self.session.commit()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 768, in commit
    self.transaction.commit()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 370, in commit
    self._prepare_impl()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 350, in _prepare_impl
    self.session.flush()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 1907, in flush
    self._flush(objects)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 2025, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Library/Python/2.7/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 1989, in _flush
    flush_context.execute()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 371, in execute
    rec.execute(self)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 524, in execute
    uow
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    mapper, table, insert)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 568, in _emit_insert_statements
    execute(statement, multiparams)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 727, in execute
    return meth(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 824, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 954, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1116, in _handle_dbapi_exception
    exc_info
  File "/Library/Python/2.7/site-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
  File "/Library/Python/2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/Library/Python/2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1366, "Incorrect string value: '\\xE6\\xF8\\xE5' for column 'email_address' at row 1") 'INSERT INTO people (email_address, name, username, domain_name, top_level_domain) VALUES (%s, %s, %s, %s, %s)' ('none@none.\xe6\xf8\xe5', 'Lars Bonnesen', 'none', 'none.\xe6\xf8\xe5', '\xe6\xf8\xe5')

This is because you cannot store 4-byte characters in MySQL with the utf-8 character set.
Since MySQL 5.5 4-Byte UTF-8 Unicode Encoding is supported.
See for detail information:

• 10.1.10.5 The utf8 Character Set (3-Byte UTF-8 Unicode Encoding)
• 10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

Here is a related django ticket: Use utf8mb4 encoding with MySQL 5.5.

Solution:
Use utf8mb4 charset for MySQL if the used mysql server supports this.

[andygrunwald]

If i switch the mysql_charset with a MySQL-Server 5.6 another message occur:

Traceback (most recent call last):
  File "./mlstats", line 38, in <module>
    pymlstats.start()
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/__init__.py", line 166, in start
    quiet, force, web_user, web_password)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 149, in __init__
    Database.create_tables(engine, checkfirst=True)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/db/session.py", line 50, in create_tables
    db.Base.metadata.create_all(engine, checkfirst=checkfirst)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/schema.py", line 3317, in create_all
    tables=tables)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1555, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1202, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 119, in traverse_single
    return meth(obj, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/ddl.py", line 709, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 119, in traverse_single
    return meth(obj, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/ddl.py", line 728, in visit_table
    self.connection.execute(CreateTable(table))
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 727, in execute
    return meth(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/ddl.py", line 67, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 781, in _execute_ddl
    compiled
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 954, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1116, in _handle_dbapi_exception
    exc_info
  File "/Library/Python/2.7/site-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
  File "/Library/Python/2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/Library/Python/2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes') '\nCREATE TABLE mailing_lists (\n\tmailing_list_url VARCHAR(255) NOT NULL, \n\tmailing_list_name VARCHAR(255), \n\tproject_name VARCHAR(255), \n\tlast_analysis DATETIME, \n\tPRIMARY KEY (mailing_list_url)\n)ENGINE=InnoDB CHARSET=utf8mb4\n\n' ()

To fix this we have to get rid of the Strings as (Primary) Keys.
This seems to be related to #48

[gpoo]

It seems to me a limitation in MySQL. I find #48 would make things more complex without gain.

[gpoo]

Indeed, it's a limitation in MySQL: See http://bugs.mysql.com/bug.php?id=4541
or the documentation in http://dev.mysql.com/doc/refman/5.1/en/create-index.html (emphasis mine):

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

[gpoo]

Possible solutions:

• Make the columns shorter (e.g. mailing_list_url(191) instead of 255)
• Use partial indexes (i.e. don't use the whole column, but a subset of each column, like 191 out of 255)

Possibly 191 is still quite long for an URL. Why 192, because 791/4=191.75.

It's kind of funny --and sad at the same time--, that here there are exposed 2 limitations in MySQL: MySQL calling something UTF8 when it was not (or a subset of UTF8), and then the limitation on bytes used for indexes.

[andygrunwald]

Thanks @gpoo for having a look.
Regarding #48 i will prepare some benchmarks about this. But i agree with you that this is a more complex thing.

Of course, i this limitation is really bad :(

At the moment i prefer to shorten the mailing_list_url column to 191 chars, because this is a really fast and pragmatic solution.
If this is okay for you i will do this.

BUT there is another thing. If we switch to utf8mb4 we have to raise the minimum requirement for MySQL to version 5.5.3. In lower versions utf8mb4 is not supported.

[gpoo]

Shortening the column 191 seems fine to me.

5.5.3 was released on March 24th, 2010. It seems safe to bump the requirement. However, it might be necessary to add a note in the README or somewhere else, to point out how to upgrade the unicode support in already existing tables. Like http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

I don't know if @sduenas has a different opinion, though.

[andygrunwald]

I`ve checked some Linux / Unix distris:

• Debian:
  • squeeze (oldstable): 5.1.73-1
  • wheezy (stable): 5.5.38-0+wheezy1
  • jessie (testing): 5.5.39-1
  • sid (unstable): 5.5.39-1
• FreeBSD: 5.5.39
• Ubuntu:
  • Lucid: 5.1.73-0ubuntu0.10.04.1
  • Precise, raring, Saucy, Trusty, Utopic: >= 5.5.34-0ubuntu0.13.04
• Redhat: 5.5.39

So i think this is enough to prove that MySQL 5.5.3 can be set as a requirement.
Currently i am working on a solution.
See here: https://github.com/andygrunwald/MailingListStats/commit/3ff9d1241804992f7dc760c3a7d9893bb536606b

But until now i was not successful.
I am fighting with an
sqlalchemy.exc.OperationalError: (OperationalError) (1054, "Unknown column 'mailing_list_url' in 'field list'") 'INSERT INTO messages_people (type_of_recipient, message_id, mailing_list_url, email_address) VALUES (%s, %s, %s, %s)' ('From', '<mailman.16.1085344651.25321.typo3-ug-denmark@lists.netfielders.de>', 'http://lists.typo3.org/pipermail/typo3-ug-denmark', 'mundaun@gmx.ch') error.
But maybe the next days :)

[andygrunwald]

I created a PR (#52) for this, because i finally fixed this for MySQL.
Afaik PostgreSQL is not affected. Can you please have a look at it ? :)

I tried to tested this with PostgreSQL as well, but i failed, but the same error message occur with master as well
The current master seems not to work with PostgreSQL 9.3:

`$ ./mlstats --db-driver 'postgres' --db-hostname 'localhost' --db-user 'operator' --db-password '' --db-name 'mlstats' 'https://lists.libresoft.es/pipermail/metrics-grimoire/'``

Retrieving https://lists.libresoft.es/pipermail/metrics-grimoire/2006-December.txt.gz...
...
Retrieving https://lists.libresoft.es/pipermail/metrics-grimoire/2014-July.txt...
Analyzing /Users/agrunwald/.mlstats/compressed/lists.libresoft.es/pipermail/metrics-grimoire/2006-December.txt.gz
   ***WARNING: 2 messages (out of 4) parsed but not stored (2 duplicate, 0 errors)***
Analyzing /Users/agrunwald/.mlstats/compressed/lists.libresoft.es/pipermail/metrics-grimoire/2007-January.txt.gz
Traceback (most recent call last):
  File "./mlstats", line 38, in <module>
    pymlstats.start()
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/__init__.py", line 166, in start
    quiet, force, web_user, web_password)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 173, in __init__
    t, s, np = self.__analyze_mailing_list(mailing_list)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 225, in __analyze_mailing_list
    total, stored, non_parsed = self.__analyze_list_of_files(mailing_list, archives_to_analyze)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/main.py", line 393, in __analyze_list_of_files
    mailing_list.location)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/db/session.py", line 155, in store_messages
    self.insert_people(name, email)
  File "/Users/agrunwald/Development/MailingListStats.git/pymlstats/db/session.py", line 81, in insert_people
    self.session.commit()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 768, in commit
    self.transaction.commit()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 370, in commit
    self._prepare_impl()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 350, in _prepare_impl
    self.session.flush()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 1907, in flush
    self._flush(objects)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 2025, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Library/Python/2.7/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/session.py", line 1989, in _flush
    flush_context.execute()
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 371, in execute
    rec.execute(self)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 524, in execute
    uow
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    mapper, table, insert)
  File "/Library/Python/2.7/site-packages/sqlalchemy/orm/persistence.py", line 568, in _emit_insert_statements
    execute(statement, multiparams)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 727, in execute
    return meth(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 824, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 954, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1119, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe1' in position 21: ordinal not in range(128)

[sduenas]

@andygrunwald, sorry to be late on this. I think to short the indexes to 191 characters is a good solution. My only concern is with message_ID field but I think it will be extremely rare to find something longer than 191.

[andygrunwald]

Thanks @sduenas.
I understand your concern.
To shorten the message id for messages_people.message_id and messages.message_ID is necessary, because both fields were used as index (foreign key + primary key).

I had a look at RFC 2822. Sadly there is no max. length defined of this field.

I suggest we will merge this change. 191 character is a lot of space.
If some mailinglist needs more space, we will have a look how to fix this, but we are not sure if this is a real case.

[andygrunwald]

Mailinglisten post: https://lists.libresoft.es/pipermail/metrics-grimoire/2014-September/002389.html