UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql, Mariadb Odbc Connector Issue    
 
   
coalee
post Jun 24 2018, 08:19 PM
Post#1



Posts: 5
Joined: 12-June 18



Hi
I have MariaDB server running on Windows Server. The DB has 24 tables which migrated from Ms Access a few months ago. My database is built on Ms Access as frontend which connected to MariaDB with MySQL ODBC connector. Everything works fine, Ms Access can connect by using User DSN profile (MySQL ODBC). However, I try to change to use MariaDB ODBC connector instead of MySQL. I think it does make sense to use the same server & ODBC connector from MariaDB. However, this causes the big issue. I can test connection to MariaDB with ODBC data Source Administration successfully (everything went very well with MariaDB ODBC). However, after Ms Access linked to MariaDB tables with MariaDB ODBC, it causes an error when I try to view the record from tables.

“The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

Then, I try to isolate the problem by creating a blank MS Access then link all the tables again (no form, no code etc.) then click the table icon to view the record. The same error occurs again. This error only occurs on 6 tables (not all) only. Does anyone come across this problem? Could you please give me some advice?

Server: MariaDB v.10.2.14
Client: Ms Access v.2013 (32 bit)
ODBC driver: MySQL ODBC 5.3.10 (32 bit), MariaDB ODBC 3.05 (32 bit)

Regards
Nat



Attached File(s)
Attached File  error1.png ( 65.44K )Number of downloads: 11
 
Go to the top of the page
 
GroverParkGeorge
post Jun 24 2018, 08:57 PM
Post#2


UA Admin
Posts: 33,005
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

It would appear that this ODB Cconnector has interpreted one or more datatypes in this database differently from the previous connections.

Therefore, you'll need to look at each of the fields in the impacted tables to see which of them is no longer compatible. It's more likely to be an integer vs small integer, for example, or a Char field with fewer characters. I don't know how MariaDB handles datatypes, so it's a test I can't help with.
This post has been edited by GroverParkGeorge: Jun 24 2018, 09:40 PM

--------------------
Go to the top of the page
 
cheekybuddha
post Jun 25 2018, 06:02 AM
Post#3


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


Can you post the connection string of one of your tables.

Eg in the Immediate Window (Ctrl+G):
CODE
?CurrentDb.TableDefs("demi-pair").Connect
and hit return

--------------------


Regards,

David Marten
Go to the top of the page
 
coalee
post Jun 25 2018, 08:31 PM
Post#4



Posts: 5
Joined: 12-June 18



@GroverParkGeorge
Thank you, for the warm welcome. Anyway, I have migrated from MS Access to MariaDB with MySQL workbench tool (Only took DB schema code ). Everything works with MySQL ODBC connector (no problem with the data type). So, I guess it should work with MariaDB/MariaDB ODBC as well but it doesn't.

@cheekybuddha
Here is the output

CODE
ODBC;DSN=eROL-TEST;


Nothing much info, as I connect to MariaDB server with DSN user profile. All user/password are set within the profile. All tables have been linked at the same profile & same time. but some tables cannot be read.

Cheers
Go to the top of the page
 
GroverParkGeorge
post Jun 25 2018, 09:06 PM
Post#5


UA Admin
Posts: 33,005
Joined: 20-June 02
From: Newcastle, WA


I see.

The error you first reported suggests that one or more fields is being interpreted as the wrong data type by the ODBC connection you are now using. We'd need to have you compare those fields to identify which field(s) are impacted. Are you able to do that?

"...but some tables cannot be read. " That's a different problem from the one suggested by the screenshot in your initial post. So, are there two different errors, or is this the same thing?
This post has been edited by GroverParkGeorge: Jun 25 2018, 09:07 PM

--------------------
Go to the top of the page
 
cheekybuddha
post Jun 26 2018, 02:47 AM
Post#6


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


OK, I was hoping it would output the connection string used by the DSN.

When you set the DSN, which options have you set?

I agree with George, this is most likely an ODBC conversion issue.

Make sure option NO_BIGINT (value = 16384) is set.

Other usual options to set would also include FOUND_ROWS (value = 2)

However, the documentation for the MariaDB connector doesn't mention NO_BIGINT, even though it claims to be a drop-in replacement for MyODBC.

It also warns not to use this connector if you plan to use multi-statements.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
coalee
post Jun 26 2018, 07:49 PM
Post#7



Posts: 5
Joined: 12-June 18



@GroverParkGeorge
Some table can read/open but some can't. The one that can read, I can just click to open the table to view and edit. The one that can't read (6 from 25 tables ) gave me the error when I click to open. I don't know what the problem is. However, yesterday I update MariaDB server to latest version 10.2.15 (10.2 series) but the problem still there, no luck.
If I have a problem with field's data type then how can I use MySQL ODBC connector without any problem? Did I miss something?

@cheekybuddha
Here is the option that I set. nothing special, just common for Access. I also set "Connection Character set" to utf8. There is no option "NO_BIGINT".


Attached File(s)
Attached File  error1.png ( 11.04K )Number of downloads: 1
 
Go to the top of the page
 
GroverParkGeorge
post Jun 26 2018, 09:07 PM
Post#8


UA Admin
Posts: 33,005
Joined: 20-June 02
From: Newcastle, WA


I see. Simply trying to open one of the 6 problem tables raises that error: "“The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

In other words, simply trying to SELECT data raises an error saying "Try inserting or pasting less data." Even though you are only trying to select.

That's something to do with the MySQL ODBC connector, not with Access. I'm afraid unless someone like David has extensive experience with MariaDB and/or MySQL, you might have more success querying a forum specifically for those applications.

--------------------
Go to the top of the page
 
coalee
post Jun 26 2018, 10:16 PM
Post#9



Posts: 5
Joined: 12-June 18



QUOTE
That's something to do with the MySQL ODBC connector, not with Access.

The problem is MariaDB ODBC connector. MySQL connector is working fine. It turns out, for my experience, that MySQL ODBC connector is more reliable than MariaDB connector. I don't have any choice apart from stay with MySQL ODBC to connecting to MariaDB server.
Go to the top of the page
 
cheekybuddha
post Jun 27 2018, 08:27 AM
Post#10


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


Yes, I think that will be the solution: to use the MyODBC connector rather than the MariaDB connector.

I guess connecting from Access is probably rather low priority for MariaDB.

Out of curiosity, what is different about the six tables which cause the error? Do they have BIGINT fields and the others don't?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
coalee
post Jun 28 2018, 05:35 PM
Post#11



Posts: 5
Joined: 12-June 18



Hi
My Access is v.2013 therefore, this is no BIGINT yet. Also, the data type that I using is INT (10), VARCHAR(255), TINYINT(1), DATETIME and LONGTEXT.

Cheers
Go to the top of the page
 
cheekybuddha
post Jun 29 2018, 09:44 AM
Post#12


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


OK, then I'm not sure where the issue lies.

Please will you post the versions of the MariaDB Connector/ODBC and MyODBC versions you are using so that anyone else searching on this issue can match with the versions they are using.

Also, mention whether you are using 32/64 bit, but I guess that should match your Access version 'bitness'.

Also, please mention if you are using ANSI or UTF8 version of each connector.

Thanks,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:22 AM