Database connection exception handling

squirrel · 2015-10-07 05:56:31

Hi ab

Would you be able to assist me with the following? I am using TODBCConnectionProperties to connect to a Mysql database.

1) Is it possible to override the contents of what the EODBCException posts with the 500 server error? Everything that is currently sent is useful information and I would like that to stay, but this line is included, which is scary, since it gives the user all the required information to connect to the database:

"SQLDriverFullString": "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;DATABASE=test_db;USER=root;PASSWORD
=testpwd;PORT=3306;Option=3;"
"DatabaseName": "DRIVER=MySQL ODBC 5.1 Driver;Server=localhost;DATABASE=test_db;USER=root;PASSWORD
=testpwd;PORT=3306;Option=3",

2) Would it be possible to redirect all exception logs to my own logging system, so that alarms can be raised for some errors / events?

3) After leaving the server running and unused the whole night, I got the error message this morning that Mysql has gone away. I assume that the connection was terminated after not being used for a while. Is there an auto reconnect option, or is this the way to do before running a query:

if not connection.MainConnection.Connected then
    connection.MainConnection.Connect;

Last edited by squirrel (2015-10-07 05:56:54)

DigDiver · 2015-10-07 07:52:06

3) I would recommend to use FireDac with direct Mysql connection. Using OnRecover, OnLost, ,OnRestored events of TFDConnection you can perform automatically reconnect.

ab · 2015-10-07 08:27:55

1) Why not set the password not in the connection string, but in the Password field?
I've hidden the SQLDriverFullString from published properties - this was plain unsafe. See http://synopse.info/fossil/info/b61c171ef6

2) Use http://synopse.info/files/html/Synopse% … #TITLE_585

3) See TSQLDBConnectionProperties.ConnectionTimeOutMinutes and http://synopse.info/fossil/info/f024266c0839

squirrel · 2015-10-07 08:57:26

1) Thanks, the change causes SQLDriverFullString to no longer appear in the error. The DatabaseName is logged in full. I must include the password in the connectionstring, because excluding it means that it is not used. It looks like the other parameters are not used when the database name is a connection string.

  ConnectionStr := 'DRIVER=MySQL ODBC 5.1 Driver;Server=localhost;DATABASE=test_db;USER=root;PORT=3306;Option=3';
  aConnection := TODBCConnectionProperties.Create('',ConnectionStr,'root','testpwd'); //exception: Access denied for user 'root'@'localhost' (using password:NO) (1045)

  ConnectionStr := 'DRIVER=MySQL ODBC 5.1 Driver;Server=localhost;DATABASE=test_db;USER=root;PASSWORD=testpwd;PORT=3306;Option=3';
  aConnection := TODBCConnectionProperties.Create('',ConnectionStr,'',''); //works perfectly

2) Thanks, this looks perfect

3) This should do the trick to keep the connection alive. If the mysql server was restarted between these disconnects and reconnects, this will reconnect. But will I then still get the connection error when running a query if the connection dropped between these heartbeats?

@DigRiver: I would love to use the newer versions of FireDac, but we are on XE3 with no option of upgrading. Firedac on XE3 is not stable enough for production use.

Last edited by squirrel (2015-10-07 09:20:47)

ab · 2015-10-07 10:37:57

1) You are correct.
I've added published TSQLDBConnectionProperties.DatabaseNameSafe property, to replace TSQLDBConnectionProperties.DatabaseName, triming any internal TSQLDBConnectionProperties.Password value for safety.
See http://synopse.info/fossil/info/25ed038dc6
So you have to:
- set the password within the connection string (as usual)
- set the TSQLDBConnectionProperties.Password property: this value would be recognized, and deleted in DatabaseNameSafe during JSON serialiation.

3) A dropped connection between heartbeats is very unlikely.
But it may happen if the timeout period is too big.

About FireDAC, you could use the latest version supplied with Delphi 10 Seattle, and install it on XE3.

squirrel · 2015-10-07 11:19:30

Perfect! Thanks! Will test the change tomorrow after downloading the nightly build.

squirrel · 2016-02-22 04:42:08

@ab

It looks like the DatabaseNameSafe property contains the database password again. Could this be a regression, or is there a property I need to set to ensure that it is not included? I've noticed that the TSQLDBConnectionProperties.Password is readonly, so it is possible that the reason the password is included, is because I am unable to set that value.

Example of the problem:

"DatabaseNameSafe": "DRIVER=MySQL ODBC 5.1 Driver;Server=localhost;DATABASE=test_db;USER=root;PASSWORD=testdbrootpwd;PORT=3306;Option=3",

Example of exception generated when I add a space in mytable name to force an odbc error to generate:

{
"errorCode":500,
"error":
{"EODBCException":{
	"ClassName":"EODBCException",
	"Address":"05868B40",
	"Statement": {
		"ClassName":"TODBCStatement",
		"Address":"04F1F2F0",
		"SQL": "SELECT * FROM my table ORDER BY ID desc LIMIT 50",
		"SQLWithInlinedParams": "SELECT * FROM my table ORDER BY ID desc LIMIT 50",
		"CurrentRow": 0,
		"TotalRowsRetrieved": 0,
		"Connection": {
			"ClassName":"TODBCConnection",
			"Address":"04EDAF90",
			"DBMSName": "MySQL",
			"DBMSVersion": "5.1.34-community",
			"DriverName": "myodbc5.dll",
			"Connected": true,
			"ServerTimeStampAtConnection": "2016-02-22T06:29:52",
			"TotalConnectionCount": 1,
			"TransactionCount": 0,
			"InTransaction": false,
			"RollbackOnDisconnect": true,
			"LastErrorMessage": "",
			"LastErrorWasAboutConnection": false,
			"Properties": {
				"ClassName":"TODBCConnectionProperties",
				"Address":"04EABD30",
				"Engine": "ODBC",
				"ServerName": "",
				"DatabaseNameSafe": "DRIVER=MySQL ODBC 5.1 Driver;Server=localhost;DATABASE=test_db;USER=root;PASSWORD=testdbrootpwd;PORT=3306;Option=3",
				"UserID": "root",
				"DBMS": "dMySQL",
				"DBMSEngineName": "MySQL",
				"BatchSendingAbilities": ["cCreate"],
				"BatchMaxSentAtOnce": 4096,
				"LoggedSQLMaxSize": 0,
				"LogSQLStatementOnException": false,
				"ForcedSchemaName": "",
				"UseCache": true,
				"RollbackOnDisconnect": true,
				"StoreVoidStringAsNull": false
			}
		}
	},
	"Message": "TODBCStatement - TODBCLib error: [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table ORDER BY starttime desc LIMIT 50' at line 1 (1064)\r\n"
}}
}

ab · 2016-02-22 10:41:34

The password should be set in the Password published field, so that the function works.
I suspect you forget to supply the Password parameter to the TSQLDBConnectionProperties.Create() constructor.
We did not make any modification to the implementation:

function TSQLDBConnectionProperties.GetDatabaseNameSafe: RawUTF8;
begin
  result := StringReplaceAll(fDatabaseName,PassWord,'***');
end;

EMartin · 2016-02-22 16:12:30

Use PWD instead of PASSWORD as property name in connection string.

mORMot Open Source

#1 2015-10-07 05:56:31

Database connection exception handling

#2 2015-10-07 07:52:06

Re: Database connection exception handling

#3 2015-10-07 08:27:55

Re: Database connection exception handling

#4 2015-10-07 08:57:26

Re: Database connection exception handling

#5 2015-10-07 10:37:57

Re: Database connection exception handling

#6 2015-10-07 11:19:30

Re: Database connection exception handling

#7 2016-02-22 04:42:08

Re: Database connection exception handling

#8 2016-02-22 10:41:34

Re: Database connection exception handling

#9 2016-02-22 16:12:30

Re: Database connection exception handling

Board footer