Edgewall Software
Modify

Opened 15 years ago

Closed 15 years ago

Last modified 12 years ago

#3884 closed defect (fixed)

MySQL error on initenv

Reported by: Fisher.Jason.R@… Owned by: Jonas Borgström
Priority: normal Milestone: 0.10.4
Component: general Version: 0.10
Severity: normal Keywords: mysql documentation
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description (last modified by Christian Boos)

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
    

Attachments (0)

Change History (11)

comment:1 by Christian Boos, 15 years ago

Description: modified (diff)
Keywords: mysql added
Milestone: 0.10.1

comment:2 by kweigold@…, 15 years ago

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

comment:3 by Christian Boos, 15 years ago

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

comment:4 by kweigold@…, 15 years ago

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

comment:5 by ericanderton@…, 15 years ago

(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'")

comment:6 by so1o, 15 years ago

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!

comment:7 by anonymous, 15 years ago

To finalize.

CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

comment:8 by Christian Boos, 15 years ago

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?

comment:9 by Christian Boos, 15 years ago

Keywords: documentation added
Resolution: fixed
Status: newclosed

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.

comment:10 by anatoly techtonik <techtonik@…>, 12 years ago

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.

comment:11 by anatoly techtonik <techtonik@…>, 12 years ago

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)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jonas Borgström.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jonas Borgström to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.