MySQL query length limit?

Discussion in 'Free Hosting' started by mindstorm8191, Apr 25, 2021.

  1. mindstorm8191

    mindstorm8191 New Member

    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    3
    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:

    PHP:
    $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
     
  2. garrettroyce

    garrettroyce Community Support Community Support

    Messages:
    5,462
    Likes Received:
    227
    Trophy Points:
    63
    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)

    Code:
    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)
    
     

Share This Page