Re: settingup mysql in suse linux
Hi.
See:
http://dev.mysql.com/doc/mysql/en/Access_denied.html
vt sharravanan [EMAIL PROTECTED] wrote:
[-- text/plain, encoding 7bit, charset: us-ascii, 6 lines --]
i have suse linux in my system and iinstalled mysql through rpms
(server,client,devel,shared,max). iam having problem in setting it, as said
in manual i created a group mysql, user mysql and used mysqld_safe to
start the deamon, then i used mysqldadmin -u root password('password') to set
the password for root it worked, but when i tried to create, or connect to
database or when i used mysqltest i get an error failed connecting to mysql,
access denied to [EMAIL PROTECTED] (using password No) what is the problem.
-
Do you Yahoo!?
Check out the new Yahoo! Front Page. www.yahoo.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Upgrade for Cpanel?
Hi. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html I read on the Cpanel.net forum that you can start the 4.0.22 mysql with a --new switch to use it as 4.1.7 so you can see if its compatible with my software. Is this true and exactly how? Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by in different languages..
Hi. May be it will be helpful: http://dev.mysql.com/doc/mysql/en/Charset.html For commercial support go to: https://order.mysql.com/?ref=ensita Alaios [EMAIL PROTECTED] wrote: Hi.. Listen to a peculiar problem. We need to order by a column which includes Greek language. The order by doesnot work correctly. The reason may be the administration's installation. So we need a nice trick for doing our job as fast as we can without interacting with the admin. Nice..eh? You can propose different implementations as adding new columns or changing the query... So? Any suggestion? Thx a lot have a nice day __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables Crash when I Delete A Row
Hi. From which version did you upgrade? If you upgraded from 4.0 you should carefully read: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html May be REPAIR with USE_FRM will be helpful. Some times after ugrade tables have to be rebuilt or repaired. Aaron [EMAIL PROTECTED] wrote: [-- text/plain, encoding quoted-printable, charset: iso-8859-1, 122 lines --] Hi all , I am having some issues with 4.1.7 on Redhat 9 Kernel 2.4.20-8SMP The other day I upgraded to RH9 and then put on: mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 Since then , it seems that one of (not all!) our tables has taken to crashing when you delete records from it. It spits out the following error: Incorrect key file for table 'Offers_To_Sell'; try to repair it Ok , well If I do a CHECK TABLE , I get this: mysql CHECK TABLE Offers_To_Sell ; +-+---+--+-+ | Table | Op| Msg_type | Msg_text| +-+---+--+-+ | Offers_To_Sell | check | warning | Table is marked as crashed | | Offers_To_Sell | check | error| Found 265847 keys of 265850 | | Offers_To_Sell | check | error| Corrupt | +-+---+--+-+ 3 rows in set (2.80 sec) Now , if I try to REPAIR , I get this: mysql REPAIR TABLE Offers_To_Sell ; +-++--+-+ | Table | Op | Msg_type | Msg_text | +-++--+-+ | worldbid.Offers_To_Sell | repair | error| 2 when fixing table | | worldbid.Offers_To_Sell | repair | error| Can't copy datafile-header to tempfile, error 9 | | worldbid.Offers_To_Sell | repair | status | Operation failed | +-++--+-+ 3 rows in set (2 min 5.49 sec) If I shell out and use myisamchk --quick , I get this: myisamchk --quick --tmpdir=/var/tmp Offers_To_Sell Checking MyISAM file: Offers_To_Sell Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: warning: Size of indexfile is: 207900672 Should be: 19564544 myisamchk: warning: Size of datafile is: 200488316 Should be: 200488292 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 265850 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 265850 Should be: 0 myisamchk: warning: Found 265850 partsShould be: 1 parts MyISAM-table 'Offers_To_Sell' is corrupted Fix it using switch -r or -o Then when I do a -r I get this: myisamchk -r --verbose Offers_To_Sell.MYI - recovering (with sort) MyISAM-table 'Offers_To_Sell.MYI' Data records: 47344 - Fixing index 1 - Searching for keys, allocating buffer for 174743 keys - Dumping 47344 keys - Fixing index 2 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 3 - Searching for keys, allocating buffer for 19239 keys - Last merge and dumping keys - Fixing index 4 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 5 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 6 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 7 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 8 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 9 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 10 - Searching for keys, allocating buffer for 45574 keys myisamchk: error: 22 when fixing table MyISAM-table 'Offers_To_Sell.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag Then, I can fix the problem by using the --safe-recover option , but as soon as a delete is done on the table , it corrupts again. Anyone have any ideas? Tanks, Aaron -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
Sequencial Replication
Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld segfaults.
Hi, I have problem with my mysqld, when its receiving alot of connections and at the same time doing insert/delete/update/select on the same table mysqld segfaults and restarts 041108 0:59:08 Warning: Got signal 14 from thread 162602 Segmentation fault 041708 13:17:54 -e \nNumber of processes running now: 0 041708 13:17:54 mysqld restarted 041108 13:17:55 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.21' socket: '/tmp/mysql.sock' port: 3306 Source distribution 041108 13:19:06 Warning: Got signal 14 from thread 2 I start mysqld with these flags: --key_buffer_size=130M --table_cache=256 --sort_buffer_size=16M --read_buffer_size=4M --query_cache_size=64M ulimit -n 8096 ulimit -l 80 ulimit -d 80 ulimit -m 80 ulimit -p 9000 ulimit -s 60 The table has about 450k rows and the size with indexes is ~1.4GB Running NetBSD 1.6.2 and mysql 4.0.21 What can cause these segfaults? and is there anything i can tune to get rid of them? // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load index into cache not working
I posted a detailed description of a similar problem one week ago to general and then later to internals with no response to either (title load index into cache doesn't?). Using load index into cache to preload a 1GB index into a 2GB cache comes back in 0.0 seconds as 'done'. But there is no disk IO (it should take 10-30 seconds to load 1GB from disk), and no impact in query speed. As Mark says - isn't that the point of preloading? Also if I could 'me too' on getting individual indexes loaded. For big tables with multiple indexes where it's only possible to fit one or two in the cache, having to load the entire set of indexes really kills the usefulness of this otherwise brilliant feature. I've resorted to creating a HEAP (Btree) 'summary' table with only the indexes I need in the meantime. Best, Andrew. --- Mark Maunder [EMAIL PROTECTED] wrote: This bug is a problem with the reporting when sending a SIGHUP or the command mysqladmin debug. What I'm seeing is the process simply isn't growing in memory. I'm looking at the process size in 'top'. I do notice that it grows once I start hitting it with queries. I'd expect it to grow as soon as I preload the index. Isn't that the point of preloading? On Fri, 2004-11-05 at 05:25, Gleb Paharenko wrote: Hi. There is a bug: http://bugs.mysql.com/bug.php?id=4285. Mark Maunder [EMAIL PROTECTED] wrote: I have a large fulltext index (the MYI file is about 750 Megs) and I've set my key_buffer_size to 1 Gig. I do: load index into cache fttest; and I watch the Mysql process in memory, and it doesn't grow. It just hangs around 250Megs. Why isn't the index loading into memory? Thanks, Mark. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- Mark D. Maunder [EMAIL PROTECTED] http://www.workzoo.com/ The Best jobs from the Best Job Sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem combining inner and right joins
Hi there, For bother mysql 4.1.7 and 4.0.18 on windows. I had the following behavior when combining inner joins and right joins: -- (1) I create a table A and insert some values in it CREATE TABLE A ( Id smallint, Name varchar(20) ) ; INSERT INTO A VALUES (1,'Hello'); INSERT INTO A VALUES (2,'Good bye'); INSERT INTO A VALUES (3,'Foo'); INSERT INTO A VALUES (4,'Good Morning'); -- (2) I create another table B CREATE TABLE B ( ID smallint ) ; -- (3) Naturally, a right join succeeds returning all 4 records in A SELECT A.Id,A.Name FROM B RIGHT JOIN A ON A.Id=B.ID; -- (4) I create another table C (Note that both B and C are empty) CREATE TABLE C ( ID smallint ) ; -- (5) I inner join B and C and then right join the result with A SELECT A.Id,A.Name FROM (B INNER JOIN C ON C.Id=B.Id) RIGHT JOIN A ON A.Id=B.Id; -- PROBLEM: the result is an empty set though I should get -- the 4 records in A -- (6) Yet more weird is that I insert a value in B which -- should not change the result of inner joining B and C -- but INSERT INTO B VALUES (17); SELECT A.Id,A.Name FROM B INNER JOIN C ON C.Id=B.Id RIGHT JOIN A ON A.Id=B.Id; -- PROBLEM: the result is the 4 records !! Thanks for help Sameh
MySQL replication
Good morning, Been reading through the docs and checking online info and I m still looking for a answer. I have a radius DB on two radius servers and I want to sync them via a master server. Seems easy enough, but I have one table which holds accounting data. How long a user has been online, when they logged on, when they logged off. This data is sent to the slave servers by the auth equipment. Is it possible to only replicate a *table* to a slave and not the entire DB? Thanks, DAve -- Systems Administrator http://www.tls.net Get rid of Unwanted Emails...get TLS Spam Blocker! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL replication
Hello. See: http://dev.mysql.com/doc/mysql/en/Replication_Options.html Dave Goodrich [EMAIL PROTECTED] wrote: Good morning, Been reading through the docs and checking online info and I m still looking for a answer. I have a radius DB on two radius servers and I want to sync them via a master server. Seems easy enough, but I have one table which holds accounting data. How long a user has been online, when they logged on, when they logged off. This data is sent to the slave servers by the auth equipment. Is it possible to only replicate a *table* to a slave and not the entire DB? Thanks, DAve -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sequencial Replication
Hello. MySQL slave receives all updates, but executes them in queue. More about implementation of replication you can read at: http://dev.mysql.com/doc/mysql/en/Replication_Implementation_Details.html Arvind Gangal [EMAIL PROTECTED] wrote: Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem connecting to MySQL server
Hey there I have upgraded the MySQL server on my Windows machine to 4.0.22 following the docs at: http://dev.mysql.com/doc/mysql/en/Windows_upgrading.html When I start the server using NET START MySQL the server starts with no problems and the following is written to my error log: 041108 14:24:28 InnoDB: Started MySQL: ready for connections. Version: '4.0.22-debug' socket: '' port: 3306 Source distribution When I stop the server using NET STOP MySQL the server stops without any problems and the following is written to the log: 041108 14:25:43 MySQL: Normal shutdown 041108 14:25:43 InnoDB: Starting shutdown... 041108 14:25:46 InnoDB: Shutdown completed 041108 14:25:46 MySQL: Shutdown Complete Now, here is the problem. When I have started the MySQL server and it is running I then go to c:\mysql\bin\ and execute the command: mysql The following message is then returned: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Any ideas why this is happening? Any help on pointers to where I may find more answers will be appreciated. Thanks! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sequencial Replication
Gleb, He was wondering if there were any plans to provide multiple slave SQL threads so that the slaves can process more efficiently NOT if the slave process is multi-threaded. He understood that there would be challenges to multithreading the slave's query processor (including reader query synchronization and multiple read locations in the binlog). In his question he also said that he understood that the slaves processed their binlogs in sequential order. Your short RTFM answer provided no new information. Can you answer his question or not? May I suggest that you begin to thoroughly read each question before you fire off a response. I know you are new to the list but some of your very short answers don't address the questions very well. Sometimes it seems as though you are picking up on a keyword or keyphrase and referring people to the manual too quickly. However, if you read the questions more carefully you will see that they are looking for a different answer (Specifically, I can think of a person this weekend wanting to know how to write a query that determined the most recent record in a set of records that share an ID value. You referred them to the manual page about TIMESTAMP columns. I believe that if you had read their post more carefully, you could have either sent them to the location eventually recommended by another list member, or provided a sample query on your own.) Please be more careful how you respond. Many of the people that come to this list have ZERO database background and they sometimes need more of an explanation that just what's in the book. Sometimes it's our jobs as list members to figure out what their real question is and not just key off of what they ask as sometimes they don't even know how to phrase the right question, it's all just too new. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gleb Paharenko [EMAIL PROTECTED] wrote on 11/08/2004 08:02:04 AM: Hello. MySQL slave receives all updates, but executes them in queue. More about implementation of replication you can read at: http://dev.mysql.com/doc/mysql/en/Replication_Implementation_Details.html Arvind Gangal [EMAIL PROTECTED] wrote: Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem retreiving data (mysql++1.7.17)
Hello;
I have a mysql++ question... I'm not sure if this is right list to post to.
Well, I just upgraded mysql++ from version 1.7.9 to 1.7.17.
Now, my problem is I don't seem to be getting the collect data from a table.
I have an order table with the following cols: LastName, FirstName,
OrderDate.
Initially to get the data after storing the Result from a select to rs I
would do something like this...
if (rs.begin() != rs.end())
{
row = *(rs.begin());
1. Glib::ustring lastname = row[LastName];
2. Glib::ustring lastname = row[FirstName];
3. Glib::Date orderdate ((Glib::ustring) row[OrderDate]);
}
Then, this data is displayed on an UI...
line 1 and 2 displays an 'a'
That means in all the three cols after the upgrade I'm getting bad
data... and the compliler is complaining about line 3 as well.
Here is the snip of my makefile.am
CXX = ccache g++
INCLUDES =\
$(GTKMM_CFLAGS)\
-I/usr/include/libglademm-2.0 \
-I/usr/include/sqlplus \
-I/usr/local/include/mysql \
-I/usr/include/cc++2 \
-I/usr/include/pcre \
-I./orders
CXXFLAGS =\
-Wno-deprecated\
-Wall -Wimplicit -Wunused -Wmissing-prototypes\
-g -O0
...
order_LDADD = \
$(GTKMM_LIBS)\
-lglademm-2.0 \
-lxml2 \
-lmysqlclient \
-lmysqlpp
...
...
Any help and/ or suggestion is highly appreciated
kinyash
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables Crash when I Delete A Row
Hi Gleb , thanks for the answer. Thats exactly what the problem was. I needed to rebuild the fulltext indexes on my tables. After I did that , the problem seems to have magically disappeared. :) Cheers, Aaron - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 07, 2004 5:23 AM Subject: Re: Tables Crash when I Delete A Row Hi. From which version did you upgrade? If you upgraded from 4.0 you should carefully read: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html May be REPAIR with USE_FRM will be helpful. Some times after ugrade tables have to be rebuilt or repaired. Aaron [EMAIL PROTECTED] wrote: [-- text/plain, encoding quoted-printable, charset: iso-8859-1, 122 lines --] Hi all , I am having some issues with 4.1.7 on Redhat 9 Kernel 2.4.20-8SMP The other day I upgraded to RH9 and then put on: mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 Since then , it seems that one of (not all!) our tables has taken to crashing when you delete records from it. It spits out the following error: Incorrect key file for table 'Offers_To_Sell'; try to repair it Ok , well If I do a CHECK TABLE , I get this: mysql CHECK TABLE Offers_To_Sell ; +-+---+--+-+ | Table | Op| Msg_type | Msg_text | +-+---+--+-+ | Offers_To_Sell | check | warning | Table is marked as crashed | | Offers_To_Sell | check | error| Found 265847 keys of 265850 | | Offers_To_Sell | check | error| Corrupt | +-+---+--+-+ 3 rows in set (2.80 sec) Now , if I try to REPAIR , I get this: mysql REPAIR TABLE Offers_To_Sell ; +-++--+-+ | Table | Op | Msg_type | Msg_text | +-++--+-+ | worldbid.Offers_To_Sell | repair | error| 2 when fixing table | | worldbid.Offers_To_Sell | repair | error| Can't copy datafile-header to tempfile, error 9 | | worldbid.Offers_To_Sell | repair | status | Operation failed | +-++--+-+ 3 rows in set (2 min 5.49 sec) If I shell out and use myisamchk --quick , I get this: myisamchk --quick --tmpdir=/var/tmp Offers_To_Sell Checking MyISAM file: Offers_To_Sell Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: warning: Size of indexfile is: 207900672 Should be: 19564544 myisamchk: warning: Size of datafile is: 200488316 Should be: 200488292 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 265850 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 265850 Should be: 0 myisamchk: warning: Found 265850 partsShould be: 1 parts MyISAM-table 'Offers_To_Sell' is corrupted Fix it using switch -r or -o Then when I do a -r I get this: myisamchk -r --verbose Offers_To_Sell.MYI - recovering (with sort) MyISAM-table 'Offers_To_Sell.MYI' Data records: 47344 - Fixing index 1 - Searching for keys, allocating buffer for 174743 keys - Dumping 47344 keys - Fixing index 2 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 3 - Searching for keys, allocating buffer for 19239 keys - Last merge and dumping keys - Fixing index 4 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 5 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 6 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 7 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 8 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 9 - Searching for keys, allocating buffer for 47345 keys - Dumping 47344 keys - Fixing index 10 - Searching for keys, allocating buffer for 45574 keys myisamchk: error: 22 when fixing table MyISAM-table 'Offers_To_Sell.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag Then, I can fix the problem by using the --safe-recover option , but as soon as a delete is done on the table , it corrupts again. Anyone have any ideas? Tanks, Aaron -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/
Fast method needed to determine if a table is corrupt
Hi, I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried CHECK TABLE tablename FAST QUICK on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim
Re: Fast method needed to determine if a table is corrupt
In the last episode (Nov 08), Tim Murtaugh said: I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried CHECK TABLE tablename FAST QUICK on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? Make your tables smaller? :) You have to check each record to see that it's okay. If your tables are big, you have to spend time reading them. The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? If mysql tries to read a record or index and can't, it sets this flag to keep you from accessing the table until you repair it. You may be better off using InnoDB tables and taking the space/performance hit. InnoDB uses a logfile to allow it to roll back partially-commited transactions after a crash, so you never have to check or repair your tables. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast method needed to determine if a table is corrupt
Tim Murtaugh wrote: Hi, I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. You need to provide a way to have an orderly shutdown on power loss. You are trying to treat the symptom, not the problem. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried CHECK TABLE tablename FAST QUICK on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 serious problems
Heikki Tuuri wrote: Ugo, - Original Message - From: Ugo Bellavance [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 05, 2004 3:42 PM Subject: Re: 4.1.7 serious problems Gleb Paharenko wrote: Hi. There were several posts in list like yours. Do you use InnoDB tables? Try to increase values of key_buffer_size, read_buffer_size and so on. InnoDB is enabled but no InnoDB table is used yet (coming soon). However, it crashes with only 1 client connected. There is still plenty of free memory. if mysqld crashes that easily, then you may have a hardware fault, or the OS version in that computer is buggy. I tested the memory and it seems ok. I doubt this is an hardware issue, since version 4.1.3 works perfectly. Thanks, You can try running memtestx86 or memburn: http://v.iki.fi/~vherva/memburn.c You can also try upgrading the kernel. Thanks, Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SLOW query
Help: (and apologies if this is posted to the wrong list..)(pls let me know
where to post if so.. Thx ;-)
I have *inherited* an App that uses PHP / MySQL. THe internal search function
within the application
that I am supporting uses the following DB Table structure and runs
the Query below to return a set of results ordered by the REGEXP match results
(The score).
The Query looks in each field, for a REGEXP match, if the REGEXP returns true,
the score is incremented by 10 * number of matches (I think)
I cannot use FULLTEXT indexes. What I would like to do is refactor the query
to improve the speed.
Can someone please advise as to why my queries ALWAYS use filesort, where and
temporary (as found out by EXPLAIN...)
Can someone recommend a new query to deliver the same results??
Thanks in advance
## START TABLE STRUCTURE ##
CREATE TABLE article_related_communities (
comm_id int(6) default NULL,
article_id varchar(12) default NULL,
KEY article_id (article_id),
KEY comm_id (comm_id)
) TYPE=MyISAM;
CREATE TABLE articles (
id int(4) NOT NULL auto_increment,
c_type int(4) NOT NULL default '1',
author int(6) NOT NULL default '0',
comm_id int(6) default '0',
comm_type int(6) default '0',
keywords longtext NOT NULL,
title varchar(128) NOT NULL default '',
synopsis text NOT NULL,
release_date varchar(14) default NULL,
expiry_date varchar(14) default NULL,
start_date varchar(14) NOT NULL default '',
closing_date varchar(14) NOT NULL default '',
location varchar(255) NOT NULL default '',
cost varchar(255) NOT NULL default '',
times text NOT NULL,
overnight_details text NOT NULL,
remuneration varchar(255) NOT NULL default '',
body text NOT NULL,
status int(6) default NULL,
expired int(2) NOT NULL default '0',
deleted int(2) NOT NULL default '0',
ctime varchar(14) NOT NULL default '',
mtime varchar(14) NOT NULL default '',
mod_author int(6) default '0',
event_type int(2) default '0',
cs_org int(8) NOT NULL default '0',
expiry_reason varchar(128) NOT NULL default '',
accumulated_rating int(6) NOT NULL default '0',
num_ratings int(6) NOT NULL default '0',
average_rating float NOT NULL default '0',
event_duration varchar(128) NOT NULL default '0',
event_organiser varchar(128) NOT NULL default '0',
event_organiser_email varchar(128) NOT NULL default '0',
PRIMARY KEY (id),
KEY c_type (c_type),
KEY status (status),
KEY comm_id (comm_id),
KEY author (author),
KEY expired (expired),
KEY deleted (deleted),
KEY expiry_date (expiry_date),
KEY release_date (release_date),
FULLTEXT KEY idx_tit_syn_body (title,synopsis,body)
) TYPE=MyISAM;
## END TABLE STRUCTURE ##
## START QUERY ##
SELECT distinct articles.* , ( ( (title REGEXP [[::]]MArket[[::]])*10+(body
REGEXP [[::]]MArket[[::]])*10+(synopsis REGEXP
[[::]]MArket[[::]])*10+(closing_date REGEXP
[[::]]MArket[[::]])*10+(location REGEXP [[::]]MArket[[::]])*10+(cost
REGEXP [[::]]MArket[[::]])*10+(times REGEXP
[[::]]MArket[[::]])*10+(overnight_details REGEXP
[[::]]MArket[[::]])*10+(remuneration REGEXP
[[::]]MArket[[::]])*10+(keywords REGEXP [[::]]MArket[[::]])*10 ) + (
(title REGEXP [[::]]Segmentation[[::]])*10+(body REGEXP
[[::]]Segmentation[[::]])*10+(synopsis REGEXP
[[::]]Segmentation[[::]])*10+(closing_date REGEXP
[[::]]Segmentation[[::]])*10+(location REGEXP
[[::]]Segmentation[[::]])*10+(cost REGEXP
[[::]]Segmentation[[::]])*10+(times REGEXP
[[::]]Segmentation[[::]])*10+(overnight_details REGEXP
[[::]]Segmentation[[::]])*10+(remuneration REGEXP
[[::]]Segmentation[[::]])*10+(keywords REGEXP
[[::]]Segmentation[[::]])*10 ) ) AS score FROM articles LEFT JOIN
article_related_communities on articles.id =
article_related_communities.article_id WHERE deleted=0 AND ( ( (title REGEXP
[[::]]MArket[[::]])*10+(body REGEXP [[::]]MArket[[::]])*10+(synopsis
REGEXP [[::]]MArket[[::]])*10+(closing_date REGEXP
[[::]]MArket[[::]])*10+(location REGEXP [[::]]MArket[[::]])*10+(cost
REGEXP [[::]]MArket[[::]])*10+(times REGEXP
[[::]]MArket[[::]])*10+(overnight_details REGEXP
[[::]]MArket[[::]])*10+(remuneration REGEXP
[[::]]MArket[[::]])*10+(keywords REGEXP [[::]]MArket[[::]])*10 ) + (
(title REGEXP [[::]]Segmentation[[::]])*10+(body REGEXP
[[::]]Segmentation[[::]])*10+(synopsis REGEXP
[[::]]Segmentation[[::]])*10+(closing_date REGEXP
[[::]]Segmentation[[::]])*10+(location REGEXP
[[::]]Segmentation[[::]])*10+(cost REGEXP
[[::]]Segmentation[[::]])*10+(times REGEXP
[[::]]Segmentation[[::]])*10+(overnight_details REGEXP
[[::]]Segmentation[[::]])*10+(remuneration REGEXP
[[::]]Segmentation[[::]])*10+(keywords REGEXP
[[::]]Segmentation[[::]])*10 ) )0
AND expiry_date '20041018'
AND (status='2'
OR (status='1' AND (author = 161 OR (articles.comm_id IN ('')
AND release_date='20041018'
AND (articles.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
OR article_related_communities.comm_id IN
(Errcode: 24) when using LOCK TABLES doing backup
Hello, We have an issue related to backup a specific db mounted on MySql 3.23.58 and RedHat 8 OS. The server is running Brightor Arcserve Backup agents for MySQL and cannot backup the entire database. Our Computer Associates support say that this error is originated by MySQL, but only occur when the agent try to access a specific db. Other databases in MySQL don´t report errors and the CA agent can do normal backup. Following the issue and the screen reported when launching mysqldump command. This method is used for Brighstor backup agent. Can you help me ? mysqldump -F --opt -S socket -P port -u username --password=password dbname -- MySQL dump 8.23 -- -- Host: localhost Database: - -- Server version 3.23.58-log /usr/local/mysql/bin/mysqldump: Got error: 1105: File '/usr/local/mysql/var/atmail/Abook_vx.MYD' not found (Errcode: 24) when using LOCK TABLES But the message not is always with the table Abook_vx.MYD. Sometimes appear in Abook_vx.MYD, other in data files .MYD and other in .frm files. Our database has 7000 tables. Could be this the problem ? The backup support say that the problem resides in the amount of tables open at the same time. Thanks in advance, Diego.-
replication problems
One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
Answered my own question, sorry for the quick posting... When I was trying to do the LOAD DATA FROM MASTER, with the master set as GRANT ALL ON *.* TO 'repl_user'@ ... the slaves were reporting an error that stated I needed to GRANT SUPER,REPLICATION CLIENT on the master, which didn't work. After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. -id ian douglas wrote: One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. Except that changes made on the master are not automatically picked up by the slaves. Am I missing something? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL replication
Ahh, thanks and to Bill Alliar as well. I just needed to re-read everything, I think that answers my questions. This full situation is this. On each slave I have a database, radius, which holds auth info and accounting info for each user. I want to limit the accounting info on the slave servers as the tables grow very large, very fast. I would like to move the accounting info over a week old onto the master server for historical use. But I do not want to update the auth info on the slave servers, it would be better to have a single point to update, delete, insert auth info such as the master. In a nutshell I want, MASTER.authinfo - SLAVE.authinfo SLAVE.accounting - MASTER.accounting delete SLAVE.accounting I believe the best solution would be to replicate auth info from the master to the slaves using, --replicate-do-db=radius --replicate-ignore-table=radius.radacct This would replicate only the radius db, and ignore the radius.radacct table correct? (there are hundreds of DB on this master) Then I can move the accounting data back to the master by running, [on the slave] SELECT * INTO OUTFILE '/file/path' FROM radius.radacct (records to keep) DELETE FROM radius.radacct WHERE (records to DELETE) [on the master] (ftp the outfile from slave to master) LOAD DATA LOCAL INFILE '/file/path' REPLACE INTO TABLE radius.radacct Does this sound right or am I making this too complicated? Thanks. DAve Gleb Paharenko wrote: Hello. See: http://dev.mysql.com/doc/mysql/en/Replication_Options.html Dave Goodrich [EMAIL PROTECTED] wrote: Good morning, Been reading through the docs and checking online info and I m still looking for a answer. I have a radius DB on two radius servers and I want to sync them via a master server. Seems easy enough, but I have one table which holds accounting data. How long a user has been online, when they logged on, when they logged off. This data is sent to the slave servers by the auth equipment. Is it possible to only replicate a *table* to a slave and not the entire DB? Thanks, DAve -- Systems Administrator http://www.tls.net Get rid of Unwanted Emails...get TLS Spam Blocker! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation of system tables failed
Hello, I am trying to install the system db with mysql 4.1.7 on my FreeBSD-4.10 but I encounter some problems. You can find on my perso page how I compile mysql (subfolder fbsd-secure). This is what I did... (root) # mysql_install_db --user=mysql --datadir=/home/mysqld/ Installing all prepared tables Installation of system tables failed! Examine the logs in /home/mysqld/ for more information. You can also try to start the mysqld daemon with: /usr/local/libexec/mysqld --skip-grant You can use the command line tool /usr/local/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /home/mysqld/ that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/bin/mysqlbug script! The only things I can see is that two directories have been created on /home/mysqld (mysql,test). /home/mysqld/mysql has lot of files but I don't see any log files giving to me the problem. So can somebody help me to solve this problem. Regards Vincent. Blondel, Vincent Registered LFS user : 7485 homepage : http://jlang.dyndns.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to use except operation?
Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword =b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the sugested solutions return me A, B (and doesn't return C) How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to use except operation (corrections for the return results)?
Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword = b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the suggested solutions return me A, B, C in the following order: if I ask for K1/K2 then it returns studies A, B; if I ask for K2/K1 (order matters) then it returns A, B, C. How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
L a n a wrote: Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword =b returns SQL error This makes no sense. You have no column named data_id. You have no columns named a or b. You have no no keywords that have a value of 'a' or 'b'. What exactly are you trying to do? I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the sugested solutions return me A, B (and doesn't return C) They could not possibly be returning these values, since you don't select Study in any of the above queries. How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana Show what you really have in your tables, your queries, and what is actually returned. Then explain what you wanted. Post ONLY to the list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 SELECT DISTINCT Study FROM yourtablename WHERE keyword='K1' AND NOT keyword='K2' That *should* give you a single entry for 'C' since its keywords do not match both 'K1' *and* 'K2' But your posting *was* a little confusing, so perhaps you can elaborate a little further on what it is you're trying to accomplish? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not returning NULL or 'N/A' fields?
Is there a way to have a select NOT return columns that have either a 'null' or 'n/a' value? I have some shared specs for some products, and some of the fields aren't used for all products, so I have blank entires in those columns for those specific products. I'd like to return all the info that is actually there. Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
ian douglas wrote: Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 SELECT DISTINCT Study FROM yourtablename WHERE keyword='K1' AND NOT keyword='K2' That *should* give you a single entry for 'C' since its keywords do not match both 'K1' *and* 'K2' No. Rows 1 and 5 both will be selected. But your posting *was* a little confusing, so perhaps you can elaborate a little further on what it is you're trying to accomplish? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not returning NULL or 'N/A' fields?
- Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 08, 2004 3:19 PM Subject: Not returning NULL or 'N/A' fields? Is there a way to have a select NOT return columns that have either a 'null' or 'n/a' value? I have some shared specs for some products, and some of the fields aren't used for all products, so I have blank entires in those columns for those specific products. I'd like to return all the info that is actually there. In a word, no. If you specify a column name in your SELECT, that column will be returned in the result set whether it contains a null or not. I believe that is true of all relational databases, not just MySQL. If you are bothered by selling nulls and would like to have them appear as zeroes or blanks or question marks instead of N/A or whatever, *that* can be done easily enough: see the COALESCE function at http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with SLOW query
You are always getting filesort, where and temporary because the
optimizer cannot use an index. Both your WHERE clause and your ORDER BY
clause use computed values. None of those values exist in an index because
you calculate them for every query.
What I did below is not a refactoring, just a different layout of your
query. I did it
this way to highlight the repetitiveness of parts of this query. (I hope
that auto-wrapping doesn't destroy this too much):
SELECT distinct articles.* ,
((
(title REGEXP [[::]]MArket[[::]])*10
+(body REGEXP [[::]]MArket[[::]])*10
+(synopsis REGEXP [[::]]MArket[[::]])*10
+(closing_date REGEXP [[::]]MArket[[::]])*10
+(location REGEXP [[::]]MArket[[::]])*10
+(cost REGEXP [[::]]MArket[[::]])*10)
+(times REGEXP [[::]]MArket[[::]])*10
+(overnight_details REGEXP [[::]]MArket[[::]])*10
+(remuneration REGEXP [[::]]MArket[[::]])*10
+(keywords REGEXP [[::]]MArket[[::]])*10
)
+(
(title REGEXP [[::]]Segmentation[[::]])*10
+(body REGEXP [[::]]Segmentation[[::]])*10
+(synopsis REGEXP [[::]]Segmentation[[::]])*10
+(closing_date REGEXP [[::]]Segmentation[[::]])*10
+(location REGEXP [[::]]Segmentation[[::]])*10
+(cost REGEXP [[::]]Segmentation[[::]])*10
+(times REGEXP [[::]]Segmentation[[::]])*10
+(overnight_details REGEXP [[::]]Segmentation[[::]])*10
+(remuneration REGEXP [[::]]Segmentation[[::]])*10
+(keywords REGEXP [[::]]Segmentation[[::]])*10
)) AS score
FROM articles
LEFT JOIN article_related_communities
on articles.id = article_related_communities.article_id
WHERE deleted=0
AND ((
(title REGEXP [[::]]MArket[[::]])*10
+(body REGEXP [[::]]MArket[[::]])*10
+(synopsis REGEXP [[::]]MArket[[::]])*10
+(closing_date REGEXP [[::]]MArket[[::]])*10
+(location REGEXP [[::]]MArket[[::]])*10
+(cost REGEXP [[::]]MArket[[::]])*10
+(times REGEXP [[::]]MArket[[::]])*10
+(overnight_details REGEXP [[::]]MArket[[::]])*10
+(remuneration REGEXP [[::]]MArket[[::]])*10
+(keywords REGEXP [[::]]MArket[[::]])*10
)
+(
(title REGEXP [[::]]Segmentation[[::]])*10
+(body REGEXP [[::]]Segmentation[[::]])*10
+(synopsis REGEXP [[::]]Segmentation[[::]])*10
+(closing_date REGEXP [[::]]Segmentation[[::]])*10
+(location REGEXP [[::]]Segmentation[[::]])*10
+(cost REGEXP [[::]]Segmentation[[::]])*10
+(times REGEXP [[::]]Segmentation[[::]])*10
+(overnight_details REGEXP
[[::]]Segmentation[[::]])*10
+(remuneration REGEXP [[::]]Segmentation[[::]])*10
+(keywords REGEXP [[::]]Segmentation[[::]])*10
))0
AND expiry_date '20041018'
AND (status='2'
OR (status='1'
AND (author = 161
OR (articles.comm_id IN (''))
)
)
)
AND release_date='20041018'
AND (articles.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
OR article_related_communities.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
)
ORDER BY score DESC
LIMIT 0,10;
It would be very nice if the optimizer detected the fact that part of your
WHERE clause and your score column are the same formula, that way it
would only need to compute that value once. I suspect that it does but I
still suggest that you drop the calculation in your where clause and move
your check to a HAVING clause. If you leave that calculation in the WHERE
clause you will force a full table scan. You lose nothing with this
change and you may gain in performance both because we might avoid a full
table scan and just in case the optimizer didn't detect the duplication
and factor out that calculation, you won't compute that value twice. It
should also makes your code easier to maintain and your query simpler to
parse. Here is what your new bottom half of your query would look like.
WHERE deleted=0
AND expiry_date '20041018'
AND (
status='2'
OR (
status='1'
AND (author = 161
OR (articles.comm_id IN (''))
)
)
)
AND release_date='20041018'
AND (
articles.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
OR article_related_communities.comm_id IN
('559','560','9','286','425','484','103','324','323','18','1','1','2','4','5')
)
HAVING score 0
help with dbf and dbt
Can someone help me, i have two files one dbf and one dbt thant i need to import it in mysql, can someone help me telling me how can i do this. Thanks. José Antonio Viadas O. Director de Desarrollo Denumeris Interactive http://www.denumeris.com/ TEL. Oficina: 56.64.31.71 TEL. Celular: 044.55.26.99.68.05
MySQL logs and restore data
I deleted some data in mysql. Is it possible to restore the data. Maybe using MYSQL logs? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XML to Mysql
This may just sound stupid but I am going to ask anyway. We run a search engine and we bring in 3+ XML feeds from other search engines Via perl and PHP. So we can end up with 300 results listed for EACH SEARCH. They are only valid for that ONE SEARCH but we need to track every click for proper payment. As you may imagine this has a huge overhead to our mysql. We are growing and about to rewrite our search engine and would like ideas on how to make the best use of these temporary storage tables. Some of the tracking urls can be 440+ characters long so we can't use varchar. Also If you have 20 searches per minute coming in and 30 seconds between the clicks the table can get quick big quickly. So jumping directly to right stored link immediately if not quicker is a must. Just spitballing for the plan ahead. Wanting to make sure we use all the available speed assests at our disposal. ThanksDonny LairsonPresidenthttp://www.gunmuse.com469 228 2183
MySQL logs and restore data
I deleted some data in mysql. Is it possible to restore the data. Maybe using MYSQL logs? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem retreiving data (mysql++1.7.17)
David Kinyanjui wrote: I have a mysql++ question... I'm not sure if this is right list to post to. It isn't. The MySQL++ mailing list's home is http://lists.mysql.com/plusplus Well, I just upgraded mysql++ from version 1.7.9 to 1.7.17. 1.7.21 is the current version. See http://tangentsoft.net/mysql++/ Now, my problem is I don't seem to be getting the collect data from a table. Have you tried the example programs? if (rs.begin() != rs.end()) That will never be true for any STL or STL-like container. I think you want something like (rs.size() 0). 1. Glib::ustring lastname = row[LastName]; See the ChangeLog for why this does not work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't get count(go._iso._objective_id) to return 0 values
I am trying to merge the following 2 selects into one select. !!! Returns all custom objectives. Another check is needed to see if in use. !!! SELECT _objectives.id, _objectives.subjects_id, _objectives.subjectHeadings_id, _objectives.name, _objectives.active, _objectives.displayOrder FROM go._objectives, go._subjectHeadings, go._subjects WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id ORDER BY go._objectives.displayOrder This returns 58 records. I then on each record do the following to determine if this id is in use. SELECT COUNT(*) FROM go._iso, go._ltaForm WHERE _objective_id = $objectiveID AND _ltaForm.id = _iso.ltaForm_id ($objectiveID = $row[0] from 1st query) Now as you can imagine this really bashes the server once records get above 200. I tried the following which returns 55 records that are in use and the count bit tells me how many times each id is in use but it does not tell me what id's are not in use. !!! Works but does not return any custom objectives that are not in use !!! SELECT _objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives, go._subjectHeadings, go._subjects, go._iso WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id AND go._iso._objective_id = _objectives.id GROUP BY go._iso._objective_id ORDER BY go._objectives.displayOrder RETURNS 55 records So it needs to return the 3 other missing records with a count value of 0. Any ideas? If there is a better place for this query, please tell. M. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem retreiving data (mysql++1.7.17)
Warren Young wrote: if (rs.begin() != rs.end()) That will never be true for any STL or STL-like container. I think you want something like (rs.size() 0). Sorry, I mean that will _always_ be true. 1. Glib::ustring lastname = row[LastName]; See the ChangeLog for why this does not work. It's in the v1.7.10 entry, by the way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NULL values from LOAD DATA infile
I'm trying to import a file using LOAD DATA INFILE into a table containing columns that default to NULL. However, values load into the table as 0 (zeros). What can I do to have these default to NULL? mysql describe table column; Field | Type | Null | Key | Default | Extra | column | int(11) | YES || NULL | | I've tried inserting both NULL and undef into records in the infile (fields delimited by commas): 45156,NULL,NULL,NULL,NULL 45156 --- Rachael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation (corrections for the return results)?
Something like: select * from table as t1, table as t2 where t1.study=t2.study and t1.keyword = 'K1' and not t2.keyword ='K2' Santino At 11:57 -0800 8-11-2004, L a n a wrote: Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword = b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the suggested solutions return me A, B, C in the following order: if I ask for K1/K2 then it returns studies A, B; if I ask for K2/K1 (order matters) then it returns A, B, C. How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- 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: NULL values from LOAD DATA infile
Rachael LaPorte Taylor wrote: I'm trying to import a file using LOAD DATA INFILE into a table containing columns that default to NULL. See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html : | Handling of NULL values varies according to the FIELDS and LINES options in use: | | * For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field |value of \N is read as NULL for input (assuming that the ESCAPED BY character is `\'). | * If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as |a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, |which is read as the string 'NULL'. | * If FIELDS ESCAPED BY is empty, NULL is written as the word NULL. | * With fixed-row format (which happens when FIELDS TERMINATED BY and FIELDS ENCLOSED BY |are both empty), NULL is written as an empty string. Note that this causes both NULL values and |empty strings in the table to be indistinguishable when written to the file because they are both |written as empty strings. If you need to be able to tell the two apart when reading the file back in, you |should not use fixed-row format. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subject Headings in Tables
I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php?order=2direction=0submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of basics tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with dbf and dbt
On Mon, 8 Nov 2004, José Antonio Viadas O. wrote: Can someone help me, i have two files one dbf and one dbt thant i need to import it in mysql, can someone help me telling me how can i do this. Have a look at dbf2mysql (http://dbf2mysql.soourceforge.net) - this will import .dbf files. I'm not sure if the current version of dbf2mysql will handle memo (.dbt) files though. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improving query responce time
Hello, I am a software developer working for Lntinfotech, I am using Mysql-4.1.3b-beta-nt,ODBC3.5.1 for an application development. the application which i am developing is a client-server architecture based,in which we have to store data of the BSM(Base Station Manager of CDMA network). the nature of data is records containing alarms and faults occuring in the CDMA system,after storing the data we need to generate statistical reports on these data my table structure is as fallows, CREATE TABLE IND_KAR_BNG_Metallica_PS_RT_4 ( gan_id INTEGER NOT NULL, bsc_id INTEGER NOT NULL, bts_id INTEGER NOT NULL, bd_type VARCHAR(10) NOT NULL, bd_id INTEGER NOT NULL, duplex VARCHAR(10) NOT NULL, data_GenTime DATETIME NOT NULL, item_id INTEGER NOT NULL, M0 INTEGER NOT NULL, M1 INTEGER NOT NULL, M2 INTEGER NOT NULL, M3 INTEGER NOT NULL, M4 INTEGER NOT NULL, M5 INTEGER NOT NULL, M6 INTEGER NOT NULL, M7 INTEGER NOT NULL, M8 INTEGER NOT NULL, M9 INTEGER NOT NULL, M10 INTEGER NOT NULL, M11 INTEGER NOT NULL, M12 INTEGER NOT NULL, M13 INTEGER NOT NULL, M14 INTEGER NOT NULL, M15 INTEGER NOT NULL, M16 INTEGER NOT NULL, M17 INTEGER NOT NULL, M18 INTEGER NOT NULL, M19 INTEGER NOT NULL, M20 INTEGER NOT NULL, M21 INTEGER NOT NULL, M22 INTEGER NOT NULL, M23 INTEGER NOT NULL, M24 INTEGER NOT NULL, M25 INTEGER NOT NULL, M26 INTEGER NOT NULL, M27 INTEGER NOT NULL, M28 INTEGER NOT NULL, M29 INTEGER NOT NULL, M30 INTEGER NOT NULL, M31 INTEGER NOT NULL, INDEX RetreiveIndex (data_GenTime,gan_id ,bsc_id ,bts_id ,bd_type ,bd_id ,item_id)); i am using ODBC3.5.1 for connection establishment to the DB server through an application running on windows 2000 PC the problem i am facing: initially application behaves fine till the record count has reached to around 25 lakh records.. after this it is drastically getting slower as the record count increases.. ie. query responce time is increasing as the record count is increasing. this continues and the application has problem in retreiving data faster.. please suggest a way to improve the query responce time with table stucture being maintained.I have alderdy tried indexing few key columns as u can see in the table structure. hoping for u reply at the earliest, thanking u, chethan. __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
