BIGINT UNSIGNED value is out of range in '(xxxx.xxxx_menu.level - 1)'

[aafisher]

Steps to reproduce the issue

Update any extension with an incorrect menu level in table ####_menu
I got this error when updating each of these: Admintools, Akeeba Backup, jEvents, and JCE.

Expected result

Success Updating Package

Actual result

Warning
BIGINT UNSIGNED value is out of range in '(####.#####_menu.level - 1)'
Package Update: There was an error installing an extension: com_jevents.zip
Before updating ensure that the update is compatible with your Joomla! installation.
You are strongly advised to make a backup of your site's files and database before you start updating.
×
danger
Error updating package.

System information (as much as possible)

Joomla 5.2.4
PHP 8.2.27
mysqlnd 8.2.27

Additional comments

(I ended up uninstalling the extension and reinstalling from scratch. Note that this will be quite an arduous process for me with my jEvents because it has so much data and custom settings. Hopefully, you can fix the problem soon, so I don't have to do this by hand.)

According to Nicholas K. Dionysopoulos, here's the problem and workaround, which I was not able to replicate:

This error is thrown either by Joomla itself or a third party extension. We do not have any code that directly tries to modify the #__menu table on your site.

In fact, the problem comes from Joomla's code. When you upgrade a component Joomla will try to delete the administrator menu items it had created under the backend Components menu. These are records in the #__menu database table. This happens in \Joomla\CMS\Installer\Adapter\ComponentAdapter::_buildAdminMenus() which calls \Joomla\CMS\Installer\Adapter\ComponentAdapter::_removeAdminMenus().

In its turn, \Joomla\CMS\Installer\Adapter\ComponentAdapter::_removeAdminMenus() will create an instance of the \Joomla\Component\Menus\Administrator\Table\MenuTable class and call its delete() method.

The \Joomla\Component\Menus\Administrator\Table\MenuTable::delete() calls the parent method, i.e. \Joomla\CMS\Table\Nested::delete(). This is where the daft code that blindly does level = level - 1 is.

The problem is that the level field of the #__menu table is an unsigned big integer (BIGINT UNSIGNED) i.e. it can only have values equal to or higher than 0 (zero). When Joomla installed or last updated the component a bug in Joomla made one of the admin Component menu items created for Admin Tools to have a level of 0 which is, of course, wrong. Adding insult to injury, Joomla's daft code now causes a database error when it tries to delete these invalid items, preventing you from upgrading the component.

You can resolve this issue, but it requires some manual intervention in the database. You have to edit the #_menu table. Find the items with menutype = main and a title starting with COM_ADMINTOOLS. Set the level of the one items whose title is exactly COM_ADMINTOOLS to 1. Set the level of the other items whose title starts with COM_ADMINTOOLS_TITLE to 2.

Having restored the correct levels of the menu items, you can now perform the upgrade correctly.

This is a bug that should be reported to and fixed by the Joomla project. The Joomla project made it clear that it does not want me to submit bug reports or bug fixes anymore, therefore I cannot do that. I can only point out the Joomla bugs I know to my clients, explain why they happen, and provide workarounds.