Wrong content returned if a table is modified

[bouda1]

Hi,

Since robotframework-databaselibrary version 4.1.0, we have an issue.

We work with mariadb.

And here is an example of code we could use:

    Connect To Database    pymysql    ${DBNameConf}    ${DBUser}    ${DBPass}    ${DBHost}    ${DBPort}
    FOR    ${index}    IN RANGE    10                                           
        ${output}    Query    SELECT current_status, state_type FROM my_table WHERE parent_id=16 and id= 314
        Sleep    1s                                                             
        IF    ${output} == ((2, '1'),)    BREAK                                 
    END                                                                         
                                                                                
    Should Be Equal As Strings    ${output}    ((2, '1'),)    mod_bam_kpi not filled
    Disconnect From Database

We know that the query does not return the expected response immediately; we need to wait to get the correct one. Therefore, we have this loop to wait for approximately 10 seconds. Since version 4.1.0, if the output is incorrect the first time, it will remain incorrect in the following steps. This is because ${output} retains its initial value.

Currently, the solution I have is to change the code to this:

    FOR    ${index}    IN RANGE    10                                           
        Connect To Database    pymysql    ${DBNameConf}    ${DBUser}    ${DBPass}    ${DBHost}    ${DBPort}
        ${output}    Query    SELECT current_status, state_type FROM my_table WHERE parent_id=16 and id= 314
        Sleep    1s                                                             
        IF    ${output} == ((2, '1'),)    BREAK                                 
        Disconnect From Database
    END                                                                         
                                                                                
    Should Be Equal As Strings    ${output}    ((2, '1'),)    mod_bam_kpi not filled

Otherwise, I can also force the version 4.0.4 of your library.
Thanks.
Cheers.

[amochin]

Sounds very weird and I can't reproduce it so far...

Are you sure that the issue occurs with DatabaseLibrary starting from v. 2.1.0 only (I guess you meant these version, because there is no version 4)?

And are you sure that the DB is really being properly updated during your loop?

Starting from v.2, the results of each request are being logged in the RF log as a table - have you checked them?
Do you see your expected changes there?

If possible, could you please provide the RF log file for the debugging?
Thanks!

[bouda1]

Sorry for the delay, You're right about the version, I was talking about v2.1.0 and newer versions. Here are several attempts to execute a test with the issue. At the end of the test, we try during 2mn to a SELECT count(*) with the 2.0.4 version of your library, it is ok whereas with the 2.1.2 version, it fails. In the archive there are three directories: * robotframework-databaselibrary-2.0.4 : the test is executed with the 2.0.4 version and works correctly as expected. * robotframework-databaselibrary-2.1.2: the test is executed in the exactly same conditions but with the 2.1.2 version and fails. * robotframework-databaselibrary-2.1.2-with-connections-moved: In the loop I changed the way we connect to the database and then the test works again. Here is the two versions of the loop: The loop as executed in the first two tests: ``` Connect To Database pymysql ${DBName} ${DBUser} ${DBPass} ${DBHost} ${DBPort} ${date} Get Current Date result_format=epoch Log To Console date=${date} FOR ${index} IN RANGE 120 ${output} Query ... SELECT count(*) FROM resources WHERE name like 'service\_%%' and parent_name='host_1' and status <> 2 Log To Console ${output} Sleep 1s IF "${output}" == "((0,),)" BREAK END Should Be Equal As Strings ${output} ((0,),) Disconnect From Database ``` The loop as executed in the third test: ``` ${date} Get Current Date result_format=epoch Log To Console date=${date} FOR ${index} IN RANGE 120 Connect To Database pymysql ${DBName} ${DBUser} ${DBPass} ${DBHost} ${DBPort} ${output} Query ... SELECT count(*) FROM resources WHERE name like 'service\_%%' and parent_name='host_1' and status <> 2 Log To Console ${output} Disconnect From Database Sleep 1s IF "${output}" == "((0,),)" BREAK END Should Be Equal As Strings ${output} ((0,),) ``` Let's hope you'll be able to reproduce. Thank you. David. Le ven. 14 févr. 2025 à 17:06, Andre Mochinin ***@***.***> a écrit :

…

Sounds very weird and I can't reproduce it so far... Are you sure that the issue occurs with DatabaseLibrary starting from v. 2.1.0 only (I guess you meant these version, because there is no version 4)? And are you sure that the DB is really being properly updated during your loop? Starting from v.2, the results of each request are being logged in the RF log as a table - have you checked them? Do you see your expected changes there? If possible, could you please provide the RF log file for the debugging? Thanks! — Reply to this email directly, view it on GitHub <#237 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQIBPO7BCHLFIVKTEAVK5T2PYIBXAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNJZG4ZDEMRZGQ> . You are receiving this because you authored the thread.Message ID: ***@***.*** .com> [image: amochin]*amochin* left a comment (MarketSquare/Robotframework-Database-Library#237) <#237 (comment)> Sounds very weird and I can't reproduce it so far... Are you sure that the issue occurs with DatabaseLibrary starting from v. 2.1.0 only (I guess you meant these version, because there is no version 4)? And are you sure that the DB is really being properly updated during your loop? Starting from v.2, the results of each request are being logged in the RF log as a table - have you checked them? Do you see your expected changes there? If possible, could you please provide the RF log file for the debugging? Thanks! — Reply to this email directly, view it on GitHub <#237 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQIBPO7BCHLFIVKTEAVK5T2PYIBXAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNJZG4ZDEMRZGQ> . You are receiving this because you authored the thread.Message ID: ***@***.*** .com>

[amochin]

I was able to reproduce and fix it.
When using pymysql, even a simple SELECT statement requires a commit / rollback - otherwise results from other transactions are not available.

In version 2.1.0, the logic was changed - a rollback is performed only in case of error.
However, there was no commit in some keywords, like Query.
I've fixed it - including a commit helped.
Please try version 2.1.3 and check if helps.

Thank you very much for finding the error and submitting the issue!

[amochin]

BTW, instead of a FOR loop I recommend using the new Retry mechanism.

[bouda1]

Thanks for the tip !! Le jeu. 20 févr. 2025 à 10:58, Andre Mochinin ***@***.***> a écrit :

…

BTW, instead of a FOR loop I recommend using the new Retry mechanism <https://marketsquare.github.io/Robotframework-Database-Library/#Retry%20mechanism> . — Reply to this email directly, view it on GitHub <#237 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQIBPNRJ3YG3SU3ADK4CQT2QWRLLAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNZRGAYDOMBTGQ> . You are receiving this because you authored the thread.Message ID: ***@***.*** .com> [image: amochin]*amochin* left a comment (MarketSquare/Robotframework-Database-Library#237) <#237 (comment)> BTW, instead of a FOR loop I recommend using the new Retry mechanism <https://marketsquare.github.io/Robotframework-Database-Library/#Retry%20mechanism> . — Reply to this email directly, view it on GitHub <#237 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQIBPNRJ3YG3SU3ADK4CQT2QWRLLAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNZRGAYDOMBTGQ> . You are receiving this because you authored the thread.Message ID: ***@***.*** .com>

[bouda1]

And also thanks for the fix. My tests are OK. Cheers. Le jeu. 20 févr. 2025 à 16:11, David Boucher ***@***.***> a écrit :

…

Thanks for the tip !! Le jeu. 20 févr. 2025 à 10:58, Andre Mochinin ***@***.***> a écrit : > BTW, instead of a FOR loop I recommend using the new Retry mechanism > <https://marketsquare.github.io/Robotframework-Database-Library/#Retry%20mechanism> > . > > — > Reply to this email directly, view it on GitHub > <#237 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ABQIBPNRJ3YG3SU3ADK4CQT2QWRLLAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNZRGAYDOMBTGQ> > . > You are receiving this because you authored the thread.Message ID: > <MarketSquare/Robotframework-Database-Library/issues/237/2671007034@ > github.com> > [image: amochin]*amochin* left a comment > (MarketSquare/Robotframework-Database-Library#237) > <#237 (comment)> > > BTW, instead of a FOR loop I recommend using the new Retry mechanism > <https://marketsquare.github.io/Robotframework-Database-Library/#Retry%20mechanism> > . > > — > Reply to this email directly, view it on GitHub > <#237 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ABQIBPNRJ3YG3SU3ADK4CQT2QWRLLAVCNFSM6AAAAABXEID7BWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNZRGAYDOMBTGQ> > . > You are receiving this because you authored the thread.Message ID: > <MarketSquare/Robotframework-Database-Library/issues/237/2671007034@ > github.com> >