Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

When upgrading from older MySQL databases, format is not changed from compact to dynamic #3060

Closed
krishnagummadapu opened this issue Oct 29, 2019 · 15 comments

Comments

@krishnagummadapu
Copy link

Hi All,
I tried to upgrade the Cacti database to version 1.2.5 and got the well known error "ALTER TABLE poller_output_realtime DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id)

little google helped me to manually run that command.
But now my issue is the Cacti page is still sitting at the Error. I tried to restart the database and restarted the system itself. But no use.
How do I proceed/restart the upgrade process from here.
Cacti

@clayton-colovore
Copy link

I have exactly the same issue. How do I trick the installer wizard into proceeding past this page?

@bmfmancini
Copy link
Member

bmfmancini commented Oct 29, 2019 via email

@clayton-colovore
Copy link

clayton-colovore commented Oct 29, 2019

have you enabled large_prefix =1 on my mysql config

not initially, but yes while setting the correct character set and collation options. even after I was able to run the ALTER TABLE line above successfully, the installer would not proceed further. watched with both SHOW PROCESSLIST and tcpdump and saw no evidence that the installer ever tried to rerun the ALTER TABLE statement

I ended up restoring yesterday's db backup and rerunning the upgrade

@krishnagummadapu
Copy link
Author

That did the trick.
Enabled the Large_prefix = 1 and did work.
Thank you.

@tomck
Copy link

tomck commented Nov 11, 2019

I had to learn that it was innodb_large_prefix=1 to get it to even let me restart mysql, but this did not resolve the issue for me. The other threads about this issue don't seem to be as simple. I will admit I have the wrong type of innodb (Anaconda instead of Barracuda) but I wasn't the one who set this up and I worry about the difficulties arising from changing database formats. Besides, now that I've gotten past that part of the process the only option the installer gives, even when trying to go to the homepage, is immediately jumping to the database upgrade page.
Screenshot_2019-11-11 Cacti Server v1 2 7 - Maintenance

However after running the suggested cli upgrade_database.php here, #2824 (comment), it seems to work even though the installer thinks it failed. 🤷‍♂

@TheWitness
Copy link
Member

What happens when you try to run the one command manually?

@ddb4github
Copy link
Contributor

Before MySQL 5.7 and MariaDB 10.2.2 introduce option innodb_default_row_format , default row format is not "Dynamic".
Then ALTER SQL will failure

@netniV
Copy link
Member

netniV commented Nov 28, 2019

I am not sure i follow

@cigamit
Copy link
Member

cigamit commented Nov 28, 2019

I think we should mandate innodb_default_row_format = Dynamic cause in MySQL 8.x, none of the recommendations remain, that includes Braracuda, or large_prefix. What a mess.

@ddb4github
Copy link
Contributor

Current cacti.requiement.mysql=5.1+

MariaDB [cacti]> show variables like 'version';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| version       | 5.5.64-MariaDB |
+---------------+----------------+
MariaDB [cacti]> set global innodb_default_row_format=Dynamic;
ERROR 1193 (HY000): Unknown system variable 'innodb_default_row_format'

@ddb4github
Copy link
Contributor

BTW, SQL below success both on 5.5 and 5.7, what ever current row format:

ALTER TABLE poller_output_realtime ROW_FORMAT=Dynamic, DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id);

@cigamit
Copy link
Member

cigamit commented Nov 29, 2019

I think adding ROW_FORMAT=Dynamic to that alter is a good idea actually. What do you think @netniV?

@cigamit
Copy link
Member

cigamit commented Nov 29, 2019

There are a truck load of ALTERS though in the install directory though. It's almost as if we should somehow do a dynamic sweep before doing all the upgrade steps. Maybe we make that the first step in the upgrade. Might take a while if a user has a lot of big plugin tables, which makes the upgrade service impacting more so than the current procedure.

So, we should handle MyISAM > InnoDB, Latin > UTF8mb4 first and not last and that should include the DYNAMIC flag. What do you think?

@netniV
Copy link
Member

netniV commented Nov 29, 2019

Moving the conversion process to the front of the queue is not a bad plan. But as stated, for really large tables and/or slower systems, that could take some time rendering the system unusable until each table is completed in turn.

In the past, it was very easy to just let the DBA decide the best course to take with regards to formats and options but more and more, specific functionality (such as long keys) a requiring us to lock all that down.

cigamit added a commit that referenced this issue Nov 29, 2019
New tables on some MariaDB versions are created by default using
Compact format instead of Dynamic
@cigamit
Copy link
Member

cigamit commented Nov 29, 2019

Okay, this is mostly fixed. The order was right, but on some MariaDB versions, the row format is Compact by default. We need to add a 'check' step to the generic upgrade script before we call this one a wash. That check script should happen before any table updates during an upgrade to ensure that the tables are Row format dynamic first. So, not completely finished. However, a MariaDB 0.8.8h to 1.2.x works flawlessly now.

cigamit added a commit that referenced this issue Nov 29, 2019
If the --dynamic flag is set, and the table is Compact, don't skip converting.
cigamit added a commit that referenced this issue Nov 29, 2019
Ensure that tables that have a compact row format are migrated to Dynamic before attempting any upgrade.
@netniV netniV changed the title Cacti database upgrade version 1.2.5 failed When upgrading from older MySQL databases, format is not changed from compact to dynamic Dec 7, 2019
netniV added a commit that referenced this issue Dec 20, 2019
* Fix #3136: Capture pre-1.2.1+ upgrade issue as extra patch for #3060

* Fixed#3136: Update Changelog

Co-authored-by: Mark Brugnoli-Vinten <netniv@hotmail.com>
@github-actions github-actions bot locked and limited conversation to collaborators Jun 30, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

8 participants