ANN: Database Workbench 2.8.9 released!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.9 has been released today! Download a trial at: http://www.upscene.com What's new?: http://www.upscene.com/products/dbw/whatsnew.htm Full list of features and fixes: http://www.upscene.com/news/20070813.htm Database Workbench supports: - Borland InterBase ( 4.x - 8.x ) - Firebird ( 1.x, 2.0 ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 2, SQL Express ) - MySQL 4, 4.1, 5.0 - Oracle Database ( 8i, 9i, 10g ) - NexusDB ( 2.05 and up ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New/improved - InterBase 8 support - NexusDB 2 support - Firebird 2 support - Microsoft SQL 2005, Service Pack 1 support - MySQL 5 support - Two-way Visual Query Builder - Increased Oracle support - New SQL Insight - Create INSERT script from ODBC datasource - TIFF support in BLOB Editor Enhancements and bugfixes related to Import/Export, DataPump, Code Editors, SQL Insight, NexusDB module performance and much more. Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: [commercial] MySQL cluster setup and support
-- Forwarded message -- From: C K [EMAIL PROTECTED] Date: Aug 13, 2007 11:37 AM Subject: [commercial] MySQL cluster setup and support To: [EMAIL PROTECTED] Dear all, we are a medium sized company in India having mfg. facilities about 65 kms. from Pune and Head Office in Pune. We are running a mysql based ERP system for our operations. Now we are shifting some of our departments to Pune and requires support to connect two mysql database servers through VPN/Leased Line. If any commercial service provider from PUNE or nearby cities in INDIA only can give such service related to MySQL cluster/replication etc., please submit your proposals to [EMAIL PROTECTED] Please call 9975844665 for more details. Thank you. CPKulkarni -- Keep your Environment clean and green. -- Keep your Environment clean and green.
why the max value of innodb_buffer_pool_size is always 4G
I install MySQL on Linux 2.6.9-52bs #2 x86_64 x86_64 x86_64 GNU/Linux, 16G memory with followiing step: 1. ./configure --prefix=/home/mysql/mysql2. make3. make install I set innodb_pool_buffer_size = 8G in my.cnf, but when I use show variables like 'innodb_buffer_pool_size'; the result is : +-++ | Variable_name | Value | +-++ | innodb_buffer_pool_size | 4093640704 | +-++ and I change the innodb_pool_buffer_size to 5G in my.cnf, the result is+-++ | Variable_name | Value | +-++ | innodb_buffer_pool_size | 1996488704 | +-++ why?? chuning
Re: Find record between 10 minutes
Good morning, Willy -
If you're using some sort of scripting language, like PHP, this would be
easily done by starting a variable at the beginning of the date range, then
repeating the below query and incrementing the variable by 10 minutes each
time through a while loop until you've passed the other end of the date
range.
Something like this:
$date = '01-01-2007 00:00:00';
while ($date = '01-02-2007 23:59:59') {
SELECT * FROM my_table
WHERE id IN
( SELECT id FROM my_table
WHERE datefield = $date
AND datefield DATE_ADD($date, INTERVAL 10 MINUTE) )
ORDER BY RANDOM
LIMIT 1
$date = $date + (php function to add 10 minutes);
}
I'm not a PHP whiz but hopefully you get the idea.
Dan
On 8/12/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi,
I have a table with records dated 01-01-2007 00:00:00 to 01-02-2007
23:59:59. What I want to do is grab 1 random record in every 10 minutes
between the date. Please help me.
Regards,
Willy
--
www.sangprabv.web.id
www.binbit.co.id
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find record between 10 minutes
[EMAIL PROTECTED] wrote: Hi, I have a table with records dated 01-01-2007 00:00:00 to 01-02-2007 23:59:59. What I want to do is grab 1 random record in every 10 minutes between the date. Please help me. Regards, Willy Does it really need to be random? This works from me: mysql select `date` from table where mod(unix_timestamp(`date`), 600) = 0 limit 10; +-+ | date| +-+ | 2007-01-01 00:00:00 | | 2007-01-01 00:10:00 | | 2007-01-01 00:20:00 | | 2007-01-01 00:30:00 | | 2007-01-01 00:40:00 | | 2007-01-01 00:50:00 | | 2007-01-01 01:00:00 | | 2007-01-01 01:10:00 | | 2007-01-01 01:20:00 | | 2007-01-01 01:30:00 | +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recommend a good database book
i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. thanks in advance, -- Jonathan Horne http://dfwlpiki.dfwlp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommend a good database book
Jonathan Horne wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. thanks in advance, Do you want to learn database design theory in general or learn how to use MySQL? The MySQL 5.0 Certification Study Guide is a really good book for getting started with MySQL and covers almost everything you'll need to know. As far as theory... I think Database Design For Mere Mortals - A Hands-On Guide To Relational Database Design, 2nd Edition is a pretty good starting point. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommend a good database book
On Monday 13 August 2007 10:43:33 Gary Josack wrote: Jonathan Horne wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. thanks in advance, Do you want to learn database design theory in general or learn how to use MySQL? The MySQL 5.0 Certification Study Guide is a really good book for getting started with MySQL and covers almost everything you'll need to know. As far as theory... I think Database Design For Mere Mortals - A Hands-On Guide To Relational Database Design, 2nd Edition is a pretty good starting point. i think my answer is... database design theory, and how i will apply it using mysql. :) thanks for your advice, -- Jonathan Horne http://dfwlpiki.dfwlp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query involving ORDER BY
Fellow MySQLers,
I have a query that performs poorly and I was hoping that someone could
make a recommendation as to how to
improve it's performance. The query involves a join between the
fs_syslog table (~700K rows) and the hosts
table (~100 rows). I am using MySQL 5.0.41 running on CentOS 4.4 (2.6
Kernel). There is only 1 index on the
fs_syslog besides the primary key:
ALTER TABLE fs_syslog ADD INDEX fs_syslog_1 (src_time DESC);
Here is the table definition for fs_syslog:
CREATE TABLE `fs_syslog` (
`id` bigint(20) NOT NULL auto_increment,
`facility` int(11) default NULL,
`severity` int(11) default NULL,
`host` varchar(255) default NULL,
`src_ip` varchar(255) default NULL,
`src_time` timestamp NULL default NULL,
`srv_ip` varchar(255) default NULL,
`srv_time` timestamp NULL default NULL,
`tag` varchar(255) default NULL,
`content` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fs_syslog_1` (`src_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And here it is for the hosts table:
CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL default '0',
`host` varchar(64) NOT NULL default '',
`dns` varchar(64) NOT NULL default '',
`useip` int(11) NOT NULL default '1',
`ip` varchar(15) NOT NULL default '127.0.0.1',
`port` int(11) NOT NULL default '10050',
`status` int(11) NOT NULL default '0',
`disable_until` int(11) NOT NULL default '0',
`error` varchar(128) NOT NULL default '',
`available` int(11) NOT NULL default '0',
`errors_from` int(11) NOT NULL default '0',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here is the query. As you can see there is an ORDER BY which is the
culprit. I would like to get rid of the ORDER BY
and rely on the index (DESC) to give me back the most recent 10 rows
from the join, but currently DESC doesn't
work when creating the index. Removing the ORDER BY makes the query
return almost instantaneously,
but when the ORDER BY is in place, they query takes anywhere from 3 secs
to 20 secs (but usually around
3 secs). The fs_syslog table only contains 700K rows now but it will
eventually contains tens of millions of rows.
I am trying to return the 10 most recently occurring syslog messages
which is the reason for the LIMIT clause.
Any help would be greatly appreciated.
SELECT fsys.facility, fsys.severity, h.host, fsys.src_ip, fsys.src_time,
fsys.content, fsys.tag
FROM fs_syslog fsys, hosts h
WHERE fsys.src_ip=h.ip
AND h.status 4
AND h.hostid in ({about 40 entries here})
AND h.host in ({about 40 entries here}))
AND fsys.src_time = DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR)
ORDER BY fsys.src_time DESC
LIMIT 0,10;
Thanks,
Mark Ponthier
Re: recommend a good database book
On 8/13/07, Jonathan Horne [EMAIL PROTECTED] wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. The book at home I have is entitled something like Beginning Databases with MySQL. I'm happy with it. http://www.amazon.com/Beginning-Databases-MySQL-Richard-Stones/dp/1861006926/ref=sr_1_1/105-0224235-8578830?ie=UTF8s=booksqid=1187026358sr=8-1 Also, if you are in the continental US, you might check out www.half.com and search by MySQL. You can get several great used books for the price of one new book. Sometimes you can get lucky and get the books for something like $1 each plus shipping. Dave.
Re: recommend a good database book
On 8/13/07, Jonathan Horne [EMAIL PROTECTED] wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. One more thing. In general, the practical issues (getting simple projects to work) are covered in a book like Beginning Databases with MySQL, but for the theoretical issues, it might be best for you to take a course or two at a local university. For example, understanding the performance impact of indexes requires understanding a little about O(N), O(log N), etc. Here are some things that are relevant: http://en.wikipedia.org/wiki/Database_index http://en.wikipedia.org/wiki/Big_O_notation http://en.wikipedia.org/wiki/Btree In general, you strive to make every database operation perform no worse than O(log N) as the database grows. Understanding how to do this requires a bit of theory. I don't think you'd find that in a book like Beginning Databases with MySQL. Dave.
Server can't find database directory
I built a laptop this weekend with mysql server version 5.0.37-log. The laptop is running Fedora 7. Last night I was able to create a database directory -- sym linking from /var/lib/mysql to a larger disk, /usr/local/data. I was able to create tables and loaded 10 million rows of data into three tables. This morning I start up the laptop and bring up the mysql client to load previous days data and . show databases doesn't show my database :-(. The directory still exists, sym link is in place, tables are still there, the server is unable to see them. The file permissions are rwxrwx... for the actual directory (owner is mysql, grp is mysql) , the user mysql can see the entire path to the directory. any insight into this problem would be greatly appreciated. tcp tanstaafl
Fwd: There's not enough space in /var/lib/mysql/ ---help!!!!
I am using debian 4.0 and mysql-server-5.0 package. My server will not start because it states that there is not enough spaceCan someone assist? rider:~# df -h FilesystemSize Used Avail Use% Mounted on /dev/mapper/vg_house-lv_root 493G 468G 0 100% / tmpfs 1.5G 0 1.5G 0% /lib/init/rw udev 10M 96K 10M 1% /dev tmpfs 1.5G 0 1.5G 0% /dev/shm /dev/md0 274M 25M 235M 10% /boot I have a 500gb lvm partition...with hardly anything on it. I am new to lvm so i will need assistance..i think the package is failing because it is not reading lvm properly. I have removed the mysql package and now i can not re-install it on the partition...i would appriciate any help offered. thanks mjh
Syntax Error in Stored Procedure
Below is a new stored procedure that I am trying to make for building a SQL
with the supplied parameters.
1:DELIMITER $$
2:CREATE PROCEDURE `Search_Code_Samples`(
3:IN search_words VARCHAR(300)
4: , IN multi_word_condition VARCHAR(3)
5: , IN language_id INT
6: , IN sample_type CHAR(1)
7:)
8:READS SQL DATA
9:COMMENT 'Prepares and executes SQL to find records according to the
data provided.'
10:BEGIN
11:DECLARE Base_SQL VARCHAR(1500);
12:DECLARE Filter_Clause VARCHAR(3000);
13:DECLARE Final_SQL VARCHAR(5500);
14:DECLARE First_Instance CHAR(1);
15:DECLARE len_search_words INT;
16:DECLARE word_idx INT;
17:DECLARE last_pos INT;
18:DECLARE word_length INT;
19:DECLARE new_word varchar(200);
20:DECLARE Search_Clause VARCHAR(1000);
21:DECLARE this_Word varchar(200);
22:DECLARE no_more_rows INT default 0;
23:
24:-- First handle the list of search words
25:IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
26:SET multi_word_condition = 'AND';
27:END IF;
28:IF (sample_type IS NULL) OR (sample_type = ' ') THEN
29:SET sample_type = 'A';
30:END IF;
31:SET Base_SQL = 'select * from CodeSamples_View ';
32:SET Filter_Clause = '';
33:SET First_Instance = 'Y';
34:IF (search_words IS NOT NULL) AND (search_words '') AND
(search_words ' ') THEN
35:-- Parse the list of search words by spaces
36:SET len_search_words = LENGTH(search_words);
37:SET word_idx = 1;
38:SET last_pos = 1;
39:CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200)
NOT NULL);
40:WHILE (word_idx = len_search_words) DO
41:IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
42:SET word_length = word_idx - last_pos;
43:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
44:SET last_pos = word_idx;
45:IF (new_word '') THEN
46:INSERT INTO Temp_Word_List VALUES (new_word);
47:END IF;
48:END IF;
49:SET word_idx = word_idx + 1;
50:END WHILE;
51:-- Put in the very last word
52:IF (word_idx last_pos) THEN
53:SET word_length = word_idx - last_pos;
54:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
55:IF (new_word '') THEN
56:INSERT INTO Temp_Word_List VALUES (new_word);
57:END IF;
58:END IF;
59:-- Generate the SQL clause for the search words.
60:DECLARE words_list CURSOR FOR SELECT Search_Word FROM
Temp_Word_List;
61:DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
62:OPEN words_list;
63:REPEAT
64:FETCH words_list INTO this_Word;
65:IF (no_more_rows 1) THEN
66:IF (First_Instance = 'Y') THEN
67:SET First_Instance = 'N';
68:ELSE
69:SET Filter_Clause = concat(Filter_Clause,
multi_word_condition, ' ');
70:END IF;
71:SET Filter_Clause = concat(Filter_Clause,
'match(title,description) against(''', this_Word, ''') ');
72:END IF;
73:UNTIL (no_more_rows = 1) END REPEAT;
74:CLOSE words_list;
75:DROP TEMPORARY TABLE Temp_Word_List;
76:END IF;
77:
78:-- Language Id
79:IF (language_id IS NOT NULL AND language_id 0) THEN
80:IF (First_Instance = 'Y') THEN
81:SET First_Instance = 'N';
82:ELSE
83:SET Filter_Clause = concat(Filter_Clause, ' AND ');
84:END IF;
85:SET Filter_Clause = concat(Filter_Clause, 'language = ',
cast(language_id as CHAR(4)), ' ');
86:END IF;
87:
88:-- Sample type
89:IF (sample_type IS NOT NULL AND sample_type 'A' AND sample_type
'' AND sample_type ' ') THEN
90:IF (First_Instance = 'Y') THEN
91:SET First_Instance = 'N';
92:ELSE
93:SET Filter_Clause = concat(Filter_Clause, ' AND ');
94:END IF;
95:SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''',
sample_type, );
96:END IF;
97:
98:-- Construct the final SQL statement.
99:IF (Filter_Clause = '') THEN
100:SET Final_SQL = Base_SQL;
101:ELSE
102:SET Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
103:END IF;
104:
105:-- Execute the constructed SQL Statement.
106:PREPARE search_statement FROM Final_SQL;
107:EXECUTE search_statement;
108:END; $$
When attempting to compile this procedure I get this error:
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 'DECLARE
this_Word varchar(200); DECLARE no_more_rows INT
Re: There's not enough space in /var/lib/mysql/ ---help!!!!
Michael, it looks to me like your root partition is absolutely 100% chock full. Am I misunderstanding your request for help? Sounds like you are saying you think you have nothing in that partition - but your 'df -h' command is showing 0 bytes available in /. Dan On 8/13/07, Michael Habashy [EMAIL PROTECTED] wrote: I am using debian 4.0 and mysql-server-5.0 package. My server will not start because it states that there is not enough spaceCan someone assist? rider:~# df -h FilesystemSize Used Avail Use% Mounted on /dev/mapper/vg_house-lv_root 493G 468G 0 100% / tmpfs 1.5G 0 1.5G 0% /lib/init/rw udev 10M 96K 10M 1% /dev tmpfs 1.5G 0 1.5G 0% /dev/shm /dev/md0 274M 25M 235M 10% /boot I have a 500gb lvm partition...with hardly anything on it. I am new to lvm so i will need assistance..i think the package is failing because it is not reading lvm properly. I have removed the mysql package and now i can not re-install it on the partition...i would appriciate any help offered. thanks mjh
RE: Syntax Error in Stored Procedure
Tom,
I think the problem might be that you have to put all the DECLARE
statements at the top before the other statements. It seems like I had
a similar problem with this once.
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Tom Khoury [mailto:[EMAIL PROTECTED]
Sent: Monday, August 13, 2007 2:38 PM
To: mysql@lists.mysql.com
Subject: Syntax Error in Stored Procedure
Below is a new stored procedure that I am trying to make for building a
SQL
with the supplied parameters.
1:DELIMITER $$
2:CREATE PROCEDURE `Search_Code_Samples`(
3:IN search_words VARCHAR(300)
4: , IN multi_word_condition VARCHAR(3)
5: , IN language_id INT
6: , IN sample_type CHAR(1)
7:)
8:READS SQL DATA
9:COMMENT 'Prepares and executes SQL to find records according to
the
data provided.'
10:BEGIN
11:DECLARE Base_SQL VARCHAR(1500);
12:DECLARE Filter_Clause VARCHAR(3000);
13:DECLARE Final_SQL VARCHAR(5500);
14:DECLARE First_Instance CHAR(1);
15:DECLARE len_search_words INT;
16:DECLARE word_idx INT;
17:DECLARE last_pos INT;
18:DECLARE word_length INT;
19:DECLARE new_word varchar(200);
20:DECLARE Search_Clause VARCHAR(1000);
21:DECLARE this_Word varchar(200);
22:DECLARE no_more_rows INT default 0;
23:
24:-- First handle the list of search words
25:IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
26:SET multi_word_condition = 'AND';
27:END IF;
28:IF (sample_type IS NULL) OR (sample_type = ' ') THEN
29:SET sample_type = 'A';
30:END IF;
31:SET Base_SQL = 'select * from CodeSamples_View ';
32:SET Filter_Clause = '';
33:SET First_Instance = 'Y';
34:IF (search_words IS NOT NULL) AND (search_words '') AND
(search_words ' ') THEN
35:-- Parse the list of search words by spaces
36:SET len_search_words = LENGTH(search_words);
37:SET word_idx = 1;
38:SET last_pos = 1;
39:CREATE TEMPORARY TABLE Temp_Word_List (Search_Word
VARCHAR(200)
NOT NULL);
40:WHILE (word_idx = len_search_words) DO
41:IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
42:SET word_length = word_idx - last_pos;
43:SET new_word = TRIM(SUBSTRING(search_words,
last_pos,
word_length));
44:SET last_pos = word_idx;
45:IF (new_word '') THEN
46:INSERT INTO Temp_Word_List VALUES (new_word);
47:END IF;
48:END IF;
49:SET word_idx = word_idx + 1;
50:END WHILE;
51:-- Put in the very last word
52:IF (word_idx last_pos) THEN
53:SET word_length = word_idx - last_pos;
54:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
55:IF (new_word '') THEN
56:INSERT INTO Temp_Word_List VALUES (new_word);
57:END IF;
58:END IF;
59:-- Generate the SQL clause for the search words.
60:DECLARE words_list CURSOR FOR SELECT Search_Word FROM
Temp_Word_List;
61:DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
62:OPEN words_list;
63:REPEAT
64:FETCH words_list INTO this_Word;
65:IF (no_more_rows 1) THEN
66:IF (First_Instance = 'Y') THEN
67:SET First_Instance = 'N';
68:ELSE
69:SET Filter_Clause = concat(Filter_Clause,
multi_word_condition, ' ');
70:END IF;
71:SET Filter_Clause = concat(Filter_Clause,
'match(title,description) against(''', this_Word, ''') ');
72:END IF;
73:UNTIL (no_more_rows = 1) END REPEAT;
74:CLOSE words_list;
75:DROP TEMPORARY TABLE Temp_Word_List;
76:END IF;
77:
78:-- Language Id
79:IF (language_id IS NOT NULL AND language_id 0) THEN
80:IF (First_Instance = 'Y') THEN
81:SET First_Instance = 'N';
82:ELSE
83:SET Filter_Clause = concat(Filter_Clause, ' AND ');
84:END IF;
85:SET Filter_Clause = concat(Filter_Clause, 'language = ',
cast(language_id as CHAR(4)), ' ');
86:END IF;
87:
88:-- Sample type
89:IF (sample_type IS NOT NULL AND sample_type 'A' AND
sample_type
'' AND sample_type ' ') THEN
90:IF (First_Instance = 'Y') THEN
91:SET First_Instance = 'N';
92:ELSE
93:SET Filter_Clause = concat(Filter_Clause, ' AND ');
94:END IF;
95:SET Filter_Clause = concat(Filter_Clause, 'sample_type =
''',
sample_type, );
96:END IF;
97:
98:-- Construct the final SQL
Re: Syntax Error in Stored Procedure
Thanks. That fixed the problem. I put all of my DECLARE statements at the
beginning of the procedure. I finally got the thing to compile and it looks
like this:
DELIMITER $$
DROP PROCEDURE IF EXISTS `code_library`.`Search_Code_Samples` $$
CREATE [EMAIL PROTECTED] PROCEDURE `Search_Code_Samples`(
IN search_words VARCHAR(300)
, IN multi_word_condition VARCHAR(3)
, IN language_id INT
, IN sample_type CHAR(1)
)
READS SQL DATA
COMMENT 'Prepares and executes SQL to find records according to the
data'
BEGIN
DECLARE Base_SQL VARCHAR(1500);
DECLARE Filter_Clause VARCHAR(3000);
DECLARE Final_SQL VARCHAR(5500);
DECLARE First_Instance CHAR(1);
DECLARE len_search_words INT;
DECLARE word_idx INT;
DECLARE last_pos INT;
DECLARE word_length INT;
DECLARE new_word varchar(200);
DECLARE Search_Clause VARCHAR(1000);
DECLARE this_Word varchar(200);
DECLARE no_more_rows INT default 0;
DECLARE words_list CURSOR FOR SELECT Search_Word FROM Temp_Word_List;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
-- First handle the list of search words
IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
SET multi_word_condition = 'AND';
END IF;
IF (sample_type IS NULL) OR (sample_type = ' ') THEN
SET sample_type = 'A';
END IF;
SET Base_SQL = 'select * from CodeSamples_View ';
SET Filter_Clause = '';
SET First_Instance = 'Y';
IF (search_words IS NOT NULL) AND (search_words '') AND (search_words
' ') THEN
-- Parse the list of search words by spaces
SET len_search_words = LENGTH(search_words);
SET word_idx = 1;
SET last_pos = 1;
CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200) NOT NULL);
WHILE (word_idx = len_search_words) DO
IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
SET last_pos = word_idx;
IF (new_word '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
SET word_idx = word_idx + 1;
END WHILE;
-- Put in the very last word
IF (word_idx last_pos) THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
IF (new_word '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
-- Generate the SQL clause for the search words.
OPEN words_list;
REPEAT
FETCH words_list INTO this_Word;
IF (no_more_rows 1) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, multi_word_condition, ' ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'match(title,description)
against(''', this_Word, ''') ');
END IF;
UNTIL (no_more_rows = 1) END REPEAT;
CLOSE words_list;
DROP TEMPORARY TABLE Temp_Word_List;
END IF;
-- Language Id
IF (language_id IS NOT NULL AND language_id 0) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'language = ', cast(language_id
as CHAR(4)), ' ');
END IF;
-- Sample type
IF (sample_type IS NOT NULL AND sample_type 'A' AND sample_type ''
AND sample_type ' ') THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''', sample_type,
);
END IF;
-- Construct the final SQL statement.
IF (Filter_Clause = '') THEN
SET @Final_SQL = Base_SQL;
ELSE
SET @Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
END IF;
-- Execute the constructed SQL Statement.
PREPARE search_statement FROM @Final_SQL;
EXECUTE search_statement;
DEALLOCATE PREPARE search_statement;
END $$
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: There's not enough space in /var/lib/mysql/ ---help!!!!
On 8/13/07, Dan Buettner [EMAIL PROTECTED] wrote: Michael, it looks to me like your root partition is absolutely 100% chock full. Am I misunderstanding your request for help? Sounds like you are saying you think you have nothing in that partition - but your 'df -h' command is showing 0 bytes available in /. Dan On 8/13/07, Michael Habashy [EMAIL PROTECTED] wrote: I am using debian 4.0 and mysql-server-5.0 package. My server will not start because it states that there is not enough spaceCan someone assist? rider:~# df -h FilesystemSize Used Avail Use% Mounted on /dev/mapper/vg_house-lv_root 493G 468G 0 100% / tmpfs 1.5G 0 1.5G 0% /lib/init/rw udev 10M 96K 10M 1% /dev tmpfs 1.5G 0 1.5G 0% /dev/shm /dev/md0 274M 25M 235M 10% /boot I have a 500gb lvm partition...with hardly anything on it. I am new to lvm so i will need assistance..i think the package is failing because it is not reading lvm properly. I have removed the mysql package and now i can not re-install it on the partition...i would appriciate any help offered. thanks mjh thanks for your assistance..i found the answer...i am not that familiar iwht lvm ..but getting used to it now..and du -sh * ---great command...found my run away process. thanks mjh
Re: MySQL Ends Enterprise Server Source Tarballs
It's nothing to be concerned about because the source tar balls and binaries are being mirrored at http://mirror.provenscaling.com/mysql/ -Eric On 8/10/07, Daevid Vincent [EMAIL PROTECTED] wrote: Is this anything to be concerned about? We are Enterprise customers. We distribute mySQL on our appliance that we sell. It doesn't seem like we should worry, now. But I'm a little nervous about the future? http://linux.slashdot.org/article.pl?sid=07/08/09/2047231 http://linux.slashdot.org/article.pl?sid=07/08/09/2047231from=rss from=rss http://www.linux.com/feature/118489 http://www.linux.com/feature/118489 ÐÆ5ÏÐ -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Ends Enterprise Server Source Tarballs
I think we/he is referring to the future of SOURCE tarballs being RELEASED by MySQL (in UPCOMING releases), not just current releases which is what the mirror hosts. On 8/13/07, Eric Bergen [EMAIL PROTECTED] wrote: It's nothing to be concerned about because the source tar balls and binaries are being mirrored at http://mirror.provenscaling.com/mysql/ -Eric On 8/10/07, Daevid Vincent [EMAIL PROTECTED] wrote: Is this anything to be concerned about? We are Enterprise customers. We distribute mySQL on our appliance that we sell. It doesn't seem like we should worry, now. But I'm a little nervous about the future? http://linux.slashdot.org/article.pl?sid=07/08/09/2047231 http://linux.slashdot.org/article.pl?sid=07/08/09/2047231from=rss from=rss http://www.linux.com/feature/118489 http://www.linux.com/feature/118489 ÐÆ5ÏÐ -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Craig Huffstetler
What's up with 5.1x beta
Does anyone have an explanation as to why it's taking so long for an official release of version 5.1? Is it me or does it seem like 5.1 is taking a lot longer to be released. I don't remember previous versions having 20 beta revs before an official release. Is there a real date available for an official release?
Re: What's up with 5.1x beta
It could have to do with 5.1 not being released as alpha until 5.0 was gamma. Here is a breakdown of the 4.0-5.1 releases and ho2 5.1 was different from the previous versions. http://ebergen.net/wordpress/2006/10/17/where-was-51/ -Eric On 8/13/07, Ed Reed [EMAIL PROTECTED] wrote: Does anyone have an explanation as to why it's taking so long for an official release of version 5.1? Is it me or does it seem like 5.1 is taking a lot longer to be released. I don't remember previous versions having 20 beta revs before an official release. Is there a real date available for an official release? -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index, unique index question
I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index, unique index question
Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query involving ORDER BY
It looks like u dont have index on fsys.src_ip and host.ip, please create
index on these two columns, and also do a explain of ur query, u will know ,
where the problem is.
regards
anandkl
On 8/13/07, Mark Ponthier [EMAIL PROTECTED] wrote:
Fellow MySQLers,
I have a query that performs poorly and I was hoping that someone could
make a recommendation as to how to
improve it's performance. The query involves a join between the
fs_syslog table (~700K rows) and the hosts
table (~100 rows). I am using MySQL 5.0.41 running on CentOS 4.4 (2.6
Kernel). There is only 1 index on the
fs_syslog besides the primary key:
ALTER TABLE fs_syslog ADD INDEX fs_syslog_1 (src_time DESC);
Here is the table definition for fs_syslog:
CREATE TABLE `fs_syslog` (
`id` bigint(20) NOT NULL auto_increment,
`facility` int(11) default NULL,
`severity` int(11) default NULL,
`host` varchar(255) default NULL,
`src_ip` varchar(255) default NULL,
`src_time` timestamp NULL default NULL,
`srv_ip` varchar(255) default NULL,
`srv_time` timestamp NULL default NULL,
`tag` varchar(255) default NULL,
`content` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fs_syslog_1` (`src_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And here it is for the hosts table:
CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL default '0',
`host` varchar(64) NOT NULL default '',
`dns` varchar(64) NOT NULL default '',
`useip` int(11) NOT NULL default '1',
`ip` varchar(15) NOT NULL default '127.0.0.1',
`port` int(11) NOT NULL default '10050',
`status` int(11) NOT NULL default '0',
`disable_until` int(11) NOT NULL default '0',
`error` varchar(128) NOT NULL default '',
`available` int(11) NOT NULL default '0',
`errors_from` int(11) NOT NULL default '0',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here is the query. As you can see there is an ORDER BY which is the
culprit. I would like to get rid of the ORDER BY
and rely on the index (DESC) to give me back the most recent 10 rows
from the join, but currently DESC doesn't
work when creating the index. Removing the ORDER BY makes the query
return almost instantaneously,
but when the ORDER BY is in place, they query takes anywhere from 3 secs
to 20 secs (but usually around
3 secs). The fs_syslog table only contains 700K rows now but it will
eventually contains tens of millions of rows.
I am trying to return the 10 most recently occurring syslog messages
which is the reason for the LIMIT clause.
Any help would be greatly appreciated.
SELECT fsys.facility, fsys.severity, h.host, fsys.src_ip, fsys.src_time,
fsys.content, fsys.tag
FROM fs_syslog fsys, hosts h
WHERE fsys.src_ip=h.ip
AND h.status 4
AND h.hostid in ({about 40 entries here})
AND h.host in ({about 40 entries here}))
AND fsys.src_time = DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR)
ORDER BY fsys.src_time DESC
LIMIT 0,10;
Thanks,
Mark Ponthier
