MySQL query length limit?

[mindstorm8191]

Hi. I am currently working on a rather large undertaking of a project. I have a script that generates a 20x20 game map, with various fields set in it. However, my SQL query doesn't seem to excecute. Here is a summary of my code:

$built = implode(',', array_map(function($long) {
    return implode(',', array_map(function($wide) {
       return '('. $wide['x'] .','. $wide['y'] .','. $wide['landType'] .','. rand(0,13) .','. (randfloat()*1.5+0.5) .',-1,0)';
    }, $long));
},$fullMap));
$db->query("INSERT INTO sw_map (x, y, biome, ugresource, ugamount, civilization, civlevel) VALUES ". $built .";");

After reviewing things, I've determined that the generated string is 36658 characters long. It seems this is the reason that the query doesn't run.

So, I imagine that this limit is necessary for a freely hosted site. Could you share what that limit is? I don't have an easy way to determine that from what I can see in the control panel (unless I try a brute-force script to find an answer... probably not wanted). My code has other sections that drop JSON structures into the database, and I'll have to re-think how these are handled. (I'm doing it this way because the objects are fully managed on the client side, not server-side, so this is just a save & load system).

Thanks

 

[garrettroyce]

I don't see anything in MariaDB (many hosts have switched to MariaDB over MySQL, but they are largely compatible) that would limit you in this regard. Maybe it's a PHP timeout? Here's a quick dump of what I found on my server (x12)

MariaDB [(none)]> show variables like '%max%';
+------------------------------------------+----------------------+
| Variable_name                            | Value                |
+------------------------------------------+----------------------+
| aria_max_sort_file_size                  | 9223372036853727232  |
| extra_max_connections                    | 1                    |
| ft_max_word_len                          | 84                   |
| group_concat_max_len                     | 1048576              |
| innodb_adaptive_max_sleep_delay          | 150000               |
| innodb_change_buffer_max_size            | 25                   |
| innodb_compression_pad_pct_max           | 50                   |
| innodb_ft_max_token_size                 | 84                   |
| innodb_io_capacity_max                   | 2000                 |
| innodb_max_dirty_pages_pct               | 75.000000            |
| innodb_max_dirty_pages_pct_lwm           | 0.000000             |
| innodb_max_purge_lag                     | 0                    |
| innodb_max_purge_lag_delay               | 0                    |
| innodb_max_purge_lag_wait                | 4294967295           |
| innodb_max_undo_log_size                 | 10485760             |
| innodb_online_alter_log_max_size         | 134217728            |
| max_allowed_packet                       | 268435456            |
| max_binlog_cache_size                    | 18446744073709547520 |
| max_binlog_size                          | 1073741824           |
| max_binlog_stmt_cache_size               | 18446744073709547520 |
| max_connect_errors                       | 100                  |
| max_connections                          | 200                  |
| max_delayed_threads                      | 20                   |
| max_digest_length                        | 1024                 |
| max_error_count                          | 64                   |
| max_heap_table_size                      | 16777216             |
| max_insert_delayed_threads               | 20                   |
| max_join_size                            | 18446744073709551615 |
| max_length_for_sort_data                 | 1024                 |
| max_long_data_size                       | 268435456            |
| max_prepared_stmt_count                  | 16382                |
| max_recursive_iterations                 | 4294967295           |
| max_relay_log_size                       | 1073741824           |
| max_seeks_for_key                        | 4294967295           |
| max_session_mem_used                     | 9223372036854775807  |
| max_sort_length                          | 1024                 |
| max_sp_recursion_depth                   | 0                    |
| max_statement_time                       | 0.000000             |
| max_tmp_tables                           | 32                   |
| max_user_connections                     | 15                   |
| max_write_lock_count                     | 4294967295           |
| myisam_max_sort_file_size                | 9223372036853727232  |
| performance_schema_max_cond_classes      | 80                   |
| performance_schema_max_cond_instances    | -1                   |
| performance_schema_max_digest_length     | 1024                 |
| performance_schema_max_file_classes      | 50                   |
| performance_schema_max_file_handles      | 32768                |
| performance_schema_max_file_instances    | -1                   |
| performance_schema_max_mutex_classes     | 200                  |
| performance_schema_max_mutex_instances   | -1                   |
| performance_schema_max_rwlock_classes    | 40                   |
| performance_schema_max_rwlock_instances  | -1                   |
| performance_schema_max_socket_classes    | 10                   |
| performance_schema_max_socket_instances  | -1                   |
| performance_schema_max_stage_classes     | 160                  |
| performance_schema_max_statement_classes | 206                  |
| performance_schema_max_table_handles     | -1                   |
| performance_schema_max_table_instances   | -1                   |
| performance_schema_max_thread_classes    | 50                   |
| performance_schema_max_thread_instances  | -1                   |
| slave_max_allowed_packet                 | 1073741824           |
| slave_parallel_max_queued                | 131072               |
| thread_pool_max_threads                  | 65536                |
| wsrep_max_ws_rows                        | 0                    |
| wsrep_max_ws_size                        | 2147483647           |
+------------------------------------------+----------------------+
65 rows in set (0.001 sec)