FiveTech Software tech support forums

www.FiveTechSoft.com
https://fivetechsupport.com/forums/

Strange MariaDB save problem.

https://fivetechsupport.com/forums/viewtopic.php?f=3&t=39114

Page 1 of 2

Strange MariaDB save problem.

PostPosted: Thu Jun 18, 2020 8:35 pm
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  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()

Re: Strange MariaDB save problem.

PostPosted: Thu Jun 18, 2020 8:59 pm
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.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 5:06 pm
by nageswaragunupudi
Please use LONGBLOB field type.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 6:27 pm
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  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

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 7:47 pm
by 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
 

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 7:59 pm
by Horizon
vilian wrote: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.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 8:10 pm
by vilian
Look at the directory if you have a hb_out.log there post its content here please.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 8:18 pm
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.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 8:43 pm
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.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 9:00 pm
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.

Re: Strange MariaDB save problem.

PostPosted: Fri Jun 19, 2020 10:51 pm
by 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.

Re: Strange MariaDB save problem.

PostPosted: Sat Jun 20, 2020 12:19 am
by 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
 

Re: Strange MariaDB save problem.

PostPosted: Sat Jun 20, 2020 12:24 pm
by Horizon
vilian wrote: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.

Re: Strange MariaDB save problem.

PostPosted: Sat Jun 20, 2020 1:22 pm
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  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 ))

Re: Strange MariaDB save problem.

PostPosted: Sun Jun 21, 2020 7:28 pm
by 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?
All times are UTC
Page 1 of 2
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/