ERROR 1037 caused by simple query
ERROR 1037 caused by simple query
am 14.02.2005 18:10:09 von Markus Hoenicka
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;
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)
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';
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?
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.
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 the best of my
knowledge.
Any clues?
regards,
Markus
--
Markus Hoenicka
markus.hoenicka@cats.de
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Re: ERROR 1037 caused by simple query
am 15.02.2005 15:54:33 von SGreen
--=_alternative 005226E185256FA9_=
Content-Type: text/plain; charset="US-ASCII"
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@cats.de
> (Spam-protected email: replace the quadrupeds with "mhoenicka")
> http://www.mhoenicka.de
>
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 005226E185256FA9_=--
Re: ERROR 1037 caused by simple query
am 15.02.2005 17:18:33 von Markus Hoenicka
Hi,
thanks for your reply.
SGreen@unimin.com was heard to say:
> 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.
Ok, this is no big deal to change, but it is not related to the problem I'm
facing. The out of memory error occurs just as well if you do not quote the
numbers.
> 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.
>
See below. I honestly didn't fiddle with any of these settings after finishing
the configuration wizard. The current size of the swap file is 384MB with an
allowed maximum of 768MB. The disk has approx. 7.5GB free space available.
mysql> SHOW STATUS;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 119 |
| Bytes_sent | 8672 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Connections | 2 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_read_first | 2 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 7173 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 1 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_tables | 0 |
| Opened_tables | 11 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 3 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 11 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
| Uptime | 30491 |
+--------------------------+-------+
156 rows in set (0.00 sec)
mysql> SHOW VARIABLES;
+---------------------------------+------------------------- ---------------------+
| Variable_name | Value
|
+---------------------------------+------------------------- ---------------------+
| back_log | 50
|
| basedir | C:\Programme\MySQL\mysql4.1\
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | utf8
|
| character_set_results | latin1
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Programme\MySQL\mysql4.1\share\charsets/
|
| collation_connection | latin1_swedish_ci
|
| collation_database | utf8_general_ci
|
| collation_server | utf8_general_ci
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | C:\Programme\MySQL\mysql4.1\Data\
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 1800
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 4
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| group_concat_max_len | 1024
|
| have_archive | NO
|
| have_bdb | NO
|
| have_compress | YES
|
| have_crypt | NO
|
| have_csv | NO
|
| have_example_engine | NO
|
| have_geometry | YES
|
| have_innodb | DISABLED
|
| have_isam | NO
|
| have_ndbcluster | NO
|
| have_openssl | NO
|
| have_query_cache | YES
|
| have_raid | NO
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_additional_mem_pool_size | 2097152
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path |
|
| innodb_data_home_dir |
|
| innodb_fast_shutdown | ON
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_flush_method |
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 10485760
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir |
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_max_purge_lag | 0
|
| innodb_table_locks | ON
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_thread_concurrency | 8
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388608
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language | C:\Programme\MySQL\mysql4.1\share\english\
|
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| log | OFF
|
| log_bin | OFF
|
| log_error | .\pc51997.err
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_update | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 1
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 100
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 16777216
|
| max_insert_delayed_threads | 20
|
| max_join_size | 4294967295
|
| max_length_for_sort_data | 1024
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| myisam_data_pointer_size | 4
|
| myisam_max_extra_sort_file_size | 107374182400
|
| myisam_max_sort_file_size | 107374182400
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| named_pipe | OFF
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 622
|
| pid_file | C:\Programme\MySQL\mysql4.1\Data\pc51997.pid
|
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 0
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 61440
|
| read_only | OFF
|
| read_rnd_buffer_size | 258048
|
| relay_log_purge | ON
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| shared_memory | OFF
|
| shared_memory_base_name | MYSQL
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_net_timeout | 3600
|
| slow_launch_time | 2
|
| sort_buffer_size | 217080
|
| sql_mode |
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| system_time_zone | Westeurop?ische Normalzeit
|
| table_cache | 256
|
| table_type | MyISAM
|
| thread_cache_size | 8
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| tmp_table_size | 5242880
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| version | 4.1.8-nt
|
| version_comment | Source distribution
|
| version_compile_machine | i32
|
| version_compile_os | NT
|
| wait_timeout | 28800
|
+---------------------------------+------------------------- ---------------------+
178 rows in set (0.00 sec)
> 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.
>
I'd be happy to if you could tell me which settings may be relevant for this
problem.
> 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.
>
Ok, thanks a lot for the hint how to work around this limitation.
regards,
Markus
--
Markus Hoenicka
markus.hoenicka@cats.de
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Re: ERROR 1037 caused by simple query
am 15.02.2005 17:33:06 von jonathan.lampe
At 10:18 AM 2/15/2005, Markus Hoenicka wrote:
>SGreen@unimin.com was heard to say:
> > You have discovered one of my pet peeves. You are quoting numbers. Numbers
> > do not need quotes unless they are being STORED AS CHARACTERS.
I would suggest the opposite: ALWAYS quote all your arguments, even
numbers, especially if you're dealing with a web application.
Consider the following queries where ID is an Integer and "123" is passed
in from a web application:
#1) SELECT * FROM Users WHERE ID=123;
#2) SELECT * FROM Users WHERE ID="123";
Now let's pretend that the application programmer didn't perform proper
data validation on the incoming "123" value. Let's also pretend that some
snot-nosed punk manages to enter "123; DELETE FROM Users" as his user
ID. Now, your queries are...
#1) SELECT * FROM Users WHERE ID=123; DELETE FROM Users;
#2) SELECT * FROM Users WHERE ID="123; DELETE FROM Users";
#1 will likely execute BOTH queries (yours and the snot-nosed's). Buh-bye
Users table. #2 will not execute.
- Jonathan Lampe, GCIA, GSNA
- jonathan.lampe@standardnetworks.com
******************* PLEASE NOTE *******************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
Re: ERROR 1037 caused by simple query
am 24.02.2005 10:58:56 von Markus Hoenicka
Hi,
just for the archive:
It took me a while until I could revisit the ERROR 1037 problem. I installed
4.1.10 to make sure I've got the latest bugfixes. The error would still show
up, but then I fiddled with my my.ini until it worked. Please do not expect me
to understand why this works, but increasing
sort_buffer_size=512K
from 212K fixes the problem. The description of this variable reads:
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
It is entirely unclear to me why this affects SELECT statements, but so be it.
Even more interesting to the inclined reader should be the fact that this
setting is listed under the MyISAM section. The database in question is an
InnoDB database, though. Makes me wonder.
regards,
Markus
--
Markus Hoenicka
markus.hoenicka@cats.de
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org