MySQL ODBC Connector - "Got error 12 from storage engine" on ORDER BY

Author

"Got error 12 from storage engine" on ORDER BY

Walter Hop

2005-12-27, 8:24 pm

Hello all,

I'm running into an error which seems to be fairly uncommon. Hopefully
somebody can give me some insight!

In the monthly run for my accounting software, I am doing a fairly
heavy query with a few joins, some date arithmetic, a 'having'
condition and finally an 'order by'.

When mysqld has been running for some time (a day or so), this query
produces the following error:
ERROR 1030 (HY000): Got error 12 from storage engine

Strangely, this problem does not appear when mysqld is restarted and I
retry the query. When I wait a day, the error starts to appear again.

I've already found that by removing the final "order by" from the
query, the error does not occur.

The result is 1107 rows by 24 columns (a few varchars and the rest is
all integer). The whole result fits in the querycache, it's not
unusually large.

The server is running 4.1.10a on FreeBSD 5.4-RELEASE-p6. It has 2GB
physical RAM of which 1.2GB is available (free + buffers) to mysqld at
almost all times. mysqld's size is 516MB. There's 1GB free on the
drive used for filesorts.

I've guessed setting max_heap_table_size=
256M but this did not help.

Because I can only test one variable change per day, I will be quickly
running out of options before january 1st :)

Any ideas would be appreciated!

Kind regards,
Walter Hop
Transip BV

--
Transip BV | http://www.transip.nl/
Hoogwaardige Innovatie | Aangename Zekerheid

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2005-12-28, 9:23 am

Hello.

> Strangely, this problem does not appear when mysqld is restarted and I
> retry the query. When I wait a day, the error starts to appear again.

It seems like a memory leak. However we can't be sure, may be due some
coincidence memory is thrilled by client threads. In my opinion your
first step should be upgrade to 4.1.16. Use MySQL from ports collection,
and if the problem still exists, check if it disappears with official
binaries. Then decrease sizes of different buffers.

> Strangely, this problem does not appear when mysqld is restarted and I

Check if error repeats after FLUSH TABLES.

Walter Hop <mysql@walter.transip.nl> wrote:
> Hello all,
>
> I'm running into an error which seems to be fairly uncommon. Hopefully
> somebody can give me some insight!
>
> In the monthly run for my accounting software, I am doing a fairly
> heavy query with a few joins, some date arithmetic, a 'having'
> condition and finally an 'order by'.
>
> When mysqld has been running for some time (a day or so), this query
> produces the following error:
> ERROR 1030 (HY000): Got error 12 from storage engine
>
> Strangely, this problem does not appear when mysqld is restarted and I
> retry the query. When I wait a day, the error starts to appear again.
>
> I've already found that by removing the final "order by" from the
> query, the error does not occur.
>
> The result is 1107 rows by 24 columns (a few varchars and the rest is
> all integer). The whole result fits in the querycache, it's not
> unusually large.
>
> The server is running 4.1.10a on FreeBSD 5.4-RELEASE-p6. It has 2GB
> physical RAM of which 1.2GB is available (free + buffers) to mysqld at
> almost all times. mysqld's size is 516MB. There's 1GB free on the
> drive used for filesorts.
>
> I've guessed setting max_heap_table_size=
256M but this did not help.
>
> Because I can only test one variable change per day, I will be quickly
> running out of options before january 1st :)
>
> Any ideas would be appreciated!
>
> Kind regards,
> Walter Hop
> Transip BV
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links