sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

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

sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

v00d00 dave
hi!

versions:
sqlite3: v3.2.1 with corresponding version of libsqlite3
(/usr/lib/libsqlite3.so.0.8.6)
testing oses: ubuntu hoary and debian sarge

i get various errors, if importing the attached file with

---
rm -f myproject.sqlite3
cat myproject.mysqldump \
         | sed "s/\`/\"/g" \
         | sed -e 's/TYPE=MyISAM/ /g' \
         | sqlite3 myproject.sqlite3
---

first of all, if i only try to import the script, i get:

---
INSERT INTO "conffile" VALUES (0,'/etc/samba/smb.conf','samba main
config file',NULL);
SQL error: no such table: conffile
---

even it was just created.
i "fixed" this problem with creating a dummy table BEFORE importing with:

---
echo "create table dummy(i integer not null primary key);" | sqlite3
# inserting of the script here
echo "drop table dummy;" | sqlite3 sykconf.sqlite3
---

not nice, but worked until now

i did my tests this way, and was happy since it worked - but now, with
the attached db-schema, i get:

---
SQL error: database disk image is malformed
---

hello ? i deleted the db, and re-created it from scratch - so, how can
the image be malformed ?
and: we have a side-effect, since if i change the order of the creation
of tables in the .mysqldump file, all works fine - by just changing the
order of the statements, not the statements themself. i guess, my input
is okay, but there is something in the statements, which confuses sqlite3

so, i am at a point, where i dont know, how to continue - i really need
help - i guess, i am doing something very wrong, and dont know what.

background: why do i use mysql is simple: i miss good
configuration/admin tools for a sqlite db - i could write an sql script
in a text editor - i am able to do so, but i prefer a "gui way" today.
so, i have a mysqldb, which i administer with my favorite tool, and then
just "dump" the db into a sqlite db - so, i have both: the nice user
interface plus admin tools of mysql and the simplicity of sqlite - if
you know a better way, tell me. without above errors, that concept would
work very well and fit all my needs.


-- MySQL dump 9.11
--
-- Host: localhost    Database: sykconf
-- ------------------------------------------------------
-- Server version 4.0.23_Debian-3ubuntu2-log

--
-- Table structure for table `blacklist`
--

CREATE TABLE `blacklist` (
  `blcfid` int(11) NOT NULL default '0',
  `blndid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`blcfid`,`blndid`)
) TYPE=MyISAM;

--
-- Dumping data for table `blacklist`
--


--
-- Table structure for table `conffile`
--

CREATE TABLE `conffile` (
  `cfcfid` int(11) NOT NULL default '0',
  `cffname` varchar(255) NOT NULL default '',
  `cfdescr` varchar(255) default NULL,
  `cftemplate` longtext,
  PRIMARY KEY  (`cfcfid`)
) TYPE=MyISAM;

--
-- Dumping data for table `conffile`
--

INSERT INTO `conffile` VALUES (0,'/etc/samba/smb.conf','samba main config file',NULL);
INSERT INTO `conffile` VALUES (1,'/etc/postfix/master.cf','postfix master file',NULL);
INSERT INTO `conffile` VALUES (2,'/etc/apache2/apache2.conf','main config file of apache2',NULL);

--
-- Table structure for table `confparams`
--

CREATE TABLE `confparams` (
  `cpcpid` int(11) NOT NULL default '0',
  `cpcfid` int(11) NOT NULL default '0',
  `cpndid` int(11) NOT NULL default '0',
  `cpcsid` int(11) NOT NULL default '0',
  `cpreplace` mediumtext,
  PRIMARY KEY  (`cpcpid`)
) TYPE=MyISAM;

--
-- Dumping data for table `confparams`
--

INSERT INTO `confparams` VALUES (0,0,0,0,'sykosch');
INSERT INTO `confparams` VALUES (1,0,1,0,'simpsons');
INSERT INTO `confparams` VALUES (2,0,0,1,'sylix samba server');

--
-- Table structure for table `confsearchfor`
--

CREATE TABLE `confsearchfor` (
  `cscsid` int(11) NOT NULL default '0',
  `cscfid` int(11) NOT NULL default '0',
  `cssearch` varchar(255) NOT NULL default '',
  `csdescr` mediumtext NOT NULL,
  PRIMARY KEY  (`cscsid`)
) TYPE=MyISAM;

--
-- Dumping data for table `confsearchfor`
--

INSERT INTO `confsearchfor` VALUES (0,0,'$$WORKGROUP','Setzt die Arbeitsgruppe der SMB-Domain');
INSERT INTO `confsearchfor` VALUES (1,0,'$$SERVERSTRING','Setzt den Serverstring des SMB-Servers');

--
-- Table structure for table `node`
--

CREATE TABLE `node` (
  `ndndid` int(11) NOT NULL default '0',
  `ndname` varchar(50) default NULL,
  `ndcomment` varchar(255) default NULL,
  PRIMARY KEY  (`ndndid`)
) TYPE=MyISAM;

--
-- Dumping data for table `node`
--

INSERT INTO `node` VALUES (0,'Default Values','Here we get all the default-values');
INSERT INTO `node` VALUES (1,'Root Node','Here should all the config data be located. All settings are saved in this node.');
INSERT INTO `node` VALUES (2,'Cluster Node I','Will be used in forthcoming Cluster Version');
INSERT INTO `node` VALUES (3,'Cluster Node II','Will be used in forthcoming Cluster Version');

Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

Martin Jenkins

>CREATE TABLE `blacklist` (
>  `blcfid` int(11) NOT NULL default '0',
>  `blndid` int(11) NOT NULL default '0',
>  PRIMARY KEY  (`blcfid`,`blndid`)
>) TYPE=MyISAM;
>  
>
If you run the sqlite3 command line utility and .read your script you'll
see why it doesn't work - you need to change the back-ticks (`) to
single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
malformed messages, so can't help there.

Martin
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

v00d00 dave
Martin Jenkins schrieb:

>
>> CREATE TABLE `blacklist` (
>>  `blcfid` int(11) NOT NULL default '0',
>>  `blndid` int(11) NOT NULL default '0',
>>  PRIMARY KEY  (`blcfid`,`blndid`)
>> ) TYPE=MyISAM;
>>  
>>
> If you run the sqlite3 command line utility and .read your script you'll
> see why it doesn't work - you need to change the back-ticks (`) to
> single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
> malformed messages, so can't help there.
>
> Martin
>

hi! of course, i do so - like i wrote in the first email:

---
cat myproject.mysqldump \
         | sed "s/\`/\"/g" \
         | sed -e 's/TYPE=MyISAM/ /g' \
         | sqlite3 myproject.sqlite3
---

like mentioned before: the syntax of the sql is fine - if i change the
ORDER of the tables manually, it runs fine.

thanks anyways
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

Ben Clewett
In reply to this post by Martin Jenkins
When executing 'mysqldump', use the '--compatible=ansi' flag.

Ben

Martin Jenkins wrote:

>
>> CREATE TABLE `blacklist` (
>>  `blcfid` int(11) NOT NULL default '0',
>>  `blndid` int(11) NOT NULL default '0',
>>  PRIMARY KEY  (`blcfid`,`blndid`)
>> ) TYPE=MyISAM;
>>  
>>
> If you run the sqlite3 command line utility and .read your script you'll
> see why it doesn't work - you need to change the back-ticks (`) to
> single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
> malformed messages, so can't help there.
>
> Martin
>

Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

v00d00 dave
hi!

i found a solution: the problem only occurs on ubuntu(hoary) machines.
i tried my scripts on a plain sarge machine, and it worked fine!
shame on me: i _thought_ i did try on sarge before, but it was an ubuntu
machine, too.

if anyone is still interested in this bug, feel free to email, i will
try helping tracing it down.

i really wonder, what might be wrong with hoary, so those unpredictable
strange error occur...

thanks anyways
dave

Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

Martin Jenkins
In reply to this post by v00d00 dave
v00d00 dave wrote:

> Martin Jenkins schrieb:
>
>>
>>> CREATE TABLE `blacklist` (
>>>  `blcfid` int(11) NOT NULL default '0',
>>>  `blndid` int(11) NOT NULL default '0',
>>>  PRIMARY KEY  (`blcfid`,`blndid`)
>>> ) TYPE=MyISAM;
>>>  
>>>
>> If you run the sqlite3 command line utility and .read your script
>> you'll see why it doesn't work - you need to change the back-ticks
>> (`) to single quotes (') and delete "TYPE=MyISAM". I didn't see any
>> disk image malformed messages, so can't help there.
>>
>> Martin
>>
>
> hi! of course, i do so - like i wrote in the first email:
>
> ---
> cat myproject.mysqldump \
>         | sed "s/\`/\"/g" \
>         | sed -e 's/TYPE=MyISAM/ /g' \
>         | sqlite3 myproject.sqlite3
> ---
>
> like mentioned before: the syntax of the sql is fine - if i change the
> ORDER of the tables manually, it runs fine.
>
> thanks anyways

Doh! So you did. :)  FWIW, I made the changes manually and the script
imports with no errors.

Martin

Reply | Threaded
Open this post in threaded view
|

sqlite3_set_authorizer

Marco Bambini
In reply to this post by v00d00 dave
 From the official documentation of the sqlite3_set_authorizer routine:
"This routine registers a callback with the SQLite library. The  
callback is invoked (at compile-time, not at run-time) for each  
attempt to access a column of a table in the database."

What does means at compile-time and not at run-time?
It means that is checked when the sqlite3_prepare routine is invoked?

Thanks a lot for you help.
Regards,
Marco Bambini

smime.p7s (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"

Martin Jenkins
In reply to this post by Martin Jenkins
Martin Jenkins wrote:

> v00d00 dave wrote:
>
>> Martin Jenkins schrieb:
>>
>>>
>>>> CREATE TABLE `blacklist` (
>>>>  `blcfid` int(11) NOT NULL default '0',
>>>>  `blndid` int(11) NOT NULL default '0',
>>>>  PRIMARY KEY  (`blcfid`,`blndid`)
>>>> ) TYPE=MyISAM;
>>>>  
>>>>
>>> If you run the sqlite3 command line utility and .read your script
>>> you'll see why it doesn't work - you need to change the back-ticks
>>> (`) to single quotes (') and delete "TYPE=MyISAM". I didn't see any
>>> disk image malformed messages, so can't help there.
>>>
>>> Martin
>>>
>>
>> hi! of course, i do so - like i wrote in the first email:
>>
>> ---
>> cat myproject.mysqldump \
>>         | sed "s/\`/\"/g" \
>>         | sed -e 's/TYPE=MyISAM/ /g' \
>>         | sqlite3 myproject.sqlite3
>> ---
>>
>> like mentioned before: the syntax of the sql is fine - if i change
>> the ORDER of the tables manually, it runs fine.
>>
>> thanks anyways
>
>
> Doh! So you did. :)  FWIW, I made the changes manually and the script
> imports with no errors.

I forgot to say this is with sqlite 3.2.1 on XP SP2. Sorry for the lack
of rigour - hay fever's getting to me.

Martin
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_set_authorizer

Dan Kennedy
In reply to this post by Marco Bambini


--- Marco Bambini <[hidden email]> wrote:

>  From the official documentation of the sqlite3_set_authorizer routine:
> "This routine registers a callback with the SQLite library. The  
> callback is invoked (at compile-time, not at run-time) for each  
> attempt to access a column of a table in the database."
>
> What does means at compile-time and not at run-time?
> It means that is checked when the sqlite3_prepare routine is invoked?

Correct.


               
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs