|
paulgreen
|
July 16, 2018 at 5:29 am
Hello – I get the following error come up when manually adding registrants to an event. The system still seems to register the attendee though! Please advise. Thanks, Paul.
An error has occurred:
WordPress database error: [Tablespace for table ‘connectn_wp916.wp_esp_promotion_object‘ exists. Please DISCARD the tablespace before IMPORT]
CREATE TABLE wp_esp_promotion_object ( POB_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRO_ID INT UNSIGNED NOT NULL, OBJ_ID INT UNSIGNED NOT NULL, POB_type VARCHAR(45) NULL, POB_used INT NULL, PRIMARY KEY (POB_ID), KEY OBJ_ID (OBJ_ID), KEY PRO_ID (PRO_ID) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
-
This topic was modified 5 years, 9 months ago by
 Josh. Reason: removed stack trace
|
|
Josh
|
July 16, 2018 at 6:57 am
Hi,
The first thing I’d check is see what version of mySQL is used. Can you install the Health Check plugin? You’ll go to WP > Plugins > Add New, then search for Health Check & install. Then go to Dashboard > Health Check and get the Database Server version listed there.
|
|
paulgreen
|
July 16, 2018 at 4:05 pm
Does this help?:
Database server
Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 10.2.16-MariaDB – MariaDB Server
*redacted*
-
This reply was modified 5 years, 9 months ago by Josh. Reason: redacted information that's not needed
|
|
Josh
|
July 16, 2018 at 4:21 pm
OK, thanks, that does help because that lets us know it’s a newer version of MariaDB.
According to
https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp
there is a file like usr/local/var/mysql/{tablename}.idb that should be removed (or just moved to somewhere else to be safe), then that may fix it
|
|
paulgreen
|
July 24, 2018 at 2:56 am
There are a number of these type files (below) – which one(s) should be deleted or moved?
-rw-rw—- 1 mysql mysql 933888 Jul 23 17:48 wp_esp_answer.ibd
-rw-rw—- 1 mysql mysql 262144 Jul 23 17:48 wp_esp_attendee_meta.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:47 wp_esp_checkin.ibd
-rw-rw—- 1 mysql mysql 98304 May 14 12:47 wp_esp_country.ibd
-rw-rw—- 1 mysql mysql 98304 May 14 12:47 wp_esp_currency.ibd
-rw-rw—- 1 mysql mysql 114688 May 14 12:47 wp_esp_currency_payment_method.ibd
-rw-rw—- 1 mysql mysql 180224 Jul 24 07:09 wp_esp_datetime.ibd
-rw-rw—- 1 mysql mysql 131072 Jun 1 17:14 wp_esp_datetime_ticket.ibd
-rw-rw—- 1 mysql mysql 1032192 Jul 23 11:40 wp_esp_event_message_template.ibd
-rw-rw—- 1 mysql mysql 147456 Jul 5 15:29 wp_esp_event_meta.ibd
-rw-rw—- 1 mysql mysql 163840 May 14 12:47 wp_esp_event_question_group.ibd
-rw-rw—- 1 mysql mysql 98304 May 14 12:47 wp_esp_event_venue.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:47 wp_esp_extra_join.ibd
-rw-rw—- 1 mysql mysql 14680064 Jul 24 07:32 wp_esp_extra_meta.ibd
-rw-rw—- 1 mysql mysql 92274688 Jul 24 07:32 wp_esp_line_item.ibd
-rw-rw—- 1 mysql mysql 475136 Jul 24 07:08 wp_esp_log.ibd
-rw-rw—- 1 mysql mysql 33554432 Jul 24 07:14 wp_esp_message.ibd
-rw-rw—- 1 mysql mysql 114688 May 14 12:48 wp_esp_message_template_group.ibd
-rw-rw—- 1 mysql mysql 393216 Jul 18 16:38 wp_esp_message_template.ibd
-rw-rw—- 1 mysql mysql 589824 Jul 24 07:08 wp_esp_payment.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:48 wp_esp_payment_method.ibd
-rw-rw—- 1 mysql mysql 163840 Jul 19 17:46 wp_esp_people_to_post.ibd
-rw-rw—- 1 mysql mysql 147456 Jul 5 15:29 wp_esp_price.ibd
-rw-rw—- 1 mysql mysql 114688 May 14 12:48 wp_esp_price_type.ibd
-rw-rw—- 1 mysql mysql 114688 May 14 12:48 wp_esp_promotion.ibd
-rw-rw—- 1 mysql mysql 163840 Jul 12 18:45 wp_esp_promotion_object.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:48 wp_esp_question_group.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:48 wp_esp_question_group_question.ibd
-rw-rw—- 1 mysql mysql 114688 May 14 12:48 wp_esp_question.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:48 wp_esp_question_option.ibd
-rw-rw—- 1 mysql mysql 10485760 Jul 24 07:09 wp_esp_registration.ibd
-rw-rw—- 1 mysql mysql 163840 Jul 24 07:08 wp_esp_registration_payment.ibd
-rw-rw—- 1 mysql mysql 131072 May 14 12:48 wp_esp_state.ibd
-rw-rw—- 1 mysql mysql 114688 Jul 24 08:29 wp_esp_status.ibd
-rw-rw—- 1 mysql mysql 163840 Jul 24 07:09 wp_esp_ticket.ibd
-rw-rw—- 1 mysql mysql 131072 Jun 1 17:14 wp_esp_ticket_price.ibd
-rw-rw—- 1 mysql mysql 98304 May 14 12:48 wp_esp_ticket_template.ibd
-rw-rw—- 1 mysql mysql 29360128 Jul 24 07:32 wp_esp_transaction.ibd
-rw-rw—- 1 mysql mysql 147456 May 17 11:34 wp_esp_venue_meta.ibd
|
|
Tony
|
July 24, 2018 at 3:26 am
wp_esp_promotion_object.ibd
Personally I would either rename that file, or move it somewhere rather than deleting it.
After doing so you’ll also need to restart MariaDB and then retest to see if it fixed the problem.
|
|
paulgreen
|
July 24, 2018 at 11:29 am
Hosts have said they have moved the file – but the same error comes up. Any ideas. (Not 100% confident in host providers to be honest!)
|
|
Josh
|
July 24, 2018 at 11:54 am
Do you know if they restarted the database after they made the change?
|
|
paulgreen
|
July 24, 2018 at 11:57 am
This what they said:
I have removed wp_esp_promotion_object.ibd and restarted mysql.
I guess if that was the case then the error shouldn’t occur because the file is not there?
|
|
Josh
|
July 24, 2018 at 12:02 pm
It may have gotten added again. They should be able to investigate further, the condition may be what’s described in this answer:
https://stackoverflow.com/a/21692066/2311736
So it could be a locked table or an orphaned tablename.ibd file without its normal companion tablename.frm file
|
|
paulgreen
|
July 25, 2018 at 9:01 am
Latest from hosts:
I have moved wp_esp_promotion_object.ibd but have noticed there is also table data for wp_esp_promotion.ibd
Would this also need removing or is this table okay?
MySQL has not been restarted as of yet, please confirm the above table and then i will restart for you to test.
|
|
Josh
|
July 25, 2018 at 9:15 am
I’m not sure what they’re asking. If you’re not getting any errors related to that other table then I don’t think that should be moved. If they find that there is a locked table or an orphaned tablename.ibd file without its normal companion tablename.frm file then it could be moved.
|
|
paulgreen
|
July 26, 2018 at 3:25 am
Hi – the problem still persists even though they say they have removed relevant files.
This is the latest they have come back with:
I believe this is because you are not discarding the table space before you attempt to import it.
Using PHPMyadmin your developer would need to discard the tablespace before you can then re-import anything over the top.
DROP TABLESPACE tablespace_name
[ENGINE [=] engine_name]
|
|
Tony
|
July 26, 2018 at 4:30 am
Your using MariaDB, right?
According to MariaDB documentation, it does not support DROP TABLESPACE so I don’t think that will work.
https://mariadb.com/kb/en/library/drop-tablespace/
Before moving forward I’d recommend creating a full database backup, DROP commands are irreversible.
What do you get if you run SELECT * FROM information_schema.INNODB_SYS_TABLESPACES?
|
|
paulgreen
|
July 26, 2018 at 4:52 am
The host provider is now recommending this:
Completely right, i was thinking of MySQL rather than MariaDB in this case you would use the following:
use database;
ALTER TABLE table_name DISCARD TABLESPACE;
This is able to be done by your developer via your cPanel acccount using PHPmyadmin, unfortunately discarding table data is something you or your developer would need to do.
Your thoughts? I can get one of my developers to do this if you think it will make a difference.
|
|
Tony
|
July 26, 2018 at 5:08 am
As far as I know, it’s effectively the same as deleting the .ibd file for that table so to be honest I’d be surprised if it made a difference, but its worth a shot.
I think you’d need something like:
ALTER TABLE connectn_wp916.wp_esp_promotion_object DISCARD TABLESPACE;
Remember to create a back, if something goes wrong the backup will be the only way to restore.
|
|
paulgreen
|
July 27, 2018 at 2:55 am
As you thought this is what happened:
SQL query:
ALTER TABLE wp_esp_promotion_object DISCARD TABLESPACE
MySQL said:
#1146 – Table ‘connectn_wp916.wp_esp_promotion_object’ doesn’t exist
So what now. Would it help if you had access to cpanel? WordPress site?
|
|
Josh
|
July 27, 2018 at 10:23 am
Can you try deactivating, then reactivating the Promotions add-on plugin?
|
|
paulgreen
|
July 28, 2018 at 10:28 am
Still comes up with same error:
An error has occurred:
WordPress database error: [Tablespace for table ‘connectn_wp916.wp_esp_promotion_object‘ exists. Please DISCARD the tablespace before IMPORT]
CREATE TABLE wp_esp_promotion_object ( POB_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRO_ID INT UNSIGNED NOT NULL, OBJ_ID INT UNSIGNED NOT NULL, POB_type VARCHAR(45) NULL, POB_used INT NULL, PRIMARY KEY (POB_ID), KEY OBJ_ID (OBJ_ID), KEY PRO_ID (PRO_ID) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
Stack Trace:#0 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/helpers/EEH_Activation.helper.php(671): EventEspresso\core\services\database\TableManager->createTable(‘esp_promotion_o…’, ‘POB_ID INT UNSI…’, ‘InnoDB ‘) #1 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/data_migration_scripts/EE_Data_Migration_Script_Base.core.php(647): EEH_Activation::create_table(‘esp_promotion_o…’, ‘POB_ID INT UNSI…’, ‘ENGINE=InnoDB ‘, false) #2 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/data_migration_scripts/EE_Data_Migration_Script_Base.core.php(553): EE_Data_Migration_Script_Base->_create_table_and_catch_errors(‘esp_promotion_o…’, ‘POB_ID INT UNSI…’, ‘ENGINE=InnoDB ‘, false) #3 /home/connectn/public_html/wp-content/plugins/eea-promotions/core/data_migration_scripts/EE_DMS_Promotions_1_0_0.dms.php(78): EE_Data_Migration_Script_Base->_table_should_exist_previously(‘esp_promotion_o…’, ‘POB_ID INT UNSI…’, ‘ENGINE=InnoDB ‘) #4 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/EE_Addon.core.php(428): EE_DMS_Promotions_1_0_0->schema_changes_before_migration() #5 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/EE_Addon.core.php(381): EE_Addon->initialize_db() #6 [internal function]: EE_Addon->initialize_db_if_no_migrations_required(”) #7 /home/connectn/public_html/wp-includes/class-wp-hook.php(286): call_user_func_array(Array, Array) #8 /home/connectn/public_html/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters(”, Array) #9 /home/connectn/public_html/wp-includes/plugin.php(453): WP_Hook->do_action(Array) #10 /home/connectn/public_html/wp-content/plugins/event-espresso-core-reg/core/EE_System.core.php(1079): do_action(‘AHEE__EE_System…’) #11 [internal function]: EE_System->perform_activations_upgrades_and_migrations(”) #12 /home/connectn/public_html/wp-includes/class-wp-hook.php(286): call_user_func_array(Array, Array) #13 /home/connectn/public_html/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters(”, Array) #14 /home/connectn/public_html/wp-includes/plugin.php(453): WP_Hook->do_action(Array) #15 /home/connectn/public_html/wp-settings.php(450): do_action(‘init’) #16 /home/connectn/public_html/wp-config.php(79): require_once(‘/home/connectn/…’) #17 /home/connectn/public_html/wp-load.php(37): require_once(‘/home/connectn/…’) #18 /home/connectn/public_html/wp-admin/admin.php(31): require_once(‘/home/connectn/…’) #19 /home/connectn/public_html/wp-admin/plugins.php(10): require_once(‘/home/connectn/…’) #20 {main}
|
|
Josh
|
July 28, 2018 at 1:34 pm
If you look in the database is there still a table named wp_esp_promotion_object there?
We’re not sure why the database would return an error like “#1146 – Table ‘connectn_wp916.wp_esp_promotion_object’ doesn’t exist” if you run the SQL command: ALTER TABLE wp_esp_promotion_object DISCARD TABLESPACE
Do you or your host have a backup of the wp_esp_promotion_object & wp_esp_promotion tables that could be imported back in?
|
|
paulgreen
|
August 1, 2018 at 3:18 pm
I am still awaiting a response from the host. However I do know that there is a daily back up of the whole database, so those tables will be there.
|
|
Tony
|
August 2, 2018 at 3:16 am
What type of hosting are you using?
Each error conflicts with the last, the IMPORT states the table exists, the ALTER shows the table doesn’t exist but then EE creating the table again shows the table already exists.
If you look at the tables in the database using PHPMyAdmin do you see the wp_esp_promotion_object table?
Did you try running this to see the table spaces in the DB? – SELECT * FROM information_schema.INNODB_SYS_TABLESPACES
What does that return?
|
|
paulgreen
|
August 4, 2018 at 6:16 am
If you look at the tables in the database using PHPMyAdmin do you see the wp_esp_promotion_object table?
– No this table is not visible in PHPMyAdmin.
Did you try running this to see the table spaces in the DB? – SELECT * FROM information_schema.INNODB_SYS_TABLESPACES
What does that return?
– This error is returned: Error
– SQL query:
–
–
– SELECT * FROM information_schema.INNODB_SYS_TABLESPACES LIMIT 0, 25
– MySQL said:
–
– #1227 – Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
I believe your web host would need to run that query as you haven’t got access to the information_schema as far as I can see.
|
|
Tony
|
August 6, 2018 at 3:50 am
As far as I can tell, the issue is server side and you’re going to need their help to fix this.
The table isn’t visible within your database.
When you run the query to make sure the tablespace isn’t set it apparently isn’t, yet when EE tries to create the table the server states it already exists even though you don’t have the table and apparently the tablespace doesn’t exist (that doesn’t really make any sense).
What I would do next is create a clone of the site and retest there, tbh, I’d probably create to copies, one locally to confirm it worked there and one on a subdomain of your live site (restricting access to anyone but staff/me) to confirm it worked there.
Right now it seems the database is in limbo and running various commands on the live site trying to figure it out when the results are conflicting with each other is probably not a good idea. I’d recommend creating a copy of the site (you can use something like Duplicator to do this fairly quickly and easily) and confirm if you have the same problem there, at least then you have a ‘safe’ environment to experiment on (note that the cause isn’t clear here so any ‘fixes’ you attempt is an experiment) and find the problem.
|
|
paulgreen
|
August 6, 2018 at 9:18 am
Hello Tony – I am in the process of moving over to a new host. Hopefully that will resolve the issue. I have never had the confidence in the current providers. So let’s see what happens then.
|
|
Tony
|
August 6, 2018 at 10:20 am
Please do let us know how this goes.
I suspect it will ‘just work’ on the new site, but it’s a strange one with the contradicting errors so can’t be 100% sure. As far as EE goes it’s just creating a new table, the SQL Server was returning the error that the tablespace already existed so it ‘should’, just work.
|
