#744 JSon and Yaml types try to create VARCHAR(65535) in do_mysql-0.9.10.1 - DataMapper

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

#744 ✓resolved

JSon and Yaml types try to create VARCHAR(65535) in do_mysql-0.9.10.1

Reported by Felix Sun | January 4th, 2009 @ 08:07 PM



/opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/adapters/data_objects_adapter.rb:92:in `execute_non_query': (mysql_errno=1074, sql_state=42000) Column length too big for column 'setting_json' (max = 21845); use BLOB or TEXT instead (MysqlError)
Query: CREATE TABLE `settings` (`id` INT NOT NULL AUTO_INCREMENT, `language_id` INT(11), `setting_key` VARCHAR(50), `setting_value` VARCHAR(50), `setting_json` VARCHAR(65535) DEFAULT NULL, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci  from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/adapters/data_objects_adapter.rb:92:in `execute'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/adapters/data_objects_adapter.rb:173:in `with_connection'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/adapters/data_objects_adapter.rb:90:in `execute'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/adapters/data_objects_adapter.rb:456:in `create_model_storage'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/auto_migrations.rb:88:in `auto_migrate_up!'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/model.rb:113:in `repository'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core.rb:181:in `repository'
        from /opt/local/lib/ruby/gems/1.8/gems/dm-core-0.9.8/lib/dm-core/repository.rb:44:in `scope'

Comments and changes to this ticket

Dan Kubb (dkubb) January 4th, 2009 @ 11:36 PM
- State changed from “unconfirmed” to “hold”
Felix, what version of MySQL are you using?
You flagged this item as spam.
Felix Sun January 5th, 2009 @ 12:15 AM
Hi Dan

It works ok with do_mysql 0.9.9, here is my version
```
Felix:~ sunfmin$ mysql --version
mysql  Ver 14.12 Distrib 5.0.51, for apple-darwin8.11.1 (i686) using  EditLine wrapper
```
Dan Kubb (dkubb) January 5th, 2009 @ 03:16 PM
- State changed from “hold” to “confirmed”
I believe the reason for this problem is that we're using utf-8 encoded characters, which take up more space than latin1 characters.

Note that the error message includes "max = 21845", well 21845 * 3 == 65535

The new do_mysql is stricter, and instead of previously silently truncating the field it throws an exception.. which I believe is the correct approach in this case.
You flagged this item as spam.
phatmann January 5th, 2009 @ 03:19 PM
This issue also applies to Yaml properties. The fix would be to use a TEXT field instead of a VARCHAR, by changing the primitive type to Text.

Per dkubb, I will (1) see what the performance and storage implications of this would be and (2) fork and make a pull request.
You flagged this item as spam.
phatmann January 5th, 2009 @ 03:26 PM
- Title changed from “JSon type try to create VARCHAR(65535) in do_mysql-0.9.10.1” to “JSon and Yaml types try to create VARCHAR(65535) in do_mysql-0.9.10.1”
You flagged this item as spam.
phatmann January 5th, 2009 @ 05:22 PM
Patch submitted:

http://github.com/phatmann/dm-mo...

Changed yaml.rb, csv.rb, and json.rb to use Text instead of String.

On MySQL5, a TEXT field is more or less the same as a VARCHAR at max length, with some minor differences (see http://dev.mysql.com/doc/refman/.... So this seems like the appropriate type for Yaml, Json, and CSV. I imagine it will also be appropriate for other dbs, but I did not research this.
You flagged this item as spam.
phatmann January 5th, 2009 @ 05:36 PM
In case you want the patch, it is attached.
- Using-Text-instead-of-String-fields.patch 1.5 KB
Dan Kubb (dkubb) January 8th, 2009 @ 02:41 PM
- State changed from “confirmed” to “resolved”
- Assigned user set to “Dan Kubb (dkubb)”
This patch was merged in:

http://github.com/sam/dm-more/co...

Marking this ticket as resolved for now.

Felix, could you try installing edge dm-types and see if the problem is fixed for you? If not, please comment here and I will reopen the ticket.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.