Creating volume snapshot failed

Hi,
v4.22
zfs storage

lxc storage volume snapshot data mwtrackdb-mysql mwtrackdb-mysql-20012022                                                                                                                           

Error: Error inserting volume "mwtrackdb-mysql/mwtrackdb-mysql-20012022" for project "default" in pool "data" of type "custom" into database "sql: Scan error on column index 0, name \"seq\": converting NULL to int64 is unsupported"

@stgraber @markylaing could this be related to https://github.com/lxc/lxd/pull/9807 ?

I don’t think it’s caused by the same issue (shared sequence) but could have been caused by the same change still. I’ll have to see if I can reproduce this here.

@dmp_78 can you:

  • Confirm it still happens
  • Show the output of snap list lxd

That’s assuming it’s using the snap. If it’s not using the snap, then you should get your distro maintainer to cherry-pick our post-release DB fix.

I’ve tested a few systems I have here and they seem to be behaving, so I’m hoping that it’s indeed the same issue and it’s just a system which hasn’t received it yet.

Still don’t quite get that NULL to int64 thing though.

Ah and the output of lxd sql global .dump | grep sequence would be useful too.

I wonder if the sequence may be missing somehow.

yes, it still happens

Ubuntu 18.04.6 LTS

snap list lxd                                                                                                                                                                                       
Name  Version  Rev    Tracking       Publisher   Notes                                                                                                                                                             
lxd   4.22     22258  latest/stable  canonical*  -
 lxd sql global .dump | grep sequence                                                                                                                                                                
DELETE FROM sqlite_sequence;                                                                                                                                                                                       
INSERT INTO sqlite_sequence VALUES('schema',8);                                                                                                                                                                    
INSERT INTO sqlite_sequence VALUES('config',1);                                                                                                                                                                    
INSERT INTO sqlite_sequence VALUES('cluster_groups',1);                                                                                                                                                            
INSERT INTO sqlite_sequence VALUES('projects',1);                                                                                                                                                                  
INSERT INTO sqlite_sequence VALUES('images',1);                                                                                                                                                                    
INSERT INTO sqlite_sequence VALUES('images_aliases',1);                                                                                                                                                            
INSERT INTO sqlite_sequence VALUES('nodes',1);                                                                                                                                                                     
INSERT INTO sqlite_sequence VALUES('images_nodes',1);                                                                                                                                                              
INSERT INTO sqlite_sequence VALUES('profiles',1);                                                                                                                                                                  
INSERT INTO sqlite_sequence VALUES('images_properties',8);                                                                                                                                                         
INSERT INTO sqlite_sequence VALUES('images_source',1);                                                                                                                                                             
INSERT INTO sqlite_sequence VALUES('instances',4);                                                                                                                                                                 
INSERT INTO sqlite_sequence VALUES('instances_backups',0);                                                                                                                                                         
INSERT INTO sqlite_sequence VALUES('instances_config',134);                                                                                                                                                        
INSERT INTO sqlite_sequence VALUES('instances_devices',2);                                                                                                                                                         
INSERT INTO sqlite_sequence VALUES('instances_devices_config',8);                                                                                                                                                  
INSERT INTO sqlite_sequence VALUES('instances_profiles',6);                                                                                                                                                        
INSERT INTO sqlite_sequence VALUES('instances_snapshots',0);                                                                                                                                                       
INSERT INTO sqlite_sequence VALUES('instances_snapshots_config',0);                                                                                                                                                
INSERT INTO sqlite_sequence VALUES('instances_snapshots_devices',0);                                                                                                                                               
INSERT INTO sqlite_sequence VALUES('instances_snapshots_devices_config',0);                                                                                                                                        
INSERT INTO sqlite_sequence VALUES('networks',0);                                                                                                                                                                  
INSERT INTO sqlite_sequence VALUES('networks_acls',0);                                                                                                                                                             
INSERT INTO sqlite_sequence VALUES('networks_config',0);                                                                                                                                                           
INSERT INTO sqlite_sequence VALUES('networks_forwards',0);                                                                                                                                                         
INSERT INTO sqlite_sequence VALUES('networks_forwards_config',0);                                                                                                                                                  
INSERT INTO sqlite_sequence VALUES('networks_nodes',0);                                                                                                                                                            
INSERT INTO sqlite_sequence VALUES('networks_peers',0);                                                                                                                                                            
INSERT INTO sqlite_sequence VALUES('networks_peers_config',0);                                                                                                                                                     
INSERT INTO sqlite_sequence VALUES('networks_zones',0);                                                                                                                                                            
INSERT INTO sqlite_sequence VALUES('networks_zones_config',0);                                                                                                                                                     
INSERT INTO sqlite_sequence VALUES('nodes_config',0);                                                                                                                                                              
INSERT INTO sqlite_sequence VALUES('operations',232);                                                                                                                                                              
INSERT INTO sqlite_sequence VALUES('profiles_config',0);                                                                                                                                                           
INSERT INTO sqlite_sequence VALUES('profiles_devices',2);                                                                                                                                                          
INSERT INTO sqlite_sequence VALUES('profiles_devices_config',7);                                                                                                                                                   
INSERT INTO sqlite_sequence VALUES('projects_config',4);                                                                                                                                                           
INSERT INTO sqlite_sequence VALUES('storage_pools',2);                                                                                                                                                             
INSERT INTO sqlite_sequence VALUES('storage_pools_config',10);                                                                                                                                                     
INSERT INTO sqlite_sequence VALUES('storage_pools_nodes',2);                                                                                                                                                       
INSERT INTO sqlite_sequence VALUES('storage_volumes',NULL);                                                                                                                                                        
INSERT INTO sqlite_sequence VALUES('storage_volumes_backups',0);                                                                                                                                                   
INSERT INTO sqlite_sequence VALUES('storage_volumes_config',14);                                                                                                                                                   
INSERT INTO sqlite_sequence VALUES('storage_volumes_snapshots_config',0);                                                                                                                                          
INSERT INTO sqlite_sequence VALUES('warnings',2); 

ah, storage_volumes seq is missing

did that

lxd sql global "SELECT MAX(id) FROM storage_volumes_all;"                                                                                                                                           
+---------+                                                                                                                                                                                                        
| MAX(id) |
+---------+
| 8       |
+---------+
lxd sql global "UPDATE sqlite_sequence SET seq = 8 WHERE name = 'storage_volumes'"

snapshot is ok now

thanks, @stgraber

p.s. such situation on all lxd instances after upgrade to 4.22

INSERT INTO sqlite_sequence VALUES('storage_volumes',NULL);                                                                                                                                                        

Yeah, that’s bad… I’ll track down how this may happen, I suspect it’s a bug in my DB patch which triggers if you somehow have no volumes or something.

I reproduced the issue and will send yet another DB patch to fix it…