# [PATCH] ODBC_Pconnect should not Commit/Rollback database transactions of previous data - Thread: https://externals.io/message/38632 - Original archive: https://news-web.php.net/php.internals/38632 - Started by Bijl on 2008-06-26T08:46:04+00:00 - Messages: 2 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #38632 — Bijl — 2008-06-26T08:46:04+00:00 URL: https://externals.io/message/38632#38632 Hi We have a problem with the (ODBC) persistent database connection. We have found that every PHP-session, using the same dsn, user and password, are getting the same persistent connection by a reasonable chance. Also meaning that if a database transaction was not finished, other PHP-session can do a rollback or commit of that transaction. Due to resource problems with (temporarily) connections the persistent connections are used, so we do. But "leftovers" of database transactions were not intended. Also if one want to open an another connection in one PHP-session the result is the same connection. (E.g. we use a DB-log-file to log errors en warning, so you don't want to do a rollback on the DB-log-file, in peculiar.) To overcome these problems one has to tag the connections. I suggest to use a new parameter on ODBC_(p)connect: Tag, a string parameter. If used it tries to re-connect to the connection with the same tag, if not existing it creates a new-connection. For example the tag parameter is filled with the `session_ID()` to ensure that a connection is bounded to one PHP-session. But also an extension on this value is possible: (Session_ID() + 'Log') or (Session_ID() + 'Data'). And if somebody does not uses this tag parameter, it works in the old way, so these solution is downwards compatible. So the adjusted description will be: resource odbc_pconnect (string $dsn , string $user , string $password [, int $cursor_type [, string $tag ] ] ) Eg ODBC_pconnect( $dns, $user,, $password, SQL_CUR_DEFAULT, `Session_ID()`+ 'LOG') The change of the PHP source of the function odbc_do_connect is minimal: void odbc_do_connect(INTERNAL_FUNCTION_PARAMETERS, int persistent) { char *db = NULL; char *uid = NULL; char *pwd = NULL; char *tag = NULL; zval **pv_db, **pv_uid, **pv_pwd, **pv_opt, **pv_tag; odbc_connection *db_conn; char *hashed_details; int hashed_len, cur_opt; /* Now an optional 4th parameter specifying the cursor type * defaulting to the cursors default */ switch(ZEND_NUM_ARGS()) { case 3: if (zend_get_parameters_ex(3, &pv_db, &pv_uid, &pv_pwd) == FAILURE) { WRONG_PARAM_COUNT; } /* Use Default: Probably a better way to do this */ cur_opt = SQL_CUR_DEFAULT; tag = ''; break; case 4: if (zend_get_parameters_ex(4, &pv_db, &pv_uid, &pv_pwd, &pv_opt) == FAILURE) { WRONG_PARAM_COUNT; } convert_to_long_ex(pv_opt); cur_opt = Z_LVAL_PP(pv_opt); /* Confirm the cur_opt range */ if (! (cur_opt == SQL_CUR_USE_IF_NEEDED || cur_opt == SQL_CUR_USE_ODBC || cur_opt == SQL_CUR_USE_DRIVER || cur_opt == SQL_CUR_DEFAULT) ) { php_error_docref(NULL TSRMLS_CC, E_WARNING, "Invalid Cursor type (%d)", cur_opt); RETURN_FALSE; } tag = ''; break; case 5: if (zend_get_parameters_ex(5, &pv_db, &pv_uid, &pv_pwd, &pv_opt, &pv_tag) == FAILURE) { WRONG_PARAM_COUNT; } convert_to_long_ex(pv_opt); cur_opt = Z_LVAL_PP(pv_opt); /* Confirm the cur_opt range */ if (! (cur_opt == SQL_CUR_USE_IF_NEEDED || cur_opt == SQL_CUR_USE_ODBC || cur_opt == SQL_CUR_USE_DRIVER || cur_opt == SQL_CUR_DEFAULT) ) { php_error_docref(NULL TSRMLS_CC, E_WARNING, "Invalid Cursor type (%d)", cur_opt); RETURN_FALSE; } convert_to_string_ex(pv_tag); tag = Z_STRVAL_PP(pv_tag); break; default: WRONG_PARAM_COUNT; break; } convert_to_string_ex(pv_db); convert_to_string_ex(pv_uid); convert_to_string_ex(pv_pwd); db = Z_STRVAL_PP(pv_db); uid = Z_STRVAL_PP(pv_uid); pwd = Z_STRVAL_PP(pv_pwd); if (ODBCG(allow_persistent) <= 0) { persistent = 0; } hashed_len = spprintf(&hashed_details, 0, "%s_%s_%s_%s_%d_%s", ODBC_TYPE, db, uid, pwd, cur_opt, tag); Gomar Bijl Software Engineer BU400 CAL Consult Phone +31 (0) 318 - 691 300 Fax +31 (0) 318 - 691 301 Direct +31 (0) 318 - 691 366 E-mail gomar.bijl@cal-consult.nl KvK nr. 09078710 ----------------------------------------------------------------------- Software for logistics www.cal-consult.nl Hi > > We have a problem with the (ODBC) persistent database connection. We > have found that every PHP-session, using the same dsn, user and > password, are getting the same persistent connection by a reasonable > chance. > Also meaning that if a database transaction was not finished, other > PHP-session can do a rollback or commit of that transaction. Right, I presume you are aware of the documentation on this topic, which affects all rdbms extensions. Just to be sure here is the link: http://php.net/manual/en/features.persistent-connections.php > To overcome these problems one has to tag the connections. I suggest > to > use a new parameter on ODBC_(p)connect: Tag, a string parameter. If > used > it tries to re-connect to the connection with the same tag, if not > existing it creates a new-connection. > For example the tag parameter is filled with the `session_ID()` to > ensure > that a connection is bounded to one PHP-session. But also an extension > on this value is possible: (Session_ID() + 'Log') or (Session_ID() + > 'Data'). > And if somebody does not uses this tag parameter, it works in the old > way, so these solution is downwards compatible. Several extensions offer a "new_link" optional boolean parameter to ensure that a new connection is returned if there is already an existing connection in this request: http://php.net/mysql_connect IIRC this "feature" (returning an existing connection link of a section connection is made to the same server in the same request) is not present in all extensions, but since ext/mysql was sort of a template for many extensions, I think a lot of developers copied this behavior. Note that this parameter is not available in the pconnect alternatives. ideally the odbc extension should do a "change user", which would clean up all the session specific stuff inside the connection (next to open transactions, there are other stuff like date formats etc that could also be set). i am not however sure if this feature exists in all rdbms. afaik the mysql developers are looking to implement this into mysqlnd. either way, any solution should try to be consistent across all affected extensions. regards, Lukas Kahwe Smith mls@pooteeweet.org