datetime insertion fails

[pbennett]

With Ruby 1.9.2 [or 1.9.3], selecting a datetime value from a datetime column from one table and then trying to insert the exact same fetched value (from a result row) into a table with the same column and type fails with:
d:/ruby/lib/ruby/gems/1.9.1/gems/rdbi-driver-mysql-0.9.2/lib/rdbi/driver/mys
ql.rb:408:in `execute': Incorrect datetime value: '2006-02-09 18:07:27 -0500' for column 'created_at' at row 1
(Mysql::Error)

ie:
rows = db.execute("select * from xxxx where yyy=?, ...", vala, valb).fetch(:all, :Struct)
rows.each do |row|
db.execute("insert into yyy (col_a, col_b, ...) values (?, ?, ?, ...)", row.col_a, row.col_b, xxxx, yyy, ...)
...
end

[pilcrow]

Thanks. What version of RDBI?

May I trouble you for a self-contained test case? (CREATE TEMPORARY TABLE ... SELECT NOW() ... db.execute('INSERT ...')) or similar?

[pbennett]

rdbi (0.9.1)
rdbi-driver-mysql (0.9.2)
talking to a 5.5.16 mysql server.

require 'rdbi'
require 'rdbi-driver-mysql'

db = RDBI.connect(:MySQL, ... )

db.execute("CREATE TEMPORARY TABLE test_a (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
created_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=61431")
db.execute("CREATE TEMPORARY TABLE test_b(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
created_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=61431")

1.upto(10).each do |i|
db.execute("INSERT INTO test_a (created_at) VALUES (NOW())")
end

rows = db.execute("SELECT * FROM test_a").fetch(:all,:Struct)
rows.each do |row|
db.execute("INSERT into test_b (created_at) VALUES (?)", row.created_at)
end

[bsanders1979]

That code worked without issue for me. While I have made modifications to fix minor issues, I did reinstall the stock gems.
ruby 1.9.2p290 (2011-07-09) [i386-mingw32]
MySQL: 5.5.12-55-log (Percona)

[pbennett]

I guess try with 1.9.3p0 - I'll try to remember to try 1.9.2p290 tomorrow but I don't recall it mattering. I'm using the stock gems with exactly what you see and it's never worked.

[pilcrow]

pbennett, please try the latest RDBI from source

[pbennett]

I tried the latest rdbi-driver-myself source and the same thing happens.

[bsanders1979]

Can you paste the entire stacktrace and the code snip which caused the error?

[pbennett]

d:\ionbuilds\team\tools\main\streams\int\src\tests>ruby date_db_test.rb
d:/ionbuilds/team/tools/main/streams/ruby/lib/ruby/gems/1.9.1/gems/rdbi-driver-mysql-0.9.2/lib/rdbi/driver/mys
ql.rb:433:in execute': Incorrect datetime value: '2011-12-19 16:59:09 -0500' for column 'created_at' at row 1 (Mysql::Error) from d:/ionbuilds/team/tools/main/streams/ruby/lib/ruby/gems/1.9.1/gems/rdbi-driver-mysql-0.9.2/lib/rd bi/driver/mysql.rb:433:innew_execution'
from d:/ionbuilds/team/tools/main/streams/ruby/lib/ruby/gems/1.9.1/gems/rdbi-0.9.1/lib/rdbi/statement.
rb:163:in execute' from d:/ionbuilds/team/tools/main/streams/ruby/lib/ruby/gems/1.9.1/gems/rdbi-0.9.1/lib/rdbi/database.r b:189:inexecute'
from date_db_test.rb:23:in block in <main>' from date_db_test.rb:22:ineach'
from date_db_test.rb:22:in `

'

[bsanders1979]

Sounds more like a mysql issue and less like a driver issue. Try this from the mysql shell directly:
create table x(id int(10) unsigned NOT NULL AUTO_INCREMENT, created_at datetime DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
insert into x(created_at) VALUES ('2011-12-19 16:59:09 -0500');
drop table x;

Perhaps this is related?
http://bugs.mysql.com/bug.php?id=60550

[pbennett]

No, not related at all - there are no such problems with dbi. I think it's simply a bad datetime value for mysql. Removing the -0500 from the end and it works fine. It's not a DST issue as changing the time value itself to anything else makes no difference. This is simply an incorrect date/time string for mysql.
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

[bsanders1979]

I think it's simply a bad datetime value for mysql

Not exactly. My instance accepts the value, but ignores the offset.
I tested the statements against a 5.5 and 5.1 instance (both Linux) and neither complained. However, I was able to reproduce the error on my 5.1 Windows instance.

[pbennett]

Well, the documentation at least seems to indicate it's a bad value. I couldn't find a reference for mysql that showed an offset as being allowed in the date/time string. It fails for me against 5.5.12 (on windows)

[pbennett]

any news? rdbi is completely nonfunctional for me right now because of this.

[bsanders1979]

Since it works on Linux, I can't help but wonder if there's some sort of setting in Windows that would allow you to pass the offset, but simply ignore it. IMO, the statement should be portable across platforms. The other option is to modify the code so that the timezone is stripped before attempting to insert You might check some sort of mysql forum about the possibility of tweaking a config variable.

[pbennett]

Again, this works as-is in dbi, and according to the mysql 5.1 and 5.5 documentation isn't supported. There is zero mention of allowing or supporting an offset.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Its properties are described in more detail in Section 10.3.1.1, “TIMESTAMP Properties”.

[pbennett]

Ok, still no response?
RDBI as it is currently is broken for mysql. It sends the wrong format for datetime types plain and simple.

See:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-literals.html
http://dev.mysql.com/doc/refman/5.5/en/datetime.html
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

So rdbi needs to not include the timezone on output to mysql - patching rdbi/types.rb TO_STRING_DATETIME constant like so:
TO_STRING_DATETIME = proc { |obj| obj.strftime("%Y-%m-%d %H:%M:%S") }
seems to work.

I was wrong about UTC for datetime, MySql only stores timestamp columns as UTC. DateTime columns are basically stored as-is - if you store 01-01-2012 10:25:00 in that column, it knows nothing about timezones nor can you specify one. You'll get it back out the same way.

[pilcrow]

I'll take a look.

[bsanders1979]

Seems like the piece of code mysql.rb, line 466: + " #{zone}" should just be removed

[pbennett]

No, then it borks inside the types gem because it insists on a zone offset always being appended to datetime strings. :\