Re: MySQL server has gone away
Hi Aveek, I would like to suggest some points here: You could try increasing the max allowed packets to 128MB. Though you think 16MB is enough, increasing it is not going to affect the server. It is dynamic value, so you could revert back the changes if you feel so. Also make sure log_warnings=2 which will give more error messages in error log. This is the basic step for this error, if it is not working then we should check for other options then. Regards, Vinodh.k On Fri, May 27, 2011 at 11:05 AM, Claudio Nanni claudio.na...@gmail.comwrote: 'MySQL server has gone away' Can be a network problem, Just to increase complexity :) On May 26, 2011 11:03 PM, Prabhat Kumar aim.prab...@gmail.com wrote: I had experience with such type of error, It was due lack of resources available to MySql, max connections exceeds on the server. you can write a simple script which will grab and store output of 'show processlist' every min. and later you cna investigate the issue. On Wed, May 25, 2011 at 3:34 AM, Aveek Misra ave...@yahoo-inc.com wrote: Nothing in the error log or the slow query log that suggests that the query size is too large or us taking too much time to execute. Thanks Aveek On May 25, 2011, at 3:53 PM, Rik Wasmus wrote: failed to execute SELECT * FROM cluster_info WHERE cluster = ?: MySQL server has gone away The error MySQL server has gone away is the error from the db handle. Can anyone give me any pointers on why that happens? I looked up the documentation in MySQL docs and the most common reason seems to be that it happens if the query size is very large or if there is a timeout. None of them seems to be a probable cause. The max_allowed_packet on the server is 16 MB and as can be seen in the query above, the query is very small and nowhere near the size limit. We also have a timeout setting (wait_timeout) of 10 minutes and the above query for us cannot possibly take that amount of time. In any case, given the same query, it executes correctly 99% of time (so to speak). It fails intermittently with the above error. What possibly could be the reason? I also looked at the max connections on the server at that time (around ~80) and it is much less than the limit we have (limit is 1000). How can I extract more information when this happens? This error message sucks since it does not tell me what exactly happened. The server version is 5.1.45. Can you access the error log of the server? That can probably shed more light on the issue... -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: WHERE does not work on calculated view field
Hi Daniel,
Could you check the 'myview' once again? I think you thought to create the
view as follows:
CREATE VIEW `myview2` AS SELECT a.*, EMPTY_STRING(b.`Name`) AS
`TypeName`FROM `mytable` a LEFT JOIN `types` b ON *a.ID* *= b.`ID`*;
Now your select queries will give results as follows:
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NULL;
+--+
| COUNT(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Regards,
Vinodh.k
MySQL DBA
On Sat, Apr 23, 2011 at 1:50 AM, Daniel Kraft d...@domob.eu wrote:
Hi Carsten,
On 04/22/11 22:11, Carsten Pedersen wrote:
On 22.04.2011 21:37, Daniel Kraft wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Hint: What's the output of SELECT * FROM `myview`?
I get:
mysql select * from myview;
++--+--+
| ID | Type | TypeName |
++--+--+
| 1 | NULL | |
++--+--+
1 row in set (0.00 sec)
mysql select *, `TypeName` IS NOT NULL from myview;
++--+--++
| ID | Type | TypeName | `TypeName` IS NOT NULL |
++--+--++
| 1 | NULL | | 1 |
++--+--++
1 row in set (0.00 sec)
Should this tell me something? To me, it looks as expected and fine.
Cheers,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=daffodil...@gmail.com
Re: Duplicate key name when importing mysql dump file
Hi Jason, You may have to run ANALYZE TABLE.. for the particular table for which you are facing the error. So it'll rebuild the indexes. This would be the best one to save your data. We can use the method which Mr.Isart suggested, but it'll ignore the error and also will lead to data loss. Regards, Vinodh.k On Sat, Jun 20, 2009 at 12:19 AM, Isart Montane isart.mont...@gmail.comwrote: Hi Jason, if you run mysql with -f it will ignore any errors and continue importing cat aac.sql | mysql -f -u root AAC Isart On Wed, Jun 17, 2009 at 8:59 PM, Jason Novotny jason.novo...@gmail.com wrote: Hi, I'm trying to import a dumpfile like so: cat aac.sql | mysql -u root AAC It all runs fine until I get something like: ERROR 1061 (42000) at line 5671: Duplicate key name 'FK_mediaZip_to_zipSet' Is there a way I can tell it to ignore or replace the key? Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
data position changes when 'Load Data infile'......
Hi Friend, Today I was testing the command 'Load data infile ...' command ( http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html ) in my system. That time I was surprised when I put select statement in that table. The scenario as follows : In a text file which is to be loaded, I am having data as follows: 3 v,4 a mysql desc mytable; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | foo | char(1) | YES | | NULL|| +---+-+--+-+-++ 2 rows in set (0.00 sec) mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| ++--+ 2 rows in set (0.01 sec) mysql load data infile '/home/mysql/egdata' into table mytable lines terminated by ','; Query OK, 2 rows affected, 0 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| | 3 | v| | 4 | a| ++--+ 4 rows in set (0.00 sec) mysql delete from mytable where id in (3,4); Query OK, 2 rows affected (0.00 sec) mysql load data infile '/home/mysql/egdata' into table mytable lines terminated by ','; Query OK, 2 rows affected, 0 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from mytable; ++--+ | id | foo | ++--+ | 1 | a| | 2 | b| | 4 | a| | 3 | v| ++--+ 4 rows in set (0.00 sec) The select query gives the different orders for value '3' and '4' which was loaded twice with same txt file. Why is this happening like this? Any reason or algorithm involve in this?
