Quantcast

Another .DUMP issue with v 3.18.0

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Another .DUMP issue with v 3.18.0

Tony Papadimitriou
The following dump cannot be used to rebuild the database because the column name is not properly quoted giving an error.

To reproduce:

sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
sql .dump xxx.db | sql

Error: near line 4: near "zone": syntax error
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Richard Hipp-3
On 4/7/17, Tony Papadimitriou <[hidden email]> wrote:
> The following dump cannot be used to rebuild the database because the column
> name is not properly quoted giving an error.
>
> To reproduce:
>
> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
> sql .dump xxx.db | sql
>

I'm unable to repro.

Please send the database file via private email.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Josh Hunsaker
On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
> On 4/7/17, Tony Papadimitriou wrote:
>>
>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
>> sql .dump xxx.db | sql
>>
>
> I'm unable to repro.
>

Is this possibly because the shell that Tony is using is evaluating `time zone`?

I can repro, but in my case, the obvious cause is that the `time zone`
portion gets evaluated to an empty string.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Tony Papadimitriou
-----Original Message-----
From: Josh Hunsaker

>On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
>> On 4/7/17, Tony Papadimitriou wrote:
>>>
>>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
>>> sql .dump xxx.db | sql
>>>
>>
>> I'm unable to repro.
>>

>Is this possibly because the shell that Tony is using is evaluating `time
>zone`?

>I can repro, but in my case, the obvious cause is that the `time zone`
>portion gets evaluated to an empty string.

Well, try this instead:
sql xxx.db "CREATE TABLE xxx(`a b c`)" "insert into xxx values(1)"

The problem is the column has spaces and it's not quoted.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Keith Medcalf

Try using different quotes, not ones that have meaning to the shell.


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Tony Papadimitriou
> Sent: Friday, 7 April, 2017 17:04
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0
>
> -----Original Message-----
> From: Josh Hunsaker
>
> >On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
> >> On 4/7/17, Tony Papadimitriou wrote:
> >>>
> >>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx
> values('1')"
> >>> sql .dump xxx.db | sql
> >>>
> >>
> >> I'm unable to repro.
> >>
>
> >Is this possibly because the shell that Tony is using is evaluating `time
> >zone`?
>
> >I can repro, but in my case, the obvious cause is that the `time zone`
> >portion gets evaluated to an empty string.
>
> Well, try this instead:
> sql xxx.db "CREATE TABLE xxx(`a b c`)" "insert into xxx values(1)"
>
> The problem is the column has spaces and it's not quoted.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Tony Papadimitriou
Here's the dump I get:

c:\temp>sql xxx.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`a b c`);
INSERT INTO xxx(a b c) VALUES(1);
COMMIT;

Do you see the problem?
INSERT specifies column name without quotes.

However, I also tried with latest trunk, and I get a different dump:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`a b c`);
INSERT INTO xxx VALUES(1);
COMMIT;

The column name is missing.  Hmm!
This is on Win7 machine if it matters.

-----Original Message-----
From: Keith Medcalf
Sent: Saturday, April 08, 2017 2:08 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0


Try using different quotes, not ones that have meaning to the shell.


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Tony Papadimitriou
> Sent: Friday, 7 April, 2017 17:04
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0
>
> -----Original Message-----
> From: Josh Hunsaker
>
> >On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
> >> On 4/7/17, Tony Papadimitriou wrote:
> >>>
> >>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx
> values('1')"
> >>> sql .dump xxx.db | sql
> >>>
> >>
> >> I'm unable to repro.
> >>
>
> >Is this possibly because the shell that Tony is using is evaluating `time
> >zone`?
>
> >I can repro, but in my case, the obvious cause is that the `time zone`
> >portion gets evaluated to an empty string.
>
> Well, try this instead:
> sql xxx.db "CREATE TABLE xxx(`a b c`)" "insert into xxx values(1)"
>
> The problem is the column has spaces and it's not quoted.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Simon Slavin-3
In reply to this post by Richard Hipp-3

On 7 Apr 2017, at 11:02pm, Richard Hipp <[hidden email]> wrote:

> On 4/7/17, Tony Papadimitriou <[hidden email]> wrote:
>> The following dump cannot be used to rebuild the database because the column
>> name is not properly quoted giving an error.
>>
>> To reproduce:
>>
>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
>> sql .dump xxx.db | sql
>
> I'm unable to repro.

I notice that the command on those lines is "sql" rather than the "sqlite3" I would expect.  Tony, do you get the same error if you type those things into the SQLite3 shell, rather than feeding them in using your command shell ?  It works fine for me.  If not, what shell are you using ?

179:sqlite-tools-osx-x86-3180000 simon$ ./sqlite3
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE xxx(`time zone`);
sqlite> insert into xxx values('1');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`time zone`);
INSERT INTO xxx VALUES('1');
COMMIT;
sqlite>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Tony Papadimitriou


-----Original Message-----
From: Simon Slavin

>I notice that the command on those lines is "sql" rather than the "sqlite3"
>I would expect.  Tony, do you get the same error if you type those things
>into the SQLite3 shell, rather than feeding them in using >your command
>shell ?  It works fine for me.  If not, what shell are you using ?

I'm also using SQLite version 3.18.0 2017-03-28 18:48:43

SQL is the latest release version of SQLITE3 renamed to SQL so that
1. it does not collide with the latest trunk which I keep as SQLITE3 for
testing, and
2. because it's faster/easier to type all the time.

I noticed you have the same version but there is no column in the INSERT
statement.  This got me thinking what is different.  And, I remembered that
I have a headers on default on mine.

So, please try this instead:

sql xxx.db ".headers on" ".dump"

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Simon Slavin-3

> On 8 Apr 2017, at 12:44am, Tony Papadimitriou <[hidden email]> wrote:
>
> So, please try this instead:
>
> sql xxx.db ".headers on" ".dump"

sqlite> .headers on
sqlite> CREATE TABLE xxx(`time zone`);
sqlite> insert into xxx values('1');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`time zone`);
INSERT INTO xxx(time zone) VALUES('1');
COMMIT;
sqlite>

Yes, you have isolated the problem.  Without ".headers on" you get

        INSERT INTO xxx VALUES('1');

But with ".headers on" you get

        INSERT INTO xxx(time zone) VALUES('1');

However, it is not quoting the column name, so the command is invalid.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Another .DUMP issue with v 3.18.0

Richard Hipp-3
Should be fixed on trunk.

On 4/7/17, Simon Slavin <[hidden email]> wrote:

>
>> On 8 Apr 2017, at 12:44am, Tony Papadimitriou <[hidden email]> wrote:
>>
>> So, please try this instead:
>>
>> sql xxx.db ".headers on" ".dump"
>
> sqlite> .headers on
> sqlite> CREATE TABLE xxx(`time zone`);
> sqlite> insert into xxx values('1');
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE xxx(`time zone`);
> INSERT INTO xxx(time zone) VALUES('1');
> COMMIT;
> sqlite>
>
> Yes, you have isolated the problem.  Without ".headers on" you get
>
> INSERT INTO xxx VALUES('1');
>
> But with ".headers on" you get
>
> INSERT INTO xxx(time zone) VALUES('1');
>
> However, it is not quoting the column name, so the command is invalid.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...