Upgrade fails due to already existing table

Get help from other users here.

Moderators: Developer, Contributor

Post Reply
Herr.mullepulle
Posts: 5
Joined: 15 Jan 2021, 05:37

Upgrade fails due to already existing table

Post by Herr.mullepulle »

Hello!

I already posted my problem last Friday but for some reason, the topic got deleted. I would really appreciate help or if that's not possible, at least an explanation ;)

Original post:

When upgrading from 2.18.0 to 2.24.3, the upgrade fails because the table "mantis_api_token_table" already exists.

Why does Mantis try to create an already existing table? Can I prevent the creation from happening? How can I successfully finish my upgrade?

Thank you in advance!

Best regards

mantis_api_token_table already exists.png
mantis_api_token_table already exists.png (91.55 KiB) Viewed 2636 times
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Upgrade fails due to already existing table

Post by cas »

I have no clue why it happens but here is an option to deal with this.
Rename this table to another value using phpmyadmin ( or similar tool).
Run the upgrade process again and see if it finishes.
If so, you can copy the contents of the renamed table into the newly created table.
All done, everybody happy :mrgreen:
Herr.mullepulle
Posts: 5
Joined: 15 Jan 2021, 05:37

Re: Upgrade fails due to already existing table

Post by Herr.mullepulle »

Thank you for the fast reply!

Unfortunately, I don't have access to our MySQL database, so deleting or renaming the table is not an option.
Don't ask me why, my organisation is a little...special...in that regard :roll:

That's why I would be really happy, if there was a solution that did not involve tampering with database objects. I mean, the error doesn't happen to everyone obviously and I did not alter my database since I upgraded to 2.18.0, so I would be really interested to know why mentioned table already exists although it should only be installed with 2.24.3.
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Upgrade fails due to already existing table

Post by cas »

S you followed the admin guide to do the upgrade?
It looks like a fresh install is happening on an existing database :o
You should copy config/config_inc.php from the old installation to the new one.
Only then Mantis will know it is an existing installation and perform the update steps
Herr.mullepulle
Posts: 5
Joined: 15 Jan 2021, 05:37

Re: Upgrade fails due to already existing table

Post by Herr.mullepulle »

Yes, I followed the admin guide to upgrade.

Before upgrading, I copied the 3 files config_inc.php, custom_constants_inc.php & custom_strings_inc.php to the /config-directory of the new version.
config.png
config.png (15.5 KiB) Viewed 2604 times
Mantis also recognises that it has to upgrade, not install a new database, at least that's how I understand the "upgrade your installation"-button.
Upgrade.png
Upgrade.png (50.12 KiB) Viewed 2604 times
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Upgrade fails due to already existing table

Post by cas »

The only thing i would try, since you cannot access the database but you can access the scripts, is the following:
- open admin/schema.php (make a backup of this first!!!)
- look for "Schema version: 200 ", and you will find the following code :

Code: Select all

$g_upgrade[200] = array('CreateTableSQL', array( db_get_table( 'api_token' ), "
	id						I		UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
	user_id					I		NOTNULL DEFAULT '0',
	name					C(128)	NOTNULL,
	hash					C(128)	NOTNULL,
	date_created			I		UNSIGNED NOTNULL DEFAULT '0',
	date_used				I		UNSIGNED NOTNULL DEFAULT '0'",
	$t_table_options
	) );
$g_upgrade[201] = array( 'CreateIndexSQL', array( 'idx_user_id_name', db_get_table( 'api_token' ), 'user_id, name', array( 'UNIQUE' ) ) );

# Release marker: 1.3.0-rc.1

$g_upgrade[202] = array( 'CreateIndexSQL', array( 'idx_email', db_get_table( 'user' ), 'email' ) );

# Ensure consistent definition of file attachment blob columns, see #20547
$g_upgrade[203] = array( 'AlterColumnSQL', array( db_get_table( 'bug_file' ), "
	content					B		NULL " . $t_blob_default ) );
$g_upgrade[204] = array( 'AlterColumnSQL', array( db_get_table( 'project_file' ), "
	content					B		NULL " . $t_blob_default ) );

# Enable gravatar plugin if avatars are enabled
$g_upgrade[205] = array( 'UpdateFunction', 'gravatar_plugin', array() );

# Limiting column size, planning for future MySQL utf8mb4 support (see #20465)
$g_upgrade[206] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	username				C(191)	$t_notnull DEFAULT \" '' \"" ) );
$g_upgrade[207] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	realname				C(191)	$t_notnull DEFAULT \" '' \"" ) );
$g_upgrade[208] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	email					C(191)	$t_notnull DEFAULT \" '' \"" ) );

$g_upgrade[209] = array( 'AlterColumnSQL', array( db_get_table( 'api_token' ), "
	user_id					I		UNSIGNED NOTNULL DEFAULT '0',
	date_created			I		UNSIGNED NOTNULL DEFAULT '1',
	date_used				I		UNSIGNED NOTNULL DEFAULT '1'"
	) ); 
replace that code with :

Code: Select all

$g_upgrade[200] = array( 'CreateIndexSQL', array( 'idx_user_id_name', db_get_table( 'api_token' ), 'user_id, name', array( 'UNIQUE' ) ) );

# Release marker: 1.3.0-rc.1

$g_upgrade[201] = array( 'CreateIndexSQL', array( 'idx_email', db_get_table( 'user' ), 'email' ) );

# Ensure consistent definition of file attachment blob columns, see #20547
$g_upgrade[202] = array( 'AlterColumnSQL', array( db_get_table( 'bug_file' ), "
	content					B		NULL " . $t_blob_default ) );
$g_upgrade[203] = array( 'AlterColumnSQL', array( db_get_table( 'project_file' ), "
	content					B		NULL " . $t_blob_default ) );

# Enable gravatar plugin if avatars are enabled
$g_upgrade[204] = array( 'UpdateFunction', 'gravatar_plugin', array() );

# Limiting column size, planning for future MySQL utf8mb4 support (see #20465)
$g_upgrade[205] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	username				C(191)	$t_notnull DEFAULT \" '' \"" ) );
$g_upgrade[206] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	realname				C(191)	$t_notnull DEFAULT \" '' \"" ) );
$g_upgrade[207] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), "
	email					C(191)	$t_notnull DEFAULT \" '' \"" ) );

$g_upgrade[208] = array( 'AlterColumnSQL', array( db_get_table( 'api_token' ), "
	user_id					I		UNSIGNED NOTNULL DEFAULT '0',
	date_created			I		UNSIGNED NOTNULL DEFAULT '1',
	date_used				I		UNSIGNED NOTNULL DEFAULT '1'"
	) ); 
Now run the upgrade process again and see if it completes. If it fails, restore the original schema.php and log an issue on mantisbt.org/bugs ( this you can do straight away in case you do not feel comfortable making such changes ).
Herr.mullepulle
Posts: 5
Joined: 15 Jan 2021, 05:37

Re: Upgrade fails due to already existing table

Post by Herr.mullepulle »

Thanks a lot for the helpful tip!

I'm only able to retry the upgrade this Friday, I'll post the results here.
Herr.mullepulle
Posts: 5
Joined: 15 Jan 2021, 05:37

Re: Upgrade fails due to already existing table

Post by Herr.mullepulle »

I managed to successfully upgrade to 2.24.3 this morning.

The changes to /admin/schema.php were not fully complete, I also had to remove the step 200 (CREATE INDEX "idx_user_id_name" to table "API_TOKEN") because that already existed as well.

Please check the attached image. Is my current schema version 210 correct?

And again a huge thank you for your help! :)
schemaversion.png
schemaversion.png (40.99 KiB) Viewed 2518 times
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Upgrade fails due to already existing table

Post by cas »

God to hear it worked.
You will know for sure at the time of the next upgrade but I think you are fine :mrgreen:
Post Reply