Recent

Author Topic: [ MySQL ran out of memory ]  (Read 11964 times)

bleung

  • New member
  • *
  • Posts: 7
[ MySQL ran out of memory ]
« on: April 29, 2013, 06:35:09 am »
Hi,

I am working on a database (which holding over 1.5M records with 102 columns) on a MySQL server; and I am programming on Lazarus for updating the given database.

However, I got the error as:

"Could not execute statement. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: 2008; Message: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.29-log] MySQL client ran out of memory;."


while opening the database.



The program is successfully executed on the same structure but much smaller (only 20000 records) database.

Does anyone know what I am missing (have to do for huge database) on my programming?


Thanks
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: [ MySQL ran out of memory ]
« Reply #1 on: April 29, 2013, 06:50:44 am »
SZomewhere you have a SELECT * FROM table. The ODBC driver will be using a mysql_store_result and the complete resultset is loaded in the client.
For big tables, you should never select all rows. Limit your selects. Use functional subdivisions or, if you really need to recover all rows, use LIMIT OFFSET. But never trie to get all rows in memory at the same time.
Here you get your error in the ODBC driver but don't forget that a second data buffering is done in TDataset. The TDataset buffer is at least as big as the libmysql buffer.


bleung

  • New member
  • *
  • Posts: 7
Re: [ MySQL ran out of memory ]
« Reply #2 on: April 29, 2013, 07:58:00 am »
Hi, ludob,

Thanks for your information.

This is my 1st time using MySQL with Lazarus.

Could you please give me a link/website for knowing more and more about:

1.  how to use LIMIT OFFSET?
2.  how to do "..second data buffering..."?
3.  how to make the TDataset buffer as big as libmysql buffer?

Thanks for your help.
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: [ MySQL ran out of memory ]
« Reply #3 on: April 29, 2013, 08:05:28 am »
This is my 1st time using MySQL with Lazarus.
I'm assuming you know what you're doing with your 102 columns ;)

1.  how to use LIMIT OFFSET?
2.  how to do "..second data buffering..."?
3.  how to make the TDataset buffer as big as libmysql buffer?
1. Probably in the mysql documentation?
2. You can't *do* second data buffering - Ludo is saying a second buffering takes place in TDataset
3. Likewise, Ludo is saying the TDataSet *is* as big as the libmysql buffer, or even bigger.

Are you using Lazarus with an ODBC connection to connect to MySQL? You do know there are connectors for MySQL 4.x-5.x included with Lazarus?

Edit: Finally: you might want to upgrade to Lazarus 1.0.8 in any case - there have been quite a few database fixes in that Lazarus version and FPC 2.6.2 which is used for Lazarus 1.0.8
« Last Edit: April 29, 2013, 08:07:32 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bleung

  • New member
  • *
  • Posts: 7
Re: [ MySQL ran out of memory ]
« Reply #4 on: April 29, 2013, 08:13:57 am »
Hi, BigChimp,

Thanks for your quick reply.

The database exported from an ERP system that it is holding so many columns on it.

Noted; I will take a look at the MySQL document.

Since the database was in MS Access format, my program was accessing it by ODBC.  After migrated it to MySQL, I just modify the ODBC settings on the program that I did not use the MySQL connection in Lazarus.

Do you think that this issue will be resolved if I am using the MySQL connection directly in Lazarus?
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: [ MySQL ran out of memory ]
« Reply #5 on: April 29, 2013, 08:20:15 am »
Hi bleung,

ERP systems - that makes sense ;)

About using the mysql connector instead of ODBC connector: I don't know if your issues will be solved, but perhaps performance will improve and you have one less translation layer to go through (=> less possibilities for bugs etc).

I'd think Ludo's suggestion of LIMIT OFFSET is the best to look at right now. This lets you select only some records instead of the entire table when you run the query (see the MySQL documentation on SELECT)
Edit: of course, as he suggests, where possible, using a WHERE query to filter on only the rows/records you want is more elegant and probably fits in better with your program.
« Last Edit: April 29, 2013, 08:22:49 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: [ MySQL ran out of memory ]
« Reply #6 on: April 30, 2013, 06:20:16 am »
When the app was originally an access application then it was probably using ADO to access the database. That is a layer you are missing with sqldb.
When you open a table in Access (view a table), on the ODBC layer you will see that it first loads the keys and then executes small selects (typically 10 rows) to fill up the view. It will do it with 'SELECT fields FROM table WHERE key in (key1,....,key10)' queries which is understood by all DB's. Even when you click 'go to last record', iirc, it will only load those records to fill up the screen. This allows to view large tables without loading the complete table. All the keys have to fit in memory though.
When you want to do the same in sqldb, you have to program this algorithm yourself. Since you are new to this I suggest you take an ODBC trace of your access program to better understand what is happening on the ODBC layer. The ODBC trace can be started from the ODBC manager (control panel on windows).

bleung

  • New member
  • *
  • Posts: 7
Re: [ MySQL ran out of memory ]
« Reply #7 on: May 06, 2013, 09:13:49 am »
Hi bleung,

ERP systems - that makes sense ;)

About using the mysql connector instead of ODBC connector: I don't know if your issues will be solved, but perhaps performance will improve and you have one less translation layer to go through (=> less possibilities for bugs etc).

I'd think Ludo's suggestion of LIMIT OFFSET is the best to look at right now. This lets you select only some records instead of the entire table when you run the query (see the MySQL documentation on SELECT)
Edit: of course, as he suggests, where possible, using a WHERE query to filter on only the rows/records you want is more elegant and probably fits in better with your program.

Hi, BigChimp,

Thanks for your help.  After I have amended the "Select" command with "LIMIT 5000" options, my program is running well (of course, only the 1st 5000 records will be shown on the 'WorkSheetGrid"; but good for us).  Therefore, I think the "Ran Out of Memory" is on the memory allocation for the Grid; but not on the SQL query.

Regards & Thanks
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

bleung

  • New member
  • *
  • Posts: 7
Re: [ MySQL ran out of memory ]
« Reply #8 on: May 06, 2013, 09:37:19 am »
When the app was originally an access application then it was probably using ADO to access the database. That is a layer you are missing with sqldb.
When you open a table in Access (view a table), on the ODBC layer you will see that it first loads the keys and then executes small selects (typically 10 rows) to fill up the view. It will do it with 'SELECT fields FROM table WHERE key in (key1,....,key10)' queries which is understood by all DB's. Even when you click 'go to last record', iirc, it will only load those records to fill up the screen. This allows to view large tables without loading the complete table. All the keys have to fit in memory though.
When you want to do the same in sqldb, you have to program this algorithm yourself. Since you are new to this I suggest you take an ODBC trace of your access program to better understand what is happening on the ODBC layer. The ODBC trace can be started from the ODBC manager (control panel on windows).

Hi, Ludob,

Thanks for your help.  My program is running good after I have used the LIMIT option on the Select command for the WorkSheetGrid.  I understand that the concept what you are mentioning regarding the Access on opening a huge DB.  However, I am not an experienced SQL guy that I have some questions regarding that technical issue:

1.  You said Access will do "select .......where key in (key 1, key 2....key n)".  However, on my DB, we don't use any key on it -- can I use the same way to limit the displayed records by Record Number {like an imagine command "Select .... where RecNum in (Rec n.... Rec n + (Max Rec Shown))"}? (assume "Max Rec Shown" is 30, then, it will show the records from current Rec # to next 30 Rec )

2.  At the same time, do I need to rewrite the event for the DB Navigator (button events of [Prev Rec], [Next Rec], [First Rec] and [Last Rec])?

Thanks & Regards
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: [ MySQL ran out of memory ]
« Reply #9 on: May 06, 2013, 02:00:25 pm »
Quote
1.  You said Access will do "select .......where key in (key 1, key 2....key n)".  However, on my DB, we don't use any key on it -- can I use the same way to limit the displayed records by Record Number {like an imagine command "Select .... where RecNum in (Rec n.... Rec n + (Max Rec Shown))"}? (assume "Max Rec Shown" is 30, then, it will show the records from current Rec # to next 30 Rec )
1.5 M records and no key? With key I mean the primary key or any other UNIQUE index. You can't be working with 1.5 M records and not use a key  :o
If you don't have a primary key, your SELECT LIMIT N is not guaranteed to be repetitive.

Quote
2.  At the same time, do I need to rewrite the event for the DB Navigator (button events of [Prev Rec], [Next Rec], [First Rec] and [Last Rec])?
Yes. When you retrieve the records screen by screen, your dataset is changing constantly and you don't want DBNavigator to start moving in that dataset.  If you want to buffer some data (previous screens for example) you should also consider to not use DBGrid but a normal grid and manage the buffer yourself.

bleung

  • New member
  • *
  • Posts: 7
Re: [ MySQL ran out of memory ]
« Reply #10 on: May 07, 2013, 10:30:20 am »
Quote
1.5 M records and no key? With key I mean the primary key or any other UNIQUE index. You can't be working with 1.5 M records and not use a key  :o
If you don't have a primary key, your SELECT LIMIT N is not guaranteed to be repetitive.

We don't have any key on it because this database extracted from the ERP to an Excel files (several files; very stupid way) and somebody import the Access and passed to me.
Therefore, I put them together and pass it to the MySQL.

Quote
Yes. When you retrieve the records screen by screen, your dataset is changing constantly and you don't want DBNavigator to start moving in that dataset.  If you want to buffer some data (previous screens for example) you should also consider to not use DBGrid but a normal grid and manage the buffer yourself.

Thanks!! 

However, I think what did you suggest (LIMIT OFFSET) is the good way for doing that because there are 1.5M records on the single DB -- it makes no sense to load all of them into the memory (in fact, we don't need to do it at the moment). 
Lazarus ver 1.0.8
FPC ver 2.6.2
x86_64-win64-win32/win64

 

TinyPortal © 2005-2018