Error updating from 4.x to 4.2

[geeknik]

I deployed the 4.2 code via git, and visited /manage/update. I clicked update and received this error: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes.

Going back to the page and trying again gives me this error: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'reports_data' already exists

[ssl]

Can you check your database and compare it with the sql queries https://github.com/ssl/ezXSS/blob/master/app/sql/4.1-4.2.sql

Im not sure why you got this error, might be from Move data fromreportstoreports_data``

Also what is your mysql version?

[geeknik]

8.0.36-0ubuntu0.23.10.1

My DB structure using query="SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = '$db' ORDER BY (data_length + index_length) DESC;"

reports_data    86.52
reports 83.26
alerts  0.02
console 0.02
logs    0.02
payloads    0.02
sessions    0.02
sessions_data   0.02
users   0.02
settings    0.00

If I need to start over, it's okay, there's nothing in the DB I really need to save.

[ssl]

Does reports_data and sessions_data contain all the data from the reports?

And does the reports/sessions tables still contain these columns or not?

• dom, screenshot, localstorage, sessionstorage;
• dom, localstorage, sessionstorage, console;

[ssl]

Going back to the page and trying again gives me this error: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'reports_data' already exists

Also, does this mean the database is still on version 4.1?

[ssl]

Looks like this issue is only on MySQL and not on MariaDB.

It might have todo with the indexes. If all data is correct in reports/sessions_data, and the correct columns got deleted, please check if the indexes got added to the columns. If not, try running it manual and let me know what it says

ALTER TABLE reports ADD INDEX(archive);
ALTER TABLE reports ADD INDEX(payload);
ALTER TABLE reports ADD INDEX(id);
ALTER TABLE reports ADD INDEX(shareid);
ALTER TABLE reports_data ADD INDEX(reportid);

ALTER TABLE sessions ADD INDEX(id);
ALTER TABLE sessions ADD INDEX(payload);
ALTER TABLE sessions ADD INDEX(clientid);
ALTER TABLE sessions ADD INDEX(origin);
ALTER TABLE sessions_data ADD INDEX(sessionid);

ALTER TABLE reports ENGINE INNODB;
ALTER TABLE settings ENGINE INNODB;

[geeknik]

mysql> ALTER TABLE reports ADD INDEX(archive);
Query OK, 119 rows affected, 1 warning (0.02 sec)
Records: 119  Duplicates: 0  Warnings: 1

mysql> ALTER TABLE reports ADD INDEX(payload);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
mysql> ALTER TABLE reports ADD INDEX(id);
Query OK, 119 rows affected (0.01 sec)
Records: 119  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reports ADD INDEX(shareid);
Query OK, 119 rows affected (0.04 sec)
Records: 119  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reports_data ADD INDEX(reportid);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sessions ADD INDEX(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sessions ADD INDEX(payload);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sessions ADD INDEX(clientid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sessions ADD INDEX(origin);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sessions_data ADD INDEX(sessionid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reports ENGINE INNODB;
Query OK, 119 rows affected (0.08 sec)
Records: 119  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE settings ENGINE INNODB;
Query OK, 33 rows affected (0.04 sec)
Records: 33  Duplicates: 0  Warnings: 0

[ssl]

So looks like ALTER TABLE reports ADD INDEX(payload); is the problem. I'm not 100% sure why, but for now, it seems like all other queries got run without an issue and you can just manually change your version to 4.2 in the settings table and everything should work fine.

I will look into the issue but it might be fixable with your server/mysql settings. I will also add a try catch around this code to make sure it doesn't just fail.

[ssl]

Could you check if this query works and fixes it?

ALTER TABLE `reports` CHANGE `payload` `payload` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

ALTER TABLE reports ADD INDEX(payload);

[geeknik]

After I updated the settings to version 4.2, I can login, and everything appears to be working as expected. Thanks!

[ssl]

After I updated the settings to version 4.2, I can login, and everything appears to be working as expected. Thanks!

Amazing!

Could you confirm or deny if the query below gives any errors or fixes the index? Or if not, changing the varchar number to a lower number? I believe it has something todo with MySQL and index sizes. When confirming this I can add a fix to ezXSS to not fail.

Could you check if this query works and fixes it?

ALTER TABLE `reports` CHANGE `payload` `payload` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

ALTER TABLE reports ADD INDEX(payload);

[geeknik]

That query appears to work. Before I updated the settings to version 4.2, I ran that query:

mysql> ALTER TABLE `reports` CHANGE `payload` `payload` VARCHAR(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
Query OK, 119 rows affected (0.09 sec)
Records: 119  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reports ADD INDEX(payload);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

[ssl]

That query appears to work. Before I updated the settings to version 4.2, I ran that query:

mysql> ALTER TABLE `reports` CHANGE `payload` `payload` VARCHAR(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
Query OK, 119 rows affected (0.09 sec)
Records: 119  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE reports ADD INDEX(payload);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Okay thanks! That confirms my suspicion. Seems like a small bug which happens in certain installs but I will implement some fixes to make sure it doesn't happen again.

Thanks again and have a great night.