Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more
COURSE of the MONTH
13 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MySql server Win7 32, ODBC connector causing large tables to crash ODBC connector
Posted on 2012-12-25
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
On small tables works fine, but on large ones,the ODBC driver crashes. It is on an append query.
Pls help!
Connecting from MSAccess using current 32 bit ODBC driver mysql-connector-odbc-5.2.2
On small tables works fine, but on large ones,the ODBC driver crashes. It is on an append query.
Pls help!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
11
6
4
21 Comments
Active today
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?
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?
Active today
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
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
Active today
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
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
Active today
>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 ...
>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 ...
Active today
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
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
Active today
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
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
Active today
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.
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.
Active today
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
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
Active today
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 ...
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 ...
Active today
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
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
Active today
you can do sql over ado:
http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-vb-ado.html
http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-vb-ado.html
Active today
what kind of stored query?
you can translate them into the mysql corresponding code, which will then run on the mysql server, running better...
you can translate them into the mysql corresponding code, which will then run on the mysql server, running better...
Active today
Want to sort out queries failing for large tables....
Btw, tried
max_allowed_packet. of 128M
Same error
Btw, tried
max_allowed_packet. of 128M
Same error
Active today
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
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
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
- Node.js
- IT Administration
- JavaScript
- Server Software
- MySQL Server
- Percona, *Clustering
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.