Bug #8363 3.51.11 incorrectly describes Date field
Submitted: 8 Feb 2005 4:26 Modified: 21 Oct 2005 14:56
Reporter: Daniel Kasak (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.11 OS:Windows (Windows 2000)
Assigned to: Peter Harvey CPU Architecture:Any

[8 Feb 2005 4:26] Daniel Kasak
Description:
After upgrading to MyODBC-3.51.11 ( from 3.51.09 ), if I re-attach a MySQL linked table in MS Access that has a Date field in it, the field is 'detected' as a Text(255) field instead of a Date field.

Downgrading to MyODBC-3.51.09 and re-attaching the table fixes the issue.

This is a serious bug as queries which use the Date field in a comparison produce incorrect results ( text comparison used instead of date comparison ).

I can provide an ODBC trace if this is handy ( didn't really think you'd need it for this ).

How to repeat:
1) create a table in MySQL with a Date field:

create table TestIt (
ID mediumint unsigned not null auto_increment,
MyStamp timestamp(14) not null,
SomeDateField date default null,
primary key (ID)
)

2) Link table from MS Access with MyODBC-3.51.11. Click the linked table, and click 'design' to edit table design. Click 'OK' to warning that it's a linked table. Examine definition of SomeDateField - will be Text(255).

3) Link table from MS Access with MyODBC-3.51.09 and repeat above examination - field will now be correctly identified as a Date field.

You can also tell that MS Access thinks it's got a Text field instead of a Date field as the values in the field are all presented in MySQL's format ( yyyy-mm-dd ) instead of Access's format ( dd/mm/yyyy ).
[8 Feb 2005 6:06] Jorge del Conde
Thanks for your bug report!

All of the below fields are displayed as TEXT in MS Access.

CREATE TABLE `mybugtest` (
   `mydate` date default NULL,
  `mydatetime` datetime default NULL,
  `mytime` time default NULL,
  `mytimestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[11 Feb 2005 3:35] Peter Harvey
I created table as described. I then ran an ODBC browser tool which showed the correct column types. This tool uses SQLColumns() to get the types.

I demonstrated the problem in MS Access while running an ODBC trace. I then looked through the trace to check that SQLColumns() is being used and found this to be the case.

Will need to look further to find the cause of this problem. Will need to demonstrate problem in C code and then fix.
[11 Feb 2005 7:59] Peter Harvey
Seems a fix for BDE where the ODBC verison was being forced to v3 in driver during SQLAllocConnect on XP has caused a problem where the driver does not behave like v2 when, in this case, MS Access wants it to. This results in the driver sending back data types not filly recognized by MS Access (it wants v2 and its getting v3).

Will roll-back the BDE fix and try to come up with a better one later.

Look for this bug corrected in MyODBC 3.51.11-2.
[13 Feb 2005 22:12] Steven Leiphart
Another affect of this issue is that you cannot open the linked table in Access and update any DATE field.  If you try, you get this ODBC error:

ODBC--update on a linked table 'tablename' failed.
[Microsoft][ODBC Driver Manager] SQL data type out of range (#0)

P.S.  I did this on XP Pro w/SP2
[13 Feb 2005 22:17] Steven Leiphart
Where would I find v3.51.09 as was mentioned earlier in this thread?  I looked in the "Older Releases" page and it only shows versions 1 thru 4...

Thanks,
Steve.
[13 Feb 2005 22:22] Daniel Kasak
Here's ours:
http://www.nusconsulting.com.au/MyODBC-standard-3.51.9-win.exe
[13 Feb 2005 22:47] Daniel Kasak
Steven: Also note that the latest version available at:
http://www.peterharvey.org/Downloads/MySQL/MyODBC
seems to be working OK on our systems now.

Maybe you should also test this.
[15 Feb 2005 12:32] Steven Leiphart
Peter,

Bingo!  11-2 fixed my MS Access date problem.  Thank you!

P.S.  How does someone volunteer to help with this tool?  I am absolutely amazed at the quality and performance of MySQL and I feel sort of like I am shop-lifting using it for free.  I am a relatively seasoned programmer (24 years professional programming experience) and could possibly be of a help in some area.

Thanks,
Steve.
[22 Feb 2005 13:13] Vladimir Dusa
I tried this MyODBC-3.51.11-2-win version, but I have still the same problem. All the Date/Time fields of the table 'mybugtest' are linked to Access as a Text(255).

I used:
- Windows 2000 (5.00.2195, SP4) (German)
- MS Access 2000 (9.0.6926 SP3) (German)
- MDAC 2.8 (German)
- mysql-4.1.10-win32 (during installation was all settings default)
- MyODBC-3.51.11-2-win
- DSN created only with defautl values (i.e. server, database, user and password)

Thank you very much for your help
[22 Feb 2005 17:58] Denis Desjardins
Hi,

MyODBC-3.51.11-2-win version work well on XP and Win98, but I have still the same problem on the Windows 2000 Server (client side).

I used:
- Windows 2000 server (client side) (5.00.2194, SP4) (US)
- MS Access 2002 (10.6501.6626 SP3) (US)
- MDAC 2.8 (US)

I try with no succes with:
MyODBC-3.51.11-2-win
MyODBC-3.51.9
MyODBC-3.51.10
MyODBC-3.51.11-1
MyODBC-3.51.11-2

The MySQL server version 4.1.9-nt run on windows 2003 standart edition

Thank You
Denis Desjardins
[22 Feb 2005 21:32] Daniel Kasak
Vladimir / Denis: Did you relink the tables in Access after upgrading MyODBC?
If you don't relink the tables, Access will still think they're all text fields.
[23 Feb 2005 10:18] Vladimir Dusa
Daniel Kasak: Yes i did. Morover I have made a restart of the computer after deinstalling the version 3.51.11-1 and then again after installing the version 3.51.11-2 even through it is not probably needed. I have tried this reinstallation and relink now again just in case, but the Date/Time fileds are still linked as a Text.
[24 Feb 2005 9:37] Matt Price-Hutchinson
Hi Guys,
I have the same issue...  Was using MYODBC 11.0 
just upgrading to peters 11.2 will let you know 
Hutch
[24 Feb 2005 15:33] Denis Desjardins
Yes I relink the tables.
Same problem.
[25 Feb 2005 16:21] Vladimir Dusa
I tried to downgrade to older version of MySQL. I installed the older version of mysql-server on other computer (mysql-4.0.23-win) and uninstalled MyODBC-3.51.11-2-win on my computer. Then I installed older version of MyODBC (MyODBC-3.51.06.exe) on my computer. But in ODBC-Sources I see still the Version 3.51.11 and if I try to create new MySQL-DSN, error is shown: "Setup-routines for MySQL ODBC 3.51 Driver ODBC-Driver was not found. Install the Driver again." (this message is maybe not same, because I have translated it from german language.)

Could you help me, please?
[25 Feb 2005 16:44] Vladimir Dusa
I found some solution how to downgrade.
0) I've installed mysql server on other computer: mysql-4.0.23-win
1) I've reinstalled MyODBC-3.51.11-2-win.exe
2) I've deleted c:\winnt\system32\myodbc*.*
3) I've installed MyODBC-3.51.06.exe
4) I've relinked the tables in Access - Date/Time
[15 Mar 2005 21:48] Daniel Kasak
Yikes!

For anyone wanting to download MyODBC-standard-3.51.9-win.exe, I suggest looking somewhere other than my post above ( it's been removed ). I probably didn't think it through completely before posting a link to it on our web server, and I've clocked up 720MB in uploads for just this file! Hopefully no-one notices :)
[17 Mar 2005 3:44] g g
Had the same problem, which became excaserbated when I added the timestamp field to each table (as suggested by the MySQL manual). After reading this thread, I went back and looked, and sure enough the timestamp was listed as a text field.

Unistalled 11-1 and installed 11-2. Relinked tables. Done. Don't forget the warning you always see to first uninstall the old driver.

Thanks for the info. Sean.
[28 Mar 2005 18:38] Steve Gofarmer
Hi Folks !
Had the same Problems with the 3.51.11-1.
the -2 version corrects the date-problem - fine.
But it has a further bug: ACCESS 2000 reports an AutoIncremet-Field only as Integer not as Autoincrement as it should do.

I migrated an ACCESS2K-Table to MySQL. Viewing the Table in MySQL-Admin describes the Col as it is: Long Integer, Not Null and Autoincrement and so on

Linking to this Table from ACCESS2K - viewing the Properties, ACCESS shows it as Number not as Autoincremet as it was in Original, createdwith Access.

Does any one know this Problem ? Should I install an older MyODBC-Driver e.x. 3.51.9 ? I really Need help !
thx
[29 Mar 2005 15:11] Kris Bishop
I am having a similar problem.  I am running windows 98 and access 2000.  From what I can tell the -2 version is not available yet for windows.  When I look at the type in design view, it shows binary instead of text.  I cannot perform an update operation.
[3 Apr 2005 11:10] Vladimir Dusa
Hi Sean,
you were right. I had to uninstall the previous version first. I didn't know it and I installed the driver more than once. Then, when I deinstalled it, I had the myodbc-dlls still in windows-system directory. I had to delete them manually and then reinstalled it. 

Thank you very much

Vladimir
[11 Apr 2005 10:36] Manfred Pohl
Hello, 
had same problem with access after updating mysql database and updating odbc connector from .10 to .11-1.
going to .11-2 fixed this problem.
THX
Manfred
[12 Apr 2005 23:59] liam carl
Hi There,
I am running Access with a mysql back on MS server '03. I have tried completely uninstalling the versions provided included MyODBC-3.51.11-2-win.exe and to no avail. altering or inserting still returnsSQL datatype out of range. Has there been any further progress on this? Please advise.

Thanks, Liam
[13 Apr 2005 0:19] Daniel Kasak
Unpack a couple of examples of the MyODBC installer files, and check *exactly* what files it installs. Write them down somewhere. Uninstall everything. Then open Windows Explorer, right-click on your Windows folder, and search for *each* of the files ( individually ) you just wrote down. Delete them all.

I would then reboot. I may even wave a dead chicken in the air while reading from the bible in reverse.

Then install 3.51.11-2 from Peter Harvey's website ( link in comments above ).
[13 Apr 2005 17:35] liam carl
Thanks alot guys the dead chicken did the trick... so it seems.. i couldn't find a bible so i read aloud backwards from an Access 1.0 manual... i figured the age was about the same O_o

liam
[14 Apr 2005 14:01] John Foley
Neither 3.51.11-2 nor 3.51.09 appear to be available for download?
[14 Apr 2005 23:30] Daniel Kasak
Hang on while I roll my eyes to the back of my head...

The 3.51.11-2 file is mentioned multiple times in this bug, including 2 comments above yours ... which is my previous comment ... or 3 comments above this one.

Alternatively, here's another link:
http://www.peterharvey.org/Downloads/MySQL/MyODBC
[15 Apr 2005 22:16] Jeff Markham
I tested the 3.51.11-2 and it fixed the problem with date (0000-00-00) formats, but the time (00:00) just doesn't work. Database view shows blank fields for most, some show as a date. The datatype correctly shows as a Date/Time. Switching the format to a short or medium time displays 0:00 or 12:00 AM respective, but when you edit the cell, it reverts to display the date format for editing.

I'm running 4.0.20-standard server on solaris 8.
Access 2000 on Win98 with 3.51.11-2
[15 Apr 2005 22:32] Daniel Kasak
Access has never understood the 'time' type.
Use a datetime, and use some strange value like:

'30-12-1899 hh:mm:ss'

eg 5:45 pm would be:

'30-12-1899 17:45:00'

I *think* that's right. I don't have Access in front of me at the moment ( thank God ). Anyway Access will interpret the 'special' date of 30-12-1899 as it's queue that it really is dealing with a time type.
[18 Apr 2005 16:08] Geoffrey Poole
After upgrading MyODBC from 3.51.06 to 3.51.11-2, I'm getting the following error when I try to configure the existing .dsn files (created with the 3.51.06 driver) via ODBC data source administrator:

"General error: invalid file dsn"

When I try to "add" a new .dsn (via ODBC data source administrator), I am only allowed to select the driver and type in the file name. I never get the window that allows me to enter the information such as the server, database name, user id, etc. etc. Instead, after entering a .dsn file name to create, I get the error:

"A connection could not be made using the data source parameters entered. Save non-verified file DSN?"

If I click yes and try to configure the file within ODBC Data Source Admin, I'm back to the same "General error: invalid file dsn"

I'm running Win XP SP2, with MDAC 2.81.

I can uninstall 3.51.11 and reinstall 3.51.06 and everything works, except I'm having other problems associated with upgrading my server to 4.11 that I'm hoping the 3.51.11 driver would resolve.  So I'd rather not stay with 3.51.06.

I am working with Access, so I've been waiting until this date/time bug was resolved before upgrading...

Any thoughts on how to make this upgrade work would be great.
[18 Apr 2005 16:14] Geoffrey Poole
BTW, regarding the prior comment, I am manually removing the MyODBC3.dll, MyODBC3.lib, and MyODBC3d.dll files after I uninstall either verison, so that should not be the problem.

Thanks,

-Geoff
[26 Apr 2005 2:53] Damian Robinson
I am using Access 2002 (SP3), mySQL 4.1.11 and myODBC 3.51.11.2.

All my fields have a 'timestamp' and when I view the linked files in Access, it states that they are 'date/time' fields.

Firstly, I have almost two identical tables and can enter new records in one (tblDept) and not the other (tblBriefType).

[The tables are as follows:

# Table "tbldept" DDL
CREATE TABLE `tbldept` (
  `timestamp` timestamp NULL default '0000-00-00 00:00:00',
  `dptIndex` int(11) unsigned NOT NULL auto_increment,
  `dptName` varchar(20) default NULL,
  PRIMARY KEY  (`dptIndex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 11264 kB; InnoDB free: 11264 kB'

# Table "tblbrieftype" DDL
CREATE TABLE `tblbrieftype` (
  `timestamp` timestamp NULL default '0000-00-00 00:00:00',
  `bftIndex` int(11) unsigned NOT NULL auto_increment,
  `bftType` varchar(50) default NULL,
  PRIMARY KEY  (`bftIndex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 11264 kB; InnoDB free: 11264 kB; InnoDB free: 1' ]

Secondly, I can amend the current data in any table but cannot save it - nor can I then move to another record.

I have tried previous versions of myODBC - but keep getting the same thing.

Any suggestions?
[26 Apr 2005 4:05] Daniel Kasak
None of your issues are related to this particular bug. While it's good that this bug gets some attention, using it as a general support forum for anything related to MyODBC isn't such a great idea.

Access doesn't have a 'time' type ( as I've already pointed out to someone else half-way up the page ) so it's correct that it identifies your timestamp field as a DateTime.

You shouldn't use 'timestamp' as a field name. It's a reserved word, or if it it isn't, it should be.

Access most likely doesn't like your primary key because you've defined it as int unsigned. I think that's too big for Access. Try just an int, or a mediumint unsigned ( and relink your tables ).
[26 Apr 2005 5:27] Damian Robinson
Apologies for posting my question in the wrong place but thanks for the response, Daniel.

Changing the timestamp field and unsigning the int field worked a treat!

Regards, Damian
[26 Apr 2005 9:31] Benjamin Bolbach
Hi there,

i downloaded the 3.5.11-2 from http://www.peterharvey.org/Downloads/MySQL/MyODBC/ an installed it onto our win2k Server, but it didn't solve the "SQL Data Type is out of range #0" error, when trying to store a date into a mySQL-Table from Access2k. Even a change to varchar in mySQL didn't help.
Any other Idea? Another thing is that i don't see the correct version number in the driver overview of ODBC on the win2kbox.

Thanks in Advance
[29 Apr 2005 16:59] Dominic Ferard
Peter's suggested upgrade to 3.51.11-2 (thanks for the link too) worked for me. Previously I had 3.51.11 with the dreaded date problem. I ran the new windows .exe file, uninstalled myODBC, then re-ran to install. (All settings were kept anyway.) Then deleted the old linked tables in Access and re-linked them. Now date/time fields are fine (no longer text) and I can save records. It's true tho' that mySQL field with autoincrement does not appear as such in Access. Using Windows XP and Access 97.
[4 May 2005 14:33] Ozgur Yavuz
I ran the same problem, and what I have exactly done are as follows:
Following the instructions from mysql.com I took step 1 and 2.
Following the comment on this page I took the 3th step.
Using my imagination I took the next steps and it worked.

1. MDAC_TYP.EXE From Microsoft.com
2.windows2000-kb829558-x86-deu.exe From Microsoft.com
3. This was the attractive point MyODBC-3.51.11-2-win.msi. First Select uninstall option and then install it
4. Remove the ODBC DSN and create again
5. Remove the link from Access DB and create again
[6 May 2005 11:30] Kristian Løining
Hm... I found a crazy workaround, because I never got 3.51.11-2 to work :(

I have the real database located on an external db wich I don't have admin rights on, and it uses the new password procedures. At home I have my own mysql-server, and I enabled the "old-passwords"-option in my.cnf.

this is what I had to do.
1. downgrade to the working 3.51.06 on my windows client
2. create a new user on my mysql-server (after enabling old-passwords and restarting mysql) with same usr and pass as on the external server.
3. dump db from ext server and inserting it on mine (same db)
4. Hack my DNS server, so the address to the external mysql-server was replaced with my ip ;)
5. open access and update linked tables
6. Then it was just to fix the DNS again and run as normal ;)

Instead of shooting myself in the head, this solved it. hihi.
[21 Oct 2005 14:56] Mark Matthews
It appears this is fixed in 3.51.11-2 and later (3.51.12 is latest), but you _must_ un-install old versions of MyODBC before re-installing, and you _must_ re-create the DSN and re-link the tables to get Access to pick up the correct types again.