Listy dynamiczne - błąd uaktualniania - OLD Forum of Users and Developers of Epesi BIM

Listy dynamiczne - błąd uaktualniania

hasteros

Epesi wer1,7,2
menu-> crm->zarządzaj listami->jakaś lista dynamiczna->List Crits->update list powoduje błąd i listy się nieuaktualniają:

Pozdrawiam Marek

who=1
type=256
message=mysqlt error: [2013: Lost connection to MySQL server during query] in EXECUTE("INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\_%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')")

error file=/epesi/libs/adodb/adodb-errorhandler.inc.php
error line=77

error backtrace:
[PHP core called function]
function called: handle_epesi_error(256, "mysqlt error: [2013: Lost connection to MySQL server during query] in EXECUTE(\"INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')\")
", "/epesi/libs/adodb/adodb-errorhandler.inc.php", 77, Array ("mysqlt", "EXECUTE", 2013, "Lost connection to MySQL server during query", "INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')", false, Object (ADODB_mysqlt), "INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')", false, "mysqlt error: [2013: Lost connection to MySQL server during query] in EXECUTE(\"INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')\")
"))

File: /epesi/libs/adodb/adodb-errorhandler.inc.php
line 77
function called: trigger_error("mysqlt error: [2013: Lost connection to MySQL server during query] in EXECUTE(\"INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\_%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')\")
", 256)

File: /epesi/libs/adodb/adodb.inc.php
line 1071
function called: ADODB_Error_Handler("mysqlt", "EXECUTE", 2013, "Lost connection to MySQL server during query", "INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\_%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')", false, "Object (ADODB_mysqlt)")

File: /epesi/libs/adodb/adodb.inc.php
line 1027
function called: Execute("INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, 1, '2016-10-11 12:56:11', 1, 15, 'company', r.id, '1' FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE '%\\_competitor\\%' AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = '2' AND r.f_permission IS NOT NULL))) OR (r.created_by = 1)) OR ((r.id = 1)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=15 AND f_record_type='company')")

[PHP core called function]
function called: Execute("INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, ?, ?, 1, ?, ?, r.id, ? FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE ? AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = ? AND r.f_permission IS NOT NULL))) OR (r.created_by = ?)) OR ((r.id = ?)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=? AND f_record_type=?)", Array (#11))

File: /epesi/include/database.php
line 360
function called: call_user_func_array(Array (Object (ADODB_mysqlt), "Execute"), Array ("INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, ?, ?, 1, ?, ?, r.id, ? FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE ? AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = ? AND r.f_permission IS NOT NULL))) OR (r.created_by = ?)) OR ((r.id = ?)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=? AND f_record_type=?)", Array (#11)))

File: /epesi/modules/Premium/ListManager/DynamicList.php
line 53
function called: Execute("INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, %d, %T, 1, %d, %s, r.id, %s FROM company_data_1 AS r WHERE r.active=1 AND ((NOT ((r.f_group LIKE %s AND r.f_group IS NOT NULL))) AND (((NOT ((r.f_permission = %s AND r.f_permission IS NOT NULL))) OR (r.created_by = %d)) OR ((r.id = %d)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=%d AND f_record_type=%s)", Array (#11))

File: /epesi/modules/Premium/ListManager/DynamicListView.php
line 48
function called: update_list()

[PHP core called function]
function called: update_list()

File: /epesi/include/module.php
line 887
function called: call_user_func_array(Array (Object (Premium_ListManager_DynamicListView), "update_list"), Array ())

File: /epesi/include/module.php
line 818
function called: get_html_of_module(Object (Premium_ListManager_DynamicListView), null, null)

File: /epesi/include/module.php
line 117
function called: display_module(Object (Premium_ListManager_DynamicListView), null, null)

File: /epesi/modules/Premium/ListManager/ListManager_0.php
line 87
function called: pack_module("#DynamicListView", null, null, 15)

[PHP core called function]
function called: premium_listmanager_list_crits(Array (#11), Object (Utils_RecordBrowser))

File: /epesi/include/module.php
line 898
function called: call_user_func_array(Array (Object (Premium_ListManager), "premium_listmanager_list_crits"), Array (Array (#11), Object (Utils_RecordBrowser)))

File: /epesi/include/module.php
line 818
function called: get_html_of_module(Object (Premium_ListManager), Array (Array (#11), Object (Utils_RecordBrowser)), "premium_listmanager_list_crits")

[PHP core called function]
function called: display_module(Object (Premium_ListManager), Array (Array (#11), Object (Utils_RecordBrowser)), "premium_listmanager_list_crits")

File: /epesi/modules/Utils/TabbedBrowser/TabbedBrowser_0.php
line 120
function called: call_user_func_array(Array (Object (Utils_RecordBrowser), "display_module"), Array ("Object (Premium_ListManager)", Array (#2), "premium_listmanager_list_crits"))

File: /epesi/modules/Utils/TabbedBrowser/TabbedBrowser_0.php
line 85
function called: display_contents(Array (4, Array (#2), Array (#3), false), 4, true)

[PHP core called function]
function called: body()

File: /epesi/include/module.php
line 898
function called: call_user_func_array(Array (Object (Utils_TabbedBrowser), "body"), Array ())

File: /epesi/include/module.php
line 818
function called: get_html_of_module(Object (Utils_TabbedBrowser), null, null)

File: /epesi/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 1544
function called: display_module(Object (Utils_TabbedBrowser))

File: /epesi/modules/Utils/RecordBrowser/RecordBrowser_0.php
line 1209
function called: view_entry("view", "15", Array (), true)

[PHP core called function]
function called: view_entry_with_REQUEST("view", "15", Array (), true, Array ())

File: /epesi/include/module.php
line 898
function called: call_user_func_array(Array (Object (Utils_RecordBrowser), "view_entry_with_REQUEST"), Array ("view", "15", Array (#0), true, Array (#0)))

File: /epesi/include/module.php
line 818
function called: get_html_of_module(Object (Utils_RecordBrowser), Array ("view", "15", Array (#0), true, Array (#0)), "view_entry_with_REQUEST")

File: /epesi/modules/Base/Box/Box_0.php
line 138
function called: display_module(Object (Utils_RecordBrowser), Array ("view", "15", Array (#0), true, Array (#0)), "view_entry_with_REQUEST")

File: /epesi/include/epesi.php
line 246
function called: body()

File: /epesi/include/epesi.php
line 291
function called: go(Object (Base_Box))

File: /epesi/process.php
line 41
function called: process("action_module=%2FBase_Box%7C0%2FUtils_RecordBrowser%7Cmain_29f367b39deb851911382f0b1a08485a%2FPremium_ListManager%7C2%2FPremium_ListManager%23DynamicListView%7C0&%2FBase_Box%7C0%2FUtils_RecordBrowser%7Cmain_29f367b39deb851911382f0b1a08485a%2FPremium_ListManager%7C2%2FPremium_ListManager%23DynamicListView%7C0_callback_17f0fe7a833362bbb7af9f9d35ab29f0=1", "")

ajb

Każda lista ma taki problem? Może jest jakiś timeout na połączenie i coś za długo się wykonuje? Ogólnie to logi mysql'a trzeba zobaczyć, może tam jest więcej info co się dzieje.

hasteros

Nie, nie we wszystkich listach. Błędy dają tylko te listy w tych które mają, ok 1500-2000 wybranych firm/kontaktów i z wieloma kryteriami wyboru.
Te błędy Lost connection to MySQ pojawiają się też wtedy gdy nikt nie korzysta z systemu
Póki co rozmawiam z informatykami hostingu (home.pl). Zobaczymy co wskórają
Pozdrawiam Marek

ajb

Wszystko może wynikać z tych list.
Tzn cron uruchamia przebudowanie list co 24h, więc może się pojawiać ten sam błąd.

Wszystko wskazuje na to, że kwerenda się za długo wykonuje. Pytanie jaki jest timeout:

show variables like '%timeout%';

hasteros

Co do timeout, to tak to wygląda na serwerze

connect_timeout 10
delayed_insert_timeout 300
innodb_lock_wait_timeout 50
innodb_rollback_on_timeout OFF
interactive_timeout 300
lock_wait_timeout 31536000
net_read_timeout 30
net_write_timeout 60
slave_net_timeout 3600
thread_pool_idle_timeout 60
wait_timeout 120

A ponizej odpowiedź home.pl
"logach widoczne są zapytania, które są za długo wykonywane:

#2016.10.13 08:13:20 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 08:34:31 User: 00263157_albo11 DB: 00263157_albo11 Time: 52 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 08:55:31 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 09:17:17 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 09:38:17 User: 00263157_albo11 DB: 00263157_albo11 Time: 52 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 09:59:42 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 10:20:52 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 10:40:53 User: 00263157_albo11 DB: 00263157_albo11 Time: 52 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 10:42:05 User: 00263157_albo11 DB: 00263157_albo11 Time: 52 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 11:03:12 User: 00263157_albo11 DB: 00263157_albo11 Time: 53 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6749
#2016.10.13 11:51:26 User: 00263157_albo11 DB: 00263157_albo11 Time: 51 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=13 AND record_id=6761
#2016.10.13 11:51:29 User: 00263157_albo11 DB: 00263157_albo11 Time: 51 LTime: 50 State: updating Query: DELETE FROM recordbrowser_words_map WHERE tab_id=12 AND record_id=5180

Państwa baza w dużym stopniu obciąża maszynę fizyczną, na której znajduje się Państwa serwer, co ma negatywny wpływ na działanie naszej sieci i usług innych klientów. Prosimy o weryfikację i optymalizację."
Pozdrawiam Marek

ajb

Proszę o pilną aktualizację EPESI do 1.8.0. Indeks wyszukiwania jest zmieniony. Problemy powinny zniknąć

hasteros

Dzień dobry
Po aktualizacji do 1.8 (hosting home,pl) uaktualnianie list nadal mają ten sam problem.
Małe listy uaktualniają się ok, przy dużych po ok 30 sekundach dostaję błąd:
Exception
Message: DB gone away
File: /epesi/include/database.php
Line=216
error backtrace:
File: /epesi/include/database.php
line 378
function called: call_with_retry("\"\"\\"Execute\\"\"\"", "\"\"Array (\\"INSERT INTO premium_listmanager_element_data_1 (active, created_by, created_on, f_active, f_list_name, f_record_type, f_record_id, f_votes) SELECT 1, ?, ?, 1, ?, ?, r.id, ? FROM company_data_1 AS r WHERE r.active=1 AND (((NOT ((r.f_group LIKE ? AND r.f_group IS NOT NULL))) AND (NOT ((r.f_group LIKE ? AND r.f_group IS NOT NULL))) AND (((r.f_branza LIKE ? AND r.f_branza IS NOT NULL)) OR ((r.f_branza LIKE ? AND r.f_branza IS NOT NULL)) OR ((r.f_branza LIKE ? AND r.f_branza IS NOT NULL)) OR ((r.f_branza LIKE ? AND r.f_branza IS NOT NULL))) AND (NOT ((r.f_group LIKE ? AND r.f_group IS NOT NULL)))) AND (((NOT ((r.f_permission = ? AND r.f_permission IS NOT NULL))) OR (r.created_by = ?)) OR ((r.id = ?)))) AND r.id NOT IN (SELECT led.f_record_id FROM premium_listmanager_element_data_1 led WHERE f_list_name=? AND f_record_type=?)\\", Array (#17))\"\"").......... itd
Pozdrawiam Marek

ajb

Jak duża jest lista i jak duża jest baza kontaktów/firm z których ma być zbudowana lista?

hasteros

Dzień dobry
Lista firm 1600- 1800 i kontaktów to 500
Pozdrawiam Marek

ajb

Hm... no to albo serwer jakoś wybitnie wolno działa, albo dzieje się coś "złego". Nie powinno to tak wolno działać.
Pozwoliłem sobie skorzystać z dostępu do instalacji i prześledzić problem. Na razie nie doszedłem do rozwiązania, ale znalazłem inny błąd - tzn nie da się edytować kryteriów teraz.

Dam znać co i jak, gdy się uporam z problemami.

Pozdrawiam,
Adam

hasteros

Dodam, że błędy pojawiają/wyświetlają się tylko na dole strony na której aktualizuję daną listę. Te błędy nie są teraz wysyłane na maila, generowane przez wywołanie z corona też nie.
pozdrawiam Marek

ajb

Już powinna działać aktualizacja listy.
Rozdzieliłem dodawanie nowych rekordów z jednej kwerendy na wywołania new_record. Ogólnie powinno to być wolniejsze, ale nie wykonuje jednego długiego zapytania, więc nie powinno być timeout. Są u Was na instalacji wgrane zmiany do modułu listmanager. Niedługo wrzucę do sklepu poprawioną wersję modułu.
Natomiast ddycja kryteriów będzie działać po release 1.8.1.

ajb

Tak dokładnie, bo raz dziennie wykonywana jest aktualizacja wszystkich dynamicznych list.

hasteros

Dziękuję za szybką reakcję i pomoc, jak zwykle pełen profesjonalizm ?
w poprzednim poście napisałem, że maile z tymi błędami NIE są wysyłane.
Pozdrawiam Marek

hasteros

Dzień dobry
Taka mała przypominajka jeśli oczywiście czas pozwoli
" Niedługo wrzucę do sklepu poprawioną wersję modułu.
Natomiast ddycja kryteriów będzie działać po release 1.8.1."

Pozdrawiam Marek

ajb

Racja, dziękuję za przypomnienie.

ajb

Wgrałem nową wersję. Teraz powinna działać edycja kryteriów. Można zrobić update nadpisujący pliki.

hasteros

Dziękuję, wgrałem. Po krótkich testach jest OK

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge