[Bug]: "Secondary stale connection error" When Using MySQL Databases

[ame-appsmith]

Is there an existing issue for this?

• [X] I have searched the existing issues

Description

Some users that are using MySQL or MariaDB as datasource get often the error "Secondary stale connection error". Restarting the machine/containers fixes the issue, but is inconvenient for the users.

Steps To Reproduce

https://app.frontapp.com/open/msg_1ea647ik https://app.frontapp.com/open/msg_1e9wvc5o https://app.frontapp.com/open/msg_1e3k7yh8

Workaround

Currently the only known workaround is to re-start the API server post which the queries should start working again.

Public Sample App

No response

Version

Self-Hosted v1.8.4

Front conversations

[rohan-arthur]

Intercom links in case no front access: https://app.intercom.io/a/apps/y10e7138/inbox/conversation/164629100150051

https://app.intercom.io/a/apps/y10e7138/inbox/conversation/164629100172180

https://discord.com/channels/725602949748752515/1026404443517890571/1026404443517890571

[rohan-arthur]

update: have reached out to the three users to share server side logs to help investigate this, waiting for response.

[sumitsum]

We are hoping that the PR raised for the following issue will also address this issue. Once the PR is merged we will keep monitoring for occurrences of this issue. If no such occurrence is found in the future no we may assume that this issue has been addressed for most use cases. https://github.com/appsmithorg/appsmith/issues/17481

[PaulMcF1987]

I had his issue happen to me today. I have 3 apps on appsmith, it only seemed to effect one of the three apps.

[PaulMcF1987]

I restarted the container and it seems to be working now

[Silviovespoli]

We are also experiencing the same issue. Currently, we have to restart the Appsmith server twice daily. It appears that the underlying issue is with the internal MongoDB server, which is reaching its connection limit. If you require any additional information, please let us know.

[Nikhil-Nandagopal]

@rohan-arthur @sumitsum can you please update with a timeline here?

[vanscurvy]

I'm also experiencing this. I can trigger it by performing the same action in an app twice in a row, even immediately after restarting appsmith. If you would like to take a look let me know.

[ame-appsmith]

Another user is facing this error.

[sumitsum]

A fix was pushed recently that may serve as a fix for a large number of use cases. We should monitor the occurrences for versions 1.9.3 onwards.

[sumitsum]

@vanscurvy can you please mention which Appsmith version you are on ? Also, are you using cloud version or self hosted ?

[vanscurvy]

Hi @sumitsum - I am self hosted, currently on version v1.9.3.1

[hemantkrmmt]

Hi @sumitsum I have the latest version (Appsmith Community v1.9.3.1) and still getting this issue. It is working fine if we are working on a single table but when I am joining more than one, then Its causing issue. can you please review it and provide any timelines.

[sumitsum]

@vanscurvy @hemantkrmmt can you please share what steps I can take to reproduce this issue - is this something that is consistently reproducible ?

[hemantkrmmt]

@sumitsum There is a database connection that is valid since some queries are working fine. So, for the query, I am just adding the query inside the the query input box and click on run button. then I am getting the above error. It is reproducible.

[vanscurvy]

If it is helpful I can try to reproduce in a slimmed down app and share, but won't have time until later in the week. I am wondering if this issue (or at least my issue) is related to transaction commits or something similar. Here is a pattern I have noticed in some cases:

1. I take an action the runs a INSERT and UPDATE statement, followed by a COMMIT statement on success.
2. Action is reported as successful (and I can see the COMMIT came through in the mysql general log).
3. I won't see the inserted record in another client if I try to SELECT it though (i.e. mysqlworkbench).
4. I will try to do step 1 again with a new record, and this time it will error out with the "secondary stale connection" error. Notably, this is the error I get if the the query is an unprepared statement. If it is a prepared statement, then the error is "Parameter at position 0 is not set".

I was thinking back about this behavior, and I believe it started about 5 or 6 days ago. I'm wondering if something in the update triggered it? Maybe some invalid transaction sequence that I was getting away with before, but won't pass now?

I haven't changed any of the connection parameters between appsmith and mysql (not sure if this is possible). Here is what mysql logs when appsmith connects: SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation'

In the above example, here is the relevant JS and mysql:

Action JS: {{ add_record.run( ).then(() => { commit.run(); showAlert('Record successfully added.','success'); lookup_records.run(); startup.reset(); resetWidget("TABLE_records",true); } ).catch(() => { rollback.run(); showAlert('Record could not be added','error'); showAlert(add_record.data.toString(),'error'); } ) }}

add_record: BEGIN; INSERT INTO media_notes (note_subject, note, media_acquisition_id, page_citation, poi) VALUES ("{{INPUT_subject.text}}", "{{INPUT_note.text}}", {{SELECT_book.selectedOptionValue}}, {{INPUT_page_no.text}}, IF({{SWITCH_poi.isSwitchedOn}}, 1, 0)); UPDATE media_acquisitions SET last_note_datetime = NOW() WHERE id = {{SELECT_book.selectedOptionValue}};

commit: COMMIT;

lookup_records: SELECT page_citation, note_subject, media_notes.note, poi, media_acquisition_id, media_notes.id, media_acquisition_id, CONCAT(b.title, " (", med.category, ")") as Title FROM media_notes LEFT JOIN media_acquisitions ma ON ma.id = media_acquisition_id LEFT JOIN books b ON b.media_id = ma.media_id LEFT JOIN categories med ON med.id = ma.media_form_id ORDER BY media_notes.entry_datetime DESC ; startup JS: `export default {

reset: async () => {
	storeValue('allow_clear',false);
	storeValue('allow_delete',false);
	resetWidget('SWITCH_edit');
	resetWidget('INPUT_subject')
	resetWidget('INPUT_note');
	resetWidget('SWITCH_poi');
	resetWidget('INPUT_page_no');
	resetWidget('TABLE_records');
}

}`

mysql log from step 1: 2023-01-18T14:35:13.081587Z 50 Query BEGIN; 2023-01-18T14:35:13.084153Z 50 Query INSERT INTO media_notes (note_subject, note, media_acquisition_id, page_citation, poi) VALUES ("rth", "rthtr", 41500312, 78, IF(false, 1, 0)); 2023-01-18T14:35:13.092678Z 50 Query UPDATE media_acquisitions SET last_note_datetime = NOW() WHERE id = 41500312 2023-01-18T14:35:13.305350Z 48 Query SELECT page_citation, note_subject, media_notes.note, poi, media_acquisition_id, media_notes.id, media_acquisition_id, CONCAT(b.title, " (", med.category, ")") as Title FROM media_notes LEFT JOIN media_acquisitions ma ON ma.id = media_acquisition_id LEFT JOIN books b ON b.media_id = ma.media_id LEFT JOIN categories med ON med.id = ma.media_form_id ORDER BY media_notes.entry_datetime DESC 2023-01-18T14:35:13.390269Z 49 Query COMMIT

mysql log from step 4: 2023-01-18T14:36:17.116337Z 51 Query BEGIN; 2023-01-18T14:36:17.116600Z 51 Query INSERT INTO media_notes (note_subject, note, media_acquisition_id, page_citation, poi) VALUES ("jghj", "jtyytj", 41500312, 79, IF(false, 1, 0)); 2023-01-18T14:36:21.261739Z 53 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:36:21.263979Z 53 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:36:21.273552Z 53 Query BEGIN; 2023-01-18T14:36:21.273909Z 53 Query INSERT INTO media_notes (note_subject, note, media_acquisition_id, page_citation, poi) VALUES ("jghj", "jtyytj", 41500312, 79, IF(false, 1, 0)); 2023-01-18T14:36:21.316123Z 54 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:36:21.322579Z 55 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:36:21.328192Z 54 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:36:21.332878Z 55 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:36:21.337683Z 56 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:36:21.342363Z 56 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:36:21.342383Z 57 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:36:21.344521Z 57 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:36:25.497977Z 54 Query ROLLBACK 2023-01-18T14:37:03.303840Z 55 Query BEGIN; 2023-01-18T14:37:03.304119Z 55 Query INSERT INTO media_notes (note_subject, note, media_acquisition_id, page_citation, poi) VALUES ('jghj', 'jtyytj', 41500312, '79', IF('0', 1, 0)); 2023-01-18T14:37:07.412919Z 58 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:37:07.415391Z 58 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:37:07.480050Z 60 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:37:07.483061Z 62 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:37:07.484824Z 60 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:37:07.486086Z 59 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:37:07.488321Z 61 Connect [email protected] on home_db_prod using TCP/IP 2023-01-18T14:37:07.489016Z 62 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:37:07.490735Z 59 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:37:07.492770Z 61 Query SET autocommit=1,transaction_isolation='REPEATABLE-READ',session_track_schema=1,session_track_system_variables='autocommit,transaction_isolation' 2023-01-18T14:37:07.679588Z 58 Query ROLLBACK

[vanscurvy]

I've narrowed down the steps to reproduce the behavior and have started from scratch with the sakila sample database to rule out an issue with my own database. When the behavior is reproduced, it hangs up my mysql server in some way that prevents other clients (i.e. mysqlworkbench) from running queries against the same database that was queried by appsmith (other databases on the mysql server are not affected). I am running mysql in a docker container and restarting the docker container restores the database to a working condition.

Steps to reproduce:

1. Restart mysql docker container
2. On appsmith, confirm that the following queries can be run multiple times in succession with out issue: UPDATE payment SET amount = 50.50 WHERE payment_id = 1; BEGIN; UPDATE payment SET amount = 50.50 WHERE payment_id = 1; COMMIT;
3. Run a query that is a transaction without a COMMIT at the end. Then run a separate query that is just a COMMIT. BEGIN; UPDATE payment SET amount = 50.50 WHERE payment_id = 1; COMMIT;
4. Try running another query and you will get the "secondary stale" error UPDATE payment SET amount = 50.50 WHERE payment_id = 1;

This is the point at which other clients can no longer run queries against the database (they timeout with disconnections). I have tried running the same sequence from MYSQLWorkbench without issue.

So it seems that I can no longer run transactions across multiple appsmith queries. Most of my app actions are structured this way so that javascript logic can determine the composition of each transaction and either commit it or roll it back, depending on the success or failure of the functions involved.

I've attached a section of the appsmith backend log that coincides with the error happening. backend.log

[vanscurvy]

@sumitsum - I see your latest comment in #20018 . Sounds like my query structure won't work anymore because of the switch to connection pools. This is fine, and I can restructure accordingly, but just wanted to see if you agree, or if this is some other behavior that should still be looked at.

[drtakis85]

I am experiencing the same error and is just happening in an specific app . first query runs, second query give me error - Secondary stale connection error. my apps also were design working of queries based on temporary tables i recently read #20018 as an temporary solution I changed al the queries from create temporary table to create CREATE view but still in this specific app i get the stale connection error

[yasharma2301]

Hey, this issue is happening to me too. I get this error for a query as simple as SELECT * FROM table_name WHERE date_column BETWEEN 'valid date' AND 'valid date';. As I am using Appsmith cloud I can't use the workaround which is restarting the container as suggested by other users.

[sumitsum]

@yasharma2301 in this case you can try restarting your MySQL DB.

[sumitsum]

@sumitsum - I see your latest comment in #20018 . Sounds like my query structure won't work anymore because of the switch to connection pools. This is fine, and I can restructure accordingly, but just wanted to see if you agree, or if this is some other behavior that should still be looked at.

@vanscurvy in case you intend to use temporary tables across queries then yes unfortunately it won't work with the newer Appsmith versions. This is not recommended but you can also try bunching of multiple MySQL queries in the same Appsmith query in case that is possible (ie. the temporary table is getting created and used in the same Appsmith query) - this should work. Otherwise as you have mentioned we would to need to re-structure the queries so that they don't need to access temporary tables across different Appsmith queries.

[yasharma2301]

@yasharma2301 in this case you can try restarting your MySQL DB.

Hey why would restarting DB help here? The same query is working fine in DBeaver. Also other queries are working on appsmith too

[sumitsum]

@yasharma2301 this was a wild guess - I was thinking about the case where in the DB might have reached its limit of total number of open connections that it can support.

[sumitsum]

@yasharma2301 in your case what happens when you try to create a new MySQL datasource after getting the error ? Does this new datasource connection work ?

[yasharma2301]

@yasharma2301 in your case what happens when you try to create a new MySQL datasource after getting the error ? Does this new datasource connection work ?

Hey yes, datasource can be created. I just noted one thing, could you tell me approximately how many rows' export or how many kbs of data can appsmith pull based on SQL query. If the number goes beyond a threshold I think I am getting "Secondary Stale Error". If this is the case, how can a person get data dump efficiently?

[sumitsum]

@yasharma2301 thanks for sharing this. I am not sure about it. I will have to check on this. IMO, Appsmith should be able to pull up a few tens of MBs of data as far as the server is concerned - but I don't think the client would be able to render this much data without significantly impacting the client side performance. Hence it is generally not recomended to pull up more than say 20 rows at a time. It is generally recommended we use pagination: https://docs.appsmith.com/core-concepts/data-access-and-binding/displaying-data-read/display-data-tables#pagination Also, can you please share how many queries are running simultaneously on the same MySQL datasource for you ? - just gathering some data for debugging purpose.

[sribalajig]

This error has spiked after the week of Jan 9, which is about the time the MySQL driver was upgraded.

[pchambless]

I'm getting this error as well, but only for a certain query that I can tell...

[phangs]

@yasharma2301 thanks for sharing this. I am not sure about it. I will have to check on this. IMO, Appsmith should be able to pull up a few tens of MBs of data as far as the server is concerned - but I don't think the client would be able to render this much data without significantly impacting the client side performance. Hence it is generally not recomended to pull up more than say 20 rows at a time. It is generally recommended we use pagination: https://docs.appsmith.com/core-concepts/data-access-and-binding/displaying-data-read/display-data-tables#pagination Also, can you please share how many queries are running simultaneously on the same MySQL datasource for you ? - just gathering some data for debugging purpose.

Hi. I am having this issue as well as of today. Problem with using pagination is that I am using a query that gets a lot of data, around 50,000 rows. It is first displayed in a table using pagination, but I made a button using the same query (without pagination) to download the same data.

The download option in the table doesn't work for paginated records. Only the first page of the paginated result is downloaded. This is the reason I have to create a separate function for downloading data.

Before several appsmith updates, there's no stale connection issue. I do hope there will be a fix for this immediately