Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySql server Win7 32, ODBC connector causing large tables to crash ODBC connector

Posted on 2012-12-25
21
Medium Priority
?
936 Views
Last Modified: 2013-04-22
Say, MySQL server, version: 5.5.29 is running on Win7 32 Bit OS.
Connecting from MSAccess using current 32 bit ODBC driver mysql-connector-odbc-5.2.2-win32
On small tables works fine, but on large ones,the ODBC  driver crashes. It is on an append query.
Pls help!
0
Comment
Question by:shaunwingin
  • 11
  • 6
  • 4
21 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38720197
does the table have a primary key field?
especially for append operations this should work fine, at least I could work just fine even on large tables (10 million rows) ...

now, what do you call "large" tables?
what type of tables? MyISAM, InnoDB, something else?
0
 

Author Comment

by:shaunwingin
ID: 38720240
The Source table doesn't have a primary key but the destination table does. Its the autonum.
If we append a table of say 3000 records it works fine. A table of say 70,000 records causes the ODBC to crash.
They are linked ODBC tables  using the above odbc driver and created in MS Access 2000
As for MyISAM, InnoDB
Please explain the difference. I just created the tables in MSACCESS, exported the to MySQL and linked them back, as it was easier to use the graphical interface in MSAccess
0
 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1336 total points
ID: 38720241
Hi!

If you are querying very large tables or inserting/updating large records  you will have to either
limit your result set to some smaller result using some good column values or the limit keyword
http://dev.mysql.com/doc/refman/5.5/en/select.html
http://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

or change the size of the client/server packet size (max_allowed_packet) to a suitable size.
http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet

In your case I would go for the max_allowed_packet. :)

Regards,
     Tomas Helgi
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:shaunwingin
ID: 38720257
Pls reply to my post too
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38720262
>As for MyISAM, InnoDB
>Please explain the difference.
it's the table storage engines:
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
it may impact the performance ...
0
 

Author Comment

by:shaunwingin
ID: 38720386
tables stored as MYISAM
Set max_allowed_packet to 16M and same error...
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38720390
Try increasing it to 32M or 64M.
Also you have to set max_allowed_packet into the my.cnf file and restart the database.
Can you post the error you are getting?

Regards,
   Tomas Helgi
0
 

Author Comment

by:shaunwingin
ID: 38720394
and 32M
max_allowed_packet = 32M
The size of the table being appended is a 2Meg Text file imported to a table  and the table to which it is appended is about 800Meg
0
 

Author Comment

by:shaunwingin
ID: 38721174
Unfortunately access just returns a general error with inserting table - no specific error no.
A clue to solution. When I was using the older version 3 ODBC driver and had the database stored in a Linux MySql server this problem did not exist.
Is there a way I can run the query from within MySql & call it from MSaccess? I will have to pass a single parameter to the query. I need a simple insert query.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 664 total points
ID: 38721423
you might want to check out toad for MySQL ...
I use that often, and it's quite nice to run sql directly on MySQL.
note the same GUI as ms accesss, but still very useful and powerful
http://www.toadworld.com/Downloads/ToadforMySQLFreeware/tabid/561/Default.aspx
0
 

Author Comment

by:shaunwingin
ID: 38722183
I need an automated system for MSAccess to process the queries ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38722798
to automate SQL queries, you don't need ms access.
you can scheduled SQL scripts with MySQL easily also:
http://dev.mysql.com/doc/refman/5.1/en/events-overview.html

and if it's based on human input (button click), you could code it all inside a stored procedure, and just run that one ...
0
 

Author Comment

by:shaunwingin
ID: 38732603
But how would I call the MYSQL query from within Access VB
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38732622
Hi!

If you are inserting into a MySQL table  from Access you will need to first create a Linked Table through the MySQL ODBC driver and then you can use the ADO Insert method in the VB.
Same goes for  any other database table.

Regards,
     Tomas Helgi
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732693
0
 

Author Comment

by:shaunwingin
ID: 38732702
I want to call a stored Query from Access
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38732749
what kind of stored query?
you can translate them into the mysql corresponding code, which will then run on the mysql server, running better...
0
 

Author Comment

by:shaunwingin
ID: 38733226
Want to sort out queries failing for large tables....
Btw, tried
max_allowed_packet. of 128M
Same error
0
 

Author Comment

by:shaunwingin
ID: 38733286
Exact error in Access: 3155 ODBC -- insert on linked table 'name' failed
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 1336 total points
ID: 38733575
The error can be one of these
http://office.microsoft.com/en-us/access-help/HV080760716.aspx
http://blog.nkadesign.com/2009/access-run-time-error-3155-odbc-insert-on-a-linked-table-failed/
http://stackoverflow.com/questions/11315678/error-3155-odbc-insert-on-a-linked-table-sample-failed

Does your user that open a connect to the linked table have sufficient rights to the database and all the  tables involved ?

Regards,
     Tomas Helgi
0
 

Author Comment

by:shaunwingin
ID: 38733591
Yes, It only fails on big tables- see above please
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question