[SOLVED] How to fix dropped CBSubs History table?

So, I accidentally dropped the history table. Yeah, I know. No one can kick me harder than I'm kicking myself.

I did keep a copy. 400meg - now you know why I was trying to clean it out. It had 65k lines in it.

Import function - no joy. File is too damned big. I edited the db to make it much smaller (no records) and that fixed the history but I got the following syntax errors on import no matter what I do with the amount of records in the table be it 0 or 65k:

Static analysis:

Static analysis:

2 errors were found during analysis.

An opening bracket followed by a set of values was expected. (near "ALTER" at position 371)
Unexpected token. (near "ALTER" at position 371)

SQL query:

-- -- Dumping data for table `j25_cbsubs_history` -- INSERT INTO `j25_cbsubs_history` (`id`, `event_type`, `message`, `table_name`, `table_key_id`, `field_changed`, `change_type`, `old_value`, `new_value`, `event_time`, `user_id`, `ip_addresses`, `log_priority`, `log_version`) VALUES -- -- Indexes for dumped tables -- -- -- Indexes for table `j25_cbsubs_history` -- ALTER TABLE `j25_cbsubs_history` ADD PRIMARY KEY (`id`), ADD KEY `user_time_id` (`user_id`,`event_time`,`id`), ADD KEY `table_name_id_time_id` (`table_name`,`table_key_id`,`event_type`,`event_time`,`id`), ADD KEY `event_time_id` (`event_time`,`id`), ADD KEY `event_type_time_id` (`event_type`,`event_time`,`id`), ADD KEY `log_priority_time_id` (`log_priority`,`event_time`,`id`), ADD KEY `ip_addresses_time_id` (`ip_addresses`,`event_time`,`id`), ADD KEY `change_type` (`change_type`)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALTER TABLE `j25_cbsubs_history`
ADD PRIMARY KEY (`id`),
ADD KEY `user_time_' at line 14

But - I can now go to the history tab and it seems fine. 0 records and all the columns seem to be there/

Unfortunately, when I click on the PAYMENT CENTER tab under CB Subs I get:



I tried to use the tool that fixes the CB Plugins DB and it shows some issues (in red):

CB plugin "CB Paid Subscriptions" database structure differences:
Table #__cbsubs_history Column id AUTO_INCREMENT attribute is "" instead of "auto_increment"
Table #__cbsubs_history Index PRIMARY does not exist
Table #__cbsubs_history Index user_time_id does not exist
Table #__cbsubs_history Index table_name_id_time_id does not exist
Table #__cbsubs_history Index event_time_id does not exist
Table #__cbsubs_history Index event_type_time_id does not exist
Table #__cbsubs_history Index log_priority_time_id does not exist
Table #__cbsubs_history Index ip_addresses_time_id does not exist
Table #__cbsubs_history Index change_type does not exist
Table #__cbsubs_subscriptions Column previous_autorecurring_type does not exist
The CB plugin "CB Paid Subscriptions" database structure differences can be fixed (adjusted) by clicking here: Click here to fix (adjust) all CB plugin "CB Paid Subscriptions" database differences listed above (you can also Click here to preview fixing (adjusting) queries in a dry-run), but in all cases you need to backup database first as this adjustment is changing the database structure to match the needed structure for the installed version.

And it won't fix anything. When I click on the fix, it keeps on dumping me to this error message:

An error has occurred.

1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

If someone could just give me the correct syntax for building the history table I think I could fix this. Right? I mean it's gotta be a syntax issue on the DB dump that causing the import to fail.

I've tried reinstalling CB and CB Subs hoping that it will rebuild the table but no joy.

Can you please help?

Mark

The CBSubs History table can be safely truncated. If you dropped it completely then just install CBSubs over it self and it'll recreate the table. It looks like your attempt to restore it failed though. Drop it again through phpmyadmin then simply install CBSubs again and that should correct it.

As for your issue reaching CBSubs > Payments it maybe one of the search filters failing; try logging out in backend then back in again and see if that resolves it.

Thanks, Krileon. I know your lives are complicated enough without users like me doing stupid things like dropping a freaking table! I am embarrassed beyond words that I did something so completely idiotic.

I've truncated the history table and everything seems to work fine without the history table. Both the error on checking the plugins DB (clean green all through) and the issue with PAID > PAYMENT CENTER has gone away with the dropping of the history table so that's all good.

I did re-install a few times but perhaps with the history table already in place CB Subs didn't go in and fix it.

One seeming issue is that the current zip file that allows CB Subs to install the program and the plugins repository fails in that the plugins ZIP file won't install. I had to do each CB plugin manually like the old days. I'm not sure if that is connected to my history table or not but just FYI. When I re-install if that error is still there I'll let you know.

My client has a meeting today involving registration and membership so I'm going to hang fire until that's done then I'll try to re-install over the top of the current installation.

I was also having an issue with sessions on membership signup so I'm going to see if that's resolved also. If not I'll put in another ticket.

Again, my sincere thanks to all at Joomlapolis. You guys continue to remain the class of the class in the Joomla world. And probably beyond.

Regards,
Mark

Thanks, Krileon. I know your lives are complicated enough without users like me doing stupid things like dropping a freaking table! I am embarrassed beyond words that I did something so completely idiotic.

Accidents happen. Try not to beat your self up over it . I do recommend quickly generating a database backup before making any changes like that as a quick restore would've got you back up and running .

I did re-install a few times but perhaps with the history table already in place CB Subs didn't go in and fix it.

It won't re-create the table if it already exists. The duplicate auto increment column issue likely couldn't be fixed by our SQL parser as it's an abnormal situation that wouldn't typically be caused by the SQL parser (e.g. it only fixes issues it could potentially cause basically like a missing column).

One seeming issue is that the current zip file that allows CB Subs to install the program and the plugins repository fails in that the plugins ZIP file won't install. I had to do each CB plugin manually like the old days. I'm not sure if that is connected to my history table or not but just FYI. When I re-install if that error is still there I'll let you know.

Not sure what you mean by this. You don't need to unzip anything for our packages. Simply download and install through Joomla or CB (it works in either location). If an install is showing as failed during the package installation please expand the log and provide any errors it's outputting.

All issues have been resolved.

The installation fail I saw earlier went away after I dumped the history table and had the new installation (as you suggested) rebuild everything.

Thank you again for all your help. It is truly invaluable.

Mark