Ana Sayfa Keşfet Yardım
Giriş Yap
diogo
/
gnu-social
İzle 19
Yıldızla 39
Çatalla 35
Dosyalar Sorunlar 116 Değişiklik İstekleri 5 Wiki
Etiketler Kilometre Taşları
Yeni Sorun

#205 Unknown error with checkschema.php

Kapalı
postblue tarafından 4 yıl önce kere açıldı · 8 yorum
Guillaume 4 yıl önce olarak yorumlandı 
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 4 yıl önce olarak yorumlandı
Katkıcı

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 4 yıl önce olarak yorumlandı
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 4 yıl önce olarak yorumlandı
Katkıcı

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 4 yıl önce olarak yorumlandı
Katkıcı

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

If this is caused by b01974b665e7e733385589901343adc739952e25, then b04469a252c8b31e17dd5099598ed47b61b97ad7 should fix this.
XRevan86 4 yıl önce kapattı
Guillaume 4 yıl önce olarak yorumlandı
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 4 yıl önce olarak yorumlandı
Katkıcı

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 4 yıl önce olarak yorumlandı
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 4 yıl önce yeniden açtı
XRevan86 4 yıl önce olarak yorumlandı
Katkıcı

@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 4 yıl önce kapattı
Giriş yap bu konuşmaya katılmak için.
Etiketler
Etiketleri temizle
bounty bug duplicate enhancement help wanted invalid question RFC v3 wontfix
Etiket Yok
bounty
bug
duplicate
enhancement
help wanted
invalid
question
RFC
v3
wontfix
Kilometre Taşı
Kilometre Taşlarını Temizle
Kilometre Taşı Yok
Atanan
Atamayı Temizle
Atanan Kişi Yok
2 Katılımcı
Yaz Önizle
Yükleniyor...
İptal
Kaydet
Henüz bir içerik yok.