dynamic sql in proc

[Bryan Cantwell]

I have the following proc... when I run it I get a response that says
"You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'NULL' at
line 1".

I just want a programatic way to upgrade db engine to innodb where I
don't know exactly what tables exist...


DELIMITER $$

DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$
CREATE PROCEDURE `MYISAMtoINNODB`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_name VARCHAR(255);
DECLARE cur1 CURSOR FOR select table_name from information_schema.tables
where table_schema='firescope' and table_type='BASE TABLE' and
engine='MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;
REPEAT
FETCH cur1 INTO table_name;
  SET @table_name=table_name;
IF NOT done THEN
 SET @stmt_text=CONCAT("ALTER TABLE ", @table_name, "  ENGINE =
InnoDB");
 PREPARE stmt FROM @stmt_text;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
  END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END $$

DELIMITER ;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Geographic math problem

[David T. Ashley]

On 6/28/07, M5 <[EMAIL PROTECTED]> wrote:


Not being very strong at math, I have a little problem that I'm not
sure how to solve. Maybe someone can help me.

Basically, given a point (latitude, longitude) and a radius (100
meters) (think circle), I need to compute an equivalent square: That
is, two points that would correspond to two corners of the square.

From:   51, -114100 meters
To: 51.005, -114.005NE corner
   49.995, -113.995SW corner

Now, the above is not really accurate, of course, since the earth is
spherical (well, at least most people think so), and I would like
this computation to run in MySQL query, e.g.:

UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude
= (*), sw_longitude = (*)

In the above table, there are already three columns with the centre
latitude and longitude and radius. Any ideas? Thanks.



The math of latitude and longitude ain't too bad.

Consulting a globe would help.

A degree of latitude is always the same size in terms of the distance along
the earth's surface.  However, a degree of longitude varies in size--longest
at the equator and shortest (actually zero) at the poles.  The defining
equations come from that.

Just a few notes:

a)Whatever equations you derive for the corners may break down if the area
includes either pole.  You will need to guard against that.

b)My gut tells me that you can come up with some very simple approximations
(sine of this times cosine of that or dimension of the square) that will
work so long as the dimensions of the square are much smaller than the
diameter of the earth and you're not working too close to the poles.
However, if you mark up a spherical surface (such as a basketball or
beachball), I think you'll see that the relationships if either of those
assumptions break down would have to go to higher-order equations and
wouldn't be so simple, even if they can be represented in closed form.

If you want the exact relationships (which I don't believe are in the URLs
cited), you should probably post to sci.math.

Dave.

Re: Geographic math problem

[mos]

At 03:11 PM 6/28/2007, M5 wrote:

Not being very strong at math, I have a little problem that I'm not
sure how to solve. Maybe someone can help me.

Basically, given a point (latitude, longitude) and a radius (100
meters) (think circle), I need to compute an equivalent square: That
is, two points that would correspond to two corners of the square.

From:   51, -114100 meters
To: 51.005, -114.005NE corner
49.995, -113.995SW corner

Now, the above is not really accurate, of course, since the earth is
spherical (well, at least most people think so), and I would like
this computation to run in MySQL query, e.g.:

UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude
= (*), sw_longitude = (*)

In the above table, there are already three columns with the centre
latitude and longitude and radius. Any ideas? Thanks.

...Rene


Rene,
  So you're trying to fit a square inside of a circle? See 
http://mathcentral.uregina.ca/QQ/database/QQ.09.04/bob1.html


Mike 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Geographic math problem

[Philip Hallstrom]

Not being very strong at math, I have a little problem that I'm not sure how 
to solve. Maybe someone can help me.


Basically, given a point (latitude, longitude) and a radius (100 meters) 
(think circle), I need to compute an equivalent square: That is, two points 
that would correspond to two corners of the square.


From:   51, -114100 meters
To: 51.005, -114.005NE corner
49.995, -113.995SW corner

Now, the above is not really accurate, of course, since the earth is 
spherical (well, at least most people think so), and I would like this 
computation to run in MySQL query, e.g.:


UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude = (*), 
sw_longitude = (*)


In the above table, there are already three columns with the centre latitude 
and longitude and radius. Any ideas? Thanks.


http://www.mathforum.com/library/drmath/view/51711.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Geographic math problem

[M5]

Not being very strong at math, I have a little problem that I'm not  
sure how to solve. Maybe someone can help me.


Basically, given a point (latitude, longitude) and a radius (100  
meters) (think circle), I need to compute an equivalent square: That  
is, two points that would correspond to two corners of the square.


From:   51, -114100 meters
To: 51.005, -114.005NE corner
49.995, -113.995SW corner

Now, the above is not really accurate, of course, since the earth is  
spherical (well, at least most people think so), and I would like  
this computation to run in MySQL query, e.g.:


UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude  
= (*), sw_longitude = (*)


In the above table, there are already three columns with the centre  
latitude and longitude and radius. Any ideas? Thanks.


...Rene

RE: about the username and hostname

[Jerry Schwartz]

If you are starting the MySQL command line client from a shortcut, then all
you have to do is right-click on the shortcut and examine its properties.
The entire command line will be there.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -Original Message-
> From: Weiqi Wang [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 28, 2007 10:27 AM
> To: mysql@lists.mysql.com
> Subject: about the username and hostname
>
>
> Dear everyone:
>
> I start mySQL by a shotcut in windowsXP so that I don't have
> to input my username, just password is required. That brings
> in a problem: I don't know my user name(I suppose it to be
> "root") and the server host, etc. Is there anyway I can find
> it out? (I suppose the server host is the localhost but I'm not sure)
>
> Thanks very much for any help!
>
>
>
>
> Best Regards,
>
>
> yours sincerely,
>
> Weiqi
>
> ==
> Weiqi Wang
> Hertford College
> Dep. of Engineering Science
> Oxford
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: select statement with variable for table_reference?

[Price, Randall]

(Sorry for such a long post... I thought others may benefit as well as
ED)


Ed,

Here is an example using a CURSOR and a TEMPORARY TABLE to do something
similar to what you want (I think!):

== CREATE TABLES ==

mysql> show create table mydatabases;
CREATE TABLE `mydatabases` (
  `database_id` int(11) NOT NULL auto_increment,
  `database_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`database_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


mysql> show create table mytables;
CREATE TABLE `mytables` (
  `table_id` int(11) NOT NULL auto_increment,
  `database_id` int(11) NOT NULL,
  `table_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`table_id`),
  KEY `FK_mytables_mydatabases` (`database_id`),
  CONSTRAINT `FK_mytables_mydatabases` FOREIGN KEY (`database_id`)
REFERENCES `mydatabases` (`database_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


mysql> show create table myrecords;
CREATE TABLE `myrecords` (
  `record_id` int(11) NOT NULL auto_increment,
  `table_id` int(11) NOT NULL,
  `record_id_in_table` int(11) NOT NULL,
  PRIMARY KEY  (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


== TEST DATA ==

mysql> select * from mydatabases;
+-+---+
| database_id | database_name |
+-+---+
|   1 | test  |
|   2 | test2 |
|   3 | test3 |
+-+---+
3 rows in set (0.00 sec)


mysql> select * from mytables;
+--+-++
| table_id | database_id | table_name |
+--+-++
|1 |   1 | Books  |
|2 |   1 | CDs|
|3 |   1 | DVDs   |
|4 |   1 | Records|
+--+-++
4 rows in set (0.00 sec)


mysql> select * from myrecords;
+---+--++
| record_id | table_id | record_id_in_table |
+---+--++
| 1 |1 |  1 |
| 2 |1 |  2 |
| 3 |1 |  3 |
| 4 |1 |  4 |
| 5 |1 |  5 |
| 6 |2 |  1 |
| 7 |2 |  2 |
| 8 |2 |  3 |
| 9 |2 |  4 |
|10 |3 |  1 |
|11 |3 |  2 |
|12 |3 |  3 |
|13 |3 |  4 |
|14 |3 |  5 |
|15 |3 |  6 |
+---+--++
15 rows in set (0.00 sec)


mysql> select * from myinventory;
+--+-+-+--+---+
| inventory_id | user_id | database_id | table_id | record_id |
+--+-+-+--+---+
|1 |   1 |   1 |1 | 1 |
|2 |   1 |   1 |1 | 2 |
|3 |   1 |   1 |2 | 1 |
|4 |   1 |   1 |3 | 2 |
|5 |   2 |   1 |1 | 3 |
|6 |   2 |   1 |1 | 4 |
|7 |   2 |   1 |2 | 4 |
|8 |   2 |   1 |3 | 3 |
+--+-+-+--+---+
8 rows in set (0.02 sec)


mysql> select * from books;
+-+---+
| book_id | book_name |
+-+---+
|   1 | MySQL Tutorial|
|   2 | Learning XML  |
|   3 | XML IE5   |
|   4 | Programming Visual Basis 2005 |
|   5 | Learning C#   |
+-+---+
5 rows in set (0.00 sec)


mysql> select * from cds;
+---+-+
| cd_id | cd_name |
+---+-+
| 1 | Pink Floyd Meddle   |
| 2 | Rush Chronicles |
| 3 | Led Zepplin IV  |
| 4 | Frank Marino & Mahogany Rush IV |
+---+-+
4 rows in set (0.00 sec)


mysql> select * from dvds;
++---+
| dvd_id | dvd_name  |
++---+
|  1 | Dances With Wolves|
|  2 | Pink Floyd Live at Pompei |
|  3 | Braveheart|
|  4 | Saving Private Ryan   |
|  5 | Ice Age   |
|  6 | Cars  |
++---+
6 rows in set (0.00 sec)


== STORED PROCEDURE ==

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_looping`$$

CREATE PROCEDURE

Group wise maximum

[Dave G]

I just posted a question with a subject of "Revised optimization question"
and did some more searching and found the my problem should be titled
"group-wise maximum".

I need the group-wise maximum of this query based on payload_time:

CREATE PROCEDURE `getElement`(IN id INT UNSIGNED,
  IN ptime DOUBLE,
  IN tid VARCHAR(255),
  IN exact_time TINYINT)
BEGIN
   IF(ptime < 1) THEN
  SELECT MAX(ROUND(payload_time,6)) FROM data__ProcessedDataFrames
WHERE test_id=tid INTO ptime;
   END IF;
   SELECT
  J.product_id,
  P.processed_id,
  MAX(ROUND(P.payload_time,6)) as payload_time,
  P.top_level_product_name,
  SUBSTR(
 BINARY(processed_data),
 FLOOR(J.product_offset/8)+1,
 CEIL(J.product_length/8)) as substring,
 (SELECT HEX(substring)) as raw_data,
 (SELECT toString(
substring,
round(char_length(raw_data)/2,0),
data_type,
(SELECT attribute_value FROM
   config__DataProductAttributes
  WHERE attribute_name='FormatString'
  AND config__DataProductAttributes.product_id=
J.product_id),
product_offset % 8,
(product_length+(product_offset % 8)) % 8,
product_length,
byte_order,
(SELECT attribute_value FROM
   config__DataProductAttributes
  WHERE attribute_name = 'ConvParams'
  AND config__DataProductAttributes.product_id =
J.product_id))) as converted_data,
 (SELECT enum_name FROM
config__DataProductEnumConversions
   WHERE product_id=J.product_id
   AND enum_value =converted_data) as enumerated_data,
 (SELECT metricTest(converted_data,
(SELECT xmlTestMetric FROM
   test__TestMetrics
  WHERE product_id = J.product_id))) as test_metric
  FROM data__ProcessedDataFrames
   P INNER JOIN
  (SELECT E.product_id,
  top_level_product_name,
  product_length,
  product_offset,
  data_type,
  byte_order
FROM display__DataProducts_in_Element
  E INNER JOIN config__DataProducts D ON
E.product_id=D.product_id
  WHERE E.element_id=id) J
   ON P.top_level_product_name=J.top_level_product_name
   WHERE P.test_id=tid
   AND payload_time <= ptime
   GROUP BY J.product_id ;
END;;

mysql> desc data__ProcessedDataFrames;
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra
 |
++--+--+-+-++
| processed_id   | int(10) unsigned | NO   | PRI | NULL|
auto_increment |
| top_level_product_name | varchar(255) | YES  | MUL | NULL|  
 |
| test_id| int(10) unsigned | YES  | MUL | NULL|  
 |
| payload_time   | double   | YES  | MUL | NULL|  
 |
| universal_time | double   | YES  | | NULL|  
 |
| processed_data | mediumblob   | YES  | | NULL|  
 |
++--+--+-+-++
6 rows in set (0.00 sec)

mysql>

This table can get quite large so I'm trying not to query on it twice to
get the MAX(payload_time) < ptime.  Obviously what I have is not the
group-wise maximum I was hoping for.

Dave G.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: about the username and hostname

[Eugene Mah]

Check the properties of your shortcut to see what
user name is specified in the command options

Eugene


Weiqi Wang wrote:
> Dear everyone:
>
> I start mySQL by a shotcut in windowsXP so that I don't have to input my 
> username, just password is required. That brings in a problem: I don't know 
> my user name(I suppose it to be "root") and the server host, etc. Is there 
> anyway I can find it out? (I suppose the server host is the localhost but I'm 
> not sure)
>
> Thanks very much for any help!
>   

-- 
-
Eugene Mah, M.Sc., DABR   [EMAIL PROTECTED]
Medical Physicist/Misplaced Canuck[EMAIL PROTECTED]
Department of Radiology   [EMAIL PROTECTED]
Medical University of South Carolina  "For I am a Bear of Very Little
Charleston, South Carolina Brain, and long words Bother
http://www.netcom.com/~eugenem/me."   Winnie the Pooh
http://radinfo.musc.edu/~eugenem/blog/
PGP KeyID = 0x1F9779FD, 0x319393F4
PGP keys available on request ICQ 3113529 O-
-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How do I change the Collation Variables?

[Albert E. Whale]

How do I change:

| collation_connection| latin1_swedish_ci
  |
| collation_database  | latin1_swedish_ci
  |
| collation_server| latin1_swedish_ci

to latin1_bin?

Is this in my.cnf?  or is this another way to do this?
-- 
Albert E. Whale, CHS CISA CISSP
Sr. Security, Network, Risk Assessment and Systems Consultant

ABS Computer Technology, Inc.  - Email,
Internet and Security Consultants
SPAMZapper  - No-JunkMail.com
 - *True Spam Elimination*.

Re: How to optimize this long query

[Andrew Hutchings]

Kwang Chin Lee wrote:
> Hello,
>  
> I have several tables storing item information, keyword (mainly for 
> searching), category and subcategory (also for searching). The query I am 
> using now is: 
> 
> SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i 
> LEFT JOIN iteminfo it ON i.id = it.id 
> LEFT JOIN itemkeyword ik ON i.id = ik.id 
> LEFT JOIN state st ON it.state = st.id 
> LEFT JOIN itemcategory ic ON i.id = ic.id 
> LEFT JOIN subcategory s ON ic.sid = s.id 
> LEFT JOIN catsubcat cs ON cs.sid = s.id 
> LEFT JOIN category c ON c.id = cs.cid 
> WHERE (((UCASE(i.english) LIKE UCASE('% bank %') OR UCASE(i.desceng) LIKE 
> UCASE('% bank %') OR UCASE(s.english) LIKE UCASE('% bank %') OR 
> UCASE(c.english) LIKE UCASE('% bank %') OR UCASE(it.street1) LIKE UCASE('% 
> bank %') OR UCASE(it.street2) LIKE UCASE('% bank %') OR UCASE(it.garden) LIKE 
> UCASE('% bank %'))) 
> OR ((UCASE(i.english) LIKE UCASE('bank %') OR UCASE(i.desceng) LIKE 
> UCASE('bank %') OR UCASE(s.english) LIKE UCASE('bank %') OR UCASE(c.english) 
> LIKE UCASE('bank %') OR UCASE(it.street1) LIKE UCASE('bank %') OR 
> UCASE(it.street2) LIKE UCASE('bank %') OR UCASE(it.garden) LIKE UCASE('bank 
> %'))) 
> OR ((UCASE(i.english) LIKE UCASE('% bank') OR UCASE(i.desceng) LIKE UCASE('% 
> bank') OR UCASE(s.english) LIKE UCASE('% bank') OR UCASE(c.english) LIKE 
> UCASE('% bank') OR UCASE(it.street1) LIKE UCASE('% bank') OR 
> UCASE(it.street2) LIKE UCASE('% bank') OR UCASE(it.garden) LIKE UCASE('% 
> bank'))) 
> OR (UCASE(ik.keyword) LIKE UCASE('%bank%'))) 
> AND i.duedate > 1182339575 GROUP BY it.iid ORDER BY i.credit DESC, it.hits 
> DESC, i.english LIMIT 0, 10; 
> 
> Here is the EXPLAIN table: 
> 
> id select_type table type possible_keys key key_len ref rows Extra 
> 1 SIMPLE i ALL NULL NULL NULL NULL 1820 Using where; Using temporary; Using 
> filesort 
> 1 SIMPLE it ref id id 8 item.i.id 19 
> 1 SIMPLE ik ref id id 8 item.i.id 19 
> 1 SIMPLE st eq_ref PRIMARY PRIMARY 2 item.it.state 1 
> 1 SIMPLE ic ref id id 8 item.i.id 19 
> 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 item.ic.sid 1 
> 1 SIMPLE cs ref sid sid 4 item.s.id 2 
> 1 SIMPLE c eq_ref PRIMARY PRIMARY 3 item.cs.cid 1 Using where 
> 
> Now I have the questions below: 
> 1) Is it possible to shorten the WHERE clause? 
> 2) Sometimes, the keyword that I use to search takes a long time ( over 6 
> seconds). What is the main problem causing this problem? 
> 3) If I would like to sort the data by the `category.english` (if the keyword 
> found in category english name) and then following by the other criteria, how 
> do I write the ORDER BY clause? 
> 
> Thank you very much for your help~
>  
> 22-06-2007 

To answer point 2:

Firstly, as previously stated by someone else you need to ditch the
UCASE, performing functions on indexes pretty much voids the index.
Secondly if you have a % before a word then you are going to see a
performance hit.  In your case a fulltext index and query might be more
appropriate (I don't know without seeing the schema). Thirdly you need
to make sure all the group by clauses are indexed, or it will have to
use a temporary table and filesort.

Although for optimum performance you may find your entire strategy
behind this needs rethinking.
-- 
Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/
Windows is the path to the darkside...Windows leads to Blue Screen. Blue
Screen leads to downtime. Downtime leads to suffering...I sense much
Windows in you...


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Revised optimization question

[Dave G]

I asked a question yesterday that was quite onerous, so I have been
working out some of the details today, and would like to re-post my
question under a different light.

I have a table called data__ProcessedDataFrames that can grow to be
gigantic, and I need to speed up the query for pulling the data out.

What I'm trying to avoid is doing multiple queries on that table because
it is so big.  In my query I have a WHERE statement that looks like:
 WHERE P.test_id=tid
 AND payload_time <= ptime

where P is data__ProcessedDataFrames and tid is the test_id I'm looking at
and ptime is the payload_time that I'm looking at.  The problem with it is
I don't always know the exact payload_time, just that I want the
MAX(payload_time) < ptime.  I can't seem to get it right.  If I put the
MAX aggregate on payload_time, it returns the max payload_time but not the
data associated with that max payload_time.  So essentially I'm trying to
force my query to return the row that is associated with this max
payload_time without doing another query on the table to get the exact
payload_time.  Here is the table structure for data__ProcessedDataFrames:

mysql> desc data__ProcessedDataFrames;
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra
 |
++--+--+-+-++
| processed_id   | int(10) unsigned | NO   | PRI | NULL|
auto_increment |
| top_level_product_name | varchar(255) | YES  | MUL | NULL|  
 |
| test_id| int(10) unsigned | YES  | MUL | NULL|  
 |
| payload_time   | double   | YES  | MUL | NULL|  
 |
| universal_time | double   | YES  | | NULL|  
 |
| processed_data | mediumblob   | YES  | | NULL|  
 |
++--+--+-+-++
6 rows in set (0.00 sec)

mysql>


Here is the SP:
CREATE PROCEDURE `getElement`(IN id INT UNSIGNED,
  IN ptime DOUBLE,
  IN tid VARCHAR(255),
  IN exact_time TINYINT)
BEGIN
   IF(ptime < 1) THEN
  SELECT MAX(ROUND(payload_time,6)) FROM data__ProcessedDataFrames
WHERE test_id=tid INTO ptime;
   END IF;
   SELECT
  J.product_id,
  P.processed_id,
  MAX(ROUND(P.payload_time,6)) as payload_time,
  P.top_level_product_name,
  SUBSTR(
 BINARY(processed_data),
 FLOOR(J.product_offset/8)+1,
 CEIL(J.product_length/8)) as substring,
 (SELECT HEX(substring)) as raw_data,
 (SELECT toString(
substring,
round(char_length(raw_data)/2,0),
data_type,
(SELECT attribute_value FROM
   config__DataProductAttributes
  WHERE attribute_name='FormatString'
  AND config__DataProductAttributes.product_id=
J.product_id),
product_offset % 8,
(product_length+(product_offset % 8)) % 8,
product_length,
byte_order,
(SELECT attribute_value FROM
   config__DataProductAttributes
  WHERE attribute_name = 'ConvParams'
  AND config__DataProductAttributes.product_id =
J.product_id))) as converted_data,
 (SELECT enum_name FROM
config__DataProductEnumConversions
   WHERE product_id=J.product_id
   AND enum_value =converted_data) as enumerated_data,
 (SELECT metricTest(converted_data,
(SELECT xmlTestMetric FROM
   test__TestMetrics
  WHERE product_id = J.product_id))) as test_metric
  FROM data__ProcessedDataFrames
   P INNER JOIN
  (SELECT E.product_id,
  top_level_product_name,
  product_length,
  product_offset,
  data_type,
  byte_order
FROM display__DataProducts_in_Element
  E INNER JOIN config__DataProducts D ON
E.product_id=D.product_id
  WHERE E.element_id=id) J
   ON P.top_level_product_name=J.top_level_product_name
   WHERE P.test_id=tid
   AND payload_time <= ptime
   GROUP BY J.product_id ;
END;;


You can see here at the end I did a GROUP BY on J.product_id (there can be
multible product_ids) and one of the return values is:
MAX(ROUND(P.payload_time,6)) as payload_time,

Well this max does not force the processed_data from that row to be
returned, I get processed_data from another row because of the GROUP BY

Any suggestions?

David Godsey


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To uns

FULLTEXT + InnoDB = grounded?

[Sven Schwyn]

Hello

I've seen the posts of Nov 9 last year concerning the slow but steady  
development of FULLTEXT indexes for InnoDB. Has this feature been  
dropped or is it still being worked on?


My background: I'm working on a Rails project which needs fulltext  
index search on the db layer. For now I can workaround with MyISAM,  
but if the feature is no longer on the list, then it would be better  
to choose another more cumbersome approach or switch the db entirely.


Many thanks for a short update.

Cheers,  -sven


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to order a string as integer

[David T. Ashley]

On 6/28/07, Magnus Borg <[EMAIL PROTECTED]> wrote:


Got strings in the format "integer+(char)^0..1", ex 3456, 2323, 2324A,
2324B, ...

I want to order them as they where integers, like:

   order by cast(STING as unsigned) desc

But when using that query resulting in error 1292. And the result are not
correct.

How can I remove the chars before i casting the string? And if possible
take
the chars in the order.
So that i get a result like: 1000, 1000A, 1000B, 900, 800, 800A.



The queries you plan to execute influence the database design--there is no
way around that.

I would redesign the database in one of three ways:

Possibility #1:  Separate the problematic columns into an integer field and
a character field, then "order by a, b".

Possibility #2:  Add multiple parallel columns (used only for queries).  For
example, the strings might be "800" and "1000", but the parallel integers
are 800 and 1000;  ordering by the integers would get the result you want,
whereas ordering by the strings would not.

Possibility #3:  Keep a single parallel column (probably an integer), mapped
such that the sort order is what you want.  For example, let's assume you
have an unusual sort order such that you want numbers with no letters
displayed first, then those suffixed by "C", then all others with letters in
order.  You could create a "BIGINT" assigned as follows:

a)Take the base part number, multiply by 40.

b)If there is no letter suffix, add nothing.

c)If there is a "C" suffix, add 1.

d)If there is an A suffix, add 2.

e)...

So, the part number "800C" would map to (800 * 40) + 1, the part number
"800A" would map to (800 * 40) + 2, etc.

Keep those parallel integers maintained each time you INSERT or UPDATE a
record.

Then just order by them.

#3 is by far the most powerful approach.  If you know in advance what the
ordering should be, then you can design the mapping from (part number) ->
(integers) to accommodate it.  (part number) -> (strings) is also possible
if you can make the strings coincide with one of the MySQL collation orders.

In general, even if you could manage to phrase the query you're describing,
it could never be efficient.  The reason is that MySQL can only keep indexes
in terms of the ordinal data types and sort orders that are built-in to give
you approximately O(log N) query time.  Even if you can do what you want, it
won't scale well to large tables.

You have to map the problem to the the ordinal data types that MySQL
supports (integer, string, float).

Dave.

How to order a string as integer

[Magnus Borg]

Hello list

Got strings in the format "integer+(char)^0..1", ex 3456, 2323, 2324A,
2324B, ...

I want to order them as they where integers, like: 

order by cast(STING as unsigned) desc

But when using that query resulting in error 1292. And the result are not
correct.

How can I remove the chars before i casting the string? And if possible take
the chars in the order.
So that i get a result like: 1000, 1000A, 1000B, 900, 800, 800A.

//M

PS
Sorry for spelling and grammar
DS

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.9.10/875 - Release Date: 2007-06-27
21:08
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LOAD DATA INFILE

[Ananda Kumar]

Or,
Load the data into  a temp , and create necessary index on the required
columns,
Write a stored proc to update columns in the original table with values from
the temp table based on key columns joins between both tables

regards
anandkl


On 6/28/07, mos <[EMAIL PROTECTED]> wrote:


At 01:08 PM 6/27/2007, you wrote:
>Hi,
>
>
>
>I have a question on LOAD DATA INFILE command.
>
>I have a table with 10 columns. Is it possible to update only few
>columns of this table using LOAD DATA INFILE?

No. As you found out  Load Data loads the specified columns and sets the
other columns to NULL. The only way around this is to write the current
column values that you want to save back out to the text file. Or load the
data into a second temporary table with just the columns you need, and
then
do an Update to the original table (assuming of course all of the data in
the text file are updates and not new rows otherwise you'll then have to
join the original table with the temp table to find the new rows then add
those to the original table.) Whew!

Mike
(If programming was easy, everyone would be doing it.)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: permissions on mysql db

[Ananda Kumar]

you mean at os

On 6/28/07, Rolando Edwards (DBA) <[EMAIL PROTECTED]> wrote:


check your Linux/Unix user privileges

*Ananda Kumar <[EMAIL PROTECTED]>* wrote:

Hi All,
I have created a user and grant all previlages on all db and the user can
connect from any machine with in the same doiman.

Now, when i login from a mysql client machine, i am able to see all the
databases, but when i login directly from the mysql database server,
I am able to see only the information_schema and test database.

Should i grant any more previlages for the user to see all the database ,
when connected directly from the mysql database server.

regards
anandkl


 --
Need a vacation? Get great deals to amazing places
on
Yahoo! Travel.

Re: about the username and hostname

[John Meyer]

Weiqi Wang wrote:
> Dear everyone:
>
> I start mySQL by a shotcut in windowsXP so that I don't have to input my 
> username, just password is required. That brings in a problem: I don't know 
> my user name(I suppose it to be "root") and the server host, etc. Is there 
> anyway I can find it out? (I suppose the server host is the localhost but I'm 
> not sure)
>
> Thanks very much for any help!
>
>
>
>
> Best Regards,
>
>
> yours sincerely,
>
> Weiqi 
>
>   


Okay, are you bringing up the client or the server?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

about the username and hostname

[Weiqi Wang]

Dear everyone:

I start mySQL by a shotcut in windowsXP so that I don't have to input my 
username, just password is required. That brings in a problem: I don't know my 
user name(I suppose it to be "root") and the server host, etc. Is there anyway 
I can find it out? (I suppose the server host is the localhost but I'm not sure)

Thanks very much for any help!




Best Regards,


yours sincerely,

Weiqi 

==
Weiqi Wang
Hertford College
Dep. of Engineering Science
Oxford

Re: LOAD DATA INFILE

[mos]

At 01:08 PM 6/27/2007, you wrote:

Hi,



I have a question on LOAD DATA INFILE command.

I have a table with 10 columns. Is it possible to update only few
columns of this table using LOAD DATA INFILE?


No. As you found out  Load Data loads the specified columns and sets the 
other columns to NULL. The only way around this is to write the current 
column values that you want to save back out to the text file. Or load the 
data into a second temporary table with just the columns you need, and then 
do an Update to the original table (assuming of course all of the data in 
the text file are updates and not new rows otherwise you'll then have to 
join the original table with the temp table to find the new rows then add 
those to the original table.) Whew!


Mike
(If programming was easy, everyone would be doing it.) 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Document archiving

[John Meyer]

Eddy D. Sanchez wrote:
> Thanks for your answer.
>
> I'm searching an opensource project (based on mysql obviously) that I
> can hack for my needs, but if I can't find anything, I must make one,
> my intention for technology is:
> -Java for application server and framework

Might I ask why you need Java per se.  Is it just because you want
platform independence?  If so, there are multiple ways to get to that. 
Nailing yourself down on a particular language will end up limiting your
field of options.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Document archiving

[John Meyer]

Eddy D. Sanchez wrote:
> Hello Everyone.
>
> I want to scan a large quantity of books and documents and store these
> like images inside or outside a database, I want use mysql, anyone
> have any experience with this kind of systems, can you suggest me an
> opensource solution ??

First question I would have for you is do you need instant (as opposed
to just want) retrieval  of that information over the computer, or do
you have them located in a library somewhere.  If it's the latter, you
may want to use something like mysql to just store key information about
those documents like title keywords etc and their location just to
reduce redundency.  I'd hate to think of the bill for all those manhours
scanning books even if they are, say, only 50 pages each.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Document archiving

[John Meyer]

David T. Ashley wrote:
>
> Also, I have to say this to be complete ...
>
> You were aware, of course, that nearly every modern copyright for books
> prohibits digitizing the book and using it in any kind of document
> retrieval
> system?  In fact, I believe a violation has occured even if it is scanned
> and the data is never used.
>
> I just had to say this.  I don't know how U.K. prisons are, but here
> in the
> U.S. they are full of large unpleasant men who have done bad things
> and may
> do more bad things ... to you, for example.
>
And the concept that he's using these on internal documents that his
company owns the copyright on didn't occur to you?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

find a particular record.

[Ananda Kumar]

Hi All,
I am doing this to move data from one table to other and getting below
error.
How can check this particular record at this row number 921888 in dats_tem
table.


insert into reports1.dats1 select * from dats_tem;

ERROR 1292 (22007): Incorrect datetime value: '-00-00 00:00:00' for
column 'P_LAST_SOLD_DATE' at row 921888