Userman Upgrade Breaks FreePBX 14 - Offical Distro

Hello,

I am setting up a SNG7 install, and things were going great, I love the new FreePBX. But, I decided to run though module admin updates as I saw updates available, the system isn’t in production, and I know that FreePBX 14 is still early in dev phase.

I now have a basically unusable system, I got the following errors:

Then after going back to Module Admin:

On the CLI:

[[email protected] bsurvey]# fwconsole ma install userman
Updating table userman_users...Done
Updating table userman_users_settings...

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'ALTER TABLE userman_users_settings CHANGE module module VARCHAR(65) NOT NULL, CHANGE `key` `key` VARCHAR(255) NOT NULL, CHANGE type type VARCHAR(16) DEFAULT NULL':
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes



  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes


ma [-f|--force] [-d|--debug] [--edge] [--color] [--skipchown] [-e|--autoenable] [--skipdisabled] [--snapshot SNAPSHOT] [--format FORMAT] [-R|--repo REPO] [-t|--tag TAG] [--onlystdout] [--willupdate] [--securityonly] [--sendemails] [--] [<args>]...

FreePBX 14 is actually beta software right now. Not sure if you are aware of that (as this isnt posted in the beta software category)

Anyways go into mariadb and run:

ALTER TABLE userman_users_settings CHANGE module module VARCHAR(65) NOT NULL, CHANGE `key` `key` VARCHAR(190) NOT NULL, CHANGE type type VARCHAR(16) DEFAULT NULL

You will have to basically keep shrinking that “190” value down until you find the right amount if should be at. Once you find it let us know here

Sorry, I didn’t realize I should have posted in Beta. I tried running i was able to actually run the 190 via the CLI, but the command tries to use 255.

MariaDB [asterisk]> ALTER TABLE userman_users_settings CHANGE module module VARCHAR(65) NOT NULL, CHANGE `key` `key` VARCHAR(255) NOT NULL, CHANGE type type VARCHAR(16) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [asterisk]> ALTER TABLE userman_users_settings CHANGE module module VARCHAR(65) NOT NULL, CHANGE `key` `key` VARCHAR(190) NOT NULL, CHANGE type type VARCHAR(16) DEFAULT NULL;
Query OK, 115 rows affected (0.01 sec)
Records: 115  Duplicates: 0  Warnings: 0

But, the install just tries to make it back to 255 lol. I would either need to download the module, and modify the code or just downgrade the module.

I cant find how to do either of those things.

PS: I have run into a similar error with another module, tho unsigned, but perhaps this issue is something to be considered.

@tm1000 This is just a guess, but since you “pulled” 190 out of the air, and it worked, perhaps there is a typo in the userman code and it should be 190. Because 190 works as is, its the 255 that doesn’t work

I should have clarified that it does not and will not fix the issue. I had to know the key length that I proposed was supported and did not want to spin up a vm to test. Thank you for doing that.

There’s nothing to consider but thanks. The databases in the 7 distro support UTF8 in a wider scope than in 13 (and 12 didnt support UTF8 at all). Supporting UTF8 puts restrictions on column lengths because 1 characters is not 1 byte in UTF8. It can be 1,2 or 4 bytes. Depending. In FreePBX 12 it was 1 byte, in 13 it was 2 bytes and in 14 it’s 4 bytes. Thus a normal column length may have a max of 255 bytes, in previous versions of freepbx 255 characters equalled 255 bytes. However now it’s something closer to 255 characters is equal to 1,020 bytes (255 * 4 = 1020). The error says it needs to be 767. So 190 * 4 = 760 and it’ll work. So will 191. But I like even numbers.

Anyways module authors that have defined indexes in mysql will have to shorten their column length on certain columns

It’s not a typo and I didn’t pull it out of the air. 190 * 4 = 760 which is less than 767. It just needed a full explanation.

@tm1000 I understand, yes it was no trouble for me to give the code a try. As I mentioned above, I have already faced a similar issue, so I did research into column byte length restrictions relating to UTF8. It does seem that MariaDB 10.1.02 has fixed this issue, but thats fairly advanced MariaDB compared to 5.5. I tried installing MariaDB 10, but then I couldn’t get asterisk and freepbx to connect.

Thank you for the clear explanation though. It helps both me and others who might read this.

What would you say my best plan is at this point to get my system back to operable? Can I downgrade the module? Should I just wait for a fix to be implemented/Should I open a jira ticket?

This isn’t a random module, i expect almost any FreePBX 14/ SNG7 system would be broken my upgrading Userman at this point, so i’m guessing your just going to tell me to chill out and it will be fixed soon enough.

There’s really nothing for MariaDB to fix. We just have to lower the column limit in modules that have an index assigned against a column that is more than 191 characters

I’ve already published fixes to both User Manager and WebRTC right now. I am just running them through QA. But they are online. No need for me to announce that until after it was checked (sorry)

Depends on the system and when it was installed. It’s already fixed just didn’t announce it yet while I run through the upgrader for the modules.

I also submitted a pull request against the module you linked to from John.

You can also sign his modules for your own systems without getting approval from Sangoma. So key signing shouldn’t stop you from anything these days.

userman v14.0.2.1
webrtc v14.0.3.1

are both published and fixed.

confirmed.

I had to do fwconsole ma upgradeall, then reboot, then fwconsole restart.