repair with keycache during ADD INDEX
Dear Sirs and Ladies,
I tried to put some keys on a large table (more than 100.000.000 in 13G)
with the Statement:
ALTER TABLE mytable
ADD INDEX i1 (COL1(4)),
ADD INDEX i2 (COL2(4)),
ADD INDEX i3 (COL3(12)),
ADD INDEX i4 (COL4(12)),
ADD INDEX i5 (COL5(5)),
ADD INDEX i5 (COL6(11));
After about 7 hours, a 'SHOW PROCESSLIST' doesn't say 'copy to temp-table'
anymore but 'repair with keycache'.
Watching the the directory for this database, it seems to happpen at the
time when all data from .MYD is copied to
the temp-table.
The 'repair with keycache' now lasts about 12 hours and I don' know when it
comes to an end.
Is this a usual thing or did I do something wrong?
I thought 'repair with keycache' just happens while recovering of a crashed
table.
How can I speed up this process ?
Is innodb on MySQL 4.x faster doing this ?
In order ro increase speed of creating an index, I increased the
key_buffer-varibale to 1024M,
which is 60% of the memory on that engine.
OS is SUSE-LINUX 8.0 (Kernel 2.4.4)
MySQL is 2.23.52.
FS is Reiser.
The Table is myisam and looks like this:
Field TypeNullKey Default Extra
COL_A char(2)
COL1varchar(4)
COL2varchar(4)
COL6varchar(11)
COL_B enum('-1','0','1','2') YES (NULL)
COL3varchar(35)
COL4varchar(55)
COL_C varchar(35)
COL_D varchar(55)
COL_E varchar(61)
COL_F varchar(30)
COL_5 varchar(5)
COL_G varchar(50)
COL_H varchar(50)
COL_I varchar(50)
COL_J varchar(14)
COL_K int(11) 0
COL_O int(11) 0
To make it a bit easier to read, i named all collumns with an index with a
number (like COL_1).
This table will be filled once a week and there will be no further updates
or inserts till one wwek later,
when it will be dropped and rebuild with new data.
It is the only table in this database and (except mysql's privilege-tables)
the only table on this engine at all.
Please Help me
Klaus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Check for data before inserting
Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. Rgds Mike -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 16 October 2003 19:29 To: Mike Tuller Cc: MySql List Subject: Re: Check for data before inserting It sound like you want to use REPLACE instead of the SELECT and INSERT/UPDATE combo. Replace will insert if the record doesn't exist and update if it does. On Thursday, October 16, 2003, at 01:27 PM, Mike Tuller wrote: I have a shell script that I have data entered into a database, and instead of just entering in the data blindly, I want it to check to see if the item it is entering exists already, and if it does, update the information rather than inserting it. So I want to run a select statement, and if results come back, have the data updated, and if not have it inserted. I know how to do this in PHP with $query_total_rows. Is there some way in SQL to do this, or do I need to figure out a way to do it in the shell script? Thanks, Mike -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help in querying
Hi All, I'm using mysql as my database. I have a table which has the following colums: idx productname value My question is how do i display and select all the data that has a value of more than 915. Hope you can help. Regards, Delz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check for data before inserting
The REPLACE statement is very dangerous since it first DELETEs the row if found. Instead you should use : INSERT . ON DUPLICATE KEY UPDATE Check the manual for clearer syntax But this one will work if the INSERT would create a Duplicate Key entry (I mean, would violate the index or primary key) Thanks Emery - Original Message - From: Mike Knox [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 09:44 Subject: RE: Check for data before inserting Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. Rgds Mike -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 16 October 2003 19:29 To: Mike Tuller Cc: MySql List Subject: Re: Check for data before inserting It sound like you want to use REPLACE instead of the SELECT and INSERT/UPDATE combo. Replace will insert if the record doesn't exist and update if it does. On Thursday, October 16, 2003, at 01:27 PM, Mike Tuller wrote: I have a shell script that I have data entered into a database, and instead of just entering in the data blindly, I want it to check to see if the item it is entering exists already, and if it does, update the information rather than inserting it. So I want to run a select statement, and if results come back, have the data updated, and if not have it inserted. I know how to do this in PHP with $query_total_rows. Is there some way in SQL to do this, or do I need to figure out a way to do it in the shell script? Thanks, Mike -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- 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: 'Selective' joins
Andreas, What you're looking for is a syntax like CASE WHEN ... JOIN I think that's not in SQL99 (just off the top of my head, such a syntax would seem likely to defeat any optimiser). You're going to have to (i) store the results of an initial query in a temp table and work from there, or (ii) use a scripting language. If the system is going to be running for a while, choosing (ii) now might save you a little time later when MySQL implements stored procs (since it will be easier to port a script to a SP). PB - Original Message - From: Andreas Ahlenstorf To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 5:49 PM Subject: 'Selective' joins Hello, I'm having a tricky problem: I've got a database table (PicklistData with three fields, which I have to use for a join to get the data, which is associated with them. They are called EAN, ISRC and LabelProductCode. Until now, there's nothing difficult. But a thing, which makes the problem insoluble for me: This three fields may be empty, only one must be filled out (and we can't do anything against that fact). CREATE TABLE EncodingData ( StationID int(20) NOT NULL default '0', ProdID int(20) NOT NULL default '0', MediaNr int(20) NOT NULL default '0', Status int(11) NOT NULL default '0', PRIMARY KEY (StationID,ProdID,MediaNr), KEY StationID (StationID), KEY ProdID (ProdID), KEY MediaNr (MediaNr) ) TYPE=MyISAM; CREATE TABLE PicklistData ( ID int(10) unsigned NOT NULL auto_increment, Picklist int(10) unsigned NOT NULL default '0', EAN varchar(13) NOT NULL default '', ISRC varchar(12) NOT NULL default '', LabelProductCode varchar(50) NOT NULL default '', PRIMARY KEY (ID), KEY Picklist (Picklist), KEY EAN (EAN) ) TYPE=MyISAM; CREATE TABLE ProdID ( StationID int(11) NOT NULL default '0', ProdID int(20) NOT NULL auto_increment, EAN varchar(13) default NULL, LabelSpezProdCode varchar(254) NOT NULL default '', PRIMARY KEY (ProdID,StationID), KEY StationID (StationID), KEY ProdID (ProdID), KEY EAN (EAN) ) TYPE=MyISAM; CREATE TABLE Trackdata ( StationID int(11) NOT NULL default '0', ProdID int(11) NOT NULL default '0', MediaNr int(11) NOT NULL default '0', ISRC varchar(12) default NULL, PRIMARY KEY (StationID,ProdID,MediaNr), KEY StationID (StationID), KEY ProdID (ProdID), KEY MediaNr (MediaNr) ) TYPE=MyISAM; If PicklistData.EAN is not empty, I use this query: SELECT d.status FROM Picklists AS a JOIN PicklistData AS b ON a.ID = b.Picklist JOIN ProdID AS c ON b.EAN = c.EAN JOIN EncodingData AS d ON c.StationID = d.StationID AND c.ProdID = d.ProdID WHERE a.ID = '1' But if PicklistData.EAN is empty, I have to replace 'JOIN ProdID AS c ON b.EAN = c.EAN' by 'JOIN Trackdata AS c ON b.ISRC = c.ISRC' or 'JOIN ProdID AS c ON b.LabelProductCode = c.LabelSpezProdCode' to get the two fields StationID and ProdID at the end. So, my big problem is: How to make that with MySQL without using any scripting language and without big performance problems (the tables have a lot of records)? If it isn't possible like that, is it possible with minor changes on the database structure? Regards, Andreas Ahlenstorf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Challenging query....
I think it is: SET @a:=0; /* SELECT would return the value. Of course it would work but */ SELECT @a:[EMAIL PROTECTED], blah, blew from table where blah 1; Thanks Emery - Original Message - From: Diana Soares [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 19:41 Subject: RE: Challenging query On Thu, 2003-10-16 at 17:42, Jeff McKeon wrote: Is there a way to generate a line number for a query return within the returned rows?? For instance, if I return 5 rows from a query, is there a command or function I can put in a query to add a column that contains the row number returned? Select some_command(),blah, blew from table where blah 1; +---+---+---+ | row | blah | blew | +---+---+---+ | 1 | blah | blew | | 2 | blah | blew | | 3 | blah | blew | | 4 | blah | blew | +---+---+---+ You can do: SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], blah, blew from table where blah 1; -- Diana Soares -- 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: Help in querying
SELECT * FROM tablename WHERE value 915 -Original Message- From: delz [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 9:46 AM To: [EMAIL PROTECTED] Subject: Help in querying Hi All, I'm using mysql as my database. I have a table which has the following colums: idx productname value My question is how do i display and select all the data that has a value of more than 915. Hope you can help. Regards, Delz -- 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]
Someone asked it some time back
Hi group, Someone asked a question about how he could change a column name and have his old application continue using the same table while he is updating the client. This means: He has a table with columns: ColA, ColB and he is forced to change the columns names into ColX, ColB and possibly the table name. So, he was asking how he could do that change and continue using his application that still uses the old table and columns. I think I have found a solution: using MERGE tables. It that user is still in need of this I will be happy to send it over. Unfortunaly, I am unable to find his post as I have a lot of mails to search in!!! Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
error message
Hello,
I am new to mysql trying to learn by following the instructions from a trainning book.
While following the instructions from the book that I am using I got the following
error message, without the quotes, Column count doesn't match value count at row 1.
I entered the following command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
According to the book that I am using this command should add the hostname/database
combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's website. If someone
would explain to me what I am doing wrong, or where I can find information that will
help me get past this point I would appreciate it. BTW I am using version 40.0.14.
Gregory Norman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from char to Date routine
Me, I will say that such a function should be added as a feature request
since it doesn't exist.
MySQL 4.1.1 introduces MAKE_DATE()
The following can be read from
http://www.mysql.com/doc/en/Date_and_time_functions.html
MAKEDATE(year,dayofyear)
Returns a date, given year and day-of-year values. dayofyear must be greater
than 0 or the result will NULL.
mysql SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
- '2001-01-31', '2001-02-01'
mysql SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
- '2001-12-31', '2004-12-30'
mysql SELECT MAKEDATE(2001,0);
- NULL
But I really don't see how useful is the above function since it is not easy
to calculate the day of year value.
Instead it should be changed into or extended to support returning a date
(in the MySQL format -mm-dd) with arguments being a date and a format
like the one Kim asked.
Thanks
Emery
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Kim G. Pedersen [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 03:06
Subject: Re: from char to Date routine
* Kim G. Pedersen
I looking for a way to convert a datestring to date value
example
UnknowFunction('23.03.68','dd.mm.yy') - unixtimestamp
In oracle we have
to_date('23.03.68','dd.mm.yy')
I have search the net for hours without luck.
Unix timestamps starts at 01/01-1970, your example will return 0.
MySQL has the UNIX_TIMESTAMP() function:
URL: http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1365
You have to format a correct mysql date string:
SELECT UNIX_TIMESTAMP('1970-03-23');
You can change the format of the date by using string manipulation
functions:
SET @a = 23.03.70;
SELECT UNIX_TIMESTAMP(
CONCAT('19',
MID(@a,7,2),'-',
MID(@a,4,2),'-',
MID(@a,1,2)));
--
Roger
--
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: network interfaces
What if you want it to listen to two or three interfaces (I mean not all interfaces but on more than one interface). Can one separate the addresses by Commas? Each on a new line???/ Thanks Emery - Original Message - From: Matt W [EMAIL PROTECTED] To: Dan Jones [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 17, 2003 03:42 Subject: Re: network interfaces Hi Dan, Instead of skip-networking, use bind-address in my.cnf: bind-address=192.168.0.1 I think you can just specify 1 IP like that. So... you either have 1) listening on no IP (skip-networking), 2) listening on 1 IP (bind-address), or 3) listening on all IPs (the default). BTW, I think even with bind-address, you can still connect locally via Unix sockets (or named-pipes on NT), as with skip-networking. Not 100% sure on that though. Hope that helps. Matt - Original Message - From: Dan Jones To: MySQL Mailing List [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 8:15 PM Subject: network interfaces I have MySQL running on a Linux box with two network interfaces - one is a routable IP that connects to my DSL bridge and the other is a non-routable IP (192.168.1.*) that connects to my internal LAN. I'd like MySQL to be available on the internal interface but not to be available on the interface that is open to the Internet. I've tried to RTFM but have found very little info on configuring the MySQL daemon. The config file (/etc/mysql/my.cnf under Debian) contains entries to turn off networking completely (skip-networking) and to configure the port, but I haven't found anything to configure which interfaces it uses. Info or pointers to info greatly appreciated. (And yes, I realize I can configure iptables to block the port but I'd like to configure MySQL not to listen on that interface as well.) -- 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: 'Selective' joins
Hello, Peter Brawley schrieb am Freitag, 17. Oktober 2003 um 10:01: What you're looking for is a syntax like CASE WHEN ... JOIN I think that's not in SQL99 (just off the top of my head, such a syntax would seem likely to defeat any optimiser). You're going to have to (i) store the Jep, that's what I tried the last days, but I wasn't able to find a solution without syntax errors. The problem are the two different tables where I have to get the StationID and ProdID from. Without the two different tables, I could use JOIN ON CASE... that should work, I saw such a solution in a usenet posting. results of an initial query in a temp table and work from there, or (ii) use a scripting language. If the system is going to be running for a while, choosing (ii) now might save you a little time later when MySQL implements stored procs (since it will be easier to port a script to a SP). Hum... I'll ask my people, if it's possible to have one table where I can find the EAN, ISRC and LabelProductCode. I won't have temporary tables because they don't solve our problem with a lot of chaos and mess in our tables. But if that is not possible, I'll try that with the temporary table. Andreas -- Eine Ferengi-Erwerbsregel besagt: Pass auf, was du verkaufst; es könnte genau das tun, was der Kunde erwartet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Selective' joins
Hello, Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 01:56: I think you need the LEFT JOIN: URL: http://www.mysql.com/doc/en/JOIN.html I thought about that before, but how to catch up the problem, that I have different tables where I have to get the ProdID and the StationID? Andreas -- Eine Ferengi-Erwerbsregel besagt: Pass auf, was du verkaufst; es könnte genau das tun, was der Kunde erwartet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error message
Try naming the columns in the insert explicitly, like so:
insert into `mysql`.`host`
(Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv,
Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv
)
values
('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
-Original Message-
From: Gregory Norman [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:32 AM
To: [EMAIL PROTECTED]
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column
count doesn't match value count at row 1. I entered the following
command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y');
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's
website. If someone would explain to me what I am doing wrong, or where
I can find information that will help me get past this point I would
appreciate it. BTW I am using version 40.0.14.
Gregory Norman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error message
Starting from MySQL 4.0.., you have two new privileges named
Create_tmp_table_priv AND Lock_tables_priv, that's the reason, why you
aren't able to enter the values without specifying column name.
You can either specify column names like Rob told you or give two more
values in your syntax like:
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y','Y');
Enjoy
Nitin
- Original Message -
From: Rob [EMAIL PROTECTED]
To: 'Gregory Norman' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 2:26 PM
Subject: RE: error message
Try naming the columns in the insert explicitly, like so:
insert into `mysql`.`host`
(Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv,
Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv
)
values
('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
-Original Message-
From: Gregory Norman [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:32 AM
To: [EMAIL PROTECTED]
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column
count doesn't match value count at row 1. I entered the following
command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y');
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's
website. If someone would explain to me what I am doing wrong, or where
I can find information that will help me get past this point I would
appreciate it. BTW I am using version 40.0.14.
Gregory Norman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error message
That error means that the number of columns in the table is differnt from
the number of columns you are sending for insert.
For my system (4.1.0) I have got 14 fields.
For your query, you are trying to insert 12 fields.
Normally this should work even if the the number of fields is different and
the rest should be changed into their defaults.
Just try to check what are the indexes,
Thanks
Emery
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:31
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column count
doesn't match value count at row 1. I entered the following command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's website.
If someone would explain to me what I am doing wrong, or where I can find
information that will help me get past this point I would appreciate it. BTW
I am using version 40.0.14.
Gregory Norman
--
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 speed up query?
Ganbold [EMAIL PROTECTED] wrote:
I'm having some trouble running one query. I'm using FreeBSD 4.8 with
linuxthread enabled mysql-4.0.14.
Server has 1GB ram and SCSI hard disk.
I need to get size of email message which is stored in MyISAM table.
The problematic query is:
select sum(size) from message where uid='2945';
-
mysql select sum(size) from message where uid='2945';
+---+
| sum(size) |
+---+
| 249722888 |
+---+
1 row in set (35.04 sec)
-
This query took me 35 seconds. Message table has 286867 rows and 4.7GB of size.
uid field is indexed as well as some others.
uid is a part of index. Is uid the first part of compound index?
Use EXPLAIN to see if MySQL uses index.
Table structure:
-
mysql describe message;
+---+---+-+--+---++
| Field | Type| Null| Key | Default | Extra|
+---+---+-+--+---++
| sjt| tinytext | YES || NULL ||
| uid | int(1) unsigned | | MUL | 0 ||
| mto | varchar(255) | YES | MUL | NULL ||
| mfr | varchar(255) | YES | MUL | NULL ||
| msg | longtext| YES | | NULL ||
| date | int(1)| YES | MUL | NULL ||
| dir| char(1) | YES | MUL | NULL ||
| stat | enum('N','O','S','D') | YES | MUL | NULL ||
| id | int(1) unsigned | | PRI | NULL | auto_increment |
| size | int(1) | YES | MUL | NULL | |
+---+--+-+---+---++
10 rows in set (0.00 sec)
-
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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: How to speed up query?
Egor,
Result of explain:
mysql explain select sum(size) from message where uid='2945';
+-+--+---+--+-+---+--+-+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-+--+---+--+-+---+--+-+
| message | ref | uid | uid | 4 | const | 2491 | Using
where |
+-+--+---+--+-+---+--+-+
Ganbold
At 12:54 PM 17.10.2003 +0300, you wrote:
Ganbold [EMAIL PROTECTED] wrote:
I'm having some trouble running one query. I'm using FreeBSD 4.8 with
linuxthread enabled mysql-4.0.14.
Server has 1GB ram and SCSI hard disk.
I need to get size of email message which is stored in MyISAM table.
The problematic query is:
select sum(size) from message where uid='2945';
-
mysql select sum(size) from message where uid='2945';
+---+
| sum(size) |
+---+
| 249722888 |
+---+
1 row in set (35.04 sec)
-
This query took me 35 seconds. Message table has 286867 rows and 4.7GB
of size.
uid field is indexed as well as some others.
uid is a part of index. Is uid the first part of compound index?
Use EXPLAIN to see if MySQL uses index.
Table structure:
-
mysql describe message;
+---+---+-+--+---++
| Field | Type| Null| Key | Default |
Extra|
+---+---+-+--+---++
| sjt| tinytext | YES ||
NULL ||
| uid | int(1) unsigned | | MUL |
0 ||
| mto | varchar(255) | YES | MUL | NULL ||
| mfr | varchar(255) | YES | MUL | NULL ||
| msg | longtext| YES | |
NULL ||
| date | int(1)| YES | MUL | NULL ||
| dir| char(1) | YES | MUL | NULL ||
| stat | enum('N','O','S','D') | YES | MUL | NULL ||
| id | int(1) unsigned | | PRI | NULL |
auto_increment |
| size | int(1) | YES | MUL |
NULL | |
+---+--+-+---+---++
10 rows in set (0.00 sec)
-
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error message
Hi,
You can see the table structure with the command:
desc host;
after logging in to mysql using the table mysql
Greetings,
Richard
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:31 AM
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column count
doesn't match value count at row 1. I entered the following command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's website.
If someone would explain to me what I am doing wrong, or where I can find
information that will help me get past this point I would appreciate it. BTW
I am using version 40.0.14.
Gregory Norman
--
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: 'Selective' joins
* Andreas Ahlenstorf Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 01:56: I think you need the LEFT JOIN: URL: http://www.mysql.com/doc/en/JOIN.html I thought about that before, but how to catch up the problem, that I have different tables where I have to get the ProdID and the StationID? You can LEFT JOIN both join paths, one of them will be NULL, the other contain data. You can use IF() to select the right one, something like this: SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status FROM Picklists AS a LEFT JOIN PicklistData AS b ON a.ID = b.Picklist LEFT JOIN ProdID AS c ON b.EAN = c.EAN LEFT JOIN EncodingData AS d ON c.StationID = d.StationID AND c.ProdID = d.ProdID LEFT JOIN Trackdata AS c2 ON b.ISRC = c2.ISRC LEFT JOIN EncodingData AS d2 ON c2.StationID = d2.StationID AND c2.ProdID = d2.ProdID WHERE a.ID = '1' -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error message
Normally it's a good habit to explicitly name the columns.
But it also works whithout naming them in the INSERT instruction when you
are inserting exactly in all the columns.
Thanks
Emery
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: Director General: NEFACOMP [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 12:44
Subject: Re: error message
Thanks for your reply. I used the example from Rob and it worked. Does
this mean I will have to name all of the columns in a table with each
command?
Gregory
On Friday, October 17, 2003, at 05:13AM, Director General: NEFACOMP
[EMAIL PROTECTED] wrote:
That error means that the number of columns in the table is differnt from
the number of columns you are sending for insert.
For my system (4.1.0) I have got 14 fields.
For your query, you are trying to insert 12 fields.
Normally this should work even if the the number of fields is different
and
the rest should be changed into their defaults.
Just try to check what are the indexes,
Thanks
Emery
- Original Message -
From: Gregory Norman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:31
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column
count
doesn't match value count at row 1. I entered the following command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
;
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's
website.
If someone would explain to me what I am doing wrong, or where I can find
information that will help me get past this point I would appreciate it.
BTW
I am using version 40.0.14.
Gregory Norman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No longer able to log into database
[EMAIL PROTECTED] wrote: Do you talk about MySQL users or about users of the typo3? Typo3 users. I setup typo to send error messages, and I get this: There has been numerous attempts (4) to login at the Typo3 site typo3 server (www.wals.lib.wi.us). But this problem is not related to the MySQL. This is a dump of the failures: 16-10-03 08:55: Login-attempt from 172.30.2.136 (), username 'admin', password not accepted! 16-10-03 09:05: Login-attempt from 172.30.2.136 (), username 'admin', password not accepted! 16-10-03 09:10: Login-attempt from 172.30.2.136 (), username 'cleveland', password not accepted! 16-10-03 09:10: Login-attempt from 172.30.2.136 (), username 'cleveland', password not accepted! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Check for data before inserting
Mike Knox wrote: Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. You have to lock the table first, or another user might insert the record between 1 and 2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hotmail
IMPOSSIBLE DE RECEVOIR MON COURRIER SUR HOTMAIL DEPUIS AU MOINS UNE SEMAINE...?? [EMAIL PROTECTED] MERCI DE VOTRE COMPRÉHENSIONANDRÉ THIBAULT...QUÉBEC.
MySQL NPTL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ive been searching mysql.com documentation and the web for the subject but not any usefull info. I want to setup a server running a NPTL capable kernel (RedHat9 kernel + XFS patches) and a statically compiled mysqld binary (my ideea is to not have to reinstall the production machine glibc with NPTL support just for mysqld). For this I have setup a chroot enviroment on another machine (using gentoo's stage3 tarball), recompiled glibc 2.3.2 with NPTL support. Some tests with multi-threaded applications shows everything fine. First problem is with mysql configure script which checks for Linuxthreads support in a relative strange way (greps for Linuxthreads string to be present in /usr/include/pthread.h). Because glibc 2.3.2 + nptl doesnt have Linuxthreads in pthread.h I had to add that string in a comment there. Then I succeded in compiling mysql (statically) with the configure options that are used for compiling MySQL AB binariers too (at least thats what they say in the mysql docs :)). The server starts up fine and I succeed into doing simple stuff on it like: - - connecting with mysql CLI tool - - changing dbs - - creating tables - - dropping tables But when I exit the mysql CLI tool , mysqld crashes :-/ mysql show databases; +--+ | Database | +--+ | mysql| | test | +--+ 2 rows in set (0.00 sec) mysql use test Database changed mysql show tables; Empty set (0.00 sec) mysql Bye # Number of processes running now: 0 031017 13:23:20 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.15a' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x88c9178 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x44f2db7c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80953f1 0xe420 (nil) 0x82854ce 0x82859b6 0x8274ce2 0x8272e34 0x80950af 0x80a0a1b 0x8272b32 0x82ace6a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instru ctions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. PS: my first ideea was to look if/how is mysql patched into RedHat9 but unfortunetly they ship with mysql 3.23.x version :-/ - Mihai RUSUEmail: [EMAIL PROTECTED] GPG : http://dizzy.roedu.net/dizzy-gpg.txtWWW: http://dizzy.roedu.net -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE/j+7JPZzOzrZY/1QRAvTHAKDlpuH0NA66E4VrgCRDBskyXG2Z9gCg47bR mqbrh2/V8cQ3zXKBqiQZzRQ= =zbtb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Check for data before inserting
Why would the REPLACE statement not work? Simon (ps mite have missed some thing if so sorry) -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 17 October 2003 14:08 To: Mike Knox Cc: '[EMAIL PROTECTED]' Subject: Re: Check for data before inserting Mike Knox wrote: Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. You have to lock the table first, or another user might insert the record between 1 and 2. -- 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: Check for data before inserting
I didn't say it wouldn't. I was pointing out the danger of the method listed. Also, a danger with replace is that multiple records might be deleted for one insert if there are multiple unique keys in the table. Insert and replace statements will error out with the appropriate status. Simon Green wrote: Why would the REPLACE statement not work? Simon (ps mite have missed some thing if so sorry) -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 17 October 2003 14:08 To: Mike Knox Cc: '[EMAIL PROTECTED]' Subject: Re: Check for data before inserting Mike Knox wrote: Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. You have to lock the table first, or another user might insert the record between 1 and 2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hotmail
Je ne vous comprends pas - Original Message - From: André Thibault [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 15:01 Subject: Hotmail IMPOSSIBLE DE RECEVOIR MON COURRIER SUR HOTMAIL DEPUIS AU MOINS UNE SEMAINE...?? [EMAIL PROTECTED] MERCI DE VOTRE COMPRÉHENSIONANDRÉ THIBAULT...QUÉBEC. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check for data before inserting
REPLACE replaces the row (as in English). That means it first DELETEs the row before inserting!!! Very dangerous. Use INSERT ... ON DUPLICATE KEY UPDATE ... Thanks Emery - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 15:44 Subject: Re: Check for data before inserting I didn't say it wouldn't. I was pointing out the danger of the method listed. Also, a danger with replace is that multiple records might be deleted for one insert if there are multiple unique keys in the table. Insert and replace statements will error out with the appropriate status. Simon Green wrote: Why would the REPLACE statement not work? Simon (ps mite have missed some thing if so sorry) -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 17 October 2003 14:08 To: Mike Knox Cc: '[EMAIL PROTECTED]' Subject: Re: Check for data before inserting Mike Knox wrote: Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. You have to lock the table first, or another user might insert the record between 1 and 2. -- 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: Check for data before inserting
FYI, as I stated in a previous email, INSERT . ON DUPLICATE KEY UPDATE is only available in MySQL 4.1+, which is still in development and not recommended for production use. - Gabriel On Friday, October 17, 2003, at 04:04 AM, Director General: NEFACOMP wrote: The REPLACE statement is very dangerous since it first DELETEs the row if found. Instead you should use : INSERT . ON DUPLICATE KEY UPDATE Check the manual for clearer syntax But this one will work if the INSERT would create a Duplicate Key entry (I mean, would violate the index or primary key) Thanks Emery - Original Message - From: Mike Knox [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 09:44 Subject: RE: Check for data before inserting Traditionally, I'd take the following approach 1) Update - assume it's there 2) If update fails (0 rows) do an insert Thats assuming that the update case is more prevalent. If the row is unlikely to be there - insert and do the update if you get a duplicate key. Rgds Mike -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 16 October 2003 19:29 To: Mike Tuller Cc: MySql List Subject: Re: Check for data before inserting It sound like you want to use REPLACE instead of the SELECT and INSERT/UPDATE combo. Replace will insert if the record doesn't exist and update if it does. On Thursday, October 16, 2003, at 01:27 PM, Mike Tuller wrote: I have a shell script that I have data entered into a database, and instead of just entering in the data blindly, I want it to check to see if the item it is entering exists already, and if it does, update the information rather than inserting it. So I want to run a select statement, and if results come back, have the data updated, and if not have it inserted. I know how to do this in PHP with $query_total_rows. Is there some way in SQL to do this, or do I need to figure out a way to do it in the shell script? Thanks, Mike -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. === = == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solaris Replication and IP Addresses - Known Issue?
Hi All, I have been trying to assist in a replication problem with Solaris. Seems the problem came down to using an IP address in the CHANGE MASTER command. The Slave would not connect properly to a master defined by IP address, but when the IP was out in a hosts file and the hostname specified everything was fine. See: http://www.experts-exchange.com/Databases/Mysql/Q_20767370.html In addition, this issue is mentioned in a comment at http://www.mysql.com/doc/en/Replication_HOWTO.html Posted by Juha Palomaki on Thursday September 18 2003, @11:18am I couldn't get the replication working on Solaris (SunOS 5.8) with MySQL MAX 4.0.15 precompiled binary with IP addresses. When I tried to start the replication it was always complaining about not being able to login as [EMAIL PROTECTED] Everything started working after I put the IP address to /etc/hosts and put the name of the machine instead of IP address to /etc/my.cnf. I do not use Solaris and cannot replicate this myself, but is this a known issue or should it go to bugs.mysql.com? Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Java related
Hi group, I wanted to start learning programming in JAVA. What do you think is the right discussion group or mailing list for starters? I have never touched JAVA. Can someone advise on what software I should install? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
mysql max
Hi what's the max table size we can use with mysql-max version? Suggestions? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql max
It depends on your filesystem's and OS's max-size of a file in its filesystem. HOWEVER, there are a few ways you can get around this. You can use InnoDB tables, or read about RAID types here: http://www.mysql.com/doc/en/CREATE_TABLE.html (almost at the bottom of the page). You can also use MERGE tables: http://www.mysql.com/doc/en/MERGE.html On Fri, 17 Oct 2003, nm wrote: Hi what's the max table size we can use with mysql-max version? Suggestions? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixed Font
Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Than this ? mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Just a thought. Jerry
Re: Fixed Font
I dunno, the font seems fairly fixed when I view it in pine --ja On Fri, 17 Oct 2003, Jerry Rocteur wrote: Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Than this ? mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Just a thought. Jerry -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed Font
Set your mail reader to use a fixed font. That is where the problem lies. Jerry Rocteur wrote: Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from char to Date routine
At 1:55 +0200 10/17/03, Kim G. Pedersen wrote:
Hello
I looking for a way to convert a datestring to date value
example
UnknowFunction('23.03.68','dd.mm.yy') - unixtimestamp
In oracle we have
to_date('23.03.68','dd.mm.yy')
I have search the net for hours without luck.
There will be a function for this in 4.1.1, called ... uh, STR_TO_DATE(),
I think.
It'll return a DATETIME, not a TIMESTAMP, though. As has already been
pointed out, your date cannot be a TIMESTAMP, which has a range beginning
in 1970.
Regards
Kim G. Pedersen
macaos/elprint Development
+45 35373808
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed Font
Agreed. Most emails are sent to the list in plain text format, which does not include and font or style information. You could do that with HTML formatted emails, but I imagine a large number of people would dislike reading HTML email if their mail client cannot render it. - Gabriel On Friday, October 17, 2003, at 12:29 PM, gerald_clark wrote: Set your mail reader to use a fixed font. That is where the problem lies. Jerry Rocteur wrote: Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': -- 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: Java related
Here's a good-free book, considered to be one of the best for learning Java: http://mindview.net/Books/TIJ/DownloadSites - Thinking In Java, by Bruce Eckels. The key component is the Java Developers Kit - it has the Java Compiler, plus the runtime engine that all Java code runs under (JVM, or Java Virtual Machine). You will need one specific to your Operating System. For Windows, http://java.sun.com is probably the best place. Finally, you need a code editor. VI or Notepad will work fine, but I'd recommend Eclipse (http://www.eclipse.org) - free open source IDE. David. - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 8:56 AM Subject: Java related Hi group, I wanted to start learning programming in JAVA. What do you think is the right discussion group or mailing list for starters? I have never touched JAVA. Can someone advise on what software I should install? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement in MYISAM vs INNODB
It is my understanding that at least through 4.0.14, INNODB does not support using autoincrement on the last field in a multi field primary key. i.e. if a table has a primary key of three fields like cpny_ID, acct_ID, list_ID in MYISAM you can add the autoincrement attribute to list_ID and it will sequence within the cpny_ID, acct_ID group. Are there any plans to support this in INNODB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed Font
On Friday, October 17, 2003 6:38 PM CEST, Gabriel Ricard wrote: Agreed. Most emails are sent to the list in plain text format, which does not include and font or style information. You could do that with HTML formatted emails, but I imagine a large number of people would dislike reading HTML email if their mail client cannot render it. Why not set your mailer to display text-only mails with a fixed font? I did that with my Outlook Express long time ago (using 9pt Andale Mono) and never had any problems with it. Since I prefer text-only mails unless really necessary or HTML makes some sense for that, I got used to see e-mails with a fixed font, by the time. -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GEMINI
Can anyone tell me what happened to GEMINI? We've been customers of MySQL for a while and about two years ago started using Nusphere's version to take advantage of their row level locking/ACID transaction safe table type GEMINI. Things went well for a while but one day I came back for help and poof, Nusphere has moved all support to Russia, then a couple weeks later no more support exists. I understand this was due to a violation on their part of the GPL. So here I am stuck with 80GB's of GEMINI data and an aging version of MySQL prone to crash every couple of months. Does anyone know, are the GEMINI developers from Nusphere somewhere else legally continuing this development? If not can someone give me some migration suggestions? I've looked at InnoDB some, but am worried, will it provide equal performance to GEMINI? Also to migrate this I have found one copy of MySQL, version 4.00beta, that has support for both GEMINI and INNODB, will using this version cause problems for me? Thanks, Nihal
newbie Q: How to display Search Results in a secure way?
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed Font
My mistake, it was a setting in my mailer... Thanks to Dave Christensen for putting me on the right track.. Sorry for the bandwidth! Jerry On Friday, Oct 17, 2003, at 18:26 Europe/Brussels, [EMAIL PROTECTED] wrote: I dunno, the font seems fairly fixed when I view it in pine --ja On Fri, 17 Oct 2003, Jerry Rocteur wrote: Hi, I'm new to the list and I am just wondering.. Why don't you guys use a fixed font when pasting mysql queries.. Wouldn't it better to see something like this 'Monaco font on Mac OS X': mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Than this ? mysql explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+-- +-+ | message | ref | uid | uid | 4 | const | 2491 | Using where | +-+--+---+--+-+---+-- +-+ Just a thought. Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax
Hello, my name's Marlon. I have a question about sql and I need some help! How can I do something like it using mysql? update registre set (name='NewName' where lastname='OldLastName'), (name='OldName' where lastname='NewLastName'); Tank you Marlon _ Voce quer um iGMail protegido contra vírus e spams? Clique aqui: http://www.igmailseguro.ig.com.br Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql syntax
Hello, my name's Marlon. I have a question about sql and I need some help! How can I do something like it using mysql? update registre set (name='NewName' where lastname='OldLastName'), (name='OldName' where lastname='NewLastName'); I _believe_ you can do it this way. I'm sure someone will correct me if I am mistaken: UPDATE registare SET CASE lastname WHEN 'OldLastName' THEN name = 'NewName' WHEN 'NewLastName' THEN name = 'OldName' END; Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
I am not very familiar with php, but why are you using an echo within an
echo statement?
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
why not try this
echo td width=200$fnamenbsp;$lname/td;
echo td width=200$title/td;
echo tdinput type=\hidden\ name=\fid\ value=\$fid\;
echo input type=\submit\ name=\submit\ value=\Choose this
faculty\/td;
Also, most languages require you to escape when used within quotes.
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer
mailto:[EMAIL PROTECTED][EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
or888-638-6963
Fax: 724-489-4386
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
count max days
I have a database where I want to count the number of days where a field gets to a certain value. Right now I am using: SELECT count(DISTINCT aqiValues.readingDate) as greenCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = 'Twin Cities' AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 0 and 51 Then I am also using: SELECT count(DISTINCT aqiValues.readingDate) as yellowCount FROMaqiRegions, aqiSites, aqiValues WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND aqiValues.site_name = aqiSites.siteNumber AND aqiRegions.showOnSite = 1 AND aqiSites.online = 1 AND aqiRegions.aqiRegion = ? AND aqiValues.readingDate = '2003/01/01' AND aqiValues.aqiNumber between 51 and 100 My problem is I only want to count the day high value. So if it is 25 in the morning and 52 in the afternoon I only want to the yellowcount query to return results. I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP error. I must be putting it in the wrong place. Maybe I need to somehow make this only one query? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
Thanks!
That helped me get pass that error. However, maybe my logic is wrong, when I
clicked the Submit button, it took me to the detail page of the last search
result. I figured I'd need to move that input tag for the hidden value
somewhere instead. Tried a few places but still didn't work.
any comments?
thanks!
Jordan
Patrick Shoaf wrote:
I am not very familiar with php, but why are you using an echo within an
echo statement?
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
why not try this
echo td width=200$fnamenbsp;$lname/td;
echo td width=200$title/td;
echo tdinput type=\hidden\ name=\fid\ value=\$fid\;
echo input type=\submit\ name=\submit\ value=\Choose this
faculty\/td;
Also, most languages require you to escape when used within quotes.
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer
mailto:[EMAIL PROTECTED][EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
or888-638-6963
Fax: 724-489-4386
--
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: newbie Q: How to display Search Results in a secure way?
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
where whatever your criteria is
Which when you make url will look like:
echo a href=\FacDetails.php?fid=$crypt_id\$fname/a
which will generate something like:
a href=FacDetails.php?fid=23rh23kjne2323j4k23n234lJordan/a
and on your lookup, do
select col1, col2, col3
from FacDetails
where MD5(fid) = $fid;
My $0.02 cents...
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) =
mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2
cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link
which will take
the user to a detailed page. However, the fid has to show
up as part of
the URL. I want to hide the fid as I don't want people to
view the other
detailed records just by randomly entering the number for
the fid. So I
tried to modify it and came up with the following:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Problems
Hello! I am new to the world of Linux and MySQL, I have been working with it for about a month now. We decided to upgrade the 4.1Alpha instead of the current release because we wanted to test sub Select statements. After having some problems and hosing up the Red Hat machine, I re-installed RedHat (version 9) and am now installing MySQL. However, 3 of the RPM packages are giving similar dependency errors. All of the rpm packages produce the signature warnings which I'm assuming exist because this is Alpha, but these three produce dependcies errors. Is there a page with information on what to do in these cases that someone can point me to, I will appreciate any and all help. Thanks -- John (command line and errors follow) # rpm -i MySQL-Max-4.1.0-0.i386.rpm warning: MySQL-Max-4.1.0-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-Max-4.1.0-0 libssl.so.0.9.6 is needed by MySQL-Max-4.1.0-0 # rpm -i MySQL-shared-4.1.0-0.i386.rpm warning: MySQL-shared-4.1.0-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-4.1.0-0 libssl.so.0.9.6 is needed by MySQL-shared-4.1.0-0 # rpm -i MySQL-bench-4.1.0-0.i386.rpm warning: MySQL-bench-4.1.0-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: perl-DBI is needed by MySQL-bench-4.1.0-0 John Stacy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
Thanks millions Dan! That's exactly what I need. And I used your method and it worked
beautifully!
I'm so excited! Thanks again!
Jordan
Dan Greene wrote:
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
where whatever your criteria is
Which when you make url will look like:
echo a href=\FacDetails.php?fid=$crypt_id\$fname/a
which will generate something like:
a href=FacDetails.php?fid=23rh23kjne2323j4k23n234lJordan/a
and on your lookup, do
select col1, col2, col3
from FacDetails
where MD5(fid) = $fid;
My $0.02 cents...
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) =
mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2
cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link
which will take
the user to a detailed page. However, the fid has to show
up as part of
the URL. I want to hide the fid as I don't want people to
view the other
detailed records just by randomly entering the number for
the fid. So I
tried to modify it and came up with the following:
--
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 speed up query?
Hi Ganbold,
It's taking 35 seconds because it has to look at 2000+ [large] rows in
the data file to get the value of size. If the index is just on uid,
remove it and make a composite index on (uid, size):
ALTER TABLE message DROP INDEX uid,
ADD INDEX uid_size (uid, size);
It should then be very fast because it doesn't need to go to the data
file -- as EXPLAIN will show with Using index.
Hope that helps.
Matt
- Original Message -
From: Ganbold
Sent: Friday, October 17, 2003 4:14 AM
Subject: Re: How to speed up query?
Egor,
Result of explain:
mysql explain select sum(size) from message where uid='2945';
+-+--+---+--+-+---+--+--
---+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-+--+---+--+-+---+--+--
---+
| message | ref | uid | uid | 4 | const | 2491 |
Using
where |
+-+--+---+--+-+---+--+--
---+
Ganbold
At 12:54 PM 17.10.2003 +0300, you wrote:
Ganbold [EMAIL PROTECTED] wrote:
I'm having some trouble running one query. I'm using FreeBSD 4.8
with
linuxthread enabled mysql-4.0.14.
Server has 1GB ram and SCSI hard disk.
I need to get size of email message which is stored in MyISAM
table.
The problematic query is:
select sum(size) from message where uid='2945';
--
---
mysql select sum(size) from message where uid='2945';
+---+
| sum(size) |
+---+
| 249722888 |
+---+
1 row in set (35.04 sec)
--
---
This query took me 35 seconds. Message table has 286867 rows and
4.7GB
of size.
uid field is indexed as well as some others.
uid is a part of index. Is uid the first part of compound index?
Use EXPLAIN to see if MySQL uses index.
Table structure:
--
---
mysql describe message;
+---+---+-+--+---+--
--+
| Field | Type| Null| Key | Default |
Extra|
+---+---+-+--+---+--
--+
| sjt| tinytext | YES ||
NULL ||
| uid | int(1) unsigned | | MUL |
0 ||
| mto | varchar(255) | YES | MUL | NULL |
|
| mfr | varchar(255) | YES | MUL | NULL |
|
| msg | longtext| YES | |
NULL ||
| date | int(1)| YES | MUL | NULL |
|
| dir| char(1) | YES | MUL | NULL |
|
| stat | enum('N','O','S','D') | YES | MUL | NULL |
|
| id | int(1) unsigned | | PRI | NULL |
auto_increment |
| size | int(1) | YES | MUL |
NULL | |
+---+--+-+---+---+--
--+
10 rows in set (0.00 sec)
--
---
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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]
(ANNOUNCE) New PHP MySQL Web Log Site
Hello Just wanted to let anyone interested that after almost 6 years, http://www.weberdev.com now has a new brother (sister?) site called http://www.weberblog.com. This new site is a community site, where matters related to PHP MySQL are discussed. There are already a few interesting blogs and more will begin in the next few days. I Hope you find the site helpful and would love to see you there adding your thoughts. I would appreciate any comments, suggestions you may have. It's a new site so be easy on me :) Sincerely berber Visit http://www.weberdev.com/ http://www.weberblog.com/ Today!!! To see where PHP might take you tomorrow. Share your code : http://addexample.weberdev.com Search for PHP Code from your browser http://toolbar.weberdev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Selective' joins
Hello, Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 14:24: SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status FROM Picklists AS a LEFT JOIN PicklistData AS b ON a.ID = b.Picklist LEFT JOIN ProdID AS c ON b.EAN = c.EAN LEFT JOIN EncodingData AS d ON c.StationID = d.StationID AND c.ProdID = d.ProdID LEFT JOIN Trackdata AS c2 ON b.ISRC = c2.ISRC LEFT JOIN EncodingData AS d2 ON c2.StationID = d2.StationID AND c2.ProdID = d2.ProdID WHERE a.ID = '1' Hum... The query takes something around 30 seconds and returns more or less 3 mio rows with the same result. It should return only one row. :( - Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Selective' joins
* Andreas Ahlenstorf Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 14:24: SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status FROM Picklists AS a LEFT JOIN PicklistData AS b ON a.ID = b.Picklist LEFT JOIN ProdID AS c ON b.EAN = c.EAN LEFT JOIN EncodingData AS d ON c.StationID = d.StationID AND c.ProdID = d.ProdID LEFT JOIN Trackdata AS c2 ON b.ISRC = c2.ISRC LEFT JOIN EncodingData AS d2 ON c2.StationID = d2.StationID AND c2.ProdID = d2.ProdID WHERE a.ID = '1' Hum... The query takes something around 30 seconds and returns more or less 3 mio rows with the same result. It should return only one row. :( ok, then something is obviously wrong. :) The ISRC column of the Tracdata table is not indexed, it probably should be, if you want to join on it. I see the primary keys for EncodingData and Trackdata includes MediaNr. That could be a problem, try adding AND c2.MediaNr = d2.MediaNr for the last join. Try selecting some columns from each table, to see what is going on: SELECT a.ID,b.EAN,b.ISRC,c.StationID,d.status,c2.StationID,d2.status ... Also, try using EXPLAIN on your query to see if/what indexes are used. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on SELECT support
Hi, I am trying to select the first 10 rows of a table by the date the record was created. I have a date field in the table and I tried using the TOP syntax of SELECT, but I keep getting syntax error. SELECT TOP 10 DateCreatedField FROM my_table ORDER BY DateCreatedField DESCENDING Does MySQL not support TOP? If it does, can somebody tell me what is wrong with that syntax? If it doesn't, can someone please tell how I can do it without getting ALL records and extracting the first 10 programmatically? Thanks, Dale Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on SELECT support
MySQL doesn't support TOP, however, there's a LIMIT syntax for MySQL that roughly does the same thing: http://www.mysql.com/doc/en/SELECT.html for a brief explanation. In your example what you are looking for is: SELECT DateCreatedField FROM my_table ORDER BY DateCreatedField DESC LIMIT 10; On Fri, 17 Oct 2003, Dale Hans wrote: Hi, I am trying to select the first 10 rows of a table by the date the record was created. I have a date field in the table and I tried using the TOP syntax of SELECT, but I keep getting syntax error. SELECT TOP 10 DateCreatedField FROM my_table ORDER BY DateCreatedField DESCENDING Does MySQL not support TOP? If it does, can somebody tell me what is wrong with that syntax? If it doesn't, can someone please tell how I can do it without getting ALL records and extracting the first 10 programmatically? Thanks, Dale Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is SQL Standard: ISNUL()L, IFNULL() ?
Hi Group, Does anyone have a good site that contains the current SQL standard? Or does anyone know is ISNULL() and IFNULL(), and similar null testing functions, standard SQL or extensions to SQL? Thanks! I'm porting from Sybase SQLAnywhere to MySQL and just found what worked in Sybase (ISNULL()) now appears to have to be IFNULL() --- this is a real bummer because I didn't want to have to change ANY of my application code.. Thanks! Holly
Backup database with foreign keys
Hi, I want to backup a MySQL 3.23 database to upgrade it to MySQL 4.0. My tables are InnoDB and they have foreign keys. I would like to know how to backup it, because it seems that neither mysqldump nor mysqlhotcopy can be easily used. The restore script created by mysqldump contains the 'create table' (and data insert) instructions unsorted. So, when I run the script it fails because it many times tries to create tables referencing other tables that have not already been created. On the other hand, the MySQL manual says (4.8.7 mysqlhotcopy) that mysqlhotcopy can only be used with MyISAM and ISAM tables. Thanks in advance Martín _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Someone PLEASE Help Me!!! This should be really easy for you folks!
I desperately need everyone's help. I normally build database driven websites on NT Servers with an MS Access database, using perl 5 to read/write the database. Now I need to build a website that can handle a database with almost a gigabyte worth of images to catalog (no it's not porn). Ideally I would like to use the same method BUT the only hosts i can find that will offer that much disk space only use Unix and MySQL. I, However, have never used MySQL. I have used sql statements in the perl script to read/write the MS Access database, but i have never used MySQL. I need someone to tell me would I have to buy some software to build a MySQL database? Is there a free download that i can use to build the database? Where would I find it to download/purchase it? Is there an easy example of what statements I could use from a perl script to add, delete and read a single entry or a whole range of entries from the database? Can someone please help me? I'm desperate! Alternatively, if someone knows of a host that offers that much space and MS Access capatibility for around $10 a month that would certainly work too. Thank you everyone for your help and time in this. Sincerely, Mike Tiny Bailey Author of What Have We Done? The Dragon Chronicles
Re: Backup database with foreign keys
At 0:15 + 10/18/03, MartÌn Lahittette wrote: Hi, I want to backup a MySQL 3.23 database to upgrade it to MySQL 4.0. My tables are InnoDB and they have foreign keys. I would like to know how to backup it, because it seems that neither mysqldump nor mysqlhotcopy can be easily used. The restore script created by mysqldump contains the 'create table' (and data insert) instructions unsorted. So, when I run the script it fails because it many times tries to create tables referencing other tables that have not already been created. Before loading the dump file, turn off foreign key checking. Invoke mysql, then issue these statements: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE your_dump_file; On the other hand, the MySQL manual says (4.8.7 mysqlhotcopy) that mysqlhotcopy can only be used with MyISAM and ISAM tables. Thanks in advance Martín -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reducing used space by tables
Hello, How can I run garbage collection (optimize sql command, I think) on Innob tables? Thanks, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Someone PLEASE Help Me!!! This should be really easy for you folks!
On Fri, 2003-10-17 at 20:16, Mike Bailey wrote: I desperately need everyone's help. I normally build database driven websites on NT Servers with an MS Access database, using perl 5 to read/write the database. Now I need to build a website that can handle a database with almost a gigabyte worth of images to catalog (no it's not porn). Ideally I would like to use the same method BUT the only hosts i can find that will offer that much disk space only use Unix and MySQL. I, However, have never used MySQL. I have used sql statements in the perl script to read/write the MS Access database, but i have never used MySQL. I need someone to tell me would I have to buy some software to build a MySQL database? Is there a free download that i can use to build the database? Where would I find it to download/purchase it? MySQL is GPL'd software. It's free. You can purchase a license if you need a warranty, support or if you want to distribute the software as part of a non-GPL'd package. You can download either a Linux or Windows version from www.mysql.com. Is there an easy example of what statements I could use from a perl script to add, delete and read a single entry or a whole range of entries from the database? I assume you're using DBI in your Perl scripts. (I wasn't aware that Access had an SQL server engine. I thought you had to use SQLServer for that.) MySQL and Perl should work essentially the same as Access and Perl. Just use a MySQL driver instead of an Access driver. There will likely be slight variations in the dialects of SQL spoken by the two databases, of course, but it shouldn't be anything too difficult to sort out. There's also a mailing list for Perl and MySQL if you run into problems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Failed updates
Hey guys - I'm running a piece of software that is making database queries but failing on some of them and I'm trying to figure out why so I can fix it. The table structure for the table where the query fails looks like: --- create table dspam_token_data ( uid smallint unsigned, token bigint unsigned, spam_hits int, innocent_hits int, last_hit date ) type=MyISAM; create unique index id_token_data_01 on dspam_token_data(uid,token); --- Then a typical query that fails looks like this: Oct 17 15:22:29 fozzie dspam[1060]: query error: Duplicate entry '504-9261611235441802528' for key 1: update dspam_token_data set last_hit = current_date(), spam_hits = spam_hits + 1, innocent_hits = innocent_hits - 0 where uid = 504 and token in(4969289191363969024,5157322189832328960,6543336333207863017,11859958119427079737,11593811100697187954,183694448946905616,6792121364254852560,14691708723743604479,15592576291946632902,11395421438537018704,10268001371065726376,720842269804708066,9738852911221666131,12131027321663899802,7302142392335234946,14242238172346424282,5157322200926387920,13719443997178333207,4870160322091024384,12799248752630108105,4751188209648361746,4541344963299485594,14603129171059807301,4971015824631398400,7971518036279903186,12747031424870854835,9487319413386095040,13913447836044115646,17451437979588156723,9927623417453173202,13662488450238762265,15997550804531139111,505632852072304,4773032218118574080,1648593925062866834,3709266648058441096,9872040046217224573,15078128421449940735) (Note that the in() is truncated here by me because it's very long...) Can anyone tell me what is happening?? Why would the update fail? The table already has the unique constraint so does this mean that the above query would modify rows in such a way that two rows or more would become identical? Many thanks! --Jo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock wait timeout exceeded problem
I am doing a lot of inserts, updates and reads with big among of data into longtext and longblob fields using JDBC driver and mysql 4.0.15 for Windows with innodb tables. After 1 hours of intensive working the database sent me a message Lock wait timeout exceeded; Try restarting transaction SQL code: 1205. I doing know how I can solve this problem ? How I have do configure mysql or is it a bug ? Environment: 6 Java Virtual machine using each their own connections pool. O/R mapping is done with Hibernate version 2.0. I have to solve this problem today and your help will be appreciate... Thanks. Yvan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
