Strange MariaDB save problem.

[Horizon]

Hi,

I have lots of pdf file and I want to save them to related records in LONGTEXT field. It saves for a while and surprisingly after storing about 1580 mb, the application of oRs2:Save() function ends without any error. The hb_error file does not generated either.

How can I solve this problem?

Code: Select all  Expand view

oRs2 := oTb_ICBELGE:Open({{"IC_NO", oData:IID}}) // it generates cSQL and run Rowset()
IF !EMPTY(oRs2) .AND. oRs2:Reccount()>0
    oRs2:GoTop()
    DO WHILE !oRs2:Eof()       
        oData2 := TUM_BilgiOku_SQL(oRs2)
        cMYFileName := MY_FileName(oMyFoy, oData2:IC_ID, oData2:IC_TYPE)
        IF FILE(cMYFileName)
            oRs2:IC_MEMO := MemoRead(cMYFileName)
            oRs2:Save()
        ENDIF
        oRs2:SKIP()
    ENDDO
ENDIF
oRs2:End()

[vilian]

I only use Memoread to read txt files. To read any others type I use Fread.
I also think BLOB fields are better to save PDF files.

[nageswaragunupudi]

Please use LONGBLOB field type.

[Horizon]

Please use LONGBLOB field type.

Hi Mr. Rao,

As you and Villian suggested I have changed it to "LONGBLOB" and tried again. The same error occurs.

I suspect my pdf file in my queue list. I have started again from next pdf file in list. it was ok for about 1500 mb. again and application abort without any error and error file.

Any idea?

ps. my.ini

Code: Select all  Expand view

[mysqld]
datadir=D:\SQLData
port=3306
innodb_buffer_pool_size=2043M
character-set-server=latin1
collation-server=latin1_swedish_ci
wait_timeout = 28800
max_allowed_packet = 500M
[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3/lib/plugin

[vilian]

Instead of read the file with Memoread, please try to read with the function bellow:

Code: Select all  Expand view

#INCLUDE "FileIO.ch"
FUNCTION ReadFile(cFile)
LOCAL nArq,nLen,cTxt
      nArq := Fopen(cFile)
      nLen := FSeek( nArq, 0, FS_END )
      FSeek( nArq, 0, FS_SET )
      cTxt:=Space(nLen)
      FRead( nArq, @cTxt, nLen)
RETURN cTxt
 

[Horizon]

Instead of read the file with Memoread, please try to read with the function bellow:

Code: Select all  Expand view

#INCLUDE "FileIO.ch"
FUNCTION ReadFile(cFile)
LOCAL nArq,nLen,cTxt
      nArq := Fopen(cFile)
      nLen := FSeek( nArq, 0, FS_END )
      FSeek( nArq, 0, FS_SET )
      cTxt:=Space(nLen)
      FRead( nArq, @cTxt, nLen)
RETURN cTxt
 

I have tried. Same error has occured.

[vilian]

Look at the directory if you have a hb_out.log there post its content here please.

[Horizon]

Look at the directory if you have a hb_out.log there post its content here please.

Unfortunately, there is no such file.

[vilian]

Please, do one test else.
Open task manager and run your program. Pay attention to the column of memory used. Let us know if it's increasing.

[Horizon]

Please, do one test else.
Open task manager and run your program. Pay attention to the column of memory used. Let us know if it's increasing.

Yes, You are right. It is increasing.

[Horizon]

Hi Mr. Rao, Villian,

This code is always increasing memory.

Code: Select all  Expand view

oRs := oApp:oBOLUM_Cn:RowSet("select * from icrakart")
IF !EMPTY(oRs) .AND. oRs:Reccount()>0
    oRs:GOTOP()
   
    XBROWSER oRs                <---

    DO WHILE !oRs:Eof()    

        oRs2 := oApp:oBOLUM_Cn:RowSet( "select * from icbelge where IC_NO="+NTRIM(oRs:IID) )
        IF !EMPTY(oRs2) .AND. oRs2:Reccount()>0
            oRs2:GoTop()
            DO WHILE !oRs2:Eof()       

                oRs2:SKIP()
            ENDDO
        ENDIF
        oRs2:End()
        oRs2:=nil

        oRs:Skip()
    ENDDO
    oRs:End()
ENDIF

This code is memory stable. There is no change.

Code: Select all  Expand view

oRs := oApp:oBOLUM_Cn:RowSet("select * from icrakart")
IF !EMPTY(oRs) .AND. oRs:Reccount()>0
    oRs:GOTOP()
   
    XBROWSER oRs                <---

    DO WHILE !oRs:Eof()    



        oRs:Skip()
    ENDDO
    oRs:End()
ENDIF

What is wrong? Thanks.

[vilian]

Could you try by this way ?

Code: Select all  Expand view

oRs := oApp:oBOLUM_Cn:RowSet("SELECT icc.*,icb.IC_NO FROM icrakart AS icc RIGHT JOIN icbelge AS icb ON icc.IID = icb.IC_NO")
IF oRs:Reccount()>0

    DO WHILE .NOT. oRs:Eof()    
          nIID := oRs:IID
            DO WHILE  nIID = oRs:IID .AND. .NOT. oRs:Eof()        

                oRs:SKIP()
            ENDDO
        ENDIF
    ENDDO
    oRs:End()
ENDIF
 

[Horizon]

Could you try by this way ?

Code: Select all  Expand view

oRs := oApp:oBOLUM_Cn:RowSet("SELECT icc.*,icb.IC_NO FROM icrakart AS icc RIGHT JOIN icbelge AS icb ON icc.IID = icb.IC_NO")
IF oRs:Reccount()>0

    DO WHILE .NOT. oRs:Eof()    
          nIID := oRs:IID
            DO WHILE  nIID = oRs:IID .AND. .NOT. oRs:Eof()        

                oRs:SKIP()
            ENDDO
        ENDIF
    ENDDO
    oRs:End()
ENDIF
 

Hi Villian,

I have tried your solution and it incread one time with no error. but in this case (with join) icbelge table is readonly. I can not replace IC_MEMO field with new value.

I think RowSet method has a memory leak. With oRs:End() does not release memory as much as it allocates. Every time create a oRs with Rowset increasing memory used.

[vilian]

Maybe Mr Rao can help you with this memory leak. Meanwhile you can try surround the problem using an UPDATE command instead of oRs:Save(). Something like this:

Code: Select all  Expand view

oApp:oBOLUM_Cn:Execute("UPDATE icbelge SET IC_MEMO = "+oApp:oBOLUM_Cn:ValToSql( MemoRead(cMYFileName) ) +" WHERE IC_NO = "+oApp:oBOLUM_Cn:ValToSql( oRs:IC_NO ))

[Horizon]

Thank you very much Villian,

I have optimized and changed my code like that.

Code: Select all  Expand view

oRs := oTb_ICBELGE:Open("SELECT IC_ID, IC_NO, IC_TYPE FROM icbelge")
IF !EMPTY(oRs) .AND. oRs:Reccount()>0
    oRs:GOTOP()
   
    XBROWSER oRs

    Sira := 0
    oSay1:SetText(ALLTRIM(oAktif_Bolum:BL_2))
    oMeter:Set(0)
    CursorWait()

    oMeter:nTotal := oRs:RECCOUNT()
    Toplam := oRs:Reccount()
   
    DO WHILE !oRs:Eof()    
        Sira++
        oMeter:Set( Sira )
        oSay2:SetText(NTRIM(Sira)+"("+NTRIM(oRs:IC_ID)+") / "+NTRIM(oMeter:nTotal))

        cMYFileName := MY_FileName(,oRs:IC_ID,oRs:IC_TYPE,oRs:IC_NO)
        IF FILE(cMYFileName)
       
            oApp:oBOLUM_Cn:Execute("UPDATE icbelge SET IC_MEMO = "+;
                oApp:oBOLUM_Cn:ValToSql( MemoRead(cMYFileName) ) +;
                " WHERE IC_NO = "+oApp:oBOLUM_Cn:ValToSql( oRs:IC_NO ))
       
        ENDIF
                       
        oRs:Skip()
    ENDDO
ENDIF   

It is very very SLOW. I have 272.000 records. I have tested 1000 records for an hour.

Mr. Rao, any advice?