Re: Which choice of mysql??

[Peter Brawley]

On 5/24/2017 12:21, Papa wrote:
I am learning Java and I'd like to use MySQL to create, delete and 
update databases using Java. I have done this with C++ SQLite, but 
now, as I said, I want to port my code to Java [not easy for a Java 
nubby]. However, the MySQL installer 
[mysql-installer-web-community-5.7.18.1] has several choices of which 
I am not familiar. I don't use MS-Office at all, I use OpenOffice, so, 
when at the "Choosing a Setup Type" window, 'Developer Default' 
requires some MS products I do not have. 


You mean MySQL for Visual Studio and MySQL for Excel? I think you can 
uncheck them.


I tried the other options, but I think they also require MS Office 
and/or VS.


What other options require Office or VS?


What would you recommend me in this case?


You'll want Connector/J (under "Connectors"?).

PB



TIA





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

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 20:47, David Mehler wrote:

Hello,

Thanks. Here's the create statements for virtual_domains,
virtual_users, and the one that isn't working lastauth:

CREATE TABLE `virtual_domains` (
   `id` int(11) NOT NULL auto_increment,
   `name` varchar(50) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (
   `id` int(11) NOT NULL auto_increment,
   `domain_id` int(11) NOT NULL,
   `user` varchar(40) NOT NULL,
   `password` varchar(128) NOT NULL,
   `quota` bigint(20) NOT NULL DEFAULT 256,
   `quota_messages` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   UNIQUE KEY `user` (`user`),
   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (
`user` varchar(40) NOT NULL,
`remote_ip` varchar(18) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`user`),
FOREIGN KEY (`user`) REFERENCES virtual_users(`user`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Again, that works in 5.6 and 5.7, so if it doesn't work for you, there 
is something wrong in one of your files. Did you try recreating 
virtual_users?


PB

-



Thanks.
Dave.


On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:

On 4/24/2017 18:16, David Mehler wrote:

Hello,

I'm running Mysql 5.7.18.

My virtual_users are working fine, it's the new table that isn't. Or
am I missing something?

MySQL says it cannot find an index which the table's create statement
declares. If that's the create statement that created the table, the
table's corrupted.

PB



Thanks.
Dave.


On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:

On 4/24/2017 17:41, David Mehler wrote:

Hello,

Here's the output. I hope it helps.

root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
   -> `user` varchar(40) NOT NULL,
   -> `remote_ip` varchar(18) NOT NULL,
   -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
   -> PRIMARY KEY (`user`),
   -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
CASCADE
   -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1215 (HY000): Cannot add foreign key constraint
root@localhost 

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 15:28, Peter Brawley wrote:

On 4/24/2017 13:59, David Mehler wrote:

Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


Well it's very unlikely InnoDB made that up, it's probably in one of 
your Create texts.


Ah, an error cascade, as Shawn Green noticed, there's a typo in my 
suggested command, should be ...


show engine innodb status;

PB

-



PB

-




This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:

On 4/24/2017 12:28, David Mehler wrote:

Hello,

Here's the create table sand error message.

root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
-> `user` varchar(40) NOT NULL,
-> `remote_ip` varchar(18) NOT NULL,
-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`user`),
-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1215 (HY000): Cannot add foreign key constraint


For the table it's referencing here it is:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
 `password` varchar(32) NOT NULL,
 `quota` bigint(20) NOT NULL DEFAULT 256,
  `quota_messages` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   Hope this helps.

Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

`id` int(11) NOT NULL auto_increment,

`domain_id` int(11) NOT NULL,

`user` varchar(40) NOT NULL,

`password` varchar(32) NOT NULL,

`quota` bigint(20) NOT NULL DEFAULT 256,

`quota_messages` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

UNIQUE KEY (`user`),

FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE 
CASCADE


) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

`user` varchar(40) NOT NULL,

`remote_ip` varchar(18) NOT NULL,

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,

PRIMARY KEY (`user`),

FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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








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

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 13:59, David Mehler wrote:

Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


Well it's very unlikely InnoDB made that up, it's probably in one of 
your Create texts.


PB

-




This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:

On 4/24/2017 12:28, David Mehler wrote:

Hello,

Here's the create table sand error message.

root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
-> `user` varchar(40) NOT NULL,
-> `remote_ip` varchar(18) NOT NULL,
-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`user`),
-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1215 (HY000): Cannot add foreign key constraint


For the table it's referencing here it is:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
 `password` varchar(32) NOT NULL,
 `quota` bigint(20) NOT NULL DEFAULT 256,
  `quota_messages` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   Hope this helps.

Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

`id` int(11) NOT NULL auto_increment,

`domain_id` int(11) NOT NULL,

`user` varchar(40) NOT NULL,

`password` varchar(32) NOT NULL,

`quota` bigint(20) NOT NULL DEFAULT 256,

`quota_messages` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

UNIQUE KEY (`user`),

FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

`user` varchar(40) NOT NULL,

`remote_ip` varchar(18) NOT NULL,

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,

PRIMARY KEY (`user`),

FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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





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

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 12:28, David Mehler wrote:

Hello,

   Here's the create table sand error message.

   root@localhost [(none)]> use mail;
   Database changed
   root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
   -> `user` varchar(40) NOT NULL,
   -> `remote_ip` varchar(18) NOT NULL,
   -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
   UPDATE CURRENT_TIMESTAMP,
   -> PRIMARY KEY (`user`),
   -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
   -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   ERROR 1215 (HY000): Cannot add foreign key constraint


   For the table it's referencing here it is:

   CREATE TABLE `virtual_users` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `user` varchar(40) NOT NULL,
`password` varchar(32) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
 `quota_messages` int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user` (`user`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Hope this helps.


Adding in a dummy Create Table for the missing referenced 
`virtual_domains`, we have ...


drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

  id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

  `id` int(11) NOT NULL auto_increment,

  `domain_id` int(11) NOT NULL,

  `user` varchar(40) NOT NULL,

  `password` varchar(32) NOT NULL,

  `quota` bigint(20) NOT NULL DEFAULT 256,

  `quota_messages` int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`),

  UNIQUE KEY (`user`),

  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

  `user` varchar(40) NOT NULL,

  `remote_ip` varchar(18) NOT NULL,

  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,

  PRIMARY KEY (`user`),

  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your 
setup, right after the error occurs execute ...


show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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

Re: Can not add foreign key constraints

[Peter Brawley]

On 4/24/2017 9:18, David Mehler wrote:

Hello,

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(128) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  This does not:

  CREATE TABLE IF NOT EXISTS `lastauth` (
  `user` varchar(40) NOT NULL,
  `remote_ip` varchar(18) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  CURRENT_TIMESTAMP,
  PRIMARY KEY (`user`),
  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Can anyone spot the situation?


Let's see the CREATE TABLE statement for the referenced table, and the 
error message.


PB

-



  Thanks.
  Dave.




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

Re: Changing a field's data in every record

[Peter Brawley]

On 2/18/2017 15:13, debt wrote:

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single field in 
every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format like 
 2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?


If it's a timestamp, it's saved as 2017-02-16 04:58:42, not as you 
showed it, and there's no need to change it, indeed she couldn't. 
Instead, in a query that retrieves the timestamp, use the mysql 
date_format() function to format the timestamp as desired.


PB


   Can this be done solely in MySQL, or will she have to grab the data and then 
manipulate it in PHP or something?

Thanks,
Marc



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

Re: rescue Inno tables from an abandoned data directory?

[Peter Brawley]

On 12/3/2016 14:23, Martin Mueller wrote:

I abandoned a MySQL 5.22 database


There's been 5.0m 5,1, 5,4 (briefly), 5.5, 5.6 and now 5.7. No 5,.2.


  that quite suddenly andthat I wasn’t able to start up again. The data 
directory consists of a mix of ISAM and Inno tables.


You mean MyISAM?


   I  was able to copy the ISAM tables into a new 5.6 version, and they work.

I understand that INNO tables are different because different tables share a 
common table space.


Not just that.


  The MySQL documentation refers to a “cold backup,” where you copy the 
separate files after a “slow shutdown.”  It doesn’t tell you what to do with 
them after you’ve put them in a “safe place.”


This refers to 
https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/innodb-backup.html? 
Copy into identical folders on the new machine, or to corresponding 
folders named in a new my.cnf.




In my case, I can reproduce Time machine backups of data directories at varying 
times. At one point I was able to replace the non-working installation with an 
earlier installation, but then it failed unpredictably.

Are the Inno tables on Time Machine useless, or can I rescue data from them?


Mysqlbackup (Enterprise) and Percona Xtrabackup are known to do reliable 
hot backups.


PB

-



I’ll be grateful for help



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

Re: a curse on OS sierra and MySQL?

[Peter Brawley]

On 12/3/2016 13:58, Martin Mueller wrote:

I was able to install a version of MySQL 5.6 on OS Sierra. It appears that the 
“launchdaemon’ method works while the mysql.server start/stop method does not 
work.  In retrospect I should have seen that, but I also think that the 
official documentation could and should be more explicit about what is a 
significant change in Apple’s start/stop routines.


If you mean that seriously, it needs to be more specific.

PB

-



On 12/3/16, 12:43 PM, "Peter Brawley" <peter.braw...@earthlink.net> wrote:

 On 12/2/2016 17:58, Martin Mueller wrote:
 > Alas, running the stop and start commands under sudo makes zero 
difference.
 
 ?! The cited page recommends more than sudo starts and stops, eg ...
 
 |unset TMPDIR mysql_install_db |
 
 Did you try that? Did you check the pid setting in my.cnf, eg

 pid-file=/var/run/mysqld/mysqld.pid? I believe you need to ensure that
 the pid file specified in my.cnf exists and that the mysql daemon owns
 it ...
 
 mkdir /var/run/mysqld

 touch /var/run/mysqld/mysqld.pid
 chown -R mysql:mysql /var/run/mysqld
 
 Also see

 
https://urldefense.proofpoint.com/v2/url?u=http-3A__superuser.com_questions_159486_how-2Dto-2Dkill-2Dprocess-2Din-2Dmac-2Dos-2Dx-2Dand-2Dnot-2Dhave-2Dit-2Drestart-2Don-2Dits-2Down=CwIDaQ=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=Rp61bfD4ngoSU50qebNy37Nmv34OSqdiU4Sigj8b9zI=RDN0din-b9O7hEkNJOKe1CbYe_5MipeeuN2oeOMsWfI=
 
 > This is a very frustrating problem, and I hope somebody in the MySQl documentation department will take a look at it. It’s  cleary a problem that has been around for years because the Web is full of complaints and tips. But there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. And there is nothing in the MySQL documentation that draws attention to the probem.

 >
 > In my case, I’m double frustrated because some months ago my MySQL 
application broke around this problem, and then a couple of weeks ago it cured 
itself when I somewhat arbitrarily picked up an earlier version of my installation 
from Time Machine.
 That suggests the problem arose from a change in your app, or a setting
 change that occurred in a MySQL upgrade. To show that this is a common
 problem that MySQL docs ought to address, you'll need to identify the
 setting that's gone awry.
 
 PB
 
 




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

Re: a curse on OS sierra and MySQL?

[Peter Brawley]

On 12/2/2016 17:58, Martin Mueller wrote:

Alas, running the stop and start commands under sudo makes zero difference.


?! The cited page recommends more than sudo starts and stops, eg ...

|unset TMPDIR mysql_install_db |

Did you try that? Did you check the pid setting in my.cnf, eg 
pid-file=/var/run/mysqld/mysqld.pid? I believe you need to ensure that 
the pid file specified in my.cnf exists and that the mysql daemon owns 
it ...


mkdir /var/run/mysqld
touch /var/run/mysqld/mysqld.pid
chown -R mysql:mysql /var/run/mysqld

Also see 
http://superuser.com/questions/159486/how-to-kill-process-in-mac-os-x-and-not-have-it-restart-on-its-own



This is a very frustrating problem, and I hope somebody in the MySQl 
documentation department will take a look at it. It’s  cleary a problem that 
has been around for years because the Web is full of complaints and tips. But 
there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. 
And there is nothing in the MySQL documentation that draws attention to the 
probem.

In my case, I’m double frustrated because some months ago my MySQL application 
broke around this problem, and then a couple of weeks ago it cured itself when 
I somewhat arbitrarily picked up an earlier version of my installation from 
Time Machine.
That suggests the problem arose from a change in your app, or a setting 
change that occurred in a MySQL upgrade. To show that this is a common 
problem that MySQL docs ought to address, you'll need to identify the 
setting that's gone awry.


PB

Re: a curse on OS sierra and MySQL?

[Peter Brawley]

On 12/2/2016 16:59, Martin Mueller wrote:

I have been trying repeatedly and in vain to install the community edition of 
MySQL 5.7.16 on an iMac running OS sierra.  I religiously followed the 
instructions for uninstalling previous versions found at 
http://community.jaspersoft.com/wiki/uninstall-mysql-mac-os-x, and installation 
progresses smoothly and terminates successfully.  Bu the attempt to start the 
server produces the error message  :
ERROR! MySQL server PID file could not be found!


Is 
http://stackoverflow.com/questions/861/pid-error-on-mysql-server-start 
helpful?


PB

-



I have tried this and that, looking at advice on the Web, which is plentiful 
but not always. Nothing works.

I have installed Postgresql, about which I know very little. But the 
installation proceeded smoothly, and turning the server on and off works like a 
charm.

Is it a reasonable conclusion that MySQl and OS 10 just don’t like each other 
very much and that a prudent man is better off with some other database? The 
MySQL documentation is not very helpful in the sense that following it doesn’t 
produce good results.




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

Re: Adding values returned by GREATEST

[Peter Brawley]

On 5/14/2016 11:16, shawn l.green wrote:

Hello Sukhjinder,

On 5/3/2016 8:55 AM, Sukhjinder K. Narula wrote:

Hello,

i have a question regarding the GREATEST function of mysql.

I would like to add the values returned by GREATEST function is
mysql, so a
query is like below:

For example table t has 6 fields with values as follows: A = 1, B =
3, C=0,
D = 0, E = 1 and F = 0 and I run a query:

SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F)

) AS Total
FROM t

The result row I expect is: 3, 1, 4
But I get 3, 1, 6

However when I run the query like below I get correct results as total
being 4:

SELECT
(
GREATEST (1, 3, 0) + GREATEST(0,1,0)

) AS Total

So what I noticed is as I add result from GREATEST function, the
result is
adding 1 for each GREATEST call I have in total. So, if I change my
query
as below:

SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F)

) AS Total
FROM t

The results will be 3, 1, 8

GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) is
calculated as

GREATEST (A, B, C)  = 3 + 1
GREATEST(D, E, F) = 1 +1
GREATEST(D, E, F) = 1 +1

So the total is 8.

I have tried online to search for this type of behaviour but no luck.
Can
anyone please explain this.

Many Thanks,
SK


I attempted to reproduce this problem but was unable to do so.
(testing with 5.7.11)

localhost.test>SELECT @@version;
+---+
| @@version |
+---+
| 5.7.11-enterprise-commercial-advanced |
+---+

localhost.(none)>select greatest(1,3,0), greatest(0,1,0),
greatest(1,3,0)+ greatest(0,1,0) as total
-> ;
+-+-+---+
| greatest(1,3,0) | greatest(0,1,0) | total |
+-+-+---+
|   3 |   1 | 4 |
+-+-+---+
1 row in set (0.00 sec)

localhost.(none)>set @A = 1, @B = 3, @C=0, @D = 0, @E = 1, @F = 0;
Query OK, 0 rows affected (0.00 sec)

localhost.(none)>select greatest(@a,@b,@c), greatest(@d,@e,@f),
greatest(@a,@b,@c)+ greatest(@d,@e,@f) as total;
+++---+
| greatest(@a,@b,@c) | greatest(@d,@e,@f) | total |
+++---+
|  3 |  1 | 4 |
+++---+
1 row in set (0.00 sec)

localhost.(none)>create database test;
Query OK, 1 row affected (0.00 sec)

localhost.(none)>use test
Database changed
localhost.test>create table t1 (a int, b int, c int, d int, e int, f
int);
Query OK, 0 rows affected (0.23 sec)

localhost.test>insert t1 values (1,3,0,0,1,0);
Query OK, 1 row affected (0.03 sec)

localhost.test>select greatest(a,b,c), greatest(d,e,f),
greatest(a,b,c)+ greatest(d,e,f) as total from t1;
+-+-+---+
| greatest(a,b,c) | greatest(d,e,f) | total |
+-+-+---+
|   3 |   1 | 4 |
+-+-+---+
1 row in set (0.00 sec)

localhost.test>select greatest(a,b,c)+ greatest(d,e,f)+
greatest(d,e,f) as total from t1;
+---+
| total |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

Can you provide a more complete test case?
Can you tell us which version of MySQL you are using?


He asked this in the Newbie forum last month. The column is Enum, with 
whose ambiguities Greatest() can produce odd-looking arithmetic ...


drop table if exists t;
create table t(i enum('2','1','3'), j enum('5','2','8') );
insert into t values('1','1');
select greatest(i,j) from t;
+---+
| greatest(i,j) |
+---+
| 5 |
+---+
select greatest(i+0,j+0) from t;
+---+
| greatest(i+0,j+0) |
+---+
| 2 |
+---+

PB


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

Re: Lost Connection Upon Loading Dump

[Peter Brawley]

On 4/21/2016 10:51, Stephen R Guglielmo wrote:

Hello,

I have a empty db that I'm trying to load a .sql file (created via
mysqldump) into. The dump has 791611 lines and is 807 MB. Loading the
dump is consistently failing at line 1763. Line 1763 is an INSERT
statement. The line is 95610 characters long.

The error is:
ERROR 2013 (HY000) at line 1763: Lost connection to MySQL server during query

About 40 tables are restored correctly from the dump prior to it
failing at this line.

Based on advice from posts I've found on the internet, I've added the
follow settings to my.cnf
net_read_timeout=60
# 16 MB
max_allowed_packet=16777216

I added these to both the [mysqld] and [mysqld_safe] sections to be
sure. I restarted mysqld, but still get the same error upon loading
the dump.

I'm not seeing anything in my error log (do I need to enable more
verbose error logging?).

MySQL Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

I would appreciate any advice on figuring this out.


Slow: change the mysqldump params to turn off multiple inserts per
statement, and run the dump again.

Faster: grow the mysqldump net_buffer_length setting, and the mysqld setting of 
the same name, to accommodate the largest multiple insert. You may also have to 
grow max_allowed_packet.

PB

-




Thanks,
Steve




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

Re: commands out of sync, you can't run this command now'

[Peter Brawley]

On 1/8/2016 3:14, Nitin Mehta wrote:

Looks like some kind of locking mechanism in the application. Should not be a 
database issue.

 On Friday, January 8, 2016 10:47 AM, geetanjali mehra 
 wrote:
  


  Hi all,

Suddenly I have started getting below error from our web site:

'commands out of sync, you can't run this command now'

This error is generated sometimes when opening a page ,and immediately
after refreshing the page, error gone.

Can you please help me to troubleshoot this issue, so that above error
disappears permanently? Is it a database issue or application side issue?


http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html

PB

-




Best Regards,
Geetanjali Mehra
Senior Database Administrator






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

Re: help with query to count rows while excluding certain rows

[Peter Brawley]

On 12/31/2015 0:51, Larry Martell wrote:

I need to count the number of rows in a table that are grouped by a
list of columns, but I also need to exclude rows that have more then
some count when grouped by a different set of columns. Conceptually,
this is not hard, but I am having trouble doing this efficiently.

My first counting query would be this:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id

But from this count I need to subtract the count of rows that have
more then 50 rows with a different grouping:

SELECT count(*)
FROM cst_rollup
GROUP BY target_name_id, ep, wafer_id
HAVING count(*) >= 50

As you can see, the second query has wafer_id, but the first query does not.

Currently I am doing this in python, and it's slow. In my current
implementation I have one query, and it selects the columns (i.e.
doesn't just count), and I have added wafer_id:

SELECT target_name_id, ep, roiname, recipe_process,
recipe_product, recipe_layer, f_tag_bottom,
measname, recipe_id, wafer_id
FROM cst_rollup

Then I go through the result set (which can be over 200k rows) and I
count the number of rows with matching (target_name_id, ep, wafer_id).
Then I go through the rows again and regroup them without wafer_id,
but skipping the rows that have more then 50 rows for that row's
(target_name_id, ep, wafer_id).

Is this clear to everyone what I am trying to do?


If I've understand this correctly, the resultset you wish to aggregate 
on is ...


select target_name_id, ep, wafer_id
from cst_rollup a
left join (   -- exclude rows for which wafer_id count >= 50
  select name_id, ep, wafer, count(*) n
  from cst_rollup
  group by target_name_id, ep, wafer_id
  having n >= 50
) b using ( target_name_id, ep, wafer_id )
where b.target_name is null ;

If that's so, you could assemble that resultset in a temp table then run 
the desired aggregate query on it, or you could aggregate on it directly 
as a subquery.


PB

-


I'd like to do this all in sql with count because then I do not have
to actually return and parse the data in python.

Can anyone think of a way to do this in sql in a way that will be more
efficient then my current implementation?


Thanks!
-Larry




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

Re: Query Help...

[Peter Brawley]

On 2015-10-20 12:54 PM, Don Wieland wrote:

Hi all,

Trying to get a query working:

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc

FROM hiv_transactions ht

LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id

WHERE ht.donor_id = 730 AND ht.tr _date BETWEEN "2014-01-01 00:00:00" AND 
"2014-12-31 23:59:59"
ORDER BY ht.tr _date DESC, ht.rec_code ASC;



I am only showing one row of the “hiv_transactions” table when there are 
multiple rows.

On the GROUP_CONCAT I am trying to get a comma delineated list of the child 
rec_code with no duplicates

Appreciate any help. Hopefully a small mod ;-)


Group_Concat() is an aggregating function, so you need to Group By the 
column(s) on which you wish to aggregate, and for valid results you need 
to limit Selected columns to those on which you're aggregating plus 
those columns that have unique values for your aggregating columns..


PB





Don Wieland
D W   D a t a



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

Re: Trouble with LEFT JOIN

[Peter Brawley]

On 2015-09-04 9:40 AM, Richard Reina wrote:

I have the following two tables;

mysql> select * from challenge;
+++-++--+-+--+
| ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
+++-++--+-+--+
|  1 |   1 |  1 | Fall  | 2015 |   1175
|
|  2 |   1 |  4 | Fall  | 2015 |   1175
|
|  3 |   1 |  3 | Fall  | 2015 |   1175
|
|  4 |   1 |10 | Fall  | 2015 |   1175 |
|  5 |   1 |13 | Fall  | 2015 |   1175 |
|  6 |   1 |  2 | Fall  | 2015 |   1175
|
++--+--++---+-+
6 rows in set (0.00 sec)

mysql> select * from patrocinio;
+++---+-+
| ID | PTRN_ID | CHLNG_ID | AMOUNT |
+++---+-+
|  1 | 1 | 1 |   1.00  |
|  2 | 4 | 3 |   2.00  |
|  3 | 3 | 6 |   1.00  |
++---+-++
I would like to select all rows from challenges which are NOT linked to a
patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
challenges.

I am trying to go about this with a LEFT JOIN query but it does not seem to
be working for me.

mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;


... where p.chlng_id IS NULL;

(Look up exclusion joins)

PB

-


Empty set (0.00 sec)

Instead of getting rows 2 through 6 of challenges I get no rows.

Any help on how to correctly do this query would be greatly appreciated.

Thanks




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

Re: Trouble with LEFT JOIN

[Peter Brawley]

On 2015-09-04 11:39 AM, Richard Reina wrote:


2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net 
<mailto:peter.braw...@earthlink.net>>:


On 2015-09-04 9:40 AM, Richard Reina wrote:

I have the following two tables;

mysql> select * from challenge;
+++-++--+-+--+
| ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
+++-++--+-+--+
|  1 |   1 |  1 | Fall | 2015
|   1175
|
|  2 |   1 |  4 | Fall | 2015
|   1175
|
|  3 |   1 |  3 | Fall | 2015
|   1175
|
|  4 |   1 |10 | Fall   | 2015 | 
 1175 |
|  5 |   1 |13 | Fall   | 2015 | 
 1175 |

|  6 |   1 |  2 | Fall | 2015
|   1175
|
++--+--++---+-+
6 rows in set (0.00 sec)

mysql> select * from patrocinio;
+++---+-+
| ID | PTRN_ID | CHLNG_ID | AMOUNT |
+++---+-+
|  1 | 1 | 1 |   1.00   |
|  2 | 4 | 3 |   2.00   |
|  3 | 3 | 6 |   1.00   |
++---+-++
I would like to select all rows from challenges which are NOT
linked to a
patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
challenges.

I am trying to go about this with a LEFT JOIN query but it
does not seem to
be working for me.

mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;


... where p.chlng_id IS NULL;

(Look up exclusion joins)

PB

-



Hi Peter,

Thanks for the reply. Along those lines I have also tried:

select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio 
WHERE p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL;


But that's not working either.


drop table if exists patrocinio, challenge;
create table challenge(
  id smallint,plr_id smallint,acc_type_id smallint,
  season char(4), year year, char_id smallint );
insert into challenge values
(  1 ,   1 ,  1 , 'Fall'  , 2015 ,   
1175),
(  2 ,   1 ,  4 , 'Fall'  , 2015 ,   
1175),
(  3 ,   1 ,  3 , 'Fall'  , 2015 ,   
1175),
(  4 ,   1 , 10 , 'Fall'  , 2015 ,   
1175 ),
(  5 ,   1 , 13 , 'Fall'  , 2015 ,   
1175 ),
(  6 ,   1 ,  2 , 'Fall'  , 2015 ,   
1175);

create table patrocinio(
  id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) );
insert into patrocinio values
(  1 , 1 , 1 ,   1.00  ),
(  2 , 4 , 3 ,   2.00  ),
(  3 , 3 , 6 ,   1.00  );

select c.id , p.chlng_id
from challenge c
left join patrocinio p on c.id=p.chlng_id ;
+--+--+
| id   | chlng_id |
+--+--+
|1 |1 |
|3 |3 |
|6 |6 |
|2 | NULL |
|4 | NULL |
|5 | NULL |
+--+--+

select c.id , p.chlng_id
from challenge c
left join patrocinio p on c.id=p.chlng_id
where p.chlng_id is null;
+--+--+
| id   | chlng_id |
+--+--+
|2 | NULL |
|4 | NULL |
|5 | NULL |
+--+--+

PB

Re: Sequence Numbering

[Peter Brawley]

On 2015-06-29 7:03 PM, Johnny Withers wrote:

Hello all,

I have a tabled defined:

CREATE TABLE `store_seq` (
   `seq_type_id` smallint(3) unsigned NOT NULL DEFAULT '0',
   `store_id` int(10) unsigned NOT NULL DEFAULT '0',
   `loan_model_id` int(10) unsigned NOT NULL DEFAULT '0',
   `store_bank_id` int(10) unsigned NOT NULL DEFAULT '0',
   `seq_id` int(10) unsigned NOT NULL DEFAULT '0',
   `check_format` varchar(50) DEFAULT NULL,
   UNIQUE KEY `idx_contract`
(`loan_model_id`,`seq_type_id`,`store_id`,`seq_id`,`store_bank_id`),
   KEY `idx_chk`
(`store_bank_id`,`seq_type_id`,`store_id`,`seq_id`,`loan_model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The following SQL is used to generate and initialize the sequence number
for the idx_contract key:

UPDATE store_seq SET seq_id=LAST_INSERT_ID(seq_id+1) WHERE
loan_model_id=224 AND seq_type_id=2 AND store_id=179;

If the return value of the UPDATE stmt is zero, the following stmt is ran:

INSERT INTO store_seq(seq_type_id,store_id,loan_model_id,seq_id)
VALUES(2,179,224,1000)

This is working great, and has been for many years; however, today I
noticed it was not working on a particular MySQL server.


To guarantee such a result, you need a table of sequential numbers and a 
transaction which marks one of its numbers as used and uses that number 
in the write to the other table.


PB




The server where I have verified it as working is:
5.5.42-log

The server where it is not working is:
5.5.32-enterprise-commercial-advanced

This same code is used on about 10 other mysql servers where it is working
fine. I have verified the table schema is the same on both mysql servers.
Anyone have any insight as to why it may not be working on that one server?

Thanks,





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

Re: Issue with em dash character

[Peter Brawley]

On 2015-06-03 2:55 PM, Robert Voliva wrote:

We're finding that, when working with the em dash character, the LEFT and
LENGTH functions don't work well together.  This query shows trying to
strip off the last character from a string containing an em dash:

mysql select LEFT('031492349−0002,', LENGTH('031492349−0002,') - 1),
LENGTH('031492349−0002,'), LENGTH('031492349-0002,');
++-+---+
| LEFT('031492349−0002,', LENGTH('031492349−0002,') - 1) |
LENGTH('031492349−0002,')   | LENGTH('031492349-0002,') |
++-+---+
| 031492349−0002,|
  17 |15 |
++-+---+
1 row in set (0.06 sec)

Is this a bug?  If it's a feature, what could we do instead to get around
this issue?


The last of the four '031...' strings in your query diverges from the 
others at the en-dash. In the earlier strings, the dash is a multibyte 
character whose hex value is E2, whereas the dash in the later string is 
the ASCII dash value 2D.


Since the earlier dashes are 3-byte chars, octet_length() returns 17 
instead of 15.


PB

-


Thanks,
Robert Voliva




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

Select one value, not the other

[Peter Brawley]

On 2015-04-29 12:20 AM, Olivier Nicole wrote:

SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2


Assuming a table named t ...

One way, an exclusion join:

select a.*
from t a
left join t b on a.item_number=b.item_number and b.data_value=2
where a.data_value=1 and b.item_number is null;

Another way, with a semi-join:

select a.*
from t a
where a.data_value=1
  and not exists (select data_value from t b where 
b.item_number=a.item_number and data_value=2);


PB




Olivier


On Wed, April 29, 2015 06:30, Olivier Nicole wrote:

Hi,

I am sure that it is feasible with MySQl, and I am sure that's a newbie
question, but my SQL skills are limited...

I have a table where each record is made of one item_number and one
data_value.

If one item has several values, there are several records with the same
item_number and a different data_value.

What is the command to select all the records where an item_number has
the data 1 but not the data 2?

Thanks in advance,

Olivier

--
Mogens Melander
+66 8701 33224


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: forum vs email

[Peter Brawley]

On 2014-12-09 9:55 AM, Johan De Meersman wrote:

- Original Message -

From: Sándor Halász h...@tbbs.net
Subject: Re: forum vs email
That is, this list, right? What does it lack (besides readers)?

This list interacts with the forums on mysql.com?


Nope.

PB

-


Every thread here matches one on there, and vice versa? (Honest question; I 
hardly ever visit the fora - but Shawn's earlier mention that he only time for 
one, not both, makes me think not so.)





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

Re: table-for-column

[Peter Brawley]

On 2014-12-04 9:56 PM, shawn l.green wrote:



On 12/1/2014 6:09 AM, Johan De Meersman wrote:

- Original Message -

From: peter brawley peter.braw...@earthlink.net
Subject: Re: table-for-column


I wonder if anyone knows why sites like Stack Overflow and those of 
ours

I mentioned are seeing more volume, while this list and all MySQL fora
are seeing much, much less.


The major benefit I see on StackOverflow and the like, is the rating 
system on the comments, and to some extent the rating system for users.


On the other hand, I find that the signal-to-noise ratio on older 
media like mailing lists and IRC tends to be much more favourable, 
presumably because it is where the dinosaurs dwell :-)





A lot of new users may only use the MySQL Forums 


Forum use is radically down also.

PB

and not even know about this mailing list. I guess this email-based 
peer-to-peer exchange is slowly disappearing into the background like 
the old usenet newsgroups, eh?


http://forums.mysql.com/

My problem is a lack of time. I can monitor the mailing lists or the 
forums but rarely both while still doing my regular job of handling 
the official service requests.





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

Re: table-for-column

[Peter Brawley]

On 2014-11-27 9:31 AM, h...@tbbs.net wrote:

2014/11/26 20:06 -0600, Peter Brawley 

Why do you call it a hack, you get outta bed on the wrong side?


2014/11/27 14:08 +0100, Johan De Meersman 

Doesn't really belong on the list; but I'd love to hear reasonable arguments 
why that would be a bad thing in and of itself. Why is it that webdevs are so 
condescending about tables?


Excellent question (though not for this list).

Re how our MySQL tips aggregator page was written: I think the only 
relevant MySQL point may be that it's driven by an edge list model of a 
hierarchy implemented in a pair of MySQL tables.


I wonder if anyone knows why sites like Stack Overflow and those of ours 
I mentioned are seeing more volume, while this list and all MySQL fora 
are seeing much, much less.


PB

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

Re: MySQL dying?

[Peter Brawley]

On 2014-11-24 3:24 PM, Neil Tompkins wrote:

Personally I think people, myself included are using other resources like 
stackoverflow to get answers to my MySQL questions.


Visits to our MySQL article pages (www.artfulsoftware.com/queries.php, 
http://www.artfulsoftware.com/infotree/mysqlquerytree.php, 
www.artfulsoftware.com/infotree/mysqltips.php, 
www.artfulsoftware.com/infotree/mysqltiptree.php) went up 10% from 
2012-2013 and have gone up 20% from 2013-2014.


PB

-





On 24 Nov 2014, at 17:27, Michael Dykman mdyk...@gmail.com wrote:

Please gentlemen,

It is a valid question if a somewhat hackneyed one.

MySQL continues to live on in many forms but obviously, would have to lose
some ground in the face of the NoSQL solutions which are in vogue. The
concepts of relational data are too powerful to stop being relevant but it
is not longer the universal data store it once was seen as (The aplies to
RDBMS in general).

Let's not ugly up this list (which I have been on for an absurdly long
time) with flame wars.  It certainly does not have the bandwidth it once
did, but flames are a terrible way to boost it.


On Mon, Nov 24, 2014 at 12:04 PM, Ruben Safir ru...@mrbrklyn.com wrote:


On 11/24/2014 10:00 AM, Johan De Meersman wrote:

- Original Message -

From: Ruben Safir ru...@mrbrklyn.com
Subject: Re: MySQL dying?

Well, this mailing list is dead.  This is a mailing list that used to
handle 70+ questions a day, or more.


Is that why you feel the need to troll on posts from two years ago?

If you think it's dead, unsubscribe and go install MSSQL. If not, either

ask a question or stop wasting bandwidth.


Bye now.


No, but I thought it was interesting to see what has happened within the
last 2 years.  Its not an issue of trolling.  But perhaps Oracle could
have learned something from the MYSQL community, which it seems to have
failed to.  If you find that this post was troll, then you've more than
missed the point, you missed the entire boat.

Bandwidth?  This list no longer produces bandwidth...  It has been
abandoned.  What would you have done in those days when we handled so
much mail in this list that there was no time to answer trolls... the
real trolls?

You have a bad attitude man, and it sucks.

Now, back to business..

Ruben

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


--
- michael dykman
- mdyk...@gmail.com

May the Source be with you.



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

Re: Help optimize query.

[Peter Brawley]

Let's see the results of Explain Extended this query,  result of Show 
Create Table cc_member_queue_end_log.


PB

-

On 2014-11-13 1:34 PM, Mimiko wrote:

Hello. I have this table:

 show create table cc_agents_tier_status_log:
CREATE TABLE cc_agents_tier_status_log (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  date_log timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cc_agent varchar(45) NOT NULL,
  cc_agent_tier_status_id tinyint(3) unsigned NOT NULL,
  cc_queue_id tinyint(3) unsigned NOT NULL,
  cc_agent_id int(10) unsigned NOT NULL,
  cc_agent_phone smallint(5) unsigned NOT NULL,
  cc_agent_domain varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md',
  PRIMARY KEY (id),
  KEY IDX_cc_agents_tier_status_log_2 (cc_agent) USING HASH,
  KEY IDX_cc_agents_tier_status_log_3 (date_log),
  KEY FK_cc_agents_tier_status_log_2 (cc_agent_id),
  KEY FK_cc_agents_tier_status_log_3 (cc_queue_id),
  KEY FK_cc_agents_tier_status_log_1 (cc_agent_tier_status_id) 
USING BTREE,

  KEY IDX_cc_agents_tier_status_log_7 (id,date_log),
  CONSTRAINT FK_cc_agents_tier_status_log_1 FOREIGN KEY 
(cc_agent_tier_status_id) REFERENCES cc_agent_tier_status_chart 
(id) ON UPDATE CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_2 FOREIGN KEY 
(cc_agent_id) REFERENCES apacanal.employee (id) ON UPDATE 
CASCADE,
  CONSTRAINT FK_cc_agents_tier_status_log_3 FOREIGN KEY 
(cc_queue_id) REFERENCES cc_queues (id) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii

 show index from cc_agents_tier_status_log:
TableNon_uniqueKey_nameSeq_in_indexColumn_name 
Collation CardinalitySub_partPackedNull Index_type
CommentIndex_comment
cc_agents_tier_status_log0PRIMARY1idA 
23999(null)BTREE(null) (null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_21 
cc_agentA 260(null)BTREE(null)(null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_31 
date_logA 23999(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_21 
cc_agent_idA 2(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_31 
cc_queue_idA 14(null)BTREE(null)(null)
cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_11 
cc_agent_tier_status_id A2(null)BTREE(null)
(null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_7 1
idA23999(null)BTREE(null) (null)
cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_72 
date_logA 23999(null)BTREE(null)(null)


And the query is:
set @enddate:=now();
set @startdate:='2014-11-01';
set @que_id:=-1;
select s.theHour as theHour,avg(s.nrAgents) as nrAgents from
(select date(a.theDateHour) as theDate,extract(hour from 
a.theDateHour) as theHour,count(c.cc_agent_tier_status_id) as nrAgents

from (

select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.*
FROM
( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour
from
( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as 
theDate from (select @i:=-1) as t1
inner join cc_member_queue_end_log b on 1=1 and 
b.id=datediff(@enddate,@startdate)+1 ) as d

left outer join
(SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL 
SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h

on 1=1 ) AS dh
left outer join
cc_agents_tier_status_log as c
on c.date_log=dh.theDateHour where (if(@queue_id0,1,0) or 
if(@queue_id=c.cc_queue_id,1,0))

group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone


) as a
left outer join cc_agents_tier_status_log as c
on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and 
c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and 
c.cc_agent_tier_status_id=2

group by a.theDateHour
order by date(a.theDateHour),extract(hour from a.theDateHour))
as s
group by s.theHour
order by s.theHour;


This query takes 20 seconds to populate.

Table cc_agents_tier_status_log contains log entries of agent_id 
login/logout per queue per phone. status_id can have value 1 (logged 
out) and 2 (login) at date_log datetime.


The resulting table must contain average number of agents logged in at 
every hour per startdate to enddate.


Hope for some hints. Thank you.



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

Re: Returning years of data by month

[Peter Brawley]

On 2014-11-09 10:37 AM, Steffan A. Cline wrote:

Looking for suggestions on how to best pull some data.

I need to do some calcs but pull the data by year and month to make a
table like such.

201220132014
Jan $243$567$890
Feb $123$456$908
Mar S(  S(  S(
Apr
May


Assuming date column d and column being summed named qty ...

select monthname(d), sum( if( year(d)=2012, qty, 0 ) ) as '2012', sum( 
if( year(d)=2013, qty, 0 ) ) as '2013', sum( if( year(d)=2014, qty, 0 ) 
) as '2014'

from tbl
group by month(d)
order by month(d);

See Pivot tablesat Common MySQL Queries 
http://www.artfulsoftware.com/queries.php


PB



S(


I can get the data to be ordered by year, month but as you know that is
not easily conducive to putting it into an HTML table without some middle
ware storing and iterating through found sets.

Suggestions?


Thanks,
Steffan

---
T E L  6 0 2 . 7 9 3 . 0 0 1 4 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Clinestef...@execuchoice.net
http://www.ExecuChoice.net Phoenix, Arizona USA
   
---

Re: Query with variable number of columns?

[Peter Brawley]

On 2014-10-08 1:38 PM, Jan Steinman wrote:

I often use CASE WHEN ... to pivot tables. For example, splitting sales data by 
year:

   SELECT
 s_product.name AS `Product`,
 SUM(CASE WHEN YEAR(sales.Date)='2007' THEN sales.Total ELSE NULL END) AS 
`2007`,
 SUM(CASE WHEN YEAR(sales.Date)='2008' THEN sales.Total ELSE NULL END) AS 
`2008`,
 SUM(CASE WHEN YEAR(sales.Date)='2009' THEN sales.Total ELSE NULL END) AS 
`2009`,
 ...
   WHERE dynamic predicate that only has results in one year

However, this pattern will often result in numerous empty columns -- empties 
that would not be there had the table not been pivoted.

What techniques do *you* use for avoiding this anti-pattern?


Non-procedural SQL is an incomplete computer language; it can't do that. 
MySQL stored procedures are less incomplete, and can do it, but they're 
awkward. I use the app language (eg PHP) to implement such logic.


PB

-


Am I limited to using a separate programming language (PHP, in this case) with 
a separate COUNT(*) query for each possible column, then CASEing the generation 
of the column SQL? Seems awfully ugly!

Thanks in advance for any insight offered!

(And the following came up at random... perhaps I'll just live with a bunch of 
empty columns...)

 In attempting to fix any system, we may damage another that is working 
perfectly well. -- David Holmgren
 Jan Steinman, EcoReality Co-op 





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

Re: extract year-month-day-hour

[Peter Brawley]

On 2014-09-18 5:25 PM, Jopoy Solano wrote:

Hi!

I want to say sorry in advance for my ignorance. I was wondering how can I
extract year-month-day-hour in one go. For example:

From 2014-09-17 12:22:16 to 2014-09-17 12


Is this what you mean?
date_format( tsvalue, '%Y-%m-%d %H' )

PB

-



The reason for this is I'm trying to generate how much data users
historically consume every hour  for each day.

Thank you.

Jopoy




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

Re: mysql Access denied error

[Peter Brawley]

On 2014-05-04 9:17 PM, EdwardKing wrote:

I use mysql to create a database and grant rights to a user hadooptest,then I 
use hadooptest to login mysql and use the database,but it failed. Why raise 
error after I grant rights? How to solve it? Thanks.


The command sequence ...


mysql create database hadooptest;
mysql grant all on hadooptest.* to 'hadoopuser'@'%' identified by '123456';
mysql quit;
mysql -u hadoopuser -p
Enter password:


works fine, so your problem is due to something other than these 
commands, possibly other entries in the mysql.user or mysql.db table.


PB


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

Re: Data masking for mysql

[Peter Brawley]

On 2014-04-15 5:37 AM, reena.kam...@jktech.com wrote:

It can be done by data masking tool itself. Its one time activity, I do not need it 
again  again.


Rilly? If that's so, the data will never be accessed.

'PB


Please suggest data masking tool link.

-Original Message-
From: Reindl Harald h.rei...@thelounge.net
Sent: Tuesday, 15 April, 2014 2:49pm
To: mysql@lists.mysql.com
Subject: Re: Data masking for mysql



Am 15.04.2014 11:08, schrieb reena.kam...@jktech.com:

Yes, we can do it at application level and database level as well.
for example mobile no. is 9878415877, digits can by shuffle(8987148577) or can 
replace with other letter/number(first 6 digits replace with X-- xx5877) by 
using data masking. We can use any one data masking technique at DB level to 
protect our sensitive data from external exposure.
I have sensitive data in existing mysql db. I need to do data masking at DB 
level.
If any tools available for the same please respond

write a simle PHP script iterating the records, read the column, mask
the data and write it back - again: that's not the business of the db-layer



**
img src='http://jktech.com//cms/website_files/image001.jpg' /

p
CONFIDENTIALITY. This e-mail and any attachments are confidential and may also 
be privileged. If you are not the named recipient, please notify the sender 
immediately and do not disclose the contents to another person, use it for any 
purpose, or store or copy the information in any medium. Any views expressed in 
this message are those of the individual sender, except where the sender 
specifically states them to be the views of JK Technosoft Ltd. If you have 
received this e-mail in error please immediately notify the person who has sent 
this email to you with copy to administra...@jktech.com
/p
**





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

Re: Locking a Database (not tables) x

[Peter Brawley]

On 2014-03-23 8:02 PM, David Lerer wrote:

Thanks Shawn, This may work for us with some script changes. We'll take a look.

By the way, too bad we cannot rename a database, or can we?
See http://dev.mysql.com/doc/refman/5.1/en/rename-database.html about removal of a 
dangerous RENMAE DATABASE statement...

David.


David Lerer | Director, Database Administration | Interactive | 605 Third 
Avenue, 12th Floor, New York, NY 10158
Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | 
www.univision.net

-Original Message-
From: shawn l.green [mailto:shawn.l.gr...@oracle.com]
Sent: Friday, March 21, 2014 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: Locking a Database (not tables) x

Hi David.

On 3/21/2014 1:42 PM, David Lerer wrote:

Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances 
that contains many more databases. (i.e. database being a schema or a 
catalogue).
At the beginning of the import script, we first drop all objects in the QA 
database so that it will be a perfect match (object wise) to production.

Is there an easy way to lock the whole database for the duration of the import 
- so that no developers can update the database?
Obviously, I can revoke permissions, but I was wondering whether there is a 
better approach.


If you start with a DROP DATABASE   that will pretty much ensure
that nobody gets back into it.

Then re-create your tables in a new DB (yyy)

As a last set of steps do

CREATE DATABASE 
RENAME TABLE yyy.table1 to .table1, yyy.table2 to .table2,
  (repeat for all your tables).
DROP DATABASE yyy


Remember to similarly rename other database objects to, eg sprocs  funcs.

PB

-




Because this is essentially a metadata flip, the RENAME will be quite
speedy.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

The information contained in this e-mail and any attached

documents may be privileged, confidential and protected from

disclosure. If you are not the intended recipient you may not

read, copy, distribute or use this information. If you have

received this communication in error, please notify the sender

immediately by replying to this message and then delete it

from your system.




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

Re: grouping by the difference between values in rows

[Peter Brawley]

On 2014-01-12 1:17 PM, Larry Martell wrote:

I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?


Could you compute the row-to-row values  write them to a temp table, 
then run the SQL that incorporates that result column?


PB

-






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

Re: grouping by the difference between values in rows

[Peter Brawley]

On 2014-01-12 9:13 PM, Larry Martell wrote:

On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley
peter.braw...@earthlink.net wrote:

On 2014-01-12 1:17 PM, Larry Martell wrote:

I've been asked to do something that I do not think is possible in SQL.

I have a query that has this basic form:

SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
FROM t
GROUP BY a, b, c, d, f

x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
10053.490, 2542.094).

The business issue is that if either x or y in 2 rows that are in the
same a, b, c, d group are within 1 of each other then they should be
grouped together. And to make it more complicated, the tolerance is
applied as a rolling continuum. For example, if the x and y in a set
of grouped rows are:

row 1: 1.5, 9.5
row 2: 2.4, 20.8
row 3: 3.3, 40.6
row 4: 4.2, 2.5
row 5: 5.1, 10.1
row 6: 6.0, 7.9
row 7: 8.0, 21.0
row 8: 100, 200

1 through 6 get combined because all their X values are within the
tolerance of some other X in the set that's been combined. 7's Y value
is within the tolerance of 2's Y, so that should be combined as well.
8 is not combined because neither the X or Y value is within the
tolerance of any X or Y in the set that was combined.

In python I can easily parse the data and identify the rows that need
to be combined, but then I've lost the ability to calculate the
average and std. The only way I can think of to do this is to remove
the grouping from the SQL and do all the grouping and aggregating
myself. But this query often returns 20k to 30k rows after grouping.
It could easily be 80k to 100k rows that I have to process if I remove
the grouping and I think that will be very slow.

Anyone have any ideas?


Could you compute the row-to-row values  write them to a temp table, then
run the SQL that incorporates that result column?

I thought of temp tables, but I could not come up with a way to use
them for this. How can I apply the x/y tolerance grouping in sql?


Run the query you showed, saving the result to a temp table. In an sproc 
or your preferred app language, do the row-to-row processing to generate 
a new column in the temp table from the biz rules you outlined, now 
query the revised temp table as desired.


PB

-






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

Re: regexp in conditional

[Peter Brawley]

On 2014-01-07 1:22 PM, Larry Martell wrote:

Can I use an regexp in a conditional? I need to do something like this:

SELECT (IF r REGEXP '^ED[HMUZ][0-9]$', 'yes', 'no') FROM s;

I'm getting an error from that. Is there some way to do this?


SELECT IF( r REGEXP '^ED[HMUZ][0-9]$', 'yes', 'no' )
FROM s;


PB


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

Re: 5.1 to 5.6 upgrade: is it possible?

[Peter Brawley]

On 2013-12-04 1:33 PM, Ilya Kazakevich wrote:

Hello,

Have anybody tried to upgrade 5.1 to 5.6? I believe running mysql_upgrade
should be enough, but does there are some caveats?

Ilya Kazakevich.


The MySQL recommendation is to upgrade one major version at a time, ie 
5.1-5.5-5.6. There are so many 5.1-5.5 incompatibilites (eg see 
http://www.artfulsoftware.com/infotree/mysql_incompat.html), I think the 
advice is sound.


PB

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

Re: grouping and limiting results and rand()

[Peter Brawley]

On 2013-09-23 8:10 PM, Jeremiah Jester wrote:

Hello,

How would i go about selecting 5
random cars that are flagged as internet_special (value 1) for each
dealer from a specific table?  Ive tried sub selects with no luck.
Here's a basic query that has my
required conditional. Note that I get all unique dealers by doing a
distinct(dealer_web_name). Make sense? Using mysql 5.

select dealer_web_name,id,internet_special,active from inventory where
internet_special=1 and active=1;

Appreciate the help


For ideas see Within-group quotas at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

-

--
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

[Peter Brawley]

On 2013-03-29 9:57 AM, Dotan Cohen wrote:

On Thu, Mar 28, 2013 at 11:15 PM, Peter Brawley
peter.braw...@earthlink.net wrote:

Can someone run the server with --skip-grant-tables to retrieve this code?


Possibly, I'll ask. However, it is highly unlikely that such a thing
was done to create the routine.


That's not the point, which is to bypass privilege filtering long enough 
to retrieve the code.


PB

--
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

[Peter Brawley]

On 2013-03-28 3:33 PM, Dotan Cohen wrote:

On Wed, Mar 27, 2013 at 5:46 PM,  h...@tbbs.net wrote:

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.


The guy who wrote the routine is no longer available, but he never had
access to any other account. The guy with root did _not_ add this
routine for him, that is for certain! Therefore I conclude that the
current MySQL user did create this routine and I'm missing something
to understand how to see its definition.


Can someone run the server with --skip-grant-tables to retrieve this code?

PB








--
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

[Peter Brawley]

On 2013-03-25 4:28 AM, Dotan Cohen wrote:

On Sun, Mar 24, 2013 at 11:08 PM, Peter Brawley
peter.braw...@earthlink.net wrote:

Log in as admin@localhost.


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?


Ask admin to grant you the required priv.

PB

-






--
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

[Peter Brawley]

On 2013-03-24 8:25 AM, Dotan Cohen wrote:

A MySQL database is using a stored function avg_sales in some SQL
queries. I am having a hard time finding the code for this function,
in order to track down a bug that I suspect is in the function. Here
is what I get when I try to query MySQL for the function code:

mysql SELECT * FROM information_schema.ROUTINES\G
*** 1. row ***
SPECIFIC_NAME: avg_sales
  ROUTINE_CATALOG: NULL
   ROUTINE_SCHEMA: 
 ROUTINE_NAME: avg_sales
 ROUTINE_TYPE: FUNCTION
   DTD_IDENTIFIER: int(11)
 ROUTINE_BODY: SQL
   ROUTINE_DEFINITION: NULL
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
  PARAMETER_STYLE: SQL
 IS_DETERMINISTIC: NO
  SQL_DATA_ACCESS: CONTAINS SQL
 SQL_PATH: NULL
SECURITY_TYPE: DEFINER
  CREATED: 2013-03-14 02:21:14
 LAST_ALTERED: 2013-03-14 02:21:14
 SQL_MODE:
  ROUTINE_COMMENT:
  DEFINER: admin@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
   DATABASE_COLLATION: utf8_general_ci
2 rows in set (0.00 sec)

mysql SHOW CREATE FUNCTION avg_sales;
+---+--+-+--+--++
| Function  | sql_mode | Create Function | character_set_client |
collation_connection | Database Collation |
+---+--+-+--+--++
| avg_sales |  | NULL| latin1   |
latin1_swedish_ci| utf8_general_ci|
+---+--+-+--+--++
1 row in set (0.00 sec)

mysql SHOW CREATE PROCEDURE avg_sales;
ERROR 1305 (42000): PROCEDURE avg_sales does not exist
mysql SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
++
| ROUTINE_DEFINITION |
++
| NULL   |
| NULL   |
++
2 rows in set (0.00 sec)

mysql SELECT param_list,returns,body FROM mysql.proc;
ERROR 1142 (42000): SELECT command denied to user
''@'localhost' for table 'proc'
mysql


Log in as admin@localhost.

PB

-





What else should I try to get the code? This is on MySQL 5.1.61
running on CentOS. Thanks.




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

Re: Foreign key on multiple columns

[Peter Brawley]

On 2013-03-21 8:12 AM, Norah Jones wrote:

I'm trying to create a foreign key on two columns but getting error...

Here's what I tried:

 CREATE TABLE test2 (
   ID INT NOT NULL AUTO_INCREMENT,
   col1 INT NOT NULL,
   col2 INT NOT NULL,
   PRIMARY KEY (ID),
   CONSTRAINT fk FOREIGN KEY (col1, col2)
 REFERENCES test1(ID, ID)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
 ) ENGINE=InnoDB;

But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)`


Dupe reference column.

PB






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

Re: Major MySQL Revision Takes on NoSQL

[Peter Brawley]

On 2013-02-07 8:29 PM, Daevid Vincent wrote:

http://adtmag.com/articles/2013/02/06/mysql-update.aspx


The headline is weirdly deceptive. Mebbe they thought the headline would 
draw eyes to their ads.


This edition of MySQL is less like NoSQL, and more determinedly 
relational, than any before.


It'd be more accurate to say that Monty  David anticipated NoSQL by a 
decade. The early editions of MySQL, with no ACID compliance, no foreign 
keys, no transactions, were more like NoSQL than any version of MySQL 
released since 2004.


PB






--
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

[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 )...

PB

--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

[Peter Brawley]

On 2013-01-31 8:13 PM, Angela Barone wrote:

Hello,

I'm trying to write a select query that grabs two prices from my db and 
displays them on a web page.  I want it to grab the `listprice`, and either the 
`unitprice` or the `specialprice` depending on the following criteria:
if the `specialprice` is not empty,
AND it's less than the `unitprice`,
AND the current date is between the `startingdate` and `endingdate`,
then pull the `specialprice` otherwise pull the `unitprice`.


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

-



This is the code I've used up until now, and it works, but I need to 
add the date range, as described above:

?php $result = mysql_query(SELECT priceList, 
LEAST(unitprice,ifnull(specialprice,'')) AS used_price FROM catalog WHERE 
itemid='WB314',$db);
printf('font size=-1iList: $s%s/s/i/fontbr /', 
number_format(mysql_result($result,0,priceList),2));
printf('bfont color=#55Your Price:/font $%s/bbr /', 
number_format(mysql_result($result,0,used_price),2)); ?

This seems rather convoluted to me and I've been struggling with it all 
day.  Any help would be greatly appreciated!

Thank you so much!
Angela


Schema

NameTypeNULLDefault
startingd   textYes NULL
endingd textYes NULL
specialpricetinytextYes NULL
unitprice   tinytextYes NULL

• Date fields are formatted as 1/31/2013 and cannot be changed because the db 
is used by another script that I can't change.
• Field types can be changed if necessary, as long as the date format remains 
the same.

If you need more information, please let me know.



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

Re: sales data every publisher

[Peter Brawley]

On 2012-12-18 2:52 AM, Haidar Pesebe wrote:

Hi all--


There are 3 tables which each like this:

NOTE: The linkage between table A and table B is ID and IDPUBLISHER, while Table
B to C is the ISBN. Sometimes there are some titles that are not sold
in a given month.

TABLE A (Publisher)
  ---
  ID : NAME : EMAIL :
  ---
  1 : ABC : abc@abc
  2 : CDE : cde@cde
  ---

  TABLE B (BOOKS TABLE)
  
  : IDBOOK: TITLE : PUBLISHER ID : ISBN
  
  : 1 :TITLE 01 :  1 : 001
  : 2 :TITLE 02 :  1 : 002
  : 3 :TITLE 03 :  2 : 003
  : 4 :TITLE 04 :  2 :004
  

  TABLE C (SALES OF BOOKS)

  --
  : IDSALES : ISBN : PRICE : QTY : DATE :

  ---
  : 1: 001: 100: 20 :   2012-12-01 :
  : 2: 001: 100  : 11 :   2012-12-01 :
  : 3: 002: 60  : 15 :   2012-12-01 :
  : 4: 003: 30: 10 :   2012-12-01 :
  : 5: 003: 30  : 7 :2012-12-01 :
  : 6: 003: 30: 8 :2012-12-01 :
  : 7: 004: 50  : 10 :   2012-12-01 :
  ---

  How do I call up the sales in December 2012 for ABC Publisher or call the
  sale in 2012 for ABC publisher?


select b.idbook, b.title, b.isbn, s.qty, sum(s.qty) as qty, Sum(s.qty * 
s.price) as amount

from sales s
join books b on s.isbn = b.isbn
join publisher p on b.publisherID=p.id
group by b.idbook, b.title, b.isbn;

PB






RESULT OF Sales Books of ABC Publisher in December 2012

---


No. : Books Title : ISBN  :QTY : AMOUNT

---


1. : Title 01 : 001   :  31 : 3,100

2. : Tile 02  : 002   :  15 : 900


 and so on .

---


help me to solve this problem

Thanks




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

Re: Get lowest value

[Peter Brawley]

On 2012-12-14 3:28 AM, Neil Tompkins wrote:

Hi,

I've the following data

total, supplier_id, product_name, supplier_code
125,2,iPod,xyz123
100,1,iPod,abc123
145,3,iPod,1213113
245,4,iPod,12345

What query do I need to get the lowest total in this case 100 for
supplier_id 1 ?


See Within-group aggregates at http://www.artfulsoftware.com/queries.php.

PB



Thanks
Neil




--
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

[Peter Brawley]

On 2012-12-11 3:19 PM, Larry Martell wrote:

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.
Explicit join syntax makes such queries easier to read, write, develop  
debug. Is this what you mean?


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

*RIGHT JOIN data_cstimage*ON data_cstimage.id = data_cst.image_measurer_id

INNER JOIN data_target ON data_target.id = data_cst.target_name_id

INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id

INNER JOIN data_category   ON data_category.id = data_tool.category_id

INNER JOIN data_tool   ON data_tool.id = data_cst.tool_id

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'

ORDER BY target_name_id, ep, wafer_id, lot_id, date_time;


PB
http://www.artfulsoftware.com



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?

TIA!
-larry

Re: Help with left outer join

[Peter Brawley]

ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause'
But category_id is a column in data_tool.

Then a bit of reordering is required ...

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
RIGHT JOIN data_cstimage   ON data_cstimage.id = data_cst.image_measurer_id
INNER JOIN data_tool   ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_measparams ON data_measparams.id = 
data_cst.meas_params_name_id

INNER JOIN data_category   ON data_category.id = data_tool.category_id
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'

ORDER BY target_name_id, ep, wafer_id, lot_id, date_time;

PB



On 2012-12-11 5:11 PM, Larry Martell wrote:

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

RIGHT JOIN data_cstimage   ON data_cstimage.id = data_cst.image_measurer_id

INNER JOIN data_target ON data_target.id = data_cst.target_name_id

INNER JOIN data_measparams ON data_measparams.id =
data_cst.meas_params_name_id

INNER JOIN data_category   ON data_category.id = data_tool.category_id

INNER JOIN data_tool   ON data_tool.id = data_cst.tool_id

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'

ORDER BY target_name_id, ep, wafer_id, lot_id, date_time;

Thanks very much for the reply. This is giving me:

ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause'

But category_id is a column in data_tool.

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

[Peter Brawley]

On 2012-12-03 7:40 AM, amit wrote:

Hi Team,

I am not able to use multi value insert via argument in mysql stored 
procedure, Please help where am I wrong. Thanks in Advance !


mysql insert into input_data1 values(),(),(),();
Query OK, 4 rows affected (0.00 sec)

*Problem*
mysql call mobile_series1('(99889988),(12334565)');
+---+
| mobile|
+---+
| (99889988),(12334565) |
+---+
1 row in set (0.01 sec)

instead of 2 rows in table its only one row.

*Function Code:*
|DELIMITER|
DROP  PROCEDURE  IF  EXISTS  mobile_series1;
CREATE  PROCEDURE  mobile_series1(IN  str text)   
 LANGUAGE SQL READS SQL DATA
 BEGIN 
  DROP  TABLE  IF  EXISTS  input_data1;   
  CREATE  TEMPORARYTABLE  input_data1(mobile varchar(1000))  engine=memory;

  INSERT  INTO  input_data1(mobile)  VALUES  (str);
 SELECT * FROM input_data1;
  END  |
DELIMITER;|


|Dynamic SQL requires PREPARE, see the manual page for that cmd.

PB


|

|



|

--
Best Regards
Amit Jain
www.netcore.co.in
Ext - 4109
#9833777592


Banner plane



netCORE wins GOLD in 1^st Smarties India 2012 Awards
Organized by Mobile Marketing Association (MMA)
Cross Media Integration for 'Colgate Active Salt Taste Challenge 2012' 
Campaign

*Campaigns nominated for other categories *
*Brand Awareness* Active Wheel Mobile Marketing Campaign 	*Innovation* 
Active Wheel Mobile Marketing Campaign

Re: Stored Procedure Question?

[Peter Brawley]

when I call a
stored procedure does the control get backs immediately to the php script?


No, sprocs wil lvery likely slow you down.

Probably best to split the job into several part-tasks (i) read rows into a 
work buffer, (ii) walk the work buffer and mark done rows, (iii) walk the done 
list and insert them.

PB

-

On 2012-11-22 11:19 PM, Girish Talluru wrote:

Hi There,

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.

Thanks,
Girish Talluru




--
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.

[Peter Brawley]

On 2012-11-14 5:08 PM, Mogens Melander wrote:

Thanks Rick

Yes, I know it's not going the most (in lack of descriptive words) pretty
piece of code. The variable number of columns is the key phrase here.

I've already told them, this is not a SQL task, but having infinite
trust in my peers out there I know it can be done. I just fail
to do the logic myself. Believe me, I tried.


Some ideas under Automate pivot table queries at 
http://www.artfulsoftware.com/infotree/queries.php. 
http://www.artfulsoftware.com/infotree/queries.php


PB

-



On Wed, November 14, 2012 16:49, Rick James wrote:

While it is possible to pivot a table like that, the code is ugly, and
does not work well if you need a variable number of columns.

SQL can do the JOIN to get a single resultset for the information, but the
layout is best done in a application language, such as PHP.


-Original Message-
From: Mogens Melander [mailto:mog...@fumlersoft.dk]
Sent: Wednesday, November 14, 2012 12:23 AM
To: mysql@lists.mysql.com
Subject: Dynamic crosstab got me lost.

Guru's

I've been tasked with creating a report with information about which
images
are related to each active article. The images are descriptive icons
visualizing different properties.

Articles can be active 'on' or not '', and only active is to be included
in
the result.

The number of available icons changes constantly, and are too many, to
use
anything static.

What I got is:

Table: main
-
code (int)
active (on/off)
-

Table: icon
-
id (int)
filename (char)
-

Table: iconrel
-
code (int)
icon (int)
-

What I need is something like:

codefilename1   filename2   filename3   filenameN
on  off off on

Hopefully somebody out there got more of a clue than I do.

Any ideas, anybody?

--
Mogens Melander
+66 8701 33224

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Re: getting certain rows from a group by

[Peter Brawley]

On 2012-09-18 5:53 AM, Larry Martell wrote:

I have this query:

SELECT data_target.name as Target,
q1.ep as EP,
COUNT(*) as Wafers,
Lots,
SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns  0 THEN reruns
ELSE 0 END)) as 'Sites/Wafer',
MAX(LastRun) as Last Run,
SUM(CASE WHEN reruns  0 THEN reruns ELSE 0 END) as Rerun,
COUNT(*)+SUM(CASE WHEN reruns  0 THEN reruns ELSE 0 END) as Runs,
avgbottom as Avg Bottom,
3*stdbottom as 3 Sig,
maxbottom as Max,
minbottom as Min,
SUM(numonep) as Count,
SUM(numonep) - SUM(numbottoms) as NAs,
100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success,
3*stdbottom/avgbottom as 3Sig/Avg,
AVG(avgbottom) as 'Wafer Avg',
AVG(Wafer3Sigma) as 'Wafer 3 Sigma',
AVG(Ranges) as 'Avg Range',
3*STD(Ranges) as '3Sig of Ranges',
MAX(Ranges) as 'Max Range',
MIN(Ranges) as 'Min Range',
(SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer'
FROM (SELECT target_name_id,
  ep,
  wafer_id,
  COUNT(bottom) as numbottoms,
  AVG(bottom) as avgbottom,
  STD(bottom) as stdbottom,
  MAX(bottom) as maxbottom,
  MIN(bottom) as minbottom,
  MAX(date_time) as LastRun,
  COUNT(*) as numonep,
  COUNT(DISTINCT target_name_id, ep, lot_id,
data_file_id)-1 as reruns,
  COUNT(DISTINCT(lot_id)) as Lots,
  3*STD(bottom) as Wafer3Sigma,
  MAX(bottom) - MIN(bottom) as Ranges
   FROM data_cst
   WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44,
116, 117, 118, 119, 120, 121)
   AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
'2010-03-04 23:59:59'
   GROUP BY target_name_id, ep, wafer_id
   HAVING count(*)  999) q1,
  data_target
WHERE data_target.id = target_name_id
GROUP BY q1.target_name_id, q1.ep;


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).

Is this even possible from one query?


Might be, but what's the importance of doing it as one query? I'd start 
by saving this result to a temp table and developing the new query. When 
that's running, see if you can to optimise a query built by replacing 
the reference to the temp table with the original query.


PB






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

Re: function INTERVAL in view

[Peter Brawley]

n 2012-09-17 12:58 PM, h...@tbbs.net wrote:

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?


Looks like a bug. Report it?

PB
www.artfulsoftware.com







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

Re: suggestion needed for table design and relationship

[Peter Brawley]

On 2012-08-15 1:54 PM, Rajeev Prasad wrote:

I have to keep this data in MySql, and i am not sure (as SQL/databse is not my 
field) how to organise this into one or many tables? right now I would 
represent my info as follows:

device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service
   |associated_device
dev_x  | 1234 |1234  |1.2.3.4|9.8.7.6|data_specific_to_x
 |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m...
dev_y  | 2348 |7734  |10.2.3.4   |99.8.7.6   
|data_specific_to_y.|SVC_B,SVC_X...   |dev_x,dev_m...
dev_z  | 3934 |5634  |11.2.3.4   |79.8.7.6   
|data_specific_to_z.|SVC_M|dev_n,dev_m...
...


pl advice. what would be the best design? data_specific_to_device could be more 
than one column, as i get to explore the data a bit more.

and do i really need a device_id field? whi. ch any SQL table normally has.


Without a primary key, a table isn't really a table. A surrogate 
(auto_increment) PK might be simplest.


If the associated_service column is a list of values, it needs to be 
projected to a child table (parentkey, data item ...).


From the info posted, I can't tell much about the other fields

PB

-



ty.



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

Re: suggestion needed for table design and relationship

[Peter Brawley]

On 2012-08-15 5:27 PM, Rajeev Prasad wrote:

hello Peter,

data_specific_to_device = contains diff values, and is different for different 
devices
associated_service = will also be a list of values which will be different for 
different devices

and same for associated_device column.

this data rarely(almost never) changes and add happens only say twice a month. 
So can I not have one big table like below?


Size is not the issue. A basic table design rule is atomicity--one value 
per cell. Violating that rule screws up queries.



what disadvantage does it has? sorry I am not into RDBMS,


Well you are now :-).

Rilly you have three choices--read about normalisation enough to do it 
right, hire someone to do it right, or botch the system.




so i want to know prob specific to my data and proposed table layout. I am also 
not clear about relating more than one tables, if i break this up in more than 
one table


create table parent( deviceID int unsigned primary key auto_increment, 
devx, ... ) engine=innodb;


create table child child(
  childID int unsigned primary key auto_increment,
  deviceID int unsigned,
  foreign key(deviceID) references parent(deviceID) on update cascade 
on delete cascade,

   ...
) engine=innodb;

PB






thx in advance.
Rajeev



  From: Peter Brawley peter.braw...@earthlink.net
To: Rajeev Prasad rp.ne...@yahoo.com; mysql@lists.mysql.com 
mysql@lists.mysql.com
Sent: Wednesday, August 15, 2012 4:01 PM
Subject: Re: suggestion needed for table design and relationship
  
On 2012-08-15 1:54 PM, Rajeev Prasad wrote:

I have to keep this data in MySql, and i am not sure (as SQL/databse is not my 
field) how to organise this into one or many tables? right now I would 
represent my info as follows:

device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service
   |associated_device
dev_x  | 1234 |1234  |1.2.3.4|9.8.7.6|data_specific_to_x
 |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m...
dev_y  | 2348 |7734  |10.2.3.4   |99.8.7.6   
|data_specific_to_y.|SVC_B,SVC_X...   |dev_x,dev_m...
dev_z  | 3934 |5634  |11.2.3.4   |79.8.7.6   
|data_specific_to_z.|SVC_M|dev_n,dev_m...
...


pl advice. what would be the best design? data_specific_to_device could be more 
than one column, as i get to explore the data a bit more.

and do i really need a device_id field? whi. ch any SQL table normally has.

Without a primary key, a table isn't really a table. A surrogate
(auto_increment) PK might be simplest.

If the associated_service column is a list of values, it needs to be
projected to a child table (parentkey, data item ...).

 From the info posted, I can't tell much about the other fields

PB

-


ty.





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

Re: Subquery taking too much time on 5.5.18?

[Peter Brawley]

On 2012-07-07 9:52 AM, Cabbar Duzayak wrote:

Hmm,

Looking at the link http://www.artfulsoftware.com/infotree/queries.php
and explanations here, EXISTS() should have performed better, but does
not seem to??? I stopped it after about 5 minutes.

I tried both:

   SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and
B.name like 'X%');

and

   SELECT * FROM A WHERE EXISTS (SELECT B.A_ID FROM B WHERE A.id=B.A_ID
and B.name like 'X%');

Both are slow...


What's the optimizer_switch setting?

PB

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

Re: Subquery taking too much time on 5.5.18?

[Peter Brawley]

On 2012-07-06 5:07 PM, David Lerer wrote:

Cabbar, try to replace the IN subquery with an EXISTS. Something like:

   SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name 
like 'X%');


See The unbearable slowness of IN() at 
http://www.artfulsoftware.com/infotree/queries.php


SELECT a.*
FROM a
JOIN b USING(a_id)
WHERE B.name LIKE 'X%';

PB

-




Does it help?

David.

-Original Message-
From: Cabbar Duzayak [mailto:cab...@gmail.com]
Sent: Friday, July 06, 2012 11:46 AM
To: mysql@lists.mysql.com
Subject: Subquery taking too much time on 5.5.18?

Hi Everyone,

I have been trying to understand why subqueries are taking tooo much
time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release.

In a nutshell, I have 2 tables: A and B. And, I do something like this:

SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%');

Table A has 460,000 rows and Table B has 5000 rows, and A.id is my
primary key, and B.name is indexed. Also, the sub-query here (B.name
starts with X%) returns about 300 rows.

For some weird reason, this query takes a ton of time (I cancelled it
after 750 seconds). I looked at the query plan with EXPLAIN and it
could not find an index to use for table A and looks like it is doing
a table scan (even though A.id is the primary key)...

To understand it better, I divided it up, and sent two queries
separately as follows::

SELECT A_ID FROM B WHERE B.name like 'X%'
takes 0.002 second.

For testing purposes, I concatenated all ids from this query and send
a hard-coded query on A like:

SELECT * FROM A WHERE A.id in (1,2,3,4,5.)
and this takes 0.002 second.



Basically, both queries are super fast, but when I combine them via IN
w/sub-query, the thing spends a lot more time?


As an alternative, I tried using JOIN as follows:
SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%';
and this takes 0.04 seconds

JOIN is also fast, but there are cases where I really need IN subqueries.


I would really really appreciate it if you can shed some light on this
issue and tell me what I am doing wrong and/or how I can fix this?

Thanks a ton.





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

Re: category with parentid

[Peter Brawley]

On 2012-05-25 8:35 AM, h...@tbbs.net wrote:

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

It's a graph! See 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

PB

-




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: Query assistance...

[Peter Brawley]

On 2012-05-21 11:17 AM, Don Wieland wrote:
I have got this query that returns the correct rows I want to display, 
BUT the columns dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are 
not calculating correctly:


--- START QUERY ONE ---

SELECT q1.* FROM

(SELECT  apt.user_id, apt.client_id, c.last_name, c.first_name, 
MIN(apt.time_start) AS stime, 
FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m-%d') AS ftime,


(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND 
appt_status_id = '3' AND time_start  apt.time_start) AS previous,


(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND 
user_id = apt.user_id AND appt_status_id = '3' AND 
FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and 
'2012-05-20') AS dr_ther_qty,


(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND 
user_id != apt.user_id AND appt_status_id = '3' AND 
FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and 
'2012-05-20') AS dr_not_ther_qty,


(SELECT DISTINCT count(*) FROM tl_appt WHERE client_id = apt.client_id 
AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') 
between '2012-01-01' and '2012-05-20') AS dr_all_ther_qty


FROM tl_appt apt

LEFT JOIN tl_rooms r on r.room_id = apt.room_id
LEFT JOIN tl_clients c on c.client_id = apt.client_id
LEFT JOIN tl_users u on u.user_id = apt.user_id

WHERE apt.appt_id IS NOT NULL AND 
FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and 
'2012-05-20' AND apt.appt_status_id = '3' and r.location_id = '2' and 
apt.user_id IN (14, 503)

GROUP BY apt.user_id, apt.client_id
ORDER BY u.last_name, u.first_name, c.last_name, c.first_name) as q1

WHERE q1.previous  0;

--- END QUERY ONE ---

The totals of dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are 
not the same if I broke them out into separate queries:


I think it is something to do with the GROUP BY - it is multiplying rows. 


Yes: select a.id,count(*) from a join b using(...) join c using(...) 
will multiply counts. The most effective solution is to move each 
aggregation inside its own FROM clause subquery.


PB

-


Basically, the rows are correct and I want to use the user_id and 
client_id to calculate the SUB-SELECTS.


Can someone explain why when I run in the MAIN query I get this:

dr_ther_qty = 25
dr_not_ther_qty = 22
dr_all_ther_qty = 47

BUT when I break out that client into the separate queries, I get

SELECT count(*) as dr_ther_qty FROM tl_appt WHERE client_id = 161 AND 
user_id = 503 AND appt_status_id = '3' AND 
FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and 
'2012-05-20';


dr_ther_qty = 6

SELECT count(*) as dr_not_ther_qty FROM tl_appt WHERE client_id = 161 
AND user_id != 503 AND appt_status_id = '3' AND 
FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and 
'2012-05-20';


dr_not_ther_qty = 2

SELECT count(*) as dr_all_ther_qty FROM tl_appt WHERE client_id = 161 
AND appt_status_id = '3' AND FROM_UNIXTIME(time_start,'%Y-%m-%d') 
between '2012-01-01' and '2012-05-20';


dr_all_ther_qty = 8


I appreciate any enlightenment on this. Thanks!

Don




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

Re: Query help,,,

[Peter Brawley]

On 2012-05-17 9:37 AM, Don Wieland wrote:

Hi folks,

I am trying to compile a query that does statistics on appointments 
based on specific criteria. Here is my starting query:


SELECT
u.user_id,
c.client_id,
c.first_name,
c.last_name,
a.time_start AS stime,
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted

 FROM tl_appt a
  LEFT JOIN tl_users u ON a.user_id = u.user_id
  LEFT JOIN tl_clients c ON a.client_id = c.client_id
  LEFT JOIN tl_rooms r on a.room_id = r.room_id

  WHERE a.appt_id IS NOT NULL AND 
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') between '2011-05-01' and 
'2011-12-31' and r.location_id = '2' and a.user_id IN (14) ORDER BY 
u.last_name, u.first_name, c.last_name, c.first_name


This will return a set of rows where a client may have MORE THEN ONE 
appointment. From this set I need to narrow more:


1) Only display the first appointment PER Client. (there will be no 
duplicate client_id)


Then once I have that set of rows established, I need to query for two 
more result:


1) Show New Customers = those where the COUNT of appointments (in the 
entire tl_appt table) LESS THAN the stime = 0


2) Show FORMER Customers = those where the COUNT of appointments (in 
the entire tl_appt table) LESS THAN the stime  0


I am sure I need a multiple select query, but I am having a hard time 
wrapping my head around it.


Thanks for any feedback.


Conceptually the hard bit might be narrowing to the first appt per 
client. For various approaches to this task see Within-group 
aggregates at http://www.artfulsoftware.com/infotree/queries.php.


If new  former clients are to be retrieved from first appts only, you 
might want to save the result of the within-groups agggregate query to a 
result table and query that. If the whole process has to happen often, 
consider developing a wee cube, or just a denormalised reslt table that 
can be invoked whenever needed.


PB

-



Don



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

Re: The most elegant/efficient way to pull this SQL query

[Peter Brawley]

On 4/11/2012 1:30 PM, Haluk Karamete wrote:

I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs

peopleID___stateID
1__1
2__4
3__5


people table is like this;

___peopleID_FName_
___1joe
___2bob___
___3charlie_


and finally the state table goes like this;

___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__


What's the most straightforward way to achieve the following view with one
SQL statement?


peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)


select b.peopleID, concat('(',p.fname,,')'), b.stateID, 
concat('(',s.state,')')

from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;

PB

-





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

Re: MySQL Session Variables with PHP

[Peter Brawley]

On 2/22/2012 9:47 AM, Steven Staples wrote:

Good [insert time of day here] all!

I am trying to reorder my auto-inc field in my database, and I have
successfully done it with my front end that I use (SQLYog) with the
following code:

SET @var_name = 0;
UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);


Now, when I try this within PHP... I can't get it to work at all.  I assume
that the SET is the issue, but I am not 100% sure.

I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
failed, and I tried it with 2 statements, it didn't puke on it, but it
didn't work either.

?php
# db connection is already set up #
echo $db-exec('SET @var_name = 0;')
echo 'br /';
echo $db-exec('UPDATE `my_database`.`my_table` SET `id` =
(@var_name:= @ var_name +1);');
exit;
?

Does anyone know how I can do this within PHP?  Worst case scenario, is that
I just write a php shell() command instead, but I would rather avoid that if
at all possible.


The manual warns us not to rely on repeat user var assignments, but your 
approach works for me:


$conn=mysql_connect( ... );
mysql_select_db(test);
mysql_query( drop table if exists t ) or exit(mysql_error());
mysql_query( create table t (id int) ) or exit(mysql_error());
for( $i=0; $i10; $i++ ) mysql_query( insert into t values(0) ) or 
exit(mysql_error());

mysql_query( set @var=0 ) or exit(mysql_error());
mysql_query( update t set id=(@var:=@var+1) ) or exit(mysql_error());
$res = mysql_query( select id from t ) or exit(mysql_error());
while( $row = mysql_fetch_row( $res )) echo $row[0],  ;

Output: 1 2 3 4 5 6 7 8 9 10

PB

-



Thanks in advance!

Steven Staples





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

Re: weird difference in workbench and CLI query

[Peter Brawley]

On 2/13/2012 10:18 AM, James W. McNeely wrote:

When I run this query in workbench:
select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date', s.ExamDate 
'SJH Exam Date'
from chestcon_log c
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
left join sjhreports_ s on c.acc_number = s.AccessionNumber
WHERE
c.timestamp_exam= CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL '-1' 
DAY) AS DATETIME)
AND c.timestamp_exam= CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 
'-14' DAY) AS DATETIME)

I get this:
7330565 NULL 2012-02-01
6604419  2011-01-25 NULL

but when I run the same query in a shell script, on a Linux box or OS X, I get 
this:
7330565 NULL2012-02-01
6604419 NULLNULL

What are the MySQL versions and sql_mode settings on the two boxes?

PB

-



I also tried echo this and piping it into MySQL with no shell script. Same 
result. Why does the date eval to null in CLI?


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

Re: Indexed Query examining too many rows!

[Peter Brawley]

On 2/12/2012 4:40 PM, Reindl Harald wrote:

Am 12.02.2012 23:25, schrieb Cabbar Duzayak:

Hi All,

I have a table with a btree index on its searchKey column, and when I
send a simple query on this table:

explain select * from DataIndex where (searchKey like 'A%') order by
searchKey limit 10

rows is returning 59548 and it tells me that it is using the searchKey index.

Also, a select count(*) on this table returns 32104 rows, i.e.

select count(*) from DataIndex where searchKey like 'a%' -  gives
32104 as its result

Am I doing something wrong here? Given that the searched column is
indexed, shouldn't it examine way less rows?

LIKE does not benefit from keys!


It does if the wildcard is not at the front, as indicated at 
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-2719/ 
...


*When MySQL uses indexes*
...
When you use a LIKE that doesn't start with a wildcard.
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
...

PB

-

Re: how to changing default '-' character in Datetime in MySQL?

[Peter Brawley]

On 2/5/2012 9:21 PM, Michael Dykman wrote:

You are right.  It seems to have fallen into disuse since I used it last.


AFAIK it has never been used.

PB

-



At any rate, the format does not affect storage.  I, like most others,
generally specify the format using the date_format function within the
queries themselves.  It is  more stable way to proceed anyhow;
otherwise, your code will tend to behave differently between different
servers.

  - michael

On Sun, Feb 5, 2012 at 11:07 PM, Rajeev Prasadrp.ne...@yahoo.com  wrote:

thx Michael,

but the page says:

* date_format
This variable is unused.
*datetime_format
This variable is unused.


- Original Message -
From: Michael Dykmanmdyk...@gmail.com
To: mysql mailing listmysql@lists.mysql.com
Cc:
Sent: Sunday, February 5, 2012 9:24 PM
Subject: Re: how to changing default '-' character in Datetime in MySQL?

To clarify, what we are discussing is the date format. It has
nothing to do with how it is stored.  It is stored as binary data
whatever your format is.  What the date format does effect is how that
data is formatted upon conversion to a string, assuming the
date_format() method has not been specified in the query for more
fine-grained control.

There is a system variable 'date_format' which can be set in your
mysql.cnf to affect the entire system; it has been around since
version 3.23. Alternatively, it may be specified on a
session-by-session basis if you prefer.  Refer to the documentation
page below for details on manipulating system variables either
globally or on a per-session basis.

http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html

- michael dykman

2012/2/5 Halász Sándorh...@tbbs.net:

2012/02/04 19:13 -0800, Rajeev Prasad

MySQL datetime field type keeps data as: -MM-DD HH:mm:SS is there a way to 
store this data as: /MM/DD HH:mm:SS or going much further (optionally) can 
we store as: MM/DD/ HH:mm:SS  ?   if not then whats the best way to 
reformat the cell value from -MM-DD to MM/DD/YYY

That is MySQL s string format, and that is what you get. That said, there is a 
function DATE_FORMAT (look it up) that lets one change its look. Its format 
argument is quite ugly.


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




--
  - michael dykman
  - mdyk...@gmail.com

  May the Source be with you.

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





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

Re: cannot pass time to function

[Peter Brawley]

On 1/27/2012 12:09 PM, John Heim wrote:
I'm trying to create a function that formats a time in a standard way 
('%H:%i'). But all I can seem to get back is null.


DROP TABLE IF EXISTS bogus_table;
CREATE TABLE IF NOT EXISTS bogus_table (
btime TIME
);

INSERT INTO bogus_table VALUES ('12:34:56');

DROP FUNCTION IF EXISTS bogus ;
DELIMITER $$
CREATE FUNCTION bogus (
btime TIME
)
  RETURNS VARCHAR(10)
BEGIN
DECLARE ctime VARCHAR(10) DEFAULT '';
SET ctime=DATE_FORMAT(btime,'%H:%i');
RETURN ctime;
END $$
DELIMITER ;

SELECT btime,
bogus(btime) AS btime1,
DATE_FORMAT(btime,'%H:%i') AS btime2
FROM bogus_table;

+--+++
| btime| btime1 | btime2 |
+--+++
| 12:34:56 | NULL   | 12:34  |
+--+++
1 row in set, 1 warning (0.00 sec)
In 5.0 use TIME_FORMAT(). In 5.5 and 5.6, DATE_FORMAT() accepts time 
values without dates.


PB

-



mysql show warnings;

+-+--+--+
| Level   | Code | Message  |
+-+--+--+
| Warning | 1292 | Incorrect datetime value: '12:34:56' |
+-+--+--+
1 row in set (0.00 sec)

mysql



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

Re: setting the default of a date field

[Peter Brawley]

On 1/27/2012 11:18 AM, william drescher wrote:
Is there a way to set the default of a date field to the date the 
record is generated ?


bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB

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

Re: setting the default of a date field

[Peter Brawley]

On 1/27/2012 2:24 PM, william drescher wrote:

On 1/27/2012 3:21 PM, Peter Brawley wrote:

On 1/27/2012 11:18 AM, william drescher wrote:

Is there a way to set the default of a date field to the date
the record is generated ?

bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB


When I try
ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` 
`lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP

I get:
#1067 - Invalid default value for 'lastQuarterlyReview'


Yes, if you want a default value use a timestamp column.

PB

-



bill




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

Re: mysql guru??

[Peter Brawley]

On 1/20/2012 5:54 AM, bruce wrote:

Hi.

Got a major pain that I'm trying to solve using mysql.

Trying to handle a hierarchical tree structure, where I have a
parent/child structure that grows as data is added to the system.

The process needs to continuously determine if the overall tree, and
all the associated nodes/leafs have completed so not only is the
tree growing, but data for the given node/leaf is also changing,

The system is comprised of a parent app which spawns descendant apps
that in turn can spawn descendant apps, and so on..

The system is represented in mysql as a parent/child tree, where each
spawned app has an ID, as well as a status for the completion status
of the app.

I'm trying to find someone I can talk to regarding this, so I can get
clarity on how this can be implemented.

The process needs to be able to:
-update the tree tbl with updated data from the running apps
-update the tbl with new nodes/leafs as the spawned apps are created
-quickly return 0/1 if the descendants of a node have been complete

I've created a few different tbl defs, and played with a few different
approaches, but haven't got this right yet. I've looked at a number of
different articles covering hierarchical, adjacency models, closures,
etc...

**The nested soln isn't applicable to the project, as the data/tree
tbl is continually growing, which would require a complete rebuilding
of the nested tbls, which would impose a computational/time hit on the
process.


Right. An edge list model with a mix of MySQL stored procs and app 
functions should do the job, see 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.


PB

--



I can provide the sample tbl defs/data that I'm using, as well as more
data on what I'm trying to accomplish.

So, if you're skilled in this area, let's talk.

Thanks

-bruce



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

Re: Parent/Child - Linked List

[Peter Brawley]

On 1/16/2012 12:53 PM, bruce wrote:

On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com  wrote:

Hey Authur.

Should have been more clear. I've looked over a number of sites. And
with the exception of the the articles that talk about using the
Nested List approach, nowhere did I find data on how to get a
complete list of the child descendants of a given 'root'/top item from
the parent/child TBL.


Look again, eg listings 7 through 7d in 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.


PB

-



Chunks of code/pointers would be seriously useful.

Thanks


On Mon, Jan 16, 2012 at 12:18 PM, Arthur Fullerfuller.art...@gmail.com  wrote:

See the piece on trees at www.artfulsoftware.com. It goes into several
variations of how to handle hierarchies.

HTH,
--
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
   -- Neils Bohr




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

Re: Parent/Child - Linked List

[Peter Brawley]

On 1/16/2012 4:14 PM, bruce wrote:

hi Peter.

Sorry.. Been looking at this for awhile.

In the sample data/tbl I provided, it has two top level root/parents.
Ie, I have two entries that don't have a
parentID. I use 0 to be null.


Mistake. use Null.


The items are (0,1), and (0,8).


Then the table has two trees. See Multiple trees in one table at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

-


The (0,1) item, has a number of descendants. The (0,8) only has a
single descendant. For my app, I'm going to have a number of top level
items, and they're each going to have a number of descendants, where I
don't know the number of descendant rows, or the number of actual
descendants.

But either way, once I get the descendant list, I still need some way
of linking the childID of the descendant to the linked ID of the
statusTBL so I can get the status of the childID/app.

And like I said, I'm not quite sure how to proceed in an efficient
manner on this.

Thanks



On Mon, Jan 16, 2012 at 4:08 PM, Peter Brawley
peter.braw...@earthlink.net  wrote:

On 1/16/2012 2:08 PM, bruce wrote:

Hi Peter.

Not a mysql guru... so I've never used stored procedures/sub-queries..

But it sort of makes sense.

What I'm really trying to get is to be able to take a test table like
below

LOCK TABLES `parentChildTBL` WRITE;
/*!4 ALTER TABLE `parentChildTBL` DISABLE KEYS */;
INSERT INTO `parentChildTBL` VALUES
(0,1,1),
(1,2,2),
(1,3,3),
(1,4,4),
(2,5,5),
(2,6,6),
(2,7,7),
(0,8,8),
(8,9,9);
UNLOCK TABLES;

and to be able to generate the child/descendant list of the top two/2
items (1,8)


I don't understand top two(1,8). In general a non-procedural query of n
recursion levels requires n-1 joins. If the number of recursive references
is unknown beforehand, the only way to query the tree is via a stored
procedure.

PB


- if I only have a single top level item.. and can do a left join.. but
I'm not sure how to accomplish this with two top items, unless I take a look
at the approach you provided. I'm looking at being able to compare a
'status' from a linked tbl, that links on the childID... thanks On Mon, Jan
16, 2012 at 2:33 PM, Peter Brawleypeter.braw...@earthlink.net  wrote:

On 1/16/2012 12:53 PM, bruce wrote:

On Mon, Jan 16, 2012 at 1:52 PM, brucebadoug...@gmail.com  wrote:

Hey Authur.

Should have been more clear. I've looked over a number of sites. And
with the exception of the the articles that talk about using the
Nested List approach, nowhere did I find data on how to get a
complete list of the child descendants of a given 'root'/top item from
the parent/child TBL.


Look again, eg listings 7 through 7d in
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.

PB

-



Chunks of code/pointers would be seriously useful.

Thanks


On Mon, Jan 16, 2012 at 12:18 PM, Arthur
Fullerfuller.art...@gmail.com
  wrote:

See the piece on trees at www.artfulsoftware.com. It goes into several
variations of how to handle hierarchies.

HTH,
--
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
   -- Neils Bohr




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

Re: Date and Time

[Peter Brawley]

On 1/8/2012 2:21 PM, Donovan Brooke wrote:

Hello, I'm doing an insert into with date and time type fields.

I was reading:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

My question is: is the format always 'year month day'?.. or can we 
save dates in 'month day year' as well?
As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format 
date strings to the format MySQL uses.


PB

-



Thanks,
Donovan




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

Re: errors running WHILE loop

[Peter Brawley]

On 12/27/2011 12:13 PM, Mark Haney wrote:

I've had a DEVIL of a time with this problem and I'm sure it's something 
simple, but I can't find it anywhere in the documentation or online what the 
problem is.  Let me explain.

I cannot get any WHILE loop to work from either a CLI or a script on MySQL 
5.1.41.  For example, this is a simple script that sets a variable, and loops 
through 10 interations and prints the SQL version each time.  It's a silly 
script, but this is what I'm getting:

set @v1 = 0; while @v1  10 do; use mysql; select VERSION(); set @v1 = @v1 + 1;

Control constructs are allowed only withing stored routines.

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): 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 'while 
@v1  10 do' at line 1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+-+
| VERSION()   |
+-+
| 5.1.41-3ubuntu12.10 |
+-+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Here's where I'm stuck.  In some cases, I've seen where END WHILE is used, or a 
BEGIN/END block inside the WHILE, and even outside the WHILE.  I'm at a 
complete loss as to WTF is going on.  The documentation tells me little.  In 
fact the test example in the MySQL reference manual online for 5.1 bombs as 
well:

CREATE PROCEDURE dowhile()
BEGIN
   DECLARE v1 INT DEFAULT 5;

   WHILE v1  0 DO
 USE mysql;

USE is not allowed in stored routines.

PB


 SELECT VERSION();
 SET v1 = v1 - 1;
   END WHILE;
END;

mysql  source ~/dowhile.sql
ERROR 1064 (42000): 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 '' at 
line 3
ERROR 1064 (42000): 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 'WHILE 
v1  0 DO
 USE mysql' at line 1
+-+
| VERSION()   |
+-+
| 5.1.41-3ubuntu12.10 |
+-+
1 row in set (0.00 sec)

ERROR 1193 (HY000): Unknown system variable 'v1'
ERROR 1064 (42000): 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 'END 
WHILE' at line 1
ERROR 1064 (42000): 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 'END' 
at line 1
mysql

So, what the heck is going on here?  I'm at the end of my wits.





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

Re: Query query

[Peter Brawley]

On 12/3/2011 9:35 PM, Jan Steinman wrote:

Second attempt, using a join, returns just one row for Dewey 000 with the 
COUNT being about half the volumes in the library, which isn't right...

I thought a LEFT OUTER JOIN would have returned a record for every record in 
s_library_dewey, but it only returns the first.

Brain freeze again...

SELECT
 COUNT(lib.Dewey) AS Have,
 ddn.Dewey AS DDN,
 ddn.Classification AS Classification
FROM s_library_dewey ddn
LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey)
WHERE 1

Lose WHERE 1, it's useless. Add GROUP BY ddn.Dewey to get all counts.

PB

-





I'm having brain freeze, and wonder if anyone can help me with a query.

I have a library in MySQL. There's a table with a record per book, and other 
tables that it indexes into for meaningful info. One of those is an 
integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey 
field is decimal. In the Dewey table, it's an integer.

I would like to make a report with the info for each DDC, including whether or not there 
are any books for any given code's integer part. In other words, I want to 
bucketize 101.000 to 101.999, etc, for each integer Dewey number, and give 
some info if the count in that range is non-zero.

I suspect I need a subquery to do this, but my brain is frozen! (Or should I 
use a join? Can you even join on an inequality?)

The following crashes phpMyAdmin when I try to do it. I suspect it's because the subquery reference 
to ddn.Dewey is out of context. The subquery works on its own when 
ddn.Dewey is a literal integer.

SELECT
(SELECT COUNT(*)  0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) 
AS Have,
ddn.Dewey AS DDN,
ddn.Classification AS Classification
FROM s_library_dewey
ddn WHERE 1

Any thoughts on the best way to do this?

Thanks!



After providing the wealth on which the city is built, the countryside and its 
people are increasingly seen as dispensable. When it appears that cities can 
thrive on their global connections, rural hinterlands die. -- David Holmgren
 Jan Steinman, EcoReality Co-op 







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

Re: how to check how big a database is

[Peter Brawley]

On 11/15/2011 2:10 PM, Angela liu wrote:

In mysql 5.0 ,how to check how big a database is?


Thanks

See Database size at http://www.artfulsoftware.com/queries.php

PB

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

Re: How to dynamically create database and tables on mysql?

[Peter Brawley]

On 10/21/2011 3:05 AM, 王科选 wrote:

Hi,
Is there any way to dynamically create database and tables on mysql?
For example, if I want to create 100 databases(dbname is unknown until 
run time), with 100 predefined tables in it, how to achieve that?

Thanks in advance!

Easiest mebbe from a scripting language like Perl or PHP, but you could 
also do it with PREPARE in a MySQL stored procedure.


PB

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

Re: What is wrong with this outer join?

[Peter Brawley]

On 10/20/2011 9:11 AM, Shawn Green (MySQL) wrote:

On 10/19/2011 20:03, Dotan Cohen wrote:

...

Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?



MySQL has always encouraged its employees (developers, support, 
documentation, marketing, ... anyone) to listen to and keep up with 
the community feedback channels. This philosophy has existed since the 
beginning of the lists and forums.

Shawn,

Yes indeed, and your determination to maintain the philosophy that's 
characterised the MySQL project from its beginning is heartening.


Here's some feedback that's in the bailiwick of the MySQLWebmaster, but 
sending it there brings no response and has no effect. The Newbie, 
General and a few other MySQL fora have been overrun with spam for 
weeks, to the point of unusability, without effective remediation. 
What's going on?


PB

-



If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.



We do! First though, are you referencing the online documentation or 
the packaged documentation? The reason I ask is that the online 
documentation does have some user contributions and comments to go 
along with the text itself.  That outside content is not included with 
the packaged documentation.




I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.



No offense taken. We are always trying to keep MySQL easy to install, 
easy to operate, and easy to learn. All feedback is valid.


Regards,


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

Re: Inconsistent query result.

[Peter Brawley]

On 10/11/2011 8:11 AM, Paul Halliday wrote:

I have the following query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

The part that is causing the strange result is probably this:

AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
map2.c_long != 'US')

the value could be 'US' or 'UNITED STATES' depending on user input so
I check both table fields against their input.

On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
5.1.54), 'US' appears in the results.

Is there a better way to write this?

Is this what you mean?

SELECT
  COUNT(signature) AS count, MAX(timestamp) AS maxTime, 
INET_NTOA(src_ip), map1.

  cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
  signature, signature_id, ip_proto
FROM event
LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' 
AND map1.c_long != 'United States'
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' 
AND map2.c_long != 'United States'

WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

PB

-



Thanks.



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

Re: Inconsistent query result.

[Peter Brawley]

On 10/11/2011 12:26 PM, Paul Halliday wrote:

On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley
peter.braw...@earthlink.net  wrote:

On 10/11/2011 8:11 AM, Paul Halliday wrote:

I have the following query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

The part that is causing the strange result is probably this:

AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
map2.c_long != 'US')

the value could be 'US' or 'UNITED STATES' depending on user input so
I check both table fields against their input.

On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
5.1.54), 'US' appears in the results.

Is there a better way to write this?

Is this what you mean?

SELECT
  COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip),
map1.
  cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
  signature, signature_id, ip_proto
FROM event
LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' AND
map1.c_long != 'United States'
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' AND
map2.c_long != 'United States'
WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

PB


Is it OK to keep adding to those joins?
Sure. At some point, though, you might consider an intermediate table to 
precompute/hide some of the complexity.


PB

-

In a view there could be say
50 countries. The user can keep on adding more to exclude. So would I
just expand on the ANDs like so:

AND map1.cc !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ?



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

MySQL fora overrun by spam

[Peter Brawley]

Several MySQL fora, including all the Usage fora, have been overrun by 
spam to the point of unusability, with no sign of effective remediation. 
This gives the impression that a process of  letting MySQL die of 
neglect has begun.


PB

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

Re: Variables in stored procedure

[Peter Brawley]

On 10/4/2011 4:20 PM, Adam Gerson wrote:
I am getting the error that TABLENAME does not exist. How do I get 
it to substitute the value stored in TABLENAME, and not the literal 
string?


See the manual page for PREPARE.

PB

-


begin

declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;

  start transaction;
  while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0', 'statpress_mincap', 
'edit_posts', 'yes');

set v_counter=v_counter+1;
  end while;
  commit;
END


Thanks,
Adam



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

Re: mysql listed as attach page by google?

[Peter Brawley]

On 9/26/2011 1:30 PM, Michael Albert wrote:

I don't suppose I am the first to notice this, but most of
the pages on dev.mysql.com have been listed by google
as attack pages, e.g http://dev.mysql.com/downloads/.
Has there been a problem, or is google being overzealous?
No. There are problems on at least some of the MySQL web pages, and 
there's been a dramatic falling off of webmaster scrutiny such that, for 
example, the General Forum has been overrun by hundreds of spam messages 
which have been in plain view, with no effective response, for more than 
24 hours.


PB

-




Thanks!

-Mike



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

Re: locked non-existent row

[Peter Brawley]

On 9/1/2011 9:06 AM, Jerry Schwartz wrote:

-Original Message-
From: Peter Brawley [mailto:peter.braw...@earthlink.net]
Sent: Wednesday, August 31, 2011 10:40 AM
To: r...@grib.nl; mysql@lists.mysql.com
Subject: Re: locked non-existent row

On 8/31/2011 4:50 AM, Rik Wasmus wrote:

While a transaction in one thread tries to update a non-existent InnoDB
row with a given key value, an attempt to insert that value in another
thread is locked out. Does anyone know where this behaviour is documented?

[JS] Forgive my ignorance, but I thought that was standard behavior for a row-
or row-range lock (not just MySQL) in any DBMS that supported row locking.
(Back when these things were first being invented, one term was predicate
locking.) The general idea was that you are locking rows that meet certain
criteria, whether any or all of them exist or not. You're locking not only the
existence, but the potential existence, of those rows.

I would expect it to apply not only to keys, but to any set. For example,

SELECT * FROM `t` WHERE `t`.`x`  3 FOR UPDATE;

should lock all rows where `t`.`x`  3 for update, insertion, or deletion --
regardless of whether or not `x` is a key. Otherwise you have no way of
knowing who wins.

The ability to lock non-existent records is critical.

Try it, you'll see.
I agree entirely. I didn't question the practice. My question concerns 
documentation.


PB

-




Regards,

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

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.giiresearch.com









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

Re: locked non-existent row

[Peter Brawley]

On 9/2/2011 6:15 AM, Jochem van Dieten wrote:

On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley wrote:

While a transaction in one thread tries to update a non-existent InnoDB row
with a given key value, an attempt to insert that value in another thread is
locked out. Does anyone know where this behaviour is documented?

In the manual it is called gap locking:
http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html
That makes sense. A reference in 
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read 
to those bits would be helpful to users.


PB



Jochem

--
Jochem van Dieten
http://jochem.vandieten.net/



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

Re: locked non-existent row

[Peter Brawley]

On 9/1/2011 9:46 AM, Rik Wasmus wrote:

-Original Message-
From: Peter Brawley [mailto:peter.braw...@earthlink.net]
Sent: Wednesday, August 31, 2011 10:40 AM
To: r...@grib.nl; mysql@lists.mysql.com
Subject: Re: locked non-existent row

On 8/31/2011 4:50 AM, Rik Wasmus wrote:

While a transaction in one thread tries to update a non-existent InnoDB
row with a given key value, an attempt to insert that value in another
thread is locked out. Does anyone know where this behaviour is
documented?

[JS] Forgive my ignorance, but I thought that was standard behavior for a
row- or row-range lock (not just MySQL) in any DBMS that supported row
locking. (Back when these things were first being invented, one term was
predicate locking.) The general idea was that you are locking rows that
meet certain criteria, whether any or all of them exist or not. You're
locking not only the existence, but the potential existence, of those
rows.

I would expect it to apply not only to keys, but to any set. For example,

SELECT * FROM `t` WHERE `t`.`x`  3 FOR UPDATE;

should lock all rows where `t`.`x`  3 for update, insertion, or deletion
-- regardless of whether or not `x` is a key. Otherwise you have no way of
knowing who wins.

The ability to lock non-existent records is critical.
Yes, though I'm told that SQL Server, for example, does not do this 
(I've not confirmed that myself). The question here is whether the cited 
docs para adequately describes what InnoDB is doing, ie treating a 
single non-existent key value as a range.


PB

-


I concur, although this is just a transaction consisting of 1 statement :).


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

Re: locked non-existent row

[Peter Brawley]

On 8/31/2011 4:50 AM, Rik Wasmus wrote:

While a transaction in one thread tries to update a non-existent InnoDB
row with a given key value, an attempt to insert that value in another
thread is locked out. Does anyone know where this behaviour is documented?

-- connection 1
drop table if exists t;
create table t( lockid char(3), lockinfo char(8), primary
key(lockid,lockinfo) );
insert into t values('abc','def');
begin work;
update t set lockinfo='bar' where lockid='foo';

-- connection 2:
insert into t values('aaa','bbb');
Query OK, 1 row affected (0.00 sec)
insert into t values('foo','bar'); -- waits for connection 1 transaction


It has to do with transaction isolation levels. I assume REPEATABLE READ by
default for InnoDB:

http://dev.mysql.com/doc/refman/5.0/en/set-
transaction.html#isolevel_repeatable-read

 For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE,
and DELETE statements, locking depends on whether the statement uses a unique
index with a unique search condition, or a range-type search condition. For a
unique index with a unique search condition, InnoDB locks only the index
record found, not the gap before it. For other search conditions, InnoDB locks
the index range scanned, using gap locks or next-key (gap plus index-record)
locks to block insertions by other sessions into the gaps covered by the
range.
Yes, that's what the question is about, it says for a unique key value, 
as in this case, it locks only the index record found. There is no 
index record. InnoDb behaves, though, as if there is one. Where is the 
documentation for that?


PB

--


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

locked non-existent row

[Peter Brawley]

While a transaction in one thread tries to update a non-existent InnoDB 
row with a given key value, an attempt to insert that value in another 
thread is locked out. Does anyone know where this behaviour is documented?


-- connection 1
drop table if exists t;
create table t( lockid char(3), lockinfo char(8), primary 
key(lockid,lockinfo) );

insert into t values('abc','def');
begin work;
update t set lockinfo='bar' where lockid='foo';

-- connection 2:
insert into t values('aaa','bbb');
Query OK, 1 row affected (0.00 sec)
insert into t values('foo','bar'); -- waits for connection 1 transaction

PB



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

Re: Best Way to store Hierarchical Data in Mysql

[Peter Brawley]

On 8/19/2011 5:10 AM, Adarsh Sharma wrote:

Dear all,

I researched a lot on storing Hierarchical data in mysql tables.
Below links :

http://scvinodkumar.wordpress.com/2011/01/30/managing-hierarchical-data-in-mysql/ 


http://www.sitepoint.com/hierarchical-data-database-2/

shows different ways to store.

But is there any standard way of doing this fore.g storing twitter 
tweets or comments on a link etc.
Several ways, see 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.


PB

-




Thanks



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

Re: Query Optimization

[Peter Brawley]

On 8/10/2011 1:01 PM, Brandon Phelps wrote:

Hello all,

I am using the query below and variations of it to query a database 
with a TON of records.  Currently the database has around 11 million 
records but it grows every day and should cap out at around 150 million.


I am curious if there is any way I can better optimize the below 
query, as currently it takes this query around 10 seconds to run but I 
am sure this will get slower and slower as the database grows.


SELECT
open_dt,
close_dt,
protocol,
INET_NTOA(src_address) AS src_address,
src_port,
INET_NTOA(dst_address) AS dst_address,
dst_port,
sent,
rcvd
FROM connections
WHERE
dst_port = 80
ORDER BY close_dt  DESC
LIMIT 0, 30

I do have an index on the dst_port column, as you can see by the 
output of EXPLAIN:


id   1
select_type  SIMPLE
tableconnections
type ref
possible_keysdst_port
key  dst_port
key_len  2
ref  const
rows 1109273
ExtraUsing where; Using filesort

Did you try adding your ORDER BY argument close_dt to the index?

PB

-


Thanks in advance,



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

Re: Facebook Trapped In MySQL a 'Fate Worse Than Death'

[Peter Brawley]

On 7/12/2011 9:45 AM, Jerry Schwartz wrote:

Let this be a lesson to all of those designers who say That will never
happen.
Let this be a lesson to all of those designers who say ''That will 
never happen' will never happen. :-)


PB

-

Regards,

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

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com







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

Re: Mysql Trigger

[Peter Brawley]

On 7/10/2011 3:32 AM, vishesh kumar wrote:Hi Members

Whats wrong i am doing in following create trigger statement

   Create trigger test before insert on user
for each row
begin
   set New.host=upper(new.host)
end

Above statement giving error check sql syntax , please  guide me. I want to
put host field value in uppercase in user table

One-liners don't need DELIMITERs, so you can write:

create trigger test before insert on user for each row set 
new.host=upper(new.host);

Multi-line Triggers need BEGIN...END so they need DELIMITERs too:

drop trigger if exists test;
delimiter go
create trigger test before insert on user for each row
begin
  set new.c=upper(new.c);
  set ...c ...
end;
go
delimiter ;

A note on names: once you have too many tables  Triggers to remember, 
you wish you'd adopted a naming convention that helps you remember 
object names. A good convention for Trigger names is 
TablenameWhenAction, so yours would be TestBeforeIns.

PB


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

Re: Using where; Using temporary; Using filesort

[Peter Brawley]

 Is it ALWAYS possible to fabricate a query/schema in
 such a way that MySQL ALWAYS uses the ideal

No. Optimisation is better in 5.6 than in 5.0, though.

Did you try adding multi-column indexes to cover the join and the order 
by clause?


 'Using where' extra -- you just have to keep at it?
 Or is it the case that sometimes you're just S.O.L

I don't know a general answer to that question. To figure out the answer 
in a particular case, I usually have to see the Create Table statements, 
see how the query performs with representative data, and experiment with 
various index setups.


PB

-

On 5/31/2011 1:27 PM, Daevid Vincent wrote:

I sent this Friday, but it never made it to the list?!

-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Using where; Using temporary; Using filesort

I'm trying to optimize a query that doesn't seem all that complicated,
however I can't seem to get it to not use a temp table and filesort.

developer@vm_vz_daevid:~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2


EXPLAIN EXTENDED
SELECT
-- d.date_release,
-- d.dvd_title,
-- s.type,
-- s.id_place,
 s.scene_id AS index_id,
 s.dvd_id
FROM
 dvds AS d JOIN scenes_list AS s
 ON s.dvd_id = d.dvd_id
 AND d.status = 'ok'
 AND d.date_release != '-00-00'
ORDER BY d.date_release DESC,
 d.dvd_title ASC,
 s.type ASC,
 s.id_place ASC;


*** 1. row ***
id: 1
   select_type: SIMPLE
 table: d
  type: ref
possible_keys: PRIMARY,date_release,status,status_release
   key: status_release
   key_len: 1
   ref: const
  rows: 1976
 Extra: Using where; Using temporary; Using filesort
*** 2. row ***
id: 1
   select_type: SIMPLE
 table: s
  type: ref
possible_keys: dvd_id_2,dvd_id
   key: dvd_id
   key_len: 4
   ref: videoszcontent.d.dvd_id
  rows: 6
 Extra: Using where
2 rows in set, 1 warning (0.00 sec)

There are proper indexes on most every column in both tables (as you can see
there).

[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!

[b] The commented out columns above I thought might help with the ORDER BY
for some reason from my reading here:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
they did not.

[c] lopping off the ORDER BY all together stops the Using temporary; Using
filesort of course. Yeah! But now I'm left with a table of data in random
order. Re-sorting it in PHP seems like an even bigger waste of cycles, when
no doubt MySQL can sort hella-faster.

[d] just doing  ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents
the using temporary but still does filesort and again I'm in the boat of
[c]

I guess my question is this: Is it ALWAYS possible to fabricate a
query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where'
extra -- you just have to keep at it? Or is it the case that sometimes
you're just S.O.L. and no matter what, MySQL is going to give you a
Cleveland Steamer? In other words, am I wasting my time trying to tweak my
query and indexes here with the idea there's some magic incantation that
will get this right or do I just have to accept it is what it is and it's
not going to do any better.

d.




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

Re: [MYSQL] need simple help

[Peter Brawley]

On 5/12/2011 2:30 PM, Grega Leskovšek wrote:

CREATE TABLE log (  idlog int auto_increment not null,  imepriimek
varchar(50),  clock timestamp,  action varchar(30),  onfile
varchar(100), filesize float(6,2), uniqueid(idlog) );

CREATE TABLE log (
idlog int auto_increment primary key,
imepriimek varchar(50),
clock timestamp,
action varchar(30),
onfile varchar(100),
filesize float(6,2)
);

PB

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

Re: How to find top 25 selling products for each day of year?

[Peter Brawley]

I would like to create a table with the top 25 Amt_Sold products for 
each day


Examples at Top N per group at http://www.artfulsoftware.com/queries.php

PB

-

On 5/8/2011 12:21 PM, mos wrote:

I have a table (MyISAM) with summarized Sales data:

Table: ProdSales
Columns:
Sales_Date   Date,
Product_Code Char(10),
Amt_Sold  Double


There are approx 5,000 products sold each day and there are 3 years 
worth of data. I would like to create a table with the top 25 Amt_Sold 
products for each day


Example:

'2011-03-01', ABC001, 30421.21
'2011-03-01', ABC031, 3.15
'2011-03-01', ABC011, 23312.00
'2011-03-01', ABC101, 22211.87
'2011-03-01', DE0211, 21931.44
'2011-03-01', AGC331, 20321.32
'2011-03-01', DEF321, 20300.31
'2011-03-01', KLC031, 2.21
'2011-03-01', MIU031, 19332.00

25th top Amt_Sold for 2011-03-11

'2011-03-02', FER001, 40421.21
'2011-03-02', DEC031, 40010.15
etc..


So the table would have 25 rows per date and there would be several 
years worth of data.


Now I can do this easy enough for one date. But is there an efficient 
way of doing this for each day of the year without resorting to 
executing the same SQL statement for each day?


TIA
Mike
(MySQL 5.5)




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

Re: A common request

[Peter Brawley]

 How can I quickly find all the articles written by this user's 
friends, and not just random articles?


Taking the simplest possible case, with table friends(userID,friendID) 
where each friendID refers to a userID in another row, the friends of 
userID u are ...


select friendID from user where userID=u;

so articles by those friends of u are ...

select a.* from article a join (  select friendID from user where 
userID=u ) f on a.userID=f.friendID;


PB

-

On 3/29/2011 12:50 PM, Gregory Magarshak wrote:
Hey there. My company writes a lot of social applications, and there 
is one operation that is very common, but I don't know if MySQL 
supports it in a good way. I thought I'd write to this list for two 
reasons:


1) Maybe MySQL has a good way to do this, and I just don't know 
about it


2) Propose to MySQL developers a simple algorithm which would 
greatly improve MySQL support for social networking apps.


Here is the situation. Let's say I have built a social networking 
application where people create and edit some item (article, photo, 
music mix, whatever). Now, a typical user logs in, and this user has 
3000 friends. How can I quickly find all the articles written by this 
user's friends, and not just random articles?


Ideally, I would want to write something like this:

SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 
349580)


basically, execute a query with a huge IN ( ... ). Maybe if this 
would exceed the buffer size for the MySQL wire protocol, I would 
break up the list into several lists, and execute several queries, and 
union the results together myself.


But my point is, this is very common for social networking apps. 
Every app wants to show the X created by your friends, or friends 
of yours (given some list from a social network) who have taken action 
X.


Here is how I would do it if I had raw access to the MySQL index 
in memory:


a) Sort the list of entries in the IN, in ascending order.

b) Do *ONE* binary search through the index (assuming it's a BTREE 
index) and get them all in one pass. If it's a HASH index or 
something, I would have to look up each one individually.


The benefits of this approach would be that this common operation 
would be done extremely quickly. If the index fits entirely in memory, 
and I just want to get the primary keys (i.e. get the list of friends 
who did X), the disk isn't even touched. In addition, for BTREE 
indexes, I would just need ONE binary search, because the entries have 
been sorted in ascending order.


Does MySQL have something like this? And if not, perhaps you can 
add it in the next version? It would really boost MySQL's support for 
social networking apps tremendously. Alternative, how can I add this 
to my MySQL? Any advice would be appreciated.


Sincerely,
Gregory Magarshak
Qbix



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

Re: A common request

[Peter Brawley]

 Why not optimize the IN ( ... ) to do the same type of thing?

If the argument to IN() is a list of values, it'll be OK. If it's a 
SELECT, in 5.0 it will be slower than molasses (see The unbearable 
slowness of IN() at http://www.artfulsoftware.com/queries.php.


 I always tried to avoid joins because I am planning to horizontally 
partition my data.


A severe  unfortunate constraint. Can't help you there.

PB

-

On 3/29/2011 1:27 PM, Gregory Magarshak wrote:
Yes, this would be fine. But often, the list of friends is obtained 
from a social network like facebook, and is not stored internally. 
Basically, I obtain the friend list in a request to facebook, and then 
see which of those users have created things. So would I have to 
create a temporary table and insert all those uids just to make a 
join? Why not optimize the IN ( ... ) to do the same type of thing?


There is also a second problem: I want to use MySQL Cluster, because I 
expect to have many users. Would it be efficient to use JOIN between 
the friends table and the articles table? Both tables are partitioned 
by user_id as the primary key, so the join would have to hit many 
different nodes. I always tried to avoid joins because I am planning 
to horizontally partition my data. But if MySQL cluster can handle 
this join transparently and split it up based on the partition, then 
that's fine. Do you have any info on this?


Greg

On 3/29/11 2:10 PM, Peter Brawley wrote:
 How can I quickly find all the articles written by this user's 
friends, and not just random articles?


Taking the simplest possible case, with table 
friends(userID,friendID) where each friendID refers to a userID in 
another row, the friends of userID u are ...


select friendID from user where userID=u;

so articles by those friends of u are ...

select a.* from article a join (  select friendID from user where 
userID=u ) f on a.userID=f.friendID;


PB

-

On 3/29/2011 12:50 PM, Gregory Magarshak wrote:
Hey there. My company writes a lot of social applications, and there 
is one operation that is very common, but I don't know if MySQL 
supports it in a good way. I thought I'd write to this list for two 
reasons:


1) Maybe MySQL has a good way to do this, and I just don't know 
about it


2) Propose to MySQL developers a simple algorithm which would 
greatly improve MySQL support for social networking apps.


Here is the situation. Let's say I have built a social 
networking application where people create and edit some item 
(article, photo, music mix, whatever). Now, a typical user logs in, 
and this user has 3000 friends. How can I quickly find all the 
articles written by this user's friends, and not just random articles?


Ideally, I would want to write something like this:

SELECT * FROM article WHERE user_id IN (345789, 324875, 398, 
..., 349580)


basically, execute a query with a huge IN ( ... ). Maybe if this 
would exceed the buffer size for the MySQL wire protocol, I would 
break up the list into several lists, and execute several queries, 
and union the results together myself.


But my point is, this is very common for social networking apps. 
Every app wants to show the X created by your friends, or friends 
of yours (given some list from a social network) who have taken 
action X.


Here is how I would do it if I had raw access to the MySQL index 
in memory:


a) Sort the list of entries in the IN, in ascending order.

b) Do *ONE* binary search through the index (assuming it's a 
BTREE index) and get them all in one pass. If it's a HASH index or 
something, I would have to look up each one individually.


The benefits of this approach would be that this common 
operation would be done extremely quickly. If the index fits 
entirely in memory, and I just want to get the primary keys (i.e. 
get the list of friends who did X), the disk isn't even touched. In 
addition, for BTREE indexes, I would just need ONE binary search, 
because the entries have been sorted in ascending order.


Does MySQL have something like this? And if not, perhaps you can 
add it in the next version? It would really boost MySQL's support 
for social networking apps tremendously. Alternative, how can I add 
this to my MySQL? Any advice would be appreciated.


Sincerely,
Gregory Magarshak
Qbix






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

Re: Need help with query

[Peter Brawley]

 What I need is a list of orgs they bought all of items 34, 36, 58, 
63. every of them.


Some solutions under What else did buyers of X buy at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first 
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called orders

item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will give 
me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58. 
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58, 63. 
every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id 
they have item_id 34, 36, 58 and 63. All of them, not only some of 
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure 
what you DO want with your query. Why are 2607 and 1520 the only 
right answers?


Because they are. I look at the database and manually found the 
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for 
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of 
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having 
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the 
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify 
EXACTLY what you want. Giving an incomplete or contradictory 
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was trying 
to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version 
of MySQL that you are using. I don't currently have any version of 
MySQL installed so I can't try this myself to be sure it works in 
your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I 
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58 
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP









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

Re: IF

[Peter Brawley]

 Yes, but Access s IIF, of the same use, evaluates all three, and the 
documentation explicitly says so.


Let's be glad we don't depend on Access, then.

PB

-

On 3/14/2011 7:52 PM, Sándor Halász wrote:

2011/03/14 16:08 +0100, Johan De Meersman

I'm afraid I'm not authoritative on this, but it seems to me that it would be 
very very bad if the third, unused expression were to be evaluated - not only 
from a performance point of view, but who is to say that that expression is not 
a user-defined function that modifies the database ?

Yes, but Access s IIF, of the same use, evaluates all three, and the 
documentation explicitly says so. MySQL s, that I have seen, says neither. Assuming the 
worst is safer, and then one uses CASE ..., but if not, 




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

Re: trigger-dumping

[Peter Brawley]

 any idea how to really purge my Vista machine for a really fresh start?


How about installing a different OS :-) , or failing that a different 
version of Windows?


I'm sorry, we declined to install Vista on any of our boxes. We've had 
no trouble upgrading to 5.5 from 5.1 on other Windows boxes.


PB

-

On 3/11/2011 10:31 AM, Sándor Halász wrote:

2011/03/10 23:03 -0600, Peter Brawley

On 3/10/2011 8:10 PM, h...@tbbs.net wrote:

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?

Works here in 5.5.8.

PB

Maybe it has something to do with the hybrid version that I see that I have, 
5.1 daemon with 5.5 command-line programs. At first I installed 5.5, could not 
get 'root' or any other user to use it, uninstalled it (but clearly not fully), 
and installed 5.1. I would like to start from the beginning with 5.5; any idea 
how to really purge my Vista machine for a really fresh start?




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