Unable to convert MySQL date/time value

This forum can be browsed by the general public. Posting is limited to current SAPIEN license holders with active maintenance and does not offer a response time guarantee.
Forum rules
DO NOT POST SUBSCRIPTION NUMBERS, LICENSE KEYS OR ANY OTHER LICENSING INFORMATION IN THIS FORUM.
Only the original author and our tech personnel can reply to a topic that is created in this forum. If you find a topic that relates to an issue you are having, please create a new topic and reference the other in your post.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
gkhairallah
Posts: 31
Joined: Fri Jul 29, 2011 6:45 am

Unable to convert MySQL date/time value

Post by gkhairallah » Fri Jul 29, 2011 6:45 am

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?

User avatar
gkhairallah
Posts: 31
Joined: Fri Jul 29, 2011 6:45 am

Unable to convert MySQL date/time value

Post by gkhairallah » Fri Jul 29, 2011 7:12 am

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 ..

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Unable to convert MySQL date/time value

Post by davidc » Fri Jul 29, 2011 7:34 am

We are looking into the issues. David
David
SAPIEN Technologies, Inc.

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Unable to convert MySQL date/time value

Post by davidc » Tue Aug 02, 2011 4:53 am

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
David
SAPIEN Technologies, Inc.

User avatar
gkhairallah
Posts: 31
Joined: Fri Jul 29, 2011 6:45 am

Unable to convert MySQL date/time value

Post by gkhairallah » Tue Aug 02, 2011 5:29 am

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, ''

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Unable to convert MySQL date/time value

Post by davidc » Tue Aug 02, 2011 6:00 am

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
David
SAPIEN Technologies, Inc.

User avatar
gkhairallah
Posts: 31
Joined: Fri Jul 29, 2011 6:45 am

Unable to convert MySQL date/time value

Post by gkhairallah » Tue Aug 02, 2011 6:03 am

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)

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Unable to convert MySQL date/time value

Post by davidc » Tue Aug 02, 2011 8:31 am

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.
David
SAPIEN Technologies, Inc.

User avatar
davidc
Posts: 5913
Joined: Thu Aug 18, 2011 4:56 am

Unable to convert MySQL date/time value

Post by davidc » Tue Aug 02, 2011 8:34 am

For now I
recommend formatting the query.David
David
SAPIEN Technologies, Inc.

User avatar
gkhairallah
Posts: 31
Joined: Fri Jul 29, 2011 6:45 am

Unable to convert MySQL date/time value

Post by gkhairallah » Tue Aug 02, 2011 8:42 am

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)