Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

MySQL running on ANSI QUOTES fails insert (Regression 3.1.3 -> 3.2.1) #4754

Closed
seantanly opened this Issue Jan 30, 2012 · 6 comments

Comments

Projects
None yet
2 participants

Tested this on a fresh Rails 3.2.1 installation

When connecting to a MySQL 5.5 server running on ANSI_QUOTES mode, specifically having this in my.cnf

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"

ActiveRecord is unable to insert any new records.

Running this on Rails 3.1.3 console

AppConfig.create!(ckey: 'a', cvalue: 'b')
   (0.2ms)  BEGIN
  SQL (0.4ms)  INSERT INTO `app_configs` (`ckey`, `cvalue`) VALUES ('a', 'b')
   (0.7ms)  COMMIT
 => #<AppConfig id: 1, ckey: "a", cvalue: "b">

Running this on Rails 3.2.1 console

AppConfig.create!(ckey: 'a', cvalue: 'b')
   (0.3ms)  BEGIN
  SQL (0.5ms)  INSERT INTO `app_configs` (`ckey`, `cvalue`, `id`) VALUES ('a', 'b', NULL)
   (0.7ms)  ROLLBACK
ActiveModel::MissingAttributeError: can't write unknown attribute `"id"'

When I removed ANSI_QUOTES from the my.cnf, the Rails 3.2.1 console produces the same output as 3.1.3, thus successful in row insertion.

Both installation uses gem mysql2 (0.3.11)

Contributor

kennyj commented Jan 30, 2012

I can reproduce this issue, and I realize the cause of this problem.
We have another response, when we execute "SHOW CREATE TABLE".

■sql_mode = 'ANSI_QUOTES'

CREATE TABLE "posts" (
  "id" int(11) NOT NULL auto_increment,
  "title" varchar(255) collate utf8_unicode_ci default NULL,
  "body" varchar(255) collate utf8_unicode_ci default NULL,
  "created_at" datetime NOT NULL,
  "updated_at" datetime NOT NULL,
  PRIMARY KEY  ("id")
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

■sql_mode is not set.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) collate utf8_unicode_ci default NULL,
  `body` varchar(255) collate utf8_unicode_ci default NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Quoting character is not same. As a result, it seems that this issue occur by missing primary key.
AR recognize id column as normal column.

BTW, when we executed rails's testcases with ANSI_QUOTES, many testcases were failed.

It seems that this issue is in 3-1, 3-2, and master.

kennyj added a commit to kennyj/rails that referenced this issue Jan 30, 2012

Contributor

kennyj commented Jan 30, 2012

I think that I fixed this issue, and I'll send PR.

Contributor

kennyj commented Jan 30, 2012

@seantan Could you confirm my commit ? I think that I fixed this problem.

cc/ @jonleighton @tenderlove

@kennyj I applied your patch on my local rails 3.2.1 installation and that seems to have fixed the problem. =)

Contributor

kennyj commented Jan 31, 2012

@seantan Thank you for confirmation !

I've sent pull request already :-)
#4763

jonleighton added a commit that referenced this issue Jan 31, 2012

Merge pull request #4763 from kennyj/fix_4754
[MySQL] Fix GH #4754. Remove double-quote characters around PK when using sql_mode=ANSI_QUOTES

jonleighton added a commit that referenced this issue Jan 31, 2012

Merge pull request #4763 from kennyj/fix_4754
[MySQL] Fix GH #4754. Remove double-quote characters around PK when using sql_mode=ANSI_QUOTES
Contributor

kennyj commented Jan 31, 2012

I'm closing this issue, because the PR (4763) was merged.
Thanks !

@kennyj kennyj closed this Jan 31, 2012

kennyj added a commit to kennyj/rails that referenced this issue Jan 31, 2012

jonleighton added a commit that referenced this issue Jan 31, 2012

Merge pull request #4787 from kennyj/fix_4754-2
[Backport][3-1-stable] Fix GH #4754. Remove double-quote characters around PK when using sql_mode=ANSI_QUOTES
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment