英文:

Python mariadb-connector function returns empty cursor.fetchall() on 252nd iteration with different WHERE clauses

问题

Caveats:
Linux Distribution prevents upgrade beyond connector 3.1.20 and thus python module 1.0.11

Versions from /usr/bin/mariadb_config
Copyright 2011-2020 MariaDB Corporation AB
Get compiler flags for using the MariaDB Connector/C.
Usage: mariadb_config [OPTIONS]
Compiler: GNU 10.2.1

--version [10.5.19]
--cc_version [3.1.20]

Output:

***** ***** ***** ***** ***** ***** ***** ***** ***** *****

Executing SQL Query: "SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\ Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
        Values:"%RVD026%"
                "RVD
                Card Data:  [(148982, 'RVD026', 'RVD', 'Rhinar Edition Normal')]
   cardid  mfguid setTCGUid               printing
0  148982  RVD026       RVD  Rhinar Edition Normal
DBCursor Status:  <mariadb.connection connected to 'localhost' at 0xffff8929bb40>   True
DBCardID:    cardid  mfguid setTCGUid               printing
0  148982  RVD026       RVD  Rhinar Edition Normal
Length: 1
        CardId 0:     cardid  mfguid setTCGUid               printing
0  148982  RVD026       RVD  Rhinar Edition Normal
Card Last Updated SQL Query: SELECT card_lastupdate from g1st_fabdb.marketdata WHERE cardid=148982 ORDER BY card_lastupdate DESC LIMIT 1
Card Last Updated in DB: None 
        In API: 2023-05-26 21:53:45

***** In Set: RVD Card In set: 026 SETID: RVD026 ***** 

***** Current CardCount total: 251 ***** 
***** ***** ***** ***** ***** ***** ***** ***** ***** *****

Executing SQL Query: "SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\ Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
        Values:"%DRO001%"
                "DRO
                Card Data:  []
False
DBCursor Status:  <mariadb.connection connected to 'localhost' at 0xffff8929bb40>   True
DBCardID: False
Traceback (most recent call last):
  File "/home/biqu/scripts/api-dbupdate.py", line 367, in <module>
    if Debug : print("Length:",len(dfCardId))
TypeError: object of type 'bool' has no len()

Python3 function:

def get_dfCardId(SetID, sName):
    # replace with cardID identification function .,+6
    if not mydberror:
        my_cardid_query = "SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\\1 Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
        mydbSetId = "%{}%"
        if Debug: print("Executing SQL Query: \'" + my_cardid_query + "\'\n\tValues:\'" + mydbSetId.format(SetID) + "\'\n\t\t\'" + sName)
        myconn_ro = mariadb.connect(**mydbparams)
        mydbcursor = myconn_ro.cursor()
        mydbcursor.execute(my_cardid_query, (mydbSetId.format(SetID), sName))
        dbCardId = mydbcursor.fetchall()
        if Debug and myconn_ro.warnings > 0: print("Warnings: ", myconn_ro.warnings, "\n\t", myconn_ro.show_warnings)
        mydbcursor.close()
        myconn_ro.reset()
        if Debug: print("\t\tCard Data: ", dbCardId)
        if len(dbCardId) > 0:
            dfCardId = pd.DataFrame(dbCardId, index=None, columns=('cardid', 'mfguid', 'setTCGUid', 'printing'))
        else:
            dfCardId = False
        print(dfCardId)
        if Debug: print("DBCursor Status: ", mydbcursor.connection, " ", mydbcursor.closed)
        myconn_ro.close()
        return dfCardId

Summary:
Python function runs a select statement. On the 252nd iteration with different WHERE mfguids (mind you I have rearranged things and it is always on the 252 iterations regardless of the WHERE clause), it always returns no data from the cursor.fetchall(). Initially, the function was reusing the same connection initialized only once at the start of the main script. I have since tried variants using ConnectionPool (which the connection.close() statements never seemed to release the connections back to the pool), executing the cursor and connection close methods in order, and finally the current iteration which should be creating a new connection using the same parameters on each iteration and destroying it (which does not appear to be happening as the memory address seems to remain unchanged each iteration). Still, it always returns no data, no warnings, no errors, and no exceptions resulting in a failure due to the lack of data to return. So either I found a really corner bug (which I think someone would have hit before now) or I am doing something wrong. Any suggestions are much appreciated.

(Note: The code and output are provided for reference, and the translation is based on the text you provided. If you have specific questions or need further assistance, please feel free to ask.)

<details>
<summary>英文:</summary>


Caveats:
Linux Distribution prevents upgrade beyond  connector 3.1.20 and thus python module 1.0.11

Versions from /usr/bin/mariadb_config
Copyright 2011-2020 MariaDB Corporation AB
Get compiler flags for using the MariaDB Connector/C.
Usage: mariadb_config [OPTIONS]
Compiler: GNU 10.2.1
 
  --version       [10.5.19]
  --cc_version    [3.1.20]
  


Output: 

---

Executing SQL Query: "SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\1 Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
Values:"%RVD026%"
"RVD
Card Data: [(148982, 'RVD026', 'RVD', 'Rhinar Edition Normal')]
cardid mfguid setTCGUid printing
0 148982 RVD026 RVD Rhinar Edition Normal
DBCursor Status: <mariadb.connection connected to 'localhost' at 0xffff8929bb40> True
DBCardID: cardid mfguid setTCGUid printing
0 148982 RVD026 RVD Rhinar Edition Normal
Length: 1
CardId 0: cardid mfguid setTCGUid printing
0 148982 RVD026 RVD Rhinar Edition Normal
Card Last Updated SQL Query: SELECT card_lastupdate from g1st_fabdb.marketdata WHERE cardid=148982 ORDER BY card_lastupdate DESC LIMIT 1
Card Last Updated in DB: None
In API: 2023-05-26 21:53:45

***** In Set: RVD Card In set: 026 SETID: RVD026 *****

***** Current CardCount total: 251 *****

---

Executing SQL Query: "SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\1 Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
Values:"%DRO001%"
"DRO
Card Data: []
False
DBCursor Status: <mariadb.connection connected to 'localhost' at 0xffff8929bb40> True
DBCardID: False
Traceback (most recent call last):
File "/home/biqu/scripts/api-dbupdate.py", line 367, in <module>
if Debug : print("Length:",len(dfCardId))
TypeError: object of type 'bool' has no len()

---------------------------------------------------------------------------------------

python3 function: 

def get_dfCardId(SetID,sName):
#replace with cardID identification function .,+6
if not mydberror :
my_cardid_query="SELECT cardid,mfguid,setTCGUid ,CONCAT_WS(' ',REGEXP_REPLACE(REGEXP_REPLACE(ed.editionName,'^(.*)(| Edition)$','\\1 Edition'),'(Alpha Print Edition|1st Edition Edition)','1st Edition'),REPLACE(f.finishName,'Regular','Normal')) AS printing from g1st_fabdb.cards left join g1st_fabdb.setNames sn ON cards.setNameId=sn.setNameId LEFT JOIN g1st_fabdb.finishes f ON cards.finishId = f.finishId LEFT JOIN g1st_fabdb.editions ed ON cards.editionId = ed.editionId WHERE ( mfguid LIKE %s )AND setTCGUid = %s ;"
mydbSetId="%{}%"
if Debug: print("Executing SQL Query: &quot;"+my_cardid_query+"&quot;\n\tValues:&quot;"+mydbSetId.format(SetID)+"&quot;\n\t\t&quot;"+sName)
myconn_ro=mariadb.connect(**mydbparams)
mydbcursor=myconn_ro.cursor()
mydbcursor.execute(my_cardid_query,(mydbSetId.format(SetID),sName))
dbCardId = mydbcursor.fetchall()
if Debug and myconn_ro.warnings > 0: print("Warnings: ",myconn_ro.warnings,"\n\t",myconn_ro.show_warnings)
mydbcursor.close()
myconn_ro.reset()
if Debug: print("\t\tCard Data: ",dbCardId)
if len(dbCardId)>0:
dfCardId = pd.DataFrame(dbCardId,index=None,columns=('cardid','mfguid','setTCGUid','printing'))
else:
dfCardId = False # pd.DataFrame({'cardid':[''],'mfguid':[''],'setTCGUid':[''],'printing':['']})
print(dfCardId)
if Debug: print("DBCursor Status: ",mydbcursor.connection," ",mydbcursor.closed)
myconn_ro.close()
return dfCardId

Summary:
Python function runs a select statement. on the 252nd iteration with different WHERE mfguids (mind you I have rearranged things and it is always on the 252 iteration regardless of the WHERE clause) it always returns no data from the cursor.fetchall()
Initially the function was reusing the same connection initialized only once at start of main script, I have since tried variants using ConnectionPool, (which the connection.close() statements never seemed to release the connections back top the pool),  executing the cursor and connection close methods in order and finally the current iteration which should be creating a new connection using the same parameters on each iteration and destroying it (which does not appear to be happening as the memory address seems to remain unchanged each iteration.  Still it always returns no data, no warnings, no errors and no exceptions resulting in a failure due to the lack of data to return. So either I found a really corner bug  (which I think someone would have hit before now) or I am doing something wrong.  Any suggestions are much appreciated.

</details>


# 答案1
**得分**: 0

调查显示，数据库损坏导致近2000行的空查询。令人尴尬的是，每次尝试重新排序查询时，我都巧合地在252处触发了损坏。

<details>
<summary>英文:</summary>

and Survey says. DB Corruption resulting in an empty query on nearly 2000 rows.. it was coincidence that I somehow kept hitting the corruption  at 252 every time even when I tried reordering the queries....embarassing

</details>