Re: How can i drop tables with the same predix by using only one single statement?
Sorry for the multiple post screw up.
My Bad
On Thu, 2006-10-12 at 11:43 +0800, Ow Mun Heng wrote:
On Wed, 2006-10-11 at 16:11 +0100, Mark Leith wrote:
Hey
方外 醉月 wrote:
How can i do if i want to drop tables with the same predix?
If you are using 5.0:
DELIMITER //
DROP PROCEDURE drop_table_prefix//
CREATE PROCEDURE drop_table_prefix(IN dbs VARCHAR(64), IN pref VARCHAR(63))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE t_name VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = dbs
AND TABLE_NAME LIKE CONCAT(pref,'%');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t_name;
IF NOT done THEN
SET @qry = CONCAT('DROP TABLE ', t_name);
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
//
This is a good example for me to learn about procedures etc.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
MySQL and GFS
Hi, We are thinking of installing MySQL (4.1.x) on 2 nodes sharing the data over a SAN and using GFS. Is this setup correct, can the 2 nodes run active/active ? -- Taymour A El Erian System Division Manager RHCE, LPIC, CCNA, MCSE, CNA TE Data E-mail: [EMAIL PROTECTED] Web: www.tedata.net Tel:+(202)-3320700 Fax:+(202)-3320800 Ext:1101 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table status for innodb tables show innodb free 2 times
I recently deleted about 7.000.000 rows from a table, there are about 4.000.000 left. So I want to know how much space is free in table space now and execute: mysql show table status like table\G *** 1. row *** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 4354196 Avg_row_length: 210 Data_length: 917536768 Max_data_length: 0 Index_length: 2294349824 Data_free: 0 Auto_increment: 35040856 Create_time: 2006-10-12 10:29:36 Update_time: NULL Check_time: NULL Collation: latin1_german1_ci Checksum: NULL Create_options: Comment: InnoDB free: 6144 kB; InnoDB free: 1762304 kB 1 row in set (0,26 sec) Why does it show two values for InnoDB free? Which one is correct? I use MySQL 5.0.21 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the phrase
within double quote (''), then the characters matches only rows that contain
the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl. olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the phrase
within double quote (''), then the characters matches only rows that contain
the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MODE)
Thanks,
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 4:08 PM
Subject: Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl.
olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the
phrase within double quote (''), then the characters matches only rows that
contain the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Dominik, what does SHOW TABLE STATUS show for other tables? Are you using innodb_file_per_table? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php I recently deleted about 7.000.000 rows from a table, there are about 4.000.000 left. So I want to know how much space is free in table space now and execute: mysql show table status like table\G *** 1. row *** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 4354196 Avg_row_length: 210 Data_length: 917536768 Max_data_length: 0 Index_length: 2294349824 Data_free: 0 Auto_increment: 35040856 Create_time: 2006-10-12 10:29:36 Update_time: NULL Check_time: NULL Collation: latin1_german1_ci Checksum: NULL Create_options: Comment: InnoDB free: 6144 kB; InnoDB free: 1762304 kB 1 row in set (0,26 sec) Why does it show two values for InnoDB free? Which one is correct? I use MySQL 5.0.21 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
what does SHOW TABLE STATUS show for other tables? It shows 2 values for about 3 of 260 tables. So most tables are okay. It does not seem to depend on table size, as the other tables only have a few hundred rows. Are you using innodb_file_per_table? Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Dominik, I have now filed: http://bugs.mysql.com/bug.php?id=23211 about this. Is there any pattern that could explain why the double print is only in those 3 tables? What values does it print for the tables where the printout is wrong, and what values does it print for ok tables? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Heikki Tuuri wrote: FYI: http://lists.mysql.com/mysql/202574 what does SHOW TABLE STATUS show for other tables? It shows 2 values for about 3 of 260 tables. So most tables are okay. It does not seem to depend on table size, as the other tables only have a few hundred rows. Are you using innodb_file_per_table? Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Heikki thanks for filing that report. You can close it again. I had a look at the create-table statements for these 3 tables. As it turns out, the person who initially created those tables had a create statement like create table ... comment='InnoDB free: 6144 kB' for some tables. All my (well, his, I did not create these tables ... ) fault, sorry to have wasted your time. Regards Dominik Heikki Tuuri schrieb: Dominik, I have now filed: http://bugs.mysql.com/bug.php?id=23211 about this. Is there any pattern that could explain why the double print is only in those 3 tables? What values does it print for the tables where the printout is wrong, and what values does it print for ok tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OLAP for MySQL or an Aggregrate Table
You'd have to use another table. I don't believe mysql views will keep your 'moving average' values. If you're using 5.1, you can automate the select/insert with an event -- it's a cron like tool built into mysql. If you have a datetime field in either of the tables that represents the 'action' time of each piece of data, you could do it as: select DATE(datetime_field) AS period, Avg (A), Min(A), Max(A), AVG(B) min(B), max(B) from table1 left join table2 on table1.field = table2.field GROUP BY period On 10/11/06, Ow Mun Heng [EMAIL PROTECTED] wrote: Anyone here uses OLAP, running in MySQL? Any OpenSOurce OLAP query tools? How does one goes about doing OLAP? Is there any documentation w/ MySQL anywhere. (I'm currently looking at Pentaho and Mondrian etc but the Pre-configured demo didn't work as it should, meaning it doesn't run for X reasons) What about doing views? eg: I take a snapshot of the data, every 1 hours, and plug them into a view or another table and use that eg: select Avg (A), Min(A), Max(A), AVG(B) min(B), max(B) from table1 left join table2 on table1.field = table2.field etc.. etc.. Will that work? Then when I query, I query this newly created aggregrate table and I'm thinking of something along these lines. DATE | Month | Day | Hour | AVG | Min | Max| some other field 2006 | | | | 10| 0 | 100 | AAA --2006| Oct | | | 10.5| 1 | 101 | BBB --2006| Oct | 10| |10.2 | 3| 98| CCC etc..etc.. Will something like this work for a So called moving average?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: boolean search on phrase*
Hi ViSolve,
I have tried various combinations with + and *,
and with single and/or double quotes.
But unfortunately I can't get the proper results.
I get the impression that it's not possible ...
Thanks anyway, Cor
- Original Message -
From: Visolve DB Team [EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 11:05 AM
Subject: Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MODE)
Thanks,
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 4:08 PM
Subject: Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl.
olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the
phrase within double quote (''), then the characters matches only rows
that contain the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Boolean search on phrase*
Based on my experience, that wouldn't quite work. You'd need to preface each
word with a plus sign:
+olive +oil*
and it would return records regardless of the order in which the two words
appear, nor how far apart they are. That might be desirable, or it might
not.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Visolve DB Team [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 12, 2006 4:55 AM
To: C.R.Vegelin; mysql@lists.mysql.com
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you
enclose the phrase within double quote (''), then the
characters matches only rows that contain the phrase
literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive
oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: java mysql alias is being displayed blank?
On Wed, 2006-10-11 at 16:03 -0700, ADAM CZECH wrote: Does anyone know why a mysql alias would not display in the return a result? When I try to access say the first name with it's alias f_name, it is blank? , but it works for its column name first_name? This becomes more of a problem with subselects because how does one alias it? Servlet sql: sql = SELECT + u.id as user_id, + u.first_name as fname, + You're aliasing it as fname, not f_name. -- Pat Adams Digital Darkness Promotions Dallas Music Wiki http://digitaldarkness.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding duplicates
It seems to me I ought to be able to construct a query, probably using sub-SELECTs), that would do what I want. I have a table that looks roughly like this: CREATE TABLE prod (prod_num INTEGER UNIQUE, prod_title VARCHAR(255), updated DATE) UNIQUE PRIMARY KEY (prod_num); In this table there might be two records that have the same title, with different values for prod_num and updated. I want to find the prod_nums for the older of the two records. I know when the new ones were updated, so that can be a literal. I just haven't been able to think it through. Using NOT IN with the subquery gave me the inverse of what I wanted. This shouldn't be impossible to do, Access has a find duplicates wizard; but come to think of it, that lists both records and I never tried to limit it to just the older. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding duplicates
You're kind of heading down the right road. And this was discussed on the list not too long ago, how to delete duplicates. Here's one solution that will find the oldest duplicate(s): SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod JOIN ( SELECT prod_title,max(updated) maxdate FROM prod GROUP BY prod_title ) AS proda ON prod.prod_title=proda.prod_title AND prod.updated!=proda.maxdate That should show you all the duplicates. Although product title and date are not good values to check for duplicates on. If you have one product that has two records with the same date, this query won't find it because the record are exactly the same. You're prod_num is not specified as auto_increment, so you couldn't use that for uniqness. If it was auto_increment, then you know the latest record with have the highest number, then you would use that instead of updated. - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 12, 2006 1:42 PM Subject: Finding duplicates It seems to me I ought to be able to construct a query, probably using sub-SELECTs), that would do what I want. I have a table that looks roughly like this: CREATE TABLE prod (prod_num INTEGER UNIQUE, prod_title VARCHAR(255), updated DATE) UNIQUE PRIMARY KEY (prod_num); In this table there might be two records that have the same title, with different values for prod_num and updated. I want to find the prod_nums for the older of the two records. I know when the new ones were updated, so that can be a literal. I just haven't been able to think it through. Using NOT IN with the subquery gave me the inverse of what I wanted. This shouldn't be impossible to do, Access has a find duplicates wizard; but come to think of it, that lists both records and I never tried to limit it to just the older. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- 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: Finding duplicates
Actually, the prod_num IS an auto-increment field, so you're suggestion would work. It would be much better than updated, especially since I found out that someone was busily updating the old products after the new ones were put in. I just started down the wrong road and never rethought the matter. I will ponder your solution. I was starting with a list that just had the titles on it, that's why I have to use that as the check. In the event, I had the list of titles in an Excel spreadsheet, so I used macros to build a SELECT for each title. That was ugly, but at least it gave me the data. Thanks for your help. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, October 12, 2006 2:14 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding duplicates You're kind of heading down the right road. And this was discussed on the list not too long ago, how to delete duplicates. Here's one solution that will find the oldest duplicate(s): SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod JOIN ( SELECT prod_title,max(updated) maxdate FROM prod GROUP BY prod_title ) AS proda ON prod.prod_title=proda.prod_title AND prod.updated!=proda.maxdate That should show you all the duplicates. Although product title and date are not good values to check for duplicates on. If you have one product that has two records with the same date, this query won't find it because the record are exactly the same. You're prod_num is not specified as auto_increment, so you couldn't use that for uniqness. If it was auto_increment, then you know the latest record with have the highest number, then you would use that instead of updated. - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 12, 2006 1:42 PM Subject: Finding duplicates It seems to me I ought to be able to construct a query, probably using sub-SELECTs), that would do what I want. I have a table that looks roughly like this: CREATE TABLE prod (prod_num INTEGER UNIQUE, prod_title VARCHAR(255), updated DATE) UNIQUE PRIMARY KEY (prod_num); In this table there might be two records that have the same title, with different values for prod_num and updated. I want to find the prod_nums for the older of the two records. I know when the new ones were updated, so that can be a literal. I just haven't been able to think it through. Using NOT IN with the subquery gave me the inverse of what I wanted. This shouldn't be impossible to do, Access has a find duplicates wizard; but come to think of it, that lists both records and I never tried to limit it to just the older. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- 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: Slow Access When Inserting Records
Hi, I've tested the exact same ASP pages on my local computer, and the pages load very quickly. Therefore there must be a problem with dedicated windows server. Any ideas what the problem might be ? Thanks,Neil Date: Sun, 8 Oct 2006 17:17:07 -0500 To: mysql@lists.mysql.com From: [EMAIL PROTECTED] Subject: Re: Slow Access When Inserting Records At 01:56 PM 10/8/2006, Neil Tompkins wrote: Hi, We have a mySQL database running version 3.23.58 on a linux machine. This database is accessed from both a Windows IIS web server and a linuz zeus web server. On the linux server I'm not sure of what driver etc is installed, however the access time to save a record is far quicker than the windows server. On the windows server ODBC driver 3.51 is installed. We don't appear to have the problem when reading data from the server (SELECT FROM etc). All servers are on the same network. All data is accessed using ASP (active server pages, vb script). Any ideas what the problem might be ? Thanks, Neil Neil, How much quicker is Linux over Windows? How many rows are you adding at a time? The first thing to check is your NIC. How fast is the Windows NIC compared to the Linux NIC? Test your network to see what throughput you can achieve from each of them. A Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Grant privs to multiple tables at once?
Assume database 'biggie' with 15 tables, 10 of which start with 'foo_'. I want the user 'foouser' to have access only to those tables that begin with 'foo_'. I'm hoping that I am just being blind because I don't see anything in the manual or in the MySQL book on granting to multiple tables at once and the * wildcard appears to only work by itself, not when appended to a string (i.e. I can't do grant select on biggie.foo_* to 'foouser'...). I've tried multiple variations of wildcards, to no avail. Please tell me I'm not going to have to explicitly grant privs to each table. There are actually several set of tables for a total of several hundred tables to which I will need to apply permissions. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best way to setup replication
I'd like to know what is the best way to setup the replication on my system. I hve all myisam tables and: - main db on production (master: system A; mysql 4.0.x); - slave db as backup/backoffice (system B; mysql 4.0.x); both are working and the replication works well. - system B is also the master for (system C; mysql 5.0.22) I did the hotcopy from the sistem B and I put it on my new sistem; the db is working well, but the slave on system C doesn't work, since I have a lot of duplicate keys in the insert queries. what I did wrong? why it seems I'm unable to sync system C with the other ones? what is the best 'recipe' to setup correctly a slave system (better without stop the master db, if it's possibile) thank you in advance :) bye bye MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant privs to multiple tables at once?
I think you should try % that is used as wildcard instead for one or many characters. Granting on 'foo_%' might work. Regards, Anders -- Anders Lundgren Master Software Engineer Viba IT Handelsbolag Web: http://www.vibait.se David Felio wrote: Assume database 'biggie' with 15 tables, 10 of which start with 'foo_'. I want the user 'foouser' to have access only to those tables that begin with 'foo_'. I'm hoping that I am just being blind because I don't see anything in the manual or in the MySQL book on granting to multiple tables at once and the * wildcard appears to only work by itself, not when appended to a string (i.e. I can't do grant select on biggie.foo_* to 'foouser'...). I've tried multiple variations of wildcards, to no avail. Please tell me I'm not going to have to explicitly grant privs to each table. There are actually several set of tables for a total of several hundred tables to which I will need to apply permissions. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best way to setup replication
MAS - what you may have forgotten to do is tell system C where (or when if you think of it that way) to start replicating. Either that or system C wasn't empty when you started replicating into it. The procedure to set up replication like this would be: freeze master, flush data, record binlog filename and position, then either mysqldump or mysqlhotcopy the data, then start master again. Copy the data into an empty slave (system C) and set the slave options to start reading at the correct file and position, then start replicating. See http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html Also have a look at the mysqldump --master-data option; could make things easy for you in terms of the snapshot. Of course if you are replicating at the table level, doing circular replication, etc., things are more complex - but sounds like you don't need to consider these right now. Dan On 10/12/06, MAS! [EMAIL PROTECTED] wrote: I'd like to know what is the best way to setup the replication on my system. I hve all myisam tables and: - main db on production (master: system A; mysql 4.0.x); - slave db as backup/backoffice (system B; mysql 4.0.x); both are working and the replication works well. - system B is also the master for (system C; mysql 5.0.22) I did the hotcopy from the sistem B and I put it on my new sistem; the db is working well, but the slave on system C doesn't work, since I have a lot of duplicate keys in the insert queries. what I did wrong? why it seems I'm unable to sync system C with the other ones? what is the best 'recipe' to setup correctly a slave system (better without stop the master db, if it's possibile) thank you in advance :) bye bye MAS! -- 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: Grant privs to multiple tables at once?
It doesn't. That was one of the multiple wildcard variations I already tried. David On Oct 12, 2006, at 3:13 PM, Anders Lundgren wrote: I think you should try % that is used as wildcard instead for one or many characters. Granting on 'foo_%' might work. Regards, Anders -- Anders Lundgren Master Software Engineer Viba IT Handelsbolag Web: http://www.vibait.se David Felio wrote: Assume database 'biggie' with 15 tables, 10 of which start with 'foo_'. I want the user 'foouser' to have access only to those tables that begin with 'foo_'. I'm hoping that I am just being blind because I don't see anything in the manual or in the MySQL book on granting to multiple tables at once and the * wildcard appears to only work by itself, not when appended to a string (i.e. I can't do grant select on biggie.foo_* to 'foouser'...). I've tried multiple variations of wildcards, to no avail. Please tell me I'm not going to have to explicitly grant privs to each table. There are actually several set of tables for a total of several hundred tables to which I will need to apply permissions. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling mysql 4.2.1 with linux
Hello, We are trying to build from source MySQL standard ver. 4.2.1 on an Intel-based machine running LINUX FEDORA Core 5 without success Using a binary distribution. No RPMs Hardware being used: Optiplex GX620 Dell system. Pentium D-based processor and 1 GB of memory. Kernel 2.6.15-1.2045_FC5 The compiler being used is gcc version 4.1.0 20060304 Apache 2.2.3 and PHP 5.1.6 is up and running on the system. This is a brand new Linux install without any previous installations of MySQL I proceeded to download and install unzip mysql-standard into the usr/local/ folder ... The filename is mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz. The step where it fails is basically the compilation process. Steps to reproduce: shell groupadd mysql shell useradd -g mysql mysql shell gunzip mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz | tar -xvf - shell cd mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23 shell ./configure --prefix=/usr/local/mysql At this point I get the following message [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# ./configure --prefix=/usr/local/mysql NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databases and start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables Fill help tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# Starting mysqld daemon with databases from /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23/data STOPPING server from pid file /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686 -glibc23/data/localhost.loca ldomain.pid 061012 09:04:35 mysqld ended [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# I looked at the section Dealing with Problens Compiling MySQL available here http://dev.mysql.com/doc/refman/4.1/en/compilation-problems.html But I wihtout success. I did however, clear the cache by doing: hell rm config.cache shell make clean Re-tried again and still no go. shell make *causes to generate the following error: shell make install * displays the following shell /usr/local/mysql/bin/mysqld_safe --user=mysql*gives me the following message: [EMAIL PROTECTED] bin]# ./mysqld_safe: line 183: my_print_defaults: command not found ./mysqld_safe: line 188: my_print_defaults: command not found The file /usr/local/mysql/bin/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html for more information I tried MySQL 5.0 with the exact same result. Anybody has any suggestions? All we are trying to do is to install apache + php + mysql. Being mysql the last one we need to get working. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling mysql 4.2.1 with linux
Hello, We are trying to build from source MySQL standard ver. 4.2.1 on an Intel-based machine running LINUX FEDORA Core 5 without success Using a binary distribution. No RPMs Hardware being used: Optiplex GX620 Dell system. Pentium D-based processor and 1 GB of memory. Kernel 2.6.15-1.2045_FC5 The compiler being used is gcc version 4.1.0 20060304 Apache 2.2.3 and PHP 5.1.6 is up and running on the system. This is a brand new Linux install without any previous installations of MySQL I proceeded to download and install unzip mysql-standard into the usr/local/ folder ... The filename is mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz. The step where it fails is basically the compilation process. Steps to reproduce: shell groupadd mysql shell useradd -g mysql mysql shell gunzip mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz | tar -xvf - shell cd mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23 shell ./configure --prefix=/usr/local/mysql At this point I get the following message [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# ./configure --prefix=/usr/local/mysql NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databases and start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables Fill help tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# Starting mysqld daemon with databases from /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23/data STOPPING server from pid file /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686 -glibc23/data/localhost.loca ldomain.pid 061012 09:04:35 mysqld ended [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# I looked at the section Dealing with Problens Compiling MySQL available here http://dev.mysql.com/doc/refman/4.1/en/compilation-problems.html But I wihtout success. I did however, clear the cache by doing: hell rm config.cache shell make clean Re-tried again and still no go. shell make *causes to generate the following error: shell make install * displays the following shell /usr/local/mysql/bin/mysqld_safe --user=mysql*gives me the following message: [EMAIL PROTECTED] bin]# ./mysqld_safe: line 183: my_print_defaults: command not found ./mysqld_safe: line 188: my_print_defaults: command not found The file /usr/local/mysql/bin/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html for more information I tried MySQL 5.0 with the exact same result. Anybody has any suggestions? All we are trying to do is to install apache + php + mysql. Being mysql the last one we need to get working. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql 4.2.1 with linux
Hi, You're not using the correct version. The right one to compile is under Source and other files downloads section at the bottom of the page. Regards, Deckard abraham c wrote: Hello, We are trying to build from source MySQL standard ver. 4.2.1 on an Intel-based machine running LINUX FEDORA Core 5 without success Using a binary distribution. No RPMs Hardware being used: Optiplex GX620 Dell system. Pentium D-based processor and 1 GB of memory. Kernel 2.6.15-1.2045_FC5 The compiler being used is gcc version 4.1.0 20060304 Apache 2.2.3 and PHP 5.1.6 is up and running on the system. This is a brand new Linux install without any previous installations of MySQL I proceeded to download and install unzip mysql-standard into the usr/local/ folder ... The filename is mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz. The step where it fails is basically the compilation process. Steps to reproduce: shell groupadd mysql shell useradd -g mysql mysql shell gunzip mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23.tar.gz | tar -xvf - shell cd mysql-standard-4.2.1-pc-linux-gnu-i686 –glibc23 shell ./configure --prefix=/usr/local/mysql At this point I get the following message [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# ./configure --prefix=/usr/local/mysql NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databases and start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables Fill help tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# Starting mysqld daemon with databases from /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23/data STOPPING server from pid file /usr/local/mysql-standard-4.1.21-pc-linux-gnu-i686 -glibc23/data/localhost.loca ldomain.pid 061012 09:04:35 mysqld ended [EMAIL PROTECTED] mysql-standard-4.1.21-pc-linux-gnu-i686-glibc23]# I looked at the section Dealing with Problens Compiling MySQL available here http://dev.mysql.com/doc/refman/4.1/en/compilation-problems.html But I wihtout success. I did however, clear the cache by doing: hell rm config.cache shell make clean Re-tried again and still no go. shell make *causes to generate the following error: shell make install * displays the following shell /usr/local/mysql/bin/mysqld_safe --user=mysql*gives me the following message: [EMAIL PROTECTED] bin]# ./mysqld_safe: line 183: my_print_defaults: command not found ./mysqld_safe: line 188: my_print_defaults: command not found The file /usr/local/mysql/bin/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html for more information I tried MySQL 5.0 with the exact same result. Anybody has any suggestions? All we are trying to do is to install apache + php + mysql. Being mysql the last one we need to get working. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving DB to another System
Hi All, Wondering if it's possible for me to just tar up the DB (eg: TEST_DB) and then move it AS IS to another system? Is this possible or will I have to do a mysqldump (inclusive of create tables / data etc)?? It would be good if I can just copy (tar) the DB to another system and then re-attach the DB to the new system. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do i initialize a new datadir in mysql ?
Dear all, If I am already have a datadir in /var/lib/mysql and I intend to start a new one in /var/lib/mysql2, how do i do it ? Thanks in advance, Low Kian Seong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do i initialize a new datadir in mysql ?
Hi, Create the initial databases and start the database with the following commands: shell mysql_install_db --datadir=/var/lib/mysql2 shell mysqld_safe --datadir=/val/lib/mysql2 Thanks ViSolve DB Team. - Original Message - From: Low Kian Seong [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, October 13, 2006 10:11 AM Subject: How do i initialize a new datadir in mysql ? Dear all, If I am already have a datadir in /var/lib/mysql and I intend to start a new one in /var/lib/mysql2, how do i do it ? Thanks in advance, Low Kian Seong -- 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]
