Can't create more than max_prepared_stmt_count statements (current value: 16382)

[chrisdew]

I get this error when I leave my app running for 10 minutes. During this time it probably does a few thousand queries.

auto_prepare = true;.

I'm using NodeJS 2.6 and mysql-native@0.4.1

Is anyone else experiencing a similar issue?

Thanks,

Chris.

[chrisdew]

If I use

auto_prepare = false;

I get an error immediately. Note that I have added an error handler to the query, but is it not called.

events:14
        throw new Error("Uncaught, unspecified 'error' event.");
              ^
Error: Uncaught, unspecified 'error' event.
    at EventEmitter.emit (events:14:15)
    at cmd.emit (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/commands.js:127:16)
    at cmd.start (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/commands.js:351:21)
    at cmd.process_packet (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/commands.js:144:43)
    at [object Object].dispatch_packet (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/client.js:126:37)
    at [object Object].add (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/client.js:147:22)
    at [object Object].execute (/usr/local/lib/node/.npm/mysql-native/0.4.1/package/lib/mysql-native/client.js:108:25)
    at fooFindOrCreate (/srv/svlp_adapter/svlp_adapter.js:189:20)
    at Object.line (/srv/svlp_adapter/svlp_adapter.js:111:24)
    at Stream.<anonymous> (/srv/svlp_adapter/persistent_connection.js:64:36)

[sidorares]

could you show the code? At the moment there is no way to release PS, and they are not freed automatically. I can't imagine situation where you need 16k prepared statements. If for some reason you prefer to incorporate data in the query and not pass it as a parameter you can 'quiery()' method

[chrisdew]

Thanks for your response. You may be able to look at this and immediate tell me how I am abusing the API. The error always occurs, and occurs always at query2.

DEBUG: insert_into_foo error: {"field_count":255,"errno":1461,"message":"Can't create more than max_prepared_stmt_count statements (current value: 16382)"}

I'm currently modifying this code to add further debugging.

Thanks,

Chris.

P.S. The error never occurs on my dev machine, but it always occurs on the production machine. I am investigating all of the differences.

/**
 * This creates a new foo_id.
 * 
 * The code here is rather horrid.  In order to execute the insert into the
 * foo_type_ipv4 table it has to be nested in the 'end' event of the first 
 * insert.
 * 
 * @param {string} callsign
 * @param {function} callback
 */
function fooCreate(callsign, callback) {
    var db = db_conn.db;
    try {
        var query = db.execute( "INSERT INTO foo( active "
                                             + ", type_id "
                                             + ", organisation_hint_id "
                                             + ", ts_created "
                                             + ", ts_updated "
                                             + ")"
                              + "VALUES (?, ?, ?, NOW(), NOW()) "
                              + ";"
                              , [ 1
                                , conf.db.foo_type
                                , conf.db.org_hint_id
                                ] 
                              ) ;
        query.addListener('error', function(error) {
            sys.debug("insert_into_foo error: " + JSON.stringify(error));
            process.exit(1);
        });
                              
        query.addListener('end', function(stats) {
            sys.debug("foo insert completed: " + stats.insert_id);
            var query2 = db.execute( "INSERT INTO " + conf.db.foo_type_table + " "
                                   + "SET foo_ptr_id = ? "
                                   + ", " + conf.db.callsign_field + " = ? "
                                   + ";"
                                   , [stats.insert_id, callsign]
                                   ) ;
            query2.addListener('error', function(error) {
                sys.debug("insert_into_foo_type error: " + JSON.stringify(error));
                process.exit(1);
            });
            query2.addListener('end', function() {
                process.stats.foo_creates++;
                sys.debug("" + conf.db.foo_type_table + " insert completed");
                callback();
            });
        });
    } catch(ex) {
        callback(ex);
    }   
}

[chrisdew]

Did I accidentally close is the issue? How can I reopen it?

Thanks,

Chris.