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 wrote: > 'MySQL server has gone away' > Can be a network problem, > Just to increase complexity :) > On May 26, 2011 11:03 PM, "Prabhat Kumar" 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 > 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 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 wrote: > 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 >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?
