When I navigate to Update Database, I have a list of Todo changes that need to be Applied. I click the Apply changes and the list turns Green with a Success message. However when I click the Check for further updates. The same Todo list reappears, indicating to me that none of the changes we applied.
I even tried going in directly by turning the updatelogin to true and running emoncms/admin/db but the behaviour is the same. What do I need to do to apply the outstanding db changes?
No one knows what I need to do to apply these? Or why even after it says they have been applied, they still appear as pending changing, on refresh.
It seems that those who know the answer haven’t noticed your question. @TrystanLea or @glyn.hudson ought to be in a position to help.
Hopefully some screenshots will help explain what the issue is.
When I visit the dB Update page, I see a list of changes. I click apply and the list updates and shows all the changes are having been applied. However when I click the show more changes button, the same first list reappears.
Could this be the reason?
I opened a mysql command prompt and executed the first SQL as shown in the list of changes to be applied.
mysql> ALTER TABLE `app_config` MODIFY `userid` int(11);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 1
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
That seems to suggest that because I am specifying an integer length the SQL is failing with a warning. Apparently mySQL now no longer needs int lengths to be specified and all the int datatypes are just declared as int.
All the pending updates are changing the int field lengths. Some are also setting defaults. When I looked up the table descriptions for the tables where a field default is being set, the default does get set. But probably because it is also changing the int length that SQL is probably ending in some kind of non-zero error, which emoncms sees as an issue and marks the SQL statement as not applied, when actually it has been.
I’m afraid I don’t have the detailed knowledge to comment. I’ve ‘mentioned’ the people who I think can help you, but clearly they haven’t responded as yet.
Any idea where are the list of changes to be applied stored? I can see that Lib/dbschemasetup.php is creating a list of SQL commands to be executed. But it is clearly getting it’s inputs from somewhere. There must be some file that is storing all the changes that need to be applied. I need to find that, amend it to remove the int lengths to test my theory.
Fixed this issue.
In every /Modules folder, there is a *_schema.php file. e.g. vis_schema.php in the emoncms/Modules/vis folder.
What the Update database routine does is compares the actual description of the mqSQL tables and compares them with the array description in that schema.php file. If there is a mismatch it feels that there are changes that are needed.
mySQL no longer needs an int length. All my tables have the datatype as int, but the schema.php seems to still be specifying a length for them. The Update database routine then marks this as a change that needs to be applied. Of course that change does not be applied and hence the loop.
The fix for me was going through each schema.php file in every folder and removing the specified length from any int fields.
e.g.
change the line found in vis_schema.php from
'id' => array('type' => 'int(11)', 'Null'=>false, 'Key'=>'PRI', 'Extra'=>'auto_increment'),
to
'id' => array('type' => 'int', 'Null'=>false, 'Key'=>'PRI', 'Extra'=>'auto_increment'),
Then clicking the Update database, gives me a nice " Database is up to date - Nothing to do"
I might open an Issue on the git to fix this because I will get this same issue again when I git pull the next version.
Opened a git issue. Lets see how it gets fixed.
FTFY.
1 Like
Thanks @bruce_miranda what version of MySQL or MariaDB are you using?
Trying your changes here I now see a request to apply these changes:
Applying them gets me stuck with the further changes need to be applied notices.
My MariaDB version is: 10.3.23-MariaDB-0+deb10u1 Raspbian 10
Looks like we would need to filter out the display width of the integer for existing MariaDB users I think.
MYSQL Version
8.0.25-0
ubuntu0.20.04.1
As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types.
https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html