RE: Search Engine type search
Thanks for the info. I think that problem was the fact I only had a couple of records. When creating the FULLINDEX field, does anything else happen to the field, other than the fact you can search FULLINDEX. The reason I ask is because I have about 1600 text fields which I want to apply this to. Thanks Neil Date: Wed, 23 Aug 2006 12:34:10 +1000 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Search Engine type search Neil Tompkins wrote: I followed the instructions, but when doing a search I get no results returned. here is my table CREATE TABLE /*!32300 IF NOT EXISTS*/ MyTest ( id int(10) unsigned NOT NULL auto_increment, title varchar(200) , body text , PRIMARY KEY (id), INDEX title (title,body));I created FULL INDEX, but for some reason it is not showing above ? Do a show create table tablename; does it show up then? A full text index will only be used once a table is populated (less than 3 or 4 rows it won't use it at all). See http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html and http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html and finally some words will not be included in a full text search automatically: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: Search Engine type search
Neil Tompkins wrote: Thanks for the info. I think that problem was the fact I only had a couple of records. When creating the FULLINDEX field, does anything else happen to the field, other than the fact you can search FULLINDEX. The reason I ask is because I have about 1600 text fields which I want to apply this to. The only thing that happens is the index is created - same as any other type of index. One other thing - in case the restrictions page didn't mention this, fulltext by default ignores words which are less than 3 characters long. You can change this, it'll be in the docs somewhere - ft_min_word_len. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Return list where no data exists
Neil Tompkins wrote: Using this query seems to hang my computer and mySQL server reported the queries had been LOCKED. How many records are in each table? It could take a while, especially if you don't have indexes on the join fields. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Buffer size for innodb tables
Ratheesh K J wrote: Hello All, I wanted to know what is the best size for Innodb key cache. We are currently running MySQL 4.1.11 And we have set the buffer size to 1GB. innodb_buffer_pool_size = 1G The system has 4 GB RAM. 1) In such a case is the above setting ok? 2) All the tables are of Innodb type 3) We are using the same server for Apache and MySQL I'd say it's too high but it depends completely on your usage. Don't forget apache needs some memory as well (and your o/s too) plus whatever the glue between the two is (php? perl? ruby? python?) http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html says: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB There's a lot of information in the manual if you do some searching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dates in mysql
hi MySQL automatically converts a date or time type value to a number (numeric context) viceversa. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter. The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone. Try SELECT @@global.time_zone, @@session.time_zone; SET time_zone = timezone; timezone values can be given as strings indicating an offset from UTC, such as '+10:00' or '-6:00'. If you have a problem with SELECT NOW() returning values in UTC and not your local time, you have to tell the server your current time zone. This should be done for the environment in which the server runs. Thanks ViSolve DB Team - Original Message - From: Chris [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 23, 2006 8:11 AM Subject: dates in mysql Hi all, Does anyone know how mysql stores dates? I'm wondering whether it converts it back to UTC before storing it (and back to the client timezone setting when you select) or whether it leaves it as is with the timezone information. So to change to a different timezone I'd have to convert it back to UTC and so on manually.. I've looked through the manual a few times but can't find an answer either way :( Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
write-protection for some tables while other are writable
Hi all Is there a possibility to have some tables write-protected while others in the same db are not (and yet the write-protected ones are updatable through the replication mechanism, ie. there are tables on a slave server). I guess that both, LOCK TABLES and read-only in my.cnf, don't get this result. Thanks a lot! Gregor Mit freundlichen Grüssen Gregor Reich -- Jud Grafik+Internet Grynaustrasse 21 8730 Uznach Tel. 055 290 16 59 Fax 055 290 16 26 Skype: gregreich (Internettelefonie www.skype.com) www.juhui.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning MySQL
hi all, i developed a programm which needs my external mysql database server. now i have to transfer a lot of data and i have todo a lot of select queues. how can i tune that, so that it would be faster? in my monitoring system i saw, that CPU load is 0, the only thing thats working when my program collects data is the PROC Mit freundlichen Grüßen Jürgen Ladstätter ( [EMAIL PROTECTED] ) ___ P.I.C.S. EDV GmbH. A-5020 Salzburg, Landsturmstraße 18 Tel.: +43 (662) 455467 Fax.: +43 (662) 455468 Web: http://www.pics.co.at http://www.pics.co.at E-Mail: [EMAIL PROTECTED] Ihr Partner für Internet, Computer und Software ___
Re: Tuning MySQL
Jürgen Ladstätter wrote: hi all, i developed a programm which needs my external mysql database server. now i have to transfer a lot of data and i have todo a lot of select queues. how can i tune that, so that it would be faster? in my monitoring system i saw, that CPU load is 0, the only thing thats working when my program collects data is the PROC Tune which bit - the transfer or the select queries? The transfer can be sped up (if it's across a network) by compressing the data before sending it. Use the extended insert syntax (which will speed up the import and reduce the size of the import at the same time). http://dev.mysql.com/doc/refman/5.1/en/insert.html or use load data infile: http://dev.mysql.com/doc/refman/5.1/en/load-data.html Disable indexes before importing and re-enable them after it has finished loading: ALTER TABLE tbl_name DISABLE KEYS; ..load data here.. ALTER TABLE tbl_name ENABLE KEYS; The select queries? Turn on mysql logging and look at the queries you are running. Make sure they are indexed properly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
list of words in fulltext key index
Hi List, I have a table with a FULLTEXT KEY column, and I would like to get a list of all the FULLTEXT KEY words, eg: acetic acid acidified acrylic ... Any idea how to make such a list ? TIA, Cor
Re: Replication shattered
The problem is now the slave is saying there are duplicate key entries. Im not sure how this is possible. Any thoughts ? Your procedure looks right. Do you use innodb tables? Otherwise, single-transaction does not work as you expect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: write-protection for some tables while other are writable
Is there a possibility to have some tables write-protected while others in the same db are not (and yet the write-protected ones are updatable through the replication mechanism, ie. there are tables on a slave server). I guess that both, LOCK TABLES and read-only in my.cnf, don't get this result. Set proper privileges for your user accounts. This will not affect replication, so replication will continue to write to that table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query takes different times for execution...
Hello all, I wanted to know why a select query takes ,say, 18 sec to execute the first time I execute it and then for every successive execution it takes, say, 10 sec. I have disabled Query Caching on the server. If its not Query Caching then what else is causing this variation in exec time? 1) Also i would like to know something about table caching (what exactly gets cached here?) and its advantages 2) What is an optimal join order. Should it be a smaller result joined with a bigger one or vice-versa, Or how does MySQL do it? Thanks, Ratheesh Bhat K J
Backup Errors in MySQL Cluster (5.0.24)
Hi, While taking backup in MySQL 5.0.24 for (ndbcluster tables) i am getting the following errors : mysqldump: Error 1296: Got error 241 'Invalid schema object version' from ndbcluster when dumping table `iib_candidate_tracking` at row: 0 When i checked out using ndberror : NDB error code 1296: No message slogan found (please report a bug if you get this error code): Unknown: Unknown Any help would be appericated. Thanks Regards Dilipkumar ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com
replace delayed not working
Not sure if this is a bug we are updating a large table with replace delayed from our feeder systems, 1000 records at a time using limit's on the input data pull this works great when there's fast selects from the table, while the insert is going when a site does a large pull, which can take 1 minute due to the size, the replace is locked, I expected this, however, users doing small pulls after the replace also get locked so I end up with 1 select running, 1 locked replace, and 50 locked queries waiting behind the replace, everything backs up behind the select that's running is this normal? We did not see this until 5.0.24, we were using 5.0.19 and load data infile all at once, with no locking issues, we put 5.0.24 on, and load data infile stopped working so we switched to the replace delayed loop the replace is fast, 1000 records takes 2 seconds, but the locking of other users is causing huge problems -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query takes different times for execution...
On 8/23/06, Ratheesh K J [EMAIL PROTECTED] wrote: Hello all, I wanted to know why a select query takes ,say, 18 sec to execute the first time I execute it and then for every successive execution it takes, say, 10 sec. I have disabled Query Caching on the server. If its not Query Caching then what else is causing this variation in exec time? 1) Also i would like to know something about table caching (what exactly gets cached here?) and its advantages 2) What is an optimal join order. Should it be a smaller result joined with a bigger one or vice-versa, Or how does MySQL do it? The operating system can cache things. If you run something like: select id from table order by rand() limit 100; that should clear the o/s cache. Not sure about the other questions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup locking tables on 5.0.24
We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace delayed not working
On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: Not sure if this is a bug we are updating a large table with replace delayed from our feeder systems, 1000 records at a time using limit's on the input data pull this works great when there's fast selects from the table, while the insert is going when a site does a large pull, which can take 1 minute due to the size, the replace is locked, I expected this, however, users doing small pulls after the replace also get locked so I end up with 1 select running, 1 locked replace, and 50 locked queries waiting behind the replace, everything backs up behind the select that's running is this normal? We did not see this until 5.0.24, we were using 5.0.19 and load data infile all at once, with no locking issues, we put 5.0.24 on, and load data infile stopped working so we switched to the replace delayed loop the replace is fast, 1000 records takes 2 seconds, but the locking of other users is causing huge problems It's not a bug. I guess this is a myisam table? http://dev.mysql.com/doc/refman/5.1/en/table-locking.html Any time the table is changed with myisam, the whole table is locked. That's the way it works. Innodb doesn't do it this way but it might cause other issues for you. Try changing the table type on a test system and see if it helps/hinders your problem(s). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace delayed not working
It's not a bug. I guess this is a myisam table? http://dev.mysql.com/doc/refman/5.1/en/table-locking.html Any time the table is changed with myisam, the whole table is locked. That's the way it works. Innodb doesn't do it this way but it might cause other issues for you. Try changing the table type on a test system and see if it helps/hinders your problem(s). why did they change this? I reverted everything to 5.0.19 and I'm using load data infiles again, with zero locking issues load data infile seems to use different logic from replace delayed, it does not lock at all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
chris smith wrote: On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace delayed not working
chris smith wrote: On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: Not sure if this is a bug we are updating a large table with replace delayed from our feeder systems, 1000 records at a time using limit's on the input data pull this works great when there's fast selects from the table, while the insert is going when a site does a large pull, which can take 1 minute due to the size, the replace is locked, I expected this, however, users doing small pulls after the replace also get locked so I end up with 1 select running, 1 locked replace, and 50 locked queries waiting behind the replace, everything backs up behind the select that's running is this normal? We did not see this until 5.0.24, we were using 5.0.19 and load data infile all at once, with no locking issues, we put 5.0.24 on, and load data infile stopped working so we switched to the replace delayed loop the replace is fast, 1000 records takes 2 seconds, but the locking of other users is causing huge problems It's not a bug. I guess this is a myisam table? http://dev.mysql.com/doc/refman/5.1/en/table-locking.html Any time the table is changed with myisam, the whole table is locked. That's the way it works. Innodb doesn't do it this way but it might cause other issues for you. Try changing the table type on a test system and see if it helps/hinders your problem(s). This is my load data infile line load data concurrent infile 'blah' ignore into table bla zero locking with 5.0.19, but with 5.0.24 it locked all the users out, pretty much all day as these go on 24/7 now I figured replace delayed ignore would fix it, but nope, same problem for now we are back on 5.0.19 using load data concurrent without problems -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: list of words in fulltext key index
In the last episode (Aug 23), C.R.Vegelin said: Hi List, I have a table with a FULLTEXT KEY column, and I would like to get a list of all the FULLTEXT KEY words, eg: acetic acid acidified acrylic ... Any idea how to make such a list ? You can run the myisam_ftdump program to get this info. There's no way to get it from a client connection. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query takes different times for execution...
When a query goes from 18 to 10 seconds, or similar times, it is likely because of caching at the MySQL or OS/disk level, not query caching. The first time, the info had to be found on the disk and read, which is relatively slow. After that it's in memory and can be accessed more quickly in the future, though it still has to be analyzed/sifted/ordered. When a query goes form 18 seconds to more like 0.01 seconds, it's because of query caching. The data is not consulted; instead the query's precise answer is already in memory in the right order, so it is sent nearly instantaneously. Query caching can give an enormous speed boost for near-static databases; for dbs that change a lot, its impact (positive or negative) is generally minimal. There is some minor overhead associated with it. Dan On 8/23/06, Ratheesh K J [EMAIL PROTECTED] wrote: Hello all, I wanted to know why a select query takes ,say, 18 sec to execute the first time I execute it and then for every successive execution it takes, say, 10 sec. I have disabled Query Caching on the server. If its not Query Caching then what else is causing this variation in exec time? 1) Also i would like to know something about table caching (what exactly gets cached here?) and its advantages 2) What is an optimal join order. Should it be a smaller result joined with a bigger one or vice-versa, Or how does MySQL do it? Thanks, Ratheesh Bhat K J
HUGE load when user with few privileges execs show databases
Hello, as the title reads, when a user X with access to few tables runs show databases the query executes _very_ slowly and there's a sudden HUGE load (mysqld takes 99% cpu for the whole duration of the query): show databases; ++ | Database | ++ | A | | B | | C | | D | | E | | F | ++ 6 rows in set (18.35 sec) Now if I connect as super_user: mysql show databases; ... 286 rows in set (0.00 sec) There are a lot of users defined with very fine grained grants: mysql select count(*) from mysql.user; +--+ | count(*) | +--+ | 1025 | +--+ 1 row in set (0.01 sec) mysql select count(*) from mysql.db; +--+ | count(*) | +--+ | 1975 | +--+ 1 row in set (0.00 sec) mysql select count(*) from mysql.tables_priv; +--+ | count(*) | +--+ | 194177 | +--+ 1 row in set (0.00 sec) This anomaly is a very big problem for me. What can I do to solve it? Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Ascolta tutta la musica che vuoi gratis! * Clicca su www.radiosnj.com Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5176d=23-8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search Engine type search
Actually, by default a full text search ignores words that are three characters or less, not less than three characters. I found this out by searching on red. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 One other thing - in case the restrictions page didn't mention this, fulltext by default ignores words which are less than 3 characters long. You can change this, it'll be in the docs somewhere - ft_min_word_len. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query takes different times for execution...
More than likely, the OS file system is caching the files that make up the tables. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ratheesh K J [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 7:02 AM To: mysql@lists.mysql.com Subject: Query takes different times for execution... Hello all, I wanted to know why a select query takes ,say, 18 sec to execute the first time I execute it and then for every successive execution it takes, say, 10 sec. I have disabled Query Caching on the server. If its not Query Caching then what else is causing this variation in exec time? 1) Also i would like to know something about table caching (what exactly gets cached here?) and its advantages 2) What is an optimal join order. Should it be a smaller result joined with a bigger one or vice-versa, Or how does MySQL do it? Thanks, Ratheesh Bhat K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with INNODB transactions
Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Not sure about performance, or am I?
Hey, I have this query: SELECT team. * , COUNT(*) - IF(team_id IS NULL, 1, 0) AS numberofmember FROM team LEFT JOIN teammember ON ( team.id = teammember.team_id ) WHERE CONCAT( team.name, team.description ) LIKE '% %' AND team.status =1 AND team.inviteonly =0 GROUP BY team.id ORDER BY numberofmember DESC This works fine, but it feels like it is not optimal. I have index on team.id and teammember.team_id, so that is ok I believe. But it feels I should do the count thing separately. However, I can not do that because my system does not support sub queries. Would it be better to first just list all team and then make a new query that counts the number of members? And if you ask why I am doing the COUNT(*) - IF(team_id IS NULL, 1, 0) it is because I want to count the number of members, but if there are no members the row count will still be 1 but with NULL in the teammember fields. So if there is NULL there I know there are no members, so I need to subtract 1 from them so the result is 0. Maybe that is also slowing down the query. I have to point out that right now I do not have any performance issue, this is just theory that I am thinking about, I want to build a system that can be robust and not needs to be redeveloped. Best regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HUGE load when user with few privileges execs show databases
Nico, as a first stab, I would try optimizing the tables in question. OPTIMIZE TABLE mysql.user etc. The one with 194,177 entries would be a good candidate for this especially. I wonder also if you would see something logged in the slow query log as this happens? Perhaps the tables_priv table could use an additional index, if OPTIMIZE doesn't do the trick. Hope this helps. Dan On 8/23/06, Nico Sabbi [EMAIL PROTECTED] wrote: Hello, as the title reads, when a user X with access to few tables runs show databases the query executes _very_ slowly and there's a sudden HUGE load (mysqld takes 99% cpu for the whole duration of the query): show databases; ++ | Database | ++ | A | | B | | C | | D | | E | | F | ++ 6 rows in set (18.35 sec) Now if I connect as super_user: mysql show databases; ... 286 rows in set (0.00 sec) There are a lot of users defined with very fine grained grants: mysql select count(*) from mysql.user; +--+ | count(*) | +--+ | 1025 | +--+ 1 row in set (0.01 sec) mysql select count(*) from mysql.db; +--+ | count(*) | +--+ | 1975 | +--+ 1 row in set (0.00 sec) mysql select count(*) from mysql.tables_priv; +--+ | count(*) | +--+ | 194177 | +--+ 1 row in set (0.00 sec) This anomaly is a very big problem for me. What can I do to solve it? Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Ascolta tutta la musica che vuoi gratis! * Clicca su www.radiosnj.com Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5176d=23-8 -- 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]
The length of the sql query
Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The length of the sql query
--On August 23, 2006 1:55:36 PM -0400 Emi Lu [EMAIL PROTECTED] wrote: Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! All SQL in/out is limited by the max packet size configuration parameter, however if you're running SELECT's with a multi-megabyte where clause, you'll have other more practical issues. Seriously you probably don't want to do what you're doing. Also MySQL 3.23 is very ancient history now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The length of the sql query
Emi, it appears in 3.23 your limit is 16 MB. In 4.0 and later, it is 1 GB. http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html Dan On 8/23/06, Emi Lu [EMAIL PROTECTED] wrote: Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! -- 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: Problem with INNODB transactions
What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New DBManager Professional 3.3.3 (Enterprise Edition)
DBTools Software is pleased to announce the new DBManager Professional 3.3.3 Enterprise Edition. This version is a major bug fix with a few extra funcionality added to the application. For a complete lists of changes check http://www.dbtools.com.br/EN/article.php?id=00071. You can download the latest version at http://www.dbtools.com.br/EN/downloads. What is DBManager? DBManager is a commercial product for the Windows environment to manage MySQL, PostgreSQL, Interbase, Firebird, SQlite, DBF, Microsoft Access, Microsoft SQL Server, Sybase, Oracle and ODBC Datasources. It has a complete set of Enterprise Features: - Object Management (Including databases, tables, views, procedures, functions, etc) - Lots of wizards to Import and Export Structure and Data between databases - Powerful Query and Procedure Builders, with multiple results and blob viewer - Form, Report and Diagram (ER) Designers - Console - Task Builder for process automations - Server and Database Monitoring with charts - and much more For a complete list of features see http://www.dbtools.com.br/EN/dbmanagerpro. Best Regards, DBTools Software http://www.dbtools.com.br/EN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search Engine type search
OK, I appear to be getting somewhere with the FULL TEXT search. Does anyone have any good resources about producing search engine type results ? for example if some enters a search phrase like londn how would I suggest the word london ? From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: RE: Search Engine type search Date: Wed, 23 Aug 2006 12:18:59 -0400 Actually, by default a full text search ignores words that are three characters or less, not less than three characters. I found this out by searching on red. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 One other thing - in case the restrictions page didn't mention this, fulltext by default ignores words which are less than 3 characters long. You can change this, it'll be in the docs somewhere - ft_min_word_len. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
How to query bind address?
Is there a way to query a MySQL (5.0.xx) server to find out which IP address(es) it's listening on? It should be listening on all, but I'm unable to connect from remote machines, so need to troubleshoot a bit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search Engine type search
OK, I appear to be getting somewhere with the FULL TEXT search. Does anyone have any good resources about producing search engine type results ? for example if some enters a search phrase like londn how would I suggest the word london ? Hi Neil, That's a completely different thing, but very common to see both together. I'd say a good way of doing this is to have a from/to table and when the user search for from and to have more results (replace and search to check) you show him a did you mean box with the new word in bold. It's really hard to extract that information from searches anyway so the best you can do, as far as I know, is to monitor the common mistakes by looking into logs and adding terms to that table manually. cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subqueries in MySQL 4.1
Hi, I have a query like this: SELECT table1.*,( SELECT COUNT( field2 ) FROM table2 WHERE id=10 ) AS total FROM table1 GROUP BY id LIMIT 1 but the subqueries do not work with mysql 4.1. How can I convert it (or make to work) in MySQL 3.x, 4.0 possibly in one only query? thanks -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query bind address?
I believe MySQL listens on all IPs ... what if any error message are
you getting?
Can you access other services (SSH, telnet, FTP) over the same routes?
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
Dan
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
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]
Outputting text in a
Is it possible to output text to the screen from a .sql script? If so, how? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subqueries in MySQL 4.1
See http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html for some tips Dan On 8/23/06, spacemarc [EMAIL PROTECTED] wrote: Hi, I have a query like this: SELECT table1.*,( SELECT COUNT( field2 ) FROM table2 WHERE id=10 ) AS total FROM table1 GROUP BY id LIMIT 1 but the subqueries do not work with mysql 4.1. How can I convert it (or make to work) in MySQL 3.x, 4.0 possibly in one only query? thanks -- http://www.spacemarc.it -- 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: Subqueries in MySQL 4.1
On Wed, 2006-08-23 at 22:23 +0200, spacemarc wrote: Hi, I have a query like this: SELECT table1.*,( SELECT COUNT( field2 ) FROM table2 WHERE id=10 ) AS total FROM table1 GROUP BY id LIMIT 1 but the subqueries do not work with mysql 4.1. How can I convert it (or make to work) in MySQL 3.x, 4.0 possibly in one only query? Your query doesn't show any relationship between the two tables (via a join condition or correlation) so you would have to do two queries (which is exactly what your original query does anyway: SELECT COUNT(field2):[EMAIL PROTECTED] FROM table2 WHERE id = 10; SELECT table1.*, @counter as total FROM table1 LIMIT 1; Note that I took out the GROUP BY clause, which is pointless given the query's structure of returning the first id column. Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outputting text in a
Sure - in your sql script, put in SELECT text to the screen; Dan On 8/23/06, Jesse [EMAIL PROTECTED] wrote: Is it possible to output text to the screen from a .sql script? If so, how? Thanks, Jesse -- 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: Search Engine type search
That's a tough one. Basically, you need a spell checker plug-in for your application, I don't think there's much hope of doing this in MySQL alone. You might find a spell checker that uses MySQL for a back end, of course, but I doubt it. Doing a search on Google, I found some open-source spell checkers (http://www.thefreecountry.com/sourcecode/spellcheckers.shtml, et al). I don't know what language you are using, so I can't go further than that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 4:02 PM To: mysql@lists.mysql.com Subject: RE: Search Engine type search OK, I appear to be getting somewhere with the FULL TEXT search. Does anyone have any good resources about producing search engine type results ? for example if some enters a search phrase like londn how would I suggest the word london ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query bind address?
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other location.
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query bind address?
Is it running on Windows, James? You may have to allow connections
through to MySQL in Windows Firewall.
Otherwise it seems like maybe it's been started with --skip-networking ?
Dan
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other location.
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to copy between two databases with the same tables but different number of rows
Hi, I'm trying to copy between two databases with the same tables, but the destination one has a different number of rows. I used SQLyog first to create the script with the necessary commands and it partially worked after applying it to the second table, but my problem is with one of the tables with the same name, in both databases and with a different number of rows. when I issue INSERTs from the source database, to add the records in the destination, it complains the number of rows is different, that's strange so I am looking a way to fix this, and this is the first time I am doing it so please advise me. Thank you, Rick Fitzgerald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query bind address?
Yes, it's running on Windows XP, but the Windows Firewall has been
disabled.
Just figured it out... This was a version of MySQL bundled with an
application. I wrongly assumed that shutting down the application also
shut down the included MySQL server. The original my.cnf had a
bind-address of 127.0.0.1, which I'd removed, but since the MySQL server
wasn't actually being restarted, the new configuration was never picked
up.
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
To: James Eaton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, August 23, 2006 3:07 PM
Subject: Re: Re: How to query bind address?
Is it running on Windows, James? You may have to allow connections
through to MySQL in Windows Firewall.
Otherwise it seems like maybe it's been started with --skip-networking ?
Dan
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same
routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other
location.
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but
I'm
unable to connect from remote machines, so need to troubleshoot a
bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to 4.0
Hi, i was sleep during 2 years and yet no update mysql 3.2X... :) First i download : MySQL-server-4.0.23-0.i386.rpm MySQL-client-4.0.23-0.i386.rpm MySQL-devel-4.0.23-0.i386.rpm MySQL-shared-4.0.23-0.i386.rpm MySQL-shared-compat-4.0.23-0.i386.rpm But when rpm -Uvh *.rpm warning: MySQL-client-4.0.27-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing...### [100%] package rhn-org-trusted-ssl-cert-1.0-2 is already installed file /usr/share/rhn/RHN-ORG-TRUSTED-SSL-CERT from install of rhn-org-trusted-ssl-cert-1.0-2 conflicts with file from package rhn-org-trusted-ssl-cert-1.0-2 I can repair this? Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outputting text in a
There apparently isn't a PRINT or RAISERROR function like there is in SQL Server, I've asked about this before. I created a stored procedure to dump debug text into a table (which is timestamped) and another to output the rows in there in a SELECT. It isn't the same but it's something. Dan Buettner wrote: Sure - in your sql script, put in SELECT text to the screen; Dan On 8/23/06, Jesse [EMAIL PROTECTED] wrote: Is it possible to output text to the screen from a .sql script? If so, how? Thanks, Jesse -- 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]
REPAIR TABLE and mysqlcheck
Hi Folks, Does anybody know if it is possible to restrict access to these commands on an individual basis? I have a need to exclude users, who would otherwise have most privileges, and was wondering if there was a particular privilege that I could revoke. If not, does anybody have a technique that may provide for this? Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent ---
Chemistry search
Has anyone come up with searching a chemicals based database using mols or smilies? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace delayed not working
matt_lists wrote: It's not a bug. I guess this is a myisam table? http://dev.mysql.com/doc/refman/5.1/en/table-locking.html Any time the table is changed with myisam, the whole table is locked. That's the way it works. Innodb doesn't do it this way but it might cause other issues for you. Try changing the table type on a test system and see if it helps/hinders your problem(s). why did they change this? I reverted everything to 5.0.19 and I'm using load data infiles again, with zero locking issues It has always been like that. load data infile seems to use different logic from replace delayed, it does not lock at all What table type do you have in each version? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
matt_lists wrote: chris smith wrote: On 8/23/06, matt_lists [EMAIL PROTECTED] wrote: We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the tables for selects the backup takes 3 hours, so the site is down the whole time I'm using this backup line mysqldump -d -f --quote-names --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work Do you have any stored procedures? I wonder if any bugs were fixed regarding those. I could select my_procedure(); and it does an update of a table's view count or something (think forum post views type scenario). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to copy between two databases with the same tables but different number of rows
axis wrote: Hi, I'm trying to copy between two databases with the same tables, but the destination one has a different number of rows. I used SQLyog first to create the script with the necessary commands and it partially worked after applying it to the second table, but my problem is with one of the tables with the same name, in both databases and with a different number of rows. when I issue INSERTs from the source database, to add the records in the destination, it complains the number of rows is different, that's strange so I am looking a way to fix this, and this is the first time I am doing it so please advise me. Are you sure it's rows and not fields that are different? That would make sense if the number of fields/columns were different, rows shouldn't matter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dates in mysql
Visolve DB Team wrote: hi MySQL automatically converts a date or time type value to a number (numeric context) viceversa. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the *system_time_zone* system variable. The value does not change thereafter. The global *time_zone* system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone. Try SELECT @@global.time_zone, @@session.time_zone; SET time_zone = timezone; */timezone/* values can be given as strings indicating an offset from UTC, such as '+10:00' or '-6:00'. If you have a problem with SELECT NOW() returning values in UTC and not your local time, you have to tell the server your current time zone. This should be done for the environment in which the server runs. Thanks for the info, very helpful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search Engine type search
Jerry Schwartz wrote: Actually, by default a full text search ignores words that are three characters or less, not less than three characters. I found this out by searching on red. Ahh, oops :) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search Engine type search
Neil Tompkins wrote: OK, I appear to be getting somewhere with the FULL TEXT search. Does anyone have any good resources about producing search engine type results ? for example if some enters a search phrase like londn how would I suggest the word london ? I was listening to a podcast a while ago that explained this (can't find the reference sorry) and basically what they do is if the search term contains no results or only a few results, log what the person types in next to search for. The most common next search becomes the did you mean. Whether that's true or not I don't know but it's a pretty simple idea and would work reasonably well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup Errors in MySQL Cluster (5.0.24)
Dilipkumar wrote: Hi, While taking backup in MySQL 5.0.24 for (ndbcluster tables) i am getting the following errors : mysqldump: Error 1296: Got error 241 'Invalid schema object version' from ndbcluster when dumping table `iib_candidate_tracking` at row: 0 When i checked out using ndberror : NDB error code 1296: No message slogan found (please report a bug if you get this error code): Unknown: Unknown It's telling you what to do, log a bug report: http://bugs.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with INNODB transactions
Hi, We maintain a pool ..it is just a collection, where we maintain a list of connections. Regards prasad -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 11:41 PM To: Prasad Ramisetti (WT01 - Broadband Networks); mysql@lists.mysql.com Subject: RE: Problem with INNODB transactions What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Chemistry search
- Original Message - From: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 23, 2006 10:15 PM Subject: Chemistry search Has anyone come up with searching a chemicals based database using mols or smilies? Huh? What do you mean by mols - molar weights? And why would anyone search for anything to do with chemicals based on smilies? How would :-) or symbols like that help? Or is this some other sense of the word smilies than the customary Internet one? -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 2006-08-22 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
