Custom error on memory table overflow

[bmschmidt]

Currently an error can be thrown that looks like this:

 File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1114, "The table 'tmp' is full")
make[1]: *** [.bookworm/targets/database_metadata] Error 1

The error should be wrapped in text describing the problem and its solution.

It means that the mysql configuration limits on metadata are too low; the solution is to run bookworm config msyql, or to manually update my.cnf to recommended values. If the problem persists, we're likely talking about a bookworm with tens of millions of items of metadata or extraordinarily long fields.

[jonathandfitzgerald]

Hi Ben, I ran into this exact error. I tried increasing the values in my.cnf to no avail. (I eventually made the log files so large that it ate up all the storage on my computer.) Also, bookworm config mysql throws errors too.

[bmschmidt]

What happens if you put SHOW VARIABLES LIKE 'tmp_table_size'; into the mysql prompt? Sometimes it can be hard to get MySQL to read the global my.cnf on a Mac. (And have you restarted the MySQL server after changing it?)

And what is the content of your my.cnf?

MariaDB [(none)]> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| tmp_table_size | 1073741824 |
+----------------+------------+
1 row in set (0.00 sec)

[jonathandfitzgerald]

Here's what I get when I show variables. It would appear that my table size is significantly smaller than yours...

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

Here's what's in my.cnf...

[client]

user = 'www-data'
password = 'password'
host = 'localhost'
[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

The log file size at 1G is because I freaked out after it ate up all my storage.

But the bigger problem is I'm beginning to think that my whole environment is not set up quite right. I know a lot more now than I did when I was getting this set up. For example, I have 2 versions of mysql, which is problematic. And I didn't install it using brew, which is how I installed most everything else. I'm wondering if I shouldn't just scrap it all and start again.

[bmschmidt]

If you don't have the space for the logfiles, this may be a non-starter; you're going to need 10s of GB for this.

But: here's the solution. Your [mysqld] section needs some additional parameters. So if you could paste or e-mail me the error on bookworm config mysql that would be useful; but what you want to do is add the following lines to the end of yours and then restart the server.

[client]
max_allowed_packet=1073741824

[mysqld]
max_allowed_packet=1073741824
myisam_sort_buffer_size = 512M
read_rnd_buffer_size = 8M
read_buffer_size = 4M
max_heap_table_size = 1024M
tmp_table_size = 1024M

character_set_server = utf8
query_cache_size = 128M
query_cache_type = 1
query_cache_limit = 2M

bulk_insert_buffer_size = 512M
myisam_max_sort_file_size = 1500G
sort_buffer_size = 8M

key_buffer_size=1500M

[jonathandfitzgerald]

Great! I’ll try this. In the meantime, here’s the error I get on bookworm config mysql:

Traceback (most recent call last): File "/usr/local/bin/bookworm", line 9, in <module> load_entry_point('bookwormDB==0.4.0', 'console_scripts', 'bookworm')() File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/manager.py", line 555, in run_arguments getattr(my_bookworm,args.action)(args) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/manager.py", line 72, in config bookwormDB.configuration.reconfigure_passwords(args.users,args.force) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/configuration.py", line 281, in reconfigure_passwords update_settings_for(name) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/configuration.py", line 244, in update_settings_for system.change_client_password() File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/configuration.py", line 170, in change_client_password cur.execute("SET PASSWORD FOR '%s'@'localhost'=PASSWORD('%s')" % (user,new_password)) File "/Library/Python/2.7/site-packages/MySQLdb/cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "/Library/Python/2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'www-data''@'localhost'=PASSWORD(''password'')' at line 1") Traceback (most recent call last): File "/usr/local/bin/bookworm", line 9, in <module> load_entry_point('bookwormDB==0.4.0', 'console_scripts', 'bookworm')() File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/manager.py", line 555, in run_arguments getattr(my_bookworm,args.action)(args) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/manager.py", line 72, in config bookwormDB.coxnfiguration.reconfigure_passwords(args.users,args.force) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/configuration.py", line 281, in reconfigure_passwords update_settings_for(name) File "/Library/Python/2.7/site-packages/bookwormDB-0.4.0-py2.7.egg/bookwormDB/configuration.py", line 239, in update_settings_for admin = Configfile("admin",[default_cnf_file_location],default=default_cnf_file_location) NameError: global name 'default_cnf_file_location' is not defined

[jonathandfitzgerald]

Good news! I got it to make without errors, but when I attempt to serve it, the webpages load, but do not work. Neither page shows a visualization and no searches work. So close!