SqlJocky to connect to MySQL in Dart: Error 1156 Got packets out of order

[xxgreg]

There's a question about sqljocky on SO:

http://stackoverflow.com/questions/20358084/sqljocky-to-connect-to-mysql-in-dart-error-1156-got-packets-out-of-order

[jamesots]

To the stackoverflow poster: Can you turn logging on using the following code, and add the results here (making sure your password is removed from the log first)?

hierarchicalLoggingEnabled = true;
Logger.root.level = Level.ALL;
Logger.root.onRecord.listen((LogRecord r) {
  print("${r.time}: ${r.loggerName}: ${r.message}");
});

[TheNetBird]

When I add that code it gives several "undefined name" errors. Is there something I need to do to make it visible to main?

[jamesots]

I forgot to mention that you need the logging library in your pubspec and it needs to be imported:

import 'package:logging/logging.dart';

[TheNetBird]

Excellent, thanks. Here's the data, I didn't see the password in it, so I took out the IP just in case.

2013-12-05 12:12:11.991: ConnectionPool: Running query: select * from person
2013-12-05 12:12:12.010: ConnectionPool: Getting a connection
2013-12-05 12:12:12.016: ConnectionPool: Number of in-use connections: 0
2013-12-05 12:12:12.017: ConnectionPool: Creating new pooled cnx#0
2013-12-05 12:12:12.028: Connection.Lifecycle: Use connection #0
2013-12-05 12:12:12.033: Connection: opening connection to .._._:3306/reto_zurich
2013-12-05 12:12:12.150: BufferedSocket: WRITE data
2013-12-05 12:12:12.167: BufferedSocket: READ data
2013-12-05 12:12:12.168: BufferedSocket: READ data: no buffer
2013-12-05 12:12:12.169: Connection: readPacket readyForHeader=true
2013-12-05 12:12:12.172: BufferedSocket: readBuffer, length=4
2013-12-05 12:12:12.173: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.176: BufferedSocket: read 4 bytes
2013-12-05 12:12:12.178: Connection: about to read 79 bytes for packet 0
2013-12-05 12:12:12.179: Connection: buffer size=79
2013-12-05 12:12:12.179: BufferedSocket: readBuffer, length=79
2013-12-05 12:12:12.179: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.180: BufferedSocket: read 79 bytes
2013-12-05 12:12:12.230: Connection: sendBuffer header
2013-12-05 12:12:12.233: Connection: sending header, packet 1
2013-12-05 12:12:12.234: BufferedSocket: writeBuffer length=4
2013-12-05 12:12:12.235: BufferedSocket: _writeBuffer offset=0
2013-12-05 12:12:12.239: BufferedSocket: Wrote 4 bytes
2013-12-05 12:12:12.240: Connection: sendBuffer body, buffer length=70
2013-12-05 12:12:12.240: BufferedSocket: writeBuffer length=70
2013-12-05 12:12:12.240: BufferedSocket: _writeBuffer offset=0
2013-12-05 12:12:12.241: BufferedSocket: Wrote 70 bytes
2013-12-05 12:12:12.303: BufferedSocket: READ data
2013-12-05 12:12:12.304: BufferedSocket: READ data: no buffer
2013-12-05 12:12:12.304: Connection: readPacket readyForHeader=true
2013-12-05 12:12:12.304: BufferedSocket: readBuffer, length=4
2013-12-05 12:12:12.304: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.304: BufferedSocket: read 4 bytes
2013-12-05 12:12:12.305: BufferedSocket: READ data
2013-12-05 12:12:12.305: BufferedSocket: READ data: no buffer
2013-12-05 12:12:12.305: Connection: readPacket readyForHeader=false
2013-12-05 12:12:12.306: Connection: about to read 1 bytes for packet 2
2013-12-05 12:12:12.306: Connection: buffer size=1
2013-12-05 12:12:12.306: BufferedSocket: readBuffer, length=1
2013-12-05 12:12:12.307: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.307: BufferedSocket: read 1 bytes
2013-12-05 12:12:12.310: Connection: Response finished for #0. Not auto-releasing
2013-12-05 12:12:12.311: ConnectionPool: Logged in on cnx#0
2013-12-05 12:12:12.312: ConnectionPool: Got cnx#0 for query
2013-12-05 12:12:12.314: Connection: sendBuffer header
2013-12-05 12:12:12.314: Connection: sending header, packet 0
2013-12-05 12:12:12.314: BufferedSocket: writeBuffer length=4
2013-12-05 12:12:12.315: BufferedSocket: _writeBuffer offset=0
2013-12-05 12:12:12.315: BufferedSocket: Wrote 4 bytes
2013-12-05 12:12:12.315: Connection: sendBuffer body, buffer length=21
2013-12-05 12:12:12.316: BufferedSocket: writeBuffer length=21
2013-12-05 12:12:12.316: BufferedSocket: _writeBuffer offset=0
2013-12-05 12:12:12.316: BufferedSocket: Wrote 21 bytes
2013-12-05 12:12:12.347: BufferedSocket: READ data
2013-12-05 12:12:12.347: BufferedSocket: READ data: no buffer
2013-12-05 12:12:12.347: Connection: readPacket readyForHeader=true
2013-12-05 12:12:12.348: BufferedSocket: readBuffer, length=4
2013-12-05 12:12:12.348: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.348: BufferedSocket: read 4 bytes
2013-12-05 12:12:12.349: Connection: about to read 33 bytes for packet 3
2013-12-05 12:12:12.349: Connection: buffer size=33
2013-12-05 12:12:12.349: BufferedSocket: readBuffer, length=33
2013-12-05 12:12:12.350: BufferedSocket: readBuffer, data already ready
2013-12-05 12:12:12.350: BufferedSocket: read 33 bytes
2013-12-05 12:12:12.361: QueryStreamHandler: Processing query response
2013-12-05 12:12:12.363: Connection: Response finished for #0, setting handler to null and waiting to release and reuse
2013-12-05 12:12:12.365: Connection: completing with exception: Error 1156 (08S01): Got packets out of order
Uncaught Error: Error 1156 (08S01): Got packets out of order
Unhandled exception:
Error 1156 (08S01): Got packets out of order
#0 _rootHandleUncaughtError.. (dart:async/zone.dart:677)
#1 _asyncRunCallback (dart:async/schedule_microtask.dart:18)
#2 _asyncRunCallback (dart:async/schedule_microtask.dart:21)
#3 _createTimer. (dart:async-patch/timer_patch.dart:11)
#4 _Timer._createTimerHandler._handleTimeout (timer_impl.dart:151)
#5 _Timer._createTimerHandler._handleTimeout (timer_impl.dart:159)
#6 _Timer._createTimerHandler._handleTimeout (timer_impl.dart:159)
#7 _Timer._createTimerHandler. (timer_impl.dart:166)
#8 _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:93)

[jamesots]

Do the tests or the example in sqljocky work, or do they also have the same error?

If they don't, can you show me the code that produces this error?

[TheNetBird]

I'm not sure how to run the tests in sqlJocky (Sorry I'm still pretty new to this)

This is the entirety of the code I'm running at the moment

import 'package:sqlJocky/sqlJocky.dart';
import 'package:logging/logging.dart';

void main() {
print("Hello, World!");

hierarchicalLoggingEnabled = true;
Logger.root.level = Level.ALL;
Logger.root.onRecord.listen((LogRecord r) {
print("${r.time}: ${r.loggerName}: ${r.message}");
});

var pool = new ConnectionPool(host: '80.74.155.194', port: 3306, 
    user: 'reto', password: '**********', db: 'reto_zurich', max: 5);

pool.query('select * from person').then((result) {

for (var row in result) {
print("A row:");
for (var col in row) {
print(col);
}
}
});

}

[jamesots]

If you ran that code you'd get this error: "Uncaught Error: Class '_ResultsImpl' has no instance getter 'iterator'.", as result is a stream. This was a change in v0.8.0 a few weeks ago - do you have the latest version of sqljocky installed? Run 'pub upgrade sqljocky' to upgrade to it. You'd then need to change the code to this:

result.forEach((row) {
  print("A row");
  // etc...
});

[TheNetBird]

I've run pub upgrade many times, but even the original install I had should have been the newest version since I created this project just a couple days ago. I haven't gotten that error.

I changed the code to what you stated anyway, but the output looks the same

[jamesots]

If you didn't get an error, I guess it wasn't getting that far. I'll keep investigating.

[TheNetBird]

Thanks for all your help James :)

[jamesots]

I haven't been able to reproduce this yet. Do you have old_password turned on in your mysql config file?

[TheNetBird]

I haven't found a way to check this remotely, I'll be meeting with the guy who is also the admin for this DB tomorrow I will ask him how to find this

[TheNetBird]

Unfortunately he wasn't in the office, so I wasn't able to ask him directly. However, I upgraded my Workbench and started getting a new error about authentication. Following this error led me to this thread http://bugs.mysql.com/bug.php?id=70023

And I am now 95% sure you are right and its the old password issue. I've sent an email to the DB admin asking him to make a couple changes, I'll confirm here afterwards if it works.

It looks like the way the workbench fixed this was allowing an option for users to use the old authentication protocol. Maybe that is something that would be useful in sqlJocky as well? If you are just focusing on the newer versions that makes sense too, but its an idea.

[Thiltal]

I've got the same error. In Buffer.readString I've got a this._list [80, 27, 45, 66, 248, 237, 99, 104, 111, 122, 237, 32, 112, 97, 99, 107, 101, 116, 121, 32, 118, 32, 99, 104, 121, 98, 110, 233, 109, 32, 112, 111, 248, 97, 100, 237]. It means P�-B��choz� packety v chybn�m po�ad�. I can read it as "Příchozí packety v chybném pořadí". I'm from Czech republic, I can translate it as "Got packets out of order". TheNetBird has database reto_zurich -> Zurich is in Germany, database sent localized message (same problem with me). Utf8decoder cannot read it correctly. No idea which encoding can mysql use to send error messages.

[mttbrb]

Is there an option in SQLJocky that would allow me to work around mysql being configured this way? For our product reconfiguring mysql is not an option since it is already deployed worldwide.

[SHATSON]

characterSet: CharacterSet.UTF8MB4, or collation: "utf8mb4_0900_ai_ci", when connecting to MySQL 8.0