converting a mysql database

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

converting a mysql database

Dave Dyer

I'm taking a test cut at converting a existing mysql database
to sqlite.  I dumped the mysql database, tweaked the prototype
into slite format, and converted the escape characters in the
data to standard sql format.  Here's what happens when I attempt
an import:

sqlite> .read proto.txt
sqlite> .read city-part.sql
INSERT INTO city VALUES (112.0000,7617.0000,'Hartford',41.7640,-72.6860);
SQL error: unable to open database file
INSERT INTO city VALUES (52.0000,6583.0000,'Eindhoven',51.4500,5.4670);
SQL error: unable to open database file
INSERT INTO city VALUES (13.0000,5828.0000,'Bradford',50.5480,-4.6610);
SQL error: unable to open database file
...

These sql errors are random. Since there's no other activity
affecting this database, I wouldn't expect any contention problem.
Is there something I'm missing?


Oh yea, and this is EXTREMELY slow, presumably because I haven't
wrapped the inserts in a transaction.

Reply | Threaded
Open this post in threaded view
|

Re: converting a mysql database

Ulrik Sandborg-Petersen
Hi Dave,

Dave Dyer wrote:

>I'm taking a test cut at converting a existing mysql database
>to sqlite.  I dumped the mysql database, tweaked the prototype
>into slite format, and converted the escape characters in the
>data to standard sql format.  Here's what happens when I attempt
>an import:
>
>sqlite> .read proto.txt
>sqlite> .read city-part.sql
>INSERT INTO city VALUES (112.0000,7617.0000,'Hartford',41.7640,-72.6860);
>SQL error: unable to open database file
>INSERT INTO city VALUES (52.0000,6583.0000,'Eindhoven',51.4500,5.4670);
>SQL error: unable to open database file
>INSERT INTO city VALUES (13.0000,5828.0000,'Bradford',50.5480,-4.6610);
>SQL error: unable to open database file
>...
>
>These sql errors are random. Since there's no other activity
>affecting this database, I wouldn't expect any contention problem.
>Is there something I'm missing?
>  
>

Without knowing the contents of proto.txt, I'd say you probably have
defective RAM in your machine, or a defective harddrive. If your machine
is a x86, you can Google memtest86 to get an excellent free tool for
checking for RAM defects.



>Oh yea, and this is EXTREMELY slow, presumably because I haven't
>wrapped the inserts in a transaction.
>
>  
>

Very, very likely, that shuld be the cause of the slowness.

Ulrik


Reply | Threaded
Open this post in threaded view
|

Re: converting a mysql database

Dave Dyer
In reply to this post by Dave Dyer

I looked into this, and the actual problem is windows returning
a "access denied" error code when trying to recreate the journal
file immediately after deleting it.   I can't find any documentation
that says create might fail for this reason, but filemon (from
systeminternals.com) says the status is "delete pending" in this
situation.  So I think this is a genuine windows glitch, which
might be related to my having a fast, dual-core processor.

In any case, it seems to be possible to paper over the problem
by waiting a few milliseconds and retrying the create.

Reply | Threaded
Open this post in threaded view
|

Re: Re: converting a mysql database

Roger Binns
> I can't find any documentation that says create might fail for this reason,

One likely cause is tag-along programs such as virus checkers, content indexers,
backup agents etc.  They like to open files immediately after an application
has written to one and then closed the file.  And while they hold it open you
can't delete it.

If you use Subversion on Windows you sometimes encounter this.  Subversion
creates and renames a lot of files.  They even have an FAQ entry about this:

  http://subversion.tigris.org/faq.html#windows-access-denied

You can read more in the bug it references:

  http://svn.haxx.se/dev/archive-2003-10/0136.shtml

The only solution is for apps that see access denied on Windows when they shouldn't
to loop a few times to see if the situation resolves itself.

Roger

Reply | Threaded
Open this post in threaded view
|

Re: converting a mysql database

Dave Dyer
In reply to this post by Dave Dyer

the thread that Roger Binns pointed out (with similar problems
in an app called "subversion") seems to be right on the money.
The problem is associated with unrelated processes doing background
file operations.  I wrote a test program, and found it fails
for me if I run it in a directory which is shared using windows'
file sharing for macintosh.  This is a feature present only on
winxx-server, but I suspect it will also fail in other, more common,
configurations.  Needless to say, it's extremely serious if CreateFile
can fail for no locally apparent reason!


Here is the test program:

--

#include <windows.h>
#include <stdio.h>

#define filename "test.file"
#define fileflags FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_RANDOM_ACCESS | FILE_FLAG_DELETE_ON_CLOSE
int main(int argc,char **argv,char **envp)
{
        long i;
        for(i=0;;i++)
        { char buf[100];
                char rbuf[100];
                char errbuf[256];
                HANDLE ff = CreateFile(filename,
                                   GENERIC_READ | GENERIC_WRITE,
                                   0,
                                   NULL,
                                   CREATE_ALWAYS,
                                   fileflags,
                                   NULL);
           if(ff==INVALID_HANDLE_VALUE)
                { sprintf(errbuf,"gen %d file create error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);

                };
           sprintf(buf,"file generation %d\n",i);
           { long len=strlen(buf);
             long wlen = 0;
                 
                 if((0==WriteFile(ff,buf,len,&wlen,NULL)) || (wlen!=len))
                {
                sprintf(errbuf,"gen %d file write error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);
                };

                if(0!=SetFilePointer(ff,0,NULL,SEEK_SET))
                { sprintf(errbuf,"gen %d file seek error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);
                };

                 if((0==ReadFile(ff,rbuf,len,&wlen,NULL))|| (wlen!=len))
                { sprintf(errbuf,"gen %d file read error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);
                };
                if(memcmp(rbuf,buf,len)!=0)
                { sprintf(errbuf,"gen %d file compare error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);
                };

                 if(0==CloseHandle(ff))
                { sprintf(errbuf,"gen %d file close error %d\n",i,GetLastError());
                perror(errbuf);
                exit(1);
                };
                         

           }
        }
}

Reply | Threaded
Open this post in threaded view
|

Re: Re: converting a mysql database

Arjen Markus
In reply to this post by Dave Dyer
Dave Dyer wrote:

>
> I looked into this, and the actual problem is windows returning
> a "access denied" error code when trying to recreate the journal
> file immediately after deleting it.   I can't find any documentation
> that says create might fail for this reason, but filemon (from
> systeminternals.com) says the status is "delete pending" in this
> situation.  So I think this is a genuine windows glitch, which
> might be related to my having a fast, dual-core processor.
>
> In any case, it seems to be possible to paper over the problem
> by waiting a few milliseconds and retrying the create.

I have had weird problems in the past on a Windows platform
with the deletion and re-creation of files. I have never
been able to figure out what happened.

I am not familiar with inner workings of SQLite but I can imagine
that this will not be a problem anymore when you use a
transaction instead of individual inserts.

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

Re: Re: converting a mysql database

Edwin Knoppert
If the mysql db is reachable through dsn (ado-providerstring) you can use my
pbsdbms tool to import tables.

http://www.hellobasic.com




----- Original Message -----
From: "Arjen Markus" <[hidden email]>
To: <[hidden email]>
Sent: Monday, November 07, 2005 8:43 AM
Subject: Re: [sqlite] Re: converting a mysql database


> Dave Dyer wrote:
>>
>> I looked into this, and the actual problem is windows returning
>> a "access denied" error code when trying to recreate the journal
>> file immediately after deleting it.   I can't find any documentation
>> that says create might fail for this reason, but filemon (from
>> systeminternals.com) says the status is "delete pending" in this
>> situation.  So I think this is a genuine windows glitch, which
>> might be related to my having a fast, dual-core processor.
>>
>> In any case, it seems to be possible to paper over the problem
>> by waiting a few milliseconds and retrying the create.
>
> I have had weird problems in the past on a Windows platform
> with the deletion and re-creation of files. I have never
> been able to figure out what happened.
>
> I am not familiar with inner workings of SQLite but I can imagine
> that this will not be a problem anymore when you use a
> transaction instead of individual inserts.
>
> Regards,
>
> Arjen
>
>