Re: Weird join needs
Currently I have a query that is able to return (doing a left join): 'A', 'B', 'E' 'A', 'B', 'F' 'C', 'D', NULL But I need this: 'A', 'B', 'E,F' 'C', 'D', NULL Can SQL help me in any way here ? or do I have to do it by programming ? If you use MySQL 4.1 or later take a look at http://dev.mysql.com/doc/mysql/en/group-by-functions.html and read the part about GROUP_CONCAT()... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select every second record
Hi SQL-Users Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? Thank you! Greetings, Martin
Re: How to select every second record
Hello, Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? Besides the obvious why? ... A table, by itself, does not have every second record, as it has particular order of records. A resultset of rows (being a selection of records from one or more tables) doesn't have a specific order, unless you tell it to have one. Only getting every 2nd row of a resultset, IMO, would be much easier by doing that on the client side. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server 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]
RE: How to select every second record
[snip] Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? [/snip] Not LIMIT, but you can use MOD, especially with an auto-increment field (id in this case is the auto-increment field) select * from table where mod(id, 2) '0' returns odd rows select * from table where mod(id, 2) '1' returns even rows -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
persistent temporary tables
i have come to realise that temporary tables are only persistent for 30 secs, is there a way to extend that ? My applications is attempting to store data before and entry is made and then insert the data from the temporary table after the entry is inserted then drop the temp table. This should work in theory but even with peristent php connections the tables are lost. Any ideas ? I have had to create heap tables with random named tables for each user, then drop them as i would with temp tables, is this a good idea ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to select every second record
[snip] Jay Blanchard wrote: [snip] Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? [/snip] Not LIMIT, but you can use MOD, especially with an auto-increment field (id in this case is the auto-increment field) select * from table where mod(id, 2) '0' returns odd rows select * from table where mod(id, 2) '1' returns even rows You have to do it with LIMIT beacuse id may not help you... so select * from table where condition [group by field] [order by field [desc]] LIMIT 2,1 this select just second resultrow from any kind of resultset made using every thing you like in where/order by/groub by and not being limited by using IDs (auto_increment) [/snip] The problem is that this only returns ONE record, the OP wanted every other record -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: drop table is written to bin-log, load table is NOT - why????
Hello Artem, thanks for your answer. For selective replication I would check startup options --replicate-do-* and --replicate-wild-*. I'm using replicate-do-db on the client but the problem is, that the master sends the sql-statements of ALL databases to the repl-client and the replicate-do-db-command only tells the client which sql-statements it should carry out and which statements it should drop without notice. In the actual case only 5% of the traffic is caused by the database which needs to be replicated so we would have to pay for 95% useless traffic if we wouldn't use daisy-chain Replication to filter the traffic. . Also you may need log-slave-update option on if you have daisy-chain replication. I'm using this option too but it seems to have no effect doing a load table ... from master. Greetings from Germany Lutz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corrupt table problem with snort, mysql, acid and ssh setup
I'm remotely administering a fedora 2 snort box via ssh. running snort with snort -c /etc.snort/snort.conf is fine - that is everything is logged to tables and shows in acid. But if i leave it running for over about a minute it buggers up. I lose my ssh and acid/apache service. I then have to get a guy from down the road to go and restart the thing for me. When I restart it i get this error message in acid: database: mysql_error: Got error 127 from table handler But nothing in mysqld.log This box is checking quite alot of traffic and i have minimised the rules to check against. when i run it for about 30 seconds it will not crash ssh or acid and acid can read the tables but everytime i do myisamchk afterwards i get : myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/var/lib/mysql/snort/acid_ip_cache.MYI' is usable but should be fixed myisamchk: error: Size of datafile is: 0 Should be: 4011 myisamchk: error: Found key at page 2048 that points to record outside datafile MyISAM-table '/var/lib/mysql/snort/event.MYI' is corrupted Fix it using switch -r or -o myisamchk: error: Size of datafile is: 0 Should be: 6112 myisamchk: error: Found key at page 2048 that points to record outside datafile MyISAM-table '/var/lib/mysql/snort/iphdr.MYI' is corrupted Fix it using switch -r or -o There are similar errors for most tables not just the 3 here. I don't think the guy up the road will go and restart it for me again so would appreciate any help. Could this be reaching maximum table cache or memory of some sort so not closing the tables properly? I can myisamchk recover tables and it will be fine but i'd like to run snort for longer and not have to worry about losing my remote connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of tablespace when using innodb_file_per_table
Hello. It is an unusual behaviour for InnoDB tables to create .MYD files. Execute 'show create table' statement on your table to see the type of storage engine. What output does the following statement produce: show variables like 'have_innodb'; Grumm, Carsten [EMAIL PROTECTED] wrote: Hello, I'm using InnoDB with the innodb_file_per_table option on. But now my .MYD-file has reached the maximum filesize of my filesystem and i have to add a new tablespace. Can someone tell me how? Thanks in advance for your help Carsten -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow connections with 4.1.9
Hello. Misterious 'unauthenticated user' problems appears very seldom in lists and bugs database with different versions. My opinion, that it is somehow related to the networking. Do you use --skip-name-resolve option? Have you compiled the MySQL server manually? Send us your config file and starting command line options. Can you find any clues in the error log? You may switch to debug version of the server and try to find out something in a trace file. Keith Thompson [EMAIL PROTECTED] wrote: Since upgrading my Solaris9 server to MySQL 4.1.9 (previously 4.1.3), remote connections to it from my WinXP laptop have become very slow. Remote connections using the mysql command-line tool (4.1.7 client), MySQL Query Browser (1.1.5) or JDBC connections with Connector/J (3.0.16) used to all connect immediately. Now they take anywhere from 10-30 seconds. The Query Browser has also become very frustrating because every time I execute a query it freezes for 10-30 seconds before finally executing each query. I have noticed that on the server during this long wait time, my new connection appears immediately, but mysqladmin processlist shows the user to be unauthenticated user until it finally completes the connection (and updates the user to the correct username). So, I'm not having network problems getting to the server or anything like that. Also, the server is not low on memory, low on connections, is not producing any errors, etc. Via Google I found a few occurrences of others with unauthenticated user issues, but they all seem to involve lots of connections in this state. In my case it's only one--the user very slowly connecting. This has been happening since a recent server update to 4.1.9 and never happened previously with 4.1.3. Also, it does not occur when making remote connections from the same PC to a 4.1.7 server. Any suggestions? -keith -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld restarts with no apparent reason
Hello. What's in the error log? Usually it can be found in the data directory, if another location wasn't specified in the config file or the command line options. MySQL server was restared by the mysqld_safe script, through the crash of mysqld process. Alfredo Reynoso [EMAIL PROTECTED] wrote: Hi, Im running MySQL 4.0.23 (build from source with skunkware gcc 2.95.2pl1 and a precompiled FSU-pthreads 3.5c) under SCO Openserver 5.0.4. It runs great by itself, the problem comes when I make a program using the C API, it restart mysqld the second (sometimes third) time I run my program, so my program hangs expecting the answer from the already finished mysqld. Here is the example (a.out is my program): # mysqld_safe --user=root --log 448 # Starting mysqld daemon with databases from /usr/local/mysql/var # ps -u root 448 tty01 00:00:00 mysqld_safe 468 tty01 00:00:01 mysqld # a.out # ps -u root 448 tty01 00:00:00 mysqld_safe 468 tty01 00:00:01 mysqld # a.out 050126 12:19:34 mysqld restarted //Here it hangs until I press Ctrl + Pause # ps -u root 448 tty01 00:00:00 mysqld_safe 480 tty01 00:00:01 mysqld The output in hostname.log is the following: /usr/local/mysql/libexec/mysqld, Version: 4.0.23-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock TimeId Command Argument 050126 12:19:29 1 Connect[EMAIL PROTECTED] on 1 Quit /usr/local/mysql/libexec/mysqld, Version: 4.0.23-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock TimeId Command Argument As you see, there is no message from the server that explains why does mysqld restarted. I've already googled for it, but there is no such case in the internet material. Can you help me? Thanks. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, record locking question
Hello. Use: set autocommit=0; or begin a transaction before executing 'select ... lock in share mode'. Commit the tramsaction to release lock. Mojtaba Faridzad [EMAIL PROTECTED] wrote: Hi, I am trying to learn more about InnoDB to convert MyISAM to InnoDB. according to MySQL document, I can lock a record like this: SELECT * FROM mytable WHERE id = '100' LOCK IN SHARE MODE; I run this query and showed a message to stop the screen (waiting) and on the other computer I run the same query or even I updated the record (which is locked by the other computer), but I didn't get any error. How does this work? Did I miss anything here? My other question: if I lock a record with that command, then how I can release the lock? I could find any command to release the lock! thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cascade on delete problem
Hello,
I apologize for a possible simple question, but I am having trouble with the
below code.
I have three simple tables. In short, menu_group has an id, that is
referenced in the menu_group_rel. When a user deletes an id from the
menu_group, I wanted the entry in menu_group_rel (data_id) to also be
deleted. They act as one piece of data.
I have gone through the docs, but when I delete a line from the menu_group, it
does NOT delete the entry from the menu_group_rel?
Does anyone see anything wrong with the following?
Thanks,
SQL: ###
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;
CREATE DATABASE builder2;
use builder2;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(data_id)
REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp');
Re: persistent temporary tables
At 08:30 AM 1/27/2005, electroteque wrote:
i have come to realise that temporary tables are only persistent for 30
secs, is there a way to extend that ? My applications is attempting to
store data before and entry is made and then insert the data from the
temporary table after the entry is inserted then drop the temp table. This
should work in theory but even with peristent php connections the tables
are lost. Any ideas ? I have had to create heap tables with random named
tables for each user, then drop them as i would with temp tables, is this
a good idea ?
Double check all of your code. I had a similar problem in the past only to
find that at least one call to the database in my script was NOT
persistent, so it would drop the temporary database. After making ALL
connections in my script persistent, I didn't have the problem any more.
Also:
1. You cannot refer to a TEMPORARY table more than once in the same query
AND in different SELECT queries combined by UNION operator.
2.
You cannot refer to a TEMPORARY table more than once in the same query. For
example, the following does not work:
mysql SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
quotes from http://dev.mysql.com/doc/mysql/en/temporary-table-problems.html
There are probably other things to check too. Maybe someone more
experienced will reply here. To test the 30 second problem, try
this. Create a simple script that creates a temporary table, populates it
with a couple of things, starts a counter that will count for more than 30
seconds, then read from the table and output the results. Don't have the
script doing anything else. This is just to see if your system really does
kill the table before 30 seconds. Test it with a counter less than 30
seconds also to make sure that it works for you.
I have two functions that I use to connect to my databases. Not sure where
I got them. Probably off of this list or the php list which is what I
normally program in. Yes, the functions are php code:
function get_mysql_query($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
$connection_close = @mysql_close($connection);
return $result;
}
function get_mysql_query_stay_open($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
return $result;
}
Normally when I use the second one in a script, I do NOT use the first one
without watching carefully what I am doing. If it is a small script, then
I will just use the get_mysql_query_stay_open query if I need to leave the
connection open and not even worry about closing the connection. I only
worry about it on larger scripts where it might eat up to many resources if
I leave it open when it is not needed to be left open.
Hope this might help in some small way.
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inoodb transactions
Hi
I am building a transactional sistem with mysql/innodb tables i would like to
make sure my steps are rigth, i am using php on the webserver to manipulate
these mysql tables.
---
Also i try to use select ... read commited to get the accurate data but it do
not work this syntax is rigth: SELECT Field FROM TABLE READ COMMITED
---
basically i am doing:
?
/***
START TRANSACTION
*/
$conn-execute(SET AUTOCOMMIT=0);
$conn-begin();
/***
COMITT TRANSACTION
*/
if($conn-error()){
$conn-rollback();
} else {
$conn-commit();
}
---
The entire code:
?
/***
COMEÇA TRANSAÇÂO
*/
$conn-execute(SET AUTOCOMMIT=0);
$conn-begin();
$conn-execute(DELETE FROM Table_1 WHERE Field_1=.$Field_1_final. AND
Field_2='.getParam(f_Field_2).');
$conn-execute($sqlChain-getSQL());
$sqlNormal-setAction(UPDATE);
$conn-execute($sqlNormal-getSQL());
if( $id_security-_atual ( !getParam(f_security) ) ) {
$sqlDelSecurity-setAction(INSERT);
$conn-execute($sqlDelSecurity-getSQL());
}
} else { // inclusão
/***
START TRANSACTION
*/
$conn-begin();
$sql-setAction(INSERT);
$conn-execute($sqlNormal-getSQL());
$conn-execute($sqlChain-getSQL());
}
if(!$id_current_security) {
if( getParam(f_security) ) {
$sqlSecurity-setAction(INSERT);
$conn-execute($sqlSecurity-getSQL());
}
}
/***
COMITT TRANSACTION
*/
if($conn-error()){
$conn-rollback();
} else {
$conn-commit();
}
?
i would like to know if anything is wrong is this the way a transaction must be
handled
Thank´s in advance
__
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
RE: cascade on delete problem
Hello Scott,
Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB;
If you have default MyISAM tables, it won't work because they don't support
foreign keys.
-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:17 AM
To: mysql@lists.mysql.com
Subject: cascade on delete problem
Hello,
I apologize for a possible simple question, but I am having
trouble with the below code.
I have three simple tables. In short, menu_group has an id,
that is referenced in the menu_group_rel. When a user
deletes an id from the menu_group, I wanted the entry in
menu_group_rel (data_id) to also be deleted. They act as
one piece of data.
I have gone through the docs, but when I delete a line from
the menu_group, it does NOT delete the entry from the menu_group_rel?
Does anyone see anything wrong with the following?
Thanks,
SQL: ###
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE DATABASE builder2;
use builder2;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'),
('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL
FOREIGN KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company',
'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities',
'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes',
'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service',
'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday',
'opensunday.jsp');
Attention:
Any views expressed in this message are those of the individual sender, except
where the message states otherwise and the sender is authorized to state them
to be the views of any such entity. The information contained in this message
and or attachments is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. If you
received this in error, please contact the sender and delete the material from
any system and destroy any copies.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: drop table is written to bin-log, load table is NOT - why????
Hello Lutz, I was not aware of this behavior of the master server. Maybe somebody with more insight can explain. If the traffic volume is so important I would turn on compression on the master-slave connection to reduce network traffic. I think it is slave_compressed_protocol=1 option in the [mysqld] section of the MySQL configuration file. Regards, Artem -Original Message- From: Lutz Maibach [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 9:58 AM To: mysql Liste Subject: Re: drop table is written to bin-log, load table is NOT - why Hello Artem, thanks for your answer. For selective replication I would check startup options --replicate-do-* and --replicate-wild-*. I'm using replicate-do-db on the client but the problem is, that the master sends the sql-statements of ALL databases to the repl-client and the replicate-do-db-command only tells the client which sql-statements it should carry out and which statements it should drop without notice. In the actual case only 5% of the traffic is caused by the database which needs to be replicated so we would have to pay for 95% useless traffic if we wouldn't use daisy-chain Replication to filter the traffic. . Also you may need log-slave-update option on if you have daisy-chain replication. I'm using this option too but it seems to have no effect doing a load table ... from master. Greetings from Germany Lutz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld restarts with no apparent reason
This is the error log (comes from the file hostname.com.err in the data directory). The part where mysqld is restarted comes at the end, I've cutted most of the restarts because the only diference is the hour, and because there were a lot of restarts. One more thing, I think this mail would begin a new thread because its a hand made reply. If that is the case Im really sorry, this is the first time I ever post in a mailing list, and I was expecting the answer in my mail, but I didnt got it that way. If no new thread is created void this comment =). 050122 19:01:56 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050122 19:01:57 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050122 19:02:15 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050122 19:02:17 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050122 19:02:23 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050122 19:02:23 Error in accept: Invalid argument 050125 11:02:18 mysqld started 050125 11:02:21 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 37082 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 050125 11:02:22 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 050125 11:02:22 InnoDB: Flushing modified pages from the buffer pool... 050125 11:02:23 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 11:32:52 mysqld started Fatal error: Can't change to run as user '=root'. Please check that the user exists! 050125 11:32:53 Aborting 050125 11:32:53 /usr/local/mysql/libexec/mysqld: Shutdown Complete 050125 11:32:53 mysqld ended 050125 11:33:24 mysqld started 050125 11:33:26 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 050125 11:33:27 InnoDB: Flushing modified pages from the buffer pool... 050125 11:33:27 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 11:57:00 mysqld restarted 050125 12:02:02 mysqld started 050125 12:02:05 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 050125 12:02:06 InnoDB: Flushing modified pages from the buffer pool... 050125 12:02:06 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 12:03:53 mysqld started 050125 12:03:56 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 050125 12:03:57 InnoDB: Flushing modified pages from the buffer pool... 050125 12:03:57 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 12:13:18 mysqld restarted 050125 12:13:19 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 050125 12:13:19 InnoDB: Flushing modified pages from the buffer pool... 050125 12:13:19 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 12:14:52 mysqld
re: Cascade problem now error:
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150)
mysql
- mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Connection id: 13
Current database: builder
Current user: [EMAIL PROTECTED]
SSL:Not in use
Server version: 4.0.15-max-debug
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1
Server characterset:latin1
TCP port: 3306
Uptime: 10 days 1 min 3 sec
Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1 Open t
ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory used:
8631K
--
UPDATED SCRIPT BELOW:
DROP DATABASE builder;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;
// sequence stuff
// mysql CREATE TABLE sequence (id INT NOT NULL);
//mysql INSERT INTO sequence VALUES (0);
//Use the table to generate sequence numbers like this:
//mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1);
//mysql SELECT LAST_INSERT_ID();
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
) type=INNODB;
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
) type=INNODB;
ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp');
-Original Message-
From: Artem Koltsov [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:29 AM
To: Scott Purcell; mysql@lists.mysql.com
Subject: RE: cascade on delete problem
Hello Scott,
Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB;
If you have default MyISAM tables, it won't work because they don't support
foreign keys.
-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:17 AM
To: mysql@lists.mysql.com
Subject: cascade on delete problem
Hello,
I apologize for a possible simple question, but I am having
trouble with the below code.
I have three simple tables. In short, menu_group has an id,
that is referenced in the menu_group_rel. When a user
deletes an id from the menu_group, I wanted the entry in
menu_group_rel (data_id) to also be deleted. They act as
one piece of data.
I have gone through the docs, but when I delete a line from
the menu_group, it does NOT delete the entry from the menu_group_rel?
Does anyone see anything wrong with the following?
Thanks,
SQL: ###
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE DATABASE builder2;
use builder2;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert
Re: Cascade problem now error:
Have you tried perror yet?
[EMAIL PROTECTED] (ping-300) 120 perror 150
MySQL error: 150 = Foreign key constraint is incorrectly formed
Cheers,
--V
Scott Purcell wrote:
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150)
mysql
- mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Connection id: 13
Current database: builder
Current user: [EMAIL PROTECTED]
SSL:Not in use
Server version: 4.0.15-max-debug
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1
Server characterset:latin1
TCP port: 3306
Uptime: 10 days 1 min 3 sec
Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1 Open t
ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory used:
8631K
--
UPDATED SCRIPT BELOW:
DROP DATABASE builder;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;
// sequence stuff
// mysql CREATE TABLE sequence (id INT NOT NULL);
//mysql INSERT INTO sequence VALUES (0);
//Use the table to generate sequence numbers like this:
//mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1);
//mysql SELECT LAST_INSERT_ID();
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
) type=INNODB;
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
) type=INNODB;
ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp');
-Original Message-
From: Artem Koltsov [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:29 AM
To: Scott Purcell; mysql@lists.mysql.com
Subject: RE: cascade on delete problem
Hello Scott,
Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB;
If you have default MyISAM tables, it won't work because they don't support
foreign keys.
-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 10:17 AM
To: mysql@lists.mysql.com
Subject: cascade on delete problem
Hello,
I apologize for a possible simple question, but I am having
trouble with the below code.
I have three simple tables. In short, menu_group has an id,
that is referenced in the menu_group_rel. When a user
deletes an id from the menu_group, I wanted the entry in
menu_group_rel (data_id) to also be deleted. They act as
one piece of data.
I have gone through the docs, but when I delete a line from
the menu_group, it does NOT delete the entry from the menu_group_rel?
Does anyone see anything wrong with the following?
Thanks,
SQL: ###
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE
RE: How to select every second record
Try this. The second set ... select gives you what you want. However, the group by may interfere with the rest of your logic. You also don't really need the mod(@a,2) in the result set, just in the having. mysql set @a:=0; Query OK, 0 rows affected (0.00 sec) mysql select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from er_poft limit 10; +--+---+-++ | @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month | +--+---+-++ |1 | 1 |4245 | 01 | |2 | 0 |4323 | 01 | |3 | 1 |4328 | 01 | |4 | 0 |4329 | 01 | |5 | 1 |4331 | 01 | |6 | 0 |4332 | 01 | |7 | 1 |4333 | 01 | |8 | 0 |4335 | 01 | |9 | 1 |4343 | 01 | | 10 | 0 |4344 | 01 | +--+---+-++ 10 rows in set (0.00 sec) mysql set @a:=0; Query OK, 0 rows affected (0.00 sec) mysql select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from er_poft group by 3,4 having mod(@a,2) = 0 limit 5; +--+---+-++ | @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month | +--+---+-++ |2 | 0 |4323 | 01 | |4 | 0 |4329 | 01 | |6 | 0 |4332 | 01 | |8 | 0 |4335 | 01 | | 10 | 0 |4344 | 01 | +--+---+-++ 5 rows in set (0.00 sec) -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 8:50 AM To: Alessandro Sappia; mysql@lists.mysql.com Subject: RE: How to select every second record [snip] Jay Blanchard wrote: [snip] Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? [/snip] Not LIMIT, but you can use MOD, especially with an auto-increment field (id in this case is the auto-increment field) select * from table where mod(id, 2) '0' returns odd rows select * from table where mod(id, 2) '1' returns even rows You have to do it with LIMIT beacuse id may not help you... so select * from table where condition [group by field] [order by field [desc]] LIMIT 2,1 this select just second resultrow from any kind of resultset made using every thing you like in where/order by/groub by and not being limited by using IDs (auto_increment) [/snip] The problem is that this only returns ONE record, the OP wanted every other record -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to select every second record
[snip] mysql set @a:=0; Query OK, 0 rows affected (0.00 sec) mysql select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from er_poft group by 3,4 having mod(@a,2) = 0 limit 5; +--+---+-++ | @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month | +--+---+-++ |2 | 0 |4323 | 01 | |4 | 0 |4329 | 01 | |6 | 0 |4332 | 01 | |8 | 0 |4335 | 01 | | 10 | 0 |4344 | 01 | +--+---+-++ 5 rows in set (0.00 sec) [/snip] Now THIS is an elegant solution. The variable removes the need for an auto-increment field. I can see that the group by might interfere with the results if you are not careful, but using any unique identifier in the record should work out well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cascade problem now error:
Thanks for the info on the command line util. I am new and was unaware of it.
But anyway, I have gone through the docs many more times, and I cannot figure
out where my problem is. Of course the error is a foreign key problem, but I do
not see where. I even am basically using a textbook example here that fails
with the same error:
If someone see a problem, please let me know, I am creating a fresh database
and just running those 30 or so lines below, and it errors with this:
ERROR 1005: Can't create table '.\builder\menu_group_rel.frm' (errno: 150)
mysql
Sincerely
Scott
CODE:
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
PRIMARY KEY (id),
) TYPE=InnoDB;
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
FOREIGN KEY (data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE
) TYPE=InnoDB;
ERRORS
-Original Message-
From: V. M. Brasseur [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 11:06 AM
To: mysql@lists.mysql.com
Subject: Re: Cascade problem now error:
Have you tried perror yet?
[EMAIL PROTECTED] (ping-300) 120 perror 150
MySQL error: 150 = Foreign key constraint is incorrectly formed
Cheers,
--V
Scott Purcell wrote:
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150)
mysql
- mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Connection id: 13
Current database: builder
Current user: [EMAIL PROTECTED]
SSL:Not in use
Server version: 4.0.15-max-debug
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1
Server characterset:latin1
TCP port: 3306
Uptime: 10 days 1 min 3 sec
Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1
Open t
ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory
used:
8631K
--
UPDATED SCRIPT BELOW:
DROP DATABASE builder;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell'
WITH GRANT OPTION;
// sequence stuff
// mysql CREATE TABLE sequence (id INT NOT NULL);
//mysql INSERT INTO sequence VALUES (0);
//Use the table to generate sequence numbers like this:
//mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1);
//mysql SELECT LAST_INSERT_ID();
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
) type=INNODB;
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
) type=INNODB;
ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN
KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
Creating indexes
Hi I have an aplication wich is opening to many connections even i am using persistent connectins and closing every connection i do open I have created indexes in all the fields i supose they are needed I would like to know if i can and how can i measure where indexes are needed or where they can open too many internal connections since my aplication does many searches through the database.. Thank´s in advance __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
RE: Cascade problem now error:
Problem somewhat solved: I am somewhat thrown by what I stumbled across, but it has to do with the _ underscore character. If I run the same below script without the _, it works fine. But as soon as I put in the underscore _ it fails with the error. This sounds pretty insane? They are definitely different names? Does anyone have input into this? Thanks, CREATE TABLE menu_sequence (id INT NOT NULL); insert into menu_sequence VALUES (0); CREATE TABLE MENU_GROUP(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE MENU_GROUPREL(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:59 AM To: V. M. Brasseur; mysql@lists.mysql.com Subject: RE: Cascade problem now error: Thanks for the info on the command line util. I am new and was unaware of it. But anyway, I have gone through the docs many more times, and I cannot figure out where my problem is. Of course the error is a foreign key problem, but I do not see where. I even am basically using a textbook example here that fails with the same error: If someone see a problem, please let me know, I am creating a fresh database and just running those 30 or so lines below, and it errors with this: ERROR 1005: Can't create table '.\builder\menu_group_rel.frm' (errno: 150) mysql Sincerely Scott CODE: GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH GRANT OPTION; CREATE DATABASE builder; use builder; CREATE TABLE menu_sequence (id INT NOT NULL); insert into menu_sequence VALUES (0); CREATE TABLE MENU_GROUP ( id int NOT NULL, parent_id int NOT NULL DEFAULT '0', sort int, visible VARCHAR(1) NOT NULL DEFAULT 'T', PRIMARY KEY (id), ) TYPE=InnoDB; CREATE TABLE MENU_GROUP_REL ( menu_type varchar(200), data_id int NOT NULL, display_name varchar(250), link varchar(250), FOREIGN KEY (data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE ) TYPE=InnoDB; ERRORS -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:06 AM To: mysql@lists.mysql.com Subject: Re: Cascade problem now error: Have you tried perror yet? [EMAIL PROTECTED] (ping-300) 120 perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed Cheers, --V Scott Purcell wrote: Thanks, I updated my script and all looks good. But now I get an error when tryng to issue this command. Any ideas? Thanks, Scott mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(d ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE; ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150) mysql - mysql \s -- mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32) Connection id: 13 Current database: builder Current user: [EMAIL PROTECTED] SSL:Not in use Server version: 4.0.15-max-debug Protocol version: 10 Connection: localhost via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 10 days 1 min 3 sec Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1 Open t ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory used: 8631K -- UPDATED SCRIPT BELOW: DROP DATABASE builder; GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH GRANT OPTION; // sequence stuff // mysql CREATE TABLE sequence (id INT NOT NULL); //mysql INSERT INTO sequence VALUES (0); //Use the table to generate sequence numbers like this: //mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1); //mysql SELECT LAST_INSERT_ID(); CREATE DATABASE builder; use builder; CREATE TABLE menu_sequence (id INT NOT NULL); insert into menu_sequence VALUES (0); CREATE TABLE MENU_GROUP ( id int NOT NULL, parent_id int NOT NULL DEFAULT '0', sort int, visible VARCHAR(1) NOT NULL DEFAULT 'T', ) type=INNODB; ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T'); UPDATE
Mysqldump unusable, bugged?
Having repeatable problems doing restores, 4.1.8 and 4.1.9 both do the same error Is there some setting I'm missing? mysqldump -u username-pPassword --all-databases --quote-names /intranet/backup/backup.sql E:\intranet\backupmysql -u xotech -pDaredevil22 -f backup.sql ERROR at line 2153: Unknown command '\m'. ERROR at line 2153: Unknown command '\m'. ERROR at line 2153: Unknown command '\m'. mysql: Out of memory (Needed 626767192 bytes) mysql: Out of memory (Needed 626763096 bytes) E:\intranet\backup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication talk.
Consider the simple case where we have a single master and a single slave. Ideally, we would redirect all writes to the master server only, and reads on the master AND the slave. Is it possible to redirect UPDATE, DELETE, INSERT queries only on the master server automatically, and SELECT* on the master and the slave ? Is this a MySQL configuration option that I missed in the doc ? Any pointers would be appreciated. Thank you ! - Frederic Trudeau [EMAIL PROTECTED] Programmation / Departement reseau Communications Accessibles Montreal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication talk.
No this is not a mysql option directly. Your going to have to change your database API level to write only to the master and read only from the slave. The only thing that mysql will do is enforce read only on a slave via GRANTS and write only on a master. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Frederic Trudeau [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 10:25 AM To: mysql@lists.mysql.com Subject: Replication talk. Consider the simple case where we have a single master and a single slave. Ideally, we would redirect all writes to the master server only, and reads on the master AND the slave. Is it possible to redirect UPDATE, DELETE, INSERT queries only on the master server automatically, and SELECT* on the master and the slave ? Is this a MySQL configuration option that I missed in the doc ? Any pointers would be appreciated. Thank you ! - Frederic Trudeau [EMAIL PROTECTED] Programmation / Departement reseau Communications Accessibles Montreal -- 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: Cascade problem now error:
Hi,
You have an error in the statement. See below. If you remove the comma after
primary key def everything should work.
[skip]
CODE:
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
PRIMARY KEY (id),
*^ this genereate error *
) TYPE=InnoDB;
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
FOREIGN KEY (data_id) REFERENCES MENU_GROUP(id) ON
DELETE CASCADE
) TYPE=InnoDB;
ERRORS
-Original Message-
From: V. M. Brasseur [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 11:06 AM
To: mysql@lists.mysql.com
Subject: Re: Cascade problem now error:
Have you tried perror yet?
[EMAIL PROTECTED] (ping-300) 120 perror 150
MySQL error: 150 = Foreign key constraint is incorrectly formed
Cheers,
--V
Scott Purcell wrote:
Thanks,
I updated my script and all looks good. But now I get an
error when tryng to issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT
PK_MENU_GROUP_REL FOREIGN KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table '.\builder\#sql-260_d.frm'
(errno: 150)
mysql
- mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Connection id: 13
Current database: builder
Current user: [EMAIL PROTECTED]
SSL:Not in use
Server version: 4.0.15-max-debug
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1
Server characterset:latin1
TCP port: 3306
Uptime: 10 days 1 min 3 sec
Threads: 1 Questions: 364 Slow queries: 0 Opens: 115
Flush tables: 1 Open t
ables: 0 Queries per second avg: 0.000 Memory in use:
8324K Max memory used:
8631K
--
UPDATED SCRIPT BELOW:
DROP DATABASE builder;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
IDENTIFIED BY 'spurcell' WITH GRANT OPTION;
// sequence stuff
// mysql CREATE TABLE sequence (id INT NOT NULL);
//mysql INSERT INTO sequence VALUES (0);
//Use the table to generate sequence numbers like this:
//mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1);
//mysql SELECT LAST_INSERT_ID();
CREATE DATABASE builder;
use builder;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
) type=INNODB;
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
CREATE TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'),
('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
) type=INNODB;
ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL
FOREIGN KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company',
'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities',
'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes',
'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer
Service',
RE: Creating indexes
Database size and records are find. But your table scanning. Look at Handler_read_rnd_next 1018281500 that indicates a table scan. This mean your not using your indexes effectivly or the tables are not set up with the proper indexes. Perform show full processlist and or enable log-slow-query log-long-format DVP Dathan Vance Pattishall http://www.friendster.com From: Ângelo M. Rigo [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:23 AM To: Dathan Pattishall Subject: RE: Creating indexes Thank´s again for sharing your mysql experience ! may you can point me if my numbers are too high? best regards!! records size TABLE1 225,893 InnoDB 54.6 MB TABLE2 611 MyISAM 122.3 KB TABLE3 497 MyISAM 19.7 KB TABLE4 49,930 InnoDB 8.0 MB TABLE5 431 InnoDB 80.0 KB TABLE6 139,933 InnoDB 43.7 MB 6 tables Sum 417,295 106.5 MB Variable_name Value Handler_commit 112 Handler_delete 2969004 Handler_read_first 71073 Handler_read_key 41714285 Handler_read_next 2199647292 Handler_read_prev 6942 Handler_read_rnd 915605 Handler_read_rnd_next 1018281500 Handler_rollback 639 Handler_update 31994410 Handler_write 281417564 Dathan Pattishall [EMAIL PROTECTED] wrote: no show full processlist to see which query or set of queries take the longest time. DVP Dathan Vance Pattishall http://www.friendster.com From: Ângelo M. Rigo [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:14 AM To: Dathan Pattishall Subject: RE: Creating indexes using the show status variables can i discover wich collumn or query is consuming resources and opening too many connections ? Dathan Pattishall [EMAIL PROTECTED] wrote: Perform show status. Show status has a few variables to take a global look at your mysql server and how keys are being used. show status like 'Hand%'; +---+--+ | Variable_name | Value| +---+--+ | Handler_commit| 0| | Handler_delete| 0| | Handler_discover | 0| | Handler_read_first| 1| | Handler_read_key | 27287397 | | Handler_read_next | 12891664 | | Handler_read_prev | 0| | Handler_read_rnd | 347638 | | Handler_read_rnd_next | 1031461 | | Handler_rollback | 3| | Handler_update| 7360212 | | Handler_write | 1591558 | +---+--+ Take special note to Handler_read_rnd_next. If it's high your doing a table scan. Look online for the rest of these vars, they are very helpful. I personally graph them over time to make sure things are good. DVP Dathan Vance Pattishall http://www.friendster.com http://www.friendster.com/ -Original Message- From: Ângelo M. Rigo
Re: A problem of structure
José Pablo Ezequiel Fernández [EMAIL PROTECTED] wrote on 01/26/2005 03:49:50 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 After the good comments I've got on this mailing list, I think I have the structure more or less complete. In some cases I follow the comments, in some others, I've improvised (hehehe). So, this is the (explained) structure, what do you think ? Anything I can improve ? monster snip So, in general, what do you think ? Thank you! - -- Pupeno: [EMAIL PROTECTED] - http://pupeno.com Reading Science Fiction ? http://sfreaders.com.ar [1] I'm making that table public in my web page here: http://pupeno.com/misc/languagesDB -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB+AJxfW48a9PWGkURAvC8AJ9YeNxHCt+ZgfJrl4nvcbYxCJy+lwCfX4Rk HxtIQOtUBlI2lQZmMMakoPw= =IZH6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I think you have an excellent prototype database and can begin your initial site development against it. You may find that you will need an additional column or two but I think it should be good enough to begin working with. Incremental refinements are just part of the fun! Who knows? you may hit an idea that completely changes this design and makes everything else work much faster! Don't let it discourage you. Some of my best database improvement ideas have come from scrapping 80% of one design and reworking it another way. One thing that I know you will be adding soon will be indexes. Pattern your indexes after your most common queries and everything should hum right along. Remember, MySQL generally uses only one index per query (see the EXPLAIN documentation for more details. I also refer you to the manual sections on optimizing). A few, well chosen, multi-column indexes will generally give you better average performance than many single-column indexes. The manual has excellent advice on this topic. Come back to the list if you have any major problems, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Limit on text field select
Is there a way I can select from a text field and limit the amount of text I get back in my query? For example, if I have an articleText field of type TEXT, and the article contains 4000 words, is there a way to select that text with a limit of 200 words, or should this kind of logic go in my php code? Best regards, Erich Beyrent Information Technology Services Plymouth State University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limit on text field select
[snip] Is there a way I can select from a text field and limit the amount of text I get back in my query? For example, if I have an articleText field of type TEXT, and the article contains 4000 words, is there a way to select that text with a limit of 200 words, or should this kind of logic go in my php code? [/snip] MySQL provides a wealth of ways to select portions of fields. Start with http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html http://dev.mysql.com/doc/mysql/en/string-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does anyone know ??
Does anyone know the GA release date for Mysql 5.0 ? Thanks! Nupur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating indexes
Ângelo M. Rigo [EMAIL PROTECTED] wrote on 01/27/2005 03:04:15 PM: Hi I have an aplication wich is opening to many connections even i am using persistent connectins and closing every connection i do open I have created indexes in all the fields i supose they are needed I would like to know if i can and how can i measure where indexes are needed or where they can open too many internal connections since my aplication does many searches through the database.. Thank´s in advance __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ MySQL typically only uses a single index per query. So if you have indexed each column individually, you are probably not as well-indexed as you think. My suggestions: 1) Turn on the slow query log (http://dev.mysql.com/doc/mysql/pt/slow-query-log.html) 2) Actually look at the queries captured in the slow query log. Look for things that frequently appear in the WHERE, GROUP BY, and ORDER BY clauses. Use the EXPLAIN command to analyze your current index usage for those queries that end up in the slow query log. (http://dev.mysql.com/doc/mysql/pt/explain.html) 3) Replace most of your single-column indexes with appropriate multi-column indexes. (http://dev.mysql.com/doc/mysql/pt/mysql-indexes.html) 4) Use EXPLAIN again to analyze your slow queries against your new indexes. 5) Repeat from 2) until things improve enough. Then turn off the slow query log. The manual is your friend. Use it often: (http://dev.mysql.com/doc/mysql/pt/index.html). I have no idea why the Portuguese version does not have a Search function on it like the English version does. (Maybe that index is still under construction.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Windows Installer
Greetings Has anyone been able to successfully upgrade from 4.0.22, for example, to 4.1.9 using the windows installer and server instance wizard? For me, every time the wizard hits the 'Apply security settings' part of the config it freezes. I am now trying a manual install but wanted to know whether there is a known bug. Thanks! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
This could be a case where your dump files are not split in such a way so that they honor the max_packet_length setting of the recieving server. Make sure you tell mysql dump what the maximum size of an INSERT statement will be for the server you want to read the dump into or it will put all of the data for the entire table into a single INSERT statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt_lists [EMAIL PROTECTED] wrote on 01/27/2005 01:13:21 PM: Having repeatable problems doing restores, 4.1.8 and 4.1.9 both do the same error Is there some setting I'm missing? mysqldump -u username-pPassword --all-databases --quote-names /intranet/backup/backup.sql E:\intranet\backupmysql -u xotech -pDaredevil22 -f backup.sql ERROR at line 2153: Unknown command '\m'. ERROR at line 2153: Unknown command '\m'. ERROR at line 2153: Unknown command '\m'. mysql: Out of memory (Needed 626767192 bytes) mysql: Out of memory (Needed 626763096 bytes) E:\intranet\backup -- 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]
MySQL Windows Bot Alert - Secure Your Servers
There is a new bot spreading on the Internet that targets insecure MySQL installations on Windows, as reported at http://isc.sans.org/diary.php?isc=a508f4a185755af19ea8bd45444a570b. I am now going to quote the advisory: -- Infection Method The bot uses the MySQL UDF Dynamic Library Exploit. In order to launch the exploit, the bot first has to authenticate to mysql as 'root' user. A long list of passwords is included with the bot, and the bot will brute force the password. Once connected, the bot will create a table called 'bla' using the database 'mysql'. The 'mysql' database is typically used to store administrative information like passwords, and is part of every mysql install. The only field in this database is a BLOB named 'line'. Once the table is created, the executable is written into the table using an insert statement. Then, the content of is written to a file called 'app_result.dll' using 'select * from bla into dumpfile app_result.dll'. The 'bla' table is dropped once the file is created. In order to execute the 'app_result.dll', the bot creates a mysql function called 'app_result' which uses the 'app_result.dll' file saved earlier. This function is executed, and as a result the bot is loaded and run. -- This bot will then attempt to infect other machines. MySQL installations are at risk if proper security practices have not been followed. You need to act now to secure your Windows MySQL installation from this bot and help prevent the spread of the worm. The steps are very simple: 1. Firewall port 3306 from outside access. No MySQL servers should ever be exposed directly to the internet. If you do not have a firewall, look at the various free software firewalls available. 2. Secure your root account. See http://dev.mysql.com/doc/mysql/en/default-privileges.html and http://dev.mysql.com/doc/mysql/en/security-against-attack.html. Specifically, ensure that the root account has a STRONG password that cannot be easily guessed, and remove the [EMAIL PROTECTED] account from the grant tables: Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql; Database changed mysql DELETE FROM user WHERE host = '%' AND user = 'root'; Query OK, 1 row affected (0.03 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) mysql This will prevent logins from external machines. 3. Disable networking. If feasible, remove network access to MySQL completely by using the skip-networking option in your configuration file and restarting the server. You can still connect via named pipes on an NT based system. If your server is behind a firewall, and you have a strong root password you are not vulnerible to this worm. If not, take the necesscary steps now to ensure that your system is not infected. -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-380-6535 Mobile: +1 403-330-0870 MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: www.mysqluc.com -- 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
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
Installing MySql 4.1
Trying to install MySql 4.1 on XP using the automated installer. Have tried to install both the windows essentials and windows X86 . Tried doing typical and custom versions. Also used the Instance Configuration both detail and standard. When I get to the dialog box with the execute button I receive the following error message: Cannot create windows service for MySql. Error:0. Also went into services and tried to start and received the error message Could not start MySql service on local computer. Error 3: The system cannot find the path specified. OBVIOUSLY I dont have something set. Any help appreciated.
non-unique key of multiple columns
Hello, MySQL gurus, I have a non-unique key of 2 columns, the 1st is a varchar(15), 2nd is a int(10) unsigned. But when I 'explain' a query that uses this key, the key_len is only 15 with the key name shown up in the 'key' column of this table correctly though. According to the manual, that means how many columns of a key is accually used in the query. So the query is only using the 1st column of my key although it could not be efficient without using the 2nd column. I am not sure if the key is only built on a single col ( I added the index as 'ALTER TABLE a ADD INDEX (f, s)') or it is the MySQL optimizer that decides it will only use the 1st column of the key. Is there any way to check and get it right? Thank you all for help -- ginger -- 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,
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]
Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No
Hello, I have a replication setup on to linux boxes (debian woody, kernel 2.4.21-xfs, mysql 4.1.7-standard official intel-compiler binary from mysql.com). master:~# mysqladmin status Uptime: 464848 Threads: 10 Questions: 296385136 Slow queries: 1752 Opens: 2629 Flush tables: 1 Open tables: 405 Queries per second avg: 637.596 slave:~# mysqladmin status Uptime: 463460 Threads: 2 Questions: 292885156 Slow queries: 6 Opens: 2510 Flush tables: 1 Open tables: 327 Queries per second avg: 631.953 both systems have identical hardware (P4 2.4ghz, 3GB RAM, SCSI-Hardware-RAID) connection is gigabit-ethernet. Everything used to work fine, but I wanted to get rid of InnoDB since I did only use that for very big table containing historical data and those tables were moved to another server. I ran out of discspace, innodb-datafiles can only grow but not shrink and i didn't need it anyway, so it had to go. I stopped the slave, changed all left over innodb-tables to myisam, added skip-innodb to my.cnf on the master and the slave, restarted the server, renewed the replication by doing it the classical way: flush tables with read log, copy the /var/lib/mysql on the slave (not much, just around 20GB), reset master, unlock tables. Then start the slave-mysqld, reset slave, slave start. Everything was fine and very fast for 4 days (from saturday till wednesday afternoon), then suddenly the slave stopped. this is where the weird stuff starts: show slave status tells me everything is fine, just Slave_IO_Running: No is wrong. After typing slave start, it says Slave_IO_Running: Yes, and Slave_SQL_Running: No. Very strange. Now i did a slave stop;slave start; and everything is fine again, the slave catches up and goes on. Today (thursday afternoon), the same thing happens again and can be solved again by slave stop;slave start;. Now it happened again around 10pm. Again, the stop-start-trick made it working again. I add the output of my mysql-shell Can anybody help me with that? This is a production system under heavy load and I can't play around with different mysql-versions and such... If I don't find a solution really quick, I'll have to do help myself with some shell-skript-daemon checking if replication is running and issuing stop slave;start slave-commands otherwise... not really the way it should be :( Thanks Jan SLAVE: slave:~# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.40GHz stepping: 7 cpu MHz : 2392.077 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips: 4771.02 slave:~# free total used free sharedbuffers cached Mem: 31051042355364 749740 04401514104 -/+ buffers/cache: 8408202264284 Swap: 779144 428072 351072 MASTER master:~# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.40GHz stepping: 7 cpu MHz : 2392.163 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips: 4771.02 master:~# free total used free sharedbuffers cached Mem: 31051043096016 9088 06482087780 -/+ buffers/cache:10075882097516 Swap: 779144 391732 387412 Slave shell: wpdb2:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23083 to server version: 4.1.7-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. wpdb2 mysql show slave status\G *** 1. row *** Slave_IO_State: Master_Host: 192.168.10.26 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000210 Read_Master_Log_Pos: 146168522 Relay_Log_File: wpdb2-relay-bin.000210 Relay_Log_Pos: 146168608 Relay_Master_Log_File: mysql-bin.000210 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:
How big is too big?
Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? Our InnoDB datafile was 116GB last I checked, and I know we've got a few 20GB+ databases on there, but my real concern is over the table sizes. I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Does anyone know the point that the MySQL Administrator can't report on table sizes? Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1006: Can't create database
Hi, I'm really stuck here. I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) I checked [EMAIL PROTECTED] in the user table and it has all priviledges. Went to linux and logged in as root, logged into mysql as root, got the same error when creating a database. I searched all over the cyberspace and forums for answers. They all asked me to check 2 things: disk usage and permissions. For disk usage this is what I've got: FilesystemSize Used Avail Use% Mounted on /dev/hda3 73G 1.7G 67G 3% / /dev/hda1 76M 6.2M 66M 9% /boot none 251M 0 251M 0% /dev/shm I don't think I'm running out of space(btw I'm very new to the LAMP thing). Also I checked permissions and this is what I got: drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql So I don't know what else I can do. I can't do anything if I can't create a database. Your help is greatly appreciated! Thanks! Jordan -- 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 (...)
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
Re: How big is too big?
Does anyone know the point that the MySQL Administrator can't report on table sizes? Misao: I do not use MySQL Adminstrator, so I do not know at what point it is unable to correctly report the size of a table. But if there exists a table that it cannot correctly report the size of, it is a bug in the MySQL Administrator, and should be reported at http://bugs.mysql.com You can use SHOW TABLE STATUS LIKE 'table_name' to get the info about any given table. Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? There is no one magic number, but I define a couple of threshholds that could be of some guidance: * when the table does not fit into RAM anymore * when the most frequently accessed part of the table does not fit into RAM anymore However, the actual cut-off values for when the trouble starts is largely application dependent. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy newbie question re: option file and passwords
Hi there, For some reason, despite entering my passwords in ~/.my.cnf, I still have to enter a password on the command line to login to mysql. I have tried this with each of the accounts I have created and all return the same error: $ mysql -u sebyte ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What am I doing wrong? Here are the accounts I have created, and below that is my ~/.my.cnf. I have 'x'ed out my actual passwords in this post but is there something wrong with the syntax I am using. I have verified that the file IS being read each time I attempt to login. Any help much appreciated. TIA Sebastian mysql select host,user,password from user; +---+--+--+ | host | user | password | +---+--+--+ | localhost | root | 4be7c493348ee750 | | localhost | guest| | | localhost | debian-sys-maint | 7985ba067899ea77 | | localhost | sebyte | 5722c7a41e81cbb3 | | localhost | ddj | 7b17b74d22ac2a88 | +---+--+--+ 5 rows in set (0.08 sec) # -*- mode: shell-script -*- # ~/.my.cnf # [mysql] host=localhost user=root password='xx' host=localhost user=sebyte password='x' host=localhost user=ddj password='xxx' [mysqladmin] host=localhost user=root password='xx' -- CC me by all means but a follow-up will usually do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1006: Can't create database
[snip] I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) ... drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql [/snip] perror 13 Error code 13: Permission denied File permissions look ok at that level, and I would assume that mysql user can get to that directory. You could login to your linux box as root then 'su - mysql' and see if you can create a directory in the mysql datadir as the mysql user. This isn't a grant table issue because I believe that will give you an access denied error. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How big is too big?
Our database server has 4Gigs of ram on it, and we have a hard time of figuring out the true InnoDB settings on how to use that ram up. I ended up just increasing the ram used until it just wouldn't run anymore, then backed up and used that. We plan on adding another 4Gigs of ram, total of 8GB. Tweaking the InnoDB stuff doesn't seem as easy as the MyISAM side. So as it stands, we have 4GB, and one table that easily exceeds that, almost 8GB in size. Even after we bump the server up to 8GB, that means this table will barely fit. Does that mean we need to start cleaning out that table, or adding new ram? -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 6:52 PM To: Misao Cc: mysql@lists.mysql.com Subject: Re: How big is too big? Does anyone know the point that the MySQL Administrator can't report on table sizes? Misao: I do not use MySQL Adminstrator, so I do not know at what point it is unable to correctly report the size of a table. But if there exists a table that it cannot correctly report the size of, it is a bug in the MySQL Administrator, and should be reported at http://bugs.mysql.com You can use SHOW TABLE STATUS LIKE 'table_name' to get the info about any given table. Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? There is no one magic number, but I define a couple of threshholds that could be of some guidance: * when the table does not fit into RAM anymore * when the most frequently accessed part of the table does not fit into RAM anymore However, the actual cut-off values for when the trouble starts is largely application dependent. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database
ERROR 1006: Can't create database 'foo'. (errno: 13) errno 13 is 'Permission denied' drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql locate the directory that contains the 'test' and 'mysql' databases. This will be the directory that you do not have permission to write in for mysql. This is based upon mysqld running as user 'mysql' Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird join needs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Je ado Januaro 27 2005 08:40, Jigal van Hemert skribis: Currently I have a query that is able to return (doing a left join): 'A', 'B', 'E' 'A', 'B', 'F' 'C', 'D', NULL But I need this: 'A', 'B', 'E,F' 'C', 'D', NULL Can SQL help me in any way here ? or do I have to do it by programming ? If you use MySQL 4.1 or later take a look at http://dev.mysql.com/doc/mysql/en/group-by-functions.html and read the part about GROUP_CONCAT()... Outch! I'm running MySQL 4.0, I somehow knew that I was going to need a feature of MySQL 4.1 (it seems mysql 4.0 and 4.1 added LOT'S of good features, very nice indeed), and worts, this is going to run in a server which has MySQL 3.x :( Thanks anyway. - -- Pupeno: [EMAIL PROTECTED] - http://pupeno.com Reading Science Fiction ? http://sfreaders.com.ar -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) iD8DBQFB+Vh6fW48a9PWGkURAmlFAJ4zf4vln6MQIlYMnYR+wSYUd12JDACfQi5f xMGr8zhD0fcEygocp/bhxIk= =xi3T -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server crached problem
Hi List, Today the hard drive from my server where MySQL was running crashed. I have a dump from yesterday night but I would like to save the work for today before the crash. I saved the full directory of the MySQL (/usr/local/mysql). Is my data inside the frm file or in another file? How can I access and retrieve my data? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install error - unable to wite to /tmp/root/
Hi everyone, I am having a problem starting up mysql. I am unable to run the mysql_install_db routine. When I run it I keep getting the error - unable to write to /tmp/root/... Originally I got the error unable to write to /tmp but that has since changed to the first error. I'm not a newbee to IT or Linux. I tried chmod to change rights but that doesn't work. I'm forgetting something. Help. Michael
list of error codes
I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? Thanks, Tripp __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server crached problem
[snip] Is my data inside the frm file or in another file? How can I access and retrieve my data? [/snip] The data is not in the frm files. If you are using myisam tables the data is in the myd files and the indexes are in the myi files. Assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on another host or the same host after you get it fixed. You will probably have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysql stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
[snip] I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? [/snip] You can use perror to find out want a mysql errno means. http://dev.mysql.com/doc/mysql/en/perror.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
http://www.be-known-online.com/mysql/ (B (BBest regards (B (BNils Valentin (B (B (B [snip] (B I looked around and didn't see documentation of MySQL error codes. I did (B find a short list of INNODB codes but nothing comprehensive. Is there such (B a (B page? (B [/snip] (B (B You can use perror to find out want a mysql errno means. (B (B http://dev.mysql.com/doc/mysql/en/perror.html (B (B --- (B Tom Crimmins (B Interface Specialist (B Pottawattamie County, Iowa (B (B (B -- (B MySQL General Mailing List (B For list archives: http://lists.mysql.com/mysql (B To unsubscribe: (B http://lists.mysql.com/[EMAIL PROTECTED] (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
Hi Emmett, (B (BPlease try http://www.be-known-online.com/mysql/ (B (BNote that this error list is for the Linux version (parts differs for (Banother OS). More error descriptions can be found in the header files. (B(forgot currently which ones). If you search the forum for error codes and (Bmy name than you will find the info (roughly a year+ old) (B (Bperror (the way I remember it) will only cover a limited scope of the (Berrors (ca.10%) (B (B (BBest regards (B (BNils Valentin (B (B (B [snip] (B I looked around and didn't see documentation of MySQL error codes. I did (B find a short list of INNODB codes but nothing comprehensive. Is there such (B a (B page? (B [/snip] (B (B You can use perror to find out want a mysql errno means. (B (B http://dev.mysql.com/doc/mysql/en/perror.html (B (B --- (B Tom Crimmins (B Interface Specialist (B Pottawattamie County, Iowa (B (B (B -- (B MySQL General Mailing List (B For list archives: http://lists.mysql.com/mysql (B To unsubscribe: (B http://lists.mysql.com/[EMAIL PROTECTED] (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
[snip] I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? [/snip] OS error codes : http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Server error messages : http://dev.mysql.com/doc/mysql/en/error-handling.html (this page also tells what files to find these in) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Security Alert 2005-01-27
There is a bot active on the internet that is infecting Windows machines running MySQL Server: http://isc.sans.org/diary.php?isc=a508f4a185755af19ea8bd45444a570b An alert with background information is already available on: http://dev.mysql.com/tech-resources/articles/security_alert.html The following message contains practical information about how to protect your system. 1. What is the UDF Worm? The UDF Worm is self-propagating code that is finding MySQL servers running on Microsoft Windows with poor firewall and password security. This worm does not exploit any bugs in MySQL. It does exploit poor security setups for firewalls and passwords. This worm is Microsoft Windows specific, however firewall and password security is important on any platform. 2. What is a User Defined Function (UDF)? A User Defined Function, often referred to as a UDF, is a part of the ANSI SQL-99 specification. This feature allows developers to create custom functions. It is a common feature among the major database products. 3. What does the UDF Worm do? The UDF Worm looks for MySQL servers running on Microsoft Windows that have been exposed to the internet and have either weak or no passwords installed on the account named root. Once it finds an account it installs a UDF, and then uses that machine to scan for other machines that can possibly be infected. 4. How do I know if my MySQL installation has been infected? Run the following SQL statement: SELECT * FROM mysql.func; If a UDF is found with a name of app_result then you have probably been infected with the worm. You should look at all UDFs and determine whether or not they are legitimate. The worm is likely to mutate over time and will take on different UDF names. You may be able to remove this UDF by running the following SQL statement: DROP FUNCTION app_result; Please note however that this does not not remove the worm itself once your system has been infected. See the next section for further details. 5. How do I disinfect my system? As the worm is of a known family called WootBot or SpyBot, most virus scanners will pick up the executable with their existing signature files and alert you. F-Secure provides additional information on this type of bot as well as tools for removing it: http://www.f-secure.com/v-descs/wootbot.shtml To prevent any unauthorized person or program from connecting to your database you should verify that all of your current accounts have passwords and that they are strong passwords (i.e. not easily guess-able). Remember to always use firewalls and strong passwords to protect your MySQL Servers. Please consult your security advisors for the best way to protect your systems. 6. How do I protect my MySQL Servers on Microsoft Windows? There are 2 basic steps to protect your MySQL Servers: 1. Always use strong passwords on all accounts. 2. Use firewalls to protect your MySQL Servers. The Windows installation procedure in MySQL 4.1 and up takes additional steps to ensure that users create a secure setup. For information about securing your root account on an existing installation, see http://dev.mysql.com/doc/mysql/en/default-privileges.html and http://dev.mysql.com/doc/mysql/en/security-against-attack.html. Specifically, ensure that the root account has a STRONG password that cannot be easily guessed. Also, remove the root@'%' account from the grant tables. Connect to the MySQL server as root, the following example uses the 'mysql' command line client. Search for mysql.exe in on your hard drive, and Run this program with the parameters -u root -p. Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql DELETE FROM mysql.user WHERE host = '%' AND user = 'root'; Query OK, 1 row affected (0.03 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) mysql quit This will prevent root logins from other machines. If you don't require any access to the database server from other machines, you can even disable networking completely by using the skip-networking option in your configuration file and restarting the server. You can also use MySQL Administrator to edit your MySQL configuration. Local connections are handled via named pipes on an NT based system (Windows NT4, 2000, XP, 2003). 7. Is this a vulnerability on Microsoft Windows, Linux, or Unix? This worm is Microsoft Windows specific, however firewall and password security is important on any platform. If your server is behind a firewall, and you have a strong root password you are not vulnerable to this worm. If not, take the necesscary steps now to protect your system. Learn more about strong passwords and firewall setups for Microsoft Windows here: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/windows_password_tips.mspx
MySQL Load Balancing w/ Alteons...Half Open Connections
Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Best Regards, Jason Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing MySql 4.1
I had similar problem after incomplete removal MySQL 4.0.x which left dead service named MySQL that points to non-existing path c:\mysql. I changed the windows service name for MySQL 4.1 to MySQL41, and it works fine. Also you can try to execute mysqld from command line to see any extra details: mysqld --console --standalone -Original Message- From: Darrell and Lynda Adams [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 4:34 PM To: mysql@lists.mysql.com Subject: Installing MySql 4.1 Trying to install MySql 4.1 on XP using the automated installer. Have tried to install both the windows essentials and windows X86 . Tried doing typical and custom versions. Also used the Instance Configuration both detail and standard. When I get to the dialog box with the execute button I receive the following error message: Cannot create windows service for MySql. Error:0. Also went into services and tried to start and received the error message Could not start MySql service on local computer. Error 3: The system cannot find the path specified. OBVIOUSLY I dont have something set. Any help appreciated. Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
