Re: MySQL server has gone away. Suddenly.
Hello everyone, This problem seems to be solved... The problem i'm experiencing is that at certain moments, the connection between my perlscripts and the database gets disconnected. [...] I thought the query at that spot would be wrong, but it turns out that this alone is not causing the problem. If i skip over the rest of the perlscript and jump to that line 672 immediately, it works like expected. It seems to turn out to diskspace problems. The partition that hosted /tmp, in which MySQL creates tempfiles during queries was rather full. But for some strange reason it didn't trigger MySQL to say 'Can't write to file /tmp/mysql.XXX: No space left on device.'. It did that only after the partition was REALLY full. Then I saw the 'No space left on device' message, and cleared up the partition. And now this problem has gone away. Really hard to debug this on a live system, but i thought i'd let you guys know... Thanks again for all the hints and help! Kind regards, Sander Smeenk. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
Quoting Critters ([EMAIL PROTECTED]): SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar hack It's the logic in the WHERE statement that makes the query return all rows. You should /never ever/ directly feed user input from websites to your database. Always use prepare() and execute() statements to feed the userdata, or use the proper quote() calls... Or explicitly state what characters you will allow and filter anything but those characters from the user supplied data. Kind regards, Sander. -- | Someone who thinks logically provides a nice contrast to the real world. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting sheeri kritzer ([EMAIL PROTECTED]): Do a show status like 'uptime' after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. The server itself does not crash. Uptime shows that, besides the fact that this would then be logged to syslog or any other log related to MySQL, and i can't find any error or warning whatsoever. show variables like %connections should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a too many connections error if that was the problem, but it couldn't hurt to check. max_connections == 100, max_user_connections = 0. This, to me, seems default. I haven't touched any setting in my.cnf for a long time. What's max_allowed_packet set to? max_allowed_packet == 16776192 Also default, for all i know. You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? In the script and on the commandline. The stats.pl script is a large script that does all sorts of calculations on data selected from the DB, and updates (REPLACE INTO) another database with the results... So in fact it's just a large script of queries. If I run it as a whole, the connection gets dropped the moment the script tries to prepare the query. If i jump over the other queries in stats.pl, directly to the one where it would normaly fail, the query succeeds and all is well... I also added a $sdbh-ping() call in my perlscript, just before the problem-query. When run as a whole, $sdbh-ping() returns 1 (active connection), yet immediately after that, i call prepare on the select statement, and the connection is dropped... What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Whoo. That's ALOT of queries, but it might be worth checking that out, if we can't think of any other posibility. Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Yes. Same machine via unix sockets. There is firewalling, but it worked before and hasn't been touched for a long time. That can't be the problem... Does anyone else have admin privileges to the database? Yes. But it's not being killed by anyone or any other script. Wouldn't that be logged too? If an admin kills a mysql connection? Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html We're using MyISAM tables. Thanks for your ideas, i hope we can find out what's going on! Kind regards, Sander. -- | [ $[$RANDOM % 6] = 0 ] rm -rf ~ || echo You win! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): What is happening is that when you start you perl script which contains a lot of queries, you are first establishing a connection to MySQL, and then you are running some queries, and somewhere in between you are losing connection to the server, Well, yes and no. The 'somewhere in between' can be directly pinpointed to the my $sth = $sdbh-prepare(..query..); call the perl script does. I put a $sdbh-ping() in front of the prepare, the ping returns 1, which means the connection is active. But still the connection drops when the prepare is made. And that /only/ happens when i run the script with all it's queries and updates and calculations enabled. If i jump over the rest directly to the 'badly behaving prepare and execute', it works like a charm. So I would suggest here that you make your Perl script auotmatic reconnect aware. Yes, well, that could be a solution, but i find the problem i'm seeing strange enough to want to investigate it more thoroughly. Especially since the problem only started appearing recently. After this what you really have to diagonize is why you are losing connection to MySQL initially (i.e. at line 98).. Yes. :) Since this is occuring only intermittently I am guessing if your max_connections limit was reached It's not as intermittently as you might think. I can reproduce it easily. I just run the complete stats.pl script. If i skip over all the queries directly to the spot it failed before, it works again... Sounds like a 'Query Quota' or something silly like that :)) at the time the connection was lost, what are your wait_timeout and max_connections settings, and are the initial queries very large... | connect_timeout | 5 | | delayed_insert_timeout | 300 | | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | sync_replication_timeout | 10| | table_lock_wait_timeout | 50| | wait_timeout | 28800 | max_connections == 100. But nothing about a timeout or too many connections is being logged. That's the strange part. There's no message anywhere. Thanks for all the ideas though!! Hope we can find out what's happening! Regards, Sander. -- | The problem with dancing naked: not everything stalls when the music stops. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server has gone away. Suddenly.
Hi,
I'm running MySQL 5.0.19 (Debian sid, package revision 3) on a Dual
Opteron250 64 bit machine with 4gb memory.
The problem i'm experiencing is that at certain moments, the connection
between my perlscripts and the database gets disconnected:
| DBI connect('products','beheerv',...) failed: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98
| Master.pm: unable to connect to products db: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98.
And:
| DBD::mysql::st execute failed: Lost connection to MySQL server during
| query at /var/web/cron/stats.pl line 673.
| DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
| /var/web/cron/stats.pl line 677.
I thought the query at that spot would be wrong, but it turns out that
this alone is not causing the problem. If i skip over the rest of the
perlscript and jump to that line 672 immediately, it works like
expected.
I'd like to hear how I can debug this. MySQL doesn't seem to log
ANYTHING about this issue. All I get is the messages above. I turned on
query logging, and it just shows the query that is executed at line 673
of stats.pl:
| SELECT theme, cat, subcat, p_ids,
| UNIX_TIMESTAMP(insert_time) as insert_time
| FROM log_product
| WHERE insert_time = FROM_UNIXTIME('1146348000')
| AND insert_time FROM_UNIXTIME('1146952800');
(this query, when redirected to a textfile returns about 25 megabytes of
data, in ~540784 rows)
After that no more queries from the same connection ID are logged.
But that is expected, as the connection was lost ;)
Can anyone shed any light on this issue?
Thanks!!
Sander.
--
| Honk if you love peace and quiet.
| 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): Can anyone shed any light on this issue? This might shed more light into your problem http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the You can also get these errors if you send a query to the server that is incorrect or too large. topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql db crashing again and again~
Quoting Mohammed Abdul Azeem ([EMAIL PROTECTED]): Then it hit a mysql bug with signal 6. The mysql db was throwing the following errors on the shell prompt ie the OS shell prompt. I had exactly the same behaviour on MySQL 5.0.19 running on AMD64. What helped in my case was re-creating all the indexes on all the tables. Since then this problem disappeared. But what I did was copy the binary myisam tables from a 32 bit MySQL4 database to a 64 bit MySQL5 database. Just an idea. Regards, Sander. -- | Eat well, stay fit, die anyway. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 28 from table handler
Quoting Jacques Brignon ([EMAIL PROTECTED]): 1030 : Got error 28 from table handler % perror 28 OS error code 28: No space left on device Any idea of what might cause that, and what should be done to correct this situation? Aparently the disk that stores your MySQL datadirectory is full. Either you or your hoster should clean it up ;) With regards, Sander. -- | Junk is something you've kept for years and throw away three weeks | before you need it. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.18 crashing on AMD64
Quoting Pete Harlan ([EMAIL PROTECTED]): MySQL version 5.0.18 | Mar 14 00:32:59 zwart mysqld[29820]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** You don't say which versions of glibc or the kernel you're running, but if you're running stock Debian Sarge, that's a problem because of its known-buggy glibc 2.3.2. Sorry. I forgot about that important information. The servers are running 2.6.15.x and Debian sid, updated to the newest libc6 (2.3.6) available. It does look like it was some problem with mysql's binaries on AMD64, since after upgrading to 5.0.19 it looks to be working again. It just apears weird to me that only this server would crash mysql :| The other AMD64 which has the same config, same kernel, same libc6, didn't crash. Though that mysql was only used for selects and not inserts updates and crazy select with joins, group by's etc. But what can the message indicate? All I got from Google was that it probably had to do with buggy memory. But i ran memtest86+ on the server and it had 17 passes with zero errors... :| HTH, Sander. -- | A balanced diet is a cookie in each hand. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.19-2 and repair problems...
Hello! I'm having a weird problem i'd like to hear your opinions about: | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Table './v/games' is marked as crashed and should be repaired | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck -r v games | v.gamesOK | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck -f -r v games | v.gamesOK | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # /etc/init.d/mysql stop | Stopping MySQL database server: mysqld. | [18:45] [EMAIL PROTECTED]:~] # cd /var/lib/mysql/v | [18:45] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk -r games.MYI | - recovering (with sort) MyISAM-table 'games.MYI' | Data records: 10644 | - Fixing index 1 | - Fixing index 2 | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # echo REPAIR TABLE games | mysql v | Table Op Msg_typeMsg_text | v.games repair status OK | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | myisamchk: error: Key in wrong position at page 108544 | - check record links | myisamchk: error: Keypointers and record positions doesn't match | MyISAM-table 'games.MYI' is corrupted | Fix it using switch -r or -o | [1]17564 exit 255 myisamchk games.MYI No matter WHAT i try, fixing with 'repair table' using mysqlcheck, myisamchk, -o, -r, -f, it seems like i can't repair this table. So what's going on? Does anyone have any clue? Regards, Sander. -- | Honk if you love peace and quiet. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Quoting Kishore Jalleda ([EMAIL PROTECTED]): That usually means the table is corrupt beyond repair and nothing is really fixing it or there is something one cannot easily comprehend ( this usually happens with inconsistency among deleted records and some kind of mismatch that occurs) -anyway what I would really advice in this case is to rebuild the table from a working/clean backup and start-over, if this is a slave then thats very easy to do, if not it depends on your latest clean backup available .. Amazing. Amazing that even the tools can't tell me it's unfixable. I mean, i tried everything. :) I'll restore the databases from the master (this isn't a slave, but it's a machine i want to switch to when it finally becomes stable...) and see where we get from that... The machine didn't crash. Nor did mysql. Any clue what might cause this to happen? Disk looks fine too, no read or write errors whatsoever... Thanks, Sander. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Quoting [EMAIL PROTECTED] ([EMAIL PROTECTED]): Have you tried the following myisamchk option: --extend-check, -e Yup. But that won't even work at ALL: # myisamchk -e games.MYI Checking MyISAM file: games.MYI Data records: 10644 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 myisamchk: error: Key in wrong position at page 715776 - check records and index references myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'games.MYI' is corrupted Fix it using switch -r or -o Strange, huh! -- | Don't hate yourself in the morning -- sleep till noon. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Quoting sheeri kritzer ([EMAIL PROTECTED]): what's the result of SHOW CREATE TABLE games It gives me the create statement for that table. All perfectly normal. MyISAM table, latin1 charset. I'd drop the indexes on the games table, and then run repair tables, and then put the indexes back in. Worth a shot. Funny, i haven't tried that yet, and lo-and-behold, after rebuilding the index (that's all i did): | [22:27] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | - check record links Aparently, what i heard from a friend, it's not done to binary copy tables from 32bit systems over to 64bit systems. Is it as bad to binary copy tables from 64bit to 32bit systems? It's vital in our current setup that binary copy of tables works. Or we need to set up replication right now ;) Lemmeknow and Thanks!! Sander. -- | Lead me not into temptation... I can find it myself. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Quoting sheeri kritzer ([EMAIL PROTECTED]): According to the manual, 64 to 32 bit shouldn't matter, it should work fine as long as they're both little endian, or they're both big endian. This friend of mine said he also had major problems moving 32-64. Crashing mysql's, but not corrupt tables. Well, i had crashing mysql's earlier (see my other post on this list about MySQL on AMD64) but that seems over now. Now i have corrupt tables. Or, better stated, all tables with indexes on them are corrupt. I've copied MyISAM tables from 64 to 32 bit without a problem. Great to hear. Have to try it out myself to be sure though, but it's good to hear you have no problems doing that. BTW, I suggested that because in the error it said: myisamchk: error: Key in wrong position at page 715776 - check records and index references Did you by any chance symlink or move files, or not move the key files? My guess is that might cause that problem, otherwise, I have no idea what would cause it. Nope. What i did is, in psuedocode: for database in datadir/* for table in datadir/database/* lock table flush table binary copy table* to tempdir unlock table } } What seems like a normal binary copy to me. All three files were copied, the .frm, the .MYI and the .MYD. Regards, Sander. -- | 't Gaat om 't spel, niet om de knikkers! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.18 crashing on AMD64
Hello! I have a dual Opteron 250 system with 4GB memory running Debian with MySQL version 5.0.18 and unfortunately it keeps crashing at (somewhat) random intervals with messages like: | Mar 14 00:32:59 zwart mysqld[29820]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** | Mar 15 02:24:17 zwart mysqld[13255]: *** glibc detected *** free(): | invalid next size (normal): 0x012d3d30 *** | Mar 16 00:32:51 zwart mysqld[17749]: *** glibc detected *** double | free or corruption (!prev): 0x01333540 *** | Mar 16 14:44:07 zwart mysqld[471]: *** glibc detected *** double | free or corruption (!prev): 0x013e4160 *** All followed by the message: | Mar 16 14:44:07 zwart mysqld[471]: mysqld got signal 6; Which causes the server to stop responding to any query done. It's still running, and i can still connect to it, but anything from select statements to showing tables makes the session hang indefinitely. Eventually causing mysqld to refuse connections, and the only solution is to kill -9 the mysqld process which leaves corrupt tables... I have another AMD64 running on somewhat the same setup, same kernel, same mysql version, same processors, only less memory (2GB). Is this problem caused by bad memory? And if so, why does only mysql suffer from it? I have perl processes running that take about the same amount of memory as mysql does but yet only mysql keeps crashing. Any hints are welcome! Thanks in advance! Kind regards, Sander Smeenk. -- | For security, this message has been encrypted with ROT13 twice. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
