Re: querry problem ( datetime = Monday - Sunday )
I think that your solution is also a generic one.
But i thought that since saturday and sunday are not business days, the query
will not be played.
On monday 08h00, we know that there is no new records in the table.
I encourage Crisiti to study this solution also.
Mathias
Selon Michael Stassen [EMAIL PROTECTED]:
inferno wrote:
Hi,
I have a problem: I need to make a select for data that was entered
more than 24 hours ago, but in that 24 hours I have to count only Monday
- Friday since that is the working program, and does not have the status
= '2' ( Solved ) and the problem is that I sincerly do not know how.
Any help/suggestions are apreciated since I am just a beginner.
Best regards,
Cristi Stoica
P.S.: to give you a little idea on what I am using:
( the interface is coded in PHP for the users )
MySQL 4.0.24 and the the data looks like this:
+-+--+--+-+-++
| Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+-++
| id | int(25) unsigned | | PRI | NULL|
auto_increment |
| client_name | varchar(100) | | | |
|
| code| bigint(13) | | | 0 |
|
| status | varchar(13) | | | 0 |
|
| date| datetime | | | -00-00 00:00:00 |
|
+-+--+--+-+-++
[EMAIL PROTECTED] wrote:
Hi Cristi,
Look at this :
mysql select now();
+-+
| now() |
+-+
| 2005-09-02 23:15:21 |
+-+
1 row in set (0.00 sec)
mysql select DATE_ADD(now(), INTERVAL -1 DAY);
+--+
| DATE_ADD(now(), INTERVAL -1 DAY) |
+--+
| 2005-09-01 23:15:27 |
+--+
1 row in set (0.00 sec)
mysql select date_format(now(),'%a');
+-+
| date_format(now(),'%a') |
+-+
| Fri |
+-+
1 row in set (0.01 sec)
So your query should be similar to :
Select * from tbl where status='2' and date = DATE_ADD(now(),
INTERVAL -1 DAY)
and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');
Hope that helps
Mathias
inferno wrote:
Hi,
It is perfect, I was thinking of doing it in php but the solution
that I've had was no way optimal.
Thank you very much for the help.
Best regards and have a nice week-end,
Cristi Stoica
Are you sure? I don't think that query does what you describe. Mathias'
query shows rows entered over 24 clock hours ago, but leaves out weekend
rows. I thought you wanted rows over 24 business hours old. That is, if
you run this query at 09:30 on a Monday, 24 hours ago means 09:30 last
Friday. Is that correct? In other words, a row entered at 16:30 on Friday
is not yet 24 business hours old at 09:30 on Monday, because weekends don't
count. Such an entry would be returned by Mathias' query.
If I'm right, you need a different query. The key is that yesterday is 1
day ago if today is Tuesday through Friday, but it is 3 days ago if today is
Monday. Hence, you need something like
SET @daysago = IF(DAYNAME(CURDATE()) = 'Monday', 3, 1);
SET @yesterday = NOW() - INTERVAL @daysago DAY;
SELECT * FROM yourtable
WHERE date = @yesterday
AND status = '2';
You can do it in one query without user variables, if you like, but it's a
little uglier:
SELECT * FROM yourtable
WHERE date = NOW() - INTERVAL IF(DAYNAME(CURDATE()) = 'Monday', 3, 1) DAY
AND status = '2';
I've assumed the query will only be run on a business day. If you need to
be able to run this on the weekend and get correct results, it becomes a bit
more complicated. Something like:
SET @yesterday = CASE DAYNAME(CURDATE())
WHEN 'Saturday' THEN CURDATE() - INTERVAL 1 DAY
WHEN 'Sunday' THEN CURDATE() - INTERVAL 2 DAY
WHEN 'Monday' THEN NOW() - INTERVAL 3 DAY
ELSE NOW() - INTERVAL 1 DAY
END;
SELECT * FROM yourtable
WHERE date = @yesterday
AND status = '2';
Again, you can do it in one query by replacing @yesterday in the SELECT with
the CASE statement on the right side of the SET statement, but it's ugly.
For more information, see the manual:
Date and time functions
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
User variables
http://dev.mysql.com/doc/mysql/en/variables.html
IF and CASE functions
Re: question on how to create a subset table
Hi, This is a design question. the selection of data types must be intergrated to your application. If you have a table with : name type apple fruit redcolor a simple query like select * from tbl where type ='fruit' will give only fruits to the users. You can if you have mysql 5.x create a view on this query and even select * from theview will give only fruits. Other rdbms implemente what is called snapshots or materialized views wich are real time (or near it) refreshed. This is not done till now in mysql. Mathias Selon Raymond Owens [EMAIL PROTECTED]: I am new to Mysql and am working a project where I want a user to be able to see some records but not all records in a particular table. I am assuming that I would need to create a subset table based on the original table and give the user access to only the subset table. Is it possible to do this so that the subset table is updated real time as the main table is updated? In other words as records are put in the original table they are also put in the subset table if they meet the filter conditions. The filter conditions would be based on the values in certain fields. What is the broad overview on how this could be done, I can research out the details. . example say original table looks like this: applesround redcrunchy pears bellshapedgreentart fordfocussubcompactfast orangesroundorangejuicy hondacivicsubcompactclassy but you don't want a particular user to see any information where the first field in the row is a car company. You only want them to see the information if it is a fruit. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete, where, and subselects...
Hi, You can't delete selected rows from the same table in mysql. Just create a temp table containing the select result. And delete from table A where existe select ... from temp_table; Mathias -Original Message- From: Jason Pyeron [mailto:[EMAIL PROTECTED] Sent: vendredi 2 septembre 2005 19:53 To: mysql@lists.mysql.com Subject: delete, where, and subselects... to quote the manual: Currently, you cannot delete from a table and select from the same table in a subquery. mysql select * from paths where id=(select max(ppathref) from paths); ++-++--+ | id | typeref | name | ppathref | ++-++--+ | 216883 | 1 | shared | 216882 | | 216884 | 1 | shared.bs | 216883 | | 216885 | 1 | shared.so | 216883 | | 216886 | 1 | threads.bs | 216882 | | 216887 | 1 | threads.so | 216882 | ++-++--+ 5 rows in set (0.00 sec) mysql delete from paths where id=(select max(ppathref) from paths); ERROR 1093 (HY000): You can't specify target table 'paths' for update in FROM clause Server version: 4.1.14-standard-log Are there any workarounds? Any ideas? TIA -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- 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: querry problem ( datetime = Monday - Sunday )
Hi Cristi,
Look at this :
mysql select now();
+-+
| now() |
+-+
| 2005-09-02 23:15:21 |
+-+
1 row in set (0.00 sec)
mysql select DATE_ADD(now(), INTERVAL -1 DAY);
+--+
| DATE_ADD(now(), INTERVAL -1 DAY) |
+--+
| 2005-09-01 23:15:27 |
+--+
1 row in set (0.00 sec)
mysql select date_format(now(),'%a');
+-+
| date_format(now(),'%a') |
+-+
| Fri |
+-+
1 row in set (0.01 sec)
So your query should be similar to :
Select * from tbl where status='2' and date = DATE_ADD(now(), INTERVAL -1 DAY)
and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');
Hope that helps
Mathias
-Original Message-
From: inferno [mailto:[EMAIL PROTECTED]
Sent: vendredi 2 septembre 2005 22:58
To: mysql@lists.mysql.com
Subject: querry problem ( datetime = Monday - Sunday )
Hi,
I have a problem: I need to make a select for data that was entered
more than 24 hours ago, but in that 24 hours I have to count only Monday
- Friday since that is the working program, and does not have the status
= '2' ( Solved ) and the problem is that I sincerly do not know how.
Any help/suggestions are apreciated since I am just a beginner.
Best regards,
Cristi Stoica
P.S.: to give you a little idea on what I am using:
( the interface is coded in PHP for the users )
MySQL 4.0.24 and the the data looks like this:
++-+--+-+-++
| Field | Type| Null | Key |
Default | Extra |
++-+--+-+-++
| id | int(25) unsigned| | PRI |
NULL| auto_increment |
| client_name| varchar(100)| |
| ||
| code| bigint(13) | | |
0 ||
| status | varchar(13) | | |
0 ||
| date | datetime| | | -00-00 00:00:00
||
++-+--+-+-++
--
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: Hour counts
Hi,
You can use Timediff :
mysql SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)
mysql
mysql
mysql
mysql
mysql SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)
more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Mathias
Selon Gyurasits Zoltán [EMAIL PROTECTED]:
Hello All!
I would like to calculate the hour counts from 2 'datetime'.
Example: 2005-07-27 18:00 and 2005-07-27 19:30 = 1,5 hour
I try this but not good!
R1 : munkaido_end-munkaido_start /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 R3
07-14 15:00 07-14 17:30 23000 2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
Hi,
You can use Timediff :
mysql SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)
mysql
mysql
mysql
mysql
mysql SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)
more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Mathias
Selon Gyurasits Zoltán [EMAIL PROTECTED]:
Hello All!
I would like to calculate the hour counts from 2 'datetime'.
Example: 2005-07-27 18:00 and 2005-07-27 19:30 = 1,5 hour
I try this but not good!
R1 : munkaido_end-munkaido_start /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 R3
07-14 15:00 07-14 17:30 23000 2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where on count(*)
Hi, Look at having : having count(*) 3 for example Mathias Selon Pupeno [EMAIL PROTECTED]: I have esentially this query (the list of integers may differ): SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) GROUP BY `Plans`.`id` Of that result I want those with count bigger than N (being N a number, like 3), I tried this: SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) AND count = 3 GROUP BY `Plans`.`id` but it selected only those with less that 3, what I am doing wrong ? Thanks -- Pupeno [EMAIL PROTECTED] (http://pupeno.com) Reading ? Science Fiction ? http://sfreaders.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: null data in table question
Hi, don't forget to apply the correct changes to your queries. Having NULLs or not, let you write for example : select ... from ... where midinials is NULL; And be aware about NULL indexation in some storages. Those values are not indexed for example in oracle. I'm not sure about innodb, but this sould be. In all the cases, you can't have a unique index on such columns. Mathias Selon Martijn Tonies [EMAIL PROTECTED]: Hi Scott, I have created a web-based simple application, and used mysql for data storage. All has worked well. But I do have a simple question I would like to ask the group. I have some web-based forms that match table structure. Most of the important fields have validation, and I ensure good data into the table. But I have a few columns in the table such as middleInitial where I do not validate the data. And in the database is shows a null when I do a select * from. Is a null acceptable in the database, or is there something I should do on columns that the user may not put in data? Given that NULL means unknown and there's no value/state for non applicable, NULLs don't belong in places where you actually want to fill in nothing or empty. An empty string is an empty string. Why not insert that instead? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.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: create unique index
From: Scottnbsp;PurcellDate: June 28 2005 3:36pm
Subject: create unique index
Hello,
I am reading the docs, but I am slightly confused.
I have a table with a varchar(50) column (not a primary column) where I =
do not want duplicates. It is a properties column, and I am getting =
duplicates inserted, which is causing problems in my display.
An Oracle DBA that works with me suggested creating a unique index on =
the column. I am reading the docs here:
http://dev.mysql.com/doc/mysql/en/create-index.html
but I am not have a clear understanding of an index, so I am having =
trouble visualizing what I need to do. The column already exists.=20
I am running 4.0.15 on a PC. The current column type is: MyISAM. I am =
not sure if that is proper or not. Its usage is for a web-site.
Here is what I created a while back:
CREATE TABLE PROPERTIES (
property varchar(50),
value varchar(200),
description varchar(200)
) TYPE=3DMyISAM;
Also, if this is doable, can I also create an index across two columns? =
I have another situation where I need a combination of two columns to be =
unique.
Thanks,
Scott
***
Hi scott,
I think that what you want to do is this :
mysql CREATE TABLE PROPERTIES (
-property varchar(50),
-value varchar(200),
-description varchar(200)
- ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql create unique index UNQ on PROPERTIES(property,value);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql insert into properties values('test1','Val of test1','test');
Query OK, 1 row affected (0.02 sec)
mysql insert into properties values('test1','Val of test1','test');
ERROR 1062 (23000): Duplicate entry 'test1-Val of test1' for key 1
mysql
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
Selon Kapoor, Nishikant [EMAIL PROTECTED]:
[Sorry for cross-posting.]
This is in continuation with the above mentioned subject - I am trying to
find the 'display data order' for the returned resultset. The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of from command line,
and I am not sure how exactly to do it. I need to execute both statements a
b together or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
How can I execute both (a) and (b) in my perl script?
Thanks for any help.
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordinal number within a table
Selon Kapoor, Nishikant [EMAIL PROTECTED]:
-Original Message-
This is in continuation with the above mentioned subject -
I am trying to
find the 'display data order' for the returned resultset.
The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp
ORDER BY empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of
from command line,
and I am not sure how exactly to do it. I need to execute
both statements a
b together or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
How can I execute both (a) and (b) in my perl script?
Thanks for any help.
Nishi
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
Mathias
I could, but I am assigning the entire resultset in one shot to another
construct as follows:
my $str = SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields FROM
tables WHERE ...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks,
-Nishi
Then alter your table to add an auto_increment column, update it and play your
query without @row.
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set on 4.1 and ujis support
Hi, you may use somethinh lik ethis : $dbh-do(SET character_set_results=ujis'); look at http://dev.mysql.com/doc/mysql/en/charset-general.html Hope that helps Mathias Selon Gleb Paharenko [EMAIL PROTECTED]: Hello. What do your 'show' statements return when you execute them from the perl script? Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting more sum()
Hi, just Start here http://dev.mysql.com/doc/mysql/en/select.html mathias Selon Octavian Rasnita [EMAIL PROTECTED]: Hi, I have the following tables: create table articles( id int unsigned not null primary key, title varchar(255) not null, body text not null ); create table newspapers( id int unsigned not null primary key, name varchar(255) not null ); create table visitors( id int unsigned not null primary key, id_articles int unsigned not null ); create table comments( id int unsigned not null primary key, id_articles int unsigned not null ); I would like to select: - the title from `articles` - the length of the body from `articles` - the name of the newspaper which correspond to the title of the article - the number of visitors each articles have (count(*) from visitors where articles.id=visitors.id_articles) - the number of comments each articles have (count(*) from comments where articles.id=comments.id_articles) I don't know how to select the last 2 elements (the number of visitors and the number of comments). I want to select all the articles from `articles` even if there are no visitors or no comments in the `visitors` and `comments` tables, so I might need using left join. I have tried a few ways of doing this, but without any result. Please help me if you can. (I have written the table definitions above right in the email client, so they are not tested, but I think they are correct) Thank you. Teddy -- 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: Indexing not working
Hi,
have you created an index on (id, testId) ?
i can't see the other indexes ? The only ones are the PK and testStudent, so the
PK is used.
Mathias
Selon Sajith A [EMAIL PROTECTED]:
I was trying to analyze a query that was taking almost 4 seconds to
execute. While trying to create additional indexes - found that the
query is not using any index from table qb_test_result . The type
returned is ALL for qb_test_result .
I have given the tables and query below. It would have been a help if
some one could throw some light on why this is behaving so.. I tried
to read the mysql manual and follow the
steps given there.
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testStudent` (`testId`)
) TYPE=MyISAM ;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM ;
EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS
testId, qb_test.title testName, qb_question.marks, qb_test.passrate,
qb_test_result.marks testMark, qb_test_result.percentage
testPercentage, qb_test_result.startTime, qb_test_result.endTime,
qb_test_result.status
FROM qb_test_result, qb_test_result_details, qb_test, qb_question
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test.companyId =1
AND qb_test.author = '2'
AND qb_test_result_details.questionId = qb_question.id
+++---+--+-+---+--+-+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+++---+--+-+---+--+-+
| qb_test_result | ALL| PRIMARY,testStudent | NULL |
NULL | NULL | 2494 | |
| qb_test_result_details | ref| resultId | resultId |
4 | qb_test_result.id | 45 | |
| qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY |
2 | qb_test_result.testId |1 | Using where |
| qb_question| eq_ref | PRIMARY | PRIMARY |
4 | qb_test_result_details.questionId |1 | |
+++---+--+-+---+--+-+
Thank you
Sajith A
--
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:
Re: Indexing not working
Hi Sajith, I tried to recreate you tables and do some tests, but i don't have significant data. So i don't take your explain plan. I suggest you to test after optimize table ... and analyze table ... if you stay with the problem and you can give me a set of data, i'll try to help more. Mathias Selon Sajith A [EMAIL PROTECTED]: Thank you Mathias for your time... have you created an index on (id, testId) ? i can't see the other indexes ? The only ones are the PK and testStudent, so the PK is used. Yes i also tried that .. but it was not getting used.. it listed as the possible_keys PRIMARY and testStudent but the key value was NULL.. I tried to index all combination of fields in qb_test_result.. but nothing was getting use.. Am i doing something wrong in the query? Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Selon Michael Stassen [EMAIL PROTECTED]: The PK is not used, nor should it be. Look at the EXPLAIN output. Hi, i hate the confusion people do between the primary key and the automatic index on the PK. Primary key is a generic concept for all databases. It assumes unicity and managed data insertion. It's a physical notion. When i say using PK, i mean using PK, not the PK index. This is a sequential reading of all data pages, one by one. I always here saying, it's a full table scan ? What does this mean ? this means that the rdbms doesn't use a rowid found in the index leaf pages to access randomly (hash) to data pages. mysql create table ordered (a int auto_increment primary key,b varchar(10)); mysql insert into ordered(b) values(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); mysql select * from ordered; ++--+ | a | b| ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | ++--+ 10 rows in set (0.02 sec) This is an FTS (using the primary key, not the PK Index). Or if you want, there no index fast scan (or full scan) before data reading. if i'm wrong, execuse my ugnorance. That can be. Mathias Let me rewrite the query with explicit joins, and reformat it for readability: SELECT long list of columns FROM qb_test_result JOIN qb_test_result_details ON qb_test_result.id = qb_test_result_details.resultId JOIN qb_test ON qb_test_result.testId = qb_test.id JOIN qb_question ON qb_test_result_details.questionId = qb_question.id WHERE qb_test.companyId =1 AND qb_test.author = '2'; There are no restrictions on rows from qb_test_result in the WHERE clause, so every row matches. Hence, a full table scan of qb_test_result is required, and no index will be used. Adding indexes to qb_test_result won't help. There is an index on (companyId,author) in qb_test which might have helped, but apparently the optimizer decided that there would be more matching rows in qb_test than the total number of rows in qb_test_result. I do notice that author is an INT, but the query compares it to the string '2'. I doubt that confused the optimizer, but it should be fixed anyway. Change that comparison to AND qb_test.author = 2; Michael Selon Sajith A [EMAIL PROTECTED]: I was trying to analyze a query that was taking almost 4 seconds to execute. While trying to create additional indexes - found that the query is not using any index from table qb_test_result . The type returned is ALL for qb_test_result . I have given the tables and query below. It would have been a help if some one could throw some light on why this is behaving so.. I tried to read the mysql manual and follow the steps given there. snip EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS testId, qb_test.title testName, qb_question.marks, qb_test.passrate, qb_test_result.marks testMark, qb_test_result.percentage testPercentage, qb_test_result.startTime, qb_test_result.endTime, qb_test_result.status FROM qb_test_result, qb_test_result_details, qb_test, qb_question WHERE qb_test_result.id = qb_test_result_details.resultId AND qb_test_result.testId = qb_test.id AND qb_test.companyId =1 AND qb_test.author = '2' AND qb_test_result_details.questionId = qb_question.id +++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+---+--+-+ | qb_test_result | ALL| PRIMARY,testStudent | NULL | NULL | NULL | 2494 | | | qb_test_result_details | ref| resultId | resultId | 4 | qb_test_result.id | 45 | | | qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY | 2 | qb_test_result.testId |1 | Using where | | qb_question| eq_ref | PRIMARY | PRIMARY | 4 | qb_test_result_details.questionId |1 | | +++---+--+-+---+--+-+ Thank you Sajith A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,' ',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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: How to SELECT something (CONCAT) and search the field
sorry for the first select (bad copy of a string 'lastname'):
mysql select concat(firstname,' ',lastname) from names where concat(firstname,'
',lastname) like 'Jean Dupond%';
++
| concat(firstname,' ',lastname) |
++
| Jean Dupond|
++
1 row in set (0.00 sec)
mathias
Selon [EMAIL PROTECTED]:
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
yes in therory. But practicaly, you always have business rules and data
knowledge without what you can do nothing.
so the substring must be constructed according to data.
Mathias
Selon Ben Kutsch [EMAIL PROTECTED]:
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Extended insert syntax and replication
Hi,
this is a perl script converter for inserts to simple form. if you work from a
mysqldump, you can try it to see if the converted inserts do not genrate errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details (SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming call')
Here are the tables that cause this behavior. Replication only breaks when
I use extended insert syntax. What I notice in the slave is that it seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default 'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
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: Extended insert syntax and replication
see this for troubleshootings :
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Replication_Problems.html
hope that helps
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
The inserts succeed on the master, so the problem isn't my SQL syntax. They
are legal extended inserts. It's just that the replication slave seems to
insert them out of order and screw up the auto-increment primary key.
- Original Message -
From: [EMAIL PROTECTED]
To: Jeremiah Gowdy [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, June 23, 2005 2:51 PM
Subject: Re: Extended insert syntax and replication
Hi,
this is a perl script converter for inserts to simple form. if you work
from a
mysqldump, you can try it to see if the converted inserts do not genrate
errors
:
http://platon.sk/cvs/cvs.php/scripts/perl/mysql/mysqldump-convert.pl
Mathias
Selon Jeremiah Gowdy [EMAIL PROTECTED]:
I have two servers doing replication for logs. When I do extended insert
syntax on the master to combine multiple log entries, the slave complains
about duplicate primary key numbers, even though my inserts don't set the
primary key and the primary key is auto_increment.
So any time I use extended insert syntax, my replication breaks with:
Duplicate entry '2835610' for key 1 on query. Default database:
'AppServerLog'. Query: 'INSERT DELAYED INTO AppServerLog.Details
(SessionID,
FunctionCallID, DetailLevel, Tag, DateTime, SourceFileName,
SourceLineNumber, Data) VALUES
(361019539513084542,0,'Low',23,20050623142238,'',0,'Detected incoming
call')
Here are the tables that cause this behavior. Replication only breaks
when
I use extended insert syntax. What I notice in the slave is that it
seems
that it is doing the inserts out of order.
CREATE TABLE `Details` (
`ID` bigint(20) NOT NULL auto_increment,
`SessionID` bigint(20) NOT NULL default '0',
`FunctionCallID` bigint(20) NOT NULL default '0',
`DetailLevel` enum('Error','Warn','Low','High') NOT NULL default
'Error',
`Tag` int(11) NOT NULL default '0',
`DateTime` datetime NOT NULL default '-00-00 00:00:00',
`SourceFileName` varchar(100) NOT NULL default '',
`SourceLineNumber` int(11) NOT NULL default '0',
`Data` varchar(200) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `FunctionCalls` (
`Sequence` int(11) NOT NULL auto_increment,
`ServerName` varchar(32) NOT NULL default '',
`SessionID` bigint(20) NOT NULL default '0',
`ProcessID` int(11) NOT NULL default '0',
`ThreadID` int(11) NOT NULL default '0',
`FunctionName` varchar(64) NOT NULL default '',
`FunctionVersion` int(11) NOT NULL default '0',
`CalledDateTime` datetime NOT NULL default '-00-00 00:00:00',
`FinishedDateTime` datetime NOT NULL default '-00-00 00:00:00',
`Exception` tinyint(4) NOT NULL default '0',
`ID` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `Session` (
`Sequence` int(11) NOT NULL auto_increment,
`IP` varchar(24) NOT NULL default '',
`Identity` varchar(64) NOT NULL default '',
`ProgramName` varchar(32) NOT NULL default '',
`ProgramSessionID` bigint(20) NOT NULL default '0',
`Established` datetime NOT NULL default '-00-00 00:00:00',
`ID` bigint(20) NOT NULL default '0',
`AppServerNumber` int(11) NOT NULL default '0',
PRIMARY KEY (`Sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select performance
hi, you didn't speakabout your tuning work on the databases with only one machine. Have you done such work with the indexation part for best performance ? I can tell you that several databases with about 40 Go for all is not huge. but if you want look at some linux clustering solutions like openmosix. It's not a mysql load-balancing solutionn but a linux software one. personnaly, i advice you to begin by indexation and maybe denormalization, sumarry tables, ... tunig :o) Mathias Selon El Bunzo [EMAIL PROTECTED]: Hi, I use MySQL for years with very much respect of it's stability and performance. But in these years one of my servers has grown to several hundreds of databases with approximately 50 tables in each of the databases. Some of these database have tables containing more than a million records, which will still grow. Actually I expect them to grow even to more than 10 million records. Storing these records is no problem, but querying them becomes more and more difficult within a reasonable time. The total size of all databases is about 40 GB and will grow constantly. So, the MySQL-cluster seems no solutions since all data must be kept into memory. I am looking for a scalable solution where I can put more hardware, if necessary, for more performance. Is there some way to loadbalance select-queries over multiple servers? When I look at the google-technology, they have a lot of machines, each of them storing some chunks of data. So each server handles just a little piece of the request. It would be great if there is such technique for MySQL, splitting up the databases in chunks over several machines. Firing a query should result in a query to all machines, which return their results. The master-process collects all chunks of data from the machines and returns the total results to my application. If I run into performance trouble in the future, it should be a matter of placing some more hardware to solve the problem. Any ideas, suggestions or solutions? Thanx. El. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
Hi,
just see the client connection timezone here :
http://dev.mysql.com/tech-resources/articles/4.1/time.html
you should certainly use --default-time-zone='-3:00'
Mathias
Selon Simon Garner [EMAIL PROTECTED]:
On 21/06/2005 2:45 p.m., Scott Haneda wrote:
I need to run a BETWEEN select where I put in a date rate, the time was at
one point irrelevant, but now the client is in a new time zone +3 hours
ahead, so
BETWEEN 2005010100 AND 20051201235959 is what I pass in now, which is
wrong, how can I add three hours to it and get the days and months to wrap
as needed.
I would love to do this in SQL, not in application.
Try something like:
SELECT
*
FROM
table
WHERE
datefield '2005-01-01 00:00:00'
AND datefield DATE_ADD('2005-01-01 00:00:00', INTERVAL 3 HOUR)
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
-Simon
--
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: FLUSH TABLES /w READ LOCK vs. GLOBAL READ_ONLY in making backups
1) Reading TFM (http://dev.mysql.com/doc/mysql/en/flush.html) it appears that I do not have to 'FLUSH TABLES WITH READ LOCK' for each individual database. This statement flushes and locks all simultaneously. Am I correct? HI, to flush tables, you're right : flush table TOTO = flushes only toto flush table TOTO,TITI= flushes only toto and titi flush tables = flushes ALL Have'nt you thought to a master-slave replication and cold backup from the slave ? Mathias Selon Martijn van den Burg [EMAIL PROTECTED]: Hi, Our MySQL 4.1.10 environment runs on Solaris 8 and the data is stored on a NetApp filer. The schemas contain a mix of MyISAM and InnoDB tables. To make a backup we lock all tables in all databases (USE database; FLUSH TABLES WITH READ LOCK), and then tell NetApp to make a snapshot. We have approximately 45 databases, and depending on the amount of work that is taking place in them, setting the READ LOCK on all of them separately can take a long time (as in 15 minutes). This is unacceptable in a production environment. Now I have two questions: 1) Reading TFM (http://dev.mysql.com/doc/mysql/en/flush.html) it appears that I do not have to 'FLUSH TABLES WITH READ LOCK' for each individual database. This statement flushes and locks all simultaneously. Am I correct? 2) If not, then I wonder whether it might be a Good Idea to do a 'SET GLOBAL READ_ONLY=1' in stead of locking individual tables. What would be the impact on queries that are being executed at the moment I set the lock? Kind regards, -- Martijn van den Burg ASML ITMS Application Support / Webcenter -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- 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 when sending message
Hi, Is dswu17.btconnect.com subscribed to the list with an activated login ? Mathias Selon Pooly [EMAIL PROTECTED]: I try to send a message to this list with another account, but the message came back with an error : Your message was not delivered to: mysql@lists.mysql.com for the following reason: Diagnostic was Unable to transfer, -1 Information MTA 'lists.mysql.com' gives error message Mail from HELO dswu17.btconnect.com rejected because it does not accept bounces. This violates RFC 821/2505/2821 http://www.rfc-ignorant.org/ That's not clear for me, should I warn BT ? What should I told them ? (or any link or more appropriate list ?) thanks, -- 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: E103
Salut, Quel OS, quelle version ? Quel type d'install : compile or binary Mathias Selon [EMAIL PROTECTED]: Hello, I'm trying to install MySQL and I have an error file named E103; it has 0 Ko. What do I have to do in this case? Denisa Eustasius PS: it's the first time I install MySQL and I'm not informatician! -- 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: E103
as Rhino said, that's what i suggested. mathias Selon [EMAIL PROTECTED]: Salut Denisa, peux-tu essayer d'installer la 4.1.12 depuis le msi (après suppression de ce qui a été installé, même le répertoire) à cette url : http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.12a-win32.zip/from/pick#mirrors ? Mathias Selon [EMAIL PROTECTED]: J'utilise Windows XP. La version me MySQL est 4.1.10a. J'ai essayé installer em mode binary. Le fichier ERR est dans C:\mysql\data. En plus, en utilisant la console, j'ai toujours un message comme un échec s'est produit lors de la connexion. Merçi pour votre réponse, Denisa Salut, Quel OS, quelle version ? Quel type d'install : compile or binary Mathias Selon [EMAIL PROTECTED]: Hello, I'm trying to install MySQL and I have an error file named E103; it has 0 Ko. What do I have to do in this case? Denisa Eustasius PS: it's the first time I install MySQL and I'm not informatician! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cross-reference of field names in query
Hi, SELECT @RES=data*2 AS intermediate_result, @RES + 1 AS final_result FROM table; can do the trick. Why not your method ? because it's v4.1 Mathias Selon Tom Cunningham [EMAIL PROTECTED]: Here's something I've been curious about: Referring to one field from another field in a 'select' query: SELECT data*2 AS intermediate_result, intermediate_result + 1 AS final_result FROM table; I know that MySql 4.1 can't handle it, but: (a) Is it part of standard SQL? (I have an example from a Joe Celko book that seems to do it) (b) If yes, then why not MySql? If no, then is there a simple reason why it's not sensible? Tom. -- 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 with pathnames on a LOAD DATA INFILE
hi, havant't you an automatic naming to .htm such as test.txt.htm rather than test.txt ? it's an upload problem. Can you access your file online ? Mathias Selon Brian Dunning [EMAIL PROTECTED]: I've got a GoDaddy virtual dedicated server and I'm trying to run a LOAD DATA INFILE, but I keep getting Can't get stat of '/home/httpd/ vhosts/04planet.info/httpdocs/test.txt' (Errcode: 13) As you can see from that error message, I've uploaded my data file to the httpdocs directory and trying to run: LOAD DATA INFILE '/home/httpd/vhosts/04planet.info/httpdocs/ test.txt' etc I've Googled and read some people found success by uploading their data file to a mysql directory instead, but I can't find such a directory for the life of me by browsing around through this server. Can anyone point me in a good direction? Thanks. :) -- 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: Got error 127 from storage engine
Hi amir, you have error 127 which means : C:\perror 127 MySQL error code 127: Record-file is crashed what i suggest is to use some of the solutions given here : http://dev.mysql.com/doc/mysql/en/repair.html if you could. another way is to increase tmp_table_size and use show processlist during the query. Can also LIMIT the rows deleted in many steps. Mathias Selon Amir Shay [EMAIL PROTECTED]: Hi, Using Linux 9 with mySQL 4.1.11-standard the tables are MyISAM When trying to delete from quit large tables (millions of records) range or records (few thousands) I got the error that there is a problem in the table and we need to do a repair. Then we run the repair table which finished successfully and try to delete again, however this time we got the error Got error 127 from storage engine and the table become corrupted - need to repair it When deleting few records there is no problem However there is no trace to the error in the log file Any Help ? Amir Server parameters 'back_log', '50' 'basedir', '/' 'binlog_cache_size', '32768' 'bulk_insert_buffer_size', '8388608' 'character_set_client', 'utf8' 'character_set_connection', 'utf8' 'character_set_database', 'hebrew' 'character_set_results', 'utf8' 'character_set_server', 'hebrew' 'character_set_system', 'utf8' 'character_sets_dir', '/usr/share/mysql/charsets/' 'collation_connection', 'utf8_general_ci' 'collation_database', 'hebrew_general_ci' 'collation_server', 'hebrew_general_ci' 'concurrent_insert', 'ON' 'connect_timeout', '100' 'datadir', '/var/lib/mysql/' 'date_format', '%Y-%m-%d' 'datetime_format', '%Y-%m-%d %H:%i:%s' 'default_week_format', '0' 'delay_key_write', 'ON' 'delayed_insert_limit', '100' 'delayed_insert_timeout', '300' 'delayed_queue_size', '1000' 'expire_logs_days', '0' 'flush', 'OFF' 'flush_time', '0' 'ft_boolean_syntax', '+ -()~*:|' 'ft_max_word_len', '84' 'ft_min_word_len', '4' 'ft_query_expansion_limit', '20' 'ft_stopword_file', '(built-in)' 'group_concat_max_len', '1024' 'have_archive', 'NO' 'have_bdb', 'NO' 'have_blackhole_engine', 'NO' 'have_compress', 'YES' 'have_crypt', 'YES' 'have_csv', 'NO' 'have_example_engine', 'NO' 'have_geometry', 'YES' 'have_innodb', 'YES' 'have_isam', 'NO' 'have_ndbcluster', 'NO' 'have_openssl', 'NO' 'have_query_cache', 'YES' 'have_raid', 'NO' 'have_rtree_keys', 'YES' 'have_symlink', 'YES' 'init_connect', '' 'init_file', '' 'init_slave', '' 'innodb_additional_mem_pool_size', '14680064' 'innodb_autoextend_increment', '8' 'innodb_buffer_pool_awe_mem_mb', '0' 'innodb_buffer_pool_size', '1073741824' 'innodb_data_file_path', 'ibdata1:10M:autoextend' 'innodb_data_home_dir', '/var/lib/mysql' 'innodb_fast_shutdown', 'ON' 'innodb_file_io_threads', '4' 'innodb_file_per_table', 'OFF' 'innodb_flush_log_at_trx_commit', '1' 'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_arch_dir', '' 'innodb_log_archive', 'OFF' 'innodb_log_buffer_size', '1048576' 'innodb_log_file_size', '5242880' 'innodb_log_files_in_group', '2' 'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90' 'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', '300' 'innodb_table_locks', 'ON' 'innodb_thread_concurrency', '8' 'interactive_timeout', '100' 'join_buffer_size', '131072' 'key_buffer_size', '8388600' 'key_cache_age_threshold', '300' 'key_cache_block_size', '1024' 'key_cache_division_limit', '100' 'language', '/usr/share/mysql/english/' 'large_files_support', 'ON' 'license', 'GPL' 'local_infile', 'ON' 'locked_in_memory', 'OFF' 'long_query_time', '10' 'low_priority_updates', 'OFF' 'lower_case_file_system', 'OFF' 'lower_case_table_names', '1' 'max_allowed_packet', '1047552' 'max_binlog_cache_size', '4294967295' 'max_binlog_size', '1073741824' 'max_connect_errors', '10' 'max_connections', '100' 'max_delayed_threads', '20' 'max_error_count', '64' 'max_heap_table_size', '16777216' 'max_insert_delayed_threads', '20' 'max_join_size', '4294967295' 'max_length_for_sort_data', '1024' 'max_relay_log_size', '0' 'max_seeks_for_key', '4294967295' 'max_sort_length', '1024' 'max_tmp_tables', '32' 'max_user_connections', '0' 'max_write_lock_count', '4294967295' 'net_buffer_length', '16384' 'net_read_timeout', '30' 'net_retry_count', '10' 'net_write_timeout', '60' 'new', 'OFF' 'old_passwords', 'OFF' 'open_files_limit', '1024' 'pid_file', '/var/lib/mysql/mendelson.lotonet.local.pid' 'port', '3306' 'preload_buffer_size', '32768' 'protocol_version', '10' 'query_alloc_block_size', '8192' 'query_cache_limit', '67108864' 'query_cache_min_res_unit', '4096' 'query_cache_size', '67108864' 'query_cache_type', 'ON' 'query_cache_wlock_invalidate', 'OFF' 'query_prealloc_size', '8192' 'range_alloc_block_size', '2048' 'read_buffer_size', '131072' 'read_only', 'OFF'
Re: How to summarize a table?
Hu Juan, see my answer above Re: Subselect in an Update query. You can't update and select in a sybquery using the same table master. use tempo table for the join and update after. Mathias Selon Juan Pedro Reyes Molina [EMAIL PROTECTED]: I'm running mysql 4.1.7. For the sake of this message I have created this tables: CREATE TABLE `log` ( `ID` int(11) NOT NULL auto_increment, `Cod_P` varchar(5) NOT NULL default '', `Import` double NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM; CREATE TABLE `master` ( `Cod_P` varchar(5) NOT NULL default '', `Total` double NOT NULL default '0', PRIMARY KEY (`Cod_P`) ) ENGINE=MyISAM; I want master.Total to hold a sum of log.Import for every Cod_P In Microsoft Sql Server I have run: update master set Total = TotalImport from ( select sum(Import) TotalImport, log.Cod_P from master inner join log on (master.Cod_P=log.Cod_P) group by log.Cod_P) t where master.Cod_P = t.Cod_P which, IMHO, is a clean way to do this. in mysql I get a syntax error. I tried update `master`,`log` set Total = Total + Import where `master`.Cod_P=`log`.Cod_P but this only put in master table the value of just one row of every Cod_P in log table. this way it works update `master` set Total = (select sum(Import) from `log` where `master`.Cod_P=`log`.Cod_P) but I get 1 warning (I don't know what warning) and I deem this way inefficient in case I have to summarize several columns. I would need to write update `master` set Total = (select sum(Import) from `log` where `master`.Cod_P=`log`.Cod_P), set Total1 = (select sum(Import1) from `log` where `master`.Cod_P=`log`.Cod_P), set Total2 = (select sum(Import2) from `log` where `master`.Cod_P=`log`.Cod_P), set Total3 = (select sum(Import3) from `log` where `master`.Cod_P=`log`.Cod_P), set Total4 = (select sum(Import4) from `log` where `master`.Cod_P=`log`.Cod_P), set Total5 = (select sum(Import5) from `log` where `master`.Cod_P=`log`.Cod_P), set Total6 = (select sum(Import6) from `log` where `master`.Cod_P=`log`.Cod_P) while in Sql Server it would look something like: update master set Total = TotalImport,Total1 = TotalImport1,Total2 = TotalImport2,Total3 = TotalImport3,Total4 = TotalImport4,Total5 = TotalImport5,Total6 = TotalImport6 from ( select sum(Import) TotalImport,sum(Import1) TotalImport1,sum(Import2) TotalImport2,sum(Import3) TotalImport3,sum(Import4) TotalImport4,sum(Import5) TotalImport5,sum(Import6) TotalImport6, log.Cod_P from master inner join log on (master.Cod_P=log.Cod_P) group by log.Cod_P) t where master.Cod_P = t.Cod_P with only one subselect to put your eyes on (and to debug just in case). In an example I run, I think mysql fired every one subselect while sql server do all the work with just one round (it was just one quick test, so I cannot be sure) Maybe I'm overlooking something. Is there anyone out there with a better mysql syntax to accomplish this? -- 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: need help in stroing and retreving images from database
Hi, all binary docs can be inserted in blob columns. see this link for a php insert method :http://www.phpcs.com/code.aspx?ID=30945 this is mysql doc : http://dev.mysql.com/doc/mysql/en/blob.html Mathias Selon madderla sreedhar [EMAIL PROTECTED]: Sir , Iam working on Mysql5.0 version. Can I store .jpeg or .png or .gif etc imagefiles in database. If so where can I get the tutorials or sample code to insert and retrive the images from mysql database. Any help is welcome. Thanking you , Sreedhar __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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: Ordinal number within a table
hi, the position of a record depend on the sort order you choose in your queries (generally order by) and the execution plan of them. this execution plan depends on data, indexes, and the query itself. So what you call order is candidate to changing between two selects. the method shown with @row++ is a solution, but the given order is the display data order for you. use an order by so make it stronger. Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Ed Reed Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. I'm not sure why you'd want to know this, but it's generally a good idea to abandon the thought that records in a database are stored in a certain order with a position number attached to them. The internal way of storing data differs from engine to engine and you can never be sure that these internals will not be modified in newer releases of MySQL. It's best to think of a table as a collection of records which can be presented in the way you want. The way the database decides to store the data is in many cases not relevant at all; that's the job of the database. Regards, Jigal. -- 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 summarize a table?
Juan, i found you a link explaining the access and sqlserver ansi inner joins in update. it's in german, but can be read (i don't speak german :o)) : http://www.sql-und-xml.de/sql-tutorial/update-aktualisieren-der-zeilen.html but i've never tried this with mysql. there are some other methods, but since view come just in v5, inline views (called subqueries) will certainly be more possible in next versions of mysql (even when updating). Mathias Selon Juan Pedro Reyes Molina [EMAIL PROTECTED]: Hi, Mathias! I had read that post. Just thought it was not the same case. I'm reading log table and updating master table. What do you think about Sql Server sintax. Is that sql standard or a sql server dialect? Actually I'm working around this using a temp table. I would like to know if there is a pure sql solution because I don't like to have more temp tables that strictly needed. Thanks for your time. Time is our most valuable asset! [EMAIL PROTECTED] wrote: Hu Juan, see my answer above Re: Subselect in an Update query. You can't update and select in a sybquery using the same table master. use tempo table for the join and update after. Mathias Selon Juan Pedro Reyes Molina [EMAIL PROTECTED]: I'm running mysql 4.1.7. For the sake of this message I have created this tables: CREATE TABLE `log` ( `ID` int(11) NOT NULL auto_increment, `Cod_P` varchar(5) NOT NULL default '', `Import` double NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM; CREATE TABLE `master` ( `Cod_P` varchar(5) NOT NULL default '', `Total` double NOT NULL default '0', PRIMARY KEY (`Cod_P`) ) ENGINE=MyISAM; I want master.Total to hold a sum of log.Import for every Cod_P In Microsoft Sql Server I have run: update master set Total = TotalImport from ( select sum(Import) TotalImport, log.Cod_P from master inner join log on (master.Cod_P=log.Cod_P) group by log.Cod_P) t where master.Cod_P = t.Cod_P which, IMHO, is a clean way to do this. in mysql I get a syntax error. I tried update `master`,`log` set Total = Total + Import where `master`.Cod_P=`log`.Cod_P but this only put in master table the value of just one row of every Cod_P in log table. this way it works update `master` set Total = (select sum(Import) from `log` where `master`.Cod_P=`log`.Cod_P) but I get 1 warning (I don't know what warning) and I deem this way inefficient in case I have to summarize several columns. I would need to write update `master` set Total = (select sum(Import) from `log` where `master`.Cod_P=`log`.Cod_P), set Total1 = (select sum(Import1) from `log` where `master`.Cod_P=`log`.Cod_P), set Total2 = (select sum(Import2) from `log` where `master`.Cod_P=`log`.Cod_P), set Total3 = (select sum(Import3) from `log` where `master`.Cod_P=`log`.Cod_P), set Total4 = (select sum(Import4) from `log` where `master`.Cod_P=`log`.Cod_P), set Total5 = (select sum(Import5) from `log` where `master`.Cod_P=`log`.Cod_P), set Total6 = (select sum(Import6) from `log` where `master`.Cod_P=`log`.Cod_P) while in Sql Server it would look something like: update master set Total = TotalImport,Total1 = TotalImport1,Total2 = TotalImport2,Total3 = TotalImport3,Total4 = TotalImport4,Total5 = TotalImport5,Total6 = TotalImport6 from ( select sum(Import) TotalImport,sum(Import1) TotalImport1,sum(Import2) TotalImport2,sum(Import3) TotalImport3,sum(Import4) TotalImport4,sum(Import5) TotalImport5,sum(Import6) TotalImport6, log.Cod_P from master inner join log on (master.Cod_P=log.Cod_P) group by log.Cod_P) t where master.Cod_P = t.Cod_P with only one subselect to put your eyes on (and to debug just in case). In an example I run, I think mysql fired every one subselect while sql server do all the work with just one round (it was just one quick test, so I cannot be sure) Maybe I'm overlooking something. Is there anyone out there with a better mysql syntax to accomplish this? -- 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: Wrf files: how can I read them?
Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- 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: Re: Wrf files: how can I read them?
Mysql seminar organizer's should answer. Mathias Selon asteddy [EMAIL PROTECTED]: Thank you, but why has mysql made seminars wich must be seen with a non-free software with a 14 days trial? Is there nothing else to see it? Asteddy Hi, there is link take the free trial on webex site. haven't you seen the seconf button ? Mathias Selon asteddy [EMAIL PROTECTED]: Hello, I have found Mysql Performance Tuning Seminar available for download, but I don't know how to see it. I have found something like Webex website, but I don't see any software to download there. Can you help me please? Why is there nothing specified about that type of file on the download page of the seminar? Thank you very much. Asteddy -- 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: Subselect in an Update query
Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution * create table t as Select field1 From table1 Where field2=Some Value Update table1 Set field1=(Select field1 From t Where field2=Some Value) Where field2 =Another Value; ** mysql select * from upd; +--+--+ | a| b| +--+--+ |1 | one | |1 | two | |2 | one | |2 | two | |1 | Un | +--+--+ 5 rows in set (0.09 sec) mysql create table t as select * from upd where b='Un'; mysql update upd set a=(select a from t where b='Un') where b='one'; mysql select * from upd; +--+--+ | a| b| +--+--+ |1 | one | |1 | two | |1 | one | changed |2 | two | |1 | Un | +--+--+ 5 rows in set (0.00 sec) Mathias Selon Ed Reed [EMAIL PROTECTED]: Can anyone tell me how I can make this work or suggest a work around? Update table1 Set field1=(Select field1 From table1 Where field2=Some Value) Where field2 =Another Value; Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
Hi, i don't think so. 2 go is a limit of almsot 32-bits plateform, linux or others. Migrate to 64-bits. Mathias Selon Brady Brown [EMAIL PROTECTED]: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? Although I have been given good advice to migrate to an AMD platform overcome this limitation (and will do so eventually), I am looking for a shorter-term solution to beat this 2Gb malloc limit on a 32-bit machine. Can it be done with FreeBSD? Are there any memory allocation issues when linking to the Linux Thread Library? Are there other FreeBSD tuning measures that need to be taken? Looking forward to hearing from those who have charted this territory. Thanks, Brady -- 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: can innodb_buffer_pool_size be set 2Gb on FreeBSD?
pretty interesting. i'll test it for oracle. But the db_cache will be a simple swap file. i don't think it's as good as real memory for dirty lists management. Mathias Selon David Griffiths [EMAIL PROTECTED]: I'll post something I heard about when looking into upgrading Oracle 8i from Windows to Oracle 10g on Linux. To get more memory for the process, you would enable big memory page, and then create an in-memory temp file system; you could then allocate extra memory for a process, and part of it would be swapped out to this temp file system in memory. Red Hat Advanced Server was the OS of choice for those who did it - I played around with it, but couldn't get Oracle to start with larger memory settings (we weren't running on RedHat AS). Maybe you'll have more luck. A good page that talked about this was, http://www.oracle-base.com/articles/Linux/LargeSGAOnLinux.php Good luck. David Jeff Smelser wrote: On Friday 17 June 2005 02:38 pm, Brady Brown wrote: Have any of you MySQL/FreeBSD cats successfully set innodb_buffer_pool_size 2G without runing into any of the memory allocation problems found on Linux platforms? It has nothing to do with linux.. its an x86 thing.. So no.. However, some kernels have things to let you go over, but you get weird results when doing so. Jeff -- 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: Table full
You have also 3000 * 7 millions columns to left joins to x,y,.. others tables. And you use myisam. this will certainly be a big update problem. I suggest you to transform your query into : 1. select using the left joins to see first the number of rows to be updated 2. according to this number the strategy can be different : small number = update large number = tempo table containing the non updated rows copy the will be updated rows to tempo truncate table load tempo in the main table Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Mike, Thanks for the insight. The sent table has about 7 million records. The other tables involved have tens of thousands of records or there abouts. Not your 100 million size but certainly worth exploring. Thanks again, Tripp --- mos [EMAIL PROTECTED] wrote: Tripp, This problem may occur if your table is quite large (several gb in size). The update may make the table too large to address using conventional MySQL pointers. You may need to modify the table so it has a Max Rows= option where is the max rows you expect the table to have and this forces MySQL to use a larger table pointer. When my tables === message truncated === __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- 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:Backing up live MySQL Databases
hi, there will not be a lot of paying backup tools if just copying files is OK. You can't because active transactions change some files checkpoint, then you copied files are not at the same checkpoint state, and you can't use the copy for a restore. The only way is to lock all the tables if you want to copy files. But this is not good if you have a mssive transactionnal DB (a lot of redo log will be generated during backup with locked tables). This can be a solution because hot backup of an oracle database uses this macanisme of macking a tablespace in backup mode. Mathias Selon Ashley M. Kirchner [EMAIL PROTECTED]: With all this discussion going on about the best way to backup MySQL data, I have a question: Is there anything wrong with simply copying the db files when you're backing up, as opposed to doing a full shutdown of the server (regardless of whether it's a master or slave) and then doing a dump of the data? (Note the difference, one will copy files, the other dumps the data for backup.) -- W | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Laboratories, Inc.. 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- 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: Table full
hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- 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: problem
Hi, don't you really want to write also in Times new roman your mysql data ? you forgot the tools, OS ... (client) or it's a big jock. Mathias Selon nicolas ghosn [EMAIL PROTECTED]: Dear mysql supports, I want to change the font color for database mysql , for example I want to insert data with red color when I select this data the font color is the default black.It's possible to change the font color. thank you. -- 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: Table full
sorri it's tmp_table_size. mysql show variables like '%table%'; ++--+ | Variable_name | Value| ++--+ | innodb_file_per_table | OFF | | innodb_table_locks | ON | | lower_case_table_names | 1| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 256 | | table_type | InnoDB | | tmp_table_size | 9437184 | ++--+ 8 rows in set (0.00 sec) What are : show create table toto; the count(*) ? the query ? Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Mathias, Thanks for the reply. I couldn't find a server variable named max_temp_table_size but I did find one named max_heap_table_size. Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the Table #sql-123 is full error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- 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] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write subqueries?
Hello,
You forgot a parenthesis in the regexp function.
Second, if you construct your query with prepared statements, you can do it :
mysql insert into vt_partition(pnid) values('01234567890');
mysql select concat('vt_',pnid) into @tbl from vt_parition where pnid regexp
('^[0-9]{11}$') ;
mysql set @sql:=concat('select num, theme, intro from ', @tbl);
mysql prepare stmt from @sql;
Statement prepared
mysql execute stmt;
mysql deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
Hope that helps
N.B. you should have only one pnid to make your query work (UNIQUE pnid), or
LIMIT the result of the subquery by LIMIT 1.
Mathias
Selon admin [EMAIL PROTECTED]:
Hello!
MySQL v. 4.1.11.
I'm trying to write a subquery, MySQL says Error.
select num, theme, intro
from vt_(select pnid from vt_partition where pnid regexp '^[0-9]{11}$')
order by date desc, timer desc;
describe vt_parition:
+--+--+--+-+--++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+--++
| num | int(11) | | MUL | NULL | auto_increment |
| partname | varchar(255) | | | ||
| pnid | varchar(11) | | | ||
| timer| time | | | 00:00:00 ||
+--+--+--+-+--++
describe vt_24411620611;
+---+--+--+-+++
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-+++
| num | int(11) | | MUL | NULL | auto_increment |
| login | varchar(10) | | |||
| mail | varchar(35) | | |||
| theme | varchar(100) | | |||
| intro | text | | |||
| text | text | | |||
| date | date | | | -00-00 ||
| timer | time | | | 00:00:00 ||
+---+--+--+-+++
vt_ is a prefix, pnid is a postfix.
And the name of the table is, for expamle, vt_01234567890.
Where is/are the mistake(s) in my subquery?
Please explain me how to write a subquery in the right way.
--
Good luck!
Vladimir
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
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 write subqueries?
You're welcome.
if you have more than one pnid, you should use perl or php. then use a loop to
manage each pnid query, then send it to the server, and use it's result.
another simpler solution :
mysql create table sql as select concat('vt_',pnid) tbl from vt_parition where
pnid regexp ('^[0-9]{11}$') ;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from sql;
++
| tbl|
++
| vt_01234567890 |
| vt_09876543210 |
++
2 rows in set (0.00 sec)
mysql alter table sql modify tbl varchar(255);
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql update sql set tbl=concat('select num, theme, intro from ',tbl,';');
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql
mysql select * from sql;
++
| tbl|
++
| select num, theme, intro from vt_01234567890; |
| select num, theme, intro from vt_09876543210; |
++
2 rows in set (0.00 sec)
NOW :
1. copy/paste and execute
2. select * into outfile and source
3. every other extarction/execution method.
Mathias
Selon admin [EMAIL PROTECTED]:
Hello!
Many thanks to everybody who tried to help me!
N.B. you should have only one pnid to make your query work (UNIQUE pnid),
or LIMIT the result of the subquery by LIMIT 1.
I need to do the following:
Get one or more than one pnid from vt_partition and then make queries to
the
vt_pnid tables. The result (from all the requested vt_pnid tables) should
be sorted by date and time. How to do it?
The version of MySQL should be 4.1.11 or lower. It would be great if the
query
works even on v. 3.23.58.
First off, you are not writing a subquery. You are attempting to build a
SQL query using the results of another query. What you are attempting is
dynamic SQL. I am not familiar with a mechanism in 4.1.11 that allow you
to execute a string (or a string variable) from within a SQL statement.
Damn! You're right!! I only looked at the subquery itself, not the context
and I missed that his subquery seems to an expression to get part of his
table name! That is definitely not valid in any dialect of SQL that I know
;-)
Agree, I was wrong. :-)
--
Good luck!
Vladimir
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
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: Tough queries
Hi,
for the first query,
select concat(team_id,' (',sum(points),')') from games,points
where games.game_id=points.game_id
and games.team_id1=points.team_id
group by team_id
can solve the problem.
For the second, join players and points.
Mathias
Selon David Legault [EMAIL PROTECTED]:
Hello,
I'm a regular user of MySQL but lately on a personal project I've run
into some very complexe query management and am a little bit confused on
how to get it working. This is a hockey league stats website application.
I have 4 tables with the following columns:
teams - team_id | team_name
players - player_id | team_id | player_name
games - game_id | team_id1 | team_id2
points- point_id | game_id | team_id | goal_player_id | pass_player_id1
| pass_player_id2
The kind of queries I'd like to perform would be (if possible in one
query or a subset of queries) something to generate the following as a
list of N games with the scores (if the game was played and team name)
which would use the games, teams and points tables.
Desired Output
Team A (2) - Team B (7)
Team D (3) - Team C (1)
...
Thus, it needs to retreive the team names, and total score for each game
that I want to list (using other criteria not essential in the example)
Another Query would be to have the points of each player listed for a
team (in ORDER DESC or total points):
Team C:
Player | Goals | Assists | Points
AA 8 1 9
BB 5 3 8
CC 3 2 5
DD 1 2 3
If you could explain a little bit how each query answer you provide
works, I'd like it. If you have any tutorials or good articles about
such complexe queries, I'd be thankfull to be able to read them to help me.
Thanks
David
--
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: idepontent inserts? Allowing INSERT to fail faster than SELECT/INSERT
the already implemented solution is : select ... for update; a unique key gives you an error that you have to manage for updating rather than insert. you must catch and analyse that the error is duplicate key and not another. but this is right if you want lauch insert without waiting to see if there is a duplicate key error. Mathias Selon Kevin Burton [EMAIL PROTECTED]: I've been thinking about this for a while now. If you have an app that can compute a unique key (hashcode) and you have a unique index it should be possible to just do an INSERT instead of a SELECT first to see if the record doesn't exist and then an INSERT. This should be 2x faster than the SELECT/INSERT combo right? -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Backup/Dump
different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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: Considering migration from MyISAM to InnoDB
see the my.cnf examples in the install dir, and look at innodb* variables. you can migrate each table just using : alter table toto engine=innodb; Mathias Selon Stembridge, Michael [EMAIL PROTECTED]: I currently use MyISAM on an internal web application server; our data takes up 10mb at this time, though this is likely to grow substantially in the coming year. The database sees moderate heavy read and moderate write usage from 50 users. We're upgrading our sever from Red Hat 7.3 to SuSE Linux Enterprise 9.0 soon and have considered migrating to InnoDB as part of our upgrade.I like the performance increases I've seen documented here http://www.innodb.com/bench.php, though I'm not sure our environment calls for InnoDB. Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimises LEFT JOIN
Hi zoltan if you know difference between inner join and left join, you can conclude than maybe there are a lot of rows in table1 which do not verify you join condition. This example show you that the result is different and and add'in where clause if you want to transform the left join result like then inner join one : mysql select * from a; +--+-+ | id | txt | +--+-+ |1 | a test1 | |2 | a test2 | |3 | a test3 | +--+-+ 3 rows in set (0.03 sec) mysql select * from b; +--+-+ | id | txt | +--+-+ |1 | b test1 | |2 | b test2 | +--+-+ 2 rows in set (0.00 sec) mysql mysql mysql select * from a inner join b on a.id=b.id; +--+-+--+-+ | id | txt | id | txt | +--+-+--+-+ |1 | a test1 |1 | b test1 | |2 | a test2 |2 | b test2 | +--+-+--+-+ 2 rows in set (0.00 sec) mysql select * from a left join b on a.id=b.id; +--+-+--+-+ | id | txt | id | txt | +--+-+--+-+ |1 | a test1 |1 | b test1 | |2 | a test2 |2 | b test2 | |3 | a test3 | NULL | NULL| +--+-+--+-+ 3 rows in set (0.00 sec) mysql mysql mysql select * from a left join b on a.id=b.id where b.txt is not null; +--+-+--+-+ | id | txt | id | txt | +--+-+--+-+ |1 | a test1 |1 | b test1 | |2 | a test2 |2 | b test2 | +--+-+--+-+ 2 rows in set (0.00 sec) Hope that helps. Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Gyurasits Zoltán I have a speed problem with LEFT JOIN condition. (...) Why?? Somebody can help me? Tnx!! You will probably get a lot more useful response if you include the table structure, the complete query/queries and the output of EXPLAIN for each query (just put EXPLAIN in front of the SELECT. It will give you information about the execution path that MySQL came up with). Regards, Jigal. -- 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: Database Table Date Assistance
Hi scott, you can use datetime or timestamp. Using timestamp in this example shows you that now() can be a default or inserted value. You can also use current_tiumestamp. Other functions like date_add can help you to look for rows verifying interval days like in this example : mysql create table dates(id int, d timestamp default now()); Query OK, 0 rows affected (0.06 sec) mysql insert into dates(id) values(1); Query OK, 1 row affected (0.03 sec) mysql insert into dates(id) values(2); Query OK, 1 row affected (0.02 sec) mysql select * from dates; +--+-+ | id | d | +--+-+ |1 | 2005-06-13 10:22:47 | |2 | 2005-06-13 10:22:50 | +--+-+ 2 rows in set (0.00 sec) mysql select date_add(d,interval 2 day) from dates; ++ | date_add(d,interval 2 day) | ++ | 2005-06-15 10:22:47| | 2005-06-15 10:22:50| ++ 2 rows in set (0.05 sec) Clearer info in dev.mysql.com/doc about timestamp and datetime. mysql hope that helps. Mathias Selon Scott Purcell [EMAIL PROTECTED]: Hello, I would like to do the following: I am creating a site where people can add items to a cart. In order to keep items for [X] amount of days, I would like to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in a relationship table I can hold the 'itemRef' and 'items' they have choosen. I think this would be simple. But there are a lot of choices for the date field. I would like a date field that I can insert a now() or something, when I insert. And then later, through Java code, query and find all dates that are greater than [X] amount of days, and delete them to keep the database clean? So my question would be, A) which date type field should I create. B) how to insert now() C) can I run one query to find out if the date field is greater than [X] days? Any help would be appreciated. Sincerely Scott -- 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: mysql decrypt
Hi,
Have you tried this ? i said the length, so you can rise it until working for
tests. I used 32k and it works without any problem. I can remind that with a
blob it works also :
mysql create table t1( asunto varbinary(32000), asun varbinary(16) );
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
Query OK, 1 row affected (0.03 sec)
mysql select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
+---+-+
| decrypted | asun|
+---+-+
| aNuevo1 | aNuevo1 |
+---+-+
1 row in set (0.00 sec)
* But with a small length :
mysql create table t1( asunto varbinary(16), asun varbinary(16) );
Query OK, 0 rows affected (0.08 sec)
mysql insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
Query OK, 1 row affected (0.03 sec)
mysql select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
+---+-+
| decrypted | asun|
+---+-+
| NULL | aNuevo1 |
+---+-+
1 row in set (0.00 sec)
That's why.
Hope that helps
Mathias
Selon Alejandro Alekhine [EMAIL PROTECTED]:
Ok, if you´re right ... how can I repair the incorrect padding ?? Because I
think data is correct. It fully respects so fields´ length so as
fields´types.
Thanks
From: [EMAIL PROTECTED]
To: Alejandro Alekhine [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: mysql decrypt
Date: Tue, 17 May 2005 00:30:55 +0200
Hi,
You may have invalid data or incorrect padding when null is retuned :
http://dev.mysql.com/doc/mysql/en/encryption-functions.html
Mathias
Selon Alejandro Alekhine [EMAIL PROTECTED]:
Hi, I´m developing a database with the aes_encrypt and aes_decrypt
functions, with integrity constraints and varbinary types.
My problem is that when I insert a row into a table, I encrypt with
aes_encrypt, but when I desencrypt with aes_decrypt, it returns null
with
some values. The only way I've found is using a blob type instead of
varbinary, but this type doesn't support integrity constraints.
For example,
create table t1( asunto varbinary(16), asun varbinary(16) );
insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
The result is decrypted=NULL and asun='aNuevo1'
Why ??? The length of the fields is correct, I don´t know why. But if I
change varbinary by blob, it runs. But with blob I can´t do any
integrity
constraint.
Thanks
--
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: Regarding NOT NULL Option for Table Fields....
Hi ashok,
With check, you could do iy, but they don't work with mysql.
The only solution i ican see is an application control or :
drop table if exists tempo;
create table tempo like mine;
insert into tempo values('','');
insert into mine select * from tempo where length(...)0;
drop table tempo;
Mathias
Selon Ashok Kumar [EMAIL PROTECTED]:
hi Mathias,
My question is how can i protect the empty strings
(that contains length 0).
thanks and regards,
Ashok Kumar.P.S.
--- [EMAIL PROTECTED] wrote:
Hi,
'' or empty string is not a null in mysql. This is
true for Oracle !
this simple test lets you understand :
* Without NULLs
mysql create table notnull (t varchar(10) NOT
NULL);
Query OK, 0 rows affected (0.14 sec)
mysql insert into notnull values('test1');
Query OK, 1 row affected (0.01 sec)
mysql insert into notnull values('');
Query OK, 1 row affected (0.02 sec)
mysql insert into notnull values(NULL);
ERROR 1048 (23000): Column 't' cannot be null
mysql select * from notnull;
+---+
| t |
+---+
| test1 |
| |
+---+
2 rows in set (0.02 sec)
mysql select * from notnull where isnull(t);
Empty set (0.02 sec)
* With NULLs
mysql create table isnulle(a varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql insert into isnulle values(NULL);
Query OK, 1 row affected (0.03 sec)
mysql select * from isnulle where isnull(a);
+--+
| a|
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Hope that helps.
Mathias
Selon Ashok Kumar [EMAIL PROTECTED]:
Hi friends,
I'm having one doubt on NOT NULL specification
for
the table field properties. That is i have created
on
table-mine which contains name(Not Null) and
pwd(Not
Null). Now I intended to execute the following
query.
insert into mine values('','')
This means that i'm trying to insert the null
fields
to the table. but this query is successfully
executed
and 1 row is inserted into table with empty
values.
why it's happening and how can i resolve this
problem.
Pls give me suggestions regarding this.
Thanks and Regards,
Ashok Kumar.P.S
__
Discover Yahoo!
Stay in touch with email, IM, photo sharing and
more. Check it out!
http://discover.yahoo.com/stayintouch.html
--
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]
__
Discover Yahoo!
Get on-the-go sports scores, stock quotes, news and more. Check it out!
http://discover.yahoo.com/mobile.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to load a remote db with lots of data?
Hi, there is nothing simplier and faster than load data infile (LOCAL) since you can connect to the server Mathias Selon Berman, Mikhail [EMAIL PROTECTED]: Brian, How about an FTP service on your remote server? Mikhail Berman -Original Message- From: Brian Dunning [mailto:[EMAIL PROTECTED] Sent: Monday, June 13, 2005 10:01 AM To: mysql@lists.mysql.com Subject: How to load a remote db with lots of data? I have to load my remote MySQL db's with about a gig of data - phpMyAdmin only allows me to upload a 15MB CSV file, so I have to painstakingly separate my data into 15MB chunks and upload them one at a time. It's a huge pain and takes about two entire days. Is there a better solution?? (My ISP blocks remote access to MySQL, so I have to use phpMyAdmin or any other tool as long as I upload it. I'm not a command-line guy and don't have a clue about that.) -- 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: best configuration for ip failover
Hi, you can configure bin-log on one, and send logs the second server for sync. you will have an almost real-time synchronization env. problem is that you MUST use inodb for ttansactions, and apply exactly the changes to the standby server. no replication, no cluster. This is a simple but needed to be maintained solution. Mathias Selon Susan Ator [EMAIL PROTECTED]: This is our situation: We have two servers running RHES3 with samba connections to a server running RHES4. (No, it's not dns. Believe me when I say I don't ever want to go through *that* pain again) Our ES4 server contains shared data that is not controlled through cvs. Our two ES3 servers contain our website which is controlled through cvs. Both our ES3 servers have mysql. The mysql dbs have our username/password (and other) information. We are in the process of setting up ip failover using a dns round-robin. My concern about keeping the mysql dbs on the ES3 servers is the possibility of changing the db on one and also changing the db on the other; I don't know how to keep the changes synced. I was considering putting the mysql db on the ES4 server and both the ES3 servers would connect to the mysql db there. My partner in crime is concerned about this since it would be a single point of failure. This is my question: What would be the best configuration for maintaining database integrity? I don't mean just between what I outlined, but in general? Clusters seems to be overly complicated (from what I can understand of it). I just need some direction at this point. Thanks, Susan -- 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: Re-use a result field into a query
Well Alvaro, you can use variables to re-use resultas like that : mysql set @rs=0; Query OK, 0 rows affected (0.02 sec) mysql select 1, (@rs:=2+3) as result1, - (@rs*100) as result2 - from dual; +---+-+-+ | 1 | result1 | result2 | +---+-+-+ | 1 | 5 | 500 | +---+-+-+ 1 row in set (0.00 sec) Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- 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: storing recurring dates
hi,
have you tried to hack something with week, month and weekofyear ?
there is an interesting url at
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
be aware that the week begins on sunday.
mysql select weekofyear('2005-01-02');
+--+
| weekofyear('2005-01-02') |
+--+
| 53 |
+--+
1 row in set (0.00 sec)
mysql select weekofyear('2005-01-03');
+--+
| weekofyear('2005-01-03') |
+--+
|1 |
+--+
you can then construct a week-based calendar for the events.
hope that helps.
Mathias
Selon [EMAIL PROTECTED]:
Bob,
You should check out the Date::Manip module from CPAN, it will do what you
need to do. Here is a snippet from the docs about recurrence:
RECURRENCE
A recurrence is simply a notation for defining when a recurring event
occurs. For example, if an event occurs every other Friday or every 4
hours, this can be defined as a recurrence. With a recurrence and a
starting and ending date, you can get a list of dates in that period when
a recurring event occurs.
This should get you going with all the options you need.
Chris Hood
-Original Message-
From: Ramsey, Robert L [mailto:[EMAIL PROTECTED]
Sent: Sunday, June 12, 2005 8:51 AM
To: mysql@lists.mysql.com
Subject: storing recurring dates
Hi,
I'm doing an event project and some of the events will be reccuring. For
example:
Monday, Wednesday, Friday from 10-11:30 am starting June 1 with no end
date
Every third Monday at 3-4 pm starting July 1 and ending January 1 (last
event is third Monday in December)
Every other Friday starting at 1pm with no set end time, starting June 3
Is there a good way to store those in a mysql database? So far the only
thing I can think of is that on entry, have a script figure out all of the
dates, which is pretty easy in php. Then for the events with no end date
set an arbitrary end date of 5 years in the future knowing that the
technology will probably change by then and the app will need to be
re-written.
Is there maybe some way or combination with the php strtotime function? I
know it can take something like Third Thursday of October and turn it
into a unix time stamp. But my brain is just not working today. ;)
Thanks,
Bob
--
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: (Fine) Tuning Server Parameters
hi, look at insert buffer and buffer pool. This is quite interesting : http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Insert_buffering.html mathias Selon Manoj [EMAIL PROTECTED]: Dear All, I am trying to tweak some server parameters to fine tune my MySQL (4.0.15) server on a linux box (with 6GB of ram) All my tables/databases use InnoDB. My question is : What is the InnoDB equivalent of the server parameters Key_read_request key_reads. I had a look at show innodb status to get an idea of these parameters but got lost in all the information provided by show innodb status, hence would appreciate any pointers! My entire my.cnf is as below for reference/additional comments that would be useful in fine-tuning server parameters. Thanks for your help in advance. Cheers Manoj -- [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=256M table_cache=512 sort_buffer=8M join_buffer_size=8M read_buffer_size=8M read_rnd_buffer_size=6M max_connection=30 max_allowed_packet= 16M binlog_cache_size = 4M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format long_query_time = 10 query_cache_size = 256M query_cache_limit = 16M tmp_table_size = 400M thread_cache = 8 thread_concurrency = 8 # innodb_options innodb_data_home_dir=/usr/local/mysql/ibdata/ innodb_data_file_path=ibdata1:5G;ibdata2:1G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=1500M innodb_log_buffer_size=16M innodb_buffer_pool_size=1500M innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT -- 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: Regarding NOT NULL Option for Table Fields....
Hi,
'' or empty string is not a null in mysql. This is true for Oracle !
this simple test lets you understand :
* Without NULLs
mysql create table notnull (t varchar(10) NOT NULL);
Query OK, 0 rows affected (0.14 sec)
mysql insert into notnull values('test1');
Query OK, 1 row affected (0.01 sec)
mysql insert into notnull values('');
Query OK, 1 row affected (0.02 sec)
mysql insert into notnull values(NULL);
ERROR 1048 (23000): Column 't' cannot be null
mysql select * from notnull;
+---+
| t |
+---+
| test1 |
| |
+---+
2 rows in set (0.02 sec)
mysql select * from notnull where isnull(t);
Empty set (0.02 sec)
* With NULLs
mysql create table isnulle(a varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql insert into isnulle values(NULL);
Query OK, 1 row affected (0.03 sec)
mysql select * from isnulle where isnull(a);
+--+
| a|
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Hope that helps.
Mathias
Selon Ashok Kumar [EMAIL PROTECTED]:
Hi friends,
I'm having one doubt on NOT NULL specification for
the table field properties. That is i have created on
table-mine which contains name(Not Null) and pwd(Not
Null). Now I intended to execute the following query.
insert into mine values('','')
This means that i'm trying to insert the null fields
to the table. but this query is successfully executed
and 1 row is inserted into table with empty values.
why it's happening and how can i resolve this problem.
Pls give me suggestions regarding this.
Thanks and Regards,
Ashok Kumar.P.S
__
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html
--
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 control database size in MySQL Windows?
Hi, in versions less than 5.x, show table status gives informations about rows and avg_row_length. The product gives you a correct approximation if the stats are analyzed. This is for actions from the client. another thing is the OS commands from your datadir (du -k). Hope that helps. Mathias Selon Gordon Bruce [EMAIL PROTECTED]: If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query like this. A casual scan of the mysql tables don't show any sizes and I don't know of a way to get table/database size via SQL. mysql select table_schema, sum(DATA_LENGTH) from information_schema.tables group by 1; ++--+ | table_schema | sum(DATA_LENGTH) | ++--+ | information_schema | 0| | mailprint | 2523448288 | | mysql | 275126 | | test | 16510| ++--+ 4 rows in set, 79 warnings (6.22 sec) -Original Message- From: Salama hussein [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 1:31 PM To: mysql@lists.mysql.com Subject: How to control database size in MySQL Windows? I think the answer to this is You can't. So I guess what I can do is run a query once every while and get the sizes of all the databases and if any exceeds a predetermined size, revoke insert and update privilages. What's is the SQL query like to get a database size and the SQL to get the names of all the databases? Salama -- 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: help converting tables to excel format
if you want to use excel i suppose you have also windows with more than 3 GO free disk. install win32 mysql put the files in the data directory of one existing database connect and use that database export data to an outfile by : select * from yourtable into outfile csv fields terminated by ';' open the csv with excel. Mathias Selon Tom Beidler [EMAIL PROTECTED]: I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it. i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (SQL) Newbie.. Need help with a Query
hi, that's the same. If you use between, mysql do the rest for you : mysql explain SELECT * FROM passengers WHERE - reservation_date_time = '2005-01-01 12:10:00' - AND reservation_date_time = '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.01 sec) mysql explain SELECT * FROM passengers WHERE - reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.00 sec) Mathias Selon Cory Robin [EMAIL PROTECTED]: I'm trying to return all records between two dates.. The fields are datetime fields... Which is better? The following or using BETWEEN? (A little lost here) SELECT * FROM passengers WHERE reservation_date_time = '2005-01-01 12:10:00' AND reservation_date_time = '2005-05-01 12:10:00'; -- 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: change data prefix query
Hi,
thsi can be a solution, there are others :
mysql select substring(id,1,2), substring(id,3,length(id)) from mytable;
+---++
| substring(id,1,2) | substring(id,3,length(id)) |
+---++
| UP| 05000 |
| UP| 05001 |
| UP| 05002 |
| UP| 05003 |
| UP| 05004 |
+---++
5 rows in set (0.01 sec)
mysql
mysql
mysql update mytable set id=concat('EN',substring(id,3,length(id)));
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql select * from mytable;
+-+
| id |
+-+
| EN05000 |
| EN05001 |
| EN05002 |
| EN05003 |
| EN05004 |
+-+
5 rows in set (0.00 sec)
Mathias
Selon Scott Purcell [EMAIL PROTECTED]:
Hello,
I have a table that has a varchar column in which I need to change a prefix
for all records. Currently there are about 500 records and I did not want to
do this by hand. It looks like this:
[data here ]
UP05000
UP05001
UP05002
UP05003
The identifier has now changed to EN so each records needs to be
EN05000
EN05001
EN05002
etc.
Can this be done with a query syntax? Or do I need to do this manually.
Thanks,
Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
Hi Doug, with a desc index on stuffed_date, an optimiezd table, the query runs in : mysql select * from stuff order by stuffed_date desc limit 18,10; +---+--+ | id| stuffed_date | +---+--+ | 88233 | 2005-07-08 | | 88228 | 2005-07-08 | | 88218 | 2005-07-08 | | 88198 | 2005-07-08 | | 88153 | 2005-07-08 | | 88148 | 2005-07-08 | | 88138 | 2005-07-08 | | 88118 | 2005-07-08 | | 88078 | 2005-07-08 | | 87993 | 2005-07-08 | +---+--+ 10 rows in set (0.17 sec) This is not 0s, buti don't think you can have it. A workaroud should be an auto_increment with no gap, then a select ... from stuff where id = 18 limit 10, hoping an index rang scan, for a covering index. Mathias Selon Doug V [EMAIL PROTECTED]: Hi, I have tried to simply the problem and it exists without any JOINs. have you given the query ? SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 18, 10 - .43 sec SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 - .0007 sec have you described your tables ? stuffed_date is INDEXed have your given the size of each table ? The table is about 200k rows. have you list the indexes ? stuff table has several indices, including 'id' and 'stuffed_date'. have you specify the storage type ? MYISAM In your followup message, you mention reverse sorting the query. I imagine on the application side I would need to reverse sort again to get the correct order. Are there any other ways to speed up such a query? Thanks. -- 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: noob question
Hi, look at this : mysql SET AUTOCOMMIT=0; mysql start transaction; mysql insert into inno values(2); mysql select * from inno; +--+ | t| +--+ |1 | |2 | +--+ 2 rows in set (0.00 sec) mysql rollback; mysql select * from inno; +--+ | t| +--+ |1 | +--+ 1 row in set (0.00 sec) more at http://dev.mysql.com/doc/mysql/en/commit.html Mathias Selon Digvijoy Chatterjee [EMAIL PROTECTED]: Thank You for the quick reply , now i wanted to create INNoDb table such that i could rollback my changes ,but here i am as I issue a rollback command ; nothing happens...is there some thing like autocommit on...or rather how do i alter standard settings of mysql client... MY MAIN QUESTION is : HOW DO I COMMIT AND ROLLBACK Thanks and Regards Digz On Mon, 2005-06-06 at 19:39, Digvijoy Chatterjee wrote: How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. Thanks DIgz *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** Aut disce Aut Discede Aut Vincere Aut Mori Either learn or leave Either conquer or die [EMAIL PROTECTED] #4043 *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** -- 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: mysql UNION
did'n arrive. re-submitted -- sorry
Selon [EMAIL PROTECTED]:
Hi,
If we forget the first method which i mis-adviced, i can give a third which
is
generic.
suppose that you have an indexed type column on each table (what i did).
You
can work with 3 variables. If they are different, you query for a join, if
they
are equal, you transform the join to a simple query.
The only condition is to add a where clause a the column type which will
retreive empty set for the non selected conditions.
Example :!
set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where
[EMAIL PROTECTED];
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
When you have only one value, the same query gives :
mysql set @cat1='news'; set @cat2='news'; set @cat3='news';
Query OK, 0 rows affected (0.00 sec)
here the 3 variables are the same, so 2 queries will find an empty set.
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where
[EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
performance will not be affected since the index will be used for non used
tables.
Hope that helps :o)
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
The first method is horribly inefficient (and requires mysql 4.1+).
It reads all 3 tables, unions the resulting rows, checks for (and
removes) duplicate rows, then finally throws away roughly 2/3 of the
results (the rows from the 2 unwanted tables. Compare that to the
simple query which only addresses the 1 desired table. Mathias is
aware of this, which is why he gives the second method. It creates
the simple, one-table query using the value of $cat to choose which
table.
The big problem here is that neither of these methods actually do what
you asked for. That is, neither works if $cat is not set. With both
methods, you will get no rows unless $cat is set. In fact, the second
method will give a syntax eror, as there will be no table name in the
FROM clause.
Now, I never said this couldn't be done in SQL. Assuming $cat is
already set, the statement in $sql below should do what you asked for:
$sql = SELECT id, 'news' AS type, FROM news
Re: MySQL (SQL) Newbie.. Need help with a Query
resubmitted Selon [EMAIL PROTECTED]: hi, that's the same. If you use between, mysql do the rest for you : mysql explain SELECT * FROM passengers WHERE - reservation_date_time = '2005-01-01 12:10:00' - AND reservation_date_time = '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.01 sec) mysql explain SELECT * FROM passengers WHERE - reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.00 sec) Mathias Selon Cory Robin [EMAIL PROTECTED]: I'm trying to return all records between two dates.. The fields are datetime fields... Which is better? The following or using BETWEEN? (A little lost here) SELECT * FROM passengers WHERE reservation_date_time = '2005-01-01 12:10:00' AND reservation_date_time = '2005-05-01 12:10:00'; -- 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: mirroring oracle database
resubmitted Selon [EMAIL PROTECTED]: Hi, what is tour oracle version ? such tool can be done easily if you put your oracle database in archivelog. Be carrefull to datatypes and create your mysql database with innodb storage. Beginning the game, you can use LogMiner. A simple batch can extract the redo SQL statements and apply them to your mysql database. This will be another Heterogeneous DataGuard architecture. Why not if you have not stored procedures, triggers, views ... in your oracle database. This will surprise me if you answer me i haven't. Since it's a test like, you can install mysql v5 which supports those concepts. A+ Mathias Selon Edward Peschko [EMAIL PROTECTED]: On Sun, Jun 05, 2005 at 04:41:16PM -0700, sol beach wrote: IMO, you have much more a lively imagination than realistic, in depth technical knowlege in either MYSQL or Oracle. Current production MYSQL does NOT have stored procedures. Current production mysql doesn't, but current development does (5.02). Given that this is something that is coming online about 6 months down the fly, and is a direction that we are thinking about moving, and given how much that such an effort would save you - and given the fact that all the data in question is being backed up in an oracle database, as far as I can see, the risk is minor and the rewards major. All it really has to do is keep data for a minor interval (say, a day). Then it can be synced with the oracle database in a batch job. I say its worth a shot. If its not doable now, its perhaps doable in 6 months. And some people agree with me apparently: http://www.convert-in.com/ora2sql.htm which I was thinking about reverse engineering to an extent as a starting point. Thanks for the vote of confidence btw, and the elegent, almost statesman-way that you expressed it.. But seriously, why the testy response? Are you affiliated in any way with oracle? Isn't the whole point of mysql to ultimately provide a RDBMS that can be used instead of DB2 or Oracle anyways? And does anybody have helpful, real, experience along these lines that they'd like to share rather than just opinions? Ed -- 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: Empty database name error
resubmitted Selon [EMAIL PROTECTED]: variable basedir else change a param. in what you have as file, stop start and see. Mathias Selon Ed Kasky [EMAIL PROTECTED]: I added the line to the configuration file and removed --log-error= line from startup line but it still creates the /usr/local/mysql/var/yoda2.err as well as /var/log/mysql/error.log I am assuming it is reading from my.cnf as I get the following when I run mysqladmin variables log_error | /var/log/mysql/error.log I also checked for possible duplicate my.cnf files but there is only one. Is there another way to check to be sure it's reading the configuration file? At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -= Unfortunately I could give suggestion only about your second question. Is it possible that /var/log/mysql/error.log created by mysqld_safe (you're specifying it with --log-error command line option) and /usr/local/mysql/var/yuda2.err is created by mysqld process if it founds problems before applying location of error log to it's internal variable? Specify log-error = /var/log/mysql/error.log in your configuration file, and check that MySQL Server actually reads this file. Ed Kasky wrote: Hello there - I have a couple of questions regarding a new install of MySql 4.1.12 on RH 7.2. Being new to this list, I sure do hope this hasn't been covered before. I have scrubbed Google and searched the archives for this list but can't find an explanation or a solution to 2 issues: 1. I get the following error when starting the daemon: 050605 7:08:51 [Warning] Found an entry in the 'db' table with empty database name; Skipped Is this something that should be fixed and if so, how does one go about it? 2. I have set the error log to /var/log/mysql/error.log in the init script: LOG_ERROR=/var/log/mysql/error.log $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file --log-error=$LOG_ERROR However, I am still getting 2 error logs: What I am assuming is the default /usr/local/mysql/var/yoda2.err and the one specified - /var/log/mysql/error.log Is this expected behavior? Is there another place aside from /etc/my.cnf that I might look? Thanks in advance for any tips and/or suggestions. Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (116 of 975): It had long since come to my attention that people of accomplishment rarely sat back and let things happen to them. They went out and happened to things. - Elinor Smith -- 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] . . . . . . . . . . . . . . . . . . Randomly Generated Quote (203 of 975): To climb steep hills requires slow pace at first. - William Shakespeare -- 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: mirroring oracle database
Hi, what is tour oracle version ? such tool can be done easily if you put your oracle database in archivelog. Be carrefull to datatypes and create your mysql database with innodb storage. Beginning the game, you can use LogMiner. A simple batch can extract the redo SQL statements and apply them to your mysql database. This will be another Heterogeneous DataGuard architecture. Why not if you have not stored procedures, triggers, views ... in your oracle database. This will surprise me if you answer me i haven't. Since it's a test like, you can install mysql v5 which supports those concepts. A+ Mathias Selon Edward Peschko [EMAIL PROTECTED]: On Sun, Jun 05, 2005 at 04:41:16PM -0700, sol beach wrote: IMO, you have much more a lively imagination than realistic, in depth technical knowlege in either MYSQL or Oracle. Current production MYSQL does NOT have stored procedures. Current production mysql doesn't, but current development does (5.02). Given that this is something that is coming online about 6 months down the fly, and is a direction that we are thinking about moving, and given how much that such an effort would save you - and given the fact that all the data in question is being backed up in an oracle database, as far as I can see, the risk is minor and the rewards major. All it really has to do is keep data for a minor interval (say, a day). Then it can be synced with the oracle database in a batch job. I say its worth a shot. If its not doable now, its perhaps doable in 6 months. And some people agree with me apparently: http://www.convert-in.com/ora2sql.htm which I was thinking about reverse engineering to an extent as a starting point. Thanks for the vote of confidence btw, and the elegent, almost statesman-way that you expressed it.. But seriously, why the testy response? Are you affiliated in any way with oracle? Isn't the whole point of mysql to ultimately provide a RDBMS that can be used instead of DB2 or Oracle anyways? And does anybody have helpful, real, experience along these lines that they'd like to share rather than just opinions? Ed -- 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: Access denied/password change
hi , see -skip-grant-tables in dev.mysql.com/doc Mathias Selon Seena Blace [EMAIL PROTECTED]: Is there any way I can change passwd of database without knowing the administrator . I'm unable to get into the mysql prompt due to access denied message.I want to change passwd.How to do this? After change passwd do you think we need to grant some priviledges? thanks Anoop kumar V [EMAIL PROTECTED] wrote: The reason i sthat you have not provided authorisation privileges to the database or to the tables within the database for that user... Very often we think granting all to the database is enough to make our app work with a particular user - this may be true for some databases - but in mysql you must do a grant all on your_database.* for that user - you must explicitly authorise the user for each table (using * or each table name) within the database. Hope that helps, Anoop On 6/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote:Hello. See: http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/resetting-permissions.html Seena Blace [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 17 lines --] Hi, I have been noticing following error when trying to connect mysql. ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) or ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What could be reason? Is there any way I can connect to database without changing passwd? thanks - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out! -- 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] -- Thanks and best regards, Anoop - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty database name error
variable basedir else change a param. in what you have as file, stop start and see. Mathias Selon Ed Kasky [EMAIL PROTECTED]: I added the line to the configuration file and removed --log-error= line from startup line but it still creates the /usr/local/mysql/var/yoda2.err as well as /var/log/mysql/error.log I am assuming it is reading from my.cnf as I get the following when I run mysqladmin variables log_error | /var/log/mysql/error.log I also checked for possible duplicate my.cnf files but there is only one. Is there another way to check to be sure it's reading the configuration file? At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -= Unfortunately I could give suggestion only about your second question. Is it possible that /var/log/mysql/error.log created by mysqld_safe (you're specifying it with --log-error command line option) and /usr/local/mysql/var/yuda2.err is created by mysqld process if it founds problems before applying location of error log to it's internal variable? Specify log-error = /var/log/mysql/error.log in your configuration file, and check that MySQL Server actually reads this file. Ed Kasky wrote: Hello there - I have a couple of questions regarding a new install of MySql 4.1.12 on RH 7.2. Being new to this list, I sure do hope this hasn't been covered before. I have scrubbed Google and searched the archives for this list but can't find an explanation or a solution to 2 issues: 1. I get the following error when starting the daemon: 050605 7:08:51 [Warning] Found an entry in the 'db' table with empty database name; Skipped Is this something that should be fixed and if so, how does one go about it? 2. I have set the error log to /var/log/mysql/error.log in the init script: LOG_ERROR=/var/log/mysql/error.log $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file --log-error=$LOG_ERROR However, I am still getting 2 error logs: What I am assuming is the default /usr/local/mysql/var/yoda2.err and the one specified - /var/log/mysql/error.log Is this expected behavior? Is there another place aside from /etc/my.cnf that I might look? Thanks in advance for any tips and/or suggestions. Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (116 of 975): It had long since come to my attention that people of accomplishment rarely sat back and let things happen to them. They went out and happened to things. - Elinor Smith -- 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] . . . . . . . . . . . . . . . . . . Randomly Generated Quote (203 of 975): To climb steep hills requires slow pace at first. - William Shakespeare -- 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: mysql UNION
Hi,
If we forget the first method which i mis-adviced, i can give a third which is
generic.
suppose that you have an indexed type column on each table (what i did). You
can work with 3 variables. If they are different, you query for a join, if they
are equal, you transform the join to a simple query.
The only condition is to add a where clause a the column type which will
retreive empty set for the non selected conditions.
Example :!
set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where [EMAIL
PROTECTED];
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
When you have only one value, the same query gives :
mysql set @cat1='news'; set @cat2='news'; set @cat3='news';
Query OK, 0 rows affected (0.00 sec)
here the 3 variables are the same, so 2 queries will find an empty set.
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where [EMAIL
PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
performance will not be affected since the index will be used for non used
tables.
Hope that helps :o)
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
The first method is horribly inefficient (and requires mysql 4.1+).
It reads all 3 tables, unions the resulting rows, checks for (and
removes) duplicate rows, then finally throws away roughly 2/3 of the
results (the rows from the 2 unwanted tables. Compare that to the
simple query which only addresses the 1 desired table. Mathias is
aware of this, which is why he gives the second method. It creates
the simple, one-table query using the value of $cat to choose which
table.
The big problem here is that neither of these methods actually do what
you asked for. That is, neither works if $cat is not set. With both
methods, you will get no rows unless $cat is set. In fact, the second
method will give a syntax eror, as there will be no table name in the
FROM clause.
Now, I never said this couldn't be done in SQL. Assuming $cat is
already set, the statement in $sql below should do what you asked for:
$sql = SELECT id, 'news' AS type, FROM news
WHERE ($cat = '' OR $cat = 'news')
UNION
SELECT id, 'faq' AS type, FROM faq
WHERE ($cat = '' OR $cat = 'faq')
UNION
SELECT id, 'forum' AS type, FROM
Re: mysql UNION
The second method is dynamic sql with prepare statement from string.
It's better for the reason that the query is generated to retrieve data from
just one table (not an union which implies 3 tables).
The day your tables will be huge, i'm sure you will use the second method.
two crazy people can find a method because only one hand can't aplaude !
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Hi, your second method is probably a little too confusing (advanced) for
me to understand.
I used your first method which works fine.. thanks for the crazy stuff,
somtimes you need two crazy people to come up with a solution ;)
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which works just fine and selects everything from all 3 tables.. but
say i want to make a condition to only select from either 'faq' ,
'news' or 'forum' how can i do this?
example, if a user visits a link suck as: page.php?cat=faq it will
only select from 'faq' .. is this possible to do right in the query?
when there is no ?cat= then all three selects run.
makes sense? i am stuck on this for a few days already.
thanks.
Why don't you do this in your app? If cat is set, issue the
appropriate single-table query, otherwise issue the union. Surely
that would be simpler than trying to build one multi-purpose query.
Michael
I was hoping i could do some crazy thing like WHERE type = 'faq' so i
can do it all from one block of code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
Hi, i and i think all people will think the same : i can't help. why ? have you described your tables ? have your given the size of each table ? have you list the indexes ? have you specify the storage type ? and have you given the query ? if you're looking for just a theoritical response, docs.mysql.com can give it. and sorry, this is not an ofense . Mathias Selon Doug V [EMAIL PROTECTED]: When I do a SELECT using STRAIGHT JOIN against multiple tables where the main table has about 200k rows, it is very fast retrieving the latest rows, ie LIMIT 0, 10, but extremely slow retrieving older rows, for example, LIMIT 18 , 10. Doing an EXPLAIN shows that no filesort or temporary table is being used. When I do the SELECT without the STRAIGHT JOIN, it does do a filesort and is a little bit faster. Is there anyway to speed such a query up? Thanks. -- 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: LOAD DATA INFILE - what is the path to file?
Hi, load data infile tries to load data from specified directory on the server. if your file is on the client, try LOAD DATA LOCAL and verify the parameter --enable-local-infile more details are in http://dev.mysql.com/doc/mysql/en/load-data-local.html about data on the web server. Nota bene : *** D:\perror 13 OS error code 13: Permission denied You have an os permission eroor. Mathias Selon Chris [EMAIL PROTECTED]: I have been using LOAD DATA INFILE to load an ASCII data file into my database. The datafile is uploaded to the server temp area and the name of the file is passed to LOAD DATA INFILE query like: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I now want to load data using LOAD DATA INFILE from a data file located within my http_public directory. I can create a path to the file from my DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this error: (NOTE: I set the file permissions to 777) Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) Now if I just create a query like: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I get this error File './my_database_name/datafile.txt' not found (Errcode: 2) Which seems to tell me that LOAD DATA INFILE is looking for my data file in a location that is outside my hosting account. I just have an account with a shared hosting service provider. So how would I specify a path to a file that is outside the directory where my database is located? OBSERVATION: It appears the tmp directory must be in the database path because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE. Thanks for replies, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
But i give you a suggestion (theoritical) : if data are reverse sorted , LIMIT 18, 10 will be LIMIt 2, 10 who knows ? Mathias Selon [EMAIL PROTECTED]: Hi, i and i think all people will think the same : i can't help. why ? have you described your tables ? have your given the size of each table ? have you list the indexes ? have you specify the storage type ? and have you given the query ? if you're looking for just a theoritical response, docs.mysql.com can give it. and sorry, this is not an ofense . Mathias Selon Doug V [EMAIL PROTECTED]: When I do a SELECT using STRAIGHT JOIN against multiple tables where the main table has about 200k rows, it is very fast retrieving the latest rows, ie LIMIT 0, 10, but extremely slow retrieving older rows, for example, LIMIT 18 , 10. Doing an EXPLAIN shows that no filesort or temporary table is being used. When I do the SELECT without the STRAIGHT JOIN, it does do a filesort and is a little bit faster. Is there anyway to speed such a query up? Thanks. -- 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: mysql UNION
I agree with you,
just see that if ..
sql = SELECT id, '$cat' AS type, ... FROM $cat;
is exactly what is done in prepare statement.
dynamic sql is better than application level statement preparation, when you use
stored procedure. but since this is the a habit in mysql, i'll keep this for
oracle, and other rdbms.
P.S. haven't spent days to help. It is easy. And since it was a week-end :o)
Mathias
Selon Michael Stassen [EMAIL PROTECTED]:
Sebastian wrote:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which works just fine and selects everything from all 3 tables.. but
say i want to make a condition to only select from either 'faq' ,
'news' or 'forum' how can i do this?
example, if a user visits a link suck as: page.php?cat=faq it will
only select from 'faq' .. is this possible to do right in the query?
when there is no ?cat= then all three selects run.
makes sense? i am stuck on this for a few days already.
thanks.
Why don't you do this in your app? If cat is set, issue the
appropriate single-table query, otherwise issue the union. Surely
that would be simpler than trying to build one multi-purpose query.
Michael
I was hoping i could do some crazy thing like WHERE type = 'faq' so i
can do it all from one block of code.
Frankly, I don't see the benefit of this. What is the payoff? php was
designed for this sort of conditional execution, sql wasn't. You've
spent several days trying to find a way to do this in sql. How long
would it have taken you to write the if...else... statement in php?
Let's see:
if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
$sql = SELECT id, '$cat' AS type, ... FROM $cat;
}
else
{
$sql = SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum;
}
So, why spend days trying to come up with complicated, ugly sql when it
can be done in 5 minutes with simple, easy-to-read php?
Michael
--
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 find random records in a subset?
select LIMIT 50; mathias Selon Brian Dunning [EMAIL PROTECTED]: I am using a routine to find 50 random records in a large MySQL database (about a million records) where I generate a list of 50 random unique ID's, and then use MySQL's in command to find them. I can't use order by rand() due to its performance hit. But I have to take it one more step: I want to first limit my found set to those matching a different search criteria, and then find 50 of those. Anyone? Can this be done all within MySQL, or is it going to require some humongo PHP arrays? -- 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: Design of a Client-side MySQL Java Load Balancer
image ? you're modifying row n, node x wants to modify it, you need a rollback segment for that ! which value node x will read. i'm considering every DML is a transaction in an RDBMS, so when you say that you don't use transactions, you miss something. about load balancing, how do you track long operations and decide to migrate a transaction to another node ? have you any benchmarks with say 4 nodes , 100 transactions terminating in different times with a a protocol making a first node crash, so a seconf, so a restart of one of them, during the 100 transactions, and each node load ? Selon Kevin Burton [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi, i think that client load-balacer are more Dispatchers than real load balancer. load balancing in the database side takes care to number of connections, but also node load. So thisis more real. But this issue is difficult. No... you're making assumptions. With the two-phase protocol I developed the nodes cooperate and distribute load and connections. They also handle failover. Simply put I can do a better job than hardware balancers because I already KNOW what MySQL can do. Most load balancers are dumb. even for oracle with 9iRAC and 10gRAC, load balancing is not completely controled. you speak abot load balancing and introduce also the failover notion, which isnot a load balancing concept. Fail over is difficult because controling it implies that every node must have the image before of every transaction. Image? Failover isn't a load balancing concept? Not according to our hardware vendor :) With cache fusion, ora cle RAC gives a solution, but assumes failover only fo select statements. All DML statements are lost if a node is lost. The DML situation here is a tough one. For SELECTS I have no problem with failover. For DML I would have no problem unless you're in a transaction. We don't use transaction and I think they're evil anyway. Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html 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]
Re: mysql UNION
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
Sebastian wrote:
i have a query with 3 union selects:
SELECT id, 'news' AS type, FROM news
UNION
SELECT id, 'faq' AS type, FROM faq
UNION
SELECT id, 'forum' AS type, FROM forum
which works just fine and selects everything from all 3 tables.. but
say i want to make a condition to only select from either 'faq' ,
'news' or 'forum' how can i do this?
example, if a user visits a link suck as: page.php?cat=faq it will
only select from 'faq' .. is this possible to do right in the query?
when there is no ?cat= then all three selects run.
makes sense? i am stuck on this for a few days already.
thanks.
Why don't you do this in your app? If cat is set, issue the
appropriate single-table query, otherwise issue the union. Surely
that would be simpler than trying to build one multi-purpose query.
Michael
I was hoping i could do some crazy thing like WHERE type = 'faq' so i
can do it all from one block of code.
--
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: max_seeks_for_key in InnoDB
Hi, you can use a hint to force specific index usage : http://lib.hutech.edu.vn/ebookonline/ebook1/0596003064/hpmysql-CHP-5-SECT-4.html But this is not a good idea since data change and index selectivity can become bad. Also, if the index scan + the table scan is bigger than a full table scan, even you will prefer FTS. So, according to selectivity, usage of an index can be a very bad idea. Thsi depends on how many rows your query retreives among the count(*) of the table. Mathias Selon Bob O'Neill [EMAIL PROTECTED]: I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
Hi, Status; gives your own session id. To be combined to show innodb status (ksh or perl). Mathias Selon Frank Schröder [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Frank Schröder [EMAIL PROTECTED] wrote on 05/31/2005 03:18:11 AM: Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank Have you looked at the results of SHOW INNODB STATUS; ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yes, as you can see from my original post I'm actually trying to figure out how to do this *without* SHOW INNODB STATUS as this reports the last FK failure for the entire engine and not just my session - at least that's how I interpret the documentation. The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. I have a hard time believing that so I figure that I just haven't figured out how to get to it. I just didn't think that it was so hard. -- Frank -- 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: Select MAX(column1,column2)
Hi Scott, you may be int his case : http://bugs.php.net/bug.php?id=32882 can't reproduce it because of env lack Mathias Selon Scott Klarenbach [EMAIL PROTECTED]: You guys have been so helpful with this, I'm hoping that I can ask for one more favor... The reason I needed the greatest(max()) functionality, was to run the following query...I can make it work from the command line, but everytime I run it from PHP, the MySQL service shuts down, and needs to be restarted manually. I'm calling a stored procedure 'selectAllRequests' which is the following query: SELECT r.id, r.partNumber, r.OtherFields, functionGetHighestValue(r.partNumber, r.qty) AS 'highestValue' FROM request r WHERE r.deleted=0 ORDER BY highestValue DESC, r.dateSent DESC; the function I'm calling is as follows: CREATE FUNCTION `functionGetHighestValue`(`MPNParam` varchar(60), `qtyParam` DOUBLE(10,4)) RETURNS DOUBLE(10,4) BEGIN DECLARE dHighest DOUBLE(10,4) DEFAULT 0; SELECT GREATEST(MAX(i.distySellCost), MAX(i.originalCost), MAX(i.unitCost), MAX(i.unitSellCost))*qtyParam FROM inventory i WHERE i.MPN = 'MPNParam' AND i.status=1 INTO dHighest; RETURN dHighest; END| As I say, I can call this procedure from the command line and it works, but calling it from PHP results in the MySQL service crashing on my Windows 2003 server. I'm using PHP 5.0.4 and MySQL 5.0.4. Any help is appreciated. Thanks. On 5/27/05, Scott Klarenbach [EMAIL PROTECTED] wrote: select greatest(max(col1), max(col2), max(col3), max(col4)) from table works the best, as Keith pointed toward initially. Remember, I forgot to mention that I wanted the greatest for the whole table, not just for each rowso, 10, 12, 8 is not what I wanted...out of 10 2 3 5 4 8 1 12 7 i want 12. thanks again. On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design of a Client-side MySQL Java Load Balancer
Hi, i think that client load-balacer are more Dispatchers than real load balancer. load balancing in the database side takes care to number of connections, but also node load. So thisis more real. But this issue is difficult. even for oracle with 9iRAC and 10gRAC, load balancing is not completely controled. you speak abot load balancing and introduce also the failover notion, which isnot a load balancing concept. Fail over is difficult because controling it implies that every node must have the image before of every transaction. With cache fusion, oracle RAC gives a solution, but assumes failover only for select statements. All DML statements are lost if a node is lost. The mysql concept of clustering is different from the oracle one. Will oracle shares all in memory, mysql clusters share nothing. I'm studing this now, so can't give you more details, but i think that in future versions, all RDMBS constructors will control the two concepts in the database side. Mathias Selon Kevin Burton [EMAIL PROTECTED]: I'd love to get some feedback here: MySQL currently falls down by not providing a solution to transparent MySQL load balancing. There are some hardware solutions but these are expensive and difficult to configure. Also none of them provide any information about the current state of your MySQL configuration. For example they can't handle transparent query failover if a MySQL box fails. They also can't disconnect and reconnect to another host if the load grows too high. To that end I think it makes a lot of sense to have a MySQL client-side load balancer. This area is difficult to implement. There are a log of design issues. Also the issues WRT distributed connection management start to make the problem difficult. The other day I had a bit of an epiphany on this topic. http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find the biggest blobs
Hi, since reading blobs is not a simple action (heavy), you must store the size of every file in the table's structure. if you write with php, somthing like that filesize($binFile) gives you the column value for every insert When done, a simple order by filesize gives you what you want before beginning the blob read : SELECT bin_data, filetype, filename, filesize FROM tbl_Files order by filesize desc; look at http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=1 to have a php blob manipulation sample. other languages like perl, asp, ado, ... do the same. hope that helps Mathias Selon Roland Carlsson [EMAIL PROTECTED]: Hi! I've need to find the largest blobs in a table but I seem not to be able to figure out what it is. Could anyone please help me with this? Regards Roland -- 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: mysqldump : Character set '#33' is not a compiled character set
hi, look at : usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf in http://dev.mysql.com/doc/mysql/en/mysqldump.html add the port, protocol,password mathias Selon Gu Lei [EMAIL PROTECTED]: Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump
Hi, try stop server, cold copy to dev server, start on dev. mathias Selon ManojW [EMAIL PROTECTED]: Greetings, I took a dump of (pretty chunk) database, the output is close to 45G. I am trying to reload this dump file onto a development server but it's taking long time to load the database. Is their a faster way to load the data in? I am using plain and simple mysql dump.sql syntax on a Mysql 4.0.24 server. Thanks in advance! Cheers Manoj -- 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: LEFT JOIN changes order of results
hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? mathias Selon Scott Gifford [EMAIL PROTECTED]: Johan Höök [EMAIL PROTECTED] writes: Hi Scott, I think you've been lucky so far... As you're only ordering on listdate, which is the same for both homes in your example you might definitely get different results once you put in the limit. A basic thing about rdb's is that you must never make assumptions that it returns resultsets in the same order unless you specify what to order by, of course quite often you'll get it back in the same order but you must never bank on it. Hi Johan, I guess I have been lucky. [...] I guess you somehow have to include the mls_num in your second query to ensure that you get same resultset. I'm looking into adding mls_num into all queries to fix this problem, but it looks likely to make performance much worse. Here's MySQL's plan for a typical query: mysql EXPLAIN SELECT * FROM faar_homes WHERE zip = 48503 ORDER BY price DESC LIMIT 10 \G *** 1. row *** id: 1 select_type: SIMPLE table: faar_homes type: index possible_keys: zip key: price key_len: 4 ref: NULL rows: 5194 Extra: Using where 1 row in set (0.00 sec) When I add in mls_num, it uses a filesort: mysql EXPLAIN SELECT * FROM faar_homes WHERE zip = 48503 ORDER BY price DESC, mls_num LIMIT 10 \G *** 1. row *** id: 1 select_type: SIMPLE table: faar_homes type: ALL possible_keys: zip key: NULL key_len: NULL ref: NULL rows: 5194 Extra: Using where; Using filesort 1 row in set (0.00 sec) It seems that this fix will cause nearly all of my queries to use filesort. Any ideas for avoiding this? Thanks! --ScottG. -- 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: Need help in basic query
Hi,
Try just :
SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest
FROM isr2_aud_log WHERE
name_rec_type = 'Assignment' AND id_secr_rqst
='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF'
GROUP BY id_secr_rqst
;
++-+
| task_id| latest |
++-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
++-+
1 row in set (0.02 sec)
Mathias
Selon Anoop kumar V [EMAIL PROTECTED]:
Hi mysql-ers,
I need help in a basic query:
I have this table:
select * from isr2_aud_log where
id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
--and name_rec_type = 'Assignment'
order by id_secr_rqst, dt_aud_rec
++-+-+
| id_secr_rqst | dt_aud_rec | name_rec_type |
++-+-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
Submission |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
Exception Requested |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
Exception Resource |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 |
Director Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 |
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 |
Risk Assessment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 |
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 |
SERB Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
Assignment |
++-+-+
and i am using this query:
SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
t1.dt_aud_recAS date1
FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst =
t2.id_secr_rqst
AND t1.name_rec_type = 'Assignment' AND
t1.id_secr_rqst IN (
'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
GROUP BY t2.id_secr_rqst HAVING date1 = latest
What I expected to get is the id_secr_rqst which has the last name_rec_type
= 'Assignment'
In this case there is only one id_secr_rqst and it has the last
name_rec_type as 'Assignment'. But I do not seem to get consistent results.
As I am using an older version of mysql I do not have the liberty to use
subqueries and will have to do everything using joins only.
The problem I am facing is that this query only sometimes returns rows and
most of the time I get an empty result set. This table does not have any
primary keys.
Can somebody please point out what is the mistake I am doing - I think it
just needs a tweak here and there (I hope..)
Thanks,
Anoop
--
Thanks and best regards,
Anoop
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: View
NO Selon Jerry Swanson [EMAIL PROTECTED]: Does Mysql 4 supports views? -- 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: SELECT problem
Hi René, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon René Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS iddateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS idname - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- 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: LEFT JOIN changes order of results
you can also try to increase the value of the tmp_table_size variable. A+ Selon [EMAIL PROTECTED]: You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. you certainly know that one multi-column index is similar to a lot of multi-column others when desired columns are in the right position of columns used in the index. this may let you implement less than 40 indexes. Otherwise force mls_num in all indexes you create an add it in the queries that doesn't use it with an always true condition (nls_num =0 for example) Mathias Selon Scott Gifford [EMAIL PROTECTED]: [EMAIL PROTECTED] writes: hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? Hi mathias, mls_num is the primary key, so it does have its own index. I could create a multi-column index covering (zip,price,mls_num), but that was really just one example of many searches; there are about 10 fields that are commonly used for searches, and about 4 that are commonly sorted by, so creating all of those indexes would require 40 indexes, and that's if the searches only use one field. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
I say this : Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.11-nt-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql status -- mysql Ver 14.7 Distrib 4.1.11, for Win32 (ia32) Connection id: 1 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Using delimiter:; Server version: 4.1.11-nt-max-log Protocol version: 10 Connection: localhost via TCP/IP Server characterset:latin2 Db characterset:latin2 Client characterset:latin1 Conn. characterset:latin1 TCP port: 3306 Uptime: 12 hours 17 min 30 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 0 Queries per second avg: 0.000 -- mysql Connection id: 1 is yours. Mathias Selon Frank Schröder [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi, Status; gives your own session id. To be combined to show innodb status (ksh or perl). Mathias I'm not sure I understand. Are you saying that SHOW INNODB STATUS shows only the information of the current session or that I can supply a session id to the call? -- Frank -- 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: LEFT JOIN changes order of results
You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. you certainly know that one multi-column index is similar to a lot of multi-column others when desired columns are in the right position of columns used in the index. this may let you implement less than 40 indexes. Otherwise force mls_num in all indexes you create an add it in the queries that doesn't use it with an always true condition (nls_num =0 for example) Mathias Selon Scott Gifford [EMAIL PROTECTED]: [EMAIL PROTECTED] writes: hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? Hi mathias, mls_num is the primary key, so it does have its own index. I could create a multi-column index covering (zip,price,mls_num), but that was really just one example of many searches; there are about 10 fields that are commonly used for searches, and about 4 that are commonly sorted by, so creating all of those indexes would require 40 indexes, and that's if the searches only use one field. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import dump (4.0 4.1) and collation problem
hi, your config supports european charcters : mysql select * from tst; +--+---+ | a| txt | +--+---+ |1 | Ceci est un test en Français | |1 | se facilitará el conocimiento de la evolución del | +--+---+ 2 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) have you tried some inserts to see if it is an import problem, or just the client display ? Mathias Selon [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi, I recently upgraded from 4.0 to 4.1 because of the multilingual support. But importing the DB dump into the new 4.1 version all foreign characters (french, spanish, portoguese) have been replaced by ? character... I've been trying to understand the new collation feature but i seems quite difficult to setup. Anybody has already faced this problem ? Here's my current configuration: 1) my.cnf (only the mysqld section) [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 # Both location gets rotated by the cronjob. #log= /var/log/mysql.log log = /var/log/mysql/mysql.log basedir = /usr datadir = /db tmpdir = /tmp language= /usr/share/mysql/english 2) Output show variables like '%char%'; character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ 3) Out put show variables like '%coll%'; collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Thanks for any help Regards Roberto Jobet 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- 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: building php, using mysql for apache2
hi, have you installed php4-mysql ? look at http://www.coagul.org/article.php3?id_article=169 for example. Mathias Selon bruce [EMAIL PROTECTED]: hi... trying to build php4 with mysql4.1-12, for use in apache2. i have the following ./compile that works for php5. however, when i try to use it for php4, i get a msg, stating that it can't find the MySQL Headers... can anybody provide any pointers as far as what the Headers are for mysql, and where they'd be located? if i knew the files, i could do a search to find where they're located. -- ./configure --with-apxs2=/usr/sbin/apxs --with-mysql=/usr/bin/mysql_config -- thanks -bruce [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]
