Asked
11 years, 11 months ago
Viewed
5k times
I am using SQLite for my application. when I used truncate command in SQLite it gives me error like » syntax Error near truncate«.
Can anyone help me to solve this problem or any other alternative of truncate except DELETE command.
asked Feb 15, 2011 at 11:28
Why not DELETE?
DELETE with an empty WHERE clause should be pretty fast in SQLite because it does sth called «truncate optimization», see
http://www.sqlite.org/lang_delete.html
BenMorel
33.5k49 gold badges174 silver badges310 bronze badges
answered Feb 15, 2011 at 11:37
Frank SchmittFrank Schmitt
29.7k11 gold badges71 silver badges107 bronze badges
2
SQLite DELETE FROM is not equivalent to T-SQL TRUNCATE TABLE.
SQLite DELETE FROM will not reset the index in a field marked autoincrement.
answered Mar 30, 2011 at 12:38
The SQLLite Delete Truncation Optimization DELETE FROM {table}; (with no WHERE clause) has similar behaviour to the SQL Server TRUNCATE TABLE {table};. See the SQLLite documentation here
The Truncate Optimization
When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This «truncate» optimization makes the delete run much faster. Prior to SQLite version 3.6.5 (2008-11-12), the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5 (2008-11-12).
The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.
answered Feb 15, 2011 at 11:38
StuartLCStuartLC
103k17 gold badges205 silver badges277 bronze badges
So what taazz suggested:
procedure TForm1.TestBtnClick(Sender: TObject);(code untested, don’t know if it compiles)
begin
SQLScript1.Script.Text:='delete from albums; delete from songs;';
SQLScript1.Execute;
end;
or you could use a regular sqlquery and .execsql it — once for each delete command
Yes, I had already done that after reading that tutorial website, and it did compile OK, but it didn’t do anything until I added another line:
SQLScript1.Script.Text:=’delete from albums; delete from songs;’;
SQLScript1.Execute;
SQLTransaction1.Commit;
After I ran that, the tables appeared to be empty when I examined the database with SQLite Studio, but the file was still exactly the same size (approx 3.5Mb) as it was before all the records were deleted.
Then I remembered reading on that tutorial website about using the vacuum command after using the delete from table command to clear unused space, so I changed it to this:
SQLScript1.Script.Text:=’delete from albums; delete from songs; vacuum;’; <— added the vacuum command to the script
SQLScript1.Execute;
SQLTransaction1.Commit;
That compiled OK too, but when I ran it, I got an error message: Cannot VACUUM from within a transaction.
When I googled that error message, I got a hit from an earlier post on this forum that listed a solution to that error, so I added it:
SQLScript1.Script.Text:=’delete from albums; delete from songs;’; <— removed the vacuum command from the script
SQLScript1.Execute;
SQLTransaction1.Commit;
albumsConnection.ExecuteDirect(‘End Transaction’);
albumsConnection.ExecuteDirect(‘vacuum’); <— and added it back here
albumsConnection.ExecuteDirect(‘Begin Transaction’);
That worked perfectly. The file is now only 3072 bytes, the same size I get if I use SQLite Studio to erase all the data and vacuum the database.
So now it’s on to figuring out how to import the updated data. From what I’ve looked at, it looks like the TSqlite3Dataset component might be easier to use. It looks like I can just use 2 datasets (and 2 datasources) to connect directly to the tables, then parse the updated data and use Append and Post to add new records to the tables.
Is it possible to mix TSqlite3Dataset and the SQLdb components that I used above?
Would I have to completely disable the SQLdb components before connecting the TSqlite3Datasets, or could I connect them to the tables at design time and do the emptying procedure listed above using the SQLdb components while the tables were connected to the TSqlite3Datasets?
Or even better, could I do the above table emptying procedure also with TSqlite3Datasets? It does have a SQL property, and a ExecSQL procedure, and it looks like it might even have the ExecuteDirect procedure. That would eliminate having to use the SQLdb stuff at all.
Thanks to everybody for all the help so far, and I would very much appreciate any opinions that anybody might have on the above questions.
Open
Issue created Feb 11, 2018 by
Migration from MySQL to Postgres fails with `syntax error at or near «;»`
Current status
We run a gitlab 10.4.2 from sources with mysql.
Our MySQL database runs its own container.
Our goal
We want to migrate from sources with mysql to docker with postgresql.
Firstly, we want to migarte from MySQL to Postgres.
Our mysql-to-postgres migration process
- Create a new gitlab-container to get an uü2date PostgreSQL database
- Run the fresh PostgreSQL as its own container
- Check Postgres access from host to container
- Check MySQL access from host to container
- Run migration
Step 1: gitlab-container
We created a new gitlab 10.4.2 container as follows:
docker run
--rm
--hostname gitlab.example.com
--publish 10443:443
--publish 10080:80
--publish 10022:22
--name gitlab
--volume /docker/gitlab/config:/etc/gitlab
--volume /docker/gitlab/logs:/var/log/gitlab
--volume /docker/gitlab/data:/var/opt/gitlab
gitlab/gitlab-ce:10.4.2-ce.0After the container was started, we followed the migration steps from the docu within the container:
gitlab-ctl stop
gitlab-ctl reconfigure
gitlab-ctl start unicorn
gitlab-ctl start postgresql
gitlab-rake db:create db:migrate
gitlab-ctl stop unicornLike this, we created a fresh PostgreSQL database with up-to-date schema, which is located under /docker/gitlab/data/postgresql/data now.
Afterwards, we stopped the gitlab container.
Step 2: Run the PostgreSQL database in its own container
We want to start the freshly generated PostgreSQL database as its own container.
Thus, we configured these two files manually as follows:
-
/docker/gitlab/data/postgresql/data/pg_hba.conf:
host all all 0.0.0.0/0 trust-
/docker/gitlab/data/postgresql/data/postgresql.conf:
listen_addresses = '*'
ssl = off
unix_socket_directories = ''With this modified configuration, we run the postgresql in its own container:
docker run
--name postgres
--rm
-e POSTGRES_USER=admin
-e POSTGRES_PASSWORD=admin
-v /docker/gitlab/data/postgresql/data:/var/lib/postgresql/data
-p 5432:5432
postgres:9.6.5Step 3: Check postgres access
Due to the modified configuration, we checked the login with pgAdmin from the host to the container:
- Host:
localhost - Port:
5432 - DB:
postgres - USer
gitlab-psql - Passwort: NONE
This works as well. Great.
Step 4: Check mysql access
We checked, that we can access the mysql container from the host:
mysql -h 127.0.0.1 -P 3307 -u root -p<PASSWORD> gitlab_productionChecking it with:
This works as well. Great.
Step 5: Migrate from postgresql to pgloader
Now, since we can access both containers from the host, we started the migration process as described in the docs,
by creating an adjusted commands.load:
LOAD DATABASE
FROM mysql://root:<PASSWORD>@127.0.0.1:3307/gitlab_production
INTO postgresql://gitlab-psql@localhost:5432/gitlabhq_production
WITH include no drop, truncate, disable triggers, create no tables,
create no indexes, preserve index names, no foreign keys,
data only
ALTER SCHEMA 'gitlab_production' RENAME TO 'public'
;Finally, we run the migration with pgloader commands.load . This returns the following error:
2018-02-09T21:38:45.709000Z ERROR Database error 42601: syntax error at or near ";"
QUERY: TRUNCATE ;
2018-02-09T21:38:45.709000Z FATAL Failed to create the schema, see above.
2018-02-09T21:38:45.910000Z LOG report summary reset
table name read imported errors total time
----------------- --------- --------- --------- --------------
fetch meta data 0 0 0 0.234s
Truncate 0 0 0 0.000s
----------------- --------- --------- --------- --------------[EDIT] 2017-02-22: Tiny typos fixed
Edited Feb 22, 2018 by Dennis Boldt
gabjos
unread,
Feb 15, 2010, 10:34:22 PM2/15/10
to H2 Database
I type: truncate table test
The table does exist and I own it. But I keep getting the following
error:
I don’t know what to do. Please help.
statement TRUNCATE TABLE FROM[*] test ; expected identifier; SQL
statement:
truncate table FROM test [42001-63]
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement TRUNCATE
TABLE FROM[*] test ; expected identifier; SQL statement:
truncate table FROM test [42001-63]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.message.Message.getSyntaxError(Message.java:103)
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:
2263)
at org.h2.command.Parser.readTableOrView(Parser.java:3906)
at org.h2.command.Parser.parseTruncate(Parser.java:888)
at org.h2.command.Parser.parsePrepared(Parser.java:381)
at org.h2.command.Parser.parse(Parser.java:264)
at org.h2.command.Parser.parse(Parser.java:236)
at org.h2.command.Parser.prepareCommand(Parser.java:208)
at org.h2.engine.Session.prepareLocal(Session.java:173)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:
176)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:97)
at java.lang.Thread.run(Unknown Source)
at org.h2.engine.SessionRemote.done(SessionRemote.java:289)
at org.h2.command.CommandRemote.prepare(CommandRemote.java:54)
at org.h2.command.CommandRemote.<init>(CommandRemote.java:37)
at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:
237)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
961)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:143)
at org.h2.server.web.WebThread.getResult(WebThread.java:1392)
at org.h2.server.web.WebThread.query(WebThread.java:1054)
at org.h2.server.web.WebThread.process(WebThread.java:375)
at org.h2.server.web.WebThread.processRequest(WebThread.java:131)
at org.h2.server.web.WebThread.run(WebThread.java:158)
Thomas Mueller
unread,
Feb 15, 2010, 10:37:30 PM2/15/10
to h2-da…@googlegroups.com
Kerry Sainsbury
unread,
Feb 15, 2010, 10:44:50 PM2/15/10
to h2-da…@googlegroups.com
On Tue, Feb 16, 2010 at 8:34 AM, gabjos <sikyala…@bah.com> wrote:
I type: truncate table test
Hmmm. It works for me without any error.
create table test(i int);
truncate table test;
drop table test;
Are you SURE you’re typing «truncate table test»?
Cheers
Kerry
gabjos
unread,
Feb 15, 2010, 10:47:07 PM2/15/10
to H2 Database
That is exactly what I type and I keep getting the same errors. Now
when I type create table test1 as select * from test I get a internet
explorer error: A script on this page is causing Internet Explorer to
run slowly. If it continues to run, your computer may become
unresponsive. Do you want to abort the script.
I don’t know if this has anything to do with why the database is
complaining of syntax errors. But I clicked yes to abort it and it has
come back again. I am not running any scripts on the database. So I
don’t know where this error is coming from.
On Feb 15, 2:37 pm, Thomas Mueller <thomas.tom.muel…@gmail.com>
wrote:
Thomas Mueller
unread,
Feb 15, 2010, 10:49:18 PM2/15/10
to h2-da…@googlegroups.com
Hi,
> That is exactly what I type
No, you wrote:
TRUNCATE TABLE FROM test
not
TRUNCATE TABLE test
By the way, consider upgrading to a more recent version of H2. You are
using version 1.0.63.
Regards,
Thomas
gabjos
unread,
Feb 15, 2010, 11:04:19 PM2/15/10
to H2 Database
sorry i put the wrong error message here is the error:
truncate table test
Cannot truncate PUBLIC.test [90106-63]
org.h2.jdbc.JdbcSQLException: Cannot truncate PUBLIC.test [90106-63]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.message.Message.getSQLException(Message.java:71)
at org.h2.command.ddl.TruncateTable.update(TruncateTable.java:30)
at org.h2.command.CommandContainer.update(CommandContainer.java:
64)
at org.h2.command.Command.executeUpdate(Command.java:122)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:
241)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:97)
at java.lang.Thread.run(Unknown Source)
at org.h2.engine.SessionRemote.done(SessionRemote.java:289)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:
164)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
at org.h2.server.web.WebThread.getResult(WebThread.java:1392)
at org.h2.server.web.WebThread.query(WebThread.java:1054)
at org.h2.server.web.WebThread.process(WebThread.java:375)
at org.h2.server.web.WebThread.processRequest(WebThread.java:131)
at org.h2.server.web.WebThread.run(WebThread.java:158)
On Feb 15, 2:49 pm, Thomas Mueller <thomas.tom.muel…@gmail.com>
wrote:
Kerry Sainsbury
unread,
Feb 15, 2010, 11:15:46 PM2/15/10
to h2-da…@googlegroups.com
How do you create test?
Is it definitely a table? (not a view?)
Can you provide a complete example?
Cheers
Kerry
Thomas Mueller
unread,
Feb 16, 2010, 12:08:46 AM2/16/10
to h2-da…@googlegroups.com
gabjos
unread,
Feb 16, 2010, 1:44:32 AM2/16/10
to H2 Database
When I was trying to find out what the primary key for the table in
question I was given the following query:
select column_name from information_schema.indexes
where table_schema = ‘PUBLIC’
and table_name=’TEST’
and primary_key
I executed it on my table and since then I have been able to perform
any dml on it. Is there a way to undo this?
On Feb 15, 4:08 pm, Thomas Mueller <thomas.tom.muel…@gmail.com>
wrote:
gabjos
unread,
Feb 16, 2010, 1:45:21 AM2/16/10
to H2 Database
Kerry Sainsbury
unread,
Feb 16, 2010, 2:21:18 AM2/16/10
to h2-da…@googlegroups.com
Dude…. that’s a SELECT statement. It hasn’t changed anything. Drop your «test» table and start again.
—
You received this message because you are subscribed to the Google Groups «H2 Database» group.
To post to this group, send email to h2-da…@googlegroups.com.