Getting mysql error 1406 when converting from sqlite to mysql

[joseph.garcia@…]

Running the following command:

./trac-migrate.py --in-place <myproject> mysql://<user>:<password>@<mysqlserver>:3306/trac

Keep getting a mysql 1406 error even though the max_allowed_packet is set to 1GB

Copying tables:
  attachment table... 426 records.
  auth_cookie table... 74 records.
  cache table... 11 records.
  component table... 19 records.
  dir_cache table... 67 records.
  enum table... 15 records.
  milestone table... 32 records.
  node_change table... 6623 records.
  permission table... 37 records.
  report table... 17 records.
  repository table... 47 records.
  revision table... 1824 records.
  session table... 13365 records.
  session_attribute table... 26983 records.
  system table... 3 records.
  ticket table... 1401 records.
  ticket_change table... Traceback (most recent call last):
  File "./trac-migrate.py", line 55, in <module>
    sys.exit(main(sys.argv[1:]) or 0)
  File "./trac-migrate.py", line 51, in main
    return TracMigrationCommand(env)._do_migrate(dest, dburi)
  File "/tmp/blah/tracmigrateplugin/0.12/tracmigrate/admin.py", line 55, in _do_migrate
    return self._do_migrate_inplace(dburi)
  File "/tmp/blah/tracmigrateplugin/0.12/tracmigrate/admin.py", line 89, in _do_migrate_inplace
    self._copy_tables(src_db, dst_db, src_dburi, dburi, inplace=True)
  File "/tmp/blah/tracmigrateplugin/0.12/tracmigrate/admin.py", line 212, in _copy_tables
    count = copy_table(dst_db, cursor, table)
  File "/tmp/blah/tracmigrateplugin/0.12/tracmigrate/admin.py", line 202, in copy_table
    sum(rows, ()))
  File "/usr/local/lib64/python2.6/site-packages/Trac-1.0.11-py2.6.egg/trac/db/util.py", line 72, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.DataError: (1406, "Data too long for column 'newvalue' at row 88")

[joseph.garcia@…]

Trac is version 1.0.11

It looks like all the tables are being created with type text which has a 255 char limit.

+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| ticket   | int(11)    | NO   | PRI | 0       |       |
| time     | bigint(20) | NO   | PRI | 0       |       |
| author   | text       | YES  |     | NULL    |       |
| field    | text       | NO   | PRI | NULL    |       |
| oldvalue | text       | YES  |     | NULL    |       |
| newvalue | text       | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

[Jun Omae]

Mysql TEXT is 65535 characters limit.

[Jun Omae]

See also:

• ​trac:#8396
• ​http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

[Ryan J Ollos]

In light of ​trac:#8396, it sounds like the issue could be resolved by upgrading to Trac 1.2 before the migration.