query Never complete - Githubissues

brunoleguernic commented 11 years ago

In your example, if you change the main with

example.run().then(() { example.run().then(() { print("K THNX BYE!"); }); pool.close(); }); the 2nd dropping table never completes. In my software, it's an "insert query" that make this. Best regards.

MYSQL 5.5.24 SQLJOCKY 0.8.3 Dart Editor version 1.0.0_r30188 (STABLE) Dart SDK version 1.0.0.3_r30188

jamesots commented 11 years ago

I've made a change so that an exception will be thrown if you try to read from or write to a closed socket. The change will be in the next release, whenever that happens.

brunoleguernic commented 11 years ago

I dont understand because in the sample sent, connection pool is not close. So the socket will be not closed. Some thing wrong in my understand? Le 23 nov. 2013 16:45, "James Ots" notifications@github.com a écrit :

I've made a change so that an exception will be thrown if you try to read from or write to a closed socket. The change will be in the next release, whenever that happens.

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29134657 .

jamesots commented 11 years ago

Because the code is asynchronous, pool.close() gets executed before the second example.run() finishes.

brunoleguernic commented 11 years ago

I hope i was tired yesterday, the close was on the wrong place, I made more tries this morning, and i find what's the problem in my software;

it's the declaration of the pool with the default value: "max:5" like this: var pool = new ConnectionPool(host: host, port: port, user: user, password: password, db: db);//, max:1); function are not completed.
if "max:1" it's good.

so i modify your example to be "similar" with my soft ( if you want see the problem): import 'package:sqljocky/sqljocky.dart'; import 'package:sqljocky/utils.dart'; //import 'package:options_file/options_file.dart'; import 'dart:async';

/*

This example drops a couple of tables if they exist, before recreating them.
It then stores some data in the database and reads it back out.
You must have a connection.options file in order for this to connect. */

class Example { ConnectionPool pool;

Example(this.pool); Future run1(){ var f = new Completer(); modifData(2).then((){ readData().then((){ f.complete(null); }); }); return f.future; } Future run() { var completer = new Completer(); // drop the tables if they already exist dropTables().then(() { print("dropped tables"); // then recreate the tables return createTables(); }).then(() { print("created tables"); // add some data return addData(1); }).then(() { print("added data 1"); // add some data return addData(2); }).then(() { print("added data 1"); // add some data return modifData(1); }).then(() { // and read it back out return readData(); }).then(() { completer.complete(null); }); return completer.future; }

Future dropTables() { print("dropping tables"); var dropper = new TableDropper(pool, ['pets', 'people']); return dropper.dropTables(); }

Future createTables() { print("creating tables"); var querier = new QueryRunner(pool, ['create table people (id integer not null auto_increment, ' 'name varchar(255), ' 'age integer, ' 'primary key (id))',

                                    'create table pets (id integer not

null auto_increment, ' 'name varchar(255), ' 'species text, ' 'owner_id integer, ' 'primary key (id),' 'foreign key (owner_id) references people (id))' ]); print("executing queries"); return querier.executeQueries(); }

Future addData(int Index) { var completer = new Completer(); int NewPeople; LockTables().then((_){ pool.prepare("insert into people (name, age) values ('Dave1$Index', 15)").then((query) { print("prepared query $Index-1"); var parameters = []; return query.execute(parameters); }).then((results) { NewPeople = results.insertId; print("executed query $Index-1: result ID = ${results.insertId}"); return pool.prepare("insert into pets (name, species, ownerid) values (?, ?, ?)"); }).then((query) { print("prepared query $Index-2"); var parameters = [ ["Rover", "Dog", NewPeople], ["Daisy", "Cow", NewPeople], ["Spot", "Dog", NewPeople]]; // ["Spot", "D\u0000og", 2]]; return query.executeMulti(parameters); }).then((results) { print("executed query $Index-2"); return UnlockTables(); }).then((){ completer.complete(null); }); }); return completer.future; } Future modifData(int Index) { var completer = new Completer(); int ExistPeople = Index; LockTables().then((_){ pool.query("UPDATE IGNORE people SET name='toto$Index' WHERE people.id = $ExistPeople ").then((results) { ExistPeople = 1; print("executed query $Index-1: result ID = ${results.insertId}"); return pool.prepare("insert into pets (name, species, ownerid) values (?, ?, ?)"); }).then((query) { print("prepared query $Index-2"); var parameters = [ ["Rover", "Dog", ExistPeople], ["Daisy", "Cow", ExistPeople], ["Spot", "Dog", ExistPeople]]; // ["Spot", "D\u0000og", 2]]; return query.executeMulti(parameters); }).then((results) { print("executed query $Index-2"); return UnlockTables(); }).then((){ completer.complete(null); }); }); return completer.future; } Future readData() { var completer = new Completer(); print("querying"); // pool.prepare('select p.id, p.name, p.age, t.name, t.species ' // 'from people p ' // 'left join pets t on t.owner_id = p.id').then((query) { // return query.execute(); // }).then((result) { pool.query('select p.id, p.name, p.age, t.name, t.species ' 'from people p ' 'left join pets t on t.owner_id = p.id').then((result) { print("got results"); result.listen((row) { if (row[3] == null) { print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, No Pets"); } else { print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, Pet Name: ${row[3]}, Pet Species ${row[4]}"); } }, onDone: () { completer.complete(null); }); }); return completer.future; } Future LockTables(){ var completer = new Completer(); //Lock Table String Query = "LOCK TABLES people WRITE, pets WRITE"; pool.query(Query).then((Result){ print ("LOCK TABLES OK"); completer.complete(null); }).catchError((e){ print ("PB: $Query, $e"); completer.complete(null); }); return completer.future; } Future UnlockTables(){ var completer = new Completer(); //Lock Table pool.query("UNLOCK TABLES").then((Result){ print ("UNLOCK TABLES OK"); completer.complete(null); }).catchError((e){ print ("PB: UNLOCK TABLES NOK, $e"); completer.complete(null); }); return completer.future; } }

void main() { OptionsFile options = new OptionsFile('connection.options'); String user = options.getString('user'); String password = options.getString('password'); int port = 3306; String db = 'test'; String host = 'localhost';

// create a connection print("opening connection"); var pool = new ConnectionPool(host: host, port: port, user: user, password: password, db: db);//, max:1); print("connection open"); // create an example class var example = new Example(pool); // run the example print("running example"); example.run().then(() { // finally, close the connection example.run1().then(() { print("K THNX BYE!"); pool.close(); }); }); }

2013/11/23 James Ots notifications@github.com

I've made a change so that an exception will be thrown if you try to read from or write to a closed socket. The change will be in the next release, whenever that happens.

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29134657 .

Tel: +33 (0)7 77 79 77 76

jamesots commented 11 years ago

Ah, it looks like it's because you're locking tables. I didn't think of that — I'll have to add the ability to get a persistent connection. The problem at the moment is that when you execute the LOCK query on the pool then it picks the next available connection to run the query on. However, the next query you run might be run on a different connection, and the UNLOCK might also not run on that connection.

At the moment, the only way to be guaranteed that you keep the same connection is by using a transaction, but it might be necessary for me to add a 'getConnection()' method to the connection pool, which would return a QueriableConnection object, similar to the Transaction object, with a 'release()' method on it.

jamesots commented 11 years ago

I've implemented this in the latest version (v0.9.0)

brunoleguernic commented 11 years ago

Thank you, I will try tomorrow.

2013/11/25 James Ots notifications@github.com

I've implemented this in the latest version (v0.9.0)

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29208727 .

Tel: +33 (0)7 77 79 77 76

brunoleguernic commented 11 years ago

Only a little problem, when i release a connection yet released, i didn't see a property that informs the state of the connection, and the server breaks down (catch or future error doesnt work). NB: the saving seems very much faster...good news.

2013/11/25 James Ots notifications@github.com

I've implemented this in the latest version (v0.9.0)

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29208727 .

Tel: +33 (0)7 77 79 77 76

jamesots commented 11 years ago

Can you reword that? I don't understand what you're saying the problem is.

brunoleguernic commented 11 years ago

My function that doesn't work is:

Future Unlocktables(var cnxRetainedDB){ //////////// I PASS THIS FUNCTION WHEN AN ISOLATE LEAVES THE SERVER var completer = new Completer(); if(cnxRetainedDB == null){ completer.complete(null); return completer.future; } //UnLock Table cnxRetainedDB.query("UNLOCK TABLES").then((Result){ //print ("UNLOCK TABLES OK"); if(!cnxRetainedDB.released) ///////////////////////////////////// released NOT EXISTS cnxRetainedDB.release();//////////////////////////////////AND THIS LINE FAILS if my cnxRetainedDB yet released! completer.complete(null); }).catchError((e){ print ("UNLOCK TABLES NOK, $e"); completer.complete(null); }); return completer.future; }

2013/11/26 James Ots notifications@github.com

Can you reword that? I don't understand what you're saying the problem is.

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29284427 .

Tel: +33 (0)7 77 79 77 76

brunoleguernic commented 11 years ago

My function that doesn't work is:

Future Unlocktables(var cnxRetainedDB){ //////////// I PASS THIS FUNCTION WHEN AN ISOLATE LEAVES THE SERVER var completer = new Completer(); if(cnxRetainedDB == null){ completer.complete(null); return completer.future; } //UnLock Table cnxRetainedDB.query("UNLOCK TABLES").then((Result){ //print ("UNLOCK TABLES OK"); if(!cnxRetainedDB.released) ///////////////////////////////////// released NOT EXISTS cnxRetainedDB.release();//////////////////////////////////AND THIS LINE FAILS if my cnxRetainedDB yet released! completer.complete(null); }); return completer.future; }

2013/11/26 James Ots notifications@github.com

Can you reword that? I don't understand what you're saying the problem is.

— Reply to this email directly or view it on GitHubhttps://github.com/jamesots/sqljocky/issues/30#issuecomment-29284427 .

Tel: +33 (0)7 77 79 77 76

dart-drivers / mysql

query Never complete #30