search

codenotary / immudb

immudb - immutable database based on zero trust, SQL/Key-Value/Document model, tamperproof, data change history
https://immudb.io
Other
8.55k stars 343 forks source link

ALTER TABLE ADD COLUMN does not work #1283

Closed tutiplain closed 2 years ago

tutiplain commented 2 years ago

What happened

Using Immudb version 1.3.1 (darwin, amd64 or linux, amd64) using an ALTER TABLE ADD COLUMN query fails with error message "illegal statement, DQL statement expected".

What you expected to happen

I expected a new column to be added to my table

How to reproduce it (as minimally and precisely as possible)

I created a new table using the the web interface with this DQL query:

create table PEOPLE (id integer , name varchar, last_name varchar,primary key id);

I then attempted to add a new column by using the followinf DQL query: ALTER TABLE people ADD COLUMN phone varchar;

Environment

Here is the command used to start immudb and its output:

# ./immudb --pgsql-server=false --web-server-port=8081

(_)                             | | |    
 _ _ __ ___  _ __ ___  _   _  __| | |__  
| | '_ ` _ \| '_ ` _ \| | | |/ _` | '_ \ 
| | | | | | | | | | | | |_| | (_| | |_) |
|_|_| |_| |_|_| |_| |_|\__,_|\__,_|_.__/ 

immudb 1.3.1
Commit  : 7a95e32e58c3f183919079b786f89b11d289f0c3
Built at: Thu, 30 Jun 2022 09:45:54 AST
================ Config ================
Data dir         : ./data
Address          : 0.0.0.0:3322
Metrics address  : 0.0.0.0:9497/metrics
Config file      : configs/immudb.toml
Max recv msg size: 33554432
Auth enabled     : true
Dev mode         : false
Default database : defaultdb
Maintenance mode : false
Synced mode      : true
----------------------------------------
Superadmin default credentials
   Username      : immudb
   Password      : immudb
========================================

immudb  2022/07/15 05:38:38 INFO: Opening database 'systemdb' {replica = false}...
immudb  2022/07/15 05:38:38 INFO: Reading snapshots at 'data/systemdb/index/commit'...
immudb  2022/07/15 05:38:38 INFO: Skipping snapshots at 'data/systemdb/index/commit', reading commit data returned: empty clog
immudb  2022/07/15 05:38:38 INFO: Discarding snapshots at 'data/systemdb/index/commit'...
immudb  2022/07/15 05:38:38 INFO: Snapshots at 'data/systemdb/index/commit' has been discarded
immudb  2022/07/15 05:38:38 INFO: Index 'data/systemdb/index' {ts=0, discarded_snapshots=0} successfully loaded
immudb  2022/07/15 05:38:38 INFO: Indexing in progress at 'data/systemdb'
immudb  2022/07/15 05:38:38 INFO: Binary Linking up to date at 'data/systemdb'
immudb  2022/07/15 05:38:38 INFO: Database 'systemdb' {replica = false} successfully opened
immudb  2022/07/15 05:38:38 INFO: Opening database 'defaultdb' {replica = false}...
immudb  2022/07/15 05:38:38 INFO: Loading SQL Engine for database 'systemdb' {replica = false}...
immudb  2022/07/15 05:38:38 INFO: Flushing index 'data/systemdb/index' {ts=2, cleanup_percentage=0.00/0.00, since_cleanup=2} requested via SnapshotSince...
immudb  2022/07/15 05:38:38 INFO: Index 'data/systemdb/index' {ts=2, cleanup_percentage=0.00/0.00} successfully flushed
immudb  2022/07/15 05:38:38 INFO: Flushing index 'data/systemdb/index' {ts=2} finished with: 1 inner nodes, 0 leaf nodes, 2 entries
immudb  2022/07/15 05:38:38 INFO: SQL Engine ready for database 'systemdb' {replica = false}
immudb  2022/07/15 05:38:38 INFO: Reading snapshots at 'data/defaultdb/index/commit'...
immudb  2022/07/15 05:38:38 INFO: Skipping snapshots at 'data/defaultdb/index/commit', reading commit data returned: empty clog
immudb  2022/07/15 05:38:38 INFO: Discarding snapshots at 'data/defaultdb/index/commit'...
immudb  2022/07/15 05:38:38 INFO: Snapshots at 'data/defaultdb/index/commit' has been discarded
immudb  2022/07/15 05:38:38 INFO: Index 'data/defaultdb/index' {ts=0, discarded_snapshots=0} successfully loaded
immudb  2022/07/15 05:38:38 INFO: Indexing in progress at 'data/defaultdb'
immudb  2022/07/15 05:38:38 INFO: Binary Linking up to date at 'data/defaultdb'
immudb  2022/07/15 05:38:38 INFO: Database 'defaultdb' {replica = false} successfully opened
immudb  2022/07/15 05:38:38 INFO: Loading SQL Engine for database 'defaultdb' {replica = false}...
immudb  2022/07/15 05:38:38 INFO: Flushing index 'data/defaultdb/index' {ts=1, cleanup_percentage=0.00/0.00, since_cleanup=1} requested via SnapshotSince...
immudb  2022/07/15 05:38:38 INFO: Started with an empty default database
immudb  2022/07/15 05:38:38 INFO: Index 'data/defaultdb/index' {ts=1, cleanup_percentage=0.00/0.00} successfully flushed
immudb  2022/07/15 05:38:38 INFO: Flushing index 'data/defaultdb/index' {ts=1} finished with: 1 inner nodes, 0 leaf nodes, 1 entries
immudb  2022/07/15 05:38:38 INFO: SQL Engine ready for database 'defaultdb' {replica = false}
immudb  2022/07/15 05:38:38 INFO: sessions guard started
immudb  2022/07/15 05:38:39 INFO: Webconsole enabled: 0.0.0.0:8081
immudb  2022/07/15 05:38:39 INFO: Web API server enabled on 0.0.0.0:8081/api (http)
You can now use immuadmin and immuclient CLIs to login with the immudb superadmin user and start using immudb.

Additional info (any other context about the problem)

byo commented 2 years ago

Hi @tutiplain , thanks for the report.

What method are you using for the ALTER TABLE statement? This statement will only succeed if run through the Exec method. The Query one will fail since it's only meant to query the data. The mentioned error is returned only if the Query method is used to modify the data.

tutiplain commented 2 years ago

I am actually using ImmuDB's web console to run the command. I am not using the immuclient at this time.

I noticed this behavior when attempting to use the Migrator function for AddColumn() in the immugorm project, so I decided to attempt doing the Alter Table manually in the web console to see if it was just gorm generating invalid DQL syntax.

ARTIDIXIT commented 2 years ago

ALTER TABLE people ADD phone varchar;

use this

byo commented 2 years ago

The issue was fixed in webconsole https://github.com/codenotary/immudb-webconsole/pull/13 (updated in immudb in https://github.com/codenotary/immudb/pull/1294).

@tutiplain please check if it works as expected, dev binaries will be available shortly in https://github.com/codenotary/immudb/actions/runs/2731470130 - those are deleted after 1 week.