[Kea-users] Kea-admin lease-upgrade was errored

[Shane Kerr]

Quang Trieu,

As far as I can tell, the tables in 4.0 of the schema are all there,
and have all of the proper columns. We didn't check indexes or foreign
keys so we can't be sure about that.

If you are feeling brave, you can try setting the version to 4.0
manually and seeing what happens.

mysql> UPDATE schema_version SET version = '4', minor = '0';

It is possible that some of the stored procedures were not created, so
if you are worried you can verify that those are also correct.

Finally, I also see that there are a few tables that are not part of
Kea in the database. This should be okay, although if you are not
actually using the DHCP data in your other application(s) then maybe it
makes more sense to use a different database for them.

Cheers,

--
Shane

At 2016-01-06 08:23:36 +0700
"Quang. Trieu Minh" <quangtm at vng.com.vn> wrote:

> Hi Shane Kerr
> 
> We had a update schema in last week.
> 
> All tables and columns as bellow.
> 
> + tables
> assets
> dhcp4_options
> dhcp6_options
> hosts
> ipv6_reservations
> lease4
> lease6
> lease6_types
> lease_hwaddr_source
> lease_state
> schema_version
> subnet
> user_auth_token
> + lease 4
> address int(10) unsigned NO PRI
> hwaddr varbinary(20) YES MUL
> client_id varbinary(128) YES MUL
> valid_lifetime int(10) unsigned YES
> expire timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
> subnet_id int(10) unsigned YES
> fqdn_fwd tinyint(1) YES
> fqdn_rev tinyint(1) YES
> hostname varchar(255) YES
> state int(10) unsigned YES MUL 0
> + lease6
> address varchar(39) NO PRI
> duid varbinary(128) YES
> valid_lifetime int(10) unsigned YES
> expire timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
> subnet_id int(10) unsigned YES
> pref_lifetime int(10) unsigned YES
> lease_type tinyint(4) YES
> iaid int(10) unsigned YES MUL
> prefix_len tinyint(3) unsigned YES
> fqdn_fwd tinyint(1) YES
> fqdn_rev tinyint(1) YES
> hostname varchar(255) YES
> hwaddr varbinary(20) YES
> hwtype smallint(5) unsigned YES
> hwaddr_source int(10) unsigned YES
> state int(10) unsigned YES MUL 0
> + lease6_types
> lease_type tinyint(4) NO PRI
> name varchar(5) YES
> + lease_hwaddr_source
> hwaddr_source int(11) NO PRI
> name varchar(40) YES
> + hosts
> host_id int(10) unsigned NO PRI auto_increment
> dhcp_identifier varbinary(128) NO MUL
> dhcp_identifier_type tinyint(4) NO
> dhcp4_subnet_id int(10) unsigned YES
> dhcp6_subnet_id int(10) unsigned YES
> ipv4_address int(10) unsigned YES
> hostname varchar(255) YES
> dhcp4_client_classes varchar(255) YES
> dhcp6_client_classes varchar(255) YES
> + ipv6_reservations
> 
> reservation_id int(11) NO PRI auto_increment
> address varchar(39) NO
> prefix_len tinyint(3) unsigned NO 128
> type tinyint(4) unsigned NO 0
> dhcp6_iaid int(10) unsigned YES
> host_id int(10) unsigned NO MUL
> + dhcp4_options
> option_id int(10) unsigned NO PRI auto_increment
> code tinyint(3) unsigned NO
> value blob YES
> formatted_value text YES
> space varchar(128) YES
> persistent tinyint(1) NO 0
> dhcp_client_class varchar(128) YES
> dhcp4_subnet_id int(11) YES
> host_id int(10) unsigned YES MUL
> + dhcp6_options
> option_id int(10) unsigned NO PRI auto_increment
> code int(10) unsigned NO
> value blob YES
> formatted_value text YES
> space varchar(128) YES
> persistent tinyint(1) NO 0
> dhcp_client_class varchar(128) YES
> dhcp6_subnet_id int(11) YES
> host_id int(10) unsigned YES MUL
> + lease_state;
> state int(10) unsigned NO PRI
> name varchar(64) NO
> 
> 
> 
> 
> 
> 
> Thanks and Best Regards,
> Quang Trieu
> 
> On Wed, Jan 6, 2016 at 3:11 AM, Shane Kerr <shane at time-travellers.org>
> wrote:
> 
> > Quang Trieu,
> >
> > At 2016-01-05 22:28:02 +0700
> > "Quang. Trieu Minh" <quangtm at vng.com.vn> wrote:
> >  
> > > After I upgraded kea 0.9.0 to kea 1.0.0. I updated lease version and saw
> > > errors as bellows :
> > >
> > > root at VNGHCMAPP01:/opt/kea/sbin# ./kea-admin lease-upgrade mysql -u  
> > sqluser  
> > > -p *********** -n dhcp_management
> > > Lease DB version reported before upgrade: 3.0
> > >
> > > Processing /opt/kea/share/kea/scripts/mysql/upgrade_1.0_to_2.0.sh  
> > file...  
> > > This script upgrades 1.0 to 2.0. Reported version is 3.0. Skipping  
> > upgrade.  
> > > Processing /opt/kea/share/kea/scripts/mysql/upgrade_2.0_to_3.0.sh  
> > file...  
> > > This script upgrades 2.0 to 3.0. Reported version is 3.0. Skipping  
> > upgrade.  
> > > Processing /opt/kea/share/kea/scripts/mysql/upgrade_3.0_to_4.0.sh  
> > file...  
> > > ERROR 1060 (42S21) at line 2: Duplicate column name 'state'
> > > Processing /opt/kea/share/kea/scripts/mysql/upgrade_4.0_to_4.1.sh  
> > file...  
> > > This script upgrades 4.0 to 4.1. Reported version is 3.0. Skipping  
> > upgrade.  
> > > Lease DB version reported after upgrade: 3.0  
> >
> > It looks like the script is complaining about this command:
> >
> > # Add state column to the lease4 table.
> > ALTER TABLE lease4
> >     ADD COLUMN state INT UNSIGNED DEFAULT 0;
> >
> > The error indicates that for some reason the lease4 table already has a
> > column named 'state'.
> >
> > I'm not sure how this could happen.
> >
> > Was there an earlier attempt to upgrade the schema that failed?
> >
> > You can see the current schema of your database by using the MySQL
> > command line:
> >  
> > mysql> show tables  
> >
> > That will list all the tables, then you can go through each and check
> > the structure:
> >  
> > mysql> show columns from lease4;
> > mysql> show columns from lease6;
> > mysql> show columns from lease6_type;
> > mysql> show columns from lease_hwaddr_source;
> > mysql> show columns from hosts;
> > mysql> show columns from ipv6_reservations;
> > mysql> show columns from dhcpv4_options;
> > mysql> show columns from dhcpv6_options;
> > mysql> show columns from lease_state;  
> >
> > I don't know of any other way to see where things are broken.
> >
> > Cheers,
> >
> > --
> > Shane
> >