Re: mysqldump error 1064 for database Use command
Dear Nitin, I have newly installed mysql on this server. mysql> Select version(); +-+ | version() | +-+ | 5.1.22-rc-Debian_2~ppa5-log | +-+ Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. --Regards, Manasi Save On Sat, 4 Sep 2010 21:00:22 -0700 (PDT), Nitin Mehta wrote: Hi, Have you recently upgraded your MySQL installation? 1064 in earlier days used to mean use of reserved word. Few clients have reported this error in last couple of months after they upgraded from 5.1.41 to higher versions. As of now, I don't have a solution other than changing the name (of database in your case). Hope that helps. Regards, Nitin From: Manasi Save To: "mysql@lists.mysql.com" Sent: Sat, September 4, 2010 12:06:27 PMSubject: mysqldump error 1064 for database Use commandHi All, I have 10 mysql databases all the tables use MyIsAm mysql storage engine. Database names are 1,2,3,4,...10. When I use mysqldump command with --all-databases option. This gives me following error: mysqldump -u myuser -p --all-databases > AllNew_Databases_20100904.sql ERROR MESSAGE :- mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064) Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error. Any input will be a great help. Thanks in advance.--Regards,Manasi Save
Re: Performance problems on MySQL
At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
Hi Johnny,
mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
++-++---+---+-+-+---+--+---+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
++-++---+---+-+-+---+--+---+
| 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23
| const |1 | |
++-++---+---+-+-+---+--+---+
1 row in set (0.53 sec)
Thanks
BR
AJ
Alexandre,
Do you have UserId declared as CHAR? It looks numeric to me. If it is
stored as an integer then don't use the ' ' in the select statement
otherwise it needs to convert it.
If UserId values are integers and you have the column defined as CHAR, then
declare the column UserId as integer or BigInt and the searches should be
faster than searching on CHAR.
Mike
On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers wrote:
> What about an explain of this query:
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> -JW
>
>
> On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira wrote:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> mysql> SHOW CREATE TABLE clientinfo;
>>
>>
++--+
>> | Table | Create
>> Table
>> |
>>
>>
++--+
>> | clientinfo | CREATE TABLE `clientinfo` (
>> `userid` varchar(21) NOT NULL default '',
>> `units` float default NULL,
>> `date_last_query` datetime default NULL,
>> `last_acc` int(10) unsigned default NULL,
>> `date_last_units` datetime default NULL,
>> `notification` int(10) unsigned NOT NULL default '0',
>> `package` char(1) default NULL,
>> `user_type` varchar(5) default NULL,
>> PRIMARY KEY (`userid`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>>
>>
++--+
>> 1 row in set (0.00 sec)
>> mysql> SHOW INDEX FROM clientinfo;
>>
>>
+++--+--+-+---+-+--++--++-+
>> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
>>
>>
+++--+--+-+---+-+--++--++-+
>> | clientinfo | 0 | PRIMARY |1 | userid |
>> A | 460056 | NULL | NULL | |
BTREE | |
>>
>>
+++--+--+-+---+-+--++--++-+
>> 1 row in set (0.00 sec)
>>
>>
>> SELECT * FROM clientinfo WHERE userid='182106617';
>>
>> UPDATE clientinfo SET
>>
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>> WHERE userid='152633876';
>>
>> INSERT INTO clientinfo VALUES
>> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
>> notification=0, package='D', user_type='PRE';
>>
>> DELETE FROM clientinfo WHERE units='155618918';
>>
>> There are no other type of queries.
>>
>> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>>
>> We also run some other applications in the server, but nothing that
>> consumes all the CPU/Memory. The machine has almost 1GB of free memory and
>> 50% of idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>>
>> --
>> Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
On 9/3/2010 3:15 PM, Johnny Withers wrote: It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of the data: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html That explains why there is no length to this index. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Logs not working
Thansk, Will try to turn on log_warnings. -Mon From: Nitin Mehta To: monloi perez ; Ananda Kumar Cc: mysql mailing list Sent: Sun, September 5, 2010 12:03:45 PM Subject: Re: Logs not working I believe that will not be logged unless you have enabled log_warnings. Too many connections would normally mean that the number is going beyond the configured limit and denying new sessions is an expected behavior and should not be an error really. Regards, Nitin From: monloi perez To: Ananda Kumar Cc: mysql mailing list Sent: Sat, September 4, 2010 11:37:42 AM Subject: Re: Logs not working on the db server? meaning the mysqld log right? THere is really no data for teh current error. The last error they said was too much connections and shouldnt that be logged in mysqld.log? Thanks, Mon From: Ananda Kumar To: monloi perez Cc: mysql mailing list Sent: Thu, September 2, 2010 6:14:26 PM Subject: Re: Logs not working Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez wrote: All, > >I'm not sure if this is the right mailing list since the specific mailing lists >doesn't seem to meet my concern. > >For some reason mysql client stops logging to mysqd.log. We had an issue on Aug >20. But when I checked the log, the latest was on Aug 2. >Any idea on how to resolve this or what caused it? > >Thanks, >Mon > > > >
