Home Explore Help
Register Sign In
diogo
/
gnu-social
Watch 19
Star 39
Fork 35
Files Issues 115 Pull Requests 5 Wiki
Labels Milestones
New Issue

#205 Unknown error with checkschema.php

Closed
opened 3 years ago by postblue · 8 comments
Guillaume commented 3 years ago 
Constraint checking Notice table...
* notice_reply_to_fkey (reply_to => notice.id)
* notice_repeat_of_fkey (repeat_of => notice.id)
* notice_profile_id_fkey (profile_id => profile.id)
PHP Fatal error:  Uncaught PEAR_Exception: DB Error: unknown error in /var/www/social/lib/database/schema.php on line 544
#0 /var/www/social/lib/database/schema.php(544): PEAR_ErrorToPEAR_Exception(Object(DB_Error))
#1 /var/www/social/lib/database/schema.php(519): Schema->runSqlSet(Array)
#2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('notice', Array)
#3 /var/www/social/scripts/checkschema.php(50): SchemaUpdater->checkSchema()
#4 {main}
  thrown in /var/www/social/lib/util/framework.php on line 216
``` Constraint checking Notice table... * notice_reply_to_fkey (reply_to => notice.id) * notice_repeat_of_fkey (repeat_of => notice.id) * notice_profile_id_fkey (profile_id => profile.id) PHP Fatal error: Uncaught PEAR_Exception: DB Error: unknown error in /var/www/social/lib/database/schema.php on line 544 #0 /var/www/social/lib/database/schema.php(544): PEAR_ErrorToPEAR_Exception(Object(DB_Error)) #1 /var/www/social/lib/database/schema.php(519): Schema->runSqlSet(Array) #2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('notice', Array) #3 /var/www/social/scripts/checkschema.php(50): SchemaUpdater->checkSchema() #4 {main} thrown in /var/www/social/lib/util/framework.php on line 216 ```
XRevan86 commented 3 years ago
Collaborator

If on PostgreSQL, look into /var/log/postgresql/, it records the failing queries.

If on MariaDB, enable sql_errlog, something like this:

plugin_load_add         = sql_errlog
sql_error_log           = FORCE
sql_error_log_filename  = /var/log/mysql/sqlerr.log
sql_error_log_rotate    = ON

Then that file will contain the query that makes this fail.

Sorry for having to resort to this…

If on PostgreSQL, look into ```/var/log/postgresql/```, it records the failing queries. If on MariaDB, enable [sql_errlog](https://mariadb.com/kb/en/sql-error-log-plugin/), something like this: ```toml plugin_load_add = sql_errlog sql_error_log = FORCE sql_error_log_filename = /var/log/mysql/sqlerr.log sql_error_log_rotate = ON ``` Then that file will contain the query that makes this fail. Sorry for having to resort to this…
Guillaume commented 3 years ago
Poster

Ok, since latest update it's working, I don't know why and I don't like not knowing why. Maybe is it related to the utf8 settings I had to enforce in MariaDB because encoding was all over the place?

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

Besides, I'm still seeing notices with whacked up encoding, like https://herds.eu/conversation/3292547#notice-5470203, is there a script to fix it?

Update:

I ran a little script to change collation in the database/tables:

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

Mostly it's working but I'm getting a few errors in the sqlerr.log file I created earlier (mainly duplicate entries):

2020-08-31  8:29:49 root[root] @ localhost [] ERROR 1114: The table 'notice' is full : ALTER TABLE `notice` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
[...]
2020-08-31  8:30:06 root[root] @ localhost [] ERROR 1062: Duplicate entry 'livetweeter' for key 'PRIMARY' : ALTER TABLE `notice_source` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
2020-08-31  8:30:21 root[root] @ localhost [] ERROR 1062: Duplicate entry 'yaséquenoaplauden-109690' for key 'PRIMARY' : ALTER TABLE `notice_tag` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
2020-08-31  8:30:27 root[root] @ localhost [] ERROR 1062: Duplicate entry 'https://7nw.eu/users/luclu7' for key 'PRIMARY' : ALTER TABLE `ostatus_profile` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

The intriguing one is The table 'notice' is full and I don't find any documentation on that one.

Ok, since latest update it's working, I don't know why and I don't like not knowing why. Maybe is it related to the utf8 settings I had to enforce in MariaDB because encoding was all over the place? ``` [client] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' ``` Besides, I'm still seeing notices with whacked up encoding, like https://herds.eu/conversation/3292547#notice-5470203, is there a script to fix it? Update: I ran a little script to change collation in the database/tables: ``` #!/bin/bash # mycollate.sh <database> [<charset> <collation>] # changes MySQL/MariaDB charset and collation for one database - all tables and # all columns in all tables DB="$1" CHARSET="$2" COLL="$3" [ -n "$DB" ] || exit 1 [ -n "$CHARSET" ] || CHARSET="utf8mb4" [ -n "$COLL" ] || COLL="utf8mb4_general_ci" echo $DB echo "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLL;" | mysql echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | ( while read TABLE; do echo $DB.$TABLE echo "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB done ) ``` Mostly it's working but I'm getting a few errors in the sqlerr.log file I created earlier (mainly duplicate entries): ``` 2020-08-31 8:29:49 root[root] @ localhost [] ERROR 1114: The table 'notice' is full : ALTER TABLE `notice` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci [...] 2020-08-31 8:30:06 root[root] @ localhost [] ERROR 1062: Duplicate entry 'livetweeter' for key 'PRIMARY' : ALTER TABLE `notice_source` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 2020-08-31 8:30:21 root[root] @ localhost [] ERROR 1062: Duplicate entry 'yaséquenoaplauden-109690' for key 'PRIMARY' : ALTER TABLE `notice_tag` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 2020-08-31 8:30:27 root[root] @ localhost [] ERROR 1062: Duplicate entry 'https://7nw.eu/users/luclu7' for key 'PRIMARY' : ALTER TABLE `ostatus_profile` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ``` The intriguing one is `The table 'notice' is full` and I don't find any documentation on that one.
XRevan86 commented 3 years ago
Collaborator

The intriguing one is The table 'notice' is full and I don't find any documentation on that one.

Indeed, not sure what that means. But don't use that script, use checkschema.php instead.

I had to enforce in MariaDB because encoding was all over the place

Guillaume, that can't be right, GNU social creates everything with utf8mb4 on its own: lib/database/mysqlschema.php#L366

is there a script to fix it?

That should not have happened in the first place…

> The intriguing one is ```The table 'notice' is full``` and I don't find any documentation on that one. Indeed, not sure what that means. But don't use that script, use ```checkschema.php``` instead. > I had to enforce in MariaDB because encoding was all over the place Guillaume, that can't be right, GNU social creates everything with ```utf8mb4``` on its own: [lib/database/mysqlschema.php#L366](https://notabug.org/diogo/gnu-social/src/be5bec98879db6724bc596e2581ce0400d71c193/lib/database/mysqlschema.php#L366) > is there a script to fix it? That should not have happened in the first place…
XRevan86 commented 3 years ago
Collaborator

If this is caused by b01974b665, then b04469a252 should fix this.

If this is caused by b01974b665e7e733385589901343adc739952e25, then b04469a252c8b31e17dd5099598ed47b61b97ad7 should fix this.
XRevan86 closed 3 years ago
Guillaume commented 3 years ago
Poster

Running upgrade.php, still getting the first issue

udo -u www-data php scripts/upgrade.php -f
[sudo] Mot de passe de postblue : 
Ensuring all conversations have a URI...DONE.
Upgrading core schema...
Constraint checking Notice table...
* notice_reply_to_fkey (reply_to => notice.id)
* notice_repeat_of_fkey (repeat_of => notice.id)
* notice_profile_id_fkey (profile_id => profile.id)
PHP Fatal error:  Uncaught PEAR_Exception: DB Error: unknown error in /var/www/social/lib/database/schema.php on line 544                                                                                                                     
#0 /var/www/social/lib/database/schema.php(544): PEAR_ErrorToPEAR_Exception(Object(DB_Error))
#1 /var/www/social/lib/database/schema.php(519): Schema->runSqlSet(Array)
#2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('notice', Array)
#3 /var/www/social/scripts/upgrade.php(109): SchemaUpdater->checkSchema()
#4 /var/www/social/scripts/upgrade.php(58): updateSchemaCore()
#5 /var/www/social/scripts/upgrade.php(714): main()
#6 {main}
  thrown in /var/www/social/lib/util/framework.php on line 216

With this error:

2020-09-24 19:21:25 social[social] @ localhost [] ERROR 1114: The table 'notice' is full : ALTER TABLE `notice` DROP INDEX notice_url_idx, MODIFY COLUMN `content` text COLLATE utf8mb4_unicode_ci NULL COMMENT 'update content',
MODIFY COLUMN `url` text COLLATE utf8mb4_bin NULL COMMENT 'URL of any attachment (image, video, bookmark, whatever)', ADD INDEX notice_url_idx (`url`(191))

I may be running out of disk space over the time (90% of 40Go), is there a good way to "clean" an installation/database of unneeded files/references/... after 5+ years?

Checking disk usage, I have: 5,6G of /file/avatars, 2,3G of /file/thumbs, 8,1G of files in /file.

Running upgrade.php, still getting the first issue ``` udo -u www-data php scripts/upgrade.php -f [sudo] Mot de passe de postblue :  Ensuring all conversations have a URI...DONE. Upgrading core schema... Constraint checking Notice table... * notice_reply_to_fkey (reply_to => notice.id) * notice_repeat_of_fkey (repeat_of => notice.id) * notice_profile_id_fkey (profile_id => profile.id) PHP Fatal error: Uncaught PEAR_Exception: DB Error: unknown error in /var/www/social/lib/database/schema.php on line 544 #0 /var/www/social/lib/database/schema.php(544): PEAR_ErrorToPEAR_Exception(Object(DB_Error)) #1 /var/www/social/lib/database/schema.php(519): Schema->runSqlSet(Array) #2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('notice', Array) #3 /var/www/social/scripts/upgrade.php(109): SchemaUpdater->checkSchema() #4 /var/www/social/scripts/upgrade.php(58): updateSchemaCore() #5 /var/www/social/scripts/upgrade.php(714): main() #6 {main} thrown in /var/www/social/lib/util/framework.php on line 216 ``` With this error: ``` 2020-09-24 19:21:25 social[social] @ localhost [] ERROR 1114: The table 'notice' is full : ALTER TABLE `notice` DROP INDEX notice_url_idx, MODIFY COLUMN `content` text COLLATE utf8mb4_unicode_ci NULL COMMENT 'update content', MODIFY COLUMN `url` text COLLATE utf8mb4_bin NULL COMMENT 'URL of any attachment (image, video, bookmark, whatever)', ADD INDEX notice_url_idx (`url`(191)) ``` I may be running out of disk space over the time (90% of 40Go), is there a good way to "clean" an installation/database of unneeded files/references/... after 5+ years? Checking disk usage, I have: 5,6G of /file/avatars, 2,3G of /file/thumbs, 8,1G of files in /file.
XRevan86 commented 3 years ago
Collaborator

This is very suspicious still, I wonder if it's all in /var/lib/mysql/ibdata1 and not in individual InnoDB tablespaces.
Though a more probable cause is that this needs a relation rebuild, and there's indeed no space for that.

This is very suspicious still, I wonder if it's all in ```/var/lib/mysql/ibdata1``` and not in individual InnoDB tablespaces. Though a more probable cause is that this needs a relation rebuild, and there's indeed no space for that.
Guillaume commented 3 years ago
Poster

Still getting this error:

PHP Fatal error:  Uncaught PEAR_Exception: MDB2 Error: unknown error in /var/www/social/lib/database/schema.php on line 569
#0 /var/www/social/lib/database/schema.php(569): PEAR_ErrorToPEAR_Exception(Object(MDB2_Error))
#1 /var/www/social/lib/database/schema.php(541): Schema->runSqlSet(Array)
#2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('user', Array)
#3 /var/www/social/scripts/upgrade.php(109): SchemaUpdater->checkSchema()
#4 /var/www/social/scripts/upgrade.php(58): updateSchemaCore()
#5 /var/www/social/scripts/upgrade.php(714): main()
#6 {main}
  thrown in /var/www/social/lib/util/framework.php on line 215

Getting this in sqlerr.log:

MODIFY COLUMN `uri` varchar(191) COLLATE 'utf82020-10-11 15:46:12 social[social] @ localhost [] ERROR 1115: Unknown character set: 'utf8mb4 COLLATE ' : ALTER TABLE `user` DROP INDEX user_created_idx, MODIFY COLUMN `nickname` varchar(64) COLLATE 'utf8mb4_bin' NULL COMMENT 'nickname or username, duped in profile',
MODIFY COLUMN `password` text COLLATE 'utf8mb4_bin' NULL COMMENT 'salted password, can be null for OpenID users',
MODIFY COLUMN `email` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'email address for password recovery etc.',
MODIFY COLUMN `incomingemail` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'email address for post-by-email',
MODIFY COLUMN `language` varchar(50) COLLATE 'utf8mb4_bin' NULL COMMENT 'preferred language',
MODIFY COLUMN `timezone` varchar(50) COLLATE 'utf8mb4_bin' NULL COMMENT 'timezone',
MODIFY COLUMN `sms` varchar(64) COLLATE 'utf8mb4_bin' NULL COMMENT 'sms phone number',
MODIFY COLUMN `smsemail` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'built from sms and carrier',
MODIFY COLUMN `uri` varchar(191) COLLATE 'utf8

Disk of 38G, 29G used, 7,3G free.

Still getting this error: ``` PHP Fatal error: Uncaught PEAR_Exception: MDB2 Error: unknown error in /var/www/social/lib/database/schema.php on line 569 #0 /var/www/social/lib/database/schema.php(569): PEAR_ErrorToPEAR_Exception(Object(MDB2_Error)) #1 /var/www/social/lib/database/schema.php(541): Schema->runSqlSet(Array) #2 /var/www/social/lib/database/schemaupdater.php(70): Schema->ensureTable('user', Array) #3 /var/www/social/scripts/upgrade.php(109): SchemaUpdater->checkSchema() #4 /var/www/social/scripts/upgrade.php(58): updateSchemaCore() #5 /var/www/social/scripts/upgrade.php(714): main() #6 {main} thrown in /var/www/social/lib/util/framework.php on line 215 ``` Getting this in sqlerr.log: ``` MODIFY COLUMN `uri` varchar(191) COLLATE 'utf82020-10-11 15:46:12 social[social] @ localhost [] ERROR 1115: Unknown character set: 'utf8mb4 COLLATE ' : ALTER TABLE `user` DROP INDEX user_created_idx, MODIFY COLUMN `nickname` varchar(64) COLLATE 'utf8mb4_bin' NULL COMMENT 'nickname or username, duped in profile', MODIFY COLUMN `password` text COLLATE 'utf8mb4_bin' NULL COMMENT 'salted password, can be null for OpenID users', MODIFY COLUMN `email` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'email address for password recovery etc.', MODIFY COLUMN `incomingemail` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'email address for post-by-email', MODIFY COLUMN `language` varchar(50) COLLATE 'utf8mb4_bin' NULL COMMENT 'preferred language', MODIFY COLUMN `timezone` varchar(50) COLLATE 'utf8mb4_bin' NULL COMMENT 'timezone', MODIFY COLUMN `sms` varchar(64) COLLATE 'utf8mb4_bin' NULL COMMENT 'sms phone number', MODIFY COLUMN `smsemail` varchar(191) COLLATE 'utf8mb4_bin' NULL COMMENT 'built from sms and carrier', MODIFY COLUMN `uri` varchar(191) COLLATE 'utf8 ``` Disk of 38G, 29G used, 7,3G free.
postblue reopened 3 years ago
XRevan86 commented 3 years ago
Collaborator

@postblue, that was a new issue caused by my typo, fixed in 06dfd91a82.

@postblue, that was a new issue caused by my typo, fixed in 06dfd91a82dae81fb500eaed5112d4753b951dab.
XRevan86 closed 3 years ago
Sign in to join this conversation.
Labels
Clear labels
bounty bug duplicate enhancement help wanted invalid question RFC v3 wontfix
No Label
bounty
bug
duplicate
enhancement
help wanted
invalid
question
RFC
v3
wontfix
Milestone
Clear milestone
No Milestone
Assignee
Clear assignee
No assignee
2 Participants
Write Preview
Loading...
Cancel
Save
There is no content yet.