Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
On Fri, 21 Mar 2008 08:54:22 +0100, you wrote: >I actually created all my views by means of "CREATE VIEW ... AS ...", and >(as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL >statements. >The problem (was) that right now I couldn't open the DB anymore, due to the >malformed schema error. Ok, I understand now. >Thanks again >Marco -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
On Fri, Mar 21, 2008 at 10:12 AM, Noah Hart <[EMAIL PROTECTED]> wrote: > Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse > the SQL in sqlite_master, looking for errors. > > Regards, > > Noah I don't think that would actually help. It seems that this problem was caused by older versions of SQLite accepting certain invalid SQL syntax. Since the bad syntax was accepted by the older parser, it's not going to just start rejecting the database. What I would recommend is a twofold change: 1. Improve the error message -- perhaps display the table/view name and/or full SQL that it couldn't parse 2. When PRAGMA writable_schema=ON, treat schema errors as warnings and simply disallow access to the affected tables/views. This shouldn't cause any compatibility problems because nobody should be using writable_schema anyway. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse the SQL in sqlite_master, looking for errors. Regards, Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MarcoN Sent: Friday, March 21, 2008 12:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Malformed database schema with SQLite version > 3.5.x I actually created all my views by means of "CREATE VIEW ... AS ...", and (as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL statements. The problem (was) that right now I couldn't open the DB anymore, due to the malformed schema error. Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
I actually created all my views by means of "CREATE VIEW ... AS ...", and (as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL statements. The problem (was) that right now I couldn't open the DB anymore, due to the malformed schema error. Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Kees Nuyt <[EMAIL PROTECTED]> wrote: > > >I know I can't create an invalid view, > >because SQLite refuses to create it, > >but this seems a problem to me... could SQLite just open the database, and > >complain only on the invalid views (i.e. for instance when I open the view > >to query the data in it)? > > If you define views the way you are supposed to define > them, with SQL: > > CREATE VIEW viewname AS > SELECT .. > ; > > they are validated against the schema immediately, and > rejected if they are not valid. Views that refer to > tables or columns that no longer exist won't give a > schema error, but an error about what's missing when > the view is executed, just like an invalid SELECT > statement would do. > > So, it is not a problem until you create your own > problem by using undocumented, unsupported backdoors. > Consider it the same as patching table pages by > hex-editing the database file. > > Feel free to do it, but don't expect a safety net. > I think there must have been a bug in older versions of SQLite that allowed some invalid VIEWs to be inserted into the sqlite_master table. I don't think Marco was messing around with the writable_schema pragma in order to insert the invalid VIEWs. He just happened to have the misfortune of using a version of SQLite that failed to completely validate his input. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
On Thu, 20 Mar 2008 10:23:23 +0100, you wrote: >...oopss.. I supposed it was a fault of mine. >Thanks for the support and the help. > >Just two points: > >1. the "PRAGMA writable_schema=ON" is not described in >the "official" documentation for the PRAGMA syntax It's not a supported PRAGMA. In fact, it is very dangerous to use. It's there for debugging, recovery(?) and testing only, and shouldn't be used as a replacement for SQL. >2. does this mean that if I create an invalid view in the database, the >entire database file becomes unreadable because of the "schema error"? Yes, if you insert invalid SQL (or any other value) in sqlite_master by means of "writable_schema" you can easily damage your database beyond repair. >I know I can't create an invalid view, >because SQLite refuses to create it, >but this seems a problem to me... could SQLite just open the database, and >complain only on the invalid views (i.e. for instance when I open the view >to query the data in it)? If you define views the way you are supposed to define them, with SQL: CREATE VIEW viewname AS SELECT .. ; they are validated against the schema immediately, and rejected if they are not valid. Views that refer to tables or columns that no longer exist won't give a schema error, but an error about what's missing when the view is executed, just like an invalid SELECT statement would do. So, it is not a problem until you create your own problem by using undocumented, unsupported backdoors. Consider it the same as patching table pages by hex-editing the database file. Feel free to do it, but don't expect a safety net. >Thanks again >Marco -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
...oopss.. I supposed it was a fault of mine. Thanks for the support and the help. Just two points: 1. the "PRAGMA writable_schema=ON" is not described in the "official" documentation for the PRAGMA syntax 2. does this mean that if I create an invalid view in the database, the entire database file becomes unreadable because of the "schema error"? I know I can't create an invalid view, because SQLite refuses to create it, but this seems a problem to me... could SQLite just open the database, and complain only on the invalid views (i.e. for instance when I open the view to query the data in it)? Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
MarcoN <[EMAIL PROTECTED]> wrote: > Hello, everybody. > > I have the following problem: I have an old project that uses a database > created with an older SQLite library version. > Now, since I updated SQLite to 3.5.5, I can't use the database anymore, > because any query on the database tables returns: > > SQLite error 11 - Malformed database schema - near ")": syntax error > There is a syntax error in many of your VIEW definitions. A typical example is _TestViewExtra where you have: MonthType IN (1, 2, ) There is an extra comma after the "2". To fix this, I suggest dropping all views from the database as follows: (1) Start the CLI: sqlite3 baddatabase.db (2) Enter: "PRAGMA writable_schema=ON". (3) Enter: "select * from sqlite_master". Ignore the error. (4) Enter: "DELETE FROM sqlite_master WHERE type='view'" (5) Exit the CLI Then go back and recreate your views using valid syntax. There was apparently a bug in older versions of SQLite that allowed the incorrect syntax to get through without raising an error. That bug has now been fixed, which made your database unreadable. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
MarcoN <[EMAIL PROTECTED]> wrote: > Yes, of course I can send you the DB: the file is under 1MB, about 100K if > compressed via .zip > Can I send it to you via e-mail? > Thanks very much for the support > Please send the database directly to my email address shown below. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Yes, of course I can send you the DB: the file is under 1MB, about 100K if compressed via .zip Can I send it to you via e-mail? Thanks very much for the support On Tue, Mar 18, 2008 at 1:10 PM, <[EMAIL PROTECTED]> wrote: > MarcoN <[EMAIL PROTECTED]> wrote: > > I actually don't know how to export it, because "SQLite Database > browser" > > (that is able to open the database and execute the query) has no way to > > export it; SQLiteSpy will not open the database because it is compiled > with > > a new SQLIte library version > > Maybe I can try to find an older version of SQLiteSpy on my archives, > but I > > need more time for this... > > > > Can you send me the entire database file? How big is it? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Yes, of course I can send you the DB: the file is under 1MB, about 100K if compressed via .zip Can I send it to you via e-mail? Thanks very much for the support ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
MarcoN <[EMAIL PROTECTED]> wrote: > I actually don't know how to export it, because "SQLite Database browser" > (that is able to open the database and execute the query) has no way to > export it; SQLiteSpy will not open the database because it is compiled with > a new SQLIte library version > Maybe I can try to find an older version of SQLiteSpy on my archives, but I > need more time for this... > Can you send me the entire database file? How big is it? -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
I actually don't know how to export it, because "SQLite Database browser" (that is able to open the database and execute the query) has no way to export it; SQLiteSpy will not open the database because it is compiled with a new SQLIte library version Maybe I can try to find an older version of SQLiteSpy on my archives, but I need more time for this... Thanks for the reply ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
MarcoN <[EMAIL PROTECTED]> wrote: > Hello, everybody. > > I have the following problem: I have an old project that uses a database > created with an older SQLite library version. > Now, since I updated SQLite to 3.5.5, I can't use the database anymore, > because any query on the database tables returns: > > SQLite error 11 - Malformed database schema - near ")": syntax error > > The strange point is that I downloaded "SQLite Database Browser" from > sourceforge, and I can actually open the database with this tool. In the > help, it is saying that "SQLite Database Browser" is using version 3.3.5 of > the database engine. With this tool, I can browse the data in any table, and > a "pragma integrity_check" returns "ok". > > Any idea / help on this would be greatly appreciated. Please send the following output: SELECT sql FROM sqlite_master; Please do so quickly. We are scheduled to release 3.5.7 in about 30 minutes. If this is a bug in 3.5.x, we'd like to identify it before then. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
