Strange MariaDB save problem.

Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Strange MariaDB save problem.

Post by 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

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()
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by 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.
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Strange MariaDB save problem.

Post by nageswaragunupudi »

Please use LONGBLOB field type.
Regards

G. N. Rao.
Hyderabad, India
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

nageswaragunupudi wrote: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

[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
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by vilian »

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

Code: Select all

#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
 
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

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

Code: Select all

#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.
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by vilian »

Look at the directory if you have a hb_out.log there post its content here please.
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

vilian wrote:Look at the directory if you have a hb_out.log there post its content here please.
Unfortunately, there is no such file.
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by 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.
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

vilian wrote: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.
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

Hi Mr. Rao, Villian,

This code is always increasing memory.

Code: Select all

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

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.
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by vilian »

Could you try by this way ?

Code: Select all

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
 
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

vilian wrote:Could you try by this way ?

Code: Select all

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.
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
User avatar
vilian
Posts: 795
Joined: Wed Nov 09, 2005 2:17 am
Location: Brazil
Contact:

Re: Strange MariaDB save problem.

Post by 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

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 ))
Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil
Horizon
Posts: 997
Joined: Fri May 23, 2008 1:33 pm

Re: Strange MariaDB save problem.

Post by Horizon »

Thank you very much Villian,

I have optimized and changed my code like that.

Code: Select all

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?
Regards,

Hakan ONEMLI

Harbour & VS 2019 & FWH 20.12
Post Reply