dart-db / sqljocky5 Goto Github PK

I have a Dart Web server that should keep running, even if the MySQL/MariaDB cannot be contacted (e.g. it is restarted for maintenance or there are intermittent network issues). When the database becomes available again, the program should be able to recover and continue running.

Unfortunately, there is at least one situation where sqljocky5 v2.2.1 throws an exception that terminates the Dart program. I have found no way to catch the exception to ignore it.

It happens in this scenario:

1. A connection to the database is successfully opened.
2. A transaction is successfully created on that connection.
3. The database somehow stops running.
4. An attempt to run a query on the transaction and the exception is raised.

The program prints the following and exits:

Unhandled exception:
SocketException: Write failed (OS Error: Broken pipe, errno = 32), address = database.example.com, port = 62372
#0      _rootHandleUncaughtError.<anonymous closure> (dart:async/zone.dart:1112:29)
#1      _microtaskLoop (dart:async/schedule_microtask.dart:41:21)
#2      _startMicrotaskLoop (dart:async/schedule_microtask.dart:50:5)
#3      _runPendingImmediateCallback (dart:isolate/runtime/libisolate_patch.dart:115:13)
#4      _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:172:5)

I have written a demo program to test this scenario (and recovery from other failure modes):
https://github.com/hoylen/sqljocky5-recovery

hi,
support for caching_sha2_password also added, but not published to pub.
can you do that?

btw: thx a lot for this package :)

greetings

When running an app using SQLJocky5 I get this error eventually (Maybe the server went down or something)

Unhandled exception:
type 'SocketException' is not a subtype of type 'AsyncError'
#0      BufferedSocket._onSocketError (package:sqljocky5/comm/buffered_socket.dart:48:15)
#1      _RootZone.runUnaryGuarded (dart:async/zone.dart:1314:10)
#2      _BufferingStreamSubscription._sendError.sendError (dart:async/stream_impl.dart:358:15)
#3      _BufferingStreamSubscription._sendError (dart:async/stream_impl.dart:370:18)
#4      _BufferingStreamSubscription._addError (dart:async/stream_impl.dart:272:7)
#5      _SyncStreamController._sendError (dart:async/stream_controller.dart:767:19)
#6      _StreamController._addError (dart:async/stream_controller.dart:647:7)
#7      _StreamController.addError (dart:async/stream_controller.dart:599:5)
#8      new _RawSocket.<anonymous closure> (dart:io/runtime/binsocket_patch.dart:1296:23)
#9      _RootZone.runUnaryGuarded (dart:async/zone.dart:1314:10)
#10     _RootZone.bindUnaryCallbackGuarded.<anonymous closure> (dart:async/zone.dart:1353:26)
#11     _NativeSocket.reportError (dart:io/runtime/binsocket_patch.dart:1067:32)
#12     _NativeSocket.multiplex (dart:io/runtime/binsocket_patch.dart:905:13)
#13     _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:171:12)

Caused by

typedef ErrorHandler(AsyncError err);

being used here

  BufferedSocket._(this._socket, this.onDataReady, this.onDone, this.onError,
      this.onClosed) {
    _subscription = _socket.listen(_onData,
        onError: _onSocketError, onDone: _onSocketDone, cancelOnError: true);
  }

  _onSocketError(error) {
    if (onError != null) {
      onError(error);
    }
  }

there is the possibility to implement a mappedResultsQuery method just like in the postgres package to return rows as maps containing table and column names:

https://pub.dev/packages/postgres

List<Map<String, Map<String, dynamic>>> results = await connection.mappedResultsQuery(
  "SELECT t.id, t.name, u.name FROM t LEFT OUTER JOIN u ON t.id=u.t_id");

for (final row in results) {
  var tID = row["t"]["id"];
  var tName = row["t"]["name"];
  var uName = row["u"]["name"];
}

Is there an example to show me use of Transactions?

Transaction trans = await pool.begin();
try {
var result1 = await trans.execute('...');
var result2 = await trans.execute('...');
await trans.commit();
} catch(e) {
await trans.rollback();
}

Transaction trans = await pool.begin();

What is "pool" at the first line of this?
What is "Transaction" declaration?

thanks

When connected to a "bad server", trying to prepare a query crashes the program. No exception is raised, so the program cannot detect the problem.

A "bad server" is one that does not respond as a properly working MySQL server. For example, if the socket connection closes after returning malformed data or no data at all.

This problem was first noticed when the MySQL server was accessed by SSH port forwarding: the port forwarding was established, but the MySQL server was not running. This is different from if trying to directly connect to a MySQL service that wasn't running (in which case a SocketException is correctly raised). Other situations where the problem would occur is if the client was misconfigured and connected to a non-MySQL port: in which case the client would want to print out an error message rather than crashing/exiting.

Expected behaviour: an exception is thrown by sqljocky5. (This is what the old sqljocky 0.14.0 package did).

What actually happens: the program exits/terminates with an exit status of zero (which is misleading).

How to reproduce the problem:

Instead of connecting to a real mysql service, start a service that listens on a port and immediately closes any socket connections it receives. This can be done with the netcat command:

while true; do echo -n | nc -l 3306; date; done

This test program:

import 'dart:async';
import 'dart:io';

import 'package:logging/logging.dart';
import 'package:sqljocky5/sqljocky.dart';

Future main(List<String> arguments) async {
  hierarchicalLoggingEnabled = true;
  Logger.root.level = Level.ALL;
  Logger.root.onRecord.listen((LogRecord r) {
    print("${r.time}: ${r.loggerName}: ${r.message}");
  });

  try {
    final dbPool = new ConnectionPool(
        host: "localhost",
        port: 3306,
        user: "test",
        password: "p@ssw0rd",
        db: "testdb",
        useSSL: false,
        max: 5);

    print("Before prepare");
    final query = await dbPool.prepare("SELECT TIMEDIFF(NOW(),UTC_TIMESTAMP)");
    print("After prepare"); // this line is never reached

    final result = await query.execute(<Object>[]);
    Duration databaseTzOffset;
    await result.forEach((row) {
      databaseTzOffset = row[0] as Duration;
    });
    assert(databaseTzOffset != null);

    final localTzOffset = new DateTime.now().timeZoneOffset;

    if (databaseTzOffset == localTzOffset) {
      print("ok: timezones match");
    } else {
      print("error: timezones mismatch");
    }

    dbPool.closeConnectionsWhenNotInUse();

  } on SocketException catch(e) {
    print("Exception:\n  $e\n");
    exit(1);
  }

  print("Done");
}

Produces this output:

Before prepare
2018-02-23 17:23:34.613640: Query: Getting prepared query for: SELECT TIMEDIFF(NOW(),UTC_TIMESTAMP)
2018-02-23 17:23:34.618559: ConnectionPool: Getting a connection
2018-02-23 17:23:34.618968: ConnectionPool: Number of in-use connections: 0
2018-02-23 17:23:34.619277: ConnectionPool: Creating new pooled cnx#0
2018-02-23 17:23:34.622794: Connection.Lifecycle: Use connection #0
2018-02-23 17:23:34.627722: Connection: opening connection to localhost:3306/testdb
2018-02-23 17:23:34.628966: Connection: hoylen: connection: after BufferedSocket.connect
2018-02-23 17:23:34.659319: BufferedSocket: READ_CLOSED
2018-02-23 17:23:34.661234: BufferedSocket: CLOSED
2018-02-23 17:23:34.663046: Connection.Lifecycle: Release connection #0
2018-02-23 17:23:34.663186: Connection: done

Notice that it does not print out "After prepare", "Exception" or "Done". It simply exits with an exit status of zero.

Note: unit tests are not very useful for testing this. The test will timeout.

I've only managed to trace the problem as far as line 112 of connection_pool_impl.dart. The call to cnx.connect does return a Future, but the program dies/exits/crashes while trying to await for that future. The line that says cnx.autoRelease = true is never reached.

  _createConnection(Completer c) async {
    var cnx = new Connection(this, _pool.length, _maxPacketSize);
    cnx.use();
    cnx.autoRelease = false;
    _pool.add(cnx);
    try {
      await cnx.connect(
          host: _host,
          port: _port,
          user: _user,
          password: _password,
          db: _db,
          useCompression: _useCompression,
          useSSL: _useSSL);
      cnx.autoRelease = true;
      _log.finest("Logged in on cnx#${cnx.number}");
      c.complete(cnx);
    } catch (e, st) {
      if (!(e is MySqlException)) {
        removeConnection(cnx);
      }
      c.completeError(e, st);
    }
  }

Hi,

How can I get if a query returned a warning? ie:

    CREATE TABLE IF NOT EXISTS users
    (
        username varchar(32) PRIMARY KEY UNIQUE,
        password varchar(32),
    );

could return a warning if the table already exists how can I get that?

Android Studio show me the mistake. I'm a rookie! So......

The name 'Row' is defined in the libraries 'package:flutter/src/widgets/basic.dart' and 'package:sqljocky5/results/row.dart'.

The connection close methods (including closeConnectionsWhenNotInUse and closeConnectionsNow on ConnectionPool) should send a MySQL "QUIT" message to the server before closing the network socket connection. And they probably should be asynchronous methods, returning a Future that completes when the connection(s) have fully closed.

Currently, it simply closes the socket connection, which causes the MySQL/MariaDB server to treat it as an unclean disconnection, filling the logs with warnings like:

Aborted connection ... to db: ... (Got an error reading communication packets)

This is seen with MariaDB 10.2.15 when logging is enabled with the following in /etc/my.cnf:

[mysqld]
log_error=/var/log/mariadb/mariadb.log
# log-warnings=2

These warnings were not noticed in MariaDB 5.5, so it seems the default logging behaviour has changed. These warnings can be suppressed by setting the log-warnings level to zero or one, but obviously that will affect other warnings/error messages that one wants to keep.

There is a QuitHandler class, but it is not used anywhere.

P.S. It is nice that sqljocky5 does have closeConnectionsWhenNotInUse and closeConnectionsNow methods for connection pools. Libraries in some other languages don't have a nice way to disconnect all the connections in connection pools.

What is the best way to clean up after a TimeoutException is thrown when running an SQL query?

Problem

If you have code like this:

Transaction tx = ...;
try {
  await tx.prepared(sql1, params1);
  // Do something with the results that might raise an exception.
  await tx.prepared(sql2, params2); // what if this timeout?
  // Do something with the results that might raise an exception.
} finally {
  await tx.rollback();
}

The call to rollback throws a MySqlClientError saying: MySQL Client Error: Connection cannot process a request for QueryStreamHandler(rollback) while a request is already in progress for Instance of 'ExecuteQueryHandler'.

This is because the transaction/connection is still running the query when rollback is invoked. This can be demonstrated by adding a long delay (e.g. await new Future<void>.delayed(const Duration(seconds: 120));) before the rollback to allow the query to finish. But that is not a very practical solution.

Solution

I suspect sqljocky5 needs to deliberately terminate the running query, before it throws the timeout exception.

You don't want to just skip doing the rollback, because you want to rollback any changes made by query sql1 or if the application code had thrown an exception. You also don't want to simply discard or ignore the transaction/connection after getting a timeout: you want to reuse it when implementing a connection pool; or you want to cleanly close it so you don't get too many transactions/connections kept open. While calling exit to clean up is a solution for short running programs, for long running programs (e.g. Web servers) that is not a solution.

An interim workaround is to provide a larger timeout duration when connecting to the database, so query timeouts are avoided. But that is not always practical and has other consequences.

I'm using sqljocky5 with SSL connections to the database, and have some suggestions/feature requests:

1. Reduce the logging level. In lib/src/auth/handshake_handler.dart line 135, it performs some logging with log.shout("SSL enabled"); which tends to fill the logs with unnecessary entries. It would be better if that was a fine, finer, or finest level log entry.

2. Provide hooks for a client program to perform additional security checks. For example,

   a. Validating the server/database certificate (as well as the trust chain of CA certificates).
   b. Checking the domain name inside the certificate matches the expected database host.
   b. Performing other checks, such as if the certificate has expired.

3. The ability to specify (or obtain) the version of SSL/TLS and ciphers used.

final trans = await conn.begin();
  try {
    await trans.preparedWithAll(sql, values);
    await trans.commit();
  } on MySqlException catch (_) {
    await trans.rollback();
  }

Here is the error message:

Unhandled exception:
Error 1062 (23000): Duplicate entry 'k' for key 'PRIMARY'
#0      Handler.checkResponse (package:sqljocky5/handlers/handler.dart:52:7)
#1      ExecuteQueryHandler.processResponse (package:sqljocky5/prepared_statements/execute_query_handler.dart:288:16)
#2      Comm._processReceived (package:sqljocky5/comm/comm.dart:56:43)
<asynchronous suspension>
#3      Comm.readPacket (package:sqljocky5/comm/comm.dart:50:7)
<asynchronous suspension>
#4      Comm.connect.<anonymous closure> (package:sqljocky5/comm/comm.dart:164:34)
#5      BufferedSocket._onData (package:sqljocky5/comm/buffered_socket.dart:85:11)
#6      _RootZone.runUnaryGuarded (dart:async/zone.dart:1314:10)
#7      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:336:11)
#8      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:263:7)
#9      _SyncStreamController._sendData (dart:async/stream_controller.dart:764:19)
#10     _StreamController._add (dart:async/stream_controller.dart:640:7)
#11     _StreamController.add (dart:async/stream_controller.dart:586:5)
#12     new _RawSocket.<anonymous closure> (dart:io/runtime/binsocket_patch.dart:1323:33)
#13     _NativeSocket.issueReadEvent.issue (dart:io/runtime/binsocket_patch.dart:844:14)
#14     _microtaskLoop (dart:async/schedule_microtask.dart:41:21)
#15     _startMicrotaskLoop (dart:async/schedule_microtask.dart:50:5)
#16     _runPendingImmediateCallback (dart:isolate/runtime/libisolate_patch.dart:115:13)
#17     _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:172:5)

And when I use prepared, the exception can be caught rightly.

try {
    for (var v in values) {
      await trans.prepared(sql, v);
    }
    await trans.commit();
  } on MySqlException catch (_) {
    await trans.rollback();
  }

I'm wondering why is this project using GPL. Any project using this would be subject to copyleft. Is this intended? Is there any possibility of relicensing?

I have noticed that all of my servers that are running the current sqljocky5 version crash from time to time. The connection to MySQL gets interrupted for whatever reason (e.g. mysqld restart). Then, when the program tries to execute a query, this happens:

Unhandled exception:
Bad state: Cannot write to socket, it is closed
#0      BufferedSocket.writeBufferPart (package:sqljocky5/comm/buffered_socket.dart:112:7)
#1      BufferedSocket.writeBuffer (package:sqljocky5/comm/buffered_socket.dart:107:12)
#2      Sender._sendBufferPart (package:sqljocky5/comm/sender.dart:57:19)
<asynchronous suspension>
#3      Sender.send (package:sqljocky5/comm/sender.dart:45:14)
<asynchronous suspension>
#4      Comm._processHandler (package:sqljocky5/comm/comm.dart:114:19)
<asynchronous suspension>
#5      Comm.execHandler.<anonymous closure> (package:sqljocky5/comm/comm.dart:120:36)
#6      new Future.sync (dart:async/future.dart:224:31)
#7      Pool.withResource.<anonymous closure> (package:pool/pool.dart:126:18)
#8      _RootZone.runUnary (dart:async/zone.dart:1379:54)
#9      _FutureListener.handleValue (dart:async/future_impl.dart:126:18)
#10     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:639:45)
#11     Future._propagateToListeners (dart:async/future_impl.dart:668:32)
#12     Future._completeWithValue (dart:async/future_impl.dart:483:5)
#13     Future._asyncComplete.<anonymous closure> (dart:async/future_impl.dart:513:7)
#14     _microtaskLoop (dart:async/schedule_microtask.dart:41:21)
#15     _startMicrotaskLoop (dart:async/schedule_microtask.dart:50:5)
#16     _runPendingImmediateCallback (dart:isolate/runtime/libisolate_patch.dart:115:13)
#17     _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:172:5)

Furthermore, I wasn't able to catch the error to handle it myself (i.e. create a new connection and retry the query):
Edit: That was my fault, catching works fine. I guess I have to implement a reconnect mechanism into my application now.

Would be cool though if the library detected disconnects itself. Ideally before the user is trying to execute a query. Something like: _connection.onDisconnect.listen( ... )

When I tried example/example.dart on IntelliJ,
it stalles (awaiting something) at 'Inserting rows' without error or warnings:

C:\dart\dart-sdk\bin\dart.exe --enable-asserts --enable-vm-service:52499 C:\sqljocky_test\bin\sqljocky_test.dart
Observatory listening on http://127.0.0.1:52499/

Opening connection ...
Opened connection!
Dropping tables ...
Dropped tables!
Creating tables ...
Created table!
Inserting rows ...

It seems that the published library has some problem on preparedWithAll method.
Schema privileges of the connection is % (all).
Please republish with correct library ASAP.
Also awaiting for SHA256 compatible version.

Hey! Thanks for all the great work on this package. I'm currently running into an error when I try to connect to database. I just installed MySQL, with a fresh new database named angel_orm_test.

I'm connecting like this:

var settings = ConnectionSettings(db: 'angel_orm_test');
var connection = await MySqlConnection.connect(settings);

Which gives the following error:

Unhandled exception:
MySQL Client Error: Authentication plugin not supported: caching_sha2_password
#0      HandshakeHandler.processResponse (package:sqljocky5/auth/handshake_handler.dart:117:7)
#1      Comm._processReceived (package:sqljocky5/comm/comm.dart:58:43)
<asynchronous suspension>
#2      Comm.readPacket (package:sqljocky5/comm/comm.dart:52:7)
<asynchronous suspension>
#3      Comm.connect.<anonymous closure> (package:sqljocky5/comm/comm.dart:175:34)
#4      BufferedSocket._onData (package:sqljocky5/comm/buffered_socket.dart:85:11)
#5      _RootZone.runUnaryGuarded (dart:async/zone.dart:1314:10)
#6      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:336:11)
#7      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:263:7)
#8      _SyncStreamController._sendData (dart:async/stream_controller.dart:763:19)
#9      _StreamController._add (dart:async/stream_controller.dart:639:7)
#10     _StreamController.add (dart:async/stream_controller.dart:585:5)
#11     new _RawSocket.<anonymous closure> (dart:io/runtime/binsocket_patch.dart:1283:33)
#12     _NativeSocket.issueReadEvent.issue (dart:io/runtime/binsocket_patch.dart:826:14)
#13     _microtaskLoop (dart:async/schedule_microtask.dart:41:21)
#14     _startMicrotaskLoop (dart:async/schedule_microtask.dart:50:5)
#15     _runPendingImmediateCallback (dart:isolate/runtime/libisolate_patch.dart:115:13)
#16     _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:172:5)

I haven't used MySQL in years, so I'm not 100% sure what the cause might be.

When I run this simple codes, I found the following error log.

import 'dart:async';
import 'package:sqljocky5/sqljocky.dart';

Future<void> createTables(MySqlConnection conn) async {
  print("Creating tables ...");
  await conn.execute('CREATE TABLE people (id INTEGER NOT NULL auto_increment, '
      'name VARCHAR(255), '
      'age INTEGER, '
      'PRIMARY KEY (id))');
  await conn.execute('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("Created table!");
}

main() async {
  var s = ConnectionSettings(
    user: "root",
    password: "",
    host: "192.168.1.3",
    port: 3306,
    db: "ppms",
  );

  // create a connection
  print("Opening connection ...");
  var conn = await MySqlConnection.connect(s);
  print("Opened connection!");

  await createTables(conn);

  await conn.close();
}

E/flutter ( 4153): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: SocketException: Socket has been closed
E/flutter ( 4153): #0 Comm.connect (package:sqljocky5/comm/comm.dart:182:5)
E/flutter ( 4153):
E/flutter ( 4153): #1 MySqlConnectionImpl.connect (package:sqljocky5/connection/impl.dart:102:27)
E/flutter ( 4153):
E/flutter ( 4153): #2 MySqlConnection.connect (package:sqljocky5/connection/connection.dart:36:27)
E/flutter ( 4153): #3 main (package:f_sqljocky_test/main.dart:28:36)
E/flutter ( 4153):
E/flutter ( 4153): #4 _runMainZoned.. (dart:ui/hooks.dart:189:25)
E/flutter ( 4153): #5 _rootRun (dart:async/zone.dart:1124:13)
E/flutter ( 4153): #6 _CustomZone.run (dart:async/zone.dart:1021:19)
E/flutter ( 4153): #7 _runZoned (dart:async/zone.dart:1516:10)
E/flutter ( 4153): #8 runZoned (dart:async/zone.dart:1500:12)
E/flutter ( 4153): #9 _runMainZoned. (dart:ui/hooks.dart:180:5)
E/flutter ( 4153): #10 _startIsolate. (dart:isolate/runtime/libisolate_patch.dart:300:19)
E/flutter ( 4153): #11 _RawReceivePortImpl._handleMessage (dart:isolate/runtime/libisolate_patch.dart:171:12)
E/flutter ( 4153):
Syncing files to device Android SDK built for x86...

Could you please tell why accessing columns by name is not supported anymore (although it is on readme?)

like title

Hello, whenever i try to use LIKE query it is passing empty result.

There is currently no good way to set the transaction isolation level for sqljocky5 transactions.

It would be useful if the ConnectionPool.startTransaction method allowed the caller to optionally specify the isolation level for the transaction that is created.

The isolation level for a transaction cannot be changed once that transaction has started, so it must be set before the start transaction command is executed. There are three possible categories for setting it: global, session and for the very next transaction that will be started in the session.

This feature needs to be implemented in sqljocky5. Firstly, because it may be inappropriate to expect the Dart developer to change the global transaction isolation level for the database. That will change it for all other applications using the database - not just the Dart application - and it might not be allowed by the database owner/administrator. Even if the Dart program is allowed to execute "set global transaction isolation level..." that affects future sessions and not the currently open sessions (i.e. the application would have to "connect; set the global; disconnect; reconnect" to actually use the global value it sets). It is also unreliable, because other processes could be trying to set it to a different value.

Secondly, with the current sqljocky5 API, it is not possible for the application program to reliably change it at the session level. This has to be done by sqljocky5, since only it can reliably execute "set session transaction isolation level ..." on all the connections it has opened. The connection pooling will get in the way. A work around is to get every connection from the pool as retained connections, but that is messy and impossible if some of the connections are already in use. Also, sessions/connections can be silently re-established by the connection pool, so the level set on a connection could be lost.

Thirdly, with the current sqljocky5 API, it is not possible for the application program to use the next category. It cannot execute "set transaction isolation level..." because it does not know which connection from the pool that startTransaction will use.

Actually, the preferred change is to be able to set it on the ConnectionPool, since I can't imagine too many application needing a mixture of different transaction isolation levels for the same database. It would be simpler for most developers to set the transaction isolation level when they create the connection pool. Maybe add the option to override the level set on the ConnectionPool, for individual startTransaction invocations, for those rare applications that really need different per-transaction isolation levels.

More connections than the maximum number for the pool can be created, if too many connections are requested all at once at the start.

The "start" being when the connections are first being established, or are being re-established after they have timed out.

The cause seems to be the Future returned by the call to _createConnection (in line 97 of connection_pool_impl.dart) is not being waited upon. If the application program keeps asking for connections/transactions without yielding, the new connections don't get added to the _pool list (line 109) and therefore the test to see if the maximum number of connections has already been created (line 95) incorrectly allows more connections to be created.

Program that demonstrates the race condition: pool-filling-test.txt

SQLjocky5 needs to handle timezones correctly when the SQL TIMESTAMP datatype maps to and from Dart DateTime objects.

Currently (in SQLjocky5 v1.0.0) incorrect DateTime values can be selected or written to the database, if the MySQL server is running with a different timezone than the Dart client program. It only works correctly if both have the same timezone, but this is not always possible or guaranteed (e.g. the remote MySQL database might be in a different geographical location, or the timezone is accidentally/deliberately changed).

Cause

When performing queries, the SQL timestamp datatype is mapped into Dart local DateTime objects (i.e. ones in the "local" timezone).

When a session is established to the database, it initially has the session timezone set to "SYSTEM", which means the timestamp values are interpreted to be in the global timezone of the MySQL server. However, it could be set to any timezone depending on: how the server host has been set up; how MySQL has been configured; and it can be changed by other clients without the Dart client knowing about it. So without explicit action by the client, it is not safe for the Dart client to assume the session timezone is the same as the "local" timezone used by the Dart DateTime class.

For example, consider a database contained a timestamp value representing 2017-11-20 17:00 UTC (5pm in the UK), with the database located in Australia and the client located in California. The MySQL global timezone is set to UTC+10:00, so the timestamp value in the SYSTEM timezone is 2017-11-21 03:00 (3am the next day in Australia). The Dart client program runs with a timezone of UTC-8:00, so a query will return a local DateTime object with the value of 2017-11-21 03:00 (where local is UTC-8:00). That is wrong because the correct value should be a local DateTime object containing 2017-11-20 09:00 (9am in California is the same as 5pm in the UK).

There is also the reverse problem when inserting or updating timestamp values in the database. The Dart program uses DateTime values in its local timezone, but the database session interprets those numbers in the database's system timezone. The wrong timestamp will be written if the two timezones are not the same.

Also, it is possible to pass in UTC DateTime objects into the SQLJocky5 methods. It simply uses the numbers from the object, treating it as the same as a local DateTime object -- ignoring the fact that it represents a different moment in time (unless the local timezone happens to be the same as UTC). The wrong value is written to the database.

Suggested solution

The simplest solution is to set the database connection's session timezone to the same timezone as the "local" timezone used by DateTime. (Obviously, tampering with the database's global timezone is not a good idea.)

This can be done by running the MySQL command SET time_zone='...' before running queries involving timestamps, using an offset value obtained from converting new DateTime.now().timeZoneOffset into +/-HH:MM format.

This should be implemented by the SQLjocky5 package when a connection is created. Firstly, because it might not get done, if users are expected to issue the command in the code they write. This can be a trap for users, because their code might seem to work in testing (because the test database is in the same timezone), but is wrong in production (when timezones may change sometime in the future). Secondly, because when connection pools are created, it is very difficult (impossible?) for the user's code to ensure the command has been issued to every connection in the pool -- only the SQLjocky5 package is in a position to ensure it is done properly.

Also, the SQLjocky5 package should detect if a UTC DateTime object has been passed into queries and handle them properly: converting them to a local DateTime object before using its numbers.

Provide a mechanism so the utf8mb4 character encoding can be used.

Currently, sqljocky5 uses connections with character_set_client and character_set_connection set to utf8 (the incomplete, maximum 3-byte per character, implementation of UTF-8 in MySQL). Therefore, sqljocky5 cannot handle Unicode characters which are not in the Basic Multilingual Plane, even though the database uses utf8mb4 (the complete, maximum 4-byte per character, correct implementation of UTF-8).

For example, text containing 𨭎 or 😂 is mangled. The characters get converted to four question marks when inserting/updating, or to one question mark when selecting.

Hi, I'm trying to build a registration form for web page using flutter framework (flutter for web using dev channel). The page contains the input fields as usual. But when I try to open connection using my credentials of MySQL, the program does not show any status, no any error/exceptions or not proceeds either.

I created database as testsdb and table as flutter_login_1.
Operating system: Windows 10 [Version 10.0.18362.449]
Here is my method below that checks if user's email already exist (where email is UNIQUE key in database table).

Future _isAccountExist(String email) async {
    print('\n (*) Inside _isAccountExist() ... ');
    String statement = "SELECT user_id FROM flutter_login_1 WHERE email = '" +
        email.trim() +
        "'";
    var settings, conn;
    try {
      settings = ConnectionSettings(
        user: "amey",
        password: "Amey@25_96",
        host: "localhost",
        port: 3306,
        db: "testsdb",
      );
      // create a connection
      print("Opening connection to test ...");    // After printing this line, code stops executing further !
      conn = await MySqlConnection.connect(settings);
      print("Opened connection for test!");

      Results result = await (await conn.execute(statement)).deStream();

      print(result);
      if (result != null) return true;
    } catch (err) {
      print('\n (*) Exception while check : ' + err);
    } finally {
      await conn.close();
    }
    print('\n Record NOT found ... ');
    return false;
  }

Output :

(*) Inside register ...
(*) Inside _isAccountExist() ...
Opening connection to test ...

I also tried by not providing my password in ConnectionSettings constructor but still does not worked.
Any help appreciated.

i think after running the instance for a day, the connection seems to got disconnected.
i suspect there are no mechanism to reconnect.
getting some difficult to identify error:

MySQL Client Error: Connection cannot process a request for Instance of 'PrepareHandler' while a request is already in progress for Instance of 'PrepareHandler'
#0      setupLogger.<anonymous closure> (package:project/src/util/logging.dart:13)
#1      _rootRunUnary (dart:async/zone.dart:1136)
#2      _RootZone.runUnaryGuarded (dart:async/zone.dart:1317)
#3      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:336)
#4      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:263)
#5      _SyncBroadcastStreamController._sendData (dart:async/broadcast_stream_controller.dart:375)
#6      _BroadcastStreamController.add (dart:async/broadcast_stream_controller.dart:250)
#7      Logger._publish (package:logging/logging.dart:240)
#8      Logger.log (package:logging/logging.dart:186)
#9      Logger.severe (package:logging/logging.dart:221)
#10     Driver.handleRawRequest.<anonymous closure>.<anonymous closure>.<anonymous closure>.<anonymous closure> (package:angel_framework/src/core/driver.dart:200)
#11     new Future.<anonymous closure> (dart:async/future.dart:176)
#12     _rootRun (dart:async/zone.dart:1120)
#13     _CustomZone.run (dart:async/zone.dart:1021)
#14     _CustomZone.runGuarded (dart:async/zone.dart:923)
#15     _CustomZone.bindCallbackGuarded.<anonymous closure> (dart:async/zone.dart:963)
#16     _rootRun (dart:async/zone.dart:1124)
#17     _CustomZone.run (dart:async/zone.dart:1021)
#18     _CustomZone.bindCallback.<anonymous closure> (dart:async/zone.dart:947)
#19     Timer._createTimer.<anonymous closure> (dart:async-patch/timer_patch.dart:21)
#20     _Timer._runTimers (dart:isolate-patch/timer_impl.dart:382)
#21     _Timer._handleMessage (dart:isolate-patch/timer_impl.dart:416)
#22     _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:172)

The large "ubigint" value fails under Dart 2 (even when removing the "expect" test line - the query result value causes a failure) due to an issue parsing the unsigned 64bit value to an int. Dart int is a signed 64bit value.

I'm not sure if there's a clean way to cope with an unsigned 64bit value. BigInt may be a solution, but adds some complexity.

Hi,
at first, thank you for the helpful library here, at sconed I have issues with syntax updating the database for the specific ID, I didn't see it at the documentation.

There needs to be an execute method that can take a SQL statement and a list of parameters: Future<StreamedResults> execute(String sql, Iterable values). Sometimes (often?) you want to run a query which has parameters (e.g. from user input), but don't want to create a prepared statement.

The old sqljocky5 v1.x had such a method.

In the Transaction class of sqljocky5 v2.2.1, there is an execute method that does not accept parameters (only a string) or the prepared method which does (which takes a string and an Iterable). But if you keep using the prepared method, eventually you reach the max_prepared_stmt_count limit in MySQL (and no more queries can be run). The prepared method shouldn't exist, since it creates prepared statements on the database that can't be deallocated (other than by closing the connection).

(The execute-with-parameters method is probably also needed for the Connection, but I only need it for a Transaction.)

[ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: SocketException: Socket has been closed
following error occurred. Please provide a solution.

I am not able to use mysql1. [ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: SocketException: Socket has been closed
following error occurred. Please provide a solution.

i am not using sqljockey.

I can't find the reason for this error, please tell me how to exclude it

MySQL Client Error: Connection cannot process a request for QueryStreamHandler(....) while a request is already in progress for QueryStreamHandler(....')

When the mysqld server is cleanly shutdown and a client connection pool has connections open, the client crashes because _handler becomes null. Line 213 of connection.dart tries to invoke the processResponse method on the null.

This problem occurs when MariaDB 10.2 is used as the mysqld server. It didn't occur before with MariaDB 5.5.56.

By cleanly shutdown, I mean it was stopped by running either "systemctl stop mariadb.service" or "systemctl restart mariadb.service". If you stop mysqld with a "kill -9" and then start mysqld up again (or even by rebooting the host machine) before the client's next SQL query, then it works fine (no exception is raised, and the connection pool automatically re-establishes a new connection to process the SQL query).

I suspect MariaDB 10.2 is now doing something different when it shuts down "cleanly" and sqljocky5 isn't expecting that.

Note: if the wait_timeout has been set to a low value, so connections have timed out, then cleanly shutting down mysqld does not cause this problem. It seems to only happen when the connection pool is still holding active/open connections.

Unhandled exception:
NoSuchMethodError: The method 'processResponse' was called on null.
Receiver: null
Tried calling: processResponse(Instance of 'Buffer')
#0      Connection._handleData (package:sqljocky5/src/connection.dart:247:9)
<asynchronous suspension>
#1      Connection._handleHeader (package:sqljocky5/src/connection.dart:180:18)
<asynchronous suspension>
#2      Connection.readPacket (package:sqljocky5/src/connection.dart:165:7)
<asynchronous suspension>
#3      BufferedSocket._onData (package:sqljocky5/src/buffered_socket.dart:92:22)
#4      _RootZone.runUnaryGuarded (dart:async/zone.dart:1307)
#5      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:330)
#6      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:257)
#7      _StreamController&&_SyncStreamControllerDispatch._sendData (dart:async/stream_controller.dart:796)
#8      _StreamController._add (dart:async/stream_controller.dart:667)
#9      _StreamController.add (dart:async/stream_controller.dart:613)
#10     new _RawSocket.<anonymous closure> (dart:io-patch/socket_patch.dart:1203)
#11     _NativeSocket.issueReadEvent.issue (dart:io-patch/socket_patch.dart:760)
#12     _microtaskLoop (dart:async/schedule_microtask.dart:41)
#13     _startMicrotaskLoop (dart:async/schedule_microtask.dart:50)
#14     _runPendingImmediateCallback (dart:isolate-patch/isolate_patch.dart:99)
#15     _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:152)

Process finished with exit code 255

Line 206-213 of connection.dart:

 _handleData(buffer) async {
    _readyForHeader = true;
    //log.fine("read all data: ${_dataBuffer._list}");
    //log.fine("read all data: ${Buffer.listChars(_dataBuffer._list)}");
    _headerBuffer.reset();

    try {
      var response = _handler.processResponse(buffer);

I was doing a study with the sqljocky5 library when I found the error log: "Bad state: Cannot write to the socket, it is closed" that is returned when executing the ".preparedWithAll( )" method and trying to execute the ".close ( )", I noticed that the error does not occur when executing a similar method, which is ".prepared ( )", well I think it is a library error, if not someone knows why this error?

i try simple command to connect my physical device to mysql database on my machine but return an error of socketException, os:Connection refused