CodeIgniter Forums
Session error: "Query error: Commands out of sync; you can't run this command now..." - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: Session error: "Query error: Commands out of sync; you can't run this command now..." (/showthread.php?tid=80001)

Pages: 1 2


Session error: "Query error: Commands out of sync; you can't run this command now..." - Volder - 08-31-2021

Hello everyone
I face an issue with database session.
The site was working fine on PHP 7.2 (Codeigniter 3.1.9) for a long time. As PHP 7.2 is deprecated the hosting updated to PHP 7.4 and then in the logs I periodically started to get something like:


Code:
Query error: Commands out of sync; you can't run this command now - Invalid query: INSERT INTO `ci_sessions` (`id`, `ip_address`, `timestamp`, `data`) VALUES ('fb7eec234234234234345634639f089b06ff70b', 'X.X.X.X', 1630386492, '__ci_last_regenerate|i:1630386492;user|s:20:\"20657889564c\";')
Query error: Commands out of sync; you can't run this command now - Invalid query: SELECT RELEASE_LOCK('868db8ea6235235edrgydrfg6b5d33ea77') AS ci_session_lock



The error is always represented with 2 rows, and the first row is either insert into ci_sessions table, or update. The second row is always about release lock.

I tried to update to the latest Codeigniter 3.1.11 and the same error is still there.

After reading on the forums, the issue arise when stored procedure is used, or there are several queries comma-separated in the same db->query() request. However, we don't use stored procedures and the queries are always just one query at a time.
Is there any hint what can be done? Or how to further debug it? The main problem is that this issue repeats very rarely and I cannot replicate it on the local machine (e.g. there are thousands of users on the website per day and I see this error 2-3 times per day at different times).


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - php_rocs - 08-31-2021

@Volder ,

Have you tried the query directly in the database (to see if an error appears)? Are you sure that the input data is clean? Has any of the data field types in the table changed?


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - ikesela - 08-31-2021

maybe you should investigate on this query: SELECT RELEASE_LOCK('868db8ea6235235edrgydrfg6b5d33ea77') AS ci_session_lock


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - Volder - 09-01-2021

(08-31-2021, 02:18 PM)php_rocs Wrote: @Volder ,

Have you tried the query directly in the database (to see if an error appears)? Are you sure that the input data is clean?  Has any of the data field types in the table changed?

The problem is that there is no particular query that produces it. On the local environment I can't replicate it.
As I mentioned on the production it appears only several times a day out of thousands of user visits, so it is very rare case, which I believe would be hard to replicate.
Maybe something connected with heavy load.

What do you mean by input data and clean?

No data types were changed, neither application, nor the database was changed at all, it was only PHP upgrade 7.2 -> 7.4 and then the issue appeared.


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - Volder - 09-01-2021

(08-31-2021, 09:31 PM)ikesela Wrote: maybe you should investigate on this query: SELECT RELEASE_LOCK('868db8ea6235235edrgydrfg6b5d33ea77') AS ci_session_lock

This is a query from the standard Session core file of the CI library, not a query from the application.
If the problem is in the core of Codeigniter files then someone would face the same problem and this is why I ask on the forum to find a solution.


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - InsiteFX - 09-01-2021

Read:

MySQL - B.3.2.12 Commands out of sync


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - Volder - 09-01-2021

(09-01-2021, 12:30 AM)InsiteFX Wrote: Read:

MySQL - B.3.2.12 Commands out of sync

This is a general description when this error appears on the mysql level, however, Codeigniter as a framework takes care of wrapping all the mysql raw functions. I don't call any of those functions in the application at all.


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - InsiteFX - 09-01-2021

There used in the database drivers etc;


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - php_rocs - 09-01-2021

(09-01-2021, 12:18 AM)Volder Wrote:
(08-31-2021, 02:18 PM)php_rocs Wrote: @Volder ,

Have you tried the query directly in the database (to see if an error appears)? Are you sure that the input data is clean?  Has any of the data field types in the table changed?

The problem is that there is no particular query that produces it. On the local environment I can't replicate it.
As I mentioned on the production it appears only several times a day out of thousands of user visits, so it is very rare case, which I believe would be hard to replicate.
Maybe something connected with heavy load.

What do you mean by input data and clean?

No data types were changed, neither application, nor the database was changed at all, it was only PHP upgrade 7.2 -> 7.4 and then the issue appeared.

I mean is the data in the SQL input statement good data... or if you were to run the query directly in the database (phpMyAdmin) would it work or would it give you an error?


RE: Session error: "Query error: Commands out of sync; you can't run this command now..." - Volder - 09-02-2021

(09-01-2021, 08:01 AM)php_rocs Wrote: I mean is the data in the SQL input statement good data... or if you were to run the query directly in the database (phpMyAdmin) would it work or would it give you an error?

I now run both last such error queries directly on DB (it was update of the time for the session ID and release lock) and both successfully run. Means there was a session with such an ID. The release query worked successfully but returned NULL, probably should return NULL as this is some kind of lock release.

So no, all seems good here.