MySQL error on initenv

[Fisher.Jason.R@…]

I keep getting this error everytime I try and initialize an environment.

I'm using

• trac v0.10,
• mysql v5.022,
• Apache 2.0.59,
• Python 2.4

   C:\webserver\Python24\share\trac\wiki-default\TracFastCgi => TracFastCgi
  Failed to initialize environment. (1406, "Data too long for column 'text' at row
   0")
  Traceback (most recent call last):
    File "C:\webserver\Python24\Lib\site-packages\trac\scripts\admin.py", line 621
  , in do_initenv
      self._do_wiki_load(default_dir('wiki'), cursor)
    File "C:\webserver\Python24\Lib\site-packages\trac\scripts\admin.py", line 814
  , in _do_wiki_load
      self._do_wiki_import(filename, page, cursor, create_only)
    File "C:\webserver\Python24\Lib\site-packages\trac\scripts\admin.py", line 784
  , in _do_wiki_import
      cursor, (title, int(time.time()), data, title))
    File "C:\webserver\Python24\Lib\site-packages\trac\scripts\admin.py", line 162
  , in db_update
      cursor.execute(sql, params)
    File "C:\webserver\Python24\Lib\site-packages\trac\db\util.py", line 47, in ex
  ecute
      return self.cursor.execute(sql_escape_percent(sql), args)
    File "C:\webserver\Python24\Lib\site-packages\trac\db\util.py", line 47, in ex
  ecute
      return self.cursor.execute(sql_escape_percent(sql), args)
    File "C:\webserver\Python24\Lib\site-packages\MySQLdb\cursors.py", line 163, i
  n execute
      self.errorhandler(self, exc, value)
    File "C:\webserver\Python24\Lib\site-packages\MySQLdb\connections.py", line 35
  , in defaulterrorhandler
      raise errorclass, errorvalue
  OperationalError: (1406, "Data too long for column 'text' at row 0")
  }}
  
  Thanks
  
  Jason Fisher
  
  Fisher.Jason.R@gmail.com
  

[kweigold@…]

As far as i can see the python handler for mysql say they work up to version 4 of mysql not version 5. That could be your issue. you might want to try to use version 4 of mysql

[cboos]

Well, I'm using the MySQLdb 1.2.1_p2 bindings and the MySQL database used is at version 5.0.24a-community-nt, with no problems (I used a UTF-8 charset when creating it).

Here's my "status":

mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe  Ver 14.12 Distrib 5.0.24a, for Win32 (ia32)

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.24a-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 day 13 hours 46 min 35 sec

[kweigold@…]

Did mysql change there default charset between version 4 and 5 possibly

[ericanderton@…]

(Using 5.0.24a-community-nt)

I was able to install by not using the default collation on my database. I selected UTF-8 instead:

CREATE DATABASE `trac` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

The next stumbling bloc was in trac/env.py, where the system table is queried for the 'database_version'. MYSQL wants to return this as a BLOB, which causes the int() conversion on line 238 to fail. I found the following patch to do the trick, although there may be a more elegant way to fix this:

# trac/env.py line 236
        cursor.execute("SELECT cast(value as char(20)) FROM system WHERE name='database_version'")

[so1o]

I ran into this problem too..

if you use COLLATE utf8_bin; to create the database we will always endup with a blob. so i created a utf8_general collated database and i didnt even have to make the code change!

walla!

[anonymous]

To finalize.

CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

[cboos]

Ok, so jonas and me tend to agree that perhaps we shouldn't try to support every exotic charset and collations out there, but rather require a specific one, which might well be the one you described above.

I have the same, so to complement the information given in comment:3:

mysql> select collation(_utf8'abc');
+-----------------------+
| collation(_utf8'abc') |
+-----------------------+
| utf8_general_ci       |
+-----------------------+

So as Trac makes heavy use of unicode, I propose that we require to use this kind of database, with charset = utf8 and collation = utf8_general_ci .

Comments?

[cboos]

Well, given the lack of patches and other expertise about the MySQL, I think it's about time to make the use of the utf8 charset and utf8_general_ci an "official" requirement...

This is now documented in the MySqlDb, DatabaseBackend and TracInstall pages.

[anatoly techtonik <techtonik@…>]

utf8_general_ci uses case insensitive comparison as the name implies. This causes problems with unique keys, such as path column in node_change table. See #9031.

Quick example for future reference:

mysql> CREATE TABLE t (c TEXT CHARACTER SET utf8, PRIMARY KEY (c(255)));
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` text character set utf8 NOT NULL,
  PRIMARY KEY  (`c`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> SHOW FULL COLUMNS FROM `t`;
+-------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| c     | text | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
+-------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO `t` VALUES ('case_string'), ('casE_string');
ERROR 1062 (23000): Duplicate entry 'casE_string' for key 1

The choice here is either utf8_bin or utf8_general_cs.

[anatoly techtonik <techtonik@…>]

It appears that MySQL doesn't support utf8_general_cs, so utf8_bin is the only choice.

mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
...
| utf8_general_ci      | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin             | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci      | utf8     | 192 |         | Yes      |       8 |
| utf8_icelandic_ci    | utf8     | 193 |         | Yes      |       8 |
...
| binary               | binary   |  63 | Yes     | Yes      |       1 |
...
+----------------------+----------+-----+---------+----------+---------+
126 rows in set (0.01 sec)