List:MySQL ODBC« Previous MessageNext Message »
From:SJ Stanaitis Date:May 20 2013 6:20pm
Subject:MySQL 5.6.10 and ODBC 5.2.05 Date Literal Problem
View as plain text  
We recently upgraded from MySQL 5.0.24a to 5.6.10 CE on Oracle Linux 5.
Everything appears to be functioning OK, however I have an ODBC related
issue which is creating problems in regard to generating reports through
it via Crystal Reports.  This problem did not exist under 5.0.24a using
the ODBC 3.5 driver.  I updated the driver to 5.2 after the problem was
reported, and have also tried the most recent version of Crystal
Reports.

The following query is part of a larger query that is sent to the MySQL
server via the MySQL ODBC Driver 5.2.05:

select sql_no_cache
	if(CCT.creditCardTypeName is not null, CCT.creditCardTypeName,
if(OPBML.orderPaymentBMLID is not null, 'Bill Me Later', 'Cash/Check'))
as theType,
	'orderPayment               ' as source,
	date_format(OP.authorizationDate, '%Y-%m-%d') as theDate,
	if (OPC.checkNumber is not null, OPC.checkNumber, '') as
checkNum,
	if (OPWT.wireTransferDate is not null, OPWT.wireTransferNumber,
'') as wtNum,
	concat(C.lastName, ', ', C.firstName) as custName,
	O.orderNumber,
	OP.amount
from
	orders O
	inner join orderPayment OP on (O.ordersID=OP.ordersID)
	inner join paymentMethod PM on
(PM.paymentMethodID=OP.paymentMethodID)
	inner join customer C on (C.customerID=O.customerID)
	inner join partner P on (C.partnerID = P.partnerID and
P.businessUnit = "XXX")
	left outer join orderPaymentBML OPBML on
(OP.orderPaymentID=OPBML.orderPaymentID)
	left outer join orderPaymentCheck OPC on
(OP.orderPaymentID=OPC.orderPaymentID)
	left outer join orderPaymentCreditCard OPCC on
(OP.orderPaymentID=OPCC.orderPaymentID)
	left outer join orderPaymentWireTransfer OPWT on
(OP.orderPaymentID=OPWT.orderPaymentID)
	left outer join creditCardType CCT on
(CCT.creditCardTypeID=OPCC.creditCardTypeID)
where
	OP.authorizationDate {d '2013-05-19'} and {d '2013-05-20'} and
	PM.paymentMethodAbbreviation in ('BM', 'CC', 'CH', 'WT');

Prior to the upgrade, this query would generate a list of results when
executed.  After the upgrade, the query randomly generates an empty
result set.  I could run it 10 times in a row and anywhere from 1/2 to
3/4 of the attempts would result in an empty result set.  The two date
parameters are copied exactly as they are sent to the server via the
myodbc.sql logfile.  I get the same result running the quoted query on
the MySQL client as I do running it from Crystal Reports via ODBC.  If I
replace the ODBC parameters with the actual dates ala '2013-05-19'
instead of {d '2013-05-19'} the query will return the correct results
each and every time it is run.

I understand that the way these parameters are treated by MySQL changed
in 5.6.4, so now MySQL pays attention to the keyword... however I cannot
pin down exactly why it is having a problem with the dates in this
query.

I ran an 'explain' on the query and got this:
+----+-------------+-------+--------+-----------------------------------
-----------------------------+-----------------------+---------+--------
--------------------------+------+----------+---------------------------
-------------------------+
| id | select_type | table | type   | possible_keys
| key                   | key_len | ref                              |
rows | filtered | Extra                                              |
+----+-------------+-------+--------+-----------------------------------
-----------------------------+-----------------------+---------+--------
--------------------------+------+----------+---------------------------
-------------------------+
|  1 | SIMPLE      | OP    | range  |
PRIMARY,ordersID_ind,paymentMethodID_ind,authorizationDate_ind |
authorizationDate_ind | 6       | NULL                             |
12 |   100.00 | Using index condition; Using where                 |
|  1 | SIMPLE      | PM    | eq_ref | PRIMARY
| PRIMARY               | 4       | production_db.OP.paymentMethodID |
1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | OPC   | ref    | orderPaymentID_ind
| orderPaymentID_ind    | 4       | production_db.OP.orderPaymentID  |
1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | OPBML | ref    | orderPaymentID_ind
| orderPaymentID_ind    | 4       | production_db.OP.orderPaymentID  |
1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | OPCC  | ref    | orderPaymentID_ind
| orderPaymentID_ind    | 4       | production_db.OP.orderPaymentID  |
1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | OPWT  | ref    | orderPaymentID_ind
| orderPaymentID_ind    | 4       | production_db.OP.orderPaymentID  |
1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | O     | eq_ref | PRIMARY,customerID_ind
| PRIMARY               | 4       | production_db.OP.ordersID        |
1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY,partnerID_ind
| PRIMARY               | 4       | production_db.O.customerID       |
1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | P     | eq_ref | PRIMARY
| PRIMARY               | 4       | production_db.C.partnerID        |
1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | CCT   | ALL    | PRIMARY
| NULL                  | NULL    | NULL                             |
4 |    75.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+-----------------------------------
-----------------------------+-----------------------+---------+--------
--------------------------+------+----------+---------------------------
-------------------------+

The orderPayment table is set up as follows:
+-------------------+---------------------------------------+------+----
-+---------+----------------+
| Field             | Type                                  | Null | Key
| Default | Extra          |
+-------------------+---------------------------------------+------+----
-+---------+----------------+
| orderPaymentID    | int(11) unsigned                      | NO   | PRI
| NULL    | auto_increment |
| quotesID          | int(11) unsigned                      | YES  | MUL
| NULL    |                |
| ordersID          | int(11) unsigned                      | YES  | MUL
| NULL    |                |
| paymentOrdinal    | smallint(5) unsigned                  | NO   |
| 0       |                |
| addedDate         | datetime                              | YES  |
| NULL    |                |
| paymentMethodID   | int(11) unsigned                      | NO   | MUL
| 0       |                |
| amount            | decimal(7,2)                          | NO   |
| 0.00    |                |
| statusEnum        | enum('pending','authorized','denied') | NO   |
| pending |                |
| authorizationDate | datetime                              | YES  | MUL
| NULL    |                |
| accountLogDate    | datetime                              | YES  |
| NULL    |                |
+-------------------+---------------------------------------+------+----
-+---------+----------------+

The original query constructed the date filter as follows:
OP.authorizationDate between date_add({?date},INTERVAL "3:0:01"
HOUR_SECOND) and date_add( date_add({?date}, INTERVAL 1 DAY),

I haven't been able to find any issues similar to mine after a ton of
searching, and the MySQL error logs haven't been any help.

Anyone see something like this before?  Any ideas?

Thanks!
--SJ
Thread
MySQL 5.6.10 and ODBC 5.2.05 Date Literal ProblemSJ Stanaitis20 May