Re: ERROR 1037 caused by simple query

[SGreen]

Markus Hoenicka <> wrote on 02/14/2005 12:10:09 PM:

> Hi all,
> 
> I'm struggling to set up MySQL on WinXP, with limited success so 
> far.  I want to
> use it as the backend for an application that can use either MySQL, 
> PostgreSQL,
> or SQLite for data storage.
> 
> At least one query type generated by my app fails on MySQL on WinXP.
> The queries
> are ok in MySQL 4.1.9 on FreeBSD, MySQL 4.0.23 on Linux, PostgreSQL on
> Windows/Linux/FreeBSD, and SQLite on Windows/Linux/FreeBSD. They used to 
be ok
> in MySQL 3.23 on Windows and other platforms too. However, on WinXP at 
least
> MySQL 4.1.8 and 4.1.9 appear to be broke.
> 
> I've got a table with 33 columns. The following query retrieves the 
> dataset with
> the unique column value refdb_id=1 without problems:
> 
> mysql> SELECT DISTINCT * FROM t_refdb WHERE refdb_id='1' ORDER BY 
refdb_id;
> 

You have discovered one of my pet peeves. You are quoting numbers. Numbers 
do not need quotes unless they are being STORED AS CHARACTERS. Internally, 
the engine will need to convert your numbers to characters (for each and 
every row) or your comparator to a number in order to perform the 
comparison you request (I am not sure which way your version chooses). 
This could slow your query processing down considerably.

> The following query should retrieve the same dataset (all ids are >0), 
but it
> fails:
> 
> mysql> SELECT DISTINCT * from t_refdb WHERE refdb_id<'2' ORDER BY 
refdb_id;
> ERROR 1037 (HY001): Out of memory; restart server and try again 
> (needed 217080 b
> ytes)

So what do you have for SHOW STATUS and SHOW VARIABLES? Have you run out 
of disk space for your swap file? This kind of thing normally doesn't 
happen if your MySQL database has enough memory to grow into.

> 
> The error is independent of the value of the comparison. <'10' or 
> <'100' or even
> >'10' all cause the same error (but in these cases the DISTINCT and 
ORDER BY
> clauses would make sense, that's why I mention this).
> 
> If I leave out either the DISTINCT or the ORDER BY clause, the query 
works
> again:
> 
> mysql> SELECT * FROM t_refdb WHERE refdb_id<'2' ORDER BY refdb_id;
> mysql> SELECT DISTINCT * FROM t_refdb WHERE refdb_id<'2';

That's because if you use DISTINCT or ORDER BY in a statement, the query 
engine performs another step which requires additional memory which you do 
not seem to have. Again, check your OS settings, your disk space, your RAM 
configuration, and your memory settings in your MySQL config file.

> 
> Once again, keep in mind that *all* of the above queries work with MySQL 
on
> other platforms and with other database engines on all platforms.
> 
> Is there anything wrong with this type of queries?

Other than using quotes to identify numbers, not generally. You know those 
statements are valid on other systems. Nothing changes between platforms 
for MySQL that would affect your test queries (except the platform 
settings themselves)

> 
> Another oddity of the windows version is that the command line client 
mysql
> appears to be limited to SQL queries of no more than 256 characters. I 
can't
> enter more than that. This limitation is also missing elsewhere.

That is a limitation of your windows command shell, not MySQL. I see that 
limit on every window's version I use (XP, ME, W2K, W2K3,...) So far, I 
find it easier to work around it (pre-break long statements into several 
lines and past them all. the MySQL CLI works just fine with multi-line 
input) than to try to adjust each station I visit. I like Notepad for 
this.

> 
> Platform info: WinXP Professional Version 2002, Service Pack 2, 
> 256MB RAM, MySQL
> Versions 4.1.8 or 4.1.9, set up for a developer box with MyISAM tables 
only,
> UTF8 as default encoding, other values left to their defaults to thebest 
of my
> knowledge.
> 
> Any clues?
> 
> regards,
> Markus
> 
> -- 
> Markus Hoenicka
> markus.hoenicka@stripped
> (Spam-protected email: replace the quadrupeds with "mhoenicka")
> http://www.mhoenicka.de
> 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine