Why MySQL Tables Crash
Jul 15, 2009This is something i was wondering about and never found any answers.
Based on your experience, what are the most common reasons mysql tables crash?
This is something i was wondering about and never found any answers.
Based on your experience, what are the most common reasons mysql tables crash?
im planning on designing a webservice, it will have a lot of data, spread in many tables.
The problem is that there will be tables created constantly (around 5 each day..)
All tables/data will be accessed equally, so I dont know how to set up the system, to create multiple databases, and balance the number of tables equally on each database, or create less databases and have a lot of tables on each one.
Which one has more efficiency?
how exactly it helps?
mysqlcheck -o -u...
Like what it does to "optimize" them? Does it really help?
I have a vbulletin (3.7.2) board with many mods installed (nearly 70) without a problem.
However, upon installing one new mod I got a database error stating:
MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
How do I change ALL tables in one shot (or sql query via phpmyadmin) from the two different collations to utf8?
Also, is utf8 suggested or better than latin1?
Is there no possible way I can slave just a few tables in mysql? instead of slaving the entire table on the main database server.
View 1 Replies View RelatedI have cPanle shared account.
Is there a way to automatically check and repair mysql databases and tables, that can be scheduled as cron job?
we have mysql 5 setup and this morning at around 10:07 tables started disappearing as they were being accessed by different clients.
Databases that had 40 tables now had 30, etc. Only the tables that were attempted to be accessed were gone. This is the first time something like this has happened.
The following output was given:
This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose the
problem, but since we have already crashed, something is definitely wrong and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=208
max_connections=500
threads_connected=156
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1096188 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0xaf82930
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb143932c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x816b1a0
0xaf1898
0x20
0x81ac49d
0x8182914
0x8189010
0x8189df1
0x818a738
0x818ae5c
0xaeb371
0x9c4ffe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read [url]and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xaf36620 = SELECT * FROM `sessions` WHERE `PHPSESSID` = '5e6775cd3c6f187d8c575127ba73be19'
thd->thread_id=113407
The manual page at [url]contains
information that should help you find out what is causing the crash.
mysqld: my_new.cc:51: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.
Number of processes running now: 0
070427 10:07:49 mysqld restarted
070427 10:07:50 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
070427 10:07:53 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 227822203.
InnoDB: Doing recovery: scanned up to log sequence number 0 227822203
070427 10:07:53 InnoDB: Started; log sequence number 0 227822203
070427 10:07:54 [Note] /usr/sbin/mysqld: ready for connections.
This is the error that i get when i run test_setup.php:
Not all tables are ok!
Sorry, but there are error(s) found in the following table(s): 'records' 'domains'.
Please fix these errors and run the script again.
---
whereas rest of the tables (users, users_seq and zones)have been created.
Did i forget some step where these tables were to be created?
How can i create these tables now?
I apoligize in advance if this is the wrong place to post this thread.
I am trying to create a CNAME entry in my DNS tables and have no clue where to begin.
I have did a search but didnt find anything that gives reference on how to do it just how to fix errors.
Can anyone point me in the right direction to learning how to accomplish this task.
I have a database which is growing to have about 100 tables. These tables will grow in size and sometime I will need to use RAID 5, I am told by my server provider.
My questions are:-
1. if these other servers are mirrors, should I have the database stored on each server?
2. when one server gets too busy, does the RAID query a lesser-used server so as not to bog down the first one?
3. Or, do I need to have different content in each db on each server so a query gets what it needs form each?
I am using /usr/bin/mysqlcheck --all-databases --check --auto-repair to check all MySQL DBs every day via cron. But sometimes when the DB crash and is being repaired the tmpdir = in /etc/my.cnf is getting full 100%. Is it any smart way to use some other tmp folder when repairing the DB tables like that?
View 6 Replies View RelatedI am considering on implementing a new firewall in our colo which would have about 10 servers behind it which generates on averages 2.314 megabits/sec for everything.
I am looking at the new Watchguard x750e running version 10 of Fireware which seem like a good fit without breaking the bank but I have also thought of simply implementing a Poweredge server running CentOS and running an IPtables config to provide firewall services.
Anybody have any Feedback on the Watchguard unit or use a Watchguard product in their setup and can comment?
In phpMyAdmin I see this:
Created_tmp_disk_tables 2,118 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
Created_tmp_files 0 How many temporary files mysqld has created.
Created_tmp_tables 5,637 The number of in-memory temporary tables created automatically by the server while executing statements.
I have tried upping tmp_table_size but it's no use. My my.cnf file:
Quote:
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password= your_password
port= 3306
socket= /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port= 3306
socket= /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 384
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
tmp_table_size = 300M
max_tmp_tables=100
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id= 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin
# Point the following paths to different dedicated disks
#tmpdir= /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
This server has 1GB of RAM, not a dedicated sql server though. RAM usage is low too, 567MB of RAM is being used right now total... is there something I'm overlooking?
Is S2U-MSFC2 able to handle 2 full internet routing tables or even 3?
and what is the difference between
WS-X6408 and WS-X6408A?
WS-X6348-RJ-45 and WS-X6548-RJ45?
when fantastico applications get installed, for some reason they are creating the database, but none of the tables are installed.
View 6 Replies View RelatedHow to set up cron job auto empty a table of an database at specify time.
OS: Centos 5 64 bit , Cpanel: cPanel 11.24.4-R32486 - WHM 11.24.2 - X 3.9
I find it hard to configure IP tables for firewall, can I find already made scripts anywhere?
View 1 Replies View Relatedevery 4 or 5 days the lock table permission keeps getting revoked, does anyone have anything that can point me in the general direction of what would cause this? The only thing i can think of is a cpanel layer 2 update has occured a few times during hte periods where the permission is revoked
unfortunately whenever it happens it results in my SQL backup script failing
It's a VPS host running CentOS btw.
Window server 2012 r2: I installed wordpress on a domain using plesk. Seemed to work correctly. But when I went to the site www.example.com/wp-admin, the native or default wordpress install started up. I could find no way to bypass this, so I just re-entered all the same settings.
The result was that I ended up with 2 sets of database tables, one set named with my chosen prefix wp_blahblah_ and one set prefixed with wp_blahblah_wp_.
Not good, so I deleted the later set. Wordpress seems to be working, despite one error on the tables I can't reproduce.
i have the following server specs :-
Processor Intel Quad-Core Xeon X3323 2.5GHz/2x6MB 1066FSB
RAM 4GB 667MHz Dual Rank ECC Memory (4X1GB)
i have some sites vb forums / proxy sites / file hosting site / tube sites , all together some 10-12K visitors a day , i am using xcache , but still apache is crashing many times ,what should i do , i have asked platinum server management guys, they disable logs , suexec but still no luck, will increase of another 2gb of ram help me?
We are now online after The Planet crash but since this morning we have a strange problem...
All our servers are resolving, including our database server but the servers do not see the database server (Ip not resolving...)
Our computer to database server = Resolving
Our computer to servers = Resolving
Servers to database server = Not Resolving
The Apache service is getting crash and creaing following logs
[Mon Jul 07 23:52:21 2008] [notice] child pid 12878 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:21 2008] [notice] child pid 12881 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:21 2008] [notice] child pid 12886 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:22 2008] [notice] child pid 12859 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:23 2008] [notice] child pid 12904 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:23 2008] [notice] child pid 12909 exit signal Segmentation fault (11)
[Mon Jul 07 23:52:25 2008] [notice] child pid 12877 exit signal Segmentation fault (11)
Server Specification.
4GB RAM
Apache : 2.0.52
PHP : PHP 5.2.5
MySQL : 5.0.51
httpd.conf
KeepAlive Off
MaxKeepAliveRequests 50
KeepAliveTimeout 10
StartServers 16
MinSpareServers 8
MaxSpareServers 64
ServerLimit 1000
MaxClients 500
MaxRequestsPerChild 5000
ServerSignature On
my.cnf (Seprate box with 2GB RAM)
log-slow-queries = /var/log/mysql/mysql-slow.log
skip-external-locking
skip-locking
skip-innodb
skip-bdb
skip-name-resolve
max_connections = 800
max_user_connections = 800
key_buffer = 36M
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
thread_stack = 128K
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 999999
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 16M
old_passwords=1
user=mysql
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M
I have in my possession a new server which is running cPanel. For some unknown reason it keeps crashing about every 4-6 hours where I must get a remote reboot done.
Its starting to annoy me that I'm unable connect to anything. What I suspect is high disk red/write. As what I'm finding is over 10k blocks being written per second with only 300 being read per second. I also am not seeing any bandwidth out/in usage being high.
I have a dedicated server with NyNOC and earlier on today the server dissapeared off the net, wouldn't respond to ssh, http etc, nor pings
I submitted a ticket and the server got rebooted, went through the FSCK check and came back up ok, but now I'm concerned as to what caused it
Andy the technician said he couldn't find out the reason why and neither can I
I can only put it down to PERHAPS faulty hardware somewhere?
The /var/log/messages file doesn't say anything useful, the last entries show..
Quote:
May 27 06:41:17 server kernel: Firewall: *UDP_IN Blocked* IN=eth0 OUT= MAC=ff:ff:ff:ff:ff:ff:00:xxxxxxxx SRC=xxxxxxxx DST=255.255.255.255 LEN=153 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=UDP SPT=8778 DPT=11777 LEN=133
May 27 06:56:43 server kernel: Firewall: *UDP_IN Blocked* IN=eth0 OUT= MAC=ff:ff:ff:ff:ff:ff:00:xxxxxxxx SRC=xxxxxxxxx DST=255.255.255.255 LEN=153 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=UDP SPT=8778 DPT=11777 LEN=133
May 27 07:02:56 server kernel: Firewall: *UDP_IN Blocked* IN=eth0 OUT= MAC=00:14:d1:14:a7:c0:00xxxxxxxxx SRC=xxxxxxxxx DST=xxxxxxxx LEN=622 TOS=0x00 PREC=0x00 TTL=48 ID=0 DF PROTO=UDP SPT=54284 DPT=1027 LEN=602
May 27 07:03:13 server kernel: Firewall: *TCP_IN Blocked* IN=eth0 OUT= MAC=00:14:d1:14:a7:c0:xxxxxxx SRC=xxxxxxxx DST=xxxxxxxx LEN=48 TOS=0x00 PREC=0x00 TTL=117 ID=47647 PROTO=TCP SPT=21173 DPT=22 WINDOW=65535 RE$
May 27 13:01:45 server syslogd 1.4.1: restart.
May 27 13:01:45 server kernel: klogd 1.4.1, log source = /proc/kmsg started.
May 27 13:01:45 server kernel: Linux version 2.6.18-128.1.10.el5PAE (mockbuild@builder16.centos.org) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-44)) #1 SMP Thu May 7 11:14:31 EDT 2009
May 27 13:01:45 server kernel: BIOS-provided physical RAM map:
May 27 13:01:45 server kernel: BIOS-e820: 0000000000000000 - 000000000009fc00 (usable)
May 27 13:01:45 server kernel: BIOS-e820: 000000000009fc00 - 00000000000a0000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 00000000000e7000 - 0000000000100000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 0000000000100000 - 00000000affb0000 (usable)
May 27 13:01:45 server kernel: BIOS-e820: 00000000affb0000 - 00000000affc0000 (ACPI data)
May 27 13:01:45 server kernel: BIOS-e820: 00000000affc0000 - 00000000afff0000 (ACPI NVS)
May 27 13:01:45 server kernel: BIOS-e820: 00000000afff0000 - 00000000b0000000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 00000000fec00000 - 00000000fec01000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 00000000fee00000 - 00000000fef00000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 00000000fff80000 - 0000000100000000 (reserved)
May 27 13:01:45 server kernel: BIOS-e820: 0000000100000000 - 0000000140000000 (usable)
May 27 13:01:45 server kernel: 4224MB HIGHMEM available.
May 27 13:01:45 server kernel: 896MB LOWMEM available.
May 27 13:01:45 server kernel: found SMP MP-table at 000ff780
May 27 13:01:45 server kernel: Memory for crash kernel (0x0 to 0x0) notwithin permissible range
May 27 13:01:45 server kernel: disabling kdump
You can see it was down for some 6 hours, but I can't find a reason why
Diskspace is ample, there's no odd programs/scripts installed (it's a Centos server running whm with CSF firewall)
I reboot my cpanel server twice last night and it had crash
I am now running e2fsck and it's trying to fix the inode thing.. i've been pressing <y> 10000 times! Poor thing my fingers cramp
I've been thinking to mount my primary drive into another cpanel server (not a fresh server) as secondary drive and restore from there. The question is how? I need detailed steps by steps as it's urgent for me..
I just got a new server the other day, installed cpanel and all my usual stuff in it.
Then I started transferring some accounts off my other server on the same network. It would transfer small accounts ok but I have this one big account,. the sql is like 300 mb. It would get to the restore on it then the server would crash and be totally unresponsive.
It has to be manually rebooted at the datacenter. There is nothing in any logs. At first I thought it was the load getting hi and doing it but I ran the unix bench and got the load to 14 and it didnt hurt it.
It only does this on restoring big sql db. At the dc they say the screen on the console goes blank.
Ive done looked and cant find anything in the box. Its actually the same exact setup I use for my other servers.
So the dc is looking at it today. Has anyone ever ran into a problem like this and if so what was the cause?
No one could have missed that there was a DDoS attack on the DNS root servers last week. It’s been all over the news. It seems that aside from the G node (ironically the US Department of Defense) the rest of the nodes did ok (especially since anycast spreads out requests for 6 of the 13 root servers to over a 100 other locations).
In other words, the DNS root servers seem pretty impossible to take down (which is a good thing!)
But...
What would it take to bring down the DNS root servers? What weak links are there?
It would be interesting to have a discussion around this.
What could cause the following on one of our servers?
Rebooted and was fine.
May have happened before, will have to dig deeper into logs.
Running CestOS 4.4 (cPanel/WHM)
Assertion failure in __journal_file_buffer() at fs/jbd/transaction.c:1966: "jbd_is_locked_bh_state(bh)"
------------[ cut here ]------------
kernel BUG at fs/jbd/transaction.c:1966!
invalid operand: 0000 [#1]
SMP
Modules linked in: ipt_state ipt_TOS iptable_mangle ip_conntrack_ftp ip_conntrack_irc ip_conntrack ipt_REJECT ipt_LOG ipt_limit iptable_filter ipt_multiport ip_tables autofs4 sunrpc md5 ipv6 loop dm_mirror dm_mod button battery ac uhci_hcd ehci_hcd e100 mii ext3 jbd ata_piix libata sd_mod scsi_mod
CPU: 1
EIP: 0060:[<f8830dfc>] Not tainted VLI
EFLAGS: 00010216 (2.6.9-42.ELsmp)
EIP is at __journal_file_buffer+0x42/0x221 [jbd]
eax: 0000006b ebx: e5b6d3dc ecx: da83add0 edx: f88361ca
esi: f6da3300 edi: 00000001 ebp: 00000000 esp: da83adcc
ds: 007b es: 007b ss: 0068
Process pdflush (pid: 5216, threadinfo=da83a000 task=cb5ad2b0)
Stack: f88361ca f8835eac f88361b5 000007ae f8836634 e5b6db3c 00000000 dcca5660
e5b6d3dc f7638d68 f7f48200 f8830165 f7f482e4 00000000 00001000 f7638d68
dcca5660 00000000 f8898e42 00001000 00000000 dcca5660 f8898cda dcca5660
Call Trace:
[<f8830165>] journal_dirty_data+0x17d/0x1bc [jbd]
[<f8898e42>] ext3_journal_dirty_data+0xc/0x2a [ext3]
[<f8898cda>] walk_page_buffers+0x62/0x87 [ext3]
[<f889927a>] ext3_ordered_writepage+0xee/0x13a [ext3]
[<f889917a>] journal_dirty_data_fn+0x0/0x12 [ext3]
[<c0178962>] mpage_writepages+0x1c2/0x314
we have dual xeon linux server redhat 9 / cpanel
we have a strange crash .... you cant access ftp /ssh / httpd ...
i need to request a server reboot to get access ... and everything after this run ok...
i have check message log .. nothing in it for example
10:20:10 ftp log
11:15:60 rebootlog
also the same in httpd log ...
so as you see there is a gab between the server crash till reboot....
this happen 3 times till now ...