Tag Archives: MySQL

ActiveRecord strict mode

I really like ruby and rails, but I find it frustrating as a lone developer.

The main path for Ruby / Rails seems to be very well documented. However, the second I get off the main path, which it seems everyone must do, especially someone who is upgrading an app that started as a rails 2.1.0 application, the documentation is scarce. I find I wind up having to search in source code more often than not to understand what’s going on. And that’s scary.

Case in point, I recently upgraded from rails 3.2 to rails 4.0. They implemented a major change with ActiveRecords that I can find no mention of any where except in the source and change log.

* `mysql` and `mysql2` connections will set `SQL_MODE=STRICT_ALL_TABLES` by
default to avoid silent data loss. This can be disabled by specifying
`strict: false` in your `database.yml`.

This is a good thing. For years i have been silently losing data. Data I don’t care about, but still, since it’s silently loss, I didn’t know.

However, after upgrading the app, fixing all the deprecation warnings, and ensuring all my test passed, I pushed the code to production. Then I started getting errors like:

An ActiveRecord::StatementInvalid occurred in items#search:

Mysql2::Error: Data too long for column ‘name’ at row 1…. (with lots of boring mysql)

Basically, my name field, at 256 characters was not long enough for some data. The error message made it clear what was happening, but I didn’t understand why they would make this type of change, without documenting it in the migration documents. Maybe they could have gone through a deprecation phase where it warned you first, before going from silent to exception?

It wasn’t until after I had fixed my code to either have the proper field lengths, or manually truncate the data myself that I found that a simple “strict: false” in my database.yml file would have returned to the old behavior while I tracked down the problem, or properly checked for it, so I wouldn’t get a server error.

Another more deadly example, one that took me over a month to track down was back in rails 3.1 when they changed the meanings of .dup and .clone.

ActiveRecord::Base#dup and ActiveRecord::Base#clone semantics have changed to closer match normal Ruby dup and clone semantics.
What this really meant to me was, everywhere I was using .clone, I needed to change to .dup. I was having object rot because when It thought I was cloning an item to make a new one, I was changing the original. I needed to .dup it, so I would not copy the id at the time of duplication. Basically, for what I needed, .dup and .clone changed names. This happened silently without warning or being highlighted in the upgrade documentation.

MySQL Database Backup and Restore

To backup or restore a MySQL database, use the following commands:

Backup:

# mysqldump -u db_user -p[password] [database_name] > dump_file.sql

Restore:

# mysql -u db_user -p[password] [database_name] < dump_file.sql

The dump_file.sql file will include all the information necessary to drop and re-create any table contained therein. However, if you are using this dump / restore mechanism to keep a development database in a “as needed” sync with production, it would probably be best to add a step of entering into mysql and dropping / recreating the database. This is because the dump file will not remove any tables in your schema that are not contained in the dump file.

# mysql -u db_user -p
> drop database database_name
> create database database_name