MySQL Bugs: #35346: max_join_size default value

Bug #35346	max_join_size default value
Submitted:	17 Mar 2008 17:27	Modified:	6 Dec 2010 19:56
Reporter:	Gilles Leprince	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.54/5.0.56 GPL, 5.1.25	OS:	Any (RHEL 4 update 5, Ubuntu, Windows, FreeBSD)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	max_binlog_cache_size, Max_join_size, max_seeks_for_key, max_write_lock_count, myisam_max_sort_file_size

Description:
Hi there,

On a default installation of 5.0.56 GPL (or 5.0.54 GPL), default parameter for max_join_size is "18446744073709551615" if no size specified in the config file.

How to repeat:
do a fresh install of mysql 5.0.56 without a config file then do a show global variables like 'max_join_size' it should return :

+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 | 
+---------------+----------------------+

Suggested fix:
It can be overriden via the option max_join_size of the config file, if I set it to 4M and I start the server, the value of 4194304 is reported by the show variables command.

Thank you for the bug report.

[miguel@mira dbs]$ 5.0/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like 'max_join_size';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 | 
+---------------+----------------------+
1 row in set (0.01 sec)

mysql> 

mysql> show global variables like 'max_join_size' ;
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| max_join_size | 4294967295 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.51a-community-nt |
+----------------------+
1 row in set (0.00 sec)

mysql>

c:\dbs>5.0\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.56-nt-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like 'max_join_size';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.08 sec)

mysql>

5.0.45 community has the bug too.

This bug also affects 4 other variables as well: max_binlog_cache_size, max_seeks_for_key, max_write_lock_count, and myisam_max_sort_file_size (note this last one has an even higher value).

mysql> show global variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709551615 | 
+-----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_seeks_for_key';
+-------------------+----------------------+
| Variable_name     | Value                |
+-------------------+----------------------+
| max_seeks_for_key | 18446744073709551615 | 
+-------------------+----------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_write_lock_count';
+----------------------+----------------------+
| Variable_name        | Value                |
+----------------------+----------------------+
| max_write_lock_count | 18446744073709551615 | 
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'myisam_max_sort_file_size';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036854775807 | 
+---------------------------+---------------------+
1 row in set (0.00 sec)

Note that the above was tested on the latest Community version - 5.0.51.

This bug also affects Windows as well, though not the exact same.

It only affects 2 of the above variables: max_join_size and myisam_max_sort_file_size.

Also, the value that myisam_max_sort_file_size gets is different that what I saw on Ubuntu (and also different from the default).  (possibly because my Windows is 32-bit and the Ubuntu is running 64-bit?)

Anyways, here is the output from Windows (latest Enterprise 5.0.62):

mysql> show variables like 'max_join_size';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'myisam_max_sort_file_size';
+---------------------------+--------------+
| Variable_name             | Value        |
+---------------------------+--------------+
| myisam_max_sort_file_size | 107374182400 |
+---------------------------+--------------+
1 row in set (0.00 sec)

Also verified on 5.1.25.

While the incorrect default value itself may not be too serious, depending on how these values get used by MySQL one has to wonder what bad behavior these large, bogus values might cause.

Bug #39093 was set as duplicate of this bug here

Definitive analysis solution aggregated from https://bugs.launchpad.net/ourdelta/+bug/284123 (OurDelta):

Quick fix:
Put the following at the start of include/my_base.h
#undef BIG_TABLES
#define BIG_TABLES 1

Proper solution:
This define was relevant when many os had 32bit file size off_t (32-bit file pointers)
With a small number of careful changes, the define can go away completely.

Further background:
This bug occurs because parts of the server are compiled with BIG_TABLES define set, other parts without... Or at least, when include/my_base.h was read, the setting of BIG_TABLES was different. So on some files, it is a 32bit quantity, others it is a 64bit quantity... It is even somewhat surprising this hasn't lead to a (known) crash because there are some pointers stored in struct system_variables.

Additional conclusion from the above analysis: perhaps upgrade this bug to more serious, as it could be causing unexplained crashes.

As a case in point, other components like myisamcheck also throw the error, indicating that they too are compiled with the wrong setting. The only clean way to fix this is to get rid of the define, replacing what it does throughout the sourcecode.

See also info and tools to check this on platforms at http://ac-archive.sourceforge.net/largefile/

Apart from the BIG_TABLES define fixup in include/my_base.h the following can be applied to variables in the my_options list in mysqld.cc which relate to a # of rows, and which curently have a max_value of ~0L. Change this to ~ (ha_rows) 0
That will work regardless of the BIG_TABLES being set, and it will be correct as it's the same type as the variable it's put in to.
This does need to be checked for each individual server variables, as it depends on what type they are.

Note that for variables that are of type ulonglong, ULONG_MAX should not be used but rather LONG_MAX, as comparisons might be done signed.
For ulong, perhaps set to LONG_MAX otherwise it might still go wrong on some architectures (ulong is 32bit even on 64bit windows)

Note that this patch will be applied in OurDelta (http://ourdelta.org) builds for MySQL 5.0 with patchset d7 or above, and any builds for other series (5.1, 6.0).

--- mysql-5.0.67.orig/include/my_base.h
+++ mysql-5.0.67/include/my_base.h
@@ -41,6 +41,15 @@
 #endif /* stdin */
 #include <my_list.h>
 
+/*
+  Clean workaround for inconsistent BIG_TABLES define issue
+  https://bugs.launchpad.net/ourdelta/+bug/284123
+  http://bugs.mysql.com/35346
+  Author: Antony Curtis, patch created by Arjen Lentz
+*/
+#undef BIG_TABLES
+#define BIG_TABLES 1
+
 /* The following is bits in the flag parameter to ha_open() */
 
 #define HA_OPEN_ABORT_IF_LOCKED                0       /* default */

There is no regression in the observed behavior. The variable has had the default value of 18446744073709551615 since it was added.

Indeed, the default is inconsistent and is not documented.
But since changing it is an incompatible change, it's safer to do in 6.0.

This also affects myisamchk. The following variables 

key_buffer_size
read_buffer_size
write_buffer_size
sort_buffer_size

are set to the illegal default of 18446744073709551615 and thus generate warning messages.

Given that the cause of this bug has apparently been thoroughly analysed, and it is a problem that could quite possibly cause arbitrary undefined behaviour, why hasn't it been fixed by now?

On further analysis, my opinion is that the people claiming that the problem is some files being compiled with BIG_TABLES and some without are mistaken. It appears to be solely a problem in the my_getopt option processing layer, and hence purely cosmetic in nature.

Given that it does in fact appear to be trivial and cosmetic, yet unduly scary, why not get it fixed in the 5.0 and 5.1 branches?

@Max: the OurDelta fix was created by Antony Curtis, formerly a core dev at MySQL. With the #define BIG_TABLES 1 macro fixed, the warnings don't occur on 32bit builds.
Since builds should always enable BIG_TABLES, the fact that a build without the abovementioned fix shows the warnings is already indication that some files don't get that define from the build environment, right?

@Arjen:

> the OurDelta fix was created by Antony Curtis, formerly a core dev at MySQL.

Even core-devs make mistakes sometimes.

> With the #define BIG_TABLES 1 macro fixed, the warnings don't occur on
> 32bit builds. Since builds should always enable BIG_TABLES,

Can you explain why you believe that builds should *always* enable BIG_TABLES? If that were the case, why is it an option at all?

> the fact that a build without the abovementioned fix shows the warnings is
> already indication that some files don't get that define from the build
> environment, right?

No, it merely indicates the problem is masked by coincidence when BIG_TABLES is defined.

I believe you correctly diagnosed the problem in your comment of [21 Oct 2008 7:41], except you stated it as an additional problem, whereas I believe the option defaults/limits that you cite there are actually the only problem.

i.e., the problem is *not* that some part of the build has BIG_TABLES and another part does not - it's that the default values in the my_option structs need to take account of the BIG_TABLES definition but do not currently do that.

In corroboration of this: my build with BIG_TABLES defined *nowhere* shows the mentioned warning messages.

Pushed into 5.0.85 (revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (version source revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (merge vers: 5.0.85) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:kristofer.pettersson@sun.com-20090731070848-zd38c7u5xaofh8hl) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

Bug #56204 was marked as a duplicate of this one.

On my Windows 5.1.52 win32 and win64 builds, the only difference in values
of 'show global variables' is myisam_mmap_size, which imho, is expected.

myisamchk shows now warnings either.
So, what is the bug in modern versions of mysql, 5.0.91, 5.1.52, 5.5.6 ?

Closed as a duplicate of Bug#46385.