Starting a Second Server under Mac OS X 10.3.9
I'm trying to get a second server running using mysql 4.1.15 on Mac OS X 10.3.9. I have one server running as my main development server, and would like a second to use for testing alternate DB Schema. I've followed the instructions in the manual section 5.12.2 'running multiple servers on unix', but have only tried to use the instructions regarding running a second server from the same installation as the first. I gave the following command-line options to mysqld_safe: --datadir=/path/to/alternate/datadir --log-bin=testmysql-bin --port=3307 --socket=/tmp/testmysql.sock where the path was one appropriate to my installation, but I got back an error saying: A mysqld process already exists Is it necessarry to also designate an alternate pid file for the test installation? Any other ideas why this fails? Any suggestions for getting it to work without having to install an etirely separate mysql to run it off? Thanks, C Cris Ewing CME and Telehealth Web Services Department of Radiology Web Services University of Washington School of Medicine Work Phone: (206) 685-9116 Home Phone: (206) 365-3413 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trimming down a MySQL installation
I am installing MySQL on a server with limited space available and I'd like to minimize the footprint of the installed database. is there information available on what parts of the installation I can safely delete without harming functionality of the database? I'd love to hear recommendations from the experts out there before I just hack-and-slash through the mysql directory. Thanks in advance, Cris Cris Ewing CME and Telehealth Web Services University of Washington School of Medicine Work Phone: (206) 685-9116 Home Phone: (206) 365-3413 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
On Fri, 28 Jan 2005, Santino wrote:
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten good
results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description,
t6.course_intended_audience) AGAINST ('care')
So, does seearching on multiple columns only work if you create the
fulltext index on all of them at the same time? When I read the docs they
seemed to imply that indeces created on multiple columns wouldn't be
individually searchable. In other words, if I create a fulltext index on
col1, col2, and col3, then I will not be able to match against only col1.
I created the three as separate indeces so that I could maintain the
ability to search each separately at some point. Am I wrong to do so?
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date>'2005-02-01'
AND
t2.course_end_date<'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside that
still has me confused. If I just slightly alter the order of all the
various sub-clauses in the WHERE portion of the query, I get some courses
that violate the requirement "course_type_code='MJ'" (last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right after the
WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date>'2005-02-01'
AND
t2.course_end_date<'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
I think you must use ():
Yep, that fixed the problem, now I can put the ORed portion of the filter
at the front, where it logically seems to belong and all works just fine.
Thanks for the assist!
WHERE (
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
)
AND
t2.course_start_date>'2005-02-01'
AND
t2.course_end_date<'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later requirements,
such as the ones on course_start_date, course_end_date, and so on to the
end.
Is there a requirement as to which order sub-clauses of a WHERE clause have
to follow? I couldn't find anything that described this, but I'm perfectly
willing to admit I have a hard time finding lots of things in the online
docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten
good results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date>'2005-02-01'
AND
t2.course_end_date<'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside that
still has me confused. If I just slightly alter the order of all the
various sub-clauses in the WHERE portion of the query, I get some courses
that violate the requirement "course_type_code='MJ'" (last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right after
the WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date>'2005-02-01'
AND
t2.course_end_date<'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later
requirements, such as the ones on course_start_date, course_end_date, and
so on to the end.
Is there a requirement as to which order sub-clauses of a WHERE clause
have to follow? I couldn't find anything that described this, but I'm
perfectly willing to admit I have a hard time finding lots of things in
the online docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing <[EMAIL PROTECTED]> wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST
Re: Full text search in mulitple-table query
Shawn,
Thanks for the reply.
Here's the output of "SHOW CREATE TABLE" for one of the tables in
question:
CREATE TABLE `tblcourseextrainfo` (
`course_id` varchar(6) NOT NULL default '',
`course_description` text,
`course_intended_audience` text,
`course_keywords` text,
PRIMARY KEY (`course_id`),
FULLTEXT KEY `keywords` (`course_keywords`),
FULLTEXT KEY `course_description` (`course_description`),
FULLTEXT KEY `course_intended_audience` (`course_intended_audience`)
) TYPE=MyISAM
As you can see, I've created individual fulltext indeces for three fields
in this table, there is another table called 'tblCourses' where I have a
field called 'course_title' that also has a fulltext index. The query in
question pulls information from these two tables and about 4 others. The
result is a list of courses with all the information our customers need to
see.
here's a sample of what the sql from one query might look like:
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords,
t6.course_description
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_type_code='MJ'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t2.course_start_date>'2005-01-01'
AND
t2.course_end_date<'2005-12-31'
AND
MATCH (t6.course_keywords,t1.course_title)
AGAINST ('kidney,rheumatic');
Can you see any problems here that I'm missing?
Thanks,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing <[EMAIL PROTECTED]> wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure? ("SHOW CREATE TABLE \G"
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are worried about size/secrets.)
That would go a long way to help us understand your problems.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search in mulitple-table query
I'm running a query that pulls information from about six different tables
in a DB. I'd like to be able to do a fulltext search on fields in several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry that
references more than one table? What would be the correct syntax for such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax problems in MySQL v. 4.1.1-alpha-standard-log
Apologies if this is an easy one, I'm stumped! I'm having some trouble with syntax running MySQL from the command line. I'm running MySQL 4.1.1-alpha from the OS X standard installer package on a G5 running panther. I have logged into MySQL as the root user, providing the correct password, and I wish to see a list of the databases I have available, so I enter the following command: mysql> show databases; I get back the following: ERROR: No Query Specified If I try again, adding a 'Like' clause, things clear up: mysql> show databases like '%'; ++ | Database (%) | ++ | cme_admin | | cme_course_administration | | cme_course_finances| | cme_course_info| | cme_faculty_info | | cme_public | | cme_user_data | | mysql | | test_course_administration | | test_course_finances | | test_course_info | | test_faculty_info | ++ This same type of problem is cropping up in a number of different settings. For example, I can use the test_course_info database: mysql> use test_course_info; Database changed But trying to show the tables in this database suffers from another syntax problem: mysql> show tables from test_course_info; ERROR 12 (HY000): Can't read dir of './test_course_/' (Errcode: 2) It seems that the full name of my database is not being read for some reason. So I try just to show tables, after all, I am using the proper database, right? mysql> show tables; ERROR: No query specified So what if I try the same as before, and use wildcards? mysql> show tables like 'tbl%'; +---+ | Tables_in_test_course_info (tbl%) | +---+ | tblcoursecomments | | tblcoursedaterepeatexceptions | | tblcoursedaterepeats | | tblcoursedates| | tblcoursedatesubgroups| | tblcoursefaculty | | tblcourselocations| | tblcourses| | tblcoursesponsors | | tblcoursetypes| | tbldaterepeatperiod1s | | tbldaterepeatperiod2s | | tbldays | | tblensessions | | tbllocations | | tblregfromaccess | | tblsponsors | +---+ 17 rows in set (0.00 sec) Similar problems are showing up with things as simple as a select query: mysql> select * from tblcourses; ERROR 1096 (HY000): No tables used BUT I JUST LOOKED There is in fact a table called tblcourses in the databases. What am I doing wrong here? So, lets try to see the columns in this table: mysql> show columns from tblCourses; ERROR: No query specified mysql> show columns from tblCourses like '%'; **Big list of columns deleted to save space** One of the colums is called course_id (it's the primary key, in fact). Lets try to select that column: mysql> select course_id from tblCourses; ERROR 1054 (42S22): Unknown column 'course_' in 'field list' Once again, we seem to be missing everything after the final underscore in the column name. I'm stumped as to why these queries are not working. Does anyone have suggestions for me? Thanks, Cris Cris Ewing CME and Telehealth Web Services University of Washington School of Medicine Work Phone: (206) 685-9116 Home Phone: (206) 365-3413 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
