search

menatwork / syncCto

Contao Extension :: Synchronize multiple contao installations with each other
https://packagist.org/packages/menatwork/synccto
25 stars 14 forks source link

MySQL Fehler beim Import eines Datenbank Backups #142

Closed christian-kolb closed 10 years ago

christian-kolb commented 11 years ago

Beim Importieren eines Datenbank Backups über syncCto wird mir folgender Fehler angezeigt:

Fatal error: Uncaught exception Exception with message Query error: MySQL server has gone away (SELECT id,name FROM tl_theme ORDER BY name) thrown in /Users/Benutzer/Contao/system/libraries/Database.php on line 686

/Users/Benutzer/Contao/system/libraries/Database.php(633): Database_Statement->query()
/Users/Benutzer/Contao/system/libraries/Database.php(161): Database_Statement->execute()
/Users/Benutzer/Contao/system/modules/easy_themes/EasyThemes.php(145): Database->execute('SELECT id,name ...')
/Users/Benutzer/Contao/system/modules/easy_themes/EasyThemes.php(122): EasyThemes->getThemes()
/Users/Benutzer/Contao/system/modules/easy_themes/EasyThemes.php(105): EasyThemes->generateContainerContent()
/Users/Benutzer/Contao/system/modules/backend/BackendTemplate.php(57): EasyThemes->addContainer('...', 'be_main')
/Users/Benutzer/Contao/system/modules/backend/BackendTemplate.php(144): BackendTemplate->parse()
/Users/Benutzer/Contao/contao/main.php(221): BackendTemplate->output()
/Users/Benutzer/Contao/contao/main.php(123): Main->output()
/Users/Benutzer/Contao/contao/main.php(230): Main->run()
{main}

Fatal error: Uncaught exception 'Exception' with message 'Query error: MySQL server has gone away (UPDATE tl_user SET session='a:32:{s:7:\"referer\";a:8:{s:4:\"last\";s:35:\"/contao/main.php?do=syncCto_backups\";s:7:\"current\";s:63:\"/contao/main.php?do=syncCto_backups&table=tl_syncCto_restore_db\";s:17:\"tl_jedoFlexSlider\";s:34:\"/contao/main.php?do=jedoFlexSlider\";s:15:\"tl_iso_products\";s:32:\"/contao/main.php?do=iso_products\";s:7:\"tl_page\";s:24:\"/contao/main.php?do=page\";s:8:\"tl_theme\";s:26:\"/contao/main.php?do=themes\";s:10:\"tl_article\";s:27:\"/contao/main.php?do=article\";s:13:\"tl_iso_orders\";s:30:\"/contao/main.php?do=iso_orders\";}s:7:\"CURRENT\";a:1:{s:3:\"IDS\";a:1:{i:0;s:1:\"1\";}}s:20:\"PRODUCTDATA_OVERLOAD\";b:0;s:34:\"tl_iso_products_tl_iso_groups_tree\";a:3:{i:1;i:1;i:2;i:1;i:3;i:1;}s:20:\"tl_iso_products_tree\";a:5:{i:29;i:0;i:34;i:0;i:33;i:0;i:36;i:0;i:42;i:0;}s:12:\"tl_page_tree\";a:132:{i:1;i:1;i:3;i:1;i:4;i:1;i:5;i:1;i:17;i:1;i:18;i:1;i:21;i:1;i:24;i:1;i:25;i:1;i:26;i:1;i:31;i:1;i:32;i: in /Users/Benutzer/Contao/system/libraries/Database.php on line 686
stefanheimes commented 11 years ago

Bekannter Fehler. Bitte einmal unter Synchronisation => Einstellungen, unten auf der Seite die Checkbox "Experten-Einstellungen aktivieren" anklicken.

Dort dann bei " "wait_timeout" konfigurieren " und " "interactive_timeout" konfigurieren " auf 50000 setzten.

Danach nochmal die DB importieren. Sollte es immer noch Fehler geben, bitte eben das Ticket aktualisieren.

christian-kolb commented 11 years ago

Habe es eingetragen, ändert aber leider nichts. Er rechnet auch nicht wirklich lange, bevor er diesen Fehler bringt.

backbone87 commented 11 years ago

MySQL version und variablen posten bitte, außerdem bitte einmal EasyThemes deinstallieren und dann nochmal den Import probieren.

christian-kolb commented 11 years ago

MySQL 5.5.20

Welche Variablen brauchst du? Und (bevor ich danach wieder dumm fragen muss) wie lese ich die aus? : )

christian-kolb commented 11 years ago

"Welche Variablen brauchst du? Und (bevor ich danach wieder dumm fragen muss) wie lese ich die aus? : )"

backbone87 commented 11 years ago

Alle MySQL server variablen. Kann man zB mit PHPMyAdmin einsehen. Hast du es mal ohne easythemes probiert?

christian-kolb commented 11 years ago

Hier alle Variablen, an easy_themes sollte es aber nicht liegen. Er steigt bei anderen Installation an einem anderen Punkt aus, die "MySQL server has gone away" Fehlermeldung kommt trotzdem.

auto increment increment 1 auto increment offset 1 autocommit ON automatic sp privileges ON back log 50 basedir /Applications/MAMP/Library big tables OFF binlog cache size 32 KiB binlog direct non transactional updates OFF binlog format STATEMENT binlog stmt cache size 32 KiB bulk insert buffer size 8 MiB character set client latin1 (Wert für diese Sitzung) utf8 character set connection latin1 (Wert für diese Sitzung) utf8
character set database latin1 character set filesystem binary character set results latin1 (Wert für diese Sitzung) utf8 character set server latin1 character set system utf8 character sets dir /Applications/MAMP/Library/share/charsets/ collation connection latin1_swedish_ci (Wert für diese Sitzung) utf8_general_ci collation database latin1_swedish_ci collation server latin1_swedish_ci completion type NO_CHAIN concurrent insert AUTO connect timeout 10 datadir /Applications/MAMP/db/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default storage engine InnoDB default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown ON event scheduler OFF expire logs days 0 flush OFF flush time 0 foreign key checks ON ft boolean syntax + -><()~*:""&| ft max word len 84 ft min word len 4 ft query expansion limit 20 ft stopword file (built-in) general log OFF general log file /Applications/MAMP/db/mysql/MacBook.log group concat max len 1,024 have compress YES have crypt YES have csv YES have dynamic loading YES have geometry YES have innodb YES have ndbcluster NO have openssl NO have partitioning YES have profiling YES have query cache YES have rtree keys YES have ssl NO have symlink YES hostname MacBook.local ignore builtin innodb OFF init connect init file init slave innodb adaptive flushing ON innodb adaptive hash index ON innodb additional mem pool size 8 MiB innodb autoextend increment 8 innodb autoinc lock mode 1 innodb buffer pool instances 1 innodb buffer pool size 128 MiB innodb change buffering all innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file format Antelope innodb file format check ON innodb file format max Antelope innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force load corrupted OFF innodb force recovery 0 innodb io capacity 200 innodb large prefix OFF innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log buffer size 8 MiB innodb log file size 5 MiB innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 75 innodb max purge lag 0 innodb mirrored log groups 1 innodb old blocks pct 37 innodb old blocks time 0 innodb open files 300 innodb purge batch size 20 innodb purge threads 0 innodb random read ahead OFF innodb read ahead threshold 56 innodb read io threads 4 innodb replication delay 0 innodb rollback on timeout OFF innodb rollback segments 128 innodb spin wait delay 6 innodb stats method nulls_equal innodb stats on metadata ON innodb stats sample pages 8 innodb strict mode OFF innodb support xa ON innodb sync spin loops 30 innodb table locks ON innodb thread concurrency 0 innodb thread sleep delay 10,000 innodb use native aio OFF innodb use sys malloc ON innodb version 1.1.8 innodb write io threads 4 interactive timeout 28,800 join buffer size 128 KiB keep files on create OFF key buffer size 8 MiB key cache age threshold 300 key cache block size 1 KiB key cache division limit 100 large files support ON large page size 0 B large pages OFF lc messages en_US lc messages dir /Applications/MAMP/Library/share/ lc time names en_US license GPL local infile ON lock wait timeout 31,536,000 locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /Applications/MAMP/logs/mysql_error_log.err log output FILE log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system ON lower case table names 0 max allowed packet 1,048,576 max binlog cache size 16 EiB max binlog size 1 GiB max binlog stmt cache size 16 EiB max connect errors 10 max connections 151 max delayed threads 20 max error count 64 max heap table size 16 MiB max insert delayed threads 20 max join size 18,446,744,073,709,551,616 max length for sort data 1,024 max long data size 1,048,576 max prepared stmt count 16,382 max relay log size 0 B max seeks for key 18,446,744,073,709,551,616 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 18,446,744,073,709,551,616 metadata locks cache size 1,024 min examined row limit 0 multi range count 256 myisam data pointer size 6 B myisam max sort file size 8 EiB myisam mmap size 16 EiB myisam recover options OFF myisam repair threads 1 myisam sort buffer size 8 MiB myisam stats method nulls_unequal myisam use mmap OFF net buffer length 16,384 net read timeout 30 net retry count 10 net write timeout 60 new OFF old OFF old alter table OFF old passwords OFF open files limit 2,560 optimizer prune level 1 optimizer search depth 62 optimizer switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_inter... performance schema OFF performance schema events waits history long size 10,000 performance schema events waits history size 10 performance schema max cond classes 80 performance schema max cond instances 1,000 performance schema max file classes 50 performance schema max file handles 32,768 performance schema max file instances 10,000 performance schema max mutex classes 200 performance schema max mutex instances 1,000,000 performance schema max rwlock classes 30 performance schema max rwlock instances 1,000,000 performance schema max table handles 100,000 performance schema max table instances 50,000 performance schema max thread classes 50 performance schema max thread instances 1,000 pid file /Applications/MAMP/tmp/mysql/mysql.pid plugin dir /Applications/MAMP/Library/lib/plugin port 3,306 preload buffer size 32 KiB profiling OFF profiling history size 15 protocol version 10 query alloc block size 8 KiB query cache limit 1 MiB query cache min res unit 4,096 query cache size 0 B query cache type ON query cache wlock invalidate OFF query prealloc size 8 KiB range alloc block size 4 KiB read buffer size 128 KiB read only OFF read rnd buffer size 256 KiB relay log relay log index relay log info file relay-log.info relay log purge ON relay log recovery OFF relay log space limit 0 report host report password report port 3,306 report user rpl recovery rank 0 secure auth OFF secure file priv server id 0 skip external locking ON skip name resolve OFF skip networking OFF skip show database OFF slave compressed protocol OFF slave exec mode STRICT slave load tmpdir /var/folders/ml/yx_gxp3x4ljgvf78dxk_g_w00000gn/T/ slave net timeout 3,600 slave skip errors OFF slave transaction retries 10 slave type conversions slow launch time 2 slow query log OFF slow query log file /Applications/MAMP/db/mysql/MacBook-slow.log socket /Applications/MAMP/tmp/mysql/mysql.sock sort buffer size 2 MiB sql auto is null OFF sql big selects ON sql big tables OFF sql buffer result OFF sql log bin ON sql log off OFF sql low priority updates OFF sql max join size 18,446,744,073,709,551,616 sql mode sql notes ON sql quote show create ON sql safe updates OFF sql select limit 18,446,744,073,709,551,616 sql slave skip counter 0 sql warnings OFF ssl ca ssl capath ssl cert ssl cipher ssl key storage engine InnoDB sync binlog 0 sync frm ON sync master info 0 sync relay log 0 sync relay log info 0 system time zone CEST table definition cache 400 table open cache 400 thread cache size 0 thread concurrency 10 thread handling one-thread-per-connection thread stack 262,144 time format %H:%i:%s time zone SYSTEM timed mutexes OFF tmp table size 16 MiB tmpdir /var/folders/ml/yx_gxp3x4ljgvf78dxk_g_w00000gn/T/ transaction alloc block size 8 KiB transaction prealloc size 4 KiB tx isolation REPEATABLE-READ unique checks ON updatable views with limit YES version 5.5.20 version comment Source distribution version compile machine i386 version compile os osx10.6 wait timeout 28,800

backbone87 commented 11 years ago

tut mir leid ich finde da jetzt nix auffälliges, gegebenenfalls mal mehr PHP RAM probieren (sofern weniger als 128 oder die install sehr groß is)

christian-kolb commented 11 years ago

Ist bereits bei 512M. Sonst noch eine Idee woran es liegen könnte? PHP Version, MySQL Version, ...

andreasisaak commented 11 years ago

Ich kann mir nicht erklären was der Fehler soll. Ist das nur auf einem Server und nur in einer Installation?

christian-kolb commented 11 years ago

Tritt auf meinem lokalen Mac auf, aber auch nur bei bestimmten Installationen. Gerade bei welchen mit sehr großen Datenmengen. Sehr viele Bilder und Datentabellen. Gibt es Limits die man beachten muss, bzw. hoch schrauben kann?

stefanheimes commented 11 years ago

http://stackoverflow.com/questions/12425287/mysql-server-has-gone-away-when-importing-large-sql-file

@christian-kolb
Hier gibt es das gleiche Problem, kannst du mal prüfen ob max_allowed_packet zu klein ist. Ich habe gesehen das bei dir ~1MB nur erlaubt ist.

andreasisaak commented 11 years ago

Da leider kein Feedback mehr kam, schließe ich das Ticket.

christian-kolb commented 11 years ago

Entschuldige, ist unter gegangen. Kann gerne geschlossen werden. Die Lösung war die von @stefanheimes angesprochene max_allowed_packet Größe.

tim-bec commented 10 years ago

Darf ich das hier noch mal aufmachen? Ich hab das Problem trotz hochgesetzter max_allowed_packet und angepasster Experten Einstellungen beim syncen vom Server. Syncen des Clients klappt hingegen problemlos.

synccto 2.6.0.rc2 cto 3.2.9

Warning: Error while sending QUERY packet. PID=543 in system/modules/core/library/Contao/Database/Mysqli/Statement.php on line 78
#0 [internal function]: __error(2, 'Error while sen...', '/Users/*/W...', 78, Array)
#1 system/modules/core/library/Contao/Database/Mysqli/Statement.php(78): mysqli->query('DROP TABLE IF E...')
#2 system/modules/core/library/Contao/Database/Statement.php(281): Contao\Database\Mysqli\Statement->execute_query()
#3 system/modules/core/library/Contao/Database.php(202): Contao\Database\Statement->query('DROP TABLE IF E...')
#4 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoDatabase.php(1062): Contao\Database->query('DROP TABLE IF E...')
#5 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(4682): SyncCtoDatabase->runRestore('system/tmp/sql/...', Array)
#6 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(1123): SyncCtoModuleClient->pageSyncFromShowStep4()
#7 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(379): SyncCtoModuleClient->pageSyncFrom()
#8 system/modules/core/classes/BackendModule.php(100): SyncCtoModuleClient->compile()
#9 system/modules/core/classes/Backend.php(272): Contao\BackendModule->generate()
#10 contao/main.php(142): Contao\Backend->getBackendModule('synccto_clients')
#11 contao/main.php(293): Main->run()
#12 {main}

Fatal error: Uncaught exception Exception with message Query error: MySQL server has gone away (INSERT INTO tl_log (tstamp, source, action, username, text, func, ip, browser) VALUES(1400690641, 'BE', 'ERROR', 'wewe', 'Error on synchronization client ID 1 with msg: Query error: MySQL server has gone away (DROP TABLE IF EXISTS synccto_temp_tl_article)', 'SyncCtoModuleClient pageSyncFromShowStep4', '127.0.0.1', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.137 Safari/537.36')) thrown in system/modules/core/library/Contao/Database/Statement.php on line 283
#0 system/modules/core/library/Contao/Database/Statement.php(254): Contao\Database\Statement->query()
#1 system/modules/core/library/Contao/System.php(159): Contao\Database\Statement->execute(1400690641, 'BE', 'ERROR', 'wewe', 'Error on synchr...', 'SyncCtoModuleCl...', '127.0.0.1', 'Mozilla/5.0 (Ma...')
#2 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(4773): Contao\System::log('Error on synchr...', 'SyncCtoModuleCl...', 'ERROR')
#3 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(1123): SyncCtoModuleClient->pageSyncFromShowStep4()
#4 composer/vendor/menatwork/synccto/system/modules/syncCto/SyncCtoModuleClient.php(379): SyncCtoModuleClient->pageSyncFrom()
#5 system/modules/core/classes/BackendModule.php(100): SyncCtoModuleClient->compile()
#6 system/modules/core/classes/Backend.php(272): Contao\BackendModule->generate()
#7 contao/main.php(142): Contao\Backend->getBackendModule('synccto_clients')
#8 contao/main.php(293): Main->run()
#9 {main}
tim-bec commented 10 years ago

Der Fehler besteht hier (synccto 2.6.1 ) leider immer noch. Noch irgendwelche Vorschläge zum Bugfixen?

andreasisaak commented 10 years ago

@stefanheimes Was beim syncFrom vergessen?

tim-bec commented 10 years ago

Soo. Ich glaub ich habs gelöst. Bei mir lokal haben sich warum auch immer mehrere Instancen von mariadb um die Gunst der Verbindung gekloppt und dann nach und nach die Grätsche gemacht. Nach einem Update und dem hochstellen der max_allowed_packet auf 64M ist der sync nun durchgelaufen. Danke fürs zuhören :-)