Re: Moving to another hard drive
Hi, 1) check with the owner permission of the copied data folder 2) GRANT ALL permission to access that db (name of the copied folder) Thanks ViSolve DB Team - Original Message - From: Kevin Chen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 21, 2008 1:14 PM Subject: Moving to another hard drive I am trying to relocate MySQL and a database to another hard drive on the same system. According to this page, http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html, I get the impression that I should be able to do this by copying the data files to the new MySQL installation. However, that doesn't seem to be sufficient as MySQL does not see the database that was copied over. - Looking for last minute shopping deals? Find them fast with Yahoo! Search. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.20.9/1290 - Release Date: 2/20/2008 8:45 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic sql in proc
Hi, just try like: mysql create procedure mi() - begin - declare done int default 0; - declare table_name varchar(50); - declare cur1 cursor for select tables.table_name from information_schema.tables where table_schema='test' and table_type='BASE TABLE' and engine='MyISAM'; - DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; - open cur1; - repeat - fetch cur1 into table_name; - set @table_name=table_name; - if not done then - set @stext=CONCAT(Alter table , @table_name, engine=InnoDB); - prepare smt from @stext; - execute smt; - deallocate prepare smt; - end if; - until done end repeat; - close cur1; - end; - | Query OK, 0 rows affected (0.03 sec) mysql call mi(); Query OK, 2 rows affected (0.01 sec) +---+ | version() | +---+ | 5.0.18| +---+ 1 row in set (0.00 sec) Thanks ViSolve DB Team. - Original Message - From: Bryan Cantwell [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 29, 2007 4:03 AM Subject: dynamic sql in proc I have the following proc... when I run it I get a response that says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1. I just want a programatic way to upgrade db engine to innodb where I don't know exactly what tables exist... DELIMITER $$ DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$ CREATE PROCEDURE `MYISAMtoINNODB`() BEGIN DECLARE done INT DEFAULT 0; DECLARE table_name VARCHAR(255); DECLARE cur1 CURSOR FOR select table_name from information_schema.tables where table_schema='firescope' and table_type='BASE TABLE' and engine='MyISAM'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO table_name; SET @table_name=table_name; IF NOT done THEN SET @stmt_text=CONCAT(ALTER TABLE , @table_name, ENGINE = InnoDB); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; UNTIL done END REPEAT; CLOSE cur1; END $$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.9.10/875 - Release Date: 6/27/2007 9:08 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequently MyISAM TABLE corruption.....Pls help
Hi, Pls try altering the engine type from 'MyISAM' to 'InnoDB' mysql alter table `tablename` ENGINE='InnoDB'; Just try out. I'm not sure. But there are issues over 'MyISAM' engine' and table corruption. Pls look into the link http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html about mysqleers comments. Thanks ViSolve DB Team. - Original Message - From: Nilnandan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 02, 2007 3:58 PM Subject: Re: Frequently MyISAM TABLE corruption.Pls help hello I am still facing the same problem.. MyISAM table corruptionI am getting following errors mysql use DB1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql check table tables1; +--+---+--+-+ | Table| Op| Msg_type | Msg_text | +--+---+--+-+ | DB1.tables1 | check | error| Found wrong record at 967175516 | | DB1.tables1 | check | error| Corrupt | +--+---+--+-+ 2 rows in set (8.53 sec) == ERROR LOG == Version: '5.0.27-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 070702 1:16:18 [ERROR] /usr/sbin/mysqld: Table 'tables1' is marked as crashed and should be repaired 070702 1:16:18 [ERROR] /usr/sbin/mysqld: Sort aborted 070702 1:19:43 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:19:54 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:20:05 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:20:22 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:21:24 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:22:16 [ERROR] Got error 127 when reading table './DB1/tables1' 070702 1:22:17 [ERROR] /usr/sbin/mysqld: Table 'tables1' is marked as crashed and should be repaired 070702 1:22:17 [ERROR] /usr/sbin/mysqld: Sort aborted Database changed mysql repair table tables1; +--++--+-+ | Table| Op | Msg_type | Msg_text | +--++--+-+ | DB1.tables1 | repair | info | Key 1 - Found wrong stored record at 967175516 | | DB1.tables1 | repair | info | Found block that points outside data file at 967175964 | | DB1.tables1 | repair | info | Found block that points outside data file at 967176068 | | DB1.tables1 | repair | info | Found block that points outside data file at 967176168 | | DB1.tables1 | repair | info | Found block that points outside data file at 967176296 | | DB1.tables1 | repair | info | Found block that points outside data file at 967176484 | | DB1.tables1 | repair | info | Found block that points outside data file at 967176588 | | DB1.tables1 | repair | info | Found block with too small length at 967177484; Skipped | | DB1.tables1 | repair | warning | Number of rows changed from 186252 to 186251| | DB1.tables1 | repair | status | OK | +--++--+-+ 10 rows in set (1 min 27.77 sec) PLS help me ASAP..Its frequently corrupted..It is very important table Regards, Nilnandan Joshi DBA-INDIA Nilnandan wrote: Hello Gerald, Data_lengthMax_data_length Index_length 596483288281474976710655 33758208 580 MB is table size and 32MB is index size. The default maximum MyISAM size is 4GB. Now, tell me where is an issue? regards, Nilnandan Joshi DBA-SDU Juriscape Gerald L. Clark-2 wrote: Nilnandan wrote: Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted How big is the index file? the data file? Has either reached the file size limit of your filesystem, or the default maximum MyISAM size? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED
Re: Problem about fulltext search.
Hi, There are some words which are drawn as Stop words [Words which are not searchable in Fulltext Database]. To know the list of stopwords, http://dev.mysql.com/tech-resources/articles/full-text-revealed.html. hello is a stopword, and hence your query fails to return rows. Thanks ViSolve DB Team - Original Message - From: Niu Kun [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, July 01, 2007 7:53 AM Subject: Re: Problem about fulltext search. Steve Edberg wrote: At 11:23 PM +0800 6/30/07, Niu Kun wrote: To quote from http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html ... words that are present in more than 50% of the rows are considered common and do not match. 'hello' appears in both (100%) of your records above, so it will not match. You need to insert more test data before MySQL has enough words to compute valid relevances. steve Thank you for your advice. And again, I've got the following command. mysql insert into test values(1,aaa); Query OK, 1 row affected (0.00 sec) mysql insert into test values(1,aaa); Query OK, 1 row affected (0.00 sec) mysql insert into test values(1,aaa); Query OK, 1 row affected (0.00 sec) mysql insert into test values(1,aaa); Query OK, 1 row affected (0.00 sec) mysql insert into test values(1,aaa); Query OK, 1 row affected (0.00 sec) mysql select * from test where match(name) against(hello); Empty set (0.00 sec) mysql select * from test where match(name) against(hello in boolean mode); Empty set (0.00 sec) mysql select * from test where match(name) against(world in boolean mode); +--+-+ | id | name| +--+-+ |1 | hello world | +--+-+ 1 row in set (0.00 sec) It seems that hello can't be found. But world can be found. I wonder if mysql has restrictions on the word to be found. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.9.14/883 - Release Date: 7/1/2007 12:19 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
Hi You can also use mysqlhotcopy. like $ mysqlhotcopy dbname destination directory the option --addtodest - does not delete/rename the directory if destination directory exists; instead it will append the data to the same. [$mysqlhotcopy --addtodest dbname destination directory] --allowold - create a new destination directory; if that already exists renames it to destination directory_old. [mysqlhotcopy --allowold dbname destination directory] mysqlhotcopy is only for myisam archive tables. While restoring, simply place the dumped directory into to mysql data dir. You can use either mysqldump or mysqlhotcopy. Thanks ViSolve DB Team - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. Hi All, What are the parameters that i need to use to take consistent backup of myisam tables using MYSQLDUMP. regards anandkl On 6/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
Hi, To take consistent backup, it is enough if you go for mysqldump. 1) $ mysqldump -u user -p dbname [table1,table2] dumpfile with path -- this itself takes consistent backup. mysqldump utility by default locks the table. 2) From one terminal issue mysql LOCK TABLES tables WRITE; From another table, issue $mysqldump -u user -p dbname [table1,table2] dumpfile with path and unlock the tables once dump completed. Thanks ViSolve DB Team - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED] Cc: MySQL mysql@lists.mysql.com Sent: Friday, June 15, 2007 4:30 PM Subject: Re: mysqldump for myisam tables. Thanks all for this response. This mysqlhotcopy take backup of .frm,.myd and .myi files, but my boss wants what mysqldump does. So, is it possible to take a consistent backup of myisam tables using mysqldump. Regards anandkl On 6/15/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi You can also use mysqlhotcopy. like $ mysqlhotcopy dbname destination directory the option --addtodest - does not delete/rename the directory if destination directory exists; instead it will append the data to the same. [$mysqlhotcopy --addtodest dbname destination directory] --allowold - create a new destination directory; if that already exists renames it to destination directory_old. [mysqlhotcopy --allowold dbname destination directory] mysqlhotcopy is only for myisam archive tables. While restoring, simply place the dumped directory into to mysql data dir. You can use either mysqldump or mysqlhotcopy. Thanks ViSolve DB Team - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. Hi All, What are the parameters that i need to use to take consistent backup of myisam tables using MYSQLDUMP. regards anandkl On 6/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Understanding mysql NULL handling ...
Hi, Because NULL is not a value. Operators [!=] must be suceeded by a value. These orphans must be selected in a correct way using is clause. Thanks ViSolve DB Team - Original Message - From: Mufaddal Khumri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 14, 2007 1:20 AM Subject: Understanding mysql NULL handling ... Hello, I am using Ver 8.41 Distrib 5.0.27, for apple-darwin8.5.1 on i686 == My table definition: == mysql show create table t1; +--- +--- + | Table | Create Table | +--- +--- + | t1| CREATE TABLE `t1` ( `id` int(11) NOT NULL auto_increment, `sid` int(11) default NULL, `tx` varchar(10) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--- +--- + 1 row in set (0.00 sec) == The data in my table: == mysql select * from t1; ++--+---+ | id | sid | tx| ++--+---+ | 1 |1 | hello | | 2 |2 | hello | | 3 | NULL | | | 4 | NULL | NULL | | 5 |5 | hello | ++--+---+ 5 rows in set (0.00 sec) == The query I execute: == mysql select * from t1 where sid != 2; ++--+---+ | id | sid | tx| ++--+---+ | 1 |1 | hello | | 5 |5 | hello | ++--+---+ 2 rows in set (0.00 sec) As you can see, the rows that had sid = NULL did not get returned in the results when i did ... where sid != ; Question: Is this behaviour correct and is in accordance to the SQL standard or is it specific to MySQL ? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.15/847 - Release Date: 6/12/2007 9:42 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to get Number of rows matched?
Hi AFAIK, before changing data, the old values are saved in the rollback segment. On saving the updated values, from the Buffer to the rollback segment/data files, --- it checks if there is any matched row that matches the condition. If found, then flags Matched. ---after filtering out the matched row, it check whether there is need to change the old value to new value. if need then flags Changed and rewrite the same in the datafile/rollback segment. Thanks ViSolve DB Team. - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 11, 2007 11:41 AM Subject: how to get Number of rows matched? Hi Experts, When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using mysql_affected_rows(), but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched = 1). mysql select * from test; +--+--+ | roll | s| +--+--+ |1 | new | +--+--+ 1 row in set (0.00 sec) mysql update test set roll = 1, s = 'new' where roll = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'new' where roll = 17; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'neww' where roll = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Cheers, Rajan No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to get Number of rows matched?
Hi Simple.. The query feedback will depict the matched changed numbers. mysql update test set roll = 1, s = 'new' where roll = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Thanks ViSolve DB Team. - Original Message - From: Ace [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, June 11, 2007 5:04 PM Subject: Re: how to get Number of rows matched? Ok..thanks! But my problem here is how to check if there were any matched rows when no rows were changed. mysql_affected_rows() will tell me the affected rows. Simmly, is there any routine using which one can know if there were any matched rows? Cheers, Rajan On 6/11/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi AFAIK, before changing data, the old values are saved in the rollback segment. On saving the updated values, from the Buffer to the rollback segment/data files, --- it checks if there is any matched row that matches the condition. If found, then flags Matched. ---after filtering out the matched row, it check whether there is need to change the old value to new value. if need then flags Changed and rewrite the same in the datafile/rollback segment. Thanks ViSolve DB Team. - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 11, 2007 11:41 AM Subject: how to get Number of rows matched? Hi Experts, When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using mysql_affected_rows(), but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched = 1). mysql select * from test; +--+--+ | roll | s| +--+--+ |1 | new | +--+--+ 1 row in set (0.00 sec) mysql update test set roll = 1, s = 'new' where roll = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'new' where roll = 17; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'neww' where roll = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Cheers, Rajan No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM
Re: Max columns in a tabel in MyISAM storage engine
Hi, Might be column indexing have restriction over number of columns to be indexed, but for number of columns, hope you can overrider with the variables avg_row_length and max_rows during create/alter table. The length/size of the table depends on the file system(maximum file size defined by the OS). Try giving maximum value for avg_row_length. Thanks ViSolve DB Team - Original Message - From: Fabian Köhler [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 17, 2007 4:34 AM Subject: Max columns in a tabel in MyISAM storage engine Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.4.0/762 - Release Date: 4/15/2007 4:22 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum with update
Hi, Yes it is possible to use sum with Update, subject to the constraint -table to be updated must not present in the FROM clause of the select stmt. But in your query, the table to be updated is present in the FROM clause. Hence update doesn't support that and have to go for some procedures.. Thanks ViSolve DB Team - Original Message - From: Ahmad Al-Twaijiry [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Saturday, February 17, 2007 11:00 PM Subject: sum with update Hi Everyone I have a question regarding SUM and Update. is it possible to use SUM with Update ? for example I have 3 tables table_1: idT1 Price 120 230 350 420 table_2: idCust Total idT1 2 3011 2 2 221 1 4 2 2 3 31 1 2 302 4 2 30 1 table_3: idCust Account 2 200 3 19 399 I want to update table_3.Account**IF** (table_3.Account + SUM(table_2.Total* table_1.Price) ) small than 1000 for each customer (idCust) is this possible using only SQL or I have to do it in programming ? Thanks -- echo Hello World :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment
Hi, The thing is you want to add comments to column, which is presently null, as per SHOW FULL COLUMNS FROM table; Then add COMMENT 'your comment for the column name' , for the columns you want comments. For Instance, mysql create table test (topic varchar(10)default null COMMENT 'topic for the forum', title varchar(10) default null COMMENT 'title for the forum topic'); mysql show full columns from test\G *** 1. row *** Field: topic Type: varchar(10) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: topic for the forum *** 2. row *** Field: title Type: varchar(10) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: title for the forum topic 2 rows in set (0.01 sec) Hope Useful ! and Welcome for your interest. Thanks ViSolve DB Team. - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, February 09, 2007 6:54 AM Subject: Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment Hello, thanks again to ViSolve DB Team. so, it turns out that for MySQL4.1, il will have to built a sql file, built with script (shell or perl). [ note that so far I use MySQL 4.1 on production server, and I only test features on MySQL 5 on an other machine linux Debian]. at last, my LAST QUESTION concerns column Comment In MySQL 5, I can do : mysql select column_name,COLUMN_COMMENT from information_schema.columns where table_schema=a_base and table_name='a_tab'; in MySQL 4.1 , the equivalent would be : mysql select distinct Column_name from columns_priv where Db='a_base' and Table_name='a_tab'; AND THERE IS NO COMMENT. but, from the page : http://dev.mysql.com/doc/refman/4.1/en/charset-show.html it seems that one could use Comment mysql SHOW FULL COLUMNS FROM a_tab; displays : Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | I cannot find how to set these Comment ? An idea ? thanks. -Gilles- On Thu, 8 Feb 2007, ViSolve DB Team wrote: Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-table UPDATE unexpected result
Hi, Try this, mysql update atable,btable set atable.b=atable.b+(select sum(b) from btable where btable.a=atable.a) where atable.a=btable.a; mysql select * from atable; +--+--+ | a| b| +--+--+ | 1| 10 | | 2| 15 | | 3| 23 | | 4| 10 | +--+--+ 4 rows in set (0.00 sec) Thanks ViSolve DB Team. - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Thursday, February 08, 2007 3:57 PM Subject: Multiple-table UPDATE unexpected result Dear listmembers On mysql version 4.1.13 I execute a query of this type: UPDATE a LEFT JOIN b ON a.col = b.col SET a.x = a.x + b.y WHERE b.col IS NOT NULL; I expect that column a.x is updated for every match in the join but this is not the case. Table a is updated for the first match only as in this example: mysql use test; Database changed mysql create table atable ( a int, b int); Query OK, 0 rows affected (0.00 sec) mysql insert into atable values(1,10),(2,10),(3,10),(4,10); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 10 | |3 | 10 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) mysql create table btable (a int, b int); Query OK, 0 rows affected (0.01 sec) mysql insert into btable values(2,5),(3,6),(3,7); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from btable; +--+--+ | a| b| +--+--+ |2 |5 | |3 |6 | |3 |7 | +--+--+ 3 rows in set (0.00 sec) mysql update atable left join btable on atable.a = btable.a set atable.b = atable.b + btable.b where btable.a is not null; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 16 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) However, the result I would like to achieve is (manually edited for the purpose of explanation): mysql select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 23 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) i.e. row 3 of atable should be updated 2 times, adding 6 and 7, as there are 2 rows in btable where column a is = 3. How can I do this? Any help is apreciated. Thomas Spahni -- 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: revoke SELECT on a column [ MySQL 4.1 ] + column privileges
Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql. Hence invoke it through script. like the one: create procedure gg(IN c varchar(20)) BEGIN declare cnt int; declare i int default 1; declare col_name varchar(30); select count(*) into cnt from information_schema.columns where table_schema=the_base and table_name='t100'; select cnt; while (i=cnt) do select column_name into col_name from information_schema.columns where table_schema='the_base' and table_name='t100' and ordinal_position=i; select col_name; grant select(col_name) on forum.catagory to 'xx'@localhost identified by 'mysql'; If col_name = c then revoke select(col_name) on forum.catagory from 'xx'@localhost identified by 'mysql'; end if; set i=i+1; end while; end; mysql call g(hide_this_col); Thanks ViSolve DB Team. - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 07, 2007 9:26 PM Subject: Re: revoke SELECT on a column [ MySQL 4.1 ] + column privileges hello, first thanks to ViSolve DB Team, and since then, my question turns out to be : in the base that contains a table of 100 columns, I want to disable SELECT on only 1 column hide_this, how to apply column privileges using a loop in mysql, that could do : for each column in the_base.t100 where column_name is NOT hide_this do GRANT SELECT(column_name_n) ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; done Or should I build a script to create sql commands for that ? thanks, You have applied TABLE level GRANT PRIVILEGES and tried to REVOKE that with COLUMN PRIVILEGES. Hence the error. To Fix it, apply column privileges --- mysql GRANT SELECT(hide_this) ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql select * from information_schema.column_privileges; mysql REVOKE SELECT(hide_this) ON the_base.t100 from 'a_user'@'localhost' identified by 'a_passwd'; Note: Always TABLE PRIVILEGES override COLUMN PRIVILEGES Thanks ViSolve DB Team - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 11:05 PM Subject: revoke SELECT on a column [ MySQL 4.1 ] Hello, In a table [say t100], having 100 columns, I want to allow the select on all columns but 1. I tried to do this by granting all columns in the table t100, of the base, then revoke SELECT on the column hide_this, but this doesn't work. mysql GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql revoke SELECT (hide_this) on the_base.t100 from 'a_user'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'a_user' on host 'localhost' on table 'current' Is there a turn around, or should grant the select on the 99 other columns -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ]
Hi, No... You have applied TABLE level GRANT PRIVILEGES and tried to REVOKE that with COLUMN PRIVILEGES. Hence the error. To Fix it, apply column privileges --- mysql GRANT SELECT(hide_this) ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql select * from information_schema.column_privileges; mysql REVOKE SELECT(hide_this) ON the_base.t100 from 'a_user'@'localhost' identified by 'a_passwd'; Note: Always TABLE PRIVILEGES override COLUMN PRIVILEGES Thanks ViSolve DB Team - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 11:05 PM Subject: revoke SELECT on a column [ MySQL 4.1 ] Hello, In a table [say t100], having 100 columns, I want to allow the select on all columns but 1. I tried to do this by granting all columns in the table t100, of the base, then revoke SELECT on the column hide_this, but this doesn't work. mysql GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql revoke SELECT (hide_this) on the_base.t100 from 'a_user'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'a_user' on host 'localhost' on table 'current' Is there a turn around, or should grant the select on the 99 other columns ? regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- 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: How find duplicate entries
Hi , Try this query... SELECT COUNT(*), column1, column2 FROM tablename GROUP BY column1, column2 HAVING COUNT(*)1; Thanks, ViSolve DB Team - Original Message - From: Tomás Abad Fernández [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 9:57 PM Subject: How find duplicate entries Any can tell me a slq to find duplicate entries in a table? Thanks, Tomás No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.27/671 - Release Date: 2/5/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql limits
Hi, It can handle. You can extend the file size also. File size limit depends on the OS. Obviously the performance depends on both the processor speed and the memory. Table optimization,indexing will improve performance. Thanks ViSolve DB Team - Original Message - From: kalin mintchev [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, February 05, 2007 4:07 PM Subject: Re: mysql limits thanks... my question was more like IF mysql can handle that amount of records - about 100 million... and if it's just a question of cpu power and memory? Hi, The limit for the table can be set when you create the table itself. the MAX_ROWS and AVG_ROW_LENGTH variables (m X n matrix) will decide the table size. MAX_ROWS limts the maximum number of rows in that table. The AVG_ROW_LENGTH variable decides the length of the row. The specified value can be used by a single column itself or depends on the size of the columns. Thanks ViSolve DB Team. - Original Message - From: kalin mintchev [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 05, 2007 9:14 AM Subject: mysql limits hi all... i just wanted to ask here if somebody has experience in pushing the mysql limits... i might have a job that needs to have a table (or a few tables) holding about a 100 million records. that's a lot of records is there any limitation of some kind that wouldn;t allow mysql to handle that kind of amounts or it all depends on memory and cpu... or how are the searches - speed and otherwise - affected by such numbers? thanks -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join question
Hello, Try this... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) where a.rhrqsid = 101 or a.rhrssid = 101 Thanks, ViSolve DB Team - Original Message - From: KMiller [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 8:37 AM Subject: outer join question This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on multiple fields in several tables?
Hi, i.e. you want to retrieve all the records of john smith from 3 or 4 tables. is it? if so, use table alias. domainnames cannot be used directly.--try with mysql select * from personal p, payroll r,department d where p.ename=johnsmith or r.ename=johnsmith or d.ename=johnsmith; Thanks ViSolve DB Team. - Original Message - From: boll [EMAIL PROTECTED] To: MySQL General Mailing List mysql@lists.mysql.com Sent: Sunday, February 04, 2007 10:06 PM Subject: select on multiple fields in several tables? Hello- I'm working with an unfamiliar application, trying to figure out where my data is going. Is it possible to form a query to select from all the columns in several tables at once? Something equivalent to: SELECT FROM * WHERE * = 'john smith'; If that's not possible I'd still like to be able to search each table at once without specifying the column names: SELECT FROM table name WHERE * = 'john smith'; If that can't be done, can a sub query find the column names to be selected on? Basically, I'm trying to avoid doing separate selects on each column in each table. This is using MySQL 5.0.27 . Thanks for any suggestion or alternative methods! -John -- 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: mysql limits
Hi, The limit for the table can be set when you create the table itself. the MAX_ROWS and AVG_ROW_LENGTH variables (m X n matrix) will decide the table size. MAX_ROWS limts the maximum number of rows in that table. The AVG_ROW_LENGTH variable decides the length of the row. The specified value can be used by a single column itself or depends on the size of the columns. Thanks ViSolve DB Team. - Original Message - From: kalin mintchev [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 05, 2007 9:14 AM Subject: mysql limits hi all... i just wanted to ask here if somebody has experience in pushing the mysql limits... i might have a job that needs to have a table (or a few tables) holding about a 100 million records. that's a lot of records is there any limitation of some kind that wouldn;t allow mysql to handle that kind of amounts or it all depends on memory and cpu... or how are the searches - speed and otherwise - affected by such numbers? thanks -- 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: max_allowed_packet in my.ini
Hi, AFAWK, if you modify any of the variables in the .cnf, obviously have to restart mysql. no other go. To transmit between client and server you need to configure both the client and the server. Through .cnf you are configuring the server variable and for client you can go for either mysql or mysqldump. shell mysql --max_allowed_packet=32M or using mysql set @@SESSION.max_allowed_packet=32M; mysql show variables like max%; Thanks ViSolve DB Team. - Original Message - From: abhishek jain [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 05, 2007 11:37 AM Subject: Re: max_allowed_packet in my.ini On 2/3/07, abhishek jain [EMAIL PROTECTED] wrote: Hi friends, I am using mysql 5.0.23-nt on windows, i have to store large binary data in database, i have used setting like max_allowed_packet=16M in mysqld section of my.ini . Now i have a poblem that i want to create a setup so that the entry gets added itself into the my,ini , reason being i need to create many setups on diff. machine and i do not want to search the mysqld section and write the line and restart mysql . I can however would like to do via mysqladmin or so if it is possible as i can invoke that via mine .net program Would appreciate your comments on this, Thanks . Abhishek jain Hi Friends. Pl. reply, Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
Hi,
From MySQL 4.1, there is a support for mysqldump --compatible option.
There is a safe/cool dump for your table:
Try lik:
shell mysqldump -u dev -p visolvetestdb credits --compatible=postgresql
/home/test/ps.sql
And also,
By default tables are dumped in a format optimized for MySQL.
Legal modes are: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2,
maxdb, no_key_options, no_table_options, no_field_options. One can use
several modes separated by commas.
Thanks
ViSolve DB Team
- Original Message -
From: Jim C. [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, February 02, 2007 10:31 PM
Subject: MySQL to Postgres
I'm having to move some data from MySQL to Postgres. I used mysqldump
--compatible=postgresql, but the compatibility is extremely lacking. I'm
actually rather shocked that there doesn't seem to be a common open
standard (XML?) in use for this sort of thing.
Anyway, I'm having to line by line, table by table it.
I came across this statement and was wondering if anyone knew how I can
convert it to Postgres:
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role
set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest')
NOT NULL default ''
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
Note that it accepts everything until it hits the 'role' line. Errors
found below:
CREATE TABLE
ERROR: table credits does not exist
ERROR: syntax error at or near ( at character 183
Jim C.
--
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: How to SELECT rows closest to value
Hi, Try like this (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC, longitude ASC) a WHERE a.latitude=givenvalue AND a.longitude=givenvalue LIMIT 0,5) UNION (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC, longitude ASC) b WHERE b.latitudegivenvalue AND b.longitudegivenvalue LIMIT 0,5) ; Thanks, ViSolveDB Team - Original Message - From: M5 [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 03, 2007 7:30 AM Subject: How to SELECT rows closest to value Simply put, I have a table of ~800,000 records containing, among other things, latitude and longitude values. Given a lat/lng pair, I would like to SELECT from this table the 10 rows containing latitude/ longitude values closest to the given lat/lng pair. Possible? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.19/663 - Release Date: 2/1/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query question
Hi,, The Update query of yours will do fine.. otherwise try using string functions [instr()] like mysql update inventory_items set name='necklace' where instr(description,'necklace')0; Thanks ViSolve DB Team. - Original Message - From: Jerry Jones [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 03, 2007 8:42 AM Subject: Update query question I am new to mysql. I am trying to do a simple update query to update a field based on the contents of another field in the same table. Here is what I have. update inventory_items set name = necklace where description like %necklace%; I am not sure what is wrong. select * from inventory_items where description like %necklace%; works just fine. I cannot find much online to help me out with this. To summarize, I need to update the name field to necklace when the word necklace shows up anywhere in the description field. Thanks. -- 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: Update queries
Try using CONCAT() ; mysql update inventory_items set image_location=concat(item_number,'-1.jpg'); Thanks ViSolve DB Team. - Original Message - From: Jerry Jones [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 03, 2007 12:00 PM Subject: Update queries I need to run a query that will take the data from item_number field, add -1.jpg to the end of it, and store it in image_location I have tried update inventory_items set image_location = item_number + -1.jpg; but that just places the item number into the field, without the text in quotes. How do I append some fixed text to another field entry and place it into a different field? -- 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: rounding digits after decimal sign
Hi, If you want to round your value to the specified no. of digits, use round(). Else if you want to extract the values without rounding use truncate(). mysql select round(blustat,2) from parts; [the value will get rounded to the nearest decimal] or mysql select truncate (blustat,2) from parts; [no rounding, simply extract only the value of specified digits] For More info:http://www.keithjbrown.co.uk/vworks/mysql/mysql_p9.php Thanks, ViSolve DB Team - Original Message - From: Dimitar Vasilev [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 01, 2007 1:40 AM Subject: rounding digits after decimal sign Hello, Can anyone point me to a section of manual or link how to reduce digits after a decimal sign? I have a table mysql desc part; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | kod | varchar(255) | YES | | NULL| | | bulstat | double(15,5) | YES | MUL | NULL| | | Land| varchar(3) | YES | MUL | NULL| | | ID | varchar(15) | YES | MUL | NULL| | | IME | varchar(100) | YES | MUL | NULL| | | VALUE | double(15,5) | YES | | NULL| | | CNT | smallint(5) | YES | | NULL| | | Date| datetime | YES | | NULL| | +-+--+--+-+-+---+ and in bulstat column i have digits like 831690750.0 which i would like round to the last whole digit. Also could someone give a hint how to recode digits in order to anonymize data. My end goal is to export the dataset into a social network software for my thesis. Thanks, -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT(int_col, string_col) and charset and collation problems
Hi, It is that, what you think a binary is ,...is indeed a binary. As per the manual, If a string input or function result is a binary string, the string has no character set or collation. so the resultant 'binary' is expected. if u want the resultant as: mysql select charset(concat(tt,CONVERT(id USING latin1))) from test; Thanks ViSolve DB Team - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: list mysql mysql@lists.mysql.com Sent: Wednesday, January 31, 2007 5:31 PM Subject: CONCAT(int_col, string_col) and charset and collation problems Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- 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: CONCAT(int_col, string_col) and charset and collation problems
Hi, I know about CONVERT but I wanted to check character set of 'tt' column and this result use in CONVERT. Something like CONVERT(id USING CHARSET(Name)) which doesn't work. It wont. the syntax is -- CONVERT(expr USING transcoding_name); its the name of the transcode and not an expr. I just wonder why CONCAT(1, ',', Name) works OK even if CHARSET(1) is binary string and CONCAT(id, ',', Name) doesn't It does. mysql select concat(1,',',tt) from test; +--+ | concat(1,',',tt) | +--+ | 1,a | | 1,b | | 1,c | +--+ 3 rows in set (0.00 sec) mysql select concat(id,',',tt) from test; +---+ | concat(id,',',tt) | +---+ | 1,a | | 2,b | | 3,c | +---+ 3 rows in set (0.00 sec) Thanks ViSole DB Team. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple alternate query
Hi, You can use select * from table1 where id in (1,3,4,5); Thanks, ViSolve DB Team - Original Message - From: Miguel Vaz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, January 28, 2007 7:51 AM Subject: Re: simple alternate query I am dumb, so sorry: select * from table1 where id=1 or id =3 and id =4 Have to go spank myself until i bleed now, thanks. Miguel At 02:15 28-01-2007, Miguel Vaz wrote: Hi, I Cant get a simple query to work, heres what i am looking for: table1 -- id name 1 peter 2 john 3 mary 4 lisa 5 me I need a select that always retrieves id 1 and an interval of my choice: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.10/651 - Release Date: 1/24/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL reporting an error with subquery query
Hi, In your SQL statement you have used subquery. The subquery feature is introduced in MySQL version 4.1 and later. Thats why you are getting the Syntax error while running subquery in v4.0.24. Thanks, ViSolve DB Team - Original Message - From: A Blossom of Paradise [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 7:07 AM Subject: MYSQL reporting an error with subquery query Hello Fellow MySqueelers!, SELECT version() reveals... 4.0.24-nt-max-log I have looked at the following query a hundred times, and cannot find how MYSQL can report a syntax error to me. query string is... SELECT RAND() AS `RAND`, `prod`.`id` FROM `prod` JOIN `pack` ON `prod`.`id` = `pack`.`pid` WHERE `pack`.`did` = 3 AND NOT ( `prod`.`id` IN ( SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `oitm1` ON `ogrp1`.`id` = `oitm1`.`gid` JOIN `pack` AS `pack1` ON `oitm1`.`pid` = `pack1`.`id` WHERE `ogrp1`.`raid` = 9 AND `ogrp1`.`wid` = 3 AND `ogrp1`.`rdate` DATE_SUB( CURDATE(), INTERVAL 3 MONTH ) ) ) ORDER BY `RAND` LIMIT 3 Error number is 1064 Error message is 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `pack1`.`pid` FROM `ogrp` AS `ogrp1` JOIN `oitm` AS `o' any suggestions before i squeel? -wh No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/634 - Release Date: 1/17/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: low-priority-updates and innodb tables
hi, AFAIK, if we start mysqld with --low-priority-updates, it sets table updation a lower priority than the SELECT statements, irrespective of storage engines. hence it will affect the priority of the update operation. Ref: http://mysql.justdn.org/doc/refman/5.1/en/table-locking.html - Original Message - From: Vitaliy Okulov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 22, 2007 7:27 PM Subject: low-priority-updates and innodb tables Здравствуйте, mysql. Hi all. I want to ask about low-priority-updates and innodb tables. Does low-priority-updates=1 affect on priority of select or update query on innodb type tables? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query about MySQL
Hi, 1) In MySQL, the size of the table limits to the storage engine and the file system size. [minimum -default -2GB] 2) As of our understanding, keeping in multiple tables is a good practice, that too with perfect normalization. If multiple tables, table sizes are in hand, limit of file system. File volume size is a notable parameter when moving for multiple tables. 3) Regular backups. Enable log-bin; in case of recovery, use mysqlbinlog. Thanks ViSolve DB Team - Original Message - From: Raman Kheterpal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, January 22, 2007 2:46 PM Subject: Query about MySQL Hi All I got your mail id from one of the forums. can u plz tell me tht wht is the capacity of storage of MySQL DB. acutally we are in the process of making a project on LAMP technology, but the databse is too large..say 1722 records this much of records will remain in one table only... Can you please tell me . - Is mysql is able to manage this huge amount of data. - Can we put this much of data in single table database OR keeping the data in multiple tables is the good practice. - What precautions I have to taking while managing this much of data in MySQLetc etc kindly see the matter n reply me in this regards Thanx in advance raman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup: Copy MYSQL Directory or use MysqlDump
Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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 - MySQL 5 - mysqld using a lot of memory consistently
Hi, Here, threads_connected is considerable and below the preset value. The threads_connected and threads_running are the good indicators to see how loaded the server is. In your case it is good numbers. So use 'iostat'/relavant utility to monitor the DB activity. Also threads_created is more, which should be low. so to average it increase the thread_cache size to some 64 or more. Ref: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html Thanks ViSolve DB Team - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 11:59 AM Subject: Urgent - MySQL 5 - mysqld using a lot of memory consistently Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Request problem (with \\)
Hi Gabriel,
Try as:
mysql select * from forum where topoc like %%;
To search for '\', specify it as ''; this is because the backslashes are
stripped once by the parser and again when the pattern match is made,
leaving a single backslash to be matched against. (Exception: At the end of
the pattern string, backslash can be specified as '\\'. At the end of the
string, backslash stands for itself because there is nothing following to
escape.)
Ref: http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html
Thanks
ViSolve DB Team.
- Original Message -
From: Gabriel Linder [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, January 19, 2007 9:43 PM
Subject: Request problem (with \\)
Hello list,
I am currently trying to fix a bug in a search function with a request
like this one :
select * from forum where topic like '%[...]%' ;
where [...] is a string escaped by mysql_real_escape_string (C API) and
topic is a varchar field (not null).
It works, but there is a bug if someone is searching the character
backslash only ('\'), so the request become :
select * from forum where topic like '%\\%' ;
and it returns only topics who have a '%' in them, not a '\'. It is the
same result as if I were doing :
select * from forum where topic like '%\%' ;
To get the topics with a '\' (but it returns only the topics that ends
with a '\'), I must do :
select * from forum where topic like '%\\' ;
So it seems to me that the ending % is escaped even with '\\'. Is this a
normal behaviour ? Or am I missing something ?
Here are some infos about the server version, might be useful :
version = 4.0.20-standard
version_comment = Official MySQL-standard binary
version_compile_os = linux
--
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: Table update
Hi Update will never support group by clause, only supports group functions; Try as: Update teams t inner join rider_team as rt on (rt.team_id=t.id) inner join participants as p on (p.rider_id=rt.rider_id) inner join races as r on (r.id=p.race_id) set t.created = ( select min(starttime) from races); If 'id' also to be updated, then try as: Update teams t inner join rider_team as rt on (rt.team_id=t.id) inner join participants as p on (p.rider_id=rt.rider_id) inner join races as r on (r.id=p.race_id) set t.created = ( select min(starttime) from races), t.id=(select id from race where starttime=(select min(starttime) from race)) ; Thanks ViSolve DB Team. Original Message - From: Jørn Dahl-Stamnes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 18, 2007 3:13 AM Subject: Table update I want to update new column in a table with data from other tables. The following query give me the data: select t.id,min(r.starttime) from teams as t inner join rider_team as rt on (rt.team_id=t.id) inner join participants as p on (p.rider_id=rt.rider_id) inner join races as r on (r.id=p.race_id) group by t.id; Is it possible to write a query that do a: update teams set created=min(r.starttime) ... which updates all records in the teams table based on first query? Thanks BTW: mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- 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: how to take advantage of STR_TO_DATE
Hi,
STR_TO_DATE() simply converts the given format string to datetime value. So
to change the format of the date dispaly, go for DATE_FORMAT().
For Instance,
mysql select DATE_FORMAT('2007/10/01','%d/%m/%Y');
or
mysql select DATE_FORMAT(datecolumn,'%d/%m/%Y') from table1;
Thanks
ViSolve DB Team
- Original Message -
From: Gilles MISSONNIER [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 11, 2007 12:49 AM
Subject: how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the load data infile procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert day/month/year_4digit into the standard MySQL format
that is year_4digit-month-day, then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---+
| 1999-10-15|
+---+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
15/10/1999
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
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: Does Update allow for aliases?
Hi Reina, Try like: mysql UPDATE maindb o,altdb ao set o.price =ao.price where o.id=ao.id; This will do good. Thanks ViSolve DB Team - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 10, 2007 10:08 PM Subject: Does Update allow for aliases? I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. Thanks, Richard Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: store spatial data in mysql
Hi, LOAD DATA INFILE is capable of loading only the internal representation of the data. Hence load the Well-Known Text (WKT) representation as a text column using LOAD DATA INFILE into a text column and then shift the data geometry column using an UPDATE. Hope this will do.. Thanks ViSolve DB Team - Original Message - From: CrazyWind [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 08, 2007 7:10 PM Subject: Re: store spatial data in mysql Thanks . I have read these doucments, but if i want store a .shp file, How should I do? Convert it ? Wating~~~ ViSolve DB Team-2 wrote: Hi, More info on spatial data, http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-columns.html http://dev.mysql.com/doc/refman/5.0/en/populating-spatial-columns.html Thanks ViSolve DB Team -- View this message in context: http://www.nabble.com/store-spatial-data-in-mysql-tf2938527.html#a8217966 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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: Enum issue
Hi,
Try with,
, `consent` enum ('','Y','N','P') ,
.mysql desc table;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
.
| consent | enum('','Y','N','P') | YES | | NULL| |
+---+--+--+-+-+---+
Thanks
ViSolve DB Team
- Original Message -
From: Olaf Stein [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Wednesday, January 10, 2007 3:44 AM
Subject: Enum issue
Hi All
If I have a column
`consent` enum('Y','N','P') default NULL,
And I try to insert 'NULL' I get this error:
Warning: Data truncated for column 'consent' at row 1
What is the problem there?
What I am doing is moving data from one table to another with a python
script so I have to assign 'NULL' to the variable in the insert string (at
least to my knowledge) because python retrieves None (type type
'NoneType') when querying a NULL value.
Help is appreciated.
Olaf
--
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: MYSQL REGEXP help
Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- 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: store spatial data in mysql
Hi, More info on spatial data, http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-columns.html http://dev.mysql.com/doc/refman/5.0/en/populating-spatial-columns.html Thanks ViSolve DB Team - Original Message - From: CrazyWind [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 08, 2007 4:02 PM Subject: store spatial data in mysql Hi everyone, I'm beginner in Gis,I want to establish a spatial database using MySQL. But I don't kown hoe to. Could someone give me the detail steps, or give me a databse that had spatial data? thanks. -- View this message in context: http://www.nabble.com/store-spatial-data-in-mysql-tf2938527.html#a8215529 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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: MYSQL REGEXP help
Hi, Try with mysql select 'oer bv' REGEXP '(^b|[[:blank:]])(!?v|$v)'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 5:40 PM Subject: Re: MYSQL REGEXP help Hello, this doesn't work: mysql SELECT 'oer bv' REGEXP '[b|^b](!?[v$|v])'; ++ | 'oer bv' REGEXP '[b|^b](!?[v$|v])' | ++ | 1 | ++ 1 row in set (0.00 sec) He shouldn't select this one, because it says 'bv' and no other b Mike ViSolve DB Team schreef: Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- 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: How to access an array variables in a single query
Hi,
IN operator will do.
For Instance.
SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
Thanks
ViSolve DB Team.
- Original Message -
From: VenuGopal Papasani [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Cc: bharath kumar [EMAIL PROTECTED]
Sent: Saturday, January 06, 2007 2:07 PM
Subject: How to access an array variables in a single query
Dear all,
I have got an array variable say a[]={i1,i2,i3} now i need to get
the values from the database that matches i1,i2,i3 for example
select * from tablename where i1=a[1] and
simillaryly to a[2] and a[3] but my list is too big one more option i can
write it through loop but it takes time.Is there any possibility of
writing
a single query to retrieve all that data.
Awaiting your reply
Thanks and Regards,
venu
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On Duplicate Key Update question
Hi, From your query, understood that you want to retain old qty and new qty; result in another field. Try with, INSERT INTO TABLE1 (id,newqty) values (6,300) ON DUPLICATE KEY UPDATE totqty=oldqty+newqty, oldqty=newqty; Thanks, ViSolve DB Team - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 06, 2007 4:10 AM Subject: On Duplicate Key Update question I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to access an array variables in a single query
Hi,
If the values to be matched against were from a table, why can't subquery be
used.
Like, SELECT * FROM TABLE1 WHERE id IN (select id from table2);
Otherwise if numeric constants, then AFAIK procedure -loops would do.
Thanks
ViSolve DB Team.
- Original Message -
From: VenuGopal Papasani [EMAIL PROTECTED]
To: ViSolve DB Team [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; bharath kumar [EMAIL PROTECTED]
Sent: Saturday, January 06, 2007 2:52 PM
Subject: Re: How to access an array variables in a single query
but that is not static for 3 variables what if array contains 1000
elements
in cannot be used in such cases
Thanks and Regards,
venu
On 1/6/07, ViSolve DB Team [EMAIL PROTECTED] wrote:
Hi,
IN operator will do.
For Instance.
SELECT * FROM TABLE1 WHERE id IN ('i1','i2','i3');
Thanks
ViSolve DB Team.
- Original Message -
From: VenuGopal Papasani [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Cc: bharath kumar [EMAIL PROTECTED]
Sent: Saturday, January 06, 2007 2:07 PM
Subject: How to access an array variables in a single query
Dear all,
I have got an array variable say a[]={i1,i2,i3} now i need to
get
the values from the database that matches i1,i2,i3 for example
select * from tablename where i1=a[1] and
simillaryly to a[2] and a[3] but my list is too big one more option i
can
write it through loop but it takes time.Is there any possibility of
writing
a single query to retrieve all that data.
Awaiting your reply
Thanks and Regards,
venu
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
ధన్యవాదములతో,
వేణుగోపాల్ పాపసాని.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SP not seeing INSERTs within WHILE loop
Hi, Your procedure returned correct numbers. Does misrepresentation happens for all the runs or adhoc. Thanks ViSolve DB Team. - Original Message - From: brian stone [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 03, 2007 8:55 AM Subject: SP not seeing INSERTs within WHILE loop I have an issue where inserts are not being seen properly while in a SP. The goal is to track payments made by customers, payment distribution. I narrowed down the issue to the below, so I was able to exclude many columns and other tables in hopes it is simple enough to get some help. I have a payment table and a payment distribution table. By joining the two, I can determine how much money is left for a payment. I can then apply those monies to a customer charge. I created a simple procedure that loops and distributes $1 5 times. Problem: the SELECT returns $10 twice in a row. Unless I am missing something, it should be returning $9 on the second SELECT. After the second select, it returns 8, then 7, etc... For some reason, that second select is wrong? CREATE TABLE payment ( payment_id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL(15,5), date_dist DATETIME NULL DEFAULT NULL -- date fully distributed ); CREATE TABLE payment_dist ( payment_id INT NOT NULL DEFAULT 0, amount DECIMAL(15,5) ); -- make a $10 payment INSERT INTO PAYMENT VALUES (DEFAULT, 10.00, DEFAULT); DROP PROCEDURE p; DROP FUNCTION safe_decimal; delimiter // CREATE FUNCTION safe_decimal(d DECIMAL(15,5)) RETURNS DECIMAL(15,5) BEGIN IF d IS NULL THEN RETURN 0; END IF; RETURN d; END; // delimiter ; delimiter // CREATE PROCEDURE p () BEGIN DECLARE count INT DEFAULT 0; WHILE count 5 DO SELECT payment.payment_id AS payment_id, (payment.amount - SUM(safe_decimal(d.amount))) AS amount FROM payment LEFT JOIN payment_dist d ON payment.payment_id = d.payment_id WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1; SET count = count + 1; INSERT INTO payment_dist (payment_id, amount) VALUES (1, 1.00); END WHILE; END; // delimiter ; CALL p(); mysql CALL p(); ++--+ | payment_id | amount | ++--+ | 1 | 10.0 | ++--+ 1 row in set (0.00 sec) ++--+ | payment_id | amount | ++--+ | 1 | 10.0 | ++--+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 8.0 | ++-+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 7.0 | ++-+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 6.0 | ++-+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql SELECT payment.payment_id AS payment_id, - (payment.amount - SUM(safe_decimal(d.amount))) AS amount - FROM payment LEFT JOIN payment_dist d - ON payment.payment_id = d.payment_id - WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1; ++-+ | payment_id | amount | ++-+ | 1 | 5.0 | ++-+ 1 row in set (0.00 sec) I end up with the correct number but am getting the wrong result after the first insert. any ideas what is happening here? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err1114 The table 'sbtest' is full
Hi, 1. The table full error was due to temporary table size being large, which is created during transaction processing. Try setting SQL_BIG_TABLES, to avoid over-allocating memories for other queries. 2. Since Innodb engine, the innodb tablespace might run out of space. Try to increase the size of the tablespace,by adding another datafile, setting to innodb_autoextend_increment system variable. 3. Since you are doing with 2000 threads, try increasing thread_stack size.Hope with this FATAL error can be fixed. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 8:08 AM Subject: Err1114 The table 'sbtest' is full I used sysbench to test mysql with 2000 threads and then sysbench threw some alerts below: ALERT: failed to execute mysql_stmt_execute(): Err1114 The table 'sbtest' is fullFATAL: database error, exiting...I don't know what does it mean because sbtest is not full.mysql show create table sbtest\G*** 1. row *** Table: sbtestCreate Table: CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL auto_increment, `k` int(10) unsigned NOT NULL default '0', `c` char(120) NOT NULL default '', `pad` char(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `k` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql show table status from sbtest\G*** 1. row *** Name: sbtest Engine: InnoDBVersion: 10 Row_format: Compact Rows: 100092 Avg_row_length: 225Data_length: 22593536Max_data_length: 0 Index_length: 1589248 Data_free: 0 Auto_increment: 11Create_time: 2007-01-04 09:25:16Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: InnoDB free: 58368 kB1 row in set (0.01 sec)[EMAIL PROTECTED] mysql]# ls -l ibdata1 -rw-rw 1 mysql mysql 320864256 Jan 4 09:27 [EMAIL PROTECTED] mysql]# cat /etc/my.cnf[mysqld]log-bin=masterlogmax_connections=3000innodb_data_file_path=ibdata1:306M:autoextendmax_prepared_stmt_count=32765#logquery_cache_size=16M#innodb_buffer_pool_size=512M#key_buffer_size=512Mserver-id=1 binlog-do-db=backup master-host=172.20.23.28master-user=backupmaster-password=backupmaster-port=3306 mysql show variables like 'innodb_data%';+---+-+| Variable_name | Value |+---+-+| innodb_data_file_path | ibdata1:306M:autoextend | | innodb_data_home_dir | | +---+-+2 rows in set (0.00 sec)MySQL's version is 5.0.27-standard-log.OS is RHEL 4.0What should I do?ThanksGu Lei --- 惠普商用台式机dc5750,高性能，低功耗！( http://ad4.sina.com.cn/sina/limeng3/mail_zhuiyu/2006/mail_zhuiyu_20061225.html ) === 注册新浪2G免费邮箱（ http://mail.sina.com.cn/chooseMode.html ） -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql question regarding distinct/group by...
Hi, dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 Try, select * from dog where fooID=1 group by fooID; - which retrieves the first instance; resulting in, 1,2,1 Thanks ViSolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 6:07 AM Subject: mysql question regarding distinct/group by... hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- 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: mysql data into CSV file.
Hi, Try using, SELECT * INTO OUTFILE 'tmp/music_mp3.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' ' LINES TERMINATED BY ' \n ' FROM mp3; Thanks ViSolve DB Team - Original Message - From: Shain Lee [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Friday, January 05, 2007 11:05 AM Subject: mysql data into CSV file. Hi For example , suppose i have a database - Music, and a table in it is mp3. under mp3 there is more fields , like, id, artist, name , mp3_file...etc. I wanted to get that info which has stored in relavent fields into a CSV fle . Can somebody help me to do that please ? Can i do it mysql commands it self ? Thank you, Shaine. Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db/query question...
Hi, This will do: select name,status, max(_date) from dog where status (select max(status) from dog where status=dog.status) group by name; Thanks ViSolve DB Team. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 05, 2007 10:23 AM Subject: db/query question... hi... a further test... the following test tbl/information: dog name char status int _date timestamp id int test data dog name status_date id tom 1 01/20/07 1 tom 2 01/21/07 2 sue 1 01/20/07 3 tom 3 01/22/07 4 sue 2 01/21/07 5 bob 1 01/20/07 6 tom 4 01/23/07 7 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 if i do a regular group, i can get (for tom) tom 1 01/20/07 1 tom 2 01/21/07 2 tom 3 01/22/07 4 tom 4 01/23/07 7 sue 1 01/20/07 3 sue 2 01/21/07 5 sue 3 01/22/07 5 sue 4 01/23/07 5 sue 3 01/24/07 5 here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... any thoughts/comments.. thanks -bruce -- 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 syntax
Hi, have you checked the 'enabled' field datatype or can you give the query. Thanks ViSolve DB Team. - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3 INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- 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 syntax
Hi Scott, at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' The error is because you havent specified the value for the column countryid. If you do not want to insert the value to the column countryid then use the following query.. do not leave the value of column blank. INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid =NULL , enabled = '1' Thanks, ViSolve DB Team - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Hi, The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB. On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. Thanks Visolve DB Team - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 4:14 AM Subject: Max size and row numbers Hey everyone I have more of a general question regarding your experience with large tables. I currently have a table (MyISAM, 6 columns, lots of reading access, some writing) with about 70.000.000 records, using 2.5GB of diskspace. I am running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). I just read that the max size for a table is 256TB in a default installation. I have basically no experience with tables that big and mine is potentially growing to several hundred million records. First of all, are there theoretical limitations (if the 256TB are correct I would be fine with that I guess) in size and number of records? More importantly, what are the practical limitations and/or pitfalls? Is ext3 as filesystem a limiting factor? If you have experience or know of good links regarding this topic, please let me know Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What to do if a table is supposed to get 3GB data per day?
Hi, In our opinion, you can opt any or all of these: a) Build indexes; or rebuild indexes with REPAIR TABLE b) Take Periodic backup(mysqldump) based on the importance of the data. Clear the current table on specific condition. c) If clearing the table affects the transactions that depends on past data, then well and good compress the table.[only for myisam] shell myisampack tablename.MYI shellmyisamchk -rq tablename.MYI Thanks ViSolve DB Team - Original Message - From: Asif Lodhi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 21, 2006 5:24 PM Subject: What to do if a table is supposed to get 3GB data per day? Hi, Would you like to express your opinion as to what design strategy to take if a table (used for read operations only) is supposed to get more than 3GB of data per day? With 1000 simultaneous users ? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Data Vanishing with FireFox
Hi, The difference in the behaviour of browser interpratation might only be on Style sheet/CSS file. So the improperness might be of PHP. So better trace the 'insert' or check the method. If it is GET method, sure the length is a constraint and there may be chance for data to get eliminated if the length exceeds 256 chars. The best solution is trace the parameter values, whether they are interpreted as it is. Thanks ViSolve DB Team - Original Message - From: Matt Neimeyer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 27, 2006 8:15 PM Subject: MySQL Data Vanishing with FireFox We recently started getting reports that when users access our PHP based CRM system that it didn't work. After much debugging and tracking down it looks like that ultimately what's happening is that SOMETIMES with a statement like... INSERT INTO Customers (A,B,C) VALUES (One,Two,Three) ...that Three just doesn't make it into the database. So If I... SELECT * FROM Customers WHERE A=One ... I would get One,Two, It seems to happen more often (maybe only) with FireFox. Other tables have no problems that we can see. C is indexed... but we have other indexed fields in the table that don't seem to be affected. It's not our biggest table by far... I'm baffled. Where do I look next? This is on a hosted box so we have limited access to the command line. This is using PHP 4.3.11 with MySQL 3.23.58 on Apache 2.0.51 on a Fedora system (of unknown version) Thanks in advance... 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]
Re: oracle transfer question
Hi, Try using STDDEV(), which was given for Oracle compatibility by MySQL. SELECT employee_id, salary, hire_date, STDDEV(salary) Std Deviation of Salary FROM employees WHERE job_id = 'ST_CLERK' Group by employee_id, salary, hire_date ORDER BY hire_date; Thanks, ViSolve DB Team. - Original Message - From: wangxu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 21, 2006 8:29 AM Subject: oracle transfer question There is a oracle SQL: SELECT employee_id, salary, hire_date, STDDEV(salary) OVER (ORDER BY hire_date) Std Deviation of Salary FROM employees WHERE job_id = 'ST_CLERK'; The result is: EMPLOYEE_ID SALARY HIRE_DATE Std Deviation of Salary --- -- -- --- 137 3600 14-7月 -95 0 141 3500 17-10月-95 70.7106781 133 3300 14-6月 -96 152.752523 142 3100 29-1月 -97 221.735578 131 2500 16-2月 -97 435.889894 125 3200 16-7月 -97 389.871774 129 3300 20-8月 -97 357.903951 138 3200 26-10月-97 331.393163 130 2800 30-10月-97 339.116499 139 2700 12-2月 -98 352.136337 143 2600 15-3月 -98 369.028208 EMPLOYEE_ID SALARY HIRE_DATE Std Deviation of Salary --- -- -- --- 140 2500 06-4月 -98 388.762606 144 2500 09-7月 -98 399.679359 134 2900 26-8月 -98 384.664832 126 2700 28-9月 -98 377.586319 127 2400 14-1月 -99 390.72582 132 2100 10-4月 -99 427.974023 135 2400 12-12月-99 430.116263 136 2200 06-2月 -00 443.800703 128 2200 08-3月 -00 453.379126 I transfer the oracle sql to mysql sql: SELECT employee_id, salary, hire_date, STDDEV_pop(salary)Std Deviation of Salary FROM employees WHERE job_id = 'ST_CLERK' Group by employee_id, salary, hire_date ORDER BY hire_date; But the result are: +-+++-+ | employee_id | salary | hire_date | Std Deviation of Salary | +-+++-+ | 137 | 3600 | 1995-07-14 | 0. | | 141 | 3500 | 1995-10-17 | 0. | | 133 | 3300 | 1996-06-14 | 0. | | 142 | 3100 | 1997-01-29 | 0. | | 131 | 2500 | 1997-02-16 | 0. | | 125 | 3200 | 1997-07-16 | 0. | | 129 | 3300 | 1997-08-20 | 0. | | 138 | 3200 | 1997-10-26 | 0. | | 130 | 2800 | 1997-10-30 | 0. | | 139 | 2700 | 1998-02-12 | 0. | | 143 | 2600 | 1998-03-15 | 0. | | 140 | 2500 | 1998-04-06 | 0. | | 144 | 2500 | 1998-07-09 | 0. | | 134 | 2900 | 1998-08-26 | 0. | | 126 | 2700 | 1998-09-28 | 0. | | 127 | 2400 | 1999-01-14 | 0. | | 132 | 2100 | 1999-04-10 | 0. | | 135 | 2400 | 1999-12-12 | 0. | | 136 | 2200 | 2000-02-06 | 0. | | 128 | 2200 | 2000-03-08 | 0. | +-+++-+ Why all the compute column values are 0?What is the correct sql? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find date an time of a table update
Hi, mysql show table status like 'tablename'\G will report you the date and time of creation, updation,etc of the specified table. 'SHOW STATUS' enables only view. Note 1: For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply. As you have specified that you want it to display it in the webpage, then retrieve the same from the 'tables' table of information_schema database. mysql use information_schema; mysql show tables; mysql select * from TABLES where TABLE_NAME='tablename'; Note 2: Check the user privilege to access the database. If not grant it. Thanks ViSolve DB Team. - Original Message - From: Marcelo Fabiani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 22, 2006 6:21 AM Subject: find date an time of a table update Hi, I didn't find a way to know the time and date of the last update of table, not the data but the table info itself. Is this possible? I want to use this info in order to show it in a web page. Mysql 4.1 Apache Myisam Regards Marcelo Fabiani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: howto query DELETE all records having invalid table refs
Hi, Try, mysql delete from GbEntries where sender not in (select * from Accounts); Thanks ViSolve DB Team. - Original Message - From: Christian Parpart [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 20, 2006 3:11 PM Subject: howto query DELETE all records having invalid table refs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: account that can only do backup
Hi, File Privilege will do that. But the FILE privilege can only be granted globally (using ON *.* syntax).Therefore, attempts to restrict it to a particular scope will lead to error. so try with grant file on*.* to 'user'@'localhost'; Hope this will do. Thanks ViSolve DB Team - Original Message - From: Bing Du [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:26 AM Subject: account that can only do backup Hi, I want to create a MySQL user account that can only do database backup but nothing else. What privileges does such an account need? I'm having trouble with connecting to mysql.com to look at the documents. It's just spinning forever. Actually, the connection to mysql.com has become extremely slow since I noticed the mysql.com was redesigned. Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: definition of Created_tmp_files in show status
Hi, Since, these files disappear at the end of the session, for good performance, better keep tmp_table_size smaller, so we can eliminate populating in-memory tables, and make use of disk. The temporary table size is not only determined by tmp_table_size but also by max_heap_table_size Thanks ViSolve DB Team. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 14, 2006 9:20 PM Subject: Re: definition of Created_tmp_files in show status Thanks for that. Is the only reason for temporary files then going to be replication? And if so, should I be worried that thousands of files have been created for this purpose? Are you saying that I can tune the size (and thus the number) of the temp files by adjusting the temporary table size? My Created_tmp_disk_tables is considerably lower than my Created_tmp_files value. So it seems the Created_tmp_files cannott include the values from the Created_tmp_disk_tables. Kevin - Original Message From: Visolve DB Team [EMAIL PROTECTED] To: Kevin Fries [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, December 13, 2006 9:26:52 PM Subject: Re: definition of Created_tmp_files in show status Hi, A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails. MySQL creates temporary files as hidden files. tmp_table_size variable will determine the size of the temporary table. But if it exceeds, then server automatically converts it to disk-based tables. The server variables, Created_tmp_tables -The number of in-memory temporary tables created automatically by the server while executing statements. Created_tmp_disk_tables -The number of temporary tables on disk created automatically by the server while executing statements. Created_tmp_files - How many temporary files mysqld has created. were used to determine the temporary files status. Thanks ViSolve DB Team Original Message - From: Kevin Fries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:57 AM Subject: definition of Created_tmp_files in show status According to the mysql reference manual, the definition of this field is: How many temporary files mysqld has created. Can someone elaborate on this? What causes mysql to create a temporary file? I see something indicating it may be associated with replication. In our environment (which has replication) we see counts for this variable in the tens of thousands. Thanks in advance, Kevin Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com Need a quick answer? Get one in minutes from people who know. Ask your question on www.Answers.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: definition of Created_tmp_files in show status
Hi, A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails. MySQL creates temporary files as hidden files. tmp_table_size variable will determine the size of the temporary table. But if it exceeds, then server automatically converts it to disk-based tables. The server variables, Created_tmp_tables -The number of in-memory temporary tables created automatically by the server while executing statements. Created_tmp_disk_tables -The number of temporary tables on disk created automatically by the server while executing statements. Created_tmp_files - How many temporary files mysqld has created. were used to determine the temporary files status. Thanks ViSolve DB Team Original Message - From: Kevin Fries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:57 AM Subject: definition of Created_tmp_files in show status According to the mysql reference manual, the definition of this field is: How many temporary files mysqld has created. Can someone elaborate on this? What causes mysql to create a temporary file? I see something indicating it may be associated with replication. In our environment (which has replication) we see counts for this variable in the tens of thousands. Thanks in advance, Kevin Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prefixing fields with table name when joining?
Hi Kelly, The short script of prefixing fields with tablename till 5.0 and beta is not applicable. Hope, this senario would be rare, that too many tables with larger field length, more columns etc. Thanks ViSolve DB Team - Original Message - From: Kelly Jones [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, December 11, 2006 8:39 PM Subject: Prefixing fields with table name when joining? I have three tables (x, y, and z) with the same 3 fields (id, name, number). If I do: SELECT * FROM x, y, z WHERE ... each row of my result will contain 3 id fields, 3 name fields, and 3 number fields. Of course, I can/should do: SELECT x.id AS x_id, x.name AS x_name, x.number AS x_number, y.id AS y_id, y.name AS y_name, y.number AS y_number, z.id AS z_id, z.name AS z_name, z.number AS z_number FROM x, y, z WHERE ... Short of scripting, is there any way to get MySQL to do this? Something like: SELECT * FROM x, y, z PREFIX FIELDS WITH TABLE NAME WHERE ... -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile. -- 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: How to rename database name
Hi Simply go with either of these: 1. 'mysqldump' the current database, drop it and create a new database. Move the dumped data into the new database. or 2. Stop mysqld. Rename the database folder in the datadirectory. Start the server and grant access permissions Thanks ViSolve DB Team. - Original Message - From: jagdish gunjal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 08, 2006 10:25 AM Subject: How to rename database name Hi all, Does any know command to rename the database name in Mysql db. - Find out what India is talking about on - Yahoo! Answers India Send FREE SMS to your friend's mobile from Yahoo! Messenger Version 8. Get it NOW -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Renaming the root user - problems.
Hi No. MySQL doesn't insist on any 'root' user. If necessary you can rename it. If you have the supervisor privileges, you can edit the same. Make sure to Grant Permissions. For instance: mysql grant all on *.* to 'myroot'@localhost identified by 'myroot'; Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 07, 2006 1:41 PM Subject: Renaming the root user - problems. Hello, my question refers to the user root in MySQL 5.0.22. Is the standard MySQL root user really required with the name root or can I rename the root user for example to myroot? Our software vendor affirms that MySQL need the User root always but I argue the convers. The application of this vendor doesn't work by renaming the root user to myroot. In my opinion the application causes the fault and not the MySQL DBMS. Who is right? Regards Spiker -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- 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: SUM() of 1 and NULL is 1 ?
Hi Vegelin, In MySQL, SUM(1) = 1 SUM(NULL) = NULL In your case, you are inserting two values 1, NULL to Jan column, So now Jan column contains 1 and NULL, If you SUM the Jan then the result will be 1 not NULL. Because SUM() ignores NULL values. As far as i know, we dont have built-in function that can be used in SUM() to convert values into NULL. We have lot of function to covert NULL into values. To get a NULL value instead on 1, you have to write a stored procedure or function with the logic given below. If Column jan contains NULL then return NULL ELSE return SUM(jan) Thanks, ViSolve DB Team - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 3:27 PM Subject: SUM() of 1 and NULL is 1 ? Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() of 1 and NULL is 1 ?
Hi Vegelin, This will do.. select IF(SUM(IF(Jan IS NULL, 0, Jan))0,NULL,SUM(IF(Jan IS NULL, 0, Jan)))as jan from data; Thanks, ViSolve DB Team - Original Message - From: ViSolve DB Team [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 4:18 PM Subject: Re: SUM() of 1 and NULL is 1 ? Hi Vegelin, In MySQL, SUM(1) = 1 SUM(NULL) = NULL In your case, you are inserting two values 1, NULL to Jan column, So now Jan column contains 1 and NULL, If you SUM the Jan then the result will be 1 not NULL. Because SUM() ignores NULL values. As far as i know, we dont have built-in function that can be used in SUM() to convert values into NULL. We have lot of function to covert NULL into values. To get a NULL value instead on 1, you have to write a stored procedure or function with the logic given below. If Column jan contains NULL then return NULL ELSE return SUM(jan) Thanks, ViSolve DB Team - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 3:27 PM Subject: SUM() of 1 and NULL is 1 ? Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query help
Hi, Try this.. UPDATE table2 inner join table1 on table2.playedid=table1.playerid SET table2.totalscore=sum(table1.score) Just a guess... Thanks, ViSolve DB Team - Original Message - From: Ravi Kumar. [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 4:11 PM Subject: Update query help Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid OR update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid group by playerid However none of the above is working. Where am I wrong? Please help. The version of MySQL I am using is 4.1.14-standard-log. Thanks, Ravi. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set global does not work
Hi Try mysql set @@session.wait_timeout=1000; mysql show variables; According to the VARIABLE wait_timeout [default: 28800 seconds] a running MySQL daemon clears up idle connections if their idle period wait_timeout.so the sleeping threads will be automatically cleared if the time exceeds 'wait_timeout' variable value. Thanks ViSolve DB Team. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 07, 2006 11:52 AM Subject: RE: set global does not work Hi: I open a connection via mysql mysqlshow global variables like 'wait_timeout' -- 28800 mysql set global wait_timeout = 1000; mysqlexit; reopen mysql mysql show session variables like 'wait_timeout'; -- 28800 I expect session value to be 1000 according the doc, as it should be given the global value when a new connection is established. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: +1MB query gets Out of memory
Hi, Hope query_cache does not relate with the error. Because, if the query is larger than the query_cache size, automatically that will not be cached. So that query, on executing any no of times, will never utilize the cache. Also as of our understanding, packet_size does not produce out of memory error. Try with, Invoke mysql with the --quick option. This causes it to use the mysql_use_result() C API function to retrieve the result set, which places less of a load on the client (but more on the server). Ref: http://dev.mysql.com/doc/refman/5.0/en/out-of-memory.html Thanks ViSolve DB Team - Original Message - From: nocturnal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 30, 2006 9:11 PM Subject: +1MB query gets Out of memory Hi A client used to get Out of memory when trying to send a query that was a bit more than 1MB. The problem was solved when i increased query_cache to 2MB but now the problem is back and this time the line reported by the Out of memory error has a query that is just a bit over 1MB again. The only other value i could find that was 1MB was packet_size but that didn't help. What could i do to run this query? -- Med vänliga hälsningar Stefan Midjich aka nocturnal [Swehack] http://swehack.se -- 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: before update trigger syntax
Hi Try with mysql delimiter // mysql CREATE TRIGGER eat_lub BEFORE UPDATE ON d - FOR EACH ROW BEGIN - UPDATE d SET NEW.s=user(); - END// Query OK, 0 rows affected (0.03 sec) mysql delimiter ; Hope this helps. Thanks, ViSolve DB Team - Original Message - From: Ted Yu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 30, 2006 9:13 AM Subject: before update trigger syntax Can someone tell me what's wrong with my before update trigger syntax ? Thanks Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 5.0.18-log mysql use eventtracker; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql CREATE TRIGGER eat_lub BEFORE UPDATE ON EVENT_ACTION_TYPES - FOR EACH ROW BEGIN - SET NEW.LAST_UPDATED_BY = USER(); END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET NEW.LAST_UPDATED_BY = USER()' at line 3 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 mysql CREATE TRIGGER eat_lub BEFORE UPDATE ON EVENT_ACTION_TYPES - FOR EACH ROW BEGIN - UPDATE EVENT_ACTION_TYPES SET NEW.LAST_UPDATED_BY = USER(); END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE EVENT_ACTION_TYPES SET NEW.LAST_UPDATED_BY = USER()' at line 3 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com -- 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: No of columns in a table
Hi, As of our understanding, The maximum number of columns for any storage storage engine depends upon the option AVG_ROW_LENGTH. If you length of the column name is large, then no of columns will be less and obviously vice-versa also holds true. The options MAX_ROWS * AVG_ROW_LENGTH will decide the maximum tablesize. To get the current values, use SHOW TABLE STATUS LIKE 'tablename'; Thanks ViSolve DB Team. - Original Message - From: C K [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 29, 2006 6:06 PM Subject: No of columns in a table I need information for Maximum no. of columns in a table in MySQL with diff. engines. Where can I get it? Thanks and regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-text searching with quoted bind variables
Hi,
The thing is, if the Outermost quote is single( ' ) , and if you try to use
the same inside the string, in that case you need to go for \'. The same
applies for Double quotes also. Double quotes within the single or single
quote within the double dosen't reqire blackslash.
For instance,
mysql select * from articles where match(title,body) against ('
\'database\' ');
or can be like this
mysql select * from articles where match(title,body) against (' database
');
Thanks
ViSolve DB Team.
- Original Message -
From: William Langshaw [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, November 29, 2006 12:54 AM
Subject: Full-text searching with quoted bind variables
I am using Full-Text searching with In Boolean Mode. I am generating
my query by using binding parameters. If a user types in a quoted
string on the search form (in order to match that string as-is), the
binding mechanism escape it with a backslash. The query runs fine and
it appears to return the same results when I run the same query by
hand without the backslashes. I just want to make sure these
backslashes are not being interpreted differently and that the
full-text search engine is treating the double-quotes as they are
intended (and not looking for them literally)
E.g.
... AND MATCH (title, description) AGAINST ('chev* \malibu\' IN BOOLEAN
MODE)
vs
... AND MATCH (title, description) AGAINST ('chev* malibu' IN BOOLEAN
MODE)
They both seem to be Ok, but I just want to make sure now, before
wierd problems come up later...
thanks
-william
--
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: How to calculate max number of rows in a MyISAM table (default MAX_ROWS) ?
Hi MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH values for MyISAM tables, to decide how big the resulting table should. If you don't specify either option, the maximum size for a table is 65,536TB of data (4GB before MySQL 5.0.6). Ref: http://www.mysql.org/doc/refman/5.0/en/create-table.html Thanks ViSolve DB Team. - Original Message - From: Amit Dor-Shifer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 28, 2006 7:54 PM Subject: How to calculate max number of rows in a MyISAM table (default MAX_ROWS) ? Hi. I've created the following table, with default value for MAX_ROWS. As the following shows, it has a max. size of 4G. I'm wondering: Is the expected max number of rows in this table = Max_data_length \ Avg_row_length? If not, what might it be? How do I calculate it? mysql show table status from foo like 'bar'\G *** 1. row *** Name: bar Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 15094 Avg_row_length: 121 Data_length: 2001000 Max_data_length: 4294967295 Index_length: 1496064 Data_free: 163680 Auto_increment: 67217 Create_time: 2006-11-01 15:41:20 Update_time: 2006-11-28 11:42:13 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Thanks, Amit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql doubt.
Hi Renish, In case if you dont have the Item field in the Table B. Try the following query select item from a where item not in ( select substring(price,1,instr(price,'-')-1) from b ); This will list out the the items which doesnt have price. mysql select * from a; ++ | item | ++ | book | | pencil | | table | | chair | ++ mysql select * from b; +---+ | price | +---+ | pencil-11 dollars | | book-12 dollars | +---+ mysql select item from a where item not in ( select substring(price,1,instr(price,'-')-1) from b ); +---+ | item | +---+ | table | | chair | +---+ Hope this helps. Thanks, ViSolve DB Team - Original Message - From: Renish [EMAIL PROTECTED] To: ViSolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, November 27, 2006 12:48 PM Subject: Re: MySql doubt. Thanks if in case if we dont have the Item field in the Table B. wat to do..I know the primary key to connect both the tables/ - Original Message - From: ViSolve DB Team To: Renish ; mysql@lists.mysql.com Sent: Monday, November 27, 2006 12:48 PM Subject: Re: MySql doubt. Hi Renish, Here's the answer for your query. mysql select * from a; ++ | item | ++ | book | | pencil | | table | | chair | ++ mysql select * from b; ++---+ | item | price | ++---+ | pencil | 11 dollar | | book | 12 dollar | ++---+ You can use this query to get the items which doesnt have price. mysql select item from a where item not in ( select item from b); +---+ | item | +---+ | table | | chair | +---+ Thanks, ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 27, 2006 8:45 AM Subject: MySql doubt. Hi Guys, I have an easy doubt.. Table A consist of field Item and Table B consist of field Price. Table A consists of Items which has price aswell the items which doesnt have price. But In Table B,I have kept only the item which has price . Now, I want to subtract the total items in Table A to the total items in Table B. so I get the items which doesnt have price. I used union its not working.. can any of u guys write the code for it,,, For example Table A ItemBook Pencil Table Table B- which consists of Item with price Price -Book-11 dollar Pencil-2 dollar Please reply me asap. I hope it should be v. easy enough to tackle. Cheers, Renish koshy -- 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: Having Problem With Load Files and AutoNumber id fields
Hi,
My opinion is alter your table so that auto increment column should be the last
column. Try importing values for n-1 columns, where the nth column will get
increment with auto_increment get escaped from NULL values.
For instance:
mysqlcreate table x (item varchar(10),price int,id int not null auto_increment
primary key);
and sample.txt as
Pen10
Pencil3
Eraser5
With this, mysql LOAD DATA local INFILE fullpath/sample.txt INTO TABLE x
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
Result will be,
mysql select * from x;
++---++
| item | price | id |
++---++
| rubber | 10| 1 |
| pencil | 15| 2 |
| pen| 45| 3 |
|| NULL | 4 |
++---++
4 rows in set (0.00 sec)
Thanks
ViSolve DB Team
- Original Message -
From: John Kopanas [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, November 24, 2006 9:42 PM
Subject: Having Problem With Load Files and AutoNumber id fields
Argh... I am autogenerating a load file after parsing and massaging a
text file I get from an AS/400 system. My first field is an id that
is on auto_number so I put a tab at the begging of the line for each
one of my records... it throws a warning for each line... if instead
of putting an autonumber I put a random number it works perfectly.
How can I get it to work?
Sample line in my load file that works:
1 306600121001001467 SMART ALARMS INC 201
MILLWAY AVE UNIT #8 \N \N VAUGHAN ON L4K 5K8 0 1999-9-1
00:00:00 1999-9-1 00:00:00
Sample line that does not work:
306600121001001467 SMART ALARMS INC 201 MILLWAY AVE UNIT
#8 \N \N VAUGHAN ON L4K 5K8 0 1999-9-1 00:00:00 1999-9-1 00:00:00
My code that generates the sample line that does not work:
tempfile.puts
\t#{record[0,22].strip}\t#{record[22,32].strip}\t#{record[54,32].strip}\t#{record[86,32].strip}\t#{record[118,32].strip}\t#{record[150,30].strip}\t#{record[180,2].strip}\t#{record[182,7].strip}\t0\t2000-01-01
00:00:00\t2000-01-01 00:00:00
How can I get mysql to put the auto_numbers in for me? Why is it even
accepting my numbers?
Thanks :-)
Does anyone also know how I can see the warnings created by loadfile?
All I just see is the summary with skipped and warnings.
Thanks :-)
--
John Kopanas
[EMAIL PROTECTED]
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple doubt
Hi Renish, If you want to capture the entries which are entered more than once. Here's the answer for it. mysql select * from a; ++ | b | ++ | pen| | pencil | | rubber | | pen| | paper | | paper | ++ 6 rows in set (0.00 sec) mysql select b from a group by b having count(b)1; +---+ | b | +---+ | paper | | pen | +---+ 2 rows in set (0.00 sec) Hope this helps. Thanks, ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: Hal Wigoda [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 28, 2006 9:03 AM Subject: Re: Simple doubt Hello all, I think u guys have mistaken my question,.. The questiion is How will capture the entries which are entered more than once... Like For example Item table Mango Orange Carrot Papaya Mango Mango I want to capture only mango cos it is repeated more than once - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED] Sent: Tuesday, November 28, 2006 11:11 AM Subject: Re: Simple doubt select unique On Nov 27, 2006, at 8:45 PM, Renish wrote: Gurus, How do I go about to selecting an item which is repeated more than one in a field in a table. For example Item table Mango Orange Carrot Papaya Mango Mango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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: MySql doubt.
Hi Renish, Here's the answer for your query. mysql select * from a; ++ | item | ++ | book | | pencil | | table | | chair | ++ mysql select * from b; ++---+ | item | price | ++---+ | pencil | 11 dollar | | book | 12 dollar | ++---+ You can use this query to get the items which doesnt have price. mysql select item from a where item not in ( select item from b); +---+ | item | +---+ | table | | chair | +---+ Thanks, ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 27, 2006 8:45 AM Subject: MySql doubt. Hi Guys, I have an easy doubt.. Table A consist of field Item and Table B consist of field Price. Table A consists of Items which has price aswell the items which doesnt have price. But In Table B,I have kept only the item which has price . Now, I want to subtract the total items in Table A to the total items in Table B. so I get the items which doesnt have price. I used union its not working.. can any of u guys write the code for it,,, For example Table A ItemBook Pencil Table Table B- which consists of Item with price Price -Book-11 dollar Pencil-2 dollar Please reply me asap. I hope it should be v. easy enough to tackle. Cheers, Renish koshy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FIND_IN_SET question
Hi, Hope, a string constant can be searched against a column; Most of the string function including find_in_set, allows us to display only the table values and not the string constants passed. Try it with 2 tables; or some userdefined functions. Thanks ViSolve DB Team. - Original Message - From: Lars Schwarz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 25, 2006 6:17 PM Subject: FIND_IN_SET question hi all, this is what i got: SELECT find_in_set( box, '2,3,4,5,6,12' ) 1 AS YESNO, box FROM f2g_booking ORDER BY box what i need is those values that haven't been found from the find_in_set list. f2g_booking holds box values of 1,2,4,5,12, that means that are no entries with 3 and 6 in the table... that's what i need 3 and 6 as a result, e.g. those values from the list that haven't been found (are not in the table). how to do that? thanks: lars -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table of type=memory is full... but not
Hi, Obviously True. Try mysql show table status like 'tablename'; Check for Data_length Max_Data_Length values which speaks about the used memory size and allowed maximum size. The max_heap_table_size system global variable impose the maximum size of the memory table. Try with mysql set GLOBAL max_heap_table_size=value; For individual tables specify MAX_ROWS table option in the CREATE TABLE or in the ALTER TABLE. For more Ref: http://dev.mysql.com/doc/refman/4.1/en/memory-storage-engine.html Thanks ViSolve DB Team. - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 25, 2006 11:09 PM Subject: Re: Table of type=memory is full... but not When I moved from type=memory to the default DB it worked fine. I am starting to think that the quantity of rows i.e. 550K approx was too much for my memory on my computer to handle. Does this make sense? On 11/25/06, John Kopanas [EMAIL PROTECTED] wrote: I create a memory table with the following query: CREATE TABLE company_totals type=memory SELECT company_id id, SUM(annual_service_charge) service_charge FROM purchased_services ps GROUP BY company_id;) When I try this I get the following error: Mysql::Error: The table 'company_totals' is full: So I decided to see what is in the table: SELECT * FROM company_totals; And I get the following error: ERROR 1146 (42S02): Table 'opportunity_development.company_totals' doesn't exist Even when I do a: SHOW TABLE STATUS the table is not listed. So, where is this table? Why does mySQL think it is full but does not think it exists at the same time? Help :-) -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTOCOMMIT. displaying and setting
There is no global system variable to set Autocommit; but we can achieve the same with 'init_connect=SET AUTOCOMMIT=0'. This variable can be set from the mysql or in the commandline or in the option file. Like, mysql SET GLOBAL init_connect='SET AUTOCOMMIT=0'; or mysqlSET @@GLOBAL.init_connect='SET AUTOCOMMIT=0'; or in the option file [mysqld] init_connect='set AUTOCOMMIT=0' or shell mysqld --init_connect='set AUTOCOMMIT=0' By default AUTOCOMMIT is enabled. ie set to 1. If set to 0, then disabled. check with 2 connections; try inserting or updating in one connection; check in the other with select. This variable was added in MySQL 4.1.2. - Original Message - From: Amit Dor-Shifer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 21, 2006 4:41 PM Subject: AUTOCOMMIT. displaying and setting Hi. I'm puzzled rgd. a/m. Is AUTOCOMMIT a variable? If so, is it a session variable? Should it show up when issuing a SHOW VARIABLES command? How do I set this variable when initalizing a jdbc connection? Can I pass it on the connection URL? Can someone present a code line for this? How do I set this variable for an interactive console session? Can i do this from the command line? When I attempt this I get: # mysql -p --autocommit=0; mysql: unknown variable 'autocommit=0' Thanks, Amit -- 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: AUTOCOMMIT. displaying and setting
Hi Yes, you can set the variabe per-connection. mysql set SESSION autocommit=0; Check with two connections. For Transaction safe engines(such as InnoDB, BDB, or NDB Cluster),to disable autocommit mode for a single series of statements, use the START TRANSACTION or BEGIN statement. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state once transaction done. Thanks ViSolve DB Team. - Original Message - From: Amit Dor-Shifer [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 22, 2006 10:59 AM Subject: Re: AUTOCOMMIT. displaying and setting Thanks! I played a bit init_connect. I'm not sure though how can I verify the autocommit value. You say 'by select'. Something like this? select @@AUTOCOMMIT; Because that still gives me a value of '1'. More important to me: Can this variable be set per-connection? If so, how? Specifically, how do I set it for a none-console connection (e.g. my previously mentioned jdbc connection)? BTW: # mysql --version mysql Ver 14.7 Distrib 4.1.21, for pc-linux-gnu (i686) using readline 5.1 Amit Visolve DB Team wrote: There is no global system variable to set Autocommit; but we can achieve the same with 'init_connect=SET AUTOCOMMIT=0'. This variable can be set from the mysql or in the commandline or in the option file. Like, mysql SET GLOBAL init_connect='SET AUTOCOMMIT=0'; or mysqlSET @@GLOBAL.init_connect='SET AUTOCOMMIT=0'; or in the option file [mysqld] init_connect='set AUTOCOMMIT=0' or shell mysqld --init_connect='set AUTOCOMMIT=0' By default AUTOCOMMIT is enabled. ie set to 1. If set to 0, then disabled. check with 2 connections; try inserting or updating in one connection; check in the other with select. This variable was added in MySQL 4.1.2. - Original Message - From: Amit Dor-Shifer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 21, 2006 4:41 PM Subject: AUTOCOMMIT. displaying and setting Hi. I'm puzzled rgd. a/m. Is AUTOCOMMIT a variable? If so, is it a session variable? Should it show up when issuing a SHOW VARIABLES command? How do I set this variable when initalizing a jdbc connection? Can I pass it on the connection URL? Can someone present a code line for this? How do I set this variable for an interactive console session? Can i do this from the command line? When I attempt this I get: # mysql -p --autocommit=0; mysql: unknown variable 'autocommit=0' Thanks, Amit -- 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: Moving large Innodb table to another mysql
Hi, Why can't you try copying the data directory itself to the target server? usually data directory resides in, for windows C:\Program Files\MySQL\MySQL Server 5.0\data for Unix Linux -/usr/local/mysql, /var/lib/mysql respectively. or from the path where you installed mysql. Thanks ViSolve DB Team. - Original Message - From: sofox [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 17, 2006 5:32 PM Subject: Moving large Innodb table to another mysql Dear All, I am using mysql-4.0.26, and I have a very large innodb table(10G) . When I try to moved the table from one mysqld to another one by mysqldump to a script and import the script on target server, I have problem: 1) if I don't use --no-autocommit option when mysqldump, it will take more than 10 hours to import the script; 2) if I use --no-autocommit option during dump, I will get error # Error: 1197 SQLSTATE: HY000 (ER_TRANS_CACHE_FULL) Message: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try againAny Comment?Oscar Yen.Select
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
Hi, If you are particular about a table, i.e what engine my table uses? Try, mysql show table status like 'tablename' \G Thanks ViSolve DB Team. - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 17, 2006 10:43 PM Subject: How Do I Know If mySQL is using MyISAM or InnoDB? Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Text File Into mySQL
Hi, LOAD DATA restricts only on BLOB,TEXT,ENUM and FIXED DATA. Others are equally supported [INSERT stmt]. Likewise for mysqlimport also. If the data file has no delimiter other than space[even the name, addresss,etc contain spaces], then the target table will not the one what is expected, though it is possible to import the data. It also depends on the width of the fields. The data and the fieldname does not corresponds. Thanks ViSolve DB Team. - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 18, 2006 4:35 AM Subject: Importing Text File Into mySQL I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Thanks for your help :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- 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: Error:1067 could not start mysql server
Hi Venu, If you are having problem in running mysqldump, you can copy the data directory and place it in another mysql servers data directory. The data dir of the particular database is stored in the same name of the database. After moving the data dir, restart the mysql dameon. Now you can see the copied database in the new mysql server. If you are using Unix , by default, data dir will be in /usr/local/ mysql_ - - -/ data If it is linux , it will be in /var/lib Thanks, ViSolve DB Team - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: VenuGopal Papasani [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:32 AM Subject: Re: Error:1067 could not start mysql server Hi, On which platform? Have you checked with *.err file? Thanks ViSolve DB Team. - Original Message - From: VenuGopal Papasani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 11, 2006 7:36 PM Subject: Error:1067 could not start mysql server Dear all, I am getting the following error when i m trying to start mysql server Error:1067:process terminated unexpectedly. I have tried stopping many services and start mysql but not successful.But i need to get my data backup.Is there any alternative to mysqldump command where i can get the backup.Please give me the solution asap thanks and regards, venu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/531 - Release Date: 11/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error:1067 could not start mysql server
Hi, On which platform? Have you checked with *.err file? Thanks ViSolve DB Team. - Original Message - From: VenuGopal Papasani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 11, 2006 7:36 PM Subject: Error:1067 could not start mysql server Dear all, I am getting the following error when i m trying to start mysql server Error:1067:process terminated unexpectedly. I have tried stopping many services and start mysql but not successful.But i need to get my data backup.Is there any alternative to mysqldump command where i can get the backup.Please give me the solution asap thanks and regards, venu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convertion ORACLE query to MYSQL
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
2. DROP USER jbossjms1 CASCADE;
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
Thanks in Advance,
ViSolve PlanCAT Team
Re: wait_timeout help
Hi Actually I don't get any idle processes listed.. but have came across such mails.. On which platform you are running mysql? I guess this might be: If Windows, the server will drop the connection probably because of 'wait_timeout' expired. For others, the mysql gets reconnected with 'reconnect flag' set to 1 in the MySQL structure... Thanks ViSolve DB Team - Original Message - From: Sayed Hadi Rastgou Haghi To: Visolve DB Team Cc: mysql@lists.mysql.com Sent: Wednesday, November 08, 2006 11:51 AM Subject: Re: wait_timeout help Hi I set the wait_timeout in my.cnf. Sometimes it works well and there is no idle process 120 seconds time. But sometimes there are idle processes with 1900s time. What the reason can be? On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi According to the VARIABLE wait_timeout [default: 28800 seconds] a running MySQL daemon clears up idle connections if their idle period wait_timeout.so the sleeping threads will be automatically cleared if the time exceeds 'wait_timeout' variable value. 'wait_timeout' variable : The number of seconds the server waits for activity on a non-interactive connection before closing it. This timeout applies only to TCP/IP connections, not to connections made via Unix socket files, named pipes, or shared memory. 'interactive_timeout' variable's value or wait_timeout variables values -depends on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()) Thanks ViSolve DB Team. - Original Message - From: Sayed Hadi Rastgou Haghi To: Visolve DB Team Cc: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 4:12 PM Subject: Re: wait_timeout help Thanks again I have some questions that may help me explain my problem well. 1- What's this mean: mysql SHOW PROCESSLIS; +--+---+--++---+-++-+ | Id | User | Host |db | Command | Time | State | Info | +--+---+--++---+-+-++ |1 | usr | myhost:36336 | mydb | Sleep | 2587 | | NULL | | 11 | usr | myhost:36341 | mydb | Sleep | 2587 | | NULL | | 12 | usr | myhost:36348 | mydb | Sleep | 2587 | | NULL | | 3732| usr | myhost:43940 | mydb | Sleep | 2587 | | NULL | +--+---+--++---+-+-++ Are these processes idle? If yes why MySQL doesn't kill them? 2- If I set wait_timeout variable, will they be killed on that time? 3- If I set interactive_timeout, what will happen to queries that take time longer than interactive_timeout? On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi Try command line option, like mysql set @@session.wait_timeout=15; mysql show variables; Thanks Visolve DB Team. - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 3:21 PM Subject: Re: wait_timeout help Thanks But this sets. GLOBAL wait_timeout variable. Cab be seen by SHOW GLOBAL VARIABLES not by SHOW VARIABLES; On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi specify it in the .cnf file in the mysqld options: wait_timeout=x or specify it at the command line : set wait_timeout=x or start mysqld with --wait_timeout=x along with other options. Thanks ViSolve DB Team - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 2:08 PM Subject: wait_timeout help Dear All, I want to set wait_timeout variable on mysqld startup. Is there any way to do that? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1 Get FireFox! /a -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- Sincerely, Hadi Rastgou
Re: InnoDB + FULLTEXT
Hi, Till MySQL 5.0 there was no support for FULLTEXT by InnoDB. More info on: www.innodb.com/innodbtalkUC2005.pdf Thanks ViSolve DB Team. - Original Message - From: FalconSoft, Inc [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 09, 2006 6:28 AM Subject: InnoDB + FULLTEXT Does anyone know if/when InnoDB will support FULLTEXT indexes? I have a project that I'm working on now that really needs support for both. Thanks! Tim Gustafson FalconSoft, Inc [EMAIL PROTECTED] http://falconsoft.com/ (831) 425-4522 (831) 621-6299 (Fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wait_timeout help
Hi specify it in the .cnf file in the mysqld options: wait_timeout=x or specify it at the command line : set wait_timeout=x or start mysqld with --wait_timeout=x along with other options. Thanks ViSolve DB Team - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 2:08 PM Subject: wait_timeout help Dear All, I want to set wait_timeout variable on mysqld startup. Is there any way to do that? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wait_timeout help
Hi Try command line option, like mysql set @@session.wait_timeout=15; mysql show variables; Thanks Visolve DB Team. - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 3:21 PM Subject: Re: wait_timeout help Thanks But this sets. GLOBAL wait_timeout variable. Cab be seen by SHOW GLOBAL VARIABLES not by SHOW VARIABLES; On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi specify it in the .cnf file in the mysqld options: wait_timeout=x or specify it at the command line : set wait_timeout=x or start mysqld with --wait_timeout=x along with other options. Thanks ViSolve DB Team - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 2:08 PM Subject: wait_timeout help Dear All, I want to set wait_timeout variable on mysqld startup. Is there any way to do that? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wait_timeout help
Hi According to the VARIABLE wait_timeout [default: 28800 seconds] a running MySQL daemon clears up idle connections if their idle period wait_timeout.so the sleeping threads will be automatically cleared if the time exceeds 'wait_timeout' variable value. 'wait_timeout' variable : The number of seconds the server waits for activity on a non-interactive connection before closing it. This timeout applies only to TCP/IP connections, not to connections made via Unix socket files, named pipes, or shared memory. 'interactive_timeout' variable's value or wait_timeout variables values -depends on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()) Thanks ViSolve DB Team. - Original Message - From: Sayed Hadi Rastgou Haghi To: Visolve DB Team Cc: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 4:12 PM Subject: Re: wait_timeout help Thanks again I have some questions that may help me explain my problem well. 1- What's this mean: mysql SHOW PROCESSLIS; +--+---+--++---+-++-+ | Id | User | Host |db | Command | Time | State | Info | +--+---+--++---+-+-++ |1 | usr | myhost:36336 | mydb | Sleep | 2587 | | NULL | | 11 | usr | myhost:36341 | mydb | Sleep | 2587 | | NULL | | 12 | usr | myhost:36348 | mydb | Sleep | 2587 | | NULL | | 3732| usr | myhost:43940 | mydb | Sleep | 2587 | | NULL | +--+---+--++---+-+-++ Are these processes idle? If yes why MySQL doesn't kill them? 2- If I set wait_timeout variable, will they be killed on that time? 3- If I set interactive_timeout, what will happen to queries that take time longer than interactive_timeout? On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi Try command line option, like mysql set @@session.wait_timeout=15; mysql show variables; Thanks Visolve DB Team. - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 3:21 PM Subject: Re: wait_timeout help Thanks But this sets. GLOBAL wait_timeout variable. Cab be seen by SHOW GLOBAL VARIABLES not by SHOW VARIABLES; On 11/7/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hi specify it in the .cnf file in the mysqld options: wait_timeout=x or specify it at the command line : set wait_timeout=x or start mysqld with --wait_timeout=x along with other options. Thanks ViSolve DB Team - Original Message - From: Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 07, 2006 2:08 PM Subject: wait_timeout help Dear All, I want to set wait_timeout variable on mysqld startup. Is there any way to do that? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get FireFox! /a
Re: MAX + SUM in one query
Hi
The query will work and should.
As of our understanding,
you want to retrieve the sum of the total column for a particular id, with
its max price where the type='offer' and id same for both the tables.
Is it so?
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 2:33 PM
Subject: Re: MAX + SUM in one query
but what if I want also to include another table
for example, can I do this :
SELECT R.Stock_StockID,S.StockName, MAX(R.RequestPrice),
SUM(R.RequestTotal) FROM
Request R, StocksT S WHERE S.StockID=R.Stock_StockID AND
R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
Also remember I want to get the SUM of Total of the records that
RequestPrice=MAX(RequestPrice)
for example, if the MAX(RequestPrice)=200 then I just need the Total
SUM of the records that there RequestPrice=200
I don't need the SUM for all the records
Thanks
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi
That's fine.
But for the query, I have created a simple table which simulates as that
of
yours. I have used simple domain names.
I typed the StockID as RequestID. Nothing morethan that. But it gives
solution for your query.
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
solution:
select StockID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by StockID;
Pls have a look into the table and the output for the query.
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 12:10 PM
Subject: Re: MAX + SUM in one query
Hi
no R.RequestENDDate=Date(now()) will work fine (I use it in other sql
queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi,
The query what you tried will return empty set only, since you have
compared
the RequestENDDate with now(), which always returns false[due to
seconds].
Try extracting the date part alone from RequestENDDate for the Where
cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal
Re: MAX + SUM in one query
Hi,
The query what you tried will return empty set only, since you have compared
the RequestENDDate with now(), which always returns false[due to seconds].
Try extracting the date part alone from RequestENDDate for the Where cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
RequestType=Offer for each Stock_StockID
I tried this
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
but it doesn't work.
Anyone know how to do it ?
Thanks
--
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: when does auto_increment_increment and auto_increment_offset come into being?
Hi Obviously!. These variables were introduced in MySQL 5.0.2. These system variables accommodate multi-master replication with AUTO_INCREMENT. Thanks ViSolve DB Team. - Original Message - From: Xueron Nee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 06, 2006 10:59 AM Subject: when does auto_increment_increment and auto_increment_offset come into being? Hi all, I am reading MySQL 5.1 Reference Manual and found about the two variables in Chapter 6. Replication. But I cannot find from when these two variables come into being. I am using 4.0.27 on my servers now, and can not find them by using show variables. Should I upgrade my servers? Thanks! -- Xueron Nee [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: MAX + SUM in one query
Hi
That's fine.
But for the query, I have created a simple table which simulates as that of
yours. I have used simple domain names.
I typed the StockID as RequestID. Nothing morethan that. But it gives
solution for your query.
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
solution:
select StockID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by StockID;
Pls have a look into the table and the output for the query.
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 12:10 PM
Subject: Re: MAX + SUM in one query
Hi
no R.RequestENDDate=Date(now()) will work fine (I use it in other sql
queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi,
The query what you tried will return empty set only, since you have
compared
the RequestENDDate with now(), which always returns false[due to
seconds].
Try extracting the date part alone from RequestENDDate for the Where
cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
RequestType=Offer for each Stock_StockID
I tried this
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
but it doesn't work.
Anyone know how to do it ?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Ahmad
http://www.v-tadawul.com
--
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: More than one MyQSL in a server
hi, have you traced .err file for the nature of the error? if not you can try that. Thanks ViSolve DB Team - Original Message - From: Mário Gamito [EMAIL PROTECTED] To: Nico Sabbi [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, November 02, 2006 4:31 PM Subject: Re: More than one MyQSL in a server Hi, I'm now trying to run the second MySQL with: # ./mysqld_safe --port=3307 --socket=/usr/local/mysql-5.0.27/share/mysql.sock2 --pid-file=/tmp/mysql.sock2 --datadir=/usr/local/mysql-5.0.27/var/ but i get: Starting mysqld daemon with databases from /usr/local/mysql-5.0.27/var/ STOPPING server from pid file /usr/local/mysql-5.0.27/share/mysql.sock2 061102 10:54:03 mysqld ended Any ideas ? Best Regards, Mário Gamito On 11/2/06, Nico Sabbi [EMAIL PROTECTED] wrote: Mário Gamito wrote: Hi, I have a 3.23 MySQL running in a server and i want to install 5.0.27 I made # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 # make # make install and then # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ But here, i get the error A mysqld process already exists How can i have the two MySQL running in the same machine ? Any help would be appreciated. Warm Regards, MG mysqld_multi works pretty well. It's documented in www.mysql.com/doc. mysqld_multi --example shows a sample config file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to retrieve a list of savepoint in mysql.
Hi, As of our understanding, MySQL can show whether the engines support Transactions and Savepoints. It does not list out the count or savepoints that were not deleted. But the solution for your query can be extracted from shell show engine innodb status\G or shellshow innodb status\G and also shell show warnings\G [lists all the warnings, notes, messages of the session] Thanks ViSolve DB Team. - Original Message - From: vijay bahirji [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 30, 2006 6:30 PM Subject: how to retrieve a list of savepoint in mysql. hi all,I need some one to help me with this. I have put many savepoints during the transaction in mysql andI want to have the list of all the savepoints set during the current transaction.plz help.regards,Vijay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of different length/size datatypes
Hi, To BLOB or not to BLOB | Whether to store string in BLOB, or CHAR, or VARCHAR pls play the link: http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm Thanks ViSolve DB Team. - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, October 27, 2006 10:35 PM Subject: Performance of different length/size datatypes Hello, Originally I had this long explanation of what I'm doing and why I'm asking this question but I thought I'd just cut to the chase and ask... For a db that doesn't get a lot queries is there much of a performance difference between BLOB and VARCHAR(255)? Thanks, Chris. -- 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]
