Mysql problemen

[dark1990]

Beste,

Ik heb het volgende probleem sinds een korte tijd draai ik een lanparty site op joomla alleen sinds 2 nachten klapt mysql verbinding weg


131015 20:08:21 [Warning] IP address '119.161.148.78' could not be resolved: getnameinfo() returned error (code: -3).
131015 20:08:21 [Warning] IP address '119.161.148.78' could not be resolved: getnameinfo() returned error (code: -3).
131015 20:08:22 [Warning] IP address '119.161.148.78' could not be resolved: getnameinfo() returned error (code: -3).
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_categories.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_comments.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_img_comments.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_img_votes.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_img_votes_statistics.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_user.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_votes.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_phocagallery_votes_statistics.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_plugins.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_poll_data.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_poll_date.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_poll_menu.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_polls.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rd_sitemap_rel_viewset_has_view.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rd_sitemap_view.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rd_sitemap_viewset.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsbanners_ad.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsbanners_adcat.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsgallery2_acl.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsgallery2_comments.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsgallery2_config.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsgallery2_files.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_rsgallery2_galleries.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_sections.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_session.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_stats_agents.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_templates_menu.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_users.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_weblinks.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_xmap.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_xmap_ext.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_xmap_items.frm' (errno: 24)
131016 0:11:21 [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './nodlan_website/jos_xmap_sitemap.frm' (errno: 24)
131016 6:35:28 [ERROR] Error in accept: Too many open files
131016 6:39:44 [ERROR] Error in accept: Too many open files
131016 6:44:00 [ERROR] Error in accept: Too many open files
131016 6:48:16 [ERROR] Error in accept: Too many open files
131016 6:52:32 [ERROR] Error in accept: Too many open files


Afgelope nacht herhaalde hij dit weet ik hoevaak er voor:
[Warning] IP address '119.161.148.78' could not be resolved: getnameinfo() returned error (code: -3).

En de hele nacht door:
[ERROR] Error in accept: Too many open files

Nu heb ik op internet gelezen dat To many open files dit het oplosde:
open_files_limit=99000
Nu heb ik dit in /etc/my.cnf gezet ( Ik heb het idee dat hij deze niet als cnf gebruikt kan het mis hebben )

Ik heb mysql ook geupdate naar 5.6.14 vervolgens krijg ik dit in de err log:
2013-10-16 07:28:05 7ffa5490b700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-10-16 07:28:05 7ffa5490b700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-10-16 07:28:05 7ffa5490b700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

en

2013-10-16 07:27:48 7ffa5490b700 InnoDB: Error: Fetch of persistent statistics requested for table "nodlan_website"."jos_xmap_items" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

2013-10-16 07:27:48 7ffa5490b700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

Van het één krijg ik het ander zou iemand mij kunnen helpen deze website draait voor onze inschrijvingen en moet zo snel mogelijk opgelost worden na een reboot van mysql server werkt het weer...

[CT0]

Met ulimit -a kun je zien wat het actuele limit is.

Na het aanpassen natuurlijk wel ff mysql restarten ;)

[dark1990]

root@server1:/home/servers# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 16382
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
root@server1:/home/servers#


Heb geen idee of dit goed of slecht is ?

[dark1990]

moet zeggen in mijn /etc/my.cnf staat bijzonder weinig klopt dit ?

voor het probleem :
[Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known

Lees ik dat :
skip_name_resolve

Is dit aan te raden ? + hoe weet ik zeker dan directadmin / mysql de config /etc/my.cnf gebruikt ?

[dark1990]

zie dan open files - 1024 Heb nu handmatig met command: ulimit -n 99000 heb op 99000 gekregen

root@server1:/home/servers# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 16382
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 99000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
root@server1:/home/servers#

Maargoed lost dit alle problemen op ? en waarom pakt hij het niet uit my.cnf uit de /etc map...

[dark1990]

Met mysql restart:


131016 17:54:05 mysqld_safe mysqld from pid file /usr/local/mysql/data/server1.pid ended
131016 17:54:09 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-10-16 17:54:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see document$
2013-10-16 17:54:10 29390 [Note] Plugin 'FEDERATED' is disabled.
2013-10-16 17:54:10 29390 [Note] InnoDB: The InnoDB memory heap is disabled
2013-10-16 17:54:10 29390 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-10-16 17:54:10 29390 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-10-16 17:54:10 29390 [Note] InnoDB: Using Linux native AIO
2013-10-16 17:54:10 29390 [Note] InnoDB: Not using CPU crc32 instructions
2013-10-16 17:54:10 29390 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-10-16 17:54:10 29390 [Note] InnoDB: Completed initialization of buffer pool
2013-10-16 17:54:10 29390 [Note] InnoDB: Highest supported file format is Barracuda.
2013-10-16 17:54:10 29390 [Note] InnoDB: 128 rollback segment(s) are active.
2013-10-16 17:54:10 29390 [Note] InnoDB: Waiting for purge to start
2013-10-16 17:54:10 29390 [Note] InnoDB: 5.6.14 started; log sequence number 42502283
2013-10-16 17:54:10 29390 [Note] Server hostname (bind-address): '*'; port: 3306
2013-10-16 17:54:10 29390 [Note] IPv6 is available.
2013-10-16 17:54:10 29390 [Note] - '::' resolves to '::';
2013-10-16 17:54:10 29390 [Note] Server socket created on IP: '::'.
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MySQL 50509, now running 50614. Pl$
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MySQL 50509, now running 50614. Pl$
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MySQL 50509, now running 5061$
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host _by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_ by_event_name: expected column 'THREAD_ID' $
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user _by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_acco unt_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MySQL 50509, now running 5061$
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MySQL 50509, now running 50614.$
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to ha$
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_ty pe' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position $
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_in dex_usage' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_ta ble' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_ table' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50509, now running 50614. Please use mysql$
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thr ead_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_acc ount_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_use r_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_hos t_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_stages_summary_global _by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_history_lo ng' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _thread_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _account_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _user_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _host_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_gl obal_by_event_name' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _digest' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name ' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
2013-10-16 17:54:10 29390 [ERROR] Native table 'performance_schema'.'session_account_connect_attr s' has the wrong structure
2013-10-16 17:54:10 29390 [Note] Event Scheduler: Loaded 0 events
2013-10-16 17:54:10 29390 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.14' socket: '/tmp/mysql.sock' port: 3306 Source distribution

Websites werken verder wel..

Ook door performance_schema=off in my.cnf te zetten krijg ik de zelfde meldingen... mijn idee is dat my.cfg in etc niet de config is..

[PimEffting]

MySQL draait niet onder de user "root" waarvoor je de ulimit hebt aangepast.
Bekijk dus ook even de ulimit van de mysql-gebruiker.
Wellicht staat dit nog ergens in je init scripts verstopt?

[dark1990]

kan ik dat niet gewoon aanpassen in mysql config want als ik my.cnf aanpas in /etc gebeurd er niks na reboot

[SebastiaanStok]

skip_name_resolve kan je idd veelal zonder problemen toepassen.

Wat dit doet is het IP-adres van de client terugvoeren naar een hostnaam (reverse DNS).
http://www.vionblog.com/skip-name-resolve-to-speed-up-mysql-and-avoid-problems/

Probleem is alleen dat dit een DNS look-up vereist (voor elke verbinding!) en als er geen word gevonden het enorm traag word.
Als hij bestaat word hij de lokale cache opgeslagen en is de volgende look-up een stuk sneller, bestaat hij niet dan doet hij iedere keer opnieuw proberen zonder lokale cache.

[dark1990]

Eerst moet ik uitzoeken waarom als ik wat in /etc/my.cnf zet het bij een restart niet uitvoerd

Heb bijvoorbeeld:
open files op 99000 gezet

maar bij restart:
open files (-n) 1024

[dark1990]

Beste ik heb de limit vast weten te krijgen buiten de config om.... dat vind ik nog steeds een raadsel aangezien op een server waar ik geen DA heb draaien de /etc/my.cnf veel groter is dan 3 regels. Heb gister mysql opnieuw gebuild via DA en tot nu toe met succes meldingen in log tot nu toe :

131016 22:19:02 mysqld_safe mysqld from pid file /usr/local/mysql/data/server1.pid ended
131016 22:19:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-10-16 22:19:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see document$
2013-10-16 22:19:05 30987 [Note] Plugin 'FEDERATED' is disabled.
2013-10-16 22:19:05 30987 [Note] InnoDB: The InnoDB memory heap is disabled
2013-10-16 22:19:05 30987 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-10-16 22:19:05 30987 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-10-16 22:19:05 30987 [Note] InnoDB: Using Linux native AIO
2013-10-16 22:19:05 30987 [Note] InnoDB: Not using CPU crc32 instructions
2013-10-16 22:19:05 30987 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-10-16 22:19:05 30987 [Note] InnoDB: Completed initialization of buffer pool
2013-10-16 22:19:05 30987 [Note] InnoDB: Highest supported file format is Barracuda.
2013-10-16 22:19:06 30987 [Note] InnoDB: 128 rollback segment(s) are active.
2013-10-16 22:19:06 30987 [Note] InnoDB: Waiting for purge to start
2013-10-16 22:19:06 30987 [Note] InnoDB: 5.6.14 started; log sequence number 45233560
2013-10-16 22:19:06 30987 [Note] Server hostname (bind-address): '*'; port: 3306
2013-10-16 22:19:06 30987 [Note] IPv6 is available.
2013-10-16 22:19:06 30987 [Note] - '::' resolves to '::';
2013-10-16 22:19:06 30987 [Note] Server socket created on IP: '::'.
2013-10-16 22:19:06 30987 [Warning] 'user' entry 'root@U11.worldstream.nl' ignored in --skip-name-resolve mode.
2013-10-16 22:19:06 30987 [Warning] 'user' entry '@U11.worldstream.nl' ignored in --skip-name-resolve mode.
2013-10-16 22:19:06 30987 [Warning] 'proxies_priv' entry '@ root@U11.worldstream.nl' ignored in --skip-name-resolve mode.
2013-10-16 22:19:06 30987 [Note] Event Scheduler: Loaded 0 events
2013-10-16 22:19:06 30987 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.14' socket: '/tmp/mysql.sock' port: 3306 Source distribution