Eudora s filter

[hsv]

Amongst the common questions it is explained how to make Mac Eudora filter 
MySQL-list email based on the added header-line "List-ID". The explanation is  
just as good for Windows Eudora, no need to limit it to Macintosh.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

trigger-dumping

[hsv]

MYSQLDUMP.EXE (Ver 10.13 Distrib 5.5.8, for Win32 (x86)) has flags for 
trigger-dumping; the help that I downloaded for this version says it dumps 
triggers--but it does not. Now what?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Ordering by grouping

[hsv]

Once more I am surprised by the ordering that I get from 'GROUP BY'.

This defines the table of directors that have been on the board:

CREATE TABLE DIRECTOR
(   Chosen  DATE NOT NULL
,   Through DATE NOT NULL
,   MemberIDINTEGER REFERENCES MemberAddress (MemberID)
,   CONSTRAINT dpk PRIMARY KEY (Chosen, MemberID)
,   RankTINYINT REFERENCES MemberName (Rank)
)

This query, based also on a view ('offboard') that joins this table with 
name&address tables, lists the boards that arise from the table:

select "When", COUNT(givenname || ' ' || surname) AS directors, 
group_concat(givenname || ' ' || surname ORDER BY Surname) AS Board
FROM (select distinct chosen AS "When"
FROM director
UNION select distinct ADDDATE(through, 1)
FROM director
WHERE through < CURDATE()) as B JOIN offboard ON "When" between chosen 
and through
GROUP by "When"

It is only roughly, not completely, ordered by '"When"'. Why? When is 
'GROUP-BY' ordering complete?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: category with parentid

[hsv]

 2012/05/25 14:57 +0700, HaidarPesebe 
id | name   | parentid
--
1 | cat A  | 0
2 | cat B  | 0
3 | subcat A | 1
4 | subcat A | 1
5 | subncat B | 2
-

I want to display the result like this:

1. Cat A
  - Subcat A
  - Subcat A
2. CatB
  - Subcat B

If the degree of subordination is a number in your record, try function REPEAT 
(output concatenated to A or B), or LPAD.

**
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**



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

Re: category with parentid

[hsv]

 2012/05/28 12:54 +0700, HaidarPesebe 
select id,name from TABLE WHERE parentid='0'

and a second call to the same table as this;

select id,name from TABLE WHERE parentid='$id' (this $id is the result of
calling the first call TABLE)

Others have said, and I agree, you have a graph, and some graph algorithm is 
needed for the indenting that you seek.

You use some other programming language than MySQL for making up the queries.

In that language keep an array of arrays; your first query,
select id,name from TABLE WHERE parentid='0'
generates more id's. Make an array of them, and that array is the other array s 
first element. Make a new query,
select id,name from TABLE WHERE parentid in ($formerIDs),
and so on, each $formerID all the id's in the earlier query returned, until no 
ID is returned.

Then use the array-array, and hope that each id only once appears in it:
if parentid=0, indent 0;
if parentid found in first element, indent 1;
if parentid found in second element, indent 2;
...
You can do this with a generated CASE:
CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END
. This is in a REPEAT concatenated to your name, or LPAD with your name one of 
the arguments, say
SELECT id,
REPEAT(' ', CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END) || name, parentid FROM TABLE
.

If it is guaranteed that for each parentid there is at most one id, then all 
this becomes much simpler: it is an array of id's; the variable-comparison CASE 
is used.

**
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**



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

Re: Query help...

[hsv]

 2012/05/28 08:03 -0700, Don Wieland 
Any assistance would be appreciated. Thanks!


Maybe something like this:


SELECT usie.client_id, first_name, last_name, COUNT(anie.client_id)

FROM
(SELECT client_id, first_name, last_name, time_start
FROM tl_appt
JOIN tl_rooms USING(room_id)
JOIN tl_clients USING(client_id)
JOIN tl_users on USING(user_id)

WHERE
appt_id IS NOT NULL AND
time_start between '1293858000' and '1325393999' AND
location_id = '1' and
appt_status_id IN (3) and
user_id IN (506)

GROUP BY user_id, client_id) AS usie

LEFT JOIN

(SELECT client_id, time_start
FROM tl_appt
JOIN tl_rooms USING(room_id)
JOIN tl_clients USING(client_id)

WHERE
appt_id IS NOT NULL AND
location_id = '1' and
appt_status_id IN (3)) AS anie

ON usie.client_id = anie.client_id AND usie.time_start > anie.time_start
GROUP BY usie.client_id

**
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**



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

is it quiet out there?

[hsv]

**
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**



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

Re: Which Database when lot of insert / update queries to execute

[hsv]

 2012/06/15 18:14 +0900, Tsubasa Tanaka 
try to use `LOAD DATA INFILE' to import from CSV file.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

"Try" is the operative word: MySQL s character format is _like_ CSV, but not 
the same. The treatment of NULL is doubtless the biggest stumbling-block.


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

CREATE VIEW COMMENT ''

[hsv]

To endow CREATE VIEW with COMMENT would be splendid.


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

YEAR and time types

[hsv]

A director s term ends in a given year, but at no given time of year; depends 
on the yearly meeting.

I thought I would try YEAR to record it--but, in spite of 
http://dev.mysql.com/doc/refman/5.5/en/year.html , simply assigning NOW() to 
such a type does not work. There is also no implicit conversion to DATE. All in 
all, it behaves as a small integer, not a time type. For my end it is much less 
good than 'year-00-00', something already slightly obscure.


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

RE: YEAR and time types

[hsv]

 2012/07/23 11:54 -0700, Rick James 
mysql> SELECT YEAR(NOW());
+-+
| YEAR(NOW()) |
+-+
|2012 |
+-+

mysql> SELECT CONCAT('2012', '-01-01');
+--+
| CONCAT('2012', '-01-01') |
+--+
| 2012-01-01   |
+--+

To show that it acts like a DATE:
mysql> SELECT CONCAT('2012', '-01-01') - INTERVAL 1 day;
+---+
| CONCAT('2012', '-01-01') - INTERVAL 1 day |
+---+
| 2011-12-31|
+---+

No: I mean variables of type YEAR


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

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[hsv]

 2012/07/26 06:52 +0530, Dhaval Jaiswal 
SELECT * FROM test INTO OUTFILE '/home/test.csv' FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'

as above give your join condition before INTO OUTFILE.

Right: MySQL server writes into some directory where it is, not where MySQL 
client is. If less than a full path name is given, almost certainly the server 
will attempt to write into a directory to which it has no permission, and 
almost certainly also not into one that you want it to write into. If server 
and client run on separate machines with separate disks, there is no means 
through OUTFILE of there setting the output where the client is, only through 
client s standard output, where you get no choice of field separator, line 
separator, or field-quote character (there is none), although you can keep or 
skip the column names (-N for skipping them), and suppress the one-character 
escape character (-r), same as FIELDS ESCAPED BY ''. There is no means of 
skipping "NULL" or "\N" for nulls, which is not CSV format.

And if your MySQL is under Windows, be sure to read all instructions about 
entering full pathnames. It is best to avoid the backslash (\), because that is 
a C-escape introduced (along with much other C-stuff) into SQL s original PL1.


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

Re: YEAR and time types

[hsv]

 2012/07/24 13:07 -0700, Hassan Schroeder 
On Tue, Jul 24, 2012 at 10:36 AM, Roberta Jaskólski  wrote:

>  version(): 5.5.8-log
> @@sql_mode: 
> REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
>
> What is yours?

version  5.5.19
@@sql_mode is empty

Well, the answer to my original question is that MySQL is willind with a 
warning to "narrow" DATE to YEAR, but with my usual setting, with 
STRICT_TRANS_TABLES, the warning becomes an error. As for widening YEAR to 
DATE, MySQL never does it: with a warning it is willind to return the 0-date 
for an attempt at widening, but, with STRICT_TRANS_TABLES, as erst, the warning 
becomes an error. It would be better if MySQL were willind also to widen: if, 
say, the YEAR were 1967, it would be widened to 1967-00-00. Too bad.


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

Re: query help

[hsv]

 2012/07/31 15:02 -0700, Haluk Karamete 
So, in a case like this

shop.orders.32442
shop.orders.82000
shop.orders.34442

It would be the record whose source_recid  is shop.orders.82000. Why? Cause
82000 happens to be the largest integer.

Now, if they are always 5-digit-long integers, try ending with
ORDER BY SUBSTRING_INDEX(source_recid, '.', -1)


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

Re: DECIMAL datatype automatically makes blank become 0

[hsv]

 2012/08/04 23:14 -0400, Fred G 
I'm using MySQL 5.2 Workbench, and when I import a csv into MySQL, blank
values become 0 when I define the datatype to be a DECIMAL (in our case
(DECIMAL(12,2)). Since there is the potential for values to be 0, this
poses a problem for us. I initially wanted to make all the DECIMAL values
become VARCHAR(255), because I can still do inequality queries on this
column if it is a VARCHAR, and VARCHAR does not mind blanks. It keeps them
as is. 

MySQL does not do real CSV files, only its version of CSV-ish files. What do 
you mean "blank"? Is there really a character there, between separators, or 
nothing at all?

In any case, I guess you mean NULL in SQL terms. In that case, MySQL insists on 
seeing NULL on no escape character, or \N, where escape character is \. If this 
is LOAD DATA, a trick that another used is helpful here, making use of letting 
LOAD DATA store in a user variable, and using SET to make it either the number 
or NULL:

LOAD DATA  (..., @DN, )  SET decField = IF(@DN = '', NULL, @DN);


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

Re: help with correlated subquery

[hsv]

 2012/08/21 16:35 -0600, Larry Martell 
I am trying to write a query that selects from both a correlated
subquery and a table in the main query, and I'm having a lot of
trouble getting the proper row count. I'm sure this is very simple,
and I'm just missing it. I'll try and present a simple example. For
this example, there are 27 rows, organized like this:


mysql> select count(*), target_name_id, ep, wafer_id from data_cst
where target_name_id = 44 group by target_name_id, ep, wafer_id;
+--++--+--+
| count(*) | target_name_id | ep   | wafer_id |
+--++--+--+
|6 | 44 | 1,1  |   16 |
|3 | 44 | 1,1  |   17 |
|6 | 44 | 1,2  |   16 |
|3 | 44 | 1,2  |   17 |
|6 | 44 | 1,3  |   16 |
|3 | 44 | 1,3  |   17 |
+--++--+--+
6 rows in set (0.00 sec)

I need to get an average of a column grouped by target_name_id, ep as
well as the average of the averages grouped by target_name_id, ep,
wafer_id, and I also need the count of the rows in the target_name_id,
ep group. My query is getting the correct averages, but incorrect row
counts:

mysql> select count(*), target_name_id, ep, avg(bottom), avg(averages)
from (select avg(bottom) as averages, target_name_id as t, ep as e
from data_cst where target_name_id = 44 group by target_name_id, ep,
wafer_id) x, data_cst where target_name_id = t and ep = e group by
target_name_id, ep;
+--++--+-+-+
| count(*) | target_name_id | ep   | avg(bottom) | avg(averages)   |
+--++--+-+-+
|   18 | 44 | 1,1  |  21.8056667 |  21.8545833 |
|   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
|   18 | 44 | 1,3  | 349.763 | 349.75016665000 |
+--++--+-+-+
3 rows in set (0.01 sec)

The count for each row should be 9. What do I need in my count() to be
counting the right thing?

Your trouble lys in the joining; in effect, you are joining a row with wafer_id 
16 with a row with wafer_id 17, and also a row with wafer_id 17 with a row with 
wafer_id 16.

A further advantage to using the now standard form of joining, as Rick James 
bids you do, is that one can add further conditions to it:

select count(*), target_name_id, ep, avg(bottom), avg(averages)
from (select avg(bottom) as averages, target_name_id, ep
from data_cst
where target_name_id = 44
group by target_name_id, ep, wafer_id) x
JOIN data_cst
ON target_name_id = x.target_name_id and ep = x.ep and wafer_id < x.wafer_id
group by target_name_id, ep

The inequality, maybe, will give you what you want.


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

Re: JOIN, JOIN, JOIN

[hsv]

 2012/08/22 17:38 -0400, Shawn Green 
MySQL does not have a simple OUTER JOIN command (some RDBMSes call this a FULL 
OUTER JOIN). What we do have is the option to include the OUTER keyword into 
our LEFT or RIGHT joins. For example, both of these are acceptable:

LEFT OUTER JOIN
LEFT JOIN

Also, you need a space between "inner" and "join" as in INNER JOIN.

>If you want only the most restricitive criteria that match resultsets from 
>both select statements use INNER JOIN
>if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN

Again, MySQL does not have a plain OUTER JOIN. If you want a full Cartesian 
product of two tables, use the 'comma join' syntax with no criteria for 
matching the tables in the WHERE clause.

SELECT ... FROM tableA, tableB WHERE 

Syntax details are located here:
http://dev.mysql.com/doc/refman/5.5/en/join.html

which last is the same as
SELECT ... FROM tableA JOIN tableB WHERE 
; that is, if there is no join_condition that, too, becomes a cross-join.

 From the webpage:
< In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they 
can replace each other). In standard SQL, they are not equivalent. INNER JOIN 
is used with an ON clause, CROSS JOIN is used otherwise. >

That is, in MySQL neither INNER nor CROSS has any meaning. If there is no 
restriction (ON/USING/NATURAL) it is CROSS JOIN; otherwise, something else. As 
for OUTER, in MySQL it is a word that may appear between LEFT/RIGHT and JOIN; 
it is not really meaningful; as Shawn Green said, MySQL has no full outer join 
(but Oracle has!).

Therefore, in case of syntactical parsimony, whereto, it seems to me, MySQL is 
headed, one writes JOIN by itself with restrictions or not (for cross join), or 
after one of the words LEFT, RIGHT(, OUTER when it is implemented) with 
restrictions.

In case of syntactical security (the standard?), one always qualifys JOIN: with 
CROSS if one intends no restriction, else with INNER or OUTER, and qualifys the 
latter with LEFT or RIGHT if one intends other than full outer join. 


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

RE: Aggregate

[hsv]

 2012/09/04 11:43 -0700, Rick James 
You have DATETIME and TIMESTAMP.  Keep in mind that TIMEZONE is timezone-aware; 
DATETIME is not. 

TIMEZONE is typo for TIMESTAMP?


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

Re: Create a VIEW with nested SQL

[hsv]

 2012/09/06 08:50 -0400, Mark Haney 
>SELECT vLight.* FROM
>(SELECT lights.*, machine.mach_name from lights
>JOIN machine ON lights.mach_id = machine.mach_id
>ORDER BY date DESC) as vLight
>GROUP BY mach_id

How about

SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields "mach_id" from "lights" and "machine" become one 
unambiguous field "mach_id".

Does "mach_id" really occur more times in "lights" or "machine"? If only once 
in both tables, GROUP BY at most orders it. It is not clear that ORDER BY does 
anything with GROUP BY outside it, unless "mach_id" is unique in both tables--I 
have found that GROUP BY not always orders the output, when everything is 
unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views. 


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

Re: Create a VIEW with nested SQL

[hsv]

 2012/09/07 09:11 -0400, Mark Haney 
All I need is the most recent record for EACH machine ID, THEN to pull the 
machine name from the table that has the name in it.

Somehow I'm missing something incredibly obvious here. 

That is not certain.

There is a fairly standard, fairly ugly means of getting all the latest records 
by some one field:

SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)

This works if each pair (mach_id, date) is unique--and if it is, better so to 
declare it and make an index of it, or maybe make it the primary key. To this 
is the name-table joined:

SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)
JOIN machine USING (mach_id)

As for performance, maybe the suggested index helps (almost every table is owed 
a primary key), or maybe to reverse the three tables (call "ll" a virtual 
table).

If you really want to make a view out of it, under MySQL it can be only two 
views, not one.

(I extensivly use views, but my tables are not big, and the traffic on them is 
very little, about ten transactions each week, all by my hand. One of the views 
is painfully slow.) 


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

RE: Create a VIEW with nested SQL

[hsv]

 2012/09/10 15:49 -0700, Rick James 
SELECT ... ORDER BY .. GROUP BY.. 
is syntactically incorrect.

Yeap, my mistake.


( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick".  It is an optimal way to SELECT all the 
fields corresponding to the MAX (or MIN) of one of the fields.  But it depends 
on the optimizer not screwing it up.  MariaDB decides that this construct can 
be optimized, and messes up the 'trick'.

But this behavior is guaranteed nor in MySQL:


11.16.3. GROUP BY and HAVING with Hidden Columns

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or 
calculations in the select list that do not appear in the GROUP BY clause. You 
can use this feature to get better performance by avoiding unnecessary column 
sorting and grouping. For example, you need not group on customer.name in the 
following query: 

SELECT order.custid, customer.name, MAX(payments)
  FROM order,customer
  WHERE order.custid = customer.custid
  GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In 
MySQL, the name is redundant. 

When using this feature, all rows in each group should have the same values for 
the columns that are ommitted from the GROUP BY part. The server is free to 
return any value from the group, so the results are indeterminate unless all 
values are the same. 


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

function INTERVAL in view

[hsv]

My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in 
a view: redundant round brackets are added. If the query is

SELECT INTERVAL(1, 2, 3, 4)

within the frm file there is the expression

interval((1, 2, 3, 4))

which is wrong.

What is known about this?


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

Re: function INTERVAL in view

[hsv]

 2012/09/17 13:11 -0500, Peter Brawley 
Looks like a bug. Report it?

It was reported:

Bug #45346 VIEW containing INTERVAL(...) can be created but does not work 
Submitted: 5 Jun 2009 10:00 Modified: 5 Jun 2009 10:16
Severity:S3 (Non-critical) 
Version:6.0, 5.4, 5.1 OS:Any 

Seems it arouses little interest. I ask meself whether to add my complaint to 
it. Turns out that for my problem it is enough to add 24 and divide by 20 and 
round down--but that is much moare opaque than INTERVAL(Mean, 6, 18, 36, 72).
Actually, I hav to include 0, too: the yet opaquer function
FLOOR(.4 + SQRT(Mean/5)). Ugh.


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

Re: getting certain rows from a group by

[hsv]

 2012/09/18 06:53 -0400, Larry Martell 
This works fine. But now I need to get a certain column
(image_measurer_id) with each row returned that corresponds to the row
from the group that has bottom = Min(bottom), bottom = Max(bottom),
bottom closest to Avg(bottom), and bottom from the row where date_time
= Max(date_time). 

There is a standard and ugly way of getting such a thing, but it entails 
repeating the table reference (supposing you want any, not every, to match):

SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom
FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - 
bottom)) AS vb, Max(date_time) AS xt
FROM data_cst
  WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 
118, 119, 120, 121)
  AND DATE(data_cst.date_time) = '2010-03-04'
  GROUP BY target_name_id, ep, wafer_id
  HAVING count(*) < 999) AS st
JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom 
OR xb = bottom OR vb = bottom OR date_time = xt)

One record will be returned for every row that holds a relevant extremum, not 
guaranteed to be unique.

This query pertains only to your original subquery, not the whole query. To get 
the result to which you refer, join this to your original query, to which you 
have added something like vb: 

SELECT *
FROM (original query) JOIN (this query) ON nb = minbottom AND xb = maxbottom 
AND xt = "Last Run" AND vb =  AND (original query).target_name_id = 
(this_query).target_name_id

--but I am not confident in the result. There are problems in the original 
query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are 
aggregated over wafer_id. Therefore, it is not certain from which record from 
q1 they are returned. MySQL tends to pick the first that fits, but not only is 
nothing guaranteed, it is explicitly written that if such not aggregated fields 
appear, the output is not determinate unless all pertinent are equal, the 
possibility wherof the reason for allowing it.

When that has been handled, it is needful to change the foregoing query to 
match that one in two levels, because averaging is not associative (there is 
also the problem of equality-testing on generated floating-point numbers). If 
it were only MAX and MIN, one level of not GROUPing BY wafer_id would be all 
right.

By the way, in the original query, I suggest instead of
data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
'2010-03-04 23:59:59'
this,
DATE(data_cst.date_time) = '2010-03-04'

Instead of

 ... q1,
 data_target
WHERE data_target.id = target_name_id

this would be better:

 ... q1 JOIN data_target ON data_target.id = target_name_id

I believe that
 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100)
   = 100*SUM(numbottoms)/SUM(numonep)
and
 SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END)
   = SUM(GREATEST(reruns, 0))


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

Re: getting certain rows from a group by

[hsv]

 2012/09/19 14:36 -0400, Larry Martell 
MIN(ABS(Avg(bottom) - bottom))

Is not valid. It gives:

ERROR  (HY000): Invalid use of group function

Yes, I had my doubts of that, for all that I suggested it.


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

RE: InnoDB vs. other storage engines

[hsv]

 2012/09/19 13:44 -0700, Rick James 
http://mysql.rjweb.org/doc.php/myisam2innodb

Also, InnoDB enforces foreign-key constraints, MyISAM not.


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

RE: Aggregate

[hsv]

 2012/09/04 11:43 -0700, Rick James 
int(1) does not mean what you think.  Probably you want TINYINT UNSIGNED.

Yeap, a real misfeature of MySQL. It is also one of the ways wherin MySQL puts 
in C and takes PL1 away.


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

RE: checking progress of alter table on a MyISAM table

[hsv]

 2012/09/26 09:31 -0700, Rick James 
You could look at the .TYD and .TYI file sizes and compare to the .MYD and 
.MYI, but that can be deceptive.  If the table is really big, and has lots of 
indexes, the generation of the indexes might go slower and slower -- hence any 
math on the sizes would be optimistic. 

And under Windows more misleading--often, if a file is small, the directory 
listing will show 0 until the very end.


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

Re: Need Help Converting Character Sets

[hsv]

 2012/09/24 16:28 -0700, Mark Phillips 
I have a table, Articles, of news articles (in English) with three text
columns for the intro, body, and caption. The data came from a web page,
and the content was cut and pasted from other sources. I am finding that
there are some non utf-8 characters in these three text columns. I would
like to (1) convert these text fields to be strict utf-8 and then (2) fix
the input page to keep all new submissions utf-8.

91) For the first step, fixing the current database, I tried:

update Articles set body = CONVERT(body USING ASCII);

However, when I checked one of the articles I found an apostrophe had been
converted into a question mark. (FWIW, the apostrophe was one of those
offending non utf-8 characters):

Before conversion: "I stepped into the observatory’s control room ..."

After conversion: "I stepped into the observatory?s control room..."

Is there a better way to accomplish my first goal, without reading each
article and manually making the changes? 

I do not remember where on the MySQL website this is, but there was an article 
about converting from character sets in version 4 to those in version 5, when 
UTF-8 first was supported. It sounds to me that maybe the tricks shown there 
would be useful to you, since, in effect, through MySQL MySQL was fooled into 
accepting for UTF-8 that which was not. Conversion to binary string was 
mentioned.


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

Re: Need Help Converting Character Sets

[hsv]

 2012/09/30 11:07 -0700, Mark Phillips 
The data for this table comes from a web page (charet utf8). I copy/paste word 
files into gedit (on linux) and then copy/paste from gedit to a text boxes on 
the web page input form. I had thought I was stripping out all the funky 
characters by using a simple ascii editor like gedit, but obviously not.

After looking at the mysqldump for the table in a hex editor, I discovered I 
have these characters scatter throughout the body and intro columns:
“
” 
’
—
…
↩

How do you mean this? Is there an instance of Unicode character
LEFT DOUBLE QUOTATION MARK, or else of the string "“"?

In any case, this sounds like not an SQL, but general-programming problem; 
furthermore, I suggest that you carefully select a left double quotation mark 
or any of the other punctuation characters (RIGHT DOUBLE QUOTATION MARK, RIGHT 
SINGLE QUOTATION MARK, EM DASH, HORIZONTAL ELLIPSIS, ...) that, it seems, you 
are getting and not liking, and carefully follow your procedure.

It further seems to me that you really do not want that to be UTF-8 string, but 
ASCII, or Latin1.


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

too nice not to share it!

[hsv]

 2012/05/07 11:49 +0200, Claudio Nanni 
http://www.youtube.com/watch?v=INHF_5RIxTE

and stuff from AlgoRhythmics


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

Re: passing shell variable to the SET data type in parentheses

[hsv]

 2012/10/03 20:35 +0700, Morning Star 
$ echo $var
"value1","value2","value3"

what i did:
mysql -u $user -p${password} --skip-column-names -e 'ALTER TABLE
'$table' MODIFY '$kolom' SET(" '$var' ");' $database ;

the result:
ERROR 1064 (42000) at line 1: 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 'value1","value2","value3' at line 1

what do i have to do? please help me.

If your "var" really has three pairs of double-quotes, with the double-quotes 
within the single-quotes, you get that this
... SET(" "value1","value2","value3" ");
is passed to MySQL client, which is not valid.


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

date-IFNULL-sum bug?

[hsv]

Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.

mysql> select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)

mysql> select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03   |
+--+
1 row in set (0.00 sec)

mysql> select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)

mysql> select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)

(5.5.8 under muSoft Windows)


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

Re: (real) silly question about variables...

[hsv]

For this,
 2012/10/04 16:13 +0200, MAS! 
IF(GROUP_CONCAT(DISTINCT secA.sec_code  SEPARATOR '|') is null, 

   
   IF(GROUP_CONCAT(DISTINCT secB.sec_code  SEPARATOR '|') is null, 
settore, GROUP_CONCAT(DISTINCT secB.sec_code  SEPARATOR '|')),  
   
 GROUP_CONCAT(DISTINCT secA.sec_code  SEPARATOR '|') ) as 
settore,  

you really want

IFnull(GROUP_CONCAT(DISTINCT secA.sec_code  SEPARATOR '|'),
   IFnull(GROUP_CONCAT(DISTINCT secB.sec_code  SEPARATOR '|'), 
settore)) as settore,  

The operation
If this is not null use this, else use that
is far, far too common in SQL for there not to be a function for it.

As for your original question, note this from the MySQL HTML documentation:


8.4. User-Defined Variables

...
As a general rule, you should never assign a value to a user variable and read 
the value within the same statement. You might get the results you expect, but 
this is not guaranteed. The order of evaluation for expressions involving user 
variables is undefined and may change based on the elements contained within a 
given statement. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will 
evaluate @a first and then do an assignment second. However, changing the 
statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may 
cause MySQL to select an execution plan with a different order of evaluation. 


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

RE: date-IFNULL-sum bug?

[hsv]

 2012/10/08 14:52 -0700, Rick James 
Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR

No, those operations are well defined. Amongst the timestamp-functions there is 
constant reference to numeric context, and character context--and well there 
is, because there are no time-constants, only numerals and character strings 
taken for timestamps. It is also the only means of doing some things.


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

Re: column aliases in query

[hsv]

 2012/10/11 13:46 -0400, Mark Haney 
I know it's been a while since I wrote serious queries, but I'm sure I have 
done something like this before:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id 
FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE 
machine.factory_id = "1" AND vDate = "2012-10-11"

Where I've aliased the SUBSTR of the date and then used the alias in the WHERE 
clause of the query.  I'm getting an error message now, but I'm almost certain 
I've used that syntax before.  Am I missing something?

Yes: WHERE is for already defined names. In HAVING one refers to new names:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id 
FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE 
machine.factory_id = "1"
HAVING vDate = "2012-10-11"


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

Re: Extract text from string

[hsv]

 2012/10/12 17:56 +0100, Neil Tompkins 
Is there such a way in a MySQL query to extract the text "this is a test"
from the following strings as a example

http://www.domain.com/"; class="link">this is a
test
http://www.domain.com/"; title="this is a test"
class="link">link 

Amongst the built-in functions there isn't much; for the former example, if the 
string is "s", this works:
SUBSTRING_INDEX(SUBSTRING_INDEX(s, '>', -3), '<', 1)
; for the latter, if "t":
SUBSTRING_INDEX(SUBSTRING_INDEX(t, '"', -4), '"', 1)
.

If you want a real HTML parser that discards everything but arbitrary strings, 
you have to get & install your own.


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

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

[hsv]

 2012/10/16 12:57 -0400, Michael Dykman 
your now() statement is getting executed for every row on the select.  try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.

Quote:

Functions that return the current date or time each are evaluated only once per 
query at the start of query execution. This means that multiple references to a 
function such as 
NOW()
 within a single query always produce the same result. (For our purposes, a 
single query also includes a call to a stored program (stored routine, trigger, 
or event) and all subprograms called by that program.) This principle also 
applies to 
CURDATE(),
 
CURTIME(),
 
UTC_DATE(),
 
UTC_TIME(),
 UTC_TIMESTAMP(), and to any of their synonyms. 



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

Re: UDF behaves non-deterministic

[hsv]

 2012/11/04 22:23 +, Stefan Kuhn 
select * from table order by udf(column, 'input_value') desc;
For my understanding, this should give the same result always. 

But if for your data function "udf" returns the same for more arguments there 
is not enough to fix the order. In that case I have found that other accidental 
things affect the order, things that one would not suspect: howmuch store is 
used and needed for the ordering, ... a further reason for showing what the 
function returns. If the order varies, although the function returns the same 
in all cases, well, 


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

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/14 10:26 +0530, sagar bs 
As i have the data with some 25 variables in csv file and i need to import
to mysql.
The issue is that the date format in csv file is dd/mm/ and mysql takes
the date format like /mm/dd.
The  number of variables in the csv file are same in the table in database
of mysql.
Please help me out.

Use LOAD DATA s feature of in the same SQL statement importing into a user 
variable and using it with SET, using the function STR_TO_DATE:

load data ...
(..., @dait, ...) ...
SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y')

. It is not important how many decimal digits match each pattern, but it cannot 
match variation in the separators.

It is required that NULLs in the file take the form '\N' or 'NULL', depending 
on escape-option.


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

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/14 18:27 +0530, sagar bs 
There are four  columns in my table named like account_name, c1, c2 and c3. 
Account name is the primary key and c1, c2 contain two different dates and in 
the column c2 there are few fields  showing /00/00,  now i need to get the 
date different(in days)
between the dates present in the c1 and c2. That days should be shown in the 
c3. please help me out. 

Try DATEDIFF.

As for date /00/00, MySQL s treatment of NULLs in CSV files is peculiar: it 
wants the escape NULL or \N, separator right after separator is not NULL, but 
empty string. Consider those NULL.


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

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/15 00:30 +0100, Mogens Melander 
I guess I'm sill learning.

Does that mean that, if the last column in a load blabla. is a -00-00
terminated by ^n it might error ? Or are we talking ODBC ?

Find it under LOAD DATA 

If an empty field is parsed for a NOT NULL DATE or DATETIME, instead of 
reporting error as in strict mode, the parser makes the date -00-00--Maybe 
in ODBC, too, which I do not know, but certainly from character input. (I 
regularly use CSV files with MySQL, although the match is not perfect.) This is 
a particular case of "zero" (see LOAD DATA) for any NOT NULL type.


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

Re: Dynamic crosstab got me lost.

[hsv]

 2012/11/19 04:49 -0800, Jan Steinman 
> SELECT main.code
> , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps'
> , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps'
> , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps'
> , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps'
> , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps'
> , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps'
> FROM iconstandardrel
> JOIN main ON main.code = iconstandardrel.code
> JOIN iconstandard ON iconstandard.id = iconstandardrel.icon
> ORDER BY iconstandardrel.code;
> 
> Which produces results like:
> 
> 101577, 1, 0, 0, 0, 0, 0
> 101679, 0, 1, 0, 0, 0, 0
> 101679, 1, 0, 0, 0, 0, 0
> 101681, 1, 0, 0, 0, 0, 0
> 101748, 0, 1, 0, 0, 0, 0
> 101748, 1, 0, 0, 0, 0, 0
> 
> But I would like to have One line per code:
> 
> 101577, 1, 0, 0, 0, 0, 0
> 101679, 1, 1, 0, 0, 0, 0
> 101681, 1, 0, 0, 0, 0, 0
> 101748, 1, 1, 0, 0, 0, 0
> 
> Is it possible to achieve this in pure SQL ?

I think you need GROUP BY main.code.

Yes, with fit aggregate functions around the IFs: MAX would work, and also 
BIT_OR. I hav seen no aggregate function that corresponds to OR, the most 
natural in your case.


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

Re: Dynamic crosstab got me lost.

[hsv]

 2012/11/19 05:05 +0100, Mogens Melander 
I found an article on:

http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab

Describing how to do the dynamic generation of SQL statements. 

And I was inspired to do some such thing to one of my views.
This view has three fields:
"City-ZIP": a string of 5-digit ZIP-code and place-name;
Hoads: one of 11 distinct number from 1 through 7.5, showing how good the 
member is;
Members: howmany so good members dwell at that place.

It is more convenient to show this in a table with City-ZIP at the left, HoadS 
across the top, and Members throughout the middle. OpenOffice Calc has the 
needed operation, and I regularly used it for making the table. But with this, 
I can do much of it in MySQL (no row totals):

SELECT 'SELECT "City-ZIP", ' || GROUP_CONCAT('SUM(IF(HoadS = ' || HoadS || ', 
Members, NULL)) AS "' || HoadS || '"') || '
FROM ZIPbwise
GROUP BY "City-ZIP" WITH ROLLUP'
FROM (SELECT HoadS FROM zipbwise GROUP BY HoadS) AS g

It yields this query:

SELECT "City-ZIP",
SUM(IF(HoadS = 1.0, Members, NULL)) AS "1.0",
SUM(IF(HoadS = 1.5, Members, NULL)) AS "1.5",
SUM(IF(HoadS = 2.0, Members, NULL)) AS "2.0",
SUM(IF(HoadS = 3.0, Members, NULL)) AS "3.0",
SUM(IF(HoadS = 4.0, Members, NULL)) AS "4.0",
SUM(IF(HoadS = 4.5, Members, NULL)) AS "4.5",
SUM(IF(HoadS = 5.0, Members, NULL)) AS "5.0",
SUM(IF(HoadS = 5.5, Members, NULL)) AS "5.5",
SUM(IF(HoadS = 6.5, Members, NULL)) AS "6.5",
SUM(IF(HoadS = 7.0, Members, NULL)) AS "7.0",
SUM(IF(HoadS = 7.5, Members, NULL)) AS "7.5" 
FROM ZIPbwise GROUP BY "City-ZIP" WITH ROLLUP


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

Re: Basic SELECT help

[hsv]

 2012/11/22 14:30 +, Neil Tompkins 
I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

>From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

This ugly one, which generalizes:

select id,group_concat(type) AS tl from the_table group by id having 
find_in_set('2',tl) and find_in_set('5',tl)

Ugly becaus it involves so much converting between number & string.


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

Re: Basic SELECT help

[hsv]

 2012/11/22 14:30 +, Neil Tompkins 
I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

>From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

This ugly one, which generalizes:

select id,group_concat(type) AS tl from the_table group by id having 
find_in_set('2',tl) and find_in_set('5',tl)

Ugly becaus it involves so much converting between number & string.

For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my "tl" and a string, say '1,2', to a procedure that using 
SUBSTRING_INDEX taking the strings for arrays ensures that all found in the 
first string is also in the second string. There are times when I wish SQL had 
arrays.


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

Re: Stored Procedure Question?

[hsv]

 2012/11/23 10:49 +0530, Girish Talluru 
I have a scenario where I have to screen a huge bunch of records for in db
using certain rules. I have done in traditional php style record by record
and it took 90 mins for 4000 records. I have 800k - 900k records in
production which might possibly lead to days of execution.

I have figured out that the php script does wait for the record to execute
and then only after it it will process the next record. For this if it is
java I should have used stored procedure and multithreading concept to run
multiple threads in parallel.

But I don't think PHP supports multithreading. Now I have idea to create a
stored procedure to do all the checks and my question here is when I call a
stored procedure does the control get backs immediately to the php script?
Bcoz I want to pick other record immediately while the first one going
through the process and call the procedure again. 

Sounds to me that if your data are in a character form like a CSV file, or you 
can put them into such a form, you can use LOAD DATA to insert into the 
database. Then you would use a separate procedure, outside SQL, beforehand to 
screen the data, and maybe turn them into MySQL s CSV-ish form.

If you are using a PHP procedure, I suspect that you can do that. Furthermore, 
since your screener does not wait for MySQL, but only for PHP s own 
input-output, there is not that wait. Once LOAD DATA begins, it very swiftly 
runs, and your (other?) PHP procedure waits for _all_ the records to be 
inserted, not each one by one.

Of course, you could batch them, too, instead of making one CSV file of 900,000 
records.


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

Re: Retrieve the values from the table of its max date

[hsv]

 2012/11/29 11:46 +0530, Trimurthy 
i have a table which contains the columns 
date,sname,age,item,quantity,units.my question is i want to retrieve all 
the values from the table where date=maxdate group by sname how can i get 
those values.

A question, I suspect, found in all SQL courses


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

Re: MySQL dying?

[hsv]

 2012/12/04 15:18 -0800, Karen Abgarian 
MySQL, like all other products, can be peachy or bitchy.   Good ones, they also 
die.Wish I was kidding :-) 

Mind VHS & BetaMax? BetaMax had much better color--but VHS long outlasted it.


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

Re: MUltiple value in single insert is not working in mysql procedure

[hsv]

 2012/12/03 19:10 +0530, amit 
Problem
mysql> call mobile_series1('(99889988),(12334565)');

You are expecting MySQL to turn one string operand into twain number operands. 
That does not happen, unless you use PREPARE, which, I suspect, is not part of 
your homework. 


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

Re: Help with left outer join

[hsv]

 2012/12/11 16:19 -0500, Larry Martell 
I have this query:

SELECT data_target.name, ep, wafer_id, lot_id,
   date_time, data_file_id, data_cstimage.name,
   bottom, wf_file_path_id, data_measparams.name,
   vacc, data_category.name
FROM data_cst, data_target, data_cstimage, data_measparams,
 data_category, data_tool
WHERE data_cst.target_name_id IN (38018, 29947, 28330)
AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00'
AND data_target.id = data_cst.target_name_id
AND data_cstimage.id = data_cst.image_measurer_id
AND data_measparams.id = data_cst.meas_params_name_id
AND data_category.id = data_tool.category_id
AND data_tool.id = data_cst.tool_id
ORDER BY target_name_id, ep, wafer_id, lot_id, date_time

My problem is that when data_cst.image_measurer_id is NULL I don't get
that data_cst row even though all the other part of the where clause
are TRUE. I understand why that is, but in that case I want the row,
but with NULL in the data_cstimage.name column. I think I need a left
outer join, but I've been messing with this for hours, and I can't get
the syntax right. I've googled it, but all the examples are simple
with just 2 tables. Can someone help me with this? 

Modern forms do not give a left join if one uses WHERE-clause to reduce a full 
cross-join to an inner join. It is better to start with something like this,

FROM data_cst JOIN data_target
ON data_target.id = data_cst.target_name_id JOIN data_cstimage
ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams
ON data_measparams.id = data_cst.meas_params_name_id JOIN
 (data_category JOIN data_tool
ON data_category.id = data_tool.category_id)
ON data_tool.id = data_cst.tool_id

but I am not too sure where to bracket "data_tool". When you have put it into a 
'FROM'-clause with 'JOIN', not comma, separating the tables, with the same 
outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'. 
Maybe you want it between "data_cstimage" and "data_measparams".

(Are all the distinct "id"s really needed? When one joins on a field with the 
same name in both tables, one may use 'USING', and only the common field, with 
neither NULL, shows up in the output.)


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

CONCAT_WS and NULL

[hsv]

This is, maybe, a question of taste. I find it useful in the aggregate 
functions that they ignore all NULLs that come under their purview, but yield 
NULL if nothing else comes. Now, CONCAT_WS is no aggregate function, but is 
like them in that it ignores all NULLs that come its way, aside from the first 
argument, the separator. If all are NULL, it yields empty string. I now find 
that I wish that in this, too, it were like the aggregate functions, yielding 
NULL if its arguments are NULL. Comment?


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

Re: Foreign-key naming

[hsv]

When I wrote my comment after Larry Martell s problem, I already suspected it 
was somewhat out of place because to his problem it did not apply.

 2012/12/12 08:25 -0500, Shawn Green 
This is a perfectly acceptable naming convention to use. For example if you 
have a field on the `art` table that references the ID column of the `person` 
table to indicate the owner and another field to indicate the person who 
created the art, you might want to use the names `owner_person_id` and 
`artist_person_id` to keep them separate from the `id` column used to uniquely 
identify the work of art itself.

In this design pattern, each table has a numeric ID column (string-based 
primary keys are perfectly legal but have their drawbacks and should be used 
with care) and to reference it from another table you can use the pattern 
_id.  It keeps your naming conventions clean and predictable.

If I were to try to use a USING operator in my opening example, I would be 
trying to match the PK fields of two separate types of data.

(the USING example)
SELECT ... FROM art INNER JOIN person USING(id)...

Let's say that I renamed the id fields to art_id and person_id to make them 
table-specific. This still fails because a person's identifier as an owner is 
not the same as a work of art's creator. It also does not allow me to use the 
`person` table more than once in a single query.

(FAIL: a renamed USING example)
SELECT ...
FROM art
INNER JOIN person USING(person_id) <--- does this refer to the owner or the 
creator of the art?

(the name template example)
SELECT ...
FROM art
INNER JOIN person owner
  on art.owner_person_id = owner.id
INNER JOIN person artist
  on art.artist_person_id = artist.id
...

Well, you're right, a work can refer to people in at least two different 
aspects, there is the work's author, and the work s owner. Neither is 
appropriate for the same name as found in a list of people, because now a 
distinction is made in the undifferentiated mass. And, yes, in general I 
suspect that if in one table there are more foreign-key references to the same 
key in another table, there is enough difference in aspect that none of them is 
fittingly so named as in the original table.

What if neither "author" nor "owner" directly referred to people, but, instead, 
"author" referred to a table of artists, with their training & style listed, 
and "owner" referred to a table of owners, with preferred styles of work 
listed? These tables in the end would refer to people; shall their references 
bear a name distinct from the key in the original table's?

I believe that for every chain of foreign-key references from one table to 
another, if there is no other chain of foreign-key references from that one 
table to that other table (and no design-change that changes this is likely!), 
it is quite all right if along the chain each foreign-key reference and the key 
to which each refers have the same name. The nice thing about USING and NATURAL 
is that in a query only one coalesced field is yielded. I find it not quite 
right to pick between one field or another to yield when both are alike unless 
one of them is NULL.


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

Re: Complex MySQL Select Statement Help

[hsv]

 2013/01/31 22:24 -0600, Peter Brawley 
Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice < unitprice And CurDate() Between 
startingDate And endingDate,
specialprice,
unitprice
) as used_price
>From catalog
Where itemid='WB314';

PB 

Maybe this is gilding the lily, but if "specialprice" is null, then
specialprice < unitprice
is not true--and maybe if the null-test is left out it is less clear 


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

Re: Complex MySQL Select Statement Help

[hsv]

 2013/02/02 12:58 -0600, Peter Brawley 
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
>2013/01/31 22:24 -0600, Peter Brawley 
>Is this what you mean?
>
>Select,
>pricelist
>If( !IsNull(specialprice) And specialprice < unitprice And CurDate() Between 
>startingDate And endingDate,
>specialprice,
>unitprice
>) as used_price
>>From catalog
>Where itemid='WB314';
>
>PB
>
>Maybe this is gilding the lily, but if "specialprice" is null, then
>specialprice < unitprice
>is not true

Read again: ...If( !IsNull( specialprice )...

Right: if
specialprice < unitprice
is true, then "specialprice" is not null. The null-test is absorbed. 


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

IF and CASE

[hsv]

It is my impression that when their functions are equivalent, IF takes more 
time than CASE. Comment?

Do they always evaluate all their arguments?


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

RE: IF and CASE

[hsv]

 2013/02/05 17:06 +, Rick James 
As a Rule of Thumb, function evaluation time is not significant to the overall 
time for running a query.  (I see IF and CASE as 'functions' for this 
discussion.)

Do you have evidence that says that IF is slower?  Perhaps using BENCHMARK()? 

Not BENCHMARK: I did a query with one, and also with the other, and repeated 
each at least a dozen times, and looked at the reported time. The IF-variant 
took ever so slightly more time than the CASE-variant.

But which of the arguments are always evaluated, which only at need? This could 
be a difference, that IF s arguments always are, CASE s only at need.


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

Re: Retrieve most recent of multiple rows

[hsv]

 2013/03/13 13:18 +, Norah Jones 
I have a table which looks like this:

answer_id  q_id  answer  qscore_id  answer_timestamp
1  10Male3  1363091016
2  10Male3  1363091017
3  11Male3  1363091018
4  10Male3  1363091019
5  11Male3  1363091020
6  12Male3  1363091020
7  11Male3  1363091025

So I have multiple answers for the same questions (q_id). I want to be able to 
retrieve only ONE answer per question and that be the most recent answer.
There should be THREE rows returned, which are all the most recent answered for 
that q_id:

4  10Male3  1363091019
6  12Male3  1363091020
7  11Male3  1363091025  changed!

Something like this:

select * from x where (answer_timestamp,q_id) in (
select max(answer_timestamp), q_id from x group by q_id)
group by q_id;

It makes use of MySQL s feature of allowing not aggregated fields with GROUP 
BY. Otherwise each record with the same "q_id" and greatest "answer_timestamp" 
would be shown. Nothing is guaranteed which is indeed shown.


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

Re: a little doubt on text about MySQL

[hsv]

 2013/03/15 12:43 -0300, Marcus Vinicius 
Does anyone knows the author of this:

http://grimoire.ca/mysql/choose-something-else

Title: "Do Not Pass This Way Again"

Not I

--but, as to automatic type-conversion, I find me in agreement with the author. 
When I first began to use MySQL I was dismayed at all the automatic conversion, 
some of it decidedly unintuitive, and this one is a good example of particular 
badness:

mysql> select 0 = 'banana';
+--+
| 0 = 'banana' |
+--+
|1 |
+--+
1 row in set, 1 warning (0.03 sec)

mysql> show warnings;
+-+--++
| Level   | Code | Message|
+-+--++
| Warning | 1292 | Truncated incorrect DOUBLE value: 'banana' |
+-+--++
1 row in set (0.00 sec)

This definitly is an error to show, not let be a warning.

All those 0-values on saving into the table,  The developers seem deeply 
loath to report error. When the table is not transactional, yes, there is some 
reason for it, but a not transactional table is not a full partner in database. 
When the table is transactional, to report error is the way to go, although a 
big LOAD DATA be aborted, not to make the user learn about all those 0-values.

He makes a point that had not come to me, that type-security somewhat depends 
on SQL_MODE, which belongs to the connection. It is, quite rightly, stored in 
saved program code, but not in any table. At least these affect table behavior,

ALLOW_INVALID_DATES
NO_AUTO_CREATE_USER (administrational security!)
NO_AUTO_VALUE_ON_ZERO
NO_ZERO_DATE
NO_ZERO_IN_DATE
PAD_CHAR_TO_FULL_LENGTH (well, maybe not this one)
STRICT_ALL_TABLES
STRICT_TRANS_TABLES

, and they belong in the table, even as those that affect parsing & compiling 
belong in saved code, not only in the connection.

MySQL s own types, ENUM and SET, which have both string & integer 
manifestation, are not well handled. Try this (version 5.5):

create temporary table v (m set ('a','b'));
insert into v value (1),(2),('a'),('b'),('b,a'),(null);
select m,if( m is not null, m, 21) + 1 from v;
select m,ifnull( m, 21) + 1 from v; -- no warning, either

(and the numeric context is DOUBLE!)

What about these?
select m,if( m is not null, m, 21) from v;
select m,ifnull( m, 21) from v;
I feel that the numeric constant sets the context to numeric--the IF[NULL] s 
own context is indeterminate--, but not that happens.


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

Re: a little doubt on text about MySQL

[hsv]

 2013/03/16 03:44 +0100, Reindl Harald 
what are you speaking about?

you can define it in my.cnf and YOU are responsible for
the configuration as you are also responsible the
develop php code with error_reporting = E_ALL

These SQL-modes that pertain to type-safety are really part of the _type_: 
ALLOW_INVALID_DATES
NO_ZERO_DATE
NO_ZERO_IN_DATE
Their value when one does "CREATE TABLE ..." really belongs to the newly 
created table, if not to particular fields in the table. It is 
type-declaration. This one, NO_AUTO_VALUE_ON_ZERO, is part of the table s type, 
and belongs with the newly created table, or with the fields on which it bears 
(MyISAM).

It really is not right that one who designs a table designs it with one date 
setting or another in mind, then another, who uses that table, changes any of 
these in local SQL mode, and thereby changes the type.

As for this one, NO_AUTO_CREATE_USER, there is no reason for letting it differ 
in local or global SQL-mode from that defined in my.cnf (my.ini). Inasmuch as 
MySQL lets one set that apart from the configuration file, there is a problem, 
especially from dropping it.


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

Re: Show ROUTINE body, not PROCEDURE

[hsv]

 2013/03/25 11:28 +0200, Dotan Cohen 
Thanks. I don't have the admin or root privileges on this database. Is
that the only way to see the code behind the function?

Well, you showed us "DEFINER: admin@localhost" for the function; here is a 
snippet from MySQL help about "
SHOW CREATE FUNCTION func_name
":

... require that you be the owner of the routine or have 
SELECT
 access to the mysql.proc table. If you do not have privileges for the routine 
itself, the value displayed for the Create Procedure or Create Function field 
will be NULL.

I guess you have to find Admin (or Root) in real life to talk about the 
function.  


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

Re: Show ROUTINE body, not PROCEDURE

[hsv]

 2013/03/27 08:01 +0200, Dotan Cohen 
Actually, it is the user that I am logged in as that created the
function. That is why I find it hard to believe that one needs root /
admin access to see its definition. 

And that user set DEFINER other than itself, and that worked???
That takes SUPER.


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

error-log aging

[hsv]

Is there somewhere within MySQL means of aging the error log, that it not 
indefinitly grow big, or is that done through the OS and filesystem on which 
"mysqld" runs?


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

Re: Determing number of queries

[hsv]

 2013/04/04 22:40 +0200, Manuel Arostegui 
You can start with show innodb status;

It is now
show engine innodb status


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

Re: error-log aging

[hsv]

 2013/04/04 23:18 +0200, Reindl Harald 
> Is there somewhere within MySQL means of aging the error log, that it not 
> indefinitly grow big, or is that done through the OS and filesystem on which 
> "mysqld" runs?

man logrotate

Not Unix!

In any case, I take this to mean that this is not done within MySQL, right?


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

Re: update a row only if any column has changed, in a very large table

[hsv]

 2013/04/06 13:56 -0700, Rajeev Prasad 
I have a table with around 2,000,000 records (15 columns). I have to sync this 
from an outside source once every day. not all records are changed/removed 
/new-added everyday. so what is the best way to update only those which have 
changed/added/or deleted?

i can use update_or_create but that will update (re-write the row) even if 
nothing has changed in the row/record. wont that be an overhead? how can i 
escape that? what would be the fastest and least resources consuming way to do 
this table update?

I also have another table with 500,000 rows and i wish to implement the same 
solution to that too.

I earlier posted this on DBIx list, as i thought i could use DBIx tools to 
manage this. but based on response, it seems that MySQL tools would be more 
helpful in doing it in most efficent way. Plz. advice how can i address this.
 
I also considered to delete and simply recreate the table each day. but 
changes/add and delete are not too many (may be a few hundreds.. max)

Sounds like a case for replication (look it up: 
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is 
statement-replication, and row-replication. The former replicates all 
operations on the database, in the form wherin they were made. False changes 
(changing a field to its former value), too, are recorded. The latter records 
only those changes to a table that are real changes. (In MySQL 
statement-replication is of earlier implementation.)

After changes are recorded, they are passed from the master --the wellspring of 
the changes-- to the slave --the taker of them.

These are not tools, as such: replication is something implemented in the 
database-management system. If both your databases are in MySQL you can get 
help here.


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

Re: error-log aging

[hsv]

 2013/04/05 11:16 +0200, Johan De Meersman 
Half and half - rename the file, then issue "flush logs" in mysql to close and 
reopen the logs, which will cause a new log with the configured name to be 
created.

That being said, I'm not much aware of Windows' idiosyncracies - I hope the 
damn thing allows you to rename a file that's being held open by a program. If 
not, well... see above. 

No, as Reindl answered, but in 5.5.8 there is this:


If you flush the logs using 
FLUSH
 LOGS or 
mysqladmin
 flush-logs and 
mysqld
 is writing the error log to a file (for example, if it was started with the 
--log-error
 option), the effect is version dependent: 
* As of MySQL 5.5.7, the server closes and reopens the log file. To rename 
the file, you can do so manually before flushing. Then flushing the logs 
reopens a new file with the original file name. For example, you can rename the 
file and create a new one using the following commands: shell> mv host_name.err 
host_name.err-old

shell> mysqladmin flush-logs

shell> mv host_name.err-old backup-directory

On Windows, use rename rather than mv. 

* Prior to MySQL 5.5.7, the server renames the current log file with the 
suffix -old, then creates a new empty log file. Be aware that a second 
log-flushing operation thus causes the original error log file to be lost 
unless you save it under a different name. On Windows, you cannot rename the 
error log while the server has it open before MySQL 5.5.7. To avoid a restart, 
flush the logs first to cause the server to rename the original file and create 
a new one, then save the renamed file. That also works on Unix, or you can use 
the commands shown earlier. 


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

Re: Doubt with stored procedures

[hsv]

 2013/04/17 14:16 +0200, Antonio Fernández Pérez 
I have a doubt with stored procedures functionality. Is possible that a
stored procedure works with all databases form the server? I have created a
stored procedure on dataBaseA and also works with dataBaseB. Is that
correct? Independently of the user privileges defined. 

It is the default assumption that a procedure within a database is meant for 
use within that database, but one can call a procedure from any of the set of 
databases by qualifying the name--and the MySQL command "show procedure status" 
shows all procedures. The only question is the procedure s use of variables: if 
they refer only to the arguments, it is of no importance whence it is called. 
This is documented:

USE
 statements within stored routines are not permitted. When a routine is 
invoked, an implicit USE db_name is performed (and undone when the routine 
terminates). The causes the routine to have the given default database while it 
executes. References to objects in databases other than the routine default 
database should be qualified with the appropriate database name. 


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

Re: how to list record in column (instead of a row)

[hsv]

 2013/04/24 09:06 -0700, Rajeev Prasad 
this table has many columns and only 1 record. select * from table; generates 
an unreadable list. how can i list the record as in two columns? (column name 
and its value)? i looked at UNPIVOT, but could not get it to work.
SQL> select * from table UNPIVOTE INCLUDE NULLS;
 select * from table UNPIVOTE INCLUDE NULLS
* ERROR at line 1: ORA-00933: SQL command not properly ended 

>From MySQL client, if started with flag '-G':
select * from table
ego

For the same program there is flag '--auto-vertical-output'.

But it seems you are using Oracle; this is MySQL list.
In any case, you wrote both 'UNPIVOT' and 'UNPIVOTE'; I suspect the former is 
better.


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

Re: Long integer constant problem in views

[hsv]

 2013/04/30 17:17 +0200, Martin Koch 
CREATE OR REPLACE VIEW foo AS
  SELECT *
  FROM mytable
  WHERE id = X'36a461c81cab40169791f49ad65a3728';

Try this: _binary X'36a461c81cab40169791f49ad65a3728'

SHOW CREATE VIEW is the command for the client.


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

Re: NET START MYSQL QUESTION?

[hsv]

 2013/05/11 20:50 +0600, SIVASUTHAN NADARAJAH 
I want to start the mysql from command prompt using "NET START MYSQL"BUT 
the server not started. It display an error message. 
C:\Users\PC> NET START MySQLSystem error 5 has occurred.
Access is denied.
could you please help me, how to start the Mysql service FROM command prompt?   
SIVASUTHAN- Consultant Trainer

Well, ordinarily the MySQL service is started by Windows along with all the 
rest: After installing MySQL under Windows one runs "MySQL Instance 
Configuration Wizard"--the only MySQL program on my system that runs from 
"Start". (If you cannot do this on the machine where "mysqld.exe" is to run I 
do not know what to say.)

If the service, with program "mysqld.exe", quits, to start it one enters
"start mysqld -b..." from the command line, with the directory where "my.ini" 
is kept entered for "...". Of course, if "mysqld.exe" started and quit, as 
Reindl Harald suggested study the logs and find out why. It is not started 
through the client, "mysql.exe".


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

Re: NET START MYSQL QUESTION?

[hsv]

 2013/05/11 22:58 +0200, Reindl Harald 
why not answer the question another user made hours ago?
under which account do you try to start mysqld? 

Well, I learnt something here.

When I had the problem of (under Vista) starting "mysqld", from command prompt 
I always did this, "start mysqld -b..." (here "start" is like Unix s trailing 
"&"), and never had a problem. It showed up running on the service list. Maybe 
something like Unix s set-user-id is in effect in "mysqld.exe".

Now for the first time I learn of command "NET", and its options. I do not 
remember seeing "net start MySQL" in MySQL s help when I installed it, only 
that which I above described.

This is not so much an OS problem, but a problem at the point where the OS s 
peculiarities and a big package s nature intersect.


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

RE: Version 5.6.2-m5 Boolean Datatype

[hsv]

 2013/05/22 21:17 +, Rick James 
In query syntax, TRUE is the same as 1; FALSE is the same as 0.

and UNKNOWN is NULL. (I actually have used a three-state comparison.)

It has been suggested that one who wants a real two-state field use the type
CHAR(0) NULL.


If you have more 'flags', consider the SET datatype.  (Yeah, it is somewhat 
clumsy.) 

And SET is mapped onto some integer, even as ENUM is. BIT, now deprecated I 
understand, would have been the perfect type to map ENUM onto.


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

Re: Bug in BETWEEN same DATETIME

[hsv]

 2013/05/24 09:49 -0400, shawn green 
Or we could coerce datetime values back to their date values when both are 
being used. The trick now becomes choosing between rounding the datetime value 
(times past noon round to the next date) or do we use the floor() function all 
the time.

This is simply wrong. Timestamps are not numbers: we do not add timestamps, and 
when we subtract them we do not consider the difference something of the same 
type. Therefore, one does well to be wary when applying to a timestamp the 
notion "rounding".

But containment generally applys: an event on MAY 25th from 1pm to 4pm is 
within May 25th, which is within May,  When containment fails, then there 
is trouble: what is the first weekend of August? or the first week of August? 
better to say, the weekend or week of August 1st, or 2d, or ...; "day" is a 
"common divisor" to calendar-month, weekend, and week.

Therefore, when I learnt that in version 4 MySQL had gone from interpreting a 
comparison between DATE and a finer timestamp by the DATE to interpreting it by 
the finer timestamp I believed that MySQL was going the wrong way--that MySQL 
had gone from a realization of an intuitive sense of containing, as above, to 
one on which too much thought had been expended, with a loss of intuitive sense.

I consider the change of 2013/5/25-13 to 2013/5/25 to be truncation, not any 
sort of rounding; that is, it is a matter of notation, but one which intuitivly 
expresses containment.

These notions sometimes change over the years, and by nation. When the first 
public striking clock was set up in Milan, it pointed to hours I through XXIV, 
with sunset falling within the 24th hour--that is, the 24th hour ends with 24 
o'clock s being struck. This persists to this day in the German expression 
"viertel sechs", which means that the sixth hour is one-fourth over, or, as we 
would say it, "quarter after five". (Like expressions are found amongst the 
Germans s neighbors, but in English never took root.) Nowadays we are are more 
inclined to associate both "quarter after five" and "quarter to six" 
("dreiviertel sechs") with 5 o'clock than 6 o'clock; this accompanies the 
change of notation from 1 through 24 to 0 through 23.

I find MySQL s automatic conversion sometimes to be downright screwy; (version 
5.5.8) consider "SELECT NULL" and "SELECT NULL UNION SELECT NULL"; in one of my 
views there is a complex wholly numeric expression that becomes "varbinary(32)".


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

Re: Editing existing Trigger MySQL 5.6

[hsv]

 2013/05/29 10:39 +0100, Neil Tompkins 
Using Workbench with MySQL 5.6 how do I edit a existing Trigger.  Do I need
to DROP the Trigger and create a new one ?  If that is the case how can you
run start command in a live environment ? 

Whatever appearance Workbench adds (I do not know it), replacing a simple 
trigger looks something like this:

DROP TRIGGER IF EXISTS tr;
CREATE TRIGGER tr ...;

.

What do you mean by 'start'? The Windows command-line command?
START SLAVE? START TRANSACTION?


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

Re: Temporary Tables with Triggers Problem

[hsv]

 2013/05/29 14:51 +0100, Neil Tompkins 
This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell <> OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW()); 


You have left out the opening line, but it looks like AFTER UPDATE; is the 
table "AuditTrail", or another?

How did this pass the parser,
IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
? If Workbench corrected it, there is no knowing what the code really is. This 
is correct,
IF (SELECT COUNT(*) FROM tempHotelRateAvailability) > 0 THEN
but it is just as well to write
IF EXISTS(SELECT * FROM tempHotelRateAvailability) THEN
.

Why bother with the temporary table? It never has more rows; it is just as well 
to insert straight into AuditTrail if NEW.RoomsToSell <> OLD.RoomsToSell.


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

string-likeness

[hsv]

I wish to join two tables on likeness, not equality, of character strings. 
Soundex does not work. I am using the Levenstein edit distance, written in SQL, 
a very costly test, and I am in no position to write it in C and link it to 
MySQL--and joining on equality takes a fraction of a second, and this takes 
hours. Any good ideas?


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

Re: string-likeness

[hsv]

 2013/06/03 18:38 +0200, Hartmut Holzgraefe 
equality checks have a linear cost of O(min(len1,len2)) and can make
use of indexes, too, while Levenshtein cost is is almost quadratic
O(len1*len2) and can't make any good use of indexes ... even using
a C UDF would help only so far with this kind of complexity. It will
increase performance by a constant factor, but given long enough
input strings the len1*len2 factor will still account for the majority
of the run time increase over simple equality comparions

My set isn't that big (not the hundreds of thousands to which many on this list 
refer), only big enough to be a pain, and here the constant, between 
implementing in interpreted SQL with no array, only temporary table, and 
compiled C, with real array, probably matters--except that my C-implementation 
won't happen.


there are a few possible points of optimization though, first of all
you can cut off equal start and end sequences (linear complexity for
that part instead of quadratic). You can also add a few more tricks
if you are only interested in matches below a certain distance threshold:

* if string lengths differ by more than the threshold value you can
  rule out this pair of strings as being "similar" right away

* while iterating over the distance array keep track of the min.
  distance value of the current row ... if at the end of a row
  is larger than the threshold distance you can terminate right away

Didn't think of these ... will have to find a threshold


* only calculate operation cost, not operation type

* do not maintain a full len1*len2 array, having only the previous
  and current row in two one dimensional arrays is sufficient
  (this esp. helps in C implementation as the functions working set
  is more likely to fit into CPU caches) 

I already do this, because MySQL has no arrays, and I use a small temporary 
table instead of one linear array.


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

RE: string-likeness

[hsv]

 2013/06/03 21:43 +, Rick James 
Soundex is the 'right' approach, but it needs improvement.  So, find an 
improvement, then do something like this...

Hashing involves somekind normalizing, and in my case I see no means to it; 
otherwise I would not have considered something so costly. On the other hand, 
maybe 

I am comparing lists of place-names, and I want to match, say, any of "Mount 
Saint Francis" or "MT ST FRANCIS" or "MOUNT ST FRANCIS" or "MT SAINT 
FRANCIS"--but it is not all standard abbreviations. Sometimes there is 
"Galvestn" or "Galvston" or "Galvstn" for "Galveston", and it is not always 
vowel-letter deletion, either: "Ft Benj Harrison", "FT BENJAMIN HARRISON",  "Ft 
Benj Harsn"; "CLVR MIL ACAD", "Culver Milt Acad".

Anyhow, I gave up on a perfect solution, and instead added to each name the 
name padded with '%'s. On joining the longer name is used, but instead of the 
shorter the padded is used after "LIKE", if "LOCATE" also fails to match, and 
overall the Levenstein edit distance is used only for a check, with 
short-circuit "AND" and "OR" supposed (and the timing is such that I believe it 
is):

ON (LOCATE(Bookk.Burgh, PO.Burgh) > 0 OR LOCATE(PO.Burgh, Bookk.Burgh) > 0 OR 
CHAR_LENGTH(Bookk.Burgh) > CHAR_LENGTH(PO.Burgh) AND Bookk.Burgh LIKE PO.pBurgh 
OR CHAR_LENGTH(Bookk.Burgh) < CHAR_LENGTH(PO.Burgh) AND PO.Burgh LIKE 
Bookk.pBurgh)
AND mismatch(Bookk.Burgh, PO.Burgh, 1, 2, 1) < 8 IS NOT FALSE

It does not match "MOUNT ST FRANCIS" and "MT SAINT FRANCIS".
At least for LOCATE and LIKE there are linear-time algorithms.

All along I assumed that in the end some of the mismatching will be handled by 
hand. It is not that big a list, but doing all by hand is far too much. 


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

Re: Stopping mysql does not always stop it?

[hsv]

 2013/06/06 09:28 -0400, Mike Franon 
Long story short, 50% of the time the command /etc/init.d/mysqld stop  will
fail 

Don't see why it anywhen succeeds. My version of "mysqld" doesn't know "stop". 
As for "mysqladmin", it knows "stop", but, since that means "stop-slave", I 
doubt you want that. Maybe you want "mysqladmin ... shutdown".

Better first to read up.


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

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/11 12:59 -0700, Daevid Vincent 
Also, just for S&G this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while "clever" is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

Except that, it seems to me, it somehow reflects the reality of assigning 
attributes to the scenes (movies?) that you catalog. In a way, it looks very 
much like using a bitstring wherin each place stands for one attribute. If you 
then have also a bitstring for each user s likes and one for rows (peeves), 
telling howmany 1s are at the same place for the "genres" and liking (bit-AND, 
MySQL "&" followed by telling the number of 1s), and same for the "genres" and 
the row or peeve yields a number howmany match for liking, and how many match 
for becoming peeved. If the liking is enough greater than the becoming peeved, 
the scene and the user match.

Unhappily, although this, using bitstring for set of attributes to match, is an 
old and well understood topic, MySQL s support for bitstrings is poor, limited 
to integers (as C is so limited)--that is, to 64 bits. If you have more, you 
have to use more "words". There is, furthermore, no function for telling 
howmany 1s (or 0s) there are in an integer.

Now, if, in a more perfect world, MySQL had bitstring, and, furthermore, MySQL 
s SET were mapped onto bitstring, where it belongs, you could not only use bit 
operations (MySQL s & | ^), but also name the bits as you like.

The problem with writing one s own bit-telling function is, of course, time, 
and hiding useful information from the optimizer. In any case, here is a 
function for it, using an old well worn trick that depends on binary arithmetic:

delimiter ?
create function bittell(B INTEGER) RETURNS INTEGER
DETERMINISTIC
NO SQL
COMMENT 'Howmany 1s in argument?'
begin
declare E integer;
SET E = 0;
WHILE B <> 0 DO
set B = (B-1) & B, E = E + 1;
end WHILE;
RETURN E;
end ?
delimiter ;

If you stick with the character-string set, with a slight change in 
representation you can use a simpler-looking pattern--not more efficient, if 
MySQL s implementation is good, but of easier reading: separate the decimal 
numerals with a character that is neither a decimal digit nor a REGEXP 
operator, and bound the whole string with it--comma or semicolon (among others) 
are good.
   ',1,10,19,31,32,59,' REGEXP ',10,+.*,38,' is 0
   ',1,10,19,31,32,59,' REGEXP ',10,+.*,32,' is 1
(See also FIND_IN_SET.)

Somewhere I read that for lack of support bitstring has been withdrawn from the 
SQL standard. This is such an obvious use; why is it not supported?


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

Re: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/11 12:59 -0700, Daevid Vincent 
Also, just for S&G this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while "clever" is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

Except that, it seems to me, it somehow reflects the reality of assigning 
attributes to the scenes (movies?) that you catalog. In a way, it looks very 
much like using a bitstring wherin each place stands for one attribute. If, 
say, the bitstring for that which the user gladly picks something is called 
"glad", and that for which the user is loath to pick something is called 
"loath", an expression for fulfilling all attributes is
(glad & genre) = glad AND (loath & genre) = 0,
with no bit-telling. 


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

Re: Update just some of the fields

[hsv]

 2013/06/17 11:38 +0430, Sayyed Mohammad Emami Razavi 
update test set desc='test10' where id=1; 

_That_ is UPDATE! It is the only means of changing, but neither inserting nor 
deleting, a record.

The other fields are left the same.

MySQL also tracks whether it is an actual change; this is reflected in the 
client message
Rows matched: ?  Changed: ?  Warnings: ?
. The number after "Changed:" is the number of records where the new value 
really differs from the old.


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

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

[hsv]

 2013/06/13 23:08 +, Rick James 
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...

And have genres look like 'action,drama,foobar', that is comma-separators, and 
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,38' and FIND_IN_SET('19', genres)

And you seem no fan of named BITs (SET), either. *sigh*


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

Re: space gone after MyISAM REPAIR TABLE

[hsv]

 2013/06/26 17:31 +0100, nixofortune 
ALTER TABLE `new_innodb`
ADD KEY `idx1` (`col1`,`col2`),
ADD  KEY `idx2` (`col1`,`col2`,`col3`);

Is it really seemly for one index to be a leading part of another?
(or maybe I am really thinking of something else)


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

Re: Get Affected Rows after Stored Procedure COMMIT

[hsv]

 2013/07/02 12:29 +0100, Neil Tompkins 
I have a number of INSERT and UPDATE statements in a MySQL Stored
Procedure, that works in the form of START TRANSACTION followed by COMMIT.
 Also I am handling any EXCEPTION.

However, after calling COMMIT, how can I get the number of Rows that were
affected either INSERTED or UPDATTED ? 

Can you use function ROW_COUNT to any effect? If you can, probably you have to 
add its yields up in your own code.


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

Re: hypothetical question about data storage

[hsv]

 2013/07/27 00:58 +0200, Chris Knipe 
I would definately consider the md5 checksum as a
PK (char(32) due to the hex nature), 

Well, not that it greatly matters, but you could convert it to BINARY(16).


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

Re: Question regarding creating a query

[hsv]

 2013/07/30 14:12 -0400, Sukhjinder K. Narula 
I have several databases (all with same structure), which I to query. For
instansce:

db1, db2, db3 - all have table tb1 with field a, b and table tb2 with
fields flag1, flag2

So I want to query and get field a from tb for all db's. One way to do is
union i.e.

SELECT a FROM db1.tb1 WHERE (SELECT flag1 FROM db1.tb2) = 'y'
UNION
SELECT a FROM db2.tb1 WHERE (SELECT flag1 FROM db2.tb2) = 'y'
UNION
SELECT a FROM db3.tb1 WHERE (SELECT flag1 FROM db3.tb2) = 'y'

But the problem here is that if I add more db's, I have to update the query
every time.

In addition to above, I also have a database e.g. common, which has a table
called dbnames with field name, that keeps the name of all the databases I
have (db1, db2, db3).

So, what I would like to do is query the common db to get the names of the
db's and then run the select query on each db.

So here is the pseudocode of what I want to do:


for each (SELECT name AS DbName FROM common.dbnames)

(SELECT a FROM DbName.tb1 WHERE (SELECT flag1 FROM DbName.tb2) = 'y')  AS
CONCAT(DbName, '-', a)

Well, you could build up the united query in a string and pass it to PREPARE


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

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[hsv]

 2013/08/21 18:03 -0400, Nick Khamis 
We have the following mysql timetampe field

startdate | timestamp | NO   | | -00-00 00:00:00

When trying to insert a long value in there:

Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
c.getTimeInMillis();

We are presented with the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
datetime value: '1377119243640' for column 'stopdate' at row 1

Ugh, where is the SQL?

In any case, although it looks as if that is MySQL s internal TIMESTAMP 
representation, one does not directly use Unix timestamps; instead, one 
converts them with the MySQL function FROM_UNIXTIME.

The same effect may be gotten with any timestamp-formatting function that 
yields a string in the form '2013/08/21 18:03:00' (it is all one whether the 
separator is hyphen, slant, colon, ...).


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

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[hsv]

 2013/08/22 14:22 -0400, Nick Cameo 
SimpleDateFormat sdf = new SimpleDateFormat("-MM-dd'T'HH:mm:ss", new 
Locale("en", "US"));

Well, you have your answer (FROM_UNIXTIME(   /1000)), but that stupid ISO 
format with 'T' in the middle does not work, because to MySQL letters are not 
separators--which, I am sorry to say, I did not say, although it was in the 
back of my mind.


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

Re: Can't Connect Localhost

[hsv]

 2013/09/02 12:49 +0800, John Smith 
> > I looked in mysql.config.pl and no "localhost" :(
> "mysql.config.pl" from what software damned?
> "mysql.config.pl" does not exist in context of mysql

It exists in the following folder on my Win8 box:

/Program Files (x86)/MySQL/MySQL Server 5.5/bin

Yes, apparently it does exist in context of mysql. At any rate, it didn't work. 
Is there another config file in there? 


That is a Perl script, meant for, under Windows, configuration _reporting_. One 
directory up look for "my.ini". As for its meaning, maybe 
 helps, 
after
mysqladmin -uroot -p variables > (somepathyoumayuse)
OR
mysql -uroot -p -e"show global variables" > (somepathyoumayuse)
for showing all variables that may be in that file.

For me the commands work also with "-hlocalhost" or "-h127.0.0.1" in the 
command-line, as expected.

In any case, your original error message, although it is a MySQL error, as 
Harald said does not look like something straight from MySQL client 
("mysql.exe"), but something passed through another. What did you enter to get 
it?

Note this quote:
The error (2003) Can't connect to MySQL server on 'server' (10061) indicates 
that the network connection has been refused. You should check that there is a 
MySQL server running, that it has network connections enabled, and that the 
network port you specified is the one configured on the server. 


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

Re: Date comparison help

[hsv]

 2013/10/22 12:20 -0400,  
I recently upgraded a local MySQL installation to 5.5.32 and am trying to 
figure out why the following query won't work as expected anymore. I'm just 
trying to compare a set of dates to NOW() but since the upgrade, these don't 
seem to work as expected.

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY), 
NOW(), 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)STR_TO_DATE()
 returns a 
DATETIME
 value if the format string contains both date and time parts, or a 
DATE
 or 
TIME
 value if the string contains only date or time parts. "
How really does it decide which type to return? It is wrong if the decision is 
based whether all the hour, minute, and second are 0 or not.  


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

Re: MySQL Community Server 5.7.2 has been released (part 1)

[hsv]

MySQL fans,

 2013/09/21 18:04 +0200, Bjorn Munch 
MySQL Server 5.7.2 (Milestone Release) is a new version of the world's
most popular open source database. This is the second public milestone
release of MySQL 5.7. 

Is this a good replacement for that 5.5.8 that I long ago downloaded and 
installed? or is it better to go for a 5.6, or an older 5.7?

 2013/09/20 15:47 +0530, Sunanda Menon 
MySQL Server 5.6.14, a new version of the popular Open Source
Database Management System, has been released. MySQL 5.6.14 is
recommended for use on production systems. 

Is this better for me than any 5.7?


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

RE: Change to MySQL Community Server 5.7.2?

[hsv]

 2013/10/25 00:08 +, Rick James 
There's an old saying, "If it ain't broke, don't fix it."

Why _might_ 5.6.x or 5.7.x be "better for you"?  Sure there might be some 
features you might want, might be some performance improvements that you might 
notice, etc.  And there might be some regressions that will bite you.  
Fortunately, regressions are rare.

You should probably upgrade to 5.6 soon, simply to avoid having to do a double 
upgrade when you eventually go to 5.7. 

Everyone wants the computer where the database is changed to a newer: good time 
for installing a newer MySQL, too. In 5.6 there is a feature of interest to me: 
DATETIME (I wish it were DATE) also allows DEFAULT CURRENT_TIMESTAMP and ON 
UPDATE CURRENT_TIMESTAMP; and in 5.7 a bug that I reported, relevant to my 
code, was amended.

Now, in the announcement, it is called "public milestone release of MySQL 5.7": 
what is that, and how different from "generally available"?


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

Re: Update Column in table only if variable is Not NULL

[hsv]

 2013/10/28 21:23 +, Neil Tompkins 
Basically the snippet of the UPDATE statement I provided shows updating only 1 
field.
However in my live working example, I have about 20 possible fields that 
"might" need to be updated if the variable passed for each field is NOT NULL. 

Well, maybe something as loathsome as this:

UPDATE T SET F1 = NOW(), F2 = IFNULL(@F2, F2), FF3 = IFNULL(@FF3, FF3), FF4 = 
IFNULL(@FF4, FF4), F5 = IFNULL(@F5, F5), 

but if all are NULL F1 will be misleading. I can think only of
NOT (@F2 IS NULL AND @FF3 IS NULL AND @FF4 IS NULL AND @F5 IS NULL )
or making F1 a variable that takes ON UPDATE CURRENT_TIMESTAMP: I have read 
that MySQL checks every UPDATE for actual change, and only then changes such an 
F1 when something actually else changes.


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

Re: Update Column in table only if variable is Not NULL

[hsv]

 2013/10/29 11:35 -0400, Shawn Green 
My favorite technique is the COALESCE function for this on a column-by-column 
basis

SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) 

but if MyVariable is NULL, FieldName1 reflects the attempt to change, not 
change.


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

Re: Another query question...

[hsv]

 2013/11/04 09:32 -0800, Jan Steinman 
I noticed that I have similar queries that work as expected. The difference 
appears to be that every query that is broken uses " WITH ROLLUP", and removing 
this makes them behave as expected.

Is this a known bug? Should I submit it as such?

If someone would be so kind as to point me to the bug system, I'll crawl around 
in there to see if it's a known problem. 

There is a bug that I about a half year ago reported, 
http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with "WITH 
ROLLUP" is not always NULL, but is instead the foregoing string in the same 
field. I suspect that other bugs with missing NULL found by searching for 
ROLLUP are the same problem. (note link titled "Affects Me"!)

Another, one year ago reported, bug of mine was handled in 5.7.2, but this one 
not.


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

Re: Another query question...

[hsv]

 2013/11/08 17:35 -0800, Jan Steinman 
Okay, I think I found it:
http://bugs.mysql.com/bug.php?id=47713

I added a comment with a link to a page I set up to show the behaviour on my 
system.
http://www.ecoreality.org/wiki/WITH_ROLLUP_problem

It was submitted in 2009, severity "Critical," triaged "Serious," and still not 
fixed! 

Yea, and the bug to which I referred is also evident in the same report, where 
SumQuantity is 78, and not all the rest is NULL. 'Twouldn't surprise me if the 
bugs are akin.


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

a Java-connector

[hsv]

I have MySQL 5.5.8 under Windows Vista, and I am minded to write Java programs 
to talk to the server. I believe that a connecter is needed for that, something 
with ODBC in the name--which version is best for my use?


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

Re: LOAD DATA INFILE with space after quote but before comma

[hsv]

 2013/12/18 11:07 -0500, Anthony Ball 
I ran across a curious issue, I'd call it a bug but I'm sure others would
call it a feature.

I have a csv file with space between the " and , and it causes MySQL to eat
that field and the field after it as a single field. Is there a setting I
can use to remedy this or do I just have to make sure no whitespace
intrudes? 

Well, strictly speaking, it is a bug, in your file. If you can keep that from 
happening that is best, because in a CSV file the quotemark may appear only 
first, last, or next to a separator, unless it quotes another quote-mark.

Otherwise, if it is consistent as in Dhaval Jaiswal s (2), only do as he 
suggests.


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