My backup scripts and how reliable are they?
As per the reco of someone on this list, I created a bash script that runs once a day to do the following: # set path to final destination location=/Volumes/foo/sql_dumps/ # set db_list to the list of databases db_list=`echo show databases | /usr/local/mysql/bin/mysql -N -u -p` for db in $db_list; do echo dumping $db to $location$db.sql /usr/local/mysql/bin/mysqldump -u -p --opt $db $location$db.sql done echo changing to directory $location cd $location echo Now in: pwd echo begin gzipping and tarballing tar -zcf $location$time.tar.gz *.sql echo removing: ls -la $location*.sql rm $location*.sql echo All your MySql Database are Belong to Us; echo $location$time.tar.gz The first thing I would like to know, is what you all think of this method and how secure is it to run the username and passord in the file, if not, what other options do I have? Second question, when I do a dump out of phpmyadmin, I get 1 line at a time insert into statements, when they come from my script, I get one insert concatenated with the rest. They both work, so aside from one file being larger than the other, what are the pros and cons? One field in a few databases is of the type password, phpmyadmin outputs it as 0x6ad6600d88afb42e5bef276c039330cc and my script above yields something like this (×-ì ¶3 ?$¡ How do I made sure I have a fully restorable backup? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent help required for mysql
Soni, Sanjay K [EMAIL PROTECTED] wrote: 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be = created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be = created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table = 'mysql.host' doesn't exist 040225 13:57:25 Aborting =20 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed Look into MySQL data dir and check if files host.frm, host.MYI, host.MYD exist in the directory of 'mysql' database. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: [bug] Temp table cannot be used twice in a query
John Heitmann [EMAIL PROTECTED] wrote: Description: When a temp table is included twice in a query mysql fails with the error: ERROR 1137 at line 9: Can't reopen table: 'foo' This happens on both 4.0.17 and 4.0.18. It did not happen on 4.0.14. How-To-Repeat: create temporary table test (pk int primary key); select * from test as foo, test as bar where foo.pk=bar.pk; It's not a bug, it's documented behavior. You can't use temporary table more than once in the same query: http://www.mysql.com/doc/en/Temporary_table_problems.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Fetch data and search on different tables.
Fredrik Carlsson [EMAIL PROTECTED] wrote:
Hi,
I have a question regarding to search with fulltext on table and fetch
the data from another.
Table one:
id, textid, name, number, url
Table two:
id, text
On table two there is a fulltext index.
These two tables recently was one table, but i had to split them due to
the amount of data.
textid in table one is refering to id in table two, in order to keep
person and text together.
Before the split my search question looked like this:
select id,name,number,url match(text)
against('$searchString') as relevance from table where
match(text) against('$searchString' IN BOOLEAN MODE)
having relevance 0.9 order by relevance DESC
But due to the split of the table i cant really figure out how to make
the question.
I want to search with fulltext on table2(text) and fetch all the
corresponding data from table1 where textid=(table2.id).
Any tips?
Something like:
SELECT ... FROM one, two WHERE textid=two.id AND MATCH(text) AGAINST() ..
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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: disable an option but not take server down?
Bing Du [EMAIL PROTECTED] wrote: Our MySQL server (4.0.13) is currently running with the option --skip-networking. We want the server to accept TCP/IP connections. If this option were specified in the config file, perhaps I could just modify the config file and HUP the server process. So there would be no server down time. But now it's a command line option, how can I disable it without stopping/starting the server? You should restart server. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
DECODE, not to decrypt but as trigger
Hi! You should know the meaning of next SQL statements : SELECT DECODE(field_name, '0', 'Sunday', '1', 'Monday', '2', 'Tuesday') FROM table_name (This should mean : if field_name = '0', return 'Sunday', if field_name = '1', returns 'Monday', ...) I try to use this with mysql (in EasyPHP: Apache + MySQL + PHP on Win32), but mysql returns error at the third parameter. It expected to use the DECODE decryption function which needs only 2 parameters. How can I perform this trigger without using another table? Regards, __ Mahefa RANDIMBISOA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with subquery and 'not in'
Duncan Hill [EMAIL PROTECTED] wrote: Mysql version: 4.1.1 Platform: Linux, pre-compiled RPMs from mysql.com My problem: Right now, I use a routine that selects the IDs that haven't been seen, and promptly does an insert into notifications_seen to flag that it has been seen. This works fine. The moment I do alter table notifications_seen add unique unq_notifid_loginid (notifid, notif_loginid), my query starts returning utter foolishness - basically, no records where there were records. Why does adding a unique index cause this? (Or have I just found a bug?) The described behavior doesn't appear with supplied tables on version 4.1.2. Probably it's related to the something that is already fixed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Fetch data and search on different tables.
Hi,
thanks for your answer.
I tried what you suggested:
select table1.id,table1.name from table1,table2 where
table1.textid=table2.id AND match(table2.text)
AGAINST('$searchString' IN BOOLEAN MODE);
But that query takes for ever to run and uses 100% cpu. Is there a
faster way to do this query?
// Fredrik Carlsson
Egor Egorov [EMAIL PROTECTED] wrote:
Something like:
SELECT ... FROM one, two WHERE textid=two.id AND MATCH(text) AGAINST() ..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DECODE, not to decrypt but as trigger
Mahefa Randimbisoa\\DS-ETU [EMAIL PROTECTED] wrote: You should know the meaning of next SQL statements : SELECT DECODE(field_name, '0', 'Sunday', '1', 'Monday', '2', 'Tuesday') FROM table_name (This should mean : if field_name = '0', return 'Sunday', if field_name = '1', returns 'Monday', ...) I try to use this with mysql (in EasyPHP: Apache + MySQL + PHP on Win32), but mysql returns error at the third parameter. It expected to use the DECODE decryption function which needs only 2 parameters. How can I perform this trigger without using another table? If you want to store correspondence between weekday and number in the database, you should create table. From version 5.0 you can create your own function: http://www.mysql.com/doc/en/Stored_Procedures.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Fw: datetime in mysql
- Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: mos Sent: Friday, February 27, 2004 11:51 AM Subject: Re: datetime in mysql sorry , as u said , i did it . it doesn't work for me .This table i want to view in the Web. So that date and time must fully automated. Must be keep connect with system date and time.plz help me ...exactly i need ur help . thanx - Original Message - From: mos To: CurlyBraces Technologies ( Pvt ) Ltd Cc: [EMAIL PROTECTED] Sent: Friday, February 27, 2004 10:32 AM Subject: Re: datetime in mysql At 10:13 PM 2/26/2004, you wrote: hi , i have created datetime field in the field name "abc".so i want to get the system date and time automatically to the abc field for in each records. how can i do that ?can somebody help me ..plzthanx in advancecurlysHi Curly, :)To save the current date time into a DateTime column, make sure column is set to Not Null and just save NULL into that column. Or if you don't want to have to physically use NULL then you could use a TimeStamp column which will always update the column whenever the row is changed (for the first TimeStamp column in the table). TimeStamp is more automatic but may be too automatic if some times you don't want the timestamp column updated. The choice is yours.Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime in mysql
Hi, If You take Your really exellent MySQL Reference Manual, and search for timestamp or look into chapter 6.2, You fill find what You are looking for. Best regards Peter - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Friday, February 27, 2004 7:09 AM Subject: Fw: datetime in mysql - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: mos Sent: Friday, February 27, 2004 11:51 AM Subject: Re: datetime in mysql sorry , as u said , i did it . it doesn't work for me .This table i want to view in the Web. So that date and time must fully automated. Must be keep connect with system date and time.plz help me ...exactly i need ur help . thanx - Original Message - From: mos To: CurlyBraces Technologies ( Pvt ) Ltd Cc: [EMAIL PROTECTED] Sent: Friday, February 27, 2004 10:32 AM Subject: Re: datetime in mysql At 10:13 PM 2/26/2004, you wrote: hi , i have created datetime field in the field name abc. so i want to get the system date and time automatically to the abc field for in each records. how can i do that ?can somebody help me ..plz thanx in advance curlys Hi Curly, :) To save the current date time into a DateTime column, make sure column is set to Not Null and just save NULL into that column. Or if you don't want to have to physically use NULL then you could use a TimeStamp column which will always update the column whenever the row is changed (for the first TimeStamp column in the table). TimeStamp is more automatic but may be too automatic if some times you don't want the timestamp column updated. The choice is yours. Mike -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent help required for mysql
Hi! It seems that you have installed mysql, but you did not run the mysql_install_db, this script will create mysql internal databases(the ones that carries user and config info), the script is usually in install_prefix/bin/mysql_install_db If you have no recently installed mysql, and the server has sometime working already before this message, there is some serius problem here. But i sencerely doubt it, innodb is recreating the tables and indices, this looks like a fresh install to me =) Best Regards! On Wed, 2004-02-25 at 19:48, Soni, Sanjay K wrote: 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040225 13:57:25 Aborting 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: Patches for 4.0.15a
I don't like your chances of successfully doing this. You could try pulling the changesets out of the bitkeeper repository by hand but the fact that 4.1.X was branched from an earlier version of the 4.0.x series (I think), it might be a bit of a strugle. Anyways, good luck! :-) Regards, Chris On Fri, 2004-02-27 at 00:17, Sp.Raja wrote: Hi List, I'm using mysql-4.0.15a. But we need some specific features present in 4.1.X such as 1. INSERT ... ON DUPLICATE KEY UPDATE query support 2. Innodb multi-table space (one ibdata file per table) Could someone point me to patches for them for 4.0.15a release. so that I can back merge them specifically. We don't want to use 4.1.X since it is not production/stable version, but have decided to back merge some specific feature which are required. Regards, Sp.Raja -- 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: urgent help required for mysql
Sanjay, It seems you didn't run mysql_install_db script which is located in /scripts directory. This script creates Grant tables for you when you first install mysql. Basically the error says you missing those grant tables, you can re-run the script and recreate them. Hope that helps!! Raza GE Financial Assurance -Original Message- From: Soni, Sanjay K [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 6:48 PM To: [EMAIL PROTECTED] Subject: urgent help required for mysql 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040225 13:57:25 Aborting 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed -- 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]
Full Replication Question
Greetings, I am replicating from 4.0.17 to 4.0.16. I read through the replication docs, and I didn't see anything relating to what I'm trying. What I am hoping to do ... is slave the multiple databases ( the whole thing ), in order to avoid shutting down ( or at least locking ) the master and making a copy/dump. When I tried, and did a show master status ... the message was Writing to Net. I left it for several hours, but nothing at all was replicated that I saw, and the status never changed. My question the, is that is this possible to do a full replication without a dump of some sort ? My replication config on the slave side is: [mysqld] server-id=15 master-host=master master-user=repuser master-password=pass replicate-do-db = db1 replicate-do-db = db2 replicate-do-db = db3 And on the master the following: [mysqld] server-id = 11 log-bin = /mysql/log/binlog binlog-do-db= db1 binlog-do-db= db2 binlog-do-db= db3 **( this is not the complete config, just the relevant portions ) Additional information: The master server, got its data by replicating from the original. In this regard, even though binary logging has been turned on since the *this* master was built, the binary logs will not contain the sum total of all the transactions the db has ever seen. I can of course do a dump if I need to, but it would be much easier for me to replicate fully on the fly if I can, even though it will take much longer. Any advice would be great, Sean -- Real Time, adj.: Here and now, as opposed to fake time, which only occurs there and then. Ambrose Bierce (1842 - 1914), The Devil's Dictionary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting SQL_BIG_SELECTS=1 for MySQL server
Jennifer Horne [EMAIL PROTECTED] wrote: We've recently started using MySQL for customers with a large number of records on their systems. For the first time, one of our customers has gone over the 4 million record mark, and we're running into some problems with the MAX_JOIN_SIZE and the SQL_BIG_SELECTS. Using the control center, or command line option, we can successfully 'Set SQL_BIG_SELECTS=1' and solve the problem. So the question is, is there a way to set the server so that the default is SQL_BIG_SELECTS=1? Setting it as a variable in the my.cnf file doesn't seem to work, it causes the server to be unable to start. Is it something that needs to be set through the application accessing the database each time it opens a new connection? Or can we set it through the application globally? Forgive me if this is an obvious answer, I have spent a long time searching the documentation and other places online, but can't seem to find the solution. By default SQL_BIG_SELECTS=1 for new connections. If you use MySQL control center check value of SQL_BIG_SELECTS among Query Options listed in the Query Configuration Dialog windows -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: C api: core dump on mysql_real_connect
On Wed, 25 Feb 2004, Sasha Pachev wrote: where dbh is a global MYSQL structure. This code works fine on the old system. If I pull it out into it's own little test program on the new server, it also works fine. But when I put it in with the analog source code, it compiles fine but the mysql_real_connect causes a core dump when run. The most common reason for the above error is mysql.h header/libmysqlsclient.so library incompatibility. Make sure they are in sync. Except that when I put the same code into it's own little program, it works fine. i.e. I build a C program whose main does nothing but call the connect function. Compiles/runs with no coredump. Cliff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. Good idea. You may also want to check how query caching helps your application (look at the query cache stats in SHOW STATUS with different cache sizes), see if some dynamic pages could be converted to periodically re-generated static, and check if you can pool the writes (eg. if you are logging page hits, append to a file instead of writing to db, and run load data infile once a minute) -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting rows when order is ambiguous
Philip Mak wrote: Say I have this query: SELECT * FROM topics ORDER BY lastPostTime DESC; How would I modify it to answer the question How many rows would be returned before the row that has topics.id = $x? I was thinking of something like this: $xPostTime = SELECT lastPostTime FROM topics WHERE id = $x; SELECT COUNT(*) FROM topics WHERE lastPostTime $xPostTime; but this statement breaks down in the case where there are rows having lastPostTime = $xPostTime. Would I have to do something un-portable like this: SELECT COUNT(*) FROM topics WHERE lastPostTime $xPostTime OR (lastPostTime = $xPostTime AND id $x); or could I do something more elegant that looks like: SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC HAVING something that's true iff it comes before row with id = $x; Philip: If I understood the problem correctly, the answer to it is actually undefined. If you order by lastPostTime, the records with the same lastPostTime value can be returned in any order. I guess to accomplish your goal you could add a column seq_ord int not null to keep track of the record order according to your expectations, and then order by lastPostTime,seq_ord -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization help
Chuck Gadd wrote: I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where This kind of query cannot be efficiently optimized on a pre-4.1 version. With 4.1, if you are using MyISAM tables you could make (zip4_lo_pot,zip4_hi_pot) a spatial column with a spatial index. See http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting rows when order is ambiguous
On Thu, Feb 26, 2004 at 09:50:39AM -0700, Sasha Pachev wrote: If I understood the problem correctly, the answer to it is actually undefined. If you order by lastPostTime, the records with the same lastPostTime value can be returned in any order. I guess to accomplish your goal you could add a column seq_ord int not null to keep track of the record order according to your expectations, and then order by lastPostTime,seq_ord The table has an id column (id is the primary key) that works like your seq_ord suggestion, so I guess I could have a query like this: SELECT * FROM topics ORDER BY lastPostTime DESC, id DESC But then given a certain id = $id, I'm not sure of the best way to count the number of rows that would be returned in the above query before the row with id = $id. Is there any solution that looks like this: SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC, id DESC HAVING not sure what to put here? Or do I have to do this, which feels kludgy: $postTime = SELECT lastPostTime FROM topics WHERE id = $id; SELECT COUNT(*) FROM topics WHERE lastPostTime $postTime OR (lastPostTime = $postTime AND id $id); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Patches for 4.0.15a
Sp.Raja wrote: Hi List, I'm using mysql-4.0.15a. But we need some specific features present in 4.1.X such as 1. INSERT ... ON DUPLICATE KEY UPDATE query support 2. Innodb multi-table space (one ibdata file per table) Could someone point me to patches for them for 4.0.15a release. so that I can back merge them specifically. We don't want to use 4.1.X since it is not production/stable version, but have decided to back merge some specific feature which are required. Your would get a more stable version by using 4.1 as a whole. 4.1 and 4.0 are different enough that the patch game would be very much time consuming and error prone. 4.1 although still called alpha is really not that unstable. Back in 2002 eWeek did a benchmark of production versions of the big bucks databases vs MySQL 4.0.1-alpha (see http://www.mysql.com/eweek/). MySQL and Oracle were the only databases that could pass the original test without crashing. The test also was done on Windows, which at least back then was far from being MySQL forte. The difference between MySQL and the big bucks databases is that when the new major release comes out, MySQL calls it alpha until it proves itself fairly stable in field testing. The big bucks call it stable as soon as it passes their internal QA. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help~ newb learns mysql
You can also rerun the scripts/mysql_install_db This will reinstall the mysql.hosts form and allow you to reconnect to your server. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting rows when order is ambiguous
Philip Mak wrote: On Thu, Feb 26, 2004 at 09:50:39AM -0700, Sasha Pachev wrote: If I understood the problem correctly, the answer to it is actually undefined. If you order by lastPostTime, the records with the same lastPostTime value can be returned in any order. I guess to accomplish your goal you could add a column seq_ord int not null to keep track of the record order according to your expectations, and then order by lastPostTime,seq_ord The table has an id column (id is the primary key) that works like your seq_ord suggestion, so I guess I could have a query like this: SELECT * FROM topics ORDER BY lastPostTime DESC, id DESC But then given a certain id = $id, I'm not sure of the best way to count the number of rows that would be returned in the above query before the row with id = $id. Is there any solution that looks like this: SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC, id DESC HAVING not sure what to put here? Or do I have to do this, which feels kludgy: $postTime = SELECT lastPostTime FROM topics WHERE id = $id; SELECT COUNT(*) FROM topics WHERE lastPostTime $postTime OR (lastPostTime = $postTime AND id $id); Can you just add id $id to the where clause? -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting rows when order is ambiguous
On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote: SELECT COUNT(*) FROM topics WHERE lastPostTime $postTime OR (lastPostTime = $postTime AND id $id); Can you just add id $id to the where clause? No, that won't work because id is only used to disambiguate the order of two rows that have the same lastPostTime. (This is a forum software where topics.lastPostTime indicates the last time a topic was posted in. This may be in a different order than the topics were originally created.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search always returns no results
Hi Matt, thanks for shedding light on the version and key_len issues.
Either phpMyAdmin or MySQL is labelling the return of blank results as an
error, thus my use of the term. The output from phpMyAdmin looks like this:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
MySQL said:
That seemed less than informative to me, so I tried EXPLAIN, hoping to
uncover a clue:
table type possible_keys keykey_len ref rowsExtra
--
entry fulltext subject_index subject_index 0 1 where used
Regarding the search term, in response to your comment I tried different
searches, for words either at the beginning, middle, or end of the strings
which are stored in the subject column, but they all returned blank
results such as seen above.
General questions to the list:
key_len of 0 is okay, I know now . . . but should rows be 1 when the DB has
16 (now 19) records?
Is the syntax I'm using for the query, and to add the index, okay?
Is there an SQL command I can use to look into the index and see if it
actually contains anything?
From: Matt W [EMAIL PROTECTED]
To: Don Dikunetsis [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 01:26:17 -0600
Hi Don,
No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
added boolean searches along with more speed overall). It doesn't need
to be compiled in or anything, it's there by default. Unless someone
compiled it and actually *removed* the full-text code or something. :-)
Also, key_len of 0 in EXPLAIN is normal.
It sounded like you are getting some kind of error in your first
message? If so, what is it? Are you SURE that the EXACT word you're
searching for is present in the table (for example, with a space, etc.
on either side of it)?
Matt
- Original Message -
From: Don Dikunetsis
Sent: Thursday, February 26, 2004 12:21 AM
Subject: Re: fulltext search always returns no results
Hi, thanks for your reply, but it looks like:
As of Version 3.23.23, MySQL has support for full-text indexing and
searching.
--according to:
http://www.mysql.com/doc/en/Fulltext_Search.html
However, I would be unsurprised (though disappointed) to find that the
answer is some variant of this won't work with your
configuration--my
setup certainly doesn't seem to be responding to the normal setup
instructions for fulltext search.
From: [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST)
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no
results. I
have added a fulltext index to the column being searched. Also, I
am
searching for a term that is in the table, but not in more than
50% of
the rows.
I notice that when I add EXPLAIN to my search, the key_len of my
fulltext index is 0. Does that mean my fulltext index is empty,
explaining why my searches never return any results?
Either way, I'd be grateful for any suggestions on how to get
fulltext
search to work!
_
Store more e-mails with MSN Hotmail Extra Storage 4 plans to choose from!
http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prioritizing updates to be replicated
Hello all: I have 2 machines, Primary and Secondary. The updates on the Primary are being replicated on the Secondary via MySQL Replication. Additionally there are updates to specific tables (Alarms) that are more important than other updates. I do not want the Alarm updates to be queued behind the other less important updates. I would want the Alarm updates to get be prioritized ahead of the other updates to get replicated immediately. I do understand that MySQL replication is fast. However we have a decently write-heavy system and there is the possibility of the Slave lagging the Master. If the Master goes down during this situation the idea is not to lose the Alarms because they got queued up behind the less important updates. To solve this issue, I could only think off running 2 separate MySQL processes, one process to handle the important tables and other process to handle the others. Is there are better way to handle this requirement? All feedback is most welcome. Thank you. Gowtham. __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Valid Ports in Windows
Hi, I was just wondering what the valid range for ports is for the MySQL server in a Windows environment. Obviously, the server won't connect to a port already in use, but I know some programs disallow ports under 1024. Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Eric Scuccimarra wrote: Have one more question - indexing the relevant columns based on the explain info has made all of our queries immensely faster. But it appears that new rows are not automatically indexed. Does anyone know about this and if they are not indexed how do I reindex the tables? They should be indexed on insert under normal circumstances. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Valid Ports in Windows
Matt P. Fuller wrote: Hi, I was just wondering what the valid range for ports is for the MySQL server in a Windows environment. Obviously, the server won't connect to a port already in use, but I know some programs disallow ports under 1024. MySQL introduces no port restrictions of its own. If the OS will let it bind on a port, and you tell it to, it will. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prioritizing updates to be replicated
Gowtham Jayaram wrote: Hello all: I have 2 machines, Primary and Secondary. The updates on the Primary are being replicated on the Secondary via MySQL Replication. Additionally there are updates to specific tables (Alarms) that are more important than other updates. I do not want the Alarm updates to be queued behind the other less important updates. I would want the Alarm updates to get be prioritized ahead of the other updates to get replicated immediately. I do understand that MySQL replication is fast. However we have a decently write-heavy system and there is the possibility of the Slave lagging the Master. If the Master goes down during this situation the idea is not to lose the Alarms because they got queued up behind the less important updates. To solve this issue, I could only think off running 2 separate MySQL processes, one process to handle the important tables and other process to handle the others. Is there are better way to handle this requirement? All feedback is most welcome. Thank you. In 4.0, the slave copies the binlog from the master and stores it locally before processing it. So as long as your slave can keep up with binlog I/O, you should be fine even if the slave falls behind in query processing by a lot, and the master dies. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems connecting to MySQL with WLS
Since the connection pool needs to load the driver, putting the driver jar in WEB-INF is too late. One option is to put in the startWeblogic file and add it to the classpath there. Depending on the version of WLS there are various way to accomplish this. Best regards... - Answers to J2EE, Java, UML, Process, and Patterns! (http://groups.yahoo.com/group/bartssandbox) J2EE (http://www.cact.csupomona.edu/oncampus/programs/cert/j2ee/default.asp) Java (http://www.cact.csupomona.edu/oncampus/programs/cert/java/Default.asp) UML (http://www.cact.csupomona.edu/oncampus/programs/cert/uml/default.asp) eBuilt, Inc. (http://www.eBuilt.com) Date: Thu, 26 Feb 2004 18:40:57 +0100 To: [EMAIL PROTECTED] From: =?iso-8859-1?Q?=22Carl_Sch=E9le=2C_IT=2C_Posten=22?= [EMAIL PROTECTED] Subject: Problems connecting to MySQL with WLS Message-ID: [EMAIL PROTECTED] --_=_NextPart_001_01C3FC8F.B12822B3 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello! =20 I'm using a WLS server and MySQL. Where am I supposed to put the = mysql-connector-java-3.0.11-stable-bin.jar to make sure WLS will find = it? I've tried several places ie. under ttk and right under classes. = Still WLS doesn't find my mysql.jar file. It works when I'm compiling it = locally but when I'm trying to deploy it on the server everything goes = wrong. My hierarchy looks like this. =20 WEB-INF | classes | web | java | se | ttk | Test.class --_=_NextPart_001_01C3FC8F.B12822B3-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Valid Ports in Windows
Matt: Read the port specifications enumerated in the services file in %SystemRoot%\win32\drivers\etc Contact your net/sys admin to see which ports he is allowing to cross his firewall. Some admins shut off everything except 80 (HTTP) HTH, -Martin - Original Message - From: Matt P. Fuller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 26, 2004 1:07 PM Subject: Valid Ports in Windows Hi, I was just wondering what the valid range for ports is for the MySQL server in a Windows environment. Obviously, the server won't connect to a port already in use, but I know some programs disallow ports under 1024. Thanks, Matt -- 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]
Design Advice?
Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a register table that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select speed
Hi All, If I got one table A_table with many columns, and a second table B_table is the same but with just primary field and unique field... How much meaningful is the time difference between these queries? 1. SELECT unique_field FROM A_table WHERE prim_field='val'; 2. SELECT unique_field FROM B_table WHERE prim_field='val'; If I split A_table into some tables, and define C_table to be MERGE on the A_table pieces. Is the time difference between selecting from A_table or C_table is meaningful? thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Advice?
Your business requirement is not quite clear. Here are some questions that you need to answer before you finalize your design: 1. How often will the invoice be viewed (and I assume the viewers must be your web site users?) 2. What's the approximate traffic volume? 3. Will each time the users view different invoices (invoices of different periods), or will they often review the same invoices? 4. How big are your order tables? If it's too much work on your database side, you may need to do some work in your application. -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 11:15 AM To: [EMAIL PROTECTED] Subject: Design Advice? Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a register table that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- 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 queries proxy
Does anybody know the product able to serialize multiple similar queries coming from different sources to the one SQL server? Could be nice to have something as a SQL proxy with cashing pool, expiration, etc to put the main server load down and release his resources. Thanks in advance for any info. Igor.
Large ResultSets/TYPE_SCROLL_INSENSITIVE
I am using this: Statem=MyQueryResults.ConnectionUsed.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); Statem.setFetchSize(Integer.MIN_VALUE); But, it will not allow me to do .first() on the ResultSet...or reset to first record on the result set... TYPE_SCROLL_INSENSITIVE allows bi-directional scrolling of records... Am I correct in that I am seeing that MySQL 3.0.11 JDBC Client does not support anything other than TYPE_FORWARD_ONLY and .next() on result set? Any help would be appreciated Ted
AW: Query error in Access
Ed The MS Access SQL syntax for if() is iif(condition, then stuff, else stuff) Maybe that's the problem, I am not sure - try it Freddie -Ursprüngliche Nachricht- Von: Ed Reed [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. Februar 2004 02:09 An: [EMAIL PROTECTED] Betreff: Query error in Access Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prioritizing updates to be replicated
--- Sasha Pachev [EMAIL PROTECTED] wrote: Gowtham Jayaram wrote: Hello all: I have 2 machines, Primary and Secondary. The updates on the Primary are being replicated on the Secondary via MySQL Replication. Additionally there are updates to specific tables (Alarms) that are more important than other updates. I do not want the Alarm updates to be queued behind the other less important updates. I would want the Alarm updates to get be prioritized ahead of the other updates to get replicated immediately. I do understand that MySQL replication is fast. However we have a decently write-heavy system and there is the possibility of the Slave lagging the Master. If the Master goes down during this situation the idea is not to lose the Alarms because they got queued up behind the less important updates. To solve this issue, I could only think off running 2 separate MySQL processes, one process to handle the important tables and other process to handle the others. Is there are better way to handle this requirement? All feedback is most welcome. Thank you. In 4.0, the slave copies the binlog from the master and stores it locally before processing it. So as long as your slave can keep up with binlog I/O, you should be fine even if the slave falls behind in query processing by a lot, and the master dies. Thank you for pointing this out. I ran some tests over a slow network and the Slave continues to update from the relay log even after the Master went done. However, Is there a solution for the situation in which the relay log on the Slave has not kept up with the Master binLog I/Os AND the Master goes down? Also, in the above situation is there a way to prioritize updates to be replicated ? Gowtham. __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large ResultSets/TYPE_SCROLL_INSENSITIVE
Your resultSet needs to be scrollable. You have created a streaming resultSet. Original Message On 2/26/04, 12:33:38 PM, Ted Hulick (nVision Software) [EMAIL PROTECTED] wrote regarding Large ResultSets/TYPE_SCROLL_INSENSITIVE: I am using this: Statem=MyQueryResults.ConnectionUsed.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); Statem.setFetchSize(Integer.MIN_VALUE); But, it will not allow me to do .first() on the ResultSet...or reset to first record on the result set... TYPE_SCROLL_INSENSITIVE allows bi-directional scrolling of records... Am I correct in that I am seeing that MySQL 3.0.11 JDBC Client does not support anything other than TYPE_FORWARD_ONLY and .next() on result set? Any help would be appreciated Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Query error in Access
I mentioned that at the end of my original message. I've tried it both ways and it doesn't solve the problem. The query is good. For some reason though Access or MySQL is removing the IF statements in the middle of it. What next? Freddie Sorensen [EMAIL PROTECTED] 2/26/04 12:25:03 PM Ed The MS Access SQL syntax for if() is iif(condition, then stuff, else stuff) Maybe that's the problem, I am not sure - try it Freddie -Ursprüngliche Nachricht- Von: Ed Reed [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. Februar 2004 02:09 An: [EMAIL PROTECTED] Betreff: Query error in Access Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Advice?
It sounds like the following requirement got you thinking about the problem in the wrong way, though you still came up with a viable solution: invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. One idea is to create a new table that would serve to cross reference the Invoice table with all orders Your real need is to associate POs, etc., with an invoice. The given time period is just a useful way of grouping things _logically_. Your database structure for associating these items should rely on independent primary keys, not dates. This way, items from outside the logical date period could be included in the unlikely case that the need ever arose (business rules can change down the road). One correct solution would be to have an Invoice Line Items table such as you proposed. It would have the ID field, IvoiceID, EntryTypeID, EntryID... Keep the EntryTypID. However, instead of one EntryID field, you might consider having a foreign key field for each invoice line item type: PO_EntryID, SO_EntryID, Adj_EntryID, etc. This will help in enforcing referential integrity. Ted -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 12:16 PM To: 'Zhao, Charles'; [EMAIL PROTECTED] Subject: RE: Design Advice? Hi Charles, Answers: 1. Frequent web viewers 2. Medium... internal website 3. An invoice will get frequent views while it's active, but very few views after it's completed. 4. About 1200 entries a month. Are there approaches other than what I described? -Ed -Original Message- Your business requirement is not quite clear. Here are some questions that you need to answer before you finalize your design: 1. How often will the invoice be viewed (and I assume the viewers must be your web site users?) 2. What's the approximate traffic volume? 3. Will each time the users view different invoices (invoices of different periods), or will they often review the same invoices? 4. How big are your order tables? If it's too much work on your database side, you may need to do some work in your application. -Original Message- Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a register table that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- MySQL General Mailing List
Re: Encrypt data
hi, Mike. What do this using an encrypted loopback-device, on which the /var/lib/mysql stuff resides. just check man losetup for instructions. i'm NOT sure about the preformance issues, though. and i am very concerned about filesystem-corruptionby my logic, there's a double risk, since the host fs and the loopback-fs could be damaged. and just imagine a eeny little tiny error in the host file. as far as i can judge, that would completely screw up the whole partitition.but i'd be VERY happy to be corrected on that. (little hint: i store the passphrase on a floppy that has to be inserted on boot. a little script reads it and mounts the loopback before kicking of mysqld. the floppy is thereafter removed and stored safely) cheers and HTH, M. Mike Koponick wrote: Hello everyone, I'm new to the list and did a little searching on the archives but wanted to ask my question to the group. I have a requirement to encrypt all data stored into a database. I was wondering if there was a different way of doing this or should I use the encrypt/decrypt functions? The idea is that if someone stole the hard drive or computer, it would be hard for someone to break into the database. It seems to me the best way would be to encrypt the drive space, and use the login to authenticate the encryption space on the hard drive. Is something like this doable? I'm running a server with RedHat 9.0/MySQL version 4.0.16. Thanks in advance, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Client mySQL Server/I need help!!
Evelyn: I need help. I have followed your instructions. Here is my problem: . Installation ... ... Shell Make Install Shell scripts/mysql_install_db bash: scripts/mysql_install_db: No such file or directory -- Am I following the correct sequence? There is no directory scripts! In MySQL documentation, section 2.3.1 describes the Source Installation and that's what I am following. I am struck here since you have recently done it you may have a suggestion??? Thanks. Kirt -Original Message- From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 1:51 PM To: Kirti S. Bajwa; [EMAIL PROTECTED] Subject: RE: Client mySQL Server Just went through this. You need to install MySQL from source. The basic commands you must execute to install a MySQL source distribution are: shell groupadd mysql shell useradd -g mysql mysql shell gunzip mysql-VERSION.tar.gz | tar -xvf - shell cd mysql-VERSION shell ./configure --without-server shell make shell make install The ./configure --without-server only installs the client. You need to read the doco about the source install to look for other options you may want. Evelyn -Original Message- From: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 1:31 PM To: '[EMAIL PROTECTED]' Subject: RE: Client mySQL Server First of all I admit that I am not an expert of MySQL. However, during the last three months I have TEST installed MySQL Server software and gone through the tutorial. My next step is to setup three computers as follows: |--| |--| |--| | 12.21.237.10 | | 12.21.237.11 | | 12.21.237.12 | | freeRADIUS | | qmail/HTTP | | DataServer | |--| |--| |--| | | | |---| My design is have run MySQL on data server and keep all data (sql) on this server. freeRADIUS server is for authentication and qmail/HTTP server is for mail and web pages. I am told that I need to install MySQL client program on freeRADIUS qmail/HTTP servers and master MySQL on DataServer. I have not been able to find either Client or Master MySQL but just MySQL! Is there a subset of MySQL which is known as Client and/or Master MySQL or is it just terminology? Any help is highly appreciated. Kirti -- 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: Permissions
You could use views but they will not be available until version 5 or 5.1 In the doc you can find : Views are useful for allowing users to access a set of relations (tables) as if it were a single table, and limiting their access to just that. Views can also be used to restrict access to rows (a subset of a particular table). One does not require views to restrict access to columns, as MySQL Server has a sophisticated privilege system. See section 5.4 The MySQL Access Privilege System (http://www.mysql.com/doc/en/Privilege_system.html). Maybe this could help you Francisco -Original Message- From: Manuele [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 5:44 PM To: [EMAIL PROTECTED] Subject: Permissions Hello, I'd like to apologize, I know what I am asking is probably impossibile, but I would like to know if someone has a way to set up permissions on a row basis, for example if a certain condition matches (for example, grant select on row if columnA is NULL). Anyone has any idea on how to do so? I wouldn't like to devolve this to the application, as users will have a mysql username/password and they MIGHT directly connect to the db. Thanks in advance. -- Manuele - This mail sent through IMP: http://horde.org/imp/ -- 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]
Installing MySQL 4.1 on RedHat Linux 7.2
Hi, I'm running RedHat Linux 7.2 and I want to install MySQL 4.1. I have downloaded the following RPM packages to install in the following order: MySQL-server-4.1.1-1.i386.rpm MySQL-client-4.1.1-0.i386.rpm MySQL-devel-4.1.1-0.i386.rpm MySQL-shared-4.1.1-0.i386.rpm I try to install the packages but when I get to the shared RPM I get the following output: ]# rpm -i --force MySQL-shared-4.1.1-0.i386.rpm error: failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-4.1.1-0 libssl.so.0.9.6 is needed by MySQL-shared-4.1.1-0 I know that these libraries are installed in /usr/lib/ but 'rpm' only gives me a failed dependency. I also tried the following queries on 'rpm' to see if I have the correct RPM packages installed: ]# rpm -qilf /usr/lib/libcrypto.so.0.9.6 Name: openssl096 Relocations: (not relocateable) Version : 0.9.6 Vendor: Red Hat, Inc. Release : 6 Build Date: Mon 06 Aug 2001 10:11:09 PM MDT Install date: Thu 26 Feb 2004 04:49:52 PM MST Build Host: porky.devel.redhat.com Group : System Environment/Libraries Source RPM: openssl096-0.9.6-6.src.rpm Size: 1599661 License: BSDish Packager: Red Hat, Inc. http://bugzilla.redhat.com/bugzilla URL : http://www.openssl.org/ Summary : Secure Sockets Layer Toolkit. Description : The OpenSSL certificate management tool and the shared libraries that provide various cryptographic algorithms and protocols. /usr/lib/libcrypto.so.0.9.6 /usr/lib/libssl.so.0.9.6 /usr/share/doc/openssl096-0.9.6 /usr/share/doc/openssl096-0.9.6/CHANGES /usr/share/doc/openssl096-0.9.6/FAQ /usr/share/doc/openssl096-0.9.6/INSTALL /usr/share/doc/openssl096-0.9.6/LICENSE /usr/share/doc/openssl096-0.9.6/NEWS /usr/share/doc/openssl096-0.9.6/README /usr/share/doc/openssl096-0.9.6/c-indentation.el /usr/share/doc/openssl096-0.9.6/openssl.txt /usr/share/doc/openssl096-0.9.6/openssl_button.gif /usr/share/doc/openssl096-0.9.6/openssl_button.html /usr/share/doc/openssl096-0.9.6/ssleay.txt ]# rpm -qa | grep openssl openssl-devel-0.9.6b-8 openssl096-0.9.6-6 openssl-perl-0.9.6b-8 openssl095a-0.9.5a-11 openssl-0.9.6b-8 Has anyone else had this problem? Is there any other way that I might be able to install the shared RPM without using the '--nodeps' flag? Any ideas? Colin Lawrence
RE: Design Advice?
Ed, In that case (and I assume your web server and database server are on the same cluster), your original idea is good: store foreign keys in your invoice table pointing to all other tables. In other words, once an order is entered, there is also an entry to the invoice table. However, you should use a stored procedure or some kind of application to sort out the result for you. A table should only deal with hard data, not any soft behavior. Just in case if in the future you need have the invoice viewed repeatedly , then it's better to save the sorted result as files on your web server, or blob data in a separate table. Partitioning your invoice table based on time periods would also be an idea. -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 12:16 PM To: 'Zhao, Charles'; [EMAIL PROTECTED] Subject: RE: Design Advice? Hi Charles, Answers: 1. Frequent web viewers 2. Medium... internal website 3. An invoice will get frequent views while it's active, but very few views after it's completed. 4. About 1200 entries a month. Are there approaches other than what I described? -Ed -Original Message- Your business requirement is not quite clear. Here are some questions that you need to answer before you finalize your design: 1. How often will the invoice be viewed (and I assume the viewers must be your web site users?) 2. What's the approximate traffic volume? 3. Will each time the users view different invoices (invoices of different periods), or will they often review the same invoices? 4. How big are your order tables? If it's too much work on your database side, you may need to do some work in your application. -Original Message- Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a register table that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join two tables with Select
Hi List, I need to create a complex Select joining 2 tables. Table Product, I have these fields: Code (PK) Description Records: 01 Product A 02 Product B Table Price, I have these fields: Code (PK/FK) Sequence (PK) Price Records: 01 1 10.00 01 2 12.00 01 3 14.00 01 4 15.00 02 1 20.00 02 2 22.00 03 3 23.00 04 4 24.00 I need to combine these two tables to have this layout: Product First Price Second PriceThird Price Forth Price Product A 10.00 12.00 13.00 14.00 Product B 20.00 22.00 23.00 24.00 Is this possible to do if the Select instruction? If yes, is anyone can help me to build this Select? Thanks a lot. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOWTO add Primary Key to Existing Table
How can I add an auto-incrementing primary key to an existing table? MySQL version 4.0 Thank You -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Access DB into MySQL
I need to do this behind the scenes. The user will need to press a button for the .mdb file to be imported. I can't use a thrid party program for this. Thank you any way. Karam Chand [EMAIL PROTECTED] 2/26/2004 5:22:51 PM Hello I use SQLyog (http://www.webyog.com/sqlyog) to import data from my access db to MySQL. Karam --- Jacque Scott [EMAIL PROTECTED] wrote: I have a .mdb file which I need to import into MySQL. This needs to be done behind the scenes and with code. Can I use LOAD DATA INFILE where I use the .mdb file instead of a .txt file? If not does anyone have any suggestions? Thanks, Jacque __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools
Re: HOWTO add Primary Key to Existing Table
Hi Paul, ALTER TABLE table_name ADD id_column_name INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; The FIRST word at the end just makes it the first column in the table if that's what you want. Hope that helps. Matt - Original Message - From: Paul Maine Sent: Thursday, February 26, 2004 7:08 PM Subject: HOWTO add Primary Key to Existing Table How can I add an auto-incrementing primary key to an existing table? MySQL version 4.0 Thank You -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select speed
Lorderon wrote: Hi All, If I got one table A_table with many columns, and a second table B_table is the same but with just primary field and unique field... How much meaningful is the time difference between these queries? 1. SELECT unique_field FROM A_table WHERE prim_field='val'; 2. SELECT unique_field FROM B_table WHERE prim_field='val'; If I split A_table into some tables, and define C_table to be MERGE on the A_table pieces. Is the time difference between selecting from A_table or C_table is meaningful? You will save a little bit of time on the parser and the query trip over the connection overhead, in the best case (both tables cached in RAM) I would guess about 30% improvement. You may also use UNION instead of the MERGE table. I do not know if it would be faster to use UNION or the MERGE table - I would expect it to be a close match. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing MySQL 4.1 on RedHat Linux 7.2
Colin Lawrence wrote: Hi, I'm running RedHat Linux 7.2 and I want to install MySQL 4.1. I have downloaded the following RPM packages to install in the following order: MySQL-server-4.1.1-1.i386.rpm MySQL-client-4.1.1-0.i386.rpm MySQL-devel-4.1.1-0.i386.rpm MySQL-shared-4.1.1-0.i386.rpm I try to install the packages but when I get to the shared RPM I get the following output: ]# rpm -i --force MySQL-shared-4.1.1-0.i386.rpm error: failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-4.1.1-0 libssl.so.0.9.6 is needed by MySQL-shared-4.1.1-0 I know that these libraries are installed in /usr/lib/ but 'rpm' only gives me a failed dependency. I also tried the following queries on 'rpm' to see if I have the correct RPM packages installed: ]# rpm -qilf /usr/lib/libcrypto.so.0.9.6 Name: openssl096 Relocations: (not relocateable) Version : 0.9.6 Vendor: Red Hat, Inc. Release : 6 Build Date: Mon 06 Aug 2001 10:11:09 PM MDT Install date: Thu 26 Feb 2004 04:49:52 PM MST Build Host: porky.devel.redhat.com Group : System Environment/Libraries Source RPM: openssl096-0.9.6-6.src.rpm Size: 1599661 License: BSDish Packager: Red Hat, Inc. http://bugzilla.redhat.com/bugzilla URL : http://www.openssl.org/ Summary : Secure Sockets Layer Toolkit. Description : The OpenSSL certificate management tool and the shared libraries that provide various cryptographic algorithms and protocols. /usr/lib/libcrypto.so.0.9.6 /usr/lib/libssl.so.0.9.6 /usr/share/doc/openssl096-0.9.6 /usr/share/doc/openssl096-0.9.6/CHANGES /usr/share/doc/openssl096-0.9.6/FAQ /usr/share/doc/openssl096-0.9.6/INSTALL /usr/share/doc/openssl096-0.9.6/LICENSE /usr/share/doc/openssl096-0.9.6/NEWS /usr/share/doc/openssl096-0.9.6/README /usr/share/doc/openssl096-0.9.6/c-indentation.el /usr/share/doc/openssl096-0.9.6/openssl.txt /usr/share/doc/openssl096-0.9.6/openssl_button.gif /usr/share/doc/openssl096-0.9.6/openssl_button.html /usr/share/doc/openssl096-0.9.6/ssleay.txt ]# rpm -qa | grep openssl openssl-devel-0.9.6b-8 openssl096-0.9.6-6 openssl-perl-0.9.6b-8 openssl095a-0.9.5a-11 openssl-0.9.6b-8 Has anyone else had this problem? Is there any other way that I might be able to install the shared RPM without using the '--nodeps' flag? Any ideas? Colin Lawrence I'd say the --nodeps issue is not worth the worry in this case. MySQL is a fairly low-dependency package - in fact, when I need to test something on a new box, to save time I just copy a statically linked mysqld and errmsg.sys -- that's all it *really* needs to run - the rest is just fluff to make it more pleasant to use. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Step on migrating MyISAM to InnoDB
I have MySQL Server 3.23.xx running on RedHat Linux 9 with 40GB harddrive and 256 RAM. I'm using MyISAM table type with around 157 tables. I think i decided to change MyISAM table to InnoDB table. But, I don't know how to do that for the first step. I have read a section on MySQL documentation about InnoDB but still not clearly about that. My existing database is still 40 Mb but would be growing fast. This database have been running about 6 month and used for critical production. Thanks in advance and for your help.
Re: select speed
On Thu, 26 Feb 2004, Lorderon wrote: Hi All, If I got one table A_table with many columns, and a second table B_table is the same but with just primary field and unique field... How much meaningful is the time difference between these queries? 1. SELECT unique_field FROM A_table WHERE prim_field='val'; 2. SELECT unique_field FROM B_table WHERE prim_field='val'; If I split A_table into some tables, and define C_table to be MERGE on the A_table pieces. Is the time difference between selecting from A_table or C_table is meaningful? Unless you have other unstated requirements, from the performance perspective you are probably better off just making an index on (prim_field, unique_field) in A_table and getting rid of everything else. Then mysql should be able to execute query 1 just as fast as if it were in B_table; do an explain on it, and it should have using index in it, which means it only uses the index to do the query and doesn't even look at the table rows directly at all. And you have no worries about keeping the two in sync. As for the time difference between query 1 and 2 now, it may be very tiny or it could be huge, depending largely on if the full table can fit in cache or if only the primary field / unique field can fit in cache. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two indexing questions
On Thu, 26 Feb 2004, Keith Thompson wrote: Given these two tables: create table t1 ( id int unsigned auto_increment, a int, ... [other fields] primary key (id), index aid (a,id) ) type=innodb; create table t2 ( id int unsigned, b int, ... [other fields] index id (id), index bid (b,id) ) type=innodb; Using searches of the form: select * from t1, t2 where t1.id = t2.id and t1.a = somevalue and t2.b = somevalue Now, let's say that the data is such that the driving table is t2 (order of tables with EXPLAIN is t2, t1). Can MySQL take advantage of the bid index to retrieve the id for the join out of the index rather than pulling the data row, or is there no advantage to using index bid (b,id) over just using index bid (b) for this query? Sure, it can do that. Similarly, can MySQL use aid for this query to satisfy both the join and the t1.a = somevalue comparison together when t1 is not the driving table like this? It appears to only want to use the primary key for t1 for this query, which leads me to believe that on non-driving tables the only index it can use is one to do the join and that it can't use an index that could satisfy both the join and another field comparison at the same time. When I just created your test tables with no extra columns, explain shows it didn't want to use the multicolumn index on the second table (ie. using index) unless I explicitly did a force index, but then it did so just fine: mysql explain select * from t1, t2 force index(bid) where t1.id = t2.id and t1.a= 'xxx' and t2.b = 'yy' \G *** 1. row *** table: t1 type: ref possible_keys: PRIMARY,aid key: aid key_len: 5 ref: const rows: 1 Extra: Using where; Using index *** 2. row *** table: t2 type: ref possible_keys: bid key: bid key_len: 10 ref: const,t1.id rows: 1 Extra: Using where; Using index 2 rows in set (0.00 sec) It may well change its perspective and decide to use the index automatically if I actually had more columns in the table, or had data in it, but I don't know offhand if it is smart enough for that... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Step on migrating MyISAM to InnoDB
Asep Andria I.W. wrote: I have MySQL Server 3.23.xx running on RedHat Linux 9 with 40GB harddrive and 256 RAM. I'm using MyISAM table type with around 157 tables. I think i decided to change MyISAM table to InnoDB table. But, I don't know how to do that for the first step. I have read a section on MySQL documentation about InnoDB but still not clearly about that. My existing database is still 40 Mb but would be growing fast. This database have been running about 6 month and used for critical production. Thanks in advance and for your help. It´s simple to convert a table to innodb. Just do this: alter table your_table type=innodb; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join assistance
Using the latest MySQL 4. I could use some help on performing a join but not retrieving all the records of the child table. I have a set of tables, the ones involved in this are related as follows: members (one) - (many) orders (one) - (many) orderitems The end result should be a list of orders with only one row for each order. However, the criteria for the search requires me to join to the orderitems table, since I want to find all orders that do not have a certain product in that order. Here is the basic SQL statement I envision: SELECT * FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON o.orderId=oi.orderId WHERE (o.orderStatus=2) AND (oi.productId != 55) (BTW, I don't actually use SELECT * , just using it here for the purposes of this example) I still get orders that contains orderitems with productId 55, since there are other order items in the order that are not that product. So Distinct does not help here. What I think I need is a subselect and the IN clause, but that is not available in MySQL 4. -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime in mysql
hi , i have created datetime field in the field name "abc". so i want to get the system date and time automatically to the abc field for in each records. how can i do that ?can somebody help me .plz thanx in advance curlys -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE clauses across rows...
1) This is mostly an SQL question, although MySQL may have some trick up its sleeve that would help me. 2) I've searched the archives, and google 3) I've been using SQL for a long time, but can't think of a way to solve this 4) This may not be possible. :) I am dealing with serial data that is being put into a table, and I have to search through that data to find certain start words. That is, data that indicates the start of a new packet of data. This start word, since this is asynchronous serial data, could be split over rows. For purposes of example, let us assume we have a table of four columns, and that my start indicator is strt in one column and word in the next column. Now I want to find the next start word. The first three cases are easy, I just do something like WHERE col1 = 'strt' AND col2 = 'word', etc.. But, what I need to be able to do is something like this: SELECT unique_key_field FROM table_name WHERE (col1='strt' AND col2='word') OR (col2='strt' AND col3='word') OR (col3='strt' AND col4='word') OR (col4='strt' AND col1_in_the_next_row='word') Is this even possible? I'd hate to issue hundreds of queries to check if strt word is split across rows. Should I investigate setting variables equal to the col4, and on a failed search, use that variable in the next query to see if the old col4 pairs with anything in col1? Or am I better off searching for the good case, and on failure, go and look for 'strt' in col4, then when I get a row, see if 'word' is in col1 on the next row (via another query)? Ideas? Tips? Suggestions? Thanks much! j- k- -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Access DB into MySQL
Hello I use SQLyog (http://www.webyog.com/sqlyog) to import data from my access db to MySQL. Karam --- Jacque Scott [EMAIL PROTECTED] wrote: I have a .mdb file which I need to import into MySQL. This needs to be done behind the scenes and with code. Can I use LOAD DATA INFILE where I use the .mdb file instead of a .txt file? If not does anyone have any suggestions? Thanks, Jacque __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My backup scripts and how reliable are they?
As per the reco of someone on this list, I created a bash script that runs once a day to do the following: # set path to final destination location=/Volumes/foo/sql_dumps/ # set db_list to the list of databases db_list=`echo show databases | /usr/local/mysql/bin/mysql -N -u -p` for db in $db_list; do echo dumping $db to $location$db.sql /usr/local/mysql/bin/mysqldump -u -p --opt $db $location$db.sql done echo changing to directory $location cd $location echo Now in: pwd echo begin gzipping and tarballing tar -zcf $location$time.tar.gz *.sql echo removing: ls -la $location*.sql rm $location*.sql echo All your MySql Database are Belong to Us; echo $location$time.tar.gz The first thing I would like to know, is what you all think of this method and how secure is it to run the username and passord in the file, if not, what other options do I have? Second question, when I do a dump out of phpmyadmin, I get 1 line at a time insert into statements, when they come from my script, I get one insert concatenated with the rest. They both work, so aside from one file being larger than the other, what are the pros and cons? One field in a few databases is of the type password, phpmyadmin outputs it as 0x6ad6600d88afb42e5bef276c039330cc and my script above yields something like this (?-ì ¶3?$¡ How do I made sure I have a fully restorable backup? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetch data and search on different tables.
This query was pretty fast avg. 1-3 s / search
SELECT m1.id FROM table1 AS t1 JOIN table AS t2 ON m1.textid=m2.id WHERE
match(t2.text) AGAINST('$searchString' IN BOOLEAN MODE);
I also added an index on table1.textid and table2.id.
Is there a way to optimize this query even more?
// Fredrik Carlsson
Fredrik Carlsson [EMAIL PROTECTED] wrote:
Hi,
I have a question regarding to search with fulltext on table and fetch
the data from another.
Table one:
id, textid, name, number, url
Table two:
id, text
On table two there is a fulltext index.
These two tables recently was one table, but i had to split them due to
the amount of data.
textid in table one is refering to id in table two, in order to keep
person and text together.
Before the split my search question looked like this:
select id,name,number,url match(text)
against('$searchString') as relevance from table where
match(text) against('$searchString' IN BOOLEAN MODE)
having relevance 0.9 order by relevance DESC
But due to the split of the table i cant really figure out how to make
the question.
I want to search with fulltext on table2(text) and fetch all the
corresponding data from table1 where textid=(table2.id).
Any tips?
Egor Egorov [EMAIL PROTECTED] wrote:
Something like:
SELECT ... FROM one, two WHERE textid=two.id AND MATCH(text) AGAINST() ..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Permissions
Hello, I'd like to apologize, I know what I am asking is probably impossibile, but I would like to know if someone has a way to set up permissions on a row basis, for example if a certain condition matches (for example, grant select on row if columnA is NULL). Anyone has any idea on how to do so? I wouldn't like to devolve this to the application, as users will have a mysql username/password and they MIGHT directly connect to the db. Thanks in advance. -- Manuele - This mail sent through IMP: http://horde.org/imp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
keeping the table on RAM disk
Hi, We wanted to keep one of Innodb table on RAM disk in our database design so that we can get better performance. We were hoping the take adventage of using MULTIPLE TABLESPACE feature of 4.1.1 but I just found out that InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. Is there anyway to tell InnoDB to keep the file on a different directory for a specific table ? Thanks Aysun Alay
Two indexing questions
Given these two tables: create table t1 ( id int unsigned auto_increment, a int, ... [other fields] primary key (id), index aid (a,id) ) type=innodb; create table t2 ( id int unsigned, b int, ... [other fields] index id (id), index bid (b,id) ) type=innodb; Using searches of the form: select * from t1, t2 where t1.id = t2.id and t1.a = somevalue and t2.b = somevalue Now, let's say that the data is such that the driving table is t2 (order of tables with EXPLAIN is t2, t1). Can MySQL take advantage of the bid index to retrieve the id for the join out of the index rather than pulling the data row, or is there no advantage to using index bid (b,id) over just using index bid (b) for this query? Similarly, can MySQL use aid for this query to satisfy both the join and the t1.a = somevalue comparison together when t1 is not the driving table like this? It appears to only want to use the primary key for t1 for this query, which leads me to believe that on non-driving tables the only index it can use is one to do the join and that it can't use an index that could satisfy both the join and another field comparison at the same time. Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text Search on MERGE Tables
Hello All, Is it possible to define MERGE table on several tables with full-text indexes? And to make a select on the MERGE table with MATCH AGAINST? thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Advice?
Hi Charles, Answers: 1. Frequent web viewers 2. Medium... internal website 3. An invoice will get frequent views while it's active, but very few views after it's completed. 4. About 1200 entries a month. Are there approaches other than what I described? -Ed -Original Message- Your business requirement is not quite clear. Here are some questions that you need to answer before you finalize your design: 1. How often will the invoice be viewed (and I assume the viewers must be your web site users?) 2. What's the approximate traffic volume? 3. Will each time the users view different invoices (invoices of different periods), or will they often review the same invoices? 4. How big are your order tables? If it's too much work on your database side, you may need to do some work in your application. -Original Message- Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a register table that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL_BIG_TABLES and replication
I raised tmp_table_size to 1000M and restarted mysql on the slave and still got the same error. Any other variables I should be looking at? Thanks, Brian I don't need any of that SQL stuff -- I just want a database! -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 6:09 PM To: Stanton, Brian Cc: mysql (E-mail) Subject: Re: SQL_BIG_TABLES and replication Stanton, Brian wrote: I'm currently running mysql 4.0.13 on red hat 7.2. The following create table query currently requires the user to use SET SQL_BIG_TABLES=1 for the query to go through on the master successfully. However, that doesn't seem to get set when the slave tries to replicate the create table statement. I've tried restarting the slave with the --big-tables option, but that doesn't seem to help either. The resulting table files on the master are relatively small... 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI however the ProfileIDValue_REGID table is rather large. 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm 499M Feb 16 22:17 ProfileIDValue_REGID.MYD 443M Feb 16 22:18 ProfileIDValue_REGID.MYI 8.4k Nov 13 11:47 UniqueID_oct2003.frm 32M Nov 13 11:53 UniqueID_oct2003.MYD 51M Nov 13 11:53 UniqueID_oct2003.MYI 8.4k Jan 9 10:50 UniqueID_sep2003.frm 34M Jan 9 10:58 UniqueID_sep2003.MYD 55M Jan 9 10:58 UniqueID_sep2003.MYI Anyone have any thoughts? ERROR: 1114 The table '#sql_931_0' is full 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) select distinct UniqueID_sep2003.UniqueID from UniqueID_sep2003,ProfileIDValue_REGID where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID union select distinct UniqueID_oct2003.UniqueID from UniqueID_oct2003,ProfileIDValue_REGID where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', error_code=1114 Looks like a bug to me. MySQL should be able to figure out it needs to use the disk when an in-memory temp table exceeds tmp_table_size without SQL_BIG_TABLES. It would be nice if you could create a test case for it and submit it to MySQL developers. For now, try increasing tmp_table_size ( make sure you have enough RAM + swap space to deal with it, though). If re-writing the query is an option, I would also try it without UNION, which is a fairly new feature and could still have a few quirks. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Joins
You might want to append table to table.. in this case you should use UNION (not JOIN).. but if you got 2 identical tables of type MyISAM, then you can define a MERGE table like this: CREATE TABLE new_table (*table definition of the original tables*) type=MERGE union=(all_by_Payroll,payinc); then you can run the select query on the new_table. Unknown Sender [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I have 2 identical tables and wish to join them. I am a complete novice and thought it was simple! Here is the code that I am using with asp.net select Date, Payroll, First, Last, Rank, Number, Division, Reason, ImpDate from all_by_Payroll, payinc where + DropDownList1.SelectedItem.Value + = ' + TextBox1.Text +' ORDER BY Date ASC; Any help would be appreciated as I am now completely stuck Thanks, Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Access DB into MySQL
I have a .mdb file which I need to import into MySQL. This needs to be done behind the scenes and with code. Can I use LOAD DATA INFILE where I use the .mdb file instead of a .txt file? An mdb file isn't exactly the same as text, is it? You might as well use MS Word as the source ;-) If not does anyone have any suggestions? Use a datapump. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search always returns no results
Hi,
The comments in the fulltext doc page
(http://www.mysql.com/doc/en/Fulltext_Search.html) discuss the issues of
stopwords and over 50% hits, so I did my best to avoid those particular
bombs in my searches.
The subject column contains subjects for message posts/entries, and as
such they're strings of around six words, on average. Here's some searches
that returned blank results:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('your');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('hatching');
your is a possible stopword, but the other two are words that appear just
once in the 16 (now 19) records.
For the list in general, here's some things I tried since my last post:
1. Attempted to verify that the table is MyISAM. CHECK TABLE and ANALYZE
TABLE were processed okay, which _seems_ to indicate that the table is
MyISAM:
CHECK TABLE entry; LIMIT 0, 30
TableOp Msg_type Msg_text
entrycheckstatusOK
ANALYZE TABLE entry; LIMIT 0, 30
TableOp Msg_typeMsg_text
entryanalyzestatus Table is already up to date
2. Based on a comment in
(http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html), ran:
ALTER TABLE entry TYPE=MyISAM;
Result: command returned without an error; however, searches still come up
blank.
3. Noted in the fulltext restrictions doc
(http://www.mysql.com/doc/en/Fulltext_Restrictions.html) that fulltext
before 4.1.1 doesn't work with Unicode.
To check the current settings, tried running SHOW CHARACTER SET; and SHOW
COLLATION;, but got errors:
MySQL said: You have an error in your SQL syntax near 'CHARACTER SET' at
line 1
MySQL said: You have an error in your SQL syntax near 'COLLATION' at line 1
So I flushed the data from the table, and reloaded with data with a
character encoding explicitly set at iso-8859-1. However, my searches still
return blank results.
I must admit that at this point I'm stumped!
From: Peter Lovatt [EMAIL PROTECTED]
To: Matt W [EMAIL PROTECTED], Don Dikunetsis
[EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: RE: fulltext search always returns no results
Date: Thu, 26 Feb 2004 07:57:21 -
Hi
Full text can have some strange results.
It ignores common words, like the, if, and, etc as they are not relevant.
If you have a word that appears in most records then it will ignore it,
sometimes returning 0 results even if it is what you were looking for!
The aim of full text is to return the most relevant records. If there are
no
records that are more relevant than any others it may return none.
Could this be the cause?
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent: 26 February 2004 07:26
To: Don Dikunetsis; [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Hi Don,
No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
added boolean searches along with more speed overall). It doesn't need
to be compiled in or anything, it's there by default. Unless someone
compiled it and actually *removed* the full-text code or something. :-)
Also, key_len of 0 in EXPLAIN is normal.
It sounded like you are getting some kind of error in your first
message? If so, what is it? Are you SURE that the EXACT word you're
searching for is present in the table (for example, with a space, etc.
on either side of it)?
Matt
- Original Message -
From: Don Dikunetsis
Sent: Thursday, February 26, 2004 12:21 AM
Subject: Re: fulltext search always returns no results
Hi, thanks for your reply, but it looks like:
As of Version 3.23.23, MySQL has support for full-text indexing and
searching.
--according to:
http://www.mysql.com/doc/en/Fulltext_Search.html
However, I would be unsurprised (though disappointed) to find that the
answer is some variant of this won't work with your
configuration--my
setup certainly doesn't seem to be responding to the normal setup
instructions for fulltext search.
From: [EMAIL PROTECTED]
Subject: Re: fulltext search always returns no results
Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST)
Excuse if i'm not correct but this may be your problemo ?
MySQL 3.23.55 running on my webhost's Linux box
phpMyAdmin 2.1.0
I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
Summary: When I run a fulltext search, it always returns no
results. I
have added a fulltext index to the column being searched. Also, I
am
searching for a term that is in the table, but not in more than
50% of
the rows.
I notice that when I add EXPLAIN to
Import Access DB into MySQL
I have a .mdb file which I need to import into MySQL. This needs to be done behind the scenes and with code. Can I use LOAD DATA INFILE where I use the .mdb file instead of a .txt file? If not does anyone have any suggestions? Thanks, Jacque
Re: Counting rows when order is ambiguous
Philip Mak wrote: On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote: SELECT COUNT(*) FROM topics WHERE lastPostTime $postTime OR (lastPostTime = $postTime AND id $id); Can you just add id $id to the where clause? No, that won't work because id is only used to disambiguate the order of two rows that have the same lastPostTime. (This is a forum software where topics.lastPostTime indicates the last time a topic was posted in. This may be in a different order than the topics were originally created.) Then I would use the last query you proposed except rewrite it to use UNION instead of OR to ensure a better use of keys. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
On 26 Feb 2004 at 13:22, Eric Scuccimarra wrote: But it appears that new rows are not automatically indexed. Does anyone know about this and if they are not indexed how do I reindex the tables? You're misunderstanding something. When you create an index, all the rows in the table are indexed, and new rows are indexed as they are added. What makes you think they're not? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Have one more question - indexing the relevant columns based on the explain info has made all of our queries immensely faster. But it appears that new rows are not automatically indexed. Does anyone know about this and if they are not indexed how do I reindex the tables? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api: core dump on mysql_real_connect
Cliff Addy wrote: On Wed, 25 Feb 2004, Sasha Pachev wrote: where dbh is a global MYSQL structure. This code works fine on the old system. If I pull it out into it's own little test program on the new server, it also works fine. But when I put it in with the analog source code, it compiles fine but the mysql_real_connect causes a core dump when run. The most common reason for the above error is mysql.h header/libmysqlsclient.so library incompatibility. Make sure they are in sync. Except that when I put the same code into it's own little program, it works fine. i.e. I build a C program whose main does nothing but call the connect function. Compiles/runs with no coredump. Double-check the build process of the big program. Watch out for the include and library paths, and make sure to get rid of the stale .o files ( make clean) -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help~ newb learns mysql
Jochem van Dieten wrote: Vincent wrote: all my mistake I deleted mysql.user this tablewhat can I do to recover this ? Restore from backup. If there is no backup, mysql_install_db will re-recreate the default install version - just make sure to remove all mysql/user.* files from the datadir first. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems connecting to MySQL with WLS
Hello! I'm using a WLS server and MySQL. Where am I supposed to put the mysql-connector-java-3.0.11-stable-bin.jar to make sure WLS will find it? I've tried several places ie. under ttk and right under classes. Still WLS doesn't find my mysql.jar file. It works when I'm compiling it locally but when I'm trying to deploy it on the server everything goes wrong. My hierarchy looks like this. WEB-INF | classes | web | java | se | ttk | Test.class
Re: help~ newb learns mysql
Vincent wrote: all my mistake I deleted mysql.user this tablewhat can I do to recover this ? Restore from backup. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help~ newb learns mysql
all my mistake I deleted mysql.user this tablewhat can I do to recover this ? Plz help~ Best regards, Vincent fung -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with 4.0.18
Andrea Riela wrote: Hi folks, my system: openbsd 3.3 mysql ver: 4.0.18 Well, I was working on phpBB conf, when I've seen errors from mysql: mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'columns_priv' Didn't find any fields in table 'db' Didn't find any fields in table 'func' Didn't find any fields in table 'host' Didn't find any fields in table 'tables_priv' Didn't find any fields in table 'user' Database changed mysql select * from user; ERROR 1105: File './mysql/user.MYD' not found (Errcode: 9) But I've all as before: /usr/local/mysql/var: observe# ls cacti ib_logfile1 newsys observe.nesys.it.pid ib_arch_log_00 ibdata1 observe.log training ib_logfile0 mysql observe.nesys.it.err Error 9 is a bad file descriptor. The most probable cause of this is a bad build, or library incompatibility. If you built your own binary, try using the one provided by MySQL AB. If you were using theirs, try building your own. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dollar amounts
Keith Thompson wrote: What is the preferred way of storing a dollar amount in the range 0.00 - 9.99? double decimal(11,2) bigint (storing value*100) ...? I'm more interested in speed of use as an indexed column (especially for range searches) than in disk space usage. bigint should be the most effecient. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed searching with OR ?
Is there a corresponding 'AND' version of the 'UNION' ? ... UNION ALL ... PB
Re: Query optimization help
Without seeing the data I am assuming that you are going over the 30% threshold with your less/greater equal to where clauses. What sort of criteria are you asking the database engine to search for? Original Message On 2/25/04, 9:44:02 PM, [EMAIL PROTECTED] wrote regarding Re: Query optimization help: Maybe i'm wrong here, someone correct me, if its just int's you are gonna use set the field types to bigint it may search faster you are doing a character search, to get there quicker in a text search scenerio i'd suggest mysql4 and full text searching MATCH AGAINST I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
For anyone who is interested the thing that worked and brought the query down from 8 minutes to 5 seconds was separating out the JOIN to remove the OR. I made it into two queries and UNIONed them together and it all works beautifully now. Thanks. At 02:33 PM 2/25/2004 -0800, Daniel Clark wrote: I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- 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: Installation problem!!!
No change. Any other suggestion? Thanks. Kirti -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 7:26 AM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Installation problem!!! Hi! try using the one installed in /usr/local/mysql/bin/mysql_install_db Best Regards! On Wed, 2004-02-25 at 18:45, Kirti S. Bajwa wrote: Hello List: I have installed MySQL Master Server from Binaries 4-5 times without problem. Now I am installing MySQL CLIENT from source and have run into problem, as explained below: Download mysql-5.0.0-alpha.tar.gz in /usr/local % cd /usr/local % gunzip mysql-5.0.0-alpha.tar.gz | tar -xvf - % cd mysql-5.0.0-alpha % ./configure --without-server --prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db When I run the last line scripts/mysql_install_db, I get an error message No such file or directory. I have looked into scripts folder and ther is mysql_install_db.sh. Has anybody else experienced the same problem? What is causing this error? Thanks in advance. Kirti -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patches for 4.0.15a
Hi List, I'm using mysql-4.0.15a. But we need some specific features present in 4.1.X such as 1. INSERT ... ON DUPLICATE KEY UPDATE query support 2. Innodb multi-table space (one ibdata file per table) Could someone point me to patches for them for 4.0.15a release. so that I can back merge them specifically. We don't want to use 4.1.X since it is not production/stable version, but have decided to back merge some specific feature which are required. Regards, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation problem!!!
Hi! try using the one installed in /usr/local/mysql/bin/mysql_install_db Best Regards! On Wed, 2004-02-25 at 18:45, Kirti S. Bajwa wrote: Hello List: I have installed MySQL Master Server from Binaries 4-5 times without problem. Now I am installing MySQL CLIENT from source and have run into problem, as explained below: Download mysql-5.0.0-alpha.tar.gz in /usr/local % cd /usr/local % gunzip mysql-5.0.0-alpha.tar.gz | tar -xvf - % cd mysql-5.0.0-alpha % ./configure --without-server --prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db When I run the last line scripts/mysql_install_db, I get an error message No such file or directory. I have looked into scripts folder and ther is mysql_install_db.sh. Has anybody else experienced the same problem? What is causing this error? Thanks in advance. Kirti -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
How does ~ work in BOOLEAN MODE?
Please help me understand this: ~ are supposed to adjust the weight of a word to the relevancy score, but they only work in BOOLEAN MODE and BOOLEAN MODE doesn't care about relevancy! I am really confused. Could anyone kindly explain how they supposed to work to me? Thanks so much! Haitao Jiang __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
