Page 1 of 2

Unable to convert MySQL date/time value

Posted: Fri Jul 29, 2011 6:45 am
by gkhairallah
I'm having a problem with running queries against a MySQL database. the DB is for the KACE appliance, it's Read Only. When I connect to the database, and run any query, the log states:"Unable to convert MySQL date/time value to System.DateTime". Just for completeness sake, the query is very simple:SELECT * FROM MACHINE; If I connect to the same database using MySQL Workbench, the query works fine. If I connect to an MS-SQL database on PrimalSQL, the query works fine. I'm not really sure what may be causing this error on this particular database. Running Windows 7 Pro 32Bit Primal SQL 2011 Version 2.0.6 Any thoughts?

Unable to convert MySQL date/time value

Posted: Fri Jul 29, 2011 7:12 am
by gkhairallah
As a follow up, I was trying to go to edit the database, to see if there is anything that I can change. (I saw a suggestion to add Allow Zero DateTime=True to the connection string)... and instead, I get this error everytime I try to edit the db connection: See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.************** Exception Text **************System.NullReferenceException: Object reference not set to an instance of an object. at (Object ) at ..

Unable to convert MySQL date/time value

Posted: Fri Jul 29, 2011 7:34 am
by davidc
We are looking into the issues. David

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 4:53 am
by davidc
I can't seem to reproduce the DateTime issue. How are you connecting to the MySQL Database? ODBC or are you using MySQL settings? Can you also provide a basic schema for the table? The Edit Connection issue will be resolved in the next service release. Thank you, David

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 5:29 am
by gkhairallah
I am using a MySQL connection string, not an ODBC connection.
Here's a table schema where I'm getting the error:

'USER_NAME', 'varchar(255)', 'YES', '', 'empty', ''
'USER_DOMAIN', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_NAME', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_VERSION', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_MANUFACTURER', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_DESCRIPTION', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_IDENTIFICATION_CODE', 'varchar(255)', 'YES', '', 'empty', ''
'BIOS_SERIAL_NUMBER', 'varchar(255)', 'YES', '', 'empty', ''
'MOTHERBOARD_PRIMARY_BUS', 'varchar(255)', 'YES', '', 'empty', ''
'MOTHERBOARD_SECONDARY_BUS', 'varchar(255)', 'YES', '', 'empty', ''
'PROCESSORS', 'text', 'YES', '', NULL, ''
'SOUND_DEVICES', 'text', 'YES', '', NULL, ''
'CDROM_DEVICES', 'text', 'YES', '', NULL, ''
'VIDEO_CONTROLLERS', 'text', 'YES', '', NULL, ''
'MONITOR', 'text', 'YES', '', NULL, ''
'REGISTRY_SIZE', 'varchar(255)', 'YES', '', 'empty', ''
'REGISTRY_MAX_SIZE', 'varchar(255)', 'YES', '', 'empty', ''
'PAGEFILE_SIZE', 'varchar(255)', 'YES', '', 'empty', ''
'PAGEFILE_MAX_SIZE', 'varchar(255)', 'YES', '', 'empty', ''
'PRINTERS', 'text', 'YES', '', NULL, ''
'KUID', 'varchar(255)', 'YES', 'UNI', NULL, ''
'MANUAL_ENTRY', 'tinyint(1) unsigned', 'NO', '', '0', ''
'OS_ARCH', 'varchar(32)', 'YES', '', NULL, ''
'FORCE_INVENTORY', 'tinyint(1) unsigned', 'NO', '', '0', ''
'CUSTOM_FIELD_VALUE0', 'varchar(255)', 'YES', '', NULL, ''
'CUSTOM_FIELD_VALUE1', 'varchar(255)', 'YES', '', NULL, ''
'CUSTOM_FIELD_VALUE2', 'varchar(255)', 'YES', '', NULL, ''
'CUSTOM_FIELD_VALUE3', 'varchar(255)', 'YES', '', NULL, ''
'CUSTOM_FIELD_VALUE4', 'varchar(255)', 'YES', '', NULL, ''
'CUSTOM_FIELD_VALUE5', 'varchar(255)', 'YES', '', NULL, ''
'INVENTORY_STARTED', 'datetime', 'YES', 'MUL', NULL, ''
'CLIENT_VERSION', 'varchar(255)', 'YES', '', '', ''
'CONNECT_TIME', 'timestamp', 'NO', '', '0000-00-00 00:00:00', ''
'DISCONNECT_TIME', 'timestamp', 'NO', '', '0000-00-00 00:00:00', ''
'SMMP_VERSION', 'varchar(10)', 'YES', '', NULL, ''
'PATCHLINK_LANGUAGE_ID', 'bigint(20)', 'NO', '', '1', ''
'LAST_SHUTDOWN', 'varchar(255)', 'YES', '', 'empty', ''
'CHASSIS_TYPE', 'varchar(255)', 'YES', '', 'empty', ''
'TZ_AGENT', 'varchar(255)', 'YES', '', NULL, ''

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 6:00 am
by davidc
Can you isolate the issue to a specific field? I the mean time I will try to recreate some of the columns. Thank you, David

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 6:03 am
by gkhairallah
Yes. Actually, it's any field of type "datetime"
As a matter of fact, every table which has a field of this type fails on those types of fields in PrimalSQL. (in MySQL Workbench, there are no issues)

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 8:31 am
by davidc
Ok the issue is in the ADO .NET provider, where it cannot converting '0000-00-00 00:00:00' into DateTime object. To resolve this you will need to either format the Date: DATE_FORMAT(datefield, '%m/%d/%Y') AS datefield or add the following to the connection string: Allow Zero DateTime=True I will issue another service build that will correctly display the date after adding Allow Zero DateTime= True to the connection string.

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 8:34 am
by davidc
For now I
recommend formatting the query.David

Unable to convert MySQL date/time value

Posted: Tue Aug 02, 2011 8:42 am
by gkhairallah
Thanks for the response David,
I had seen both of these solutions, and I was able to get a successful result by reformatting the date. The problem with this, is that I often run quick select * from MACHINE WHERE ... and having to include every single datetime type field in a format function would take 5 times the time for that quick query.

Is there any way, perhaps as a workaround, to append the Allow Zero DateTime=True into the connection string for the PrimalSQL DB connection? even if I have to edit the registry.

This solution will effectively drive me away from using PrimalSQL for my queries, as it's entirely not functional for day to day quick tasks. (Almost every table within that database has a field or often more of type datetime)