RE: backup/restore
Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname < dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms < c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR 1062 at line 155: Duplicate entry '56' for key 1 ERROR 1062 at line 156: Duplicate entry '57' for key 1 ERROR 1062 at line 157: Duplicate entry '58' for key 1 ERROR 1062 at line 158: Duplicate entry '59' for key 1 ERROR 1062 at line 159: Duplicate entry '75' for key 1 ERROR 1062 at line 160: Duplicate entry '76' for key 1 ERROR 1062 at line 161: Duplicate entry '77' for key 1 Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO > c:\progra~1\mitel\opsman~1\temp\almhist.dmp Please help me in this. Thanks, Narasimha -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:55 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: RE: backup/restore [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname < mysqldumpfile You may have to specify a user and password as well, depending on your setup. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. For backup : using Mysqldump -databases ---tables table1 table2 > dump.dmp For restore : used Mysqldump -databases < dump.dmp In the above, I am not able to restore the data. Please help us for a good solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: use of soundex in queries
Raphael Matthias Krug wrote:
Hi,
I need to compare names from different tables and therefore I need
to know the proper use of soundex. I googled for it, but could not find
anything useful. And select soundex('text') is no help for me.
FYI Soundex has real problems ... use DoubleMetaphone...
Kevin
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an
invite! Also see irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you
should work for Rojo! If you recommend someone and we hire them you'll
get a free iPod!
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/restore
I'm a beginner - but can't you also use mysqlimport? Respectfully, Ligaya Turmelle Tom Crimmins wrote: [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname < mysqldumpfile You may have to specify a user and password as well, depending on your setup. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. For backup : using Mysqldump -databases ---tables table1 table2 > dump.dmp For restore : used Mysqldump -databases < dump.dmp In the above, I am not able to restore the data. Please help us for a good solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
[snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname < mysqldumpfile You may have to specify a user and password as well, depending on your setup. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. For backup : using Mysqldump -databases ---tables table1 table2 > dump.dmp For restore : used Mysqldump -databases < dump.dmp In the above, I am not able to restore the data. Please help us for a good solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disorder resualt order by a Chine word field
Hi, If a select order by a field cotians Chinese GB code word, The query result is disorder . A GB code Chinese word are 2 char wich acsii >=128 and <=255 . The My.ini set Latin1 , else if set to GB,the resualt shows "?" . Best Regards, Shuming Wang
RE: Update a Field in a table to Uppercase
[snip] Does anyone have a sample of a simple update statement I can run to update a field in a table to all uppercase values. [/snip] UPDATE mytable SET mycol=UPPER(mycol); --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User can't login to his DB.
Hi list,
I use the following commands to create a user hubert access to his DB
only, but got the following error:
# perl view.pl
DBI connect('datacube','hubert',...) failed: Access denied for user
'hubert'@'localhost' (using password: YES) at view.pl line 8
Unable to connect: Access denied for user 'hubert'@'localhost' (using
password: YES)
SQL Grant commands:
GRANT SHOW DATABASES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
GRANT SHOW DATABASES ON *.* TO hubert@'10.%' IDENTIFIED BY 'password';
GRANT ALL ON hubertsdb.* TO hubert;
Perl DBI commands:
$database = "datacube";
$username = "hubert";
$pw = "password";
$dbh = DBI->connect("DBI:mysql:$database",$username,$pw);
die "Unable to connect: $DBI::errstr\n" unless (defined $dbh);
What is wrong with this error and how to fix it?
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
The inner join should ALWAYS return a faster result than the union, if you have the indexes correctly. Can you send me the explain of the inner join version and also the full table structure and indexes on the table? This should be fairly easy to solve. > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 02, 2005 4:45 PM > To: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > The inner join statement returned the same stuff but it was not as fast > as the union is and the inner join seems to use more cpu resources. > > Could these union queries really be the problem behind my occasional > lock ups and that 200 connections being used? i mean the server is not > that loaded and the http logs show amazeingly low http traffic the night > of the lock up. > > I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se > if that helps. > > When these lock ups occures it dont seems like mysql is freeing the > connections, > for exampel the last time it happend was around 02:00 a couple of days > ago and when i checked the server 14 hours later (16:00) it still said > that max_connections was full and mysqld was idling using 0% of the CPU, > if the queries was queued up would'nt mysql at least show some activity? > > // Fredrik > > Donny Simonton wrote: > > >Frederick, > >What exactly are you trying to accomplish? Personally, I don't recommend > >using union unless absolutely necessary, since most people don't really > >understand when it should be used. And I think it shouldn't be used in > this > >case either. > > > >Select A.id, A.parent, B.id, B.parent > >from art A inner join art B using (id) > >order by A.date; > > > >See if that gives you the same results as the original query and then > >explain it to see if you get anything differently. > > > >Also what is the table structure including indexes of the table? > > > >Donny > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql tablespace monitoring
First of all, thanks for the mailing list for giving details about myisamchk.we used them but we got some errors which I have reported in 'myisamchk errors' mail to mysql. At present we are working in mysql 3.23.32 ,windows xp os. Are there any system tables that store mysql related info.(like memory related issues etc). Are there any other ways to find memory used and free memory available by data tables,log files,temporary tables.We want the information so that we can use it in programs.Information available through commands could not be used in programming(For ex:myisamchk works with single table.we need to monitor entire datadirectory so we need to write programs.) Thanking you, yours sincerely, sirisha. PS:FIRST OF ALL ARE THERE ANY SYSTEM TABLES THOSE STORE THE ABOVE RELATED INFORMATION.IF NOT PLEASE MENTION IT. Yahoo! India Matrimony: Find your life partneronline.
Re: Max connections being used every 10-12 day.
The inner join statement returned the same stuff but it was not as fast as the union is and the inner join seems to use more cpu resources. Could these union queries really be the problem behind my occasional lock ups and that 200 connections being used? i mean the server is not that loaded and the http logs show amazeingly low http traffic the night of the lock up. I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se if that helps. When these lock ups occures it dont seems like mysql is freeing the connections, for exampel the last time it happend was around 02:00 a couple of days ago and when i checked the server 14 hours later (16:00) it still said that max_connections was full and mysqld was idling using 0% of the CPU, if the queries was queued up would'nt mysql at least show some activity? // Fredrik Donny Simonton wrote: Frederick, What exactly are you trying to accomplish? Personally, I don't recommend using union unless absolutely necessary, since most people don't really understand when it should be used. And I think it shouldn't be used in this case either. Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; See if that gives you the same results as the original query and then explain it to see if you get anything differently. Also what is the table structure including indexes of the table? Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
Frederick, What exactly are you trying to accomplish? Personally, I don't recommend using union unless absolutely necessary, since most people don't really understand when it should be used. And I think it shouldn't be used in this case either. Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; See if that gives you the same results as the original query and then explain it to see if you get anything differently. Also what is the table structure including indexes of the table? Donny > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 02, 2005 5:18 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > It is a single PIII 500MHz, so i just changed thread_concurrency to 2 > :), thanks > > The slow query log don't show that many slow queries, but they did show > alot of queries that was'nt using any index, can these queries cause > some kind of occasional lock up? > > Is there a faster way to perform this query? > (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION > (SELECT id,parent FROM art WHERE > parent=495098 ORDER BY date); > > explain show the following > > ++---+--+-+-+---+- > -+-+ > | table | type | possible_keys| key | key_len | ref > | rows | Extra | > ++---+--+-+-+---+- > -+-+ > | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const |1 > | | > | art | ref | parent | parent | 5 | const |2 > | Using where; Using filesort | > ++---+--+-+-+---+- > -+-+ > > // Fredrik Carlsson > > Donny Simonton wrote: > > >What kind of box is this? According to you're my.cnf it looks like it's > a > >either a dual with hyperthreading or a quad box. > > > >I don't see that you have your slow query log turned on, this should be > the > >first thing you should do in my opinion. This is what mine looks like. > > > >### Slow Query Information ### > >log-long-format > >log-slow-queries > >log-queries-not-using-indexes > >set-variable= long_query_time=3 > > > >Then go in and fix all of those that are showing up in the slow query > log. > > > >With 4-5 queries per second, you should NEVER fill up the 200 connections > >unless you just have some awful queries or you have some tables that are > >getting corrupted and are being repaired during that time. > > > >Donny > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master will insist on running as a SLAVE if data/master.info exists.
Kevin A. Burton wrote: Mikael Fridh wrote: Kevin A. Burton wrote: This is a bug. Feature. Putting system configuration information on a unix machine in /var.. .yeah... thats not a feature. It's a feature because you installed it there. I decided to put my datadir in /mysql/data which makes the configuration information NOT in /var, see? Even so, it's not 'configuration information', it's more like state information which mysql stores and constantly flushes to disk to make it as crash-safe as possible. Slave is always "started" unless my.cnf says "skip-slave-start". Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. Again... and this file is in /var... Whats the point of /etc/my.cnf... why not just store everything in /var? If you want you can have the datadir in /etc - it's your choice! Take care -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup/restore
Hi, I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. For backup : using Mysqldump -databases ---tables table1 table2 > dump.dmp For restore : used Mysqldump -databases < dump.dmp In the above, I am not able to restore the data. Please help us for a good solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
ISAM to MyISAM Convert pls
hello i hope im correct here i have a problem, after upgrade 4.1 from 4.0 one table is away (index) i have table.ISM,.frm and .ISD. when i try to convert it with ALTER TABLE table TYPE = MYISAM; he only says did not found table.MYI (where table is my name of the table) how i can convert it to MYI? before update i had MYI file with this table..the other tables are ok only this are now ISM,FRM and )ISD file whoich cannot be read my mysql now... thx richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
Fredrik Carlsson wrote: It is a single PIII 500MHz, so i just changed thread_concurrency to 2 :), thanks The slow query log don't show that many slow queries, but they did show alot of queries that was'nt using any index, can these queries cause some kind of occasional lock up? Is there a faster way to perform this query? (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION (SELECT id,parent FROM art WHERE parent=495098 ORDER BY date); explain show the following ++---+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | ++---+--+-+-+---+--+-+ | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const | 1 | | | art | ref | parent | parent | 5 | const | 2 | Using where; Using filesort | ++---+--+-+-+---+--+-+ Have you tried: SELECT id,parent FROM ((SELECT id,parent FROM art WHERE id=495098) UNION (SELECT id,parent FROM art WHERE parent=495098)) ORDER BY date; You could also try adding an index on (parent, id, date) to speed up you second query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
It is a single PIII 500MHz, so i just changed thread_concurrency to 2 :), thanks The slow query log don't show that many slow queries, but they did show alot of queries that was'nt using any index, can these queries cause some kind of occasional lock up? Is there a faster way to perform this query? (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION (SELECT id,parent FROM art WHERE parent=495098 ORDER BY date); explain show the following ++---+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | ++---+--+-+-+---+--+-+ | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const |1 | | | art | ref | parent | parent | 5 | const |2 | Using where; Using filesort | ++---+--+-+-+---+--+-+ // Fredrik Carlsson Donny Simonton wrote: What kind of box is this? According to you're my.cnf it looks like it's a either a dual with hyperthreading or a quad box. I don't see that you have your slow query log turned on, this should be the first thing you should do in my opinion. This is what mine looks like. ### Slow Query Information ### log-long-format log-slow-queries log-queries-not-using-indexes set-variable= long_query_time=3 Then go in and fix all of those that are showing up in the slow query log. With 4-5 queries per second, you should NEVER fill up the 200 connections unless you just have some awful queries or you have some tables that are getting corrupted and are being repaired during that time. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is there a utility like mysqlbinlog but instead processes the query log?
On 30 Dec 2004, at 13:26, Daniel Gaddis wrote:
is there a utility like mysqlbinlog but instead processes the query
log?
I would like to reprocess the queries from the query log.
I don't see another reply to this on the list, so I hope it helps - the
query log is already in plain-text, so you don't need something to fish
the queries out of an unfriendly format.
This bit of perl should be a good starting point.
elephant:/var/log/mysql# cat pullqueries.pl
#!/usr/bin/perl -w
use strict;
while (my $line = <>) {
if ($line =~ /Query/) {
my (undef, undef, undef, undef, $display) = split(/ /,
$line, 5);
print $display;
}
}
example :
elephant:/var/log/mysql# tail -n 20 mysql.log | perl pullqueries.pl
SELECT fname from images where groupid='4' order by viewno desc
limit 0,1
SELECT fname from images where groupid='3' order by viewno desc
limit 0,1
SELECT fname from images where groupid='2' order by viewno desc
limit 0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
SELECT id,dirname,fname,viewno,groupid FROM images where
id='10035' limit 0,1
UPDATE images set viewno='1',lastlook=NOW('') where id='10035'
SELECT entry,whoby FROM ucaptions where picid='10035'
SELECT dirname,fname,caption from images where id='10035' limit
0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
--
Regards, Andy Davidson
http://www.fotoserve.com/
Great quality prints from digital photos.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
