max_prepared_stmt_count

[FrancisVarga]

events.js:45
        throw arguments[1]; // Unhandled 'error' event
                       ^
Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)
    at cmd.process_packet (/Users/francis/node_modules/mysql-native/lib/mysql-native/command.js:35:15)
    at SocketClient.dispatch_packet (/Users/francis/node_modules/mysql-native/lib/mysql-native/socketclient.js:105:32)
    at Socket.<anonymous> (/Users/francis/node_modules/mysql-native/lib/mysql-native/socketclient.js:59:18)
    at Socket.emit (events.js:64:17)
    at Socket._onReadable (net.js:673:31)
    at IOWatcher.onReadable [as callback] (net.js:177:10)

how i can remove statements? i write currently a exporter script from mysql to redis, couchdb and cassandra... the database has 1Mio+ rows...

[sidorares]

there is currently no implementation for 'delete statement' command. Statements are freed on reconnect. You could try to implement it yourself (it's relatively easy to add new commands and command itself is simple - send 'delete' code and two-bytes handle) or wait for me implementing it. Feel free to ask for help if you decide to do it yourself

[FrancisVarga]

or i terminate the connection and create a new client?! is that possible?!

[sidorares]

yes, when connection is closed server frees all statements allocated for this connection

could you explain your case? do you have lots of short-lived statements? The reason for still not implementing 'destroy statement' command is that I usually have two type of queries: 1) no parameters, on-time query 2) input data, used through appication lifetime. for 1) i use .query(), for 2) - execute()

Said that, I'm happy to implement 'destroyStatement' command at some time

[FrancisVarga]

my use case is currently import 1Mio+ user data into a redis database...
and later 10Mio+ transaction data into couchdb :)...

[sidorares]

statements are prepared on first execution and cached (e.i there should be no more prepared statements than types of query, not number of rows) Could you show your code?

[sidorares]

I created separate issue - #39

[FrancisVarga]

/**
 * Created by JetBrains PhpStorm.
 * User: francis
 * Date: 7/12/11
 * Time: 10:33 PM
 * To change this template use File | Settings | File Templates.
 */


var redisClient;
var redisModule;
var mysqlDBClient;
var currentRow = 0;
var sys;
var mysqlDBName = "myDB";
var mysqlDBUser = "root";
var mysqlDBPW = "root";
var currentTable = "users";

function init() {

    sys = require('sys');

    mysqlDBClient = getDBClient();

    redisModule = require('redis-node');
    redisClient = redisModule.createClient();
    redisClient.select("crowdpark_users");

    sys.log("START");

    redisClient.flushdb(fetchMySQLData);
}

function fetchMySQLData() {
    var sqlStmt = "select * from " + currentTable + " limit " + currentRow + ", 1;";
    currentRow++;

    //doesnt catch the error in mysql-native
    try {
        mysqlDBClient = getDBClient();
        mysqlDBClient.execute(sqlStmt).addListener("row", onUsersDataFetched);
    } catch(error) {
        sys.log(currentRow);
        sys.log("END");
    }

}

function getDBClient() {
    var client = require("mysql-native").createTCPClient(); // localhost:3306 by default
    client.auto_prepare = true;
    client.auth(mysqlDBName, mysqlDBUser, mysqlDBPW);

    return client;
}

function onUsersDataFetched(rows) {
    var id = rows.id;
    delete rows.id;

    if (!rows) {
        sys.log("END");
        exit;
    }

    var jsonString  = JSON.stringify(rows);

    redisClient.set(id, jsonString, function (err, status) {

        if (err) {
            throw err;
        } else {
            delete rows;
            mysqlDBClient.close();
            mysqlDBClient.terminate();
            delete mysqlDBClient;
            fetchMySQLData();
        }

    });
}

init();

[sidorares]

on my opinion there are lots things you doing wrong

1. the whole point of prepared statements is to have parametrised statement, prepare it and then only pass parameters:

var sqlStmt = "select * from " + currentTable + " limit " + currentRow + ", 1;";

currentTable and currentRow are obvious parameters here.

 var stmt =  "select * from ? limit ?,1";
 mysqlDBClient.execute(stmt, currentTable, currentRow)on('row', function(r) {
     // do something with each row
 });

this is the source of original problem: yes, you trying to have LOTS of prepared statemts while you really need only one

2. you executing one query per row - why? Did you try to request whole data? mysql client does not store whole result so there is not a problem to process 'select *' even for millions of rows

try {
    mysqlDBClient = getDBClient();
    mysqlDBClient.execute(sqlStmt).addListener("row", onUsersDataFetched);
}

• you don't catch error here because it doesent happen here. When you doing mysqlDBClient.somecommand(params) all what happens in this statement is that command is put in the queue and processed later in the event loop (this is pretty much same for all I/O commands)

4. mysqlDBClient.close(); puts 'close' command at the end of queue, mysqlDBClient.terminate(); immediatly terminates client discarding queue. It makes no sence to have both of them

5. you don't need to delete rows and client

[FrancisVarga]

Hi, thx for your time... i solve many problems with your tips and infos... thx a lot...