Re: Can not add foreign key constraint

[David Mehler]

Hello,

My thanks to everyone who helped on this issue. The index did it.

Thanks.
Dave.


On 4/28/17, Johan De Meersman  wrote:
> That is quite different, as I suspected :-)
>
> Referential keys require an index on the target table that begins with the
> referenced field, so you'll need to add one on user, as was specified in the
> create table you originally posted.
>
> On 28 April 2017 01:21:39 CEST, David Mehler  wrote:
>>Hello,
>>
>> Here's the output of the command show create table virtual_users:
>>
>>  create table virtual_users;
>>+---+-+
>> | Table | Create Table
>>
>>
>>
>>
>>
>>
>>  |
>>+---+-+
>> | virtual_users | 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 '0',
>>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>>   PRIMARY KEY (`id`),
>>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
>> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
>> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>>+---+-+
>> 1 row in set (0.00 sec)
>>
>>Thanks.
>>Dave.
>>
>>>
>>>
>>> On 4/26/17, Johan De Meersman  wrote:
>>>>
>>>> I note that the innodb status says it couldn't find an index on the
>>>> referenced column. Did the create statements come from your create
>>>> scripts
>>>> or from a show create table statement? I'm suspicious about the
>>index on
>>>> virtual_users(user).
>>>>
>>>>
>>>> - Original Message -
>>>>> From: "David Mehler" 
>>>>> To: "MySql" 
>>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>>> Subject: Re: Can not add foreign key constraint
>>>>
>>>>> Hello,
>>>>>
>>>>> Tried recreating the virtual_users table didn't solve anything.
>>Would
>>>>> it be possible for anyone to check out my box directly?
>>>>>
>>>>> Thanks.
>>>>> Dave.
>>>>
>>>> --
>>>> The bay-trees in our country are all wither'd
>>>> And meteors fright the fixed stars of heaven;
>>>> The pale-faced moon looks bloody on the earth
>>>> And lean-look'd prophets whisper fearful change.
>>>> These signs forerun the death or fall of kings.
>>>>   -- Wm. Shakespeare, "Richard II"
>>>>
>>>
>>> Thanks.
>>> Dave.
>>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/mysql
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
> 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

[David Mehler]

Hello,

 Here's the output of the command show create table virtual_users:

  create table virtual_users;
 
+---+-+
 | Table | Create Table






  |
 
+---+-+
 | virtual_users | 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 '0',
   `quota_messages` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
 REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
+---+-+
 1 row in set (0.00 sec)

Thanks.
Dave.

>
>
> On 4/26/17, Johan De Meersman  wrote:
>>
>> I note that the innodb status says it couldn't find an index on the
>> referenced column. Did the create statements come from your create
>> scripts
>> or from a show create table statement? I'm suspicious about the index on
>> virtual_users(user).
>>
>>
>> - Original Message -
>>> From: "David Mehler" 
>>> To: "MySql" 
>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>> Subject: Re: Can not add foreign key constraint
>>
>>> Hello,
>>>
>>> Tried recreating the virtual_users table didn't solve anything. Would
>>> it be possible for anyone to check out my box directly?
>>>
>>> Thanks.
>>> Dave.
>>
>> --
>> The bay-trees in our country are all wither'd
>> And meteors fright the fixed stars of heaven;
>> The pale-faced moon looks bloody on the earth
>> And lean-look'd prophets whisper fearful change.
>> These signs forerun the death or fall of kings.
>>   -- Wm. Shakespeare, "Richard II"
>>
>
> Thanks.
> Dave.
>

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

[David Mehler]

Hello,

Tried recreating the virtual_users table didn't solve anything. Would
it be possible for anyone to check out my box directly?

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> 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  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  wrote:
>>>>> On 4/24/2017 17:41, David Mehler wrote:
>>>>>> Hello,
>>>>>>
>>>>>> Here's the output. I hope it helps.

Re: Can not add foreign key constraint

[David Mehler]

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;

Thanks.
Dave.


On 4/24/17, Peter Brawley  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  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,
>>>&g

Re: Can not add foreign key constraint

[David Mehler]

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?

Thanks.
Dave.


On 4/24/17, Peter Brawley  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 

Re: Can not add foreign key constraint

[David Mehler]

+
1 row in set (0.01 sec)

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 15:28, Peter Brawley wrote:
>> On 4/24/2017 13:59, David Mehler wrote:
>>> Hello,
>>>
>>> root@localhost [mail]> show eng

Re: Can not add foreign key constraint

[David Mehler]

Hello,

Here's the engines I have:

root@localhost [(none)]> show engines;
++-++--+--++
| Engine | Support | Comment
 | Transactions | XA   | Savepoints |
++-++--+--++
| InnoDB | DEFAULT | Supports transactions, row-level
locking, and foreign keys | YES  | YES  | YES|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
 | NO   | NO   | NO |
| MEMORY | YES | Hash based, stored in memory, useful
for temporary tables  | NO   | NO   | NO |
| BLACKHOLE  | YES | /dev/null storage engine (anything
you write to it disappears) | NO   | NO   | NO |
| MyISAM | YES | MyISAM storage engine
 | NO   | NO   | NO |
| CSV| YES | CSV storage engine
 | NO   | NO   | NO |
| ARCHIVE| YES | Archive storage engine
 | NO   | NO   | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
 | NO   | NO   | NO |
| FEDERATED  | NO  | Federated MySQL storage engine
 | NULL | NULL | NULL   |
++-++--+--++
9 rows in set (0.00 sec)


Not sure why I'm getting the error 1286.

Thanks.
Dave.


On 4/24/17, 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.
>
> PB
>
> -
>
>>
>>
>> This is on a Mysql 5.7 setup.
>>
>> Thanks.
>> Dave.
>>
>>
>>
>> On 4/24/17, Peter Brawley  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,
>>>
>>>

Re: Can not add foreign key constraint

[David Mehler]

Hello,

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


This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley  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

[David Mehler]

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.

  Thanks.
  Dave.

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

Can not add foreign key constraints

[David Mehler]

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?

 Thanks.
 Dave.

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

Re: libmysql.lib

[David Clark]

Ok I have source code thank you...I go into the build directory and I read the 
reame file and for a windows build it
refers to a mysql.sln which I can't find anywhere in the file set.  Anyone got 
any ideas on that.  
On Tuesday, April 29, 2014 3:37 PM, shawn l.green  
wrote:
 
Hello David,
>
>
>On 4/29/2014 4:14 PM, David Clark wrote:
>> mysql is open source as I understand it.
>>
>> I have one project out of about 6 that is failing.
>>
>> I want to debug into libmysql.lib to get a better idea what might be going 
>> on...
>>
>> even if it is a problem in my code.  Where might I find the source/project 
>> files
>> to to this?
>>
>> Thank you,
>>
>> David Clark
>>
>
>The source packages for each Community release are available from the 
>same site as the binary package downloads. Just change which platform 
>you are looking for
>
>http://dev.mysql.com/downloads/mysql/
>http://downloads.mysql.com/archives/
>
>You can also review the bug reports to see if this is something we 
>already fixed and in which release we fixed it
>http://bugs.mysql.com/
>
>Regards,
>-- 
>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
>
>
>
>
>

libmysql.lib

[David Clark]

mysql is open source as I understand it.  

I have one project out of about 6 that is failing.  

I want to debug into libmysql.lib to get a better idea what might be going on...

even if it is a problem in my code.  Where might I find the source/project files
to to this?

Thank you,

David Clark

RE: Calling function, that operates on another database

[David Lerer]

Chris, take a look at Federated tables 
https://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html
No, it is not as easy as Oracle's dblinks.
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: bars0.bars0.bars0 [mailto:bars0.bars0.ba...@gmail.com]
Sent: Tuesday, April 08, 2014 4:16 PM
To: mysql@lists.mysql.com
Subject: Calling function, that operates on another database

Hi all.

I have standard select statement and on one column I want to run
function, that will connect to another database (same server).
Is this possible?

High level example:

SELECT db1.clients.client_id, getTurnover(db1.clients.client_id) FROM
db1.clients;

AND getTurnover($id) body would be something like:

SELECT SUM(db2.turnover.amount) FROM db2.turnover WHERE
db2.turnover.client_id = $id;


So for some data, I need to make lookup to another database table.
Is this even possible?


Cheers, Chris.

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

RE: Help with cleaning up data

[David Lerer]

Bill, here is one approach:

The following query will return the id's that should NOT be deleted:
  Select min (id) from icd9x10 group by icd9, icd10

Once you run it and happy with the results then you subquery it in a DELETE 
statement. Something like:
   Delete from icd9x10 A where A.id not in (Select min (B.id) from icd9x10 B 
group by B.icd9, B.icd10).

I have not tested it (sorry it is a weekend here...), but I hope it will lead 
you into the right direction.

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: william drescher [mailto:will...@techservsys.com]
Sent: Saturday, March 29, 2014 2:26 PM
To: mysql@lists.mysql.com
Subject: Help with cleaning up data

I am given a table: ICD9X10 which is a maping of ICD9 codes to
ICD10 codes.  Unfortunately the table contains duplicate entries
that I need to remove.

CREATE TABLE `ICD9X10` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `icd9` char(8) NOT NULL,
  `icd10` char(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `icd9` (`icd9`,`id`),
  UNIQUE KEY `icd10` (`icd10`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=ascii

id   icd9  icd10
25   29182 F10182
26   29182 F10282
27   29182 F10982

I just can't think of a way to write a querey to delete the
duplicates.  Does anyone have a suggestion ?

bill


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

RE: Locking a Database (not tables) x

[David Lerer]

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


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: Locking a Database (not tables) x

[David Lerer]

Thanks Wayne. This a great idea to prevent user activity on the server. I’ll 
use it in the future.
But I’m looking for a way to prevent user activity on a database ((i.e. 
"database"  being a "schema" or a "catalogue).
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<mailto:dle...@univision.net>  |  http://www.univision.net


[cid:1e909b.png@efba91b0.48b65711]<http://www.univision.net>

From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com]
Sent: Friday, March 21, 2014 2:12 PM
To: David Lerer
Subject: Re: Locking a Database (not tables) x

You could set max_connections = 0; then kill off any remaining connections. Do 
your data load and then set you max_connections back to what it was prior.
show variables like ‘max_connections’; (note this number)
set global max_connections = 0
This will leave 1 connection open for a superuser, I dont know what ID you use 
for that a lot of people use root.
Now import your data.
Once the import is done set global max_connections back to what it was.

On Mar 21, 2014, at 1:42 PM, David Lerer 
mailto:dle...@univision.net>> 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.

Thanks, David.

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


Walter "Wayne" Leutwyler, RHCT
Sr. MySQL Database Administrator
Mobile: 614 519 5672
Office: 614 889 4956
E-mail: wayne.leutwy...@gmail.com<mailto:wayne.leutwy...@gmail.com>
E-mail: wleut...@columbus.rr.com<mailto:wleut...@columbus.rr.com>
Website: http://penguin-workshop.dyndns.org

"Courage is being scared to death, but saddling up anyway." --John Wayne

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.

Locking a Database (not tables)

[David Lerer]

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.

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

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.

Locking a Database (not tables) x

[David Lerer]

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.

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

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: Monitoring Sessions

[David Lerer]

We have tried Oracle tool (MySQL Enterprise Monitor) which allows you to 
capture and analyze queries submitted from selected hosts, for a specific time 
window. The tool and its user interface were very useful in identifying the 
volume and heavy queries.  Licensing and (cost) may be an issue. I have not 
tried the Percona tool. David. 


- Original Message -
From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
Sent: Wednesday, October 10, 2012 08:02 AM
To: mysql@lists.mysql.com 
Subject: Re: Monitoring Sessions

Thanks Johan for info,

We already tried with tcpdump and wireshark it was helpfull. Percona tool kit i 
need to try.




 From: Johan De Meersman 
To: Anupam Karmarkar  
Cc: mysql@lists.mysql.com 
Sent: Wednesday, 10 October 2012 5:16 PM
Subject: Re: Monitoring Sessions
 
- Original Message -
> From: "Anupam Karmarkar" 
> 
> How to monitor individual session number of rows selected or updated
> by sessions, number of bytes sent and reviewed by session in a given
> time period, sessions connects runs command and then disconnects,
> SHOW GLOBAL STATUS is not helping me in this case as i want low
> level session details, there are nearly 50's application server
> requesting to 1 databases server, which server is generating more
> traffic need to monitor and what kind of queries it is firing,
> binlog file are genrating nearly 7-8 GB daily.
> 
> Data trafic we can also get on network level but can we get more
> details as mention.

Well, you can look at the local (session) status, but that would require each 
session to actually store those, as you can't access them outside of the 
session.

Alternatively, you can use tcpdump and wireshark to capture traffic as it goes 
through the wire and look at what's happening. Percona Toolkit's 
pt-query-digest tool can also work with tcpdump logs.

The general log file will also save you entire sessions, but will do so for 
EVERY session that happens - which is going to make for a huge overhead on the 
busy machine you seem to be describing, so is definitely not recommended.

Someone also recently posted links towards an init-sql based approach which 
might be adapted, and towards a McAfee Auditing module for MyQSL that also 
seemed to hold some promise.


-- 
Linux Bier Wanderung 2012, now also available in Belgium!
August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be

-- 
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: How often the slave to pull data from master?

[David Lerer]

Thanks Shawn. Very informative and useful.  David.

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: How often the slave to pull data from master?

[David Lerer]

Thanks Shawn. This is very useful.
Could you shed some light on how rolled-back transactions (or not-yet-committed 
transactions for that matter) are treated as far as the binary logs?  Are these 
updates actually go to the binary logs, thus trigger replication to the salve?
Thanks, David.

-Original Message-
From: Shawn Green [mailto:shawn.l.gr...@oracle.com] 
Sent: Monday, July 30, 2012 11:22 AM
To: Zhigang Zhang
Cc: mysql@lists.mysql.com
Subject: Re: How often the slave to pull data from master?

On 7/29/2012 12:52 AM, Zhigang Zhang wrote:
> Hi
>> If there are additional events beyond that, it
> retrieves those in sequence after it writes the current statement into
> the relay logs.
>
> I have a question about this:
>
> Whether the slave is sent a signal whenever the master generates each event?
>

Yes, the slave receives a signal but it is not a TCP (networked) signal 
that the slave receives. Let me add a few more details to the 'retrieval 
of events from the Binary log' part of the replication process.

Each slave that connects to a master will open a 'dump thread' process 
that keeps track of the end of the binary log. This is a mini-daemon 
that runs in the context of the overall MySQL server. The slave holds 
and maintains an open connection with the dump thread and when there are 
no new events to transmit to the slave, the thread goes to sleep. This 
does not break the TCP/IP connection to the slave.

The 'signal' to the 'slave' is not sent via TCP to the remote server. It 
is sent to it's proxy, the dump thread, using a pthread_cond_signal 
event. This wakes the thread and initiates the streaming of new data to 
the slave.

The dump thread is initiated by the slave when it makes its connection. 
This is considered a 'slave process' as it is the dedicated local 
listener for the slave process. Each slave gets its own dump thread. 
When the slave disconnects, the dump thread is destroyed.

Using a low-level kernel signal is much more efficient (in terms of 
network usage and CPU cycles) than to continuously ping one server from 
the other. This is why we chose this design.

For any additional lower-level details than this, I encourage you to 
review the source as it can tell you much more than most would like to 
be shared in a general forum like this list.

Also, there is a more technical discussion on the internal mechanics of 
MySQL already in place. The proper place to ask for more details would 
be the Internals mailing list.
-- 
Shawn Green
MySQL 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: Subquery taking too much time on 5.5.18?

[David Lerer]

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%');

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


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: create alias for columns bound to database?

[David Lerer]

Dante, consider using views that are defined with your alternate column names 
and present the application with these views rather than underlying table names.
David.

-Original Message-
From: D. Dante Lorenso [mailto:da...@lorenso.com] 
Sent: Friday, May 18, 2012 5:22 PM
To: mysql@lists.mysql.com
Subject: create alias for columns bound to database?

All,

I'd like to be able to create column aliases which are bound to the 
database itself.  I have an assortment of columns without naming 
standards which i'd like to migrate to a better naming scheme.

Is there a feature in MySQL that would allow me to give a database 
column multiple names?  I'm thinking that for SELECT * statements, you 
would use the default column name, but for insert, delete, update, etc, 
it would be fine to use the aliased name or default.

Doing this would *really* help to allow me to migrate the database to 
the new naming convention without breaking existing code.  I would then 
be able to refactor at a more leisurely pace.

Does the feature exist, or can it be created?

-- Dante

D. Dante Lorenso
da...@lorenso.com
972-333-4139

-- 
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: MySQL slowlog - only in file?

[David Lerer]

Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the 
queries for very easy analysis. 
I have found it very effective, especially when you have  very high number of 
queries per second.
In turning on this capture, I have not noticed any impact on database 
performance. 
David.

-Original Message-
From: Carsten Pedersen [mailto:cars...@bitbybit.dk] 
Sent: Monday, May 14, 2012 6:16 AM
To: P.R.Karthik
Cc: Nilnandan Joshi; Rafał Radecki; mysql@lists.mysql.com
Subject: Re: MySQL slowlog - only in file?

Alternatively, you can copy the data into another table easily: 
http://www.bitbybit.dk/carsten/blog/?p=115

Best,

/ Carsten

On 14.05.2012 09:34, P.R.Karthik wrote:
> Hi Rafal,
>
> If there are more slow queries in your server and logging them into a table
> will increase the IO of the server.
> It is better to be in a file. The slow query log file can be processed
> easily by 
> pt-query-digest<http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html>
> .
>
> Regards,
> KarthiK.P.R
> MySQL DBA
>
>
> On Fri, May 11, 2012 at 2:47 PM, Nilnandan Joshiwrote:
>
>> Hi Rafal,
>>
>> If you are using MySQL 5.1 and later version than you can enable the log
>> tables and you can see slow queries in the log tables.
>> Please check this post:
>> http://nilinfobin.com/2012/03/slow_log-and-general_log-tables-in-mysql-5-1/
>>
>> regards,
>> Nilnandan
>>
>> On Fri, May 11, 2012 at 2:40 PM, Rafał Radecki>> wrote:
>>
>>> Hi all.
>>>
>>> Is there a possibility to see the info from slowlog somewhere in
>> database?
>>> I would like to see slow queries using mysql and not by watching the log
>>> file.
>>> I've searched on google and mysql website but hasn't found the solution.
>>>
>>> Best regards,
>>> Rafal Radecki.
>>>
>>
>

-- 
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: Deadlock due lockwait. How can I tell mysql to wait longer?

[David Lerer]

Going on a limb here...:  I believe I have occurred similar issue (i.e. two 
transactions go into an indefinite wait).Though, very infrequent 
occurrence.  
My only explanation at that time was that there is some "loophole" when the 
deletes/inserts had some impact also on the table indexes. In our case, the 
deletes/inserts statements were invoked by a stored procedure.

David.

-Original Message-
From: Johan De Meersman [mailto:vegiv...@tuxera.be] 
Sent: Monday, May 14, 2012 9:28 AM
To: Baron Schwartz
Cc: MySql
Subject: Re: Deadlock due lockwait. How can I tell mysql to wait longer?

- Original Message -
> From: "Baron Schwartz" 
> 
> Because it can be resolved by rolling back just one of them. Why
> destroy ALL the work people are trying to accomplish, if you could
> just throw away some of it?

What I fail to understand, Baron, is how there can be a deadlock here - both 
transactions seem to be hanging on a single-table, single-row update statement. 
Shouldn't the oldest transaction already have acquired the lock by the time the 
youngest came around; and shouldn't the youngest simply wait until the eldest 
finished it's update?

Or is this a problem with the consistent view that I'm not seeing?


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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: MySQL slowlog - only in file?

[David Lerer]

Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the 
queries for very easy analysis. 
I have found it very effective, especially when you have  very high number of 
queries per second.
I have not noticed any impact on database performance. 
David.

-Original Message-
From: Carsten Pedersen [mailto:cars...@bitbybit.dk] 
Sent: Monday, May 14, 2012 6:16 AM
To: P.R.Karthik
Cc: Nilnandan Joshi; Rafał Radecki; mysql@lists.mysql.com
Subject: Re: MySQL slowlog - only in file?

Alternatively, you can copy the data into another table easily: 
http://www.bitbybit.dk/carsten/blog/?p=115

Best,

/ Carsten

On 14.05.2012 09:34, P.R.Karthik wrote:
> Hi Rafal,
>
> If there are more slow queries in your server and logging them into a table
> will increase the IO of the server.
> It is better to be in a file. The slow query log file can be processed
> easily by 
> pt-query-digest<http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html>
> .
>
> Regards,
> KarthiK.P.R
> MySQL DBA
>
>
> On Fri, May 11, 2012 at 2:47 PM, Nilnandan Joshiwrote:
>
>> Hi Rafal,
>>
>> If you are using MySQL 5.1 and later version than you can enable the log
>> tables and you can see slow queries in the log tables.
>> Please check this post:
>> http://nilinfobin.com/2012/03/slow_log-and-general_log-tables-in-mysql-5-1/
>>
>> regards,
>> Nilnandan
>>
>> On Fri, May 11, 2012 at 2:40 PM, Rafał Radecki>> wrote:
>>
>>> Hi all.
>>>
>>> Is there a possibility to see the info from slowlog somewhere in
>> database?
>>> I would like to see slow queries using mysql and not by watching the log
>>> file.
>>> I've searched on google and mysql website but hasn't found the solution.
>>>
>>> Best regards,
>>> Rafal Radecki.
>>>
>>
>

-- 
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: big character constant

[David Turner]

select 'U02714','U02718';


insert into my_table values('U02714');

insert into my_table values('U02718');

Let me know if this is what you intended.

Dave





>
> From: "h...@tbbs.net" 
>To: mysql@lists.mysql.com 
>Sent: Friday, March 23, 2012 5:14 PM
>Subject: big character constant
> 
>How does one enter characters U02714 and U02718 in a query? or insert them 
>into a record?
>
>
>-- 
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql
>
>
>
>

Re: OT: SQL Question

[David Turner]

Jeff,

I would use a join table, teacher_flights.

create table teacher_flights(
teacher_id int(11) not null,
flight_id int(11) not null,
primary key(teacher_id, flight_id));

Dave




>
> From: Mark Phillips 
>To: Mysql List  
>Sent: Friday, March 23, 2012 7:28 PM
>Subject: OT: SQL Question
> 
>My question is not specific to MySQL, even though I am using a MySQL db for
>this project. I have a servlet/jsp/MySQL web site in production, and there
>are about 2,000 records in the flights table. One of the foreign keys is
>teacher_id. Up to this point, there is a one to many relationship between
>teacher_id and the data in the flights table. I need to change the data
>model to allow for a many to many relationship between teacher_id and the
>data in the flight table. What is the best way to do this?
>
>Thanks,
>
>Mark
>
>
>

RE: how to sync mysql.user table between to two mysql instances

[David Lerer]

I was trying to get a confirmation too. 
In any event, Charles, I'd try these three steps below and see what happens. I 
assume that information_schema is populated "on the fly".
David.

-Original Message-
From: Brown, Charles [mailto:cbr...@bmi.com] 
Sent: Friday, March 16, 2012 2:59 PM
To: David Lerer; Baron Schwartz; MySql
Subject: RE: how to sync mysql.user table between to two mysql instances

Hello David,

Precisely, that's what my problem is. The users found in mysqlinst1 are not in 
mysqlinst2. There are about 30 users defined in inst1 and only 4 in inst2. I 
would like to sync these tables. How do you do it in short of creating 26 
accounts in mysqlinst2 one at a time - too tedious. Help me I'm running out of 
time.



-Original Message-
From: David Lerer [mailto:dle...@us.univision.com] 
Sent: Friday, March 16, 2012 1:36 PM
To: Baron Schwartz; MySql; Brown, Charles
Subject: RE: how to sync mysql.user table between to two mysql instances

As a follow up question, will it be ok to do the following:

1. mysqldump -hmysql-inst2 mysql
2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 
3. mysql -hmysql-inst2 mysql

This way I hope to be able to refresh a DEV instance from a PROD database, but 
preserve the permissions, users, passwords, etc (Assuming of course that the 
schemas are identical on the two instances).

Thanks, David.

-Original Message-
From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of 
Baron Schwartz
Sent: Friday, March 16, 2012 2:20 PM
To: MySql
Subject: Re: how to sync mysql.user table between to two mysql instances

Charles,

1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. 
With pt-table-sync from Percona Toolkit if you need something more 
sophisticated.

On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles  wrote:
> Can someone instruct me on how to sync mysql.user table between to two mysql 
> instances.
> In other words, I wouild like to copy mysql.user from mysql-inst1 to 
> mysql-inst2 +

--
Baron Schwartz
Win free MySQL conference tickets! http://goo.gl/mvZ4W

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


This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.


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: how to sync mysql.user table between to two mysql instances

[David Lerer]

As a follow up question, will it be ok to do the following:

1. mysqldump -hmysql-inst2 mysql
2. Backup mysql-inst1 and use the backup to restore to mysql-inst2
3. mysql -hmysql-inst2 mysql

This way I hope to be able to refresh a DEV instance from a PROD database, but 
preserve the permissions, users, passwords, etc (Assuming of course that the 
schemas are identical on the two instances).

Thanks, David.

-Original Message-
From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of 
Baron Schwartz
Sent: Friday, March 16, 2012 2:20 PM
To: MySql
Subject: Re: how to sync mysql.user table between to two mysql instances

Charles,

1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql
2. With pt-table-sync from Percona Toolkit if you need something more
sophisticated.

On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles  wrote:
> Can someone instruct me on how to sync mysql.user table between to two mysql 
> instances.
> In other words, I wouild like to copy mysql.user from mysql-inst1 to 
> mysql-inst2 +

-- 
Baron Schwartz
Win free MySQL conference tickets! http://goo.gl/mvZ4W

-- 
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: preg_replace in update statement

[David Lerer]

 
Try with a combination of functions LOCATE and SUBSTR.
Locate will return the positions for WordA and WordB within the original text, 
and, SUBSTR will allow you to string what you you need all together.
David.

On Thu, Mar 8, 2012 at 4:11 PM, Hank  wrote:
> I have a simple problem:
>
> I have a varchar field in the database, and I want to remove all text
> between WordA and WordB, including WordA and WordB, leaving all text
> before WordA and after WordB intact.
>
> Possible with just SQL?  I know I can write a PHP program to do it,
> but it's not that important to spend that much time on.  I'd like one
> SQL statement to do it.
>
> Thanks!
>
> -Hank
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
Baron Schwartz
Percona Inc <http://www.percona.com/>
Consulting, Training, Support & Services for MySQL

-- 
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: query problem with null

[David Lerer]

Have you tried to set city = null   (i.e. without the quotes)? David.



-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com] 
Sent: Friday, March 09, 2012 4:24 PM
To: mysql@lists.mysql.com
Subject: query problem with null

 When I do the following query:

SELECT * FROM geo_trivia WHERE city IS NULL;

certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected when I run the above
query.  Can anyone help?

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: Getting data from 2 tables if records have same date!

[David Giragosian]

On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green wrote:

> On 2/29/2012 5:54 PM, LUCi5R wrote:
>
>> JW,
>>
>>
>>
>> I'm trying to understand LEFT JOIN as we go - but it's not working.
>>
>>
>>
>> This query
>>
>>
>>
>> SELECT *
>>
>> FROM CUSTOMERS
>>
>> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
>>
>> WHERE CUSTOMERS.DATE = "02/28/12" AND (CALLS.PHONE IS NULL OR CALLS.DATE =
>> "02/28/12")
>>
>>
>>
>> Is giving me some results which I'm not quite sure what they are - but
>> it's
>> not the right results.
>>
>>
>>
>> The way I'm testing is, on 02/28/12 I had 57 Customers created in the
>> CUSTOMERS table.
>>
>> I also had a total of 105 Calls recorded in the CALLS table. Some calls
>> were
>> from the same customers more then once.
>>
>>
>>
>> Essentially, I need the result to be 86 which I got from some manual
>> calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
>> but not in the CALLS table. The other 85 were in both tables.
>>
>>
>>
>> The above LEFT JOIN query gave me 69 records and quite a few duplicate
>> entries. I'm trying to dissect it to understand what exactly it selected.
>>
>>
>>
>> Thanks!
>>
>>
>>
>> ~~
>> LUCi5R
>> e:  luc...@luci5r.com
>> w:  http://www.luci5r.com
>>
>>
>>
>>
>>
>> From: Johnny Withers [mailto:joh...@pixelated.net]
>> Sent: Wednesday, February 29, 2012 1:30 PM
>> To: luc...@luci5r.com
>> Cc: mysql@lists.mysql.com
>> Subject: Re: Getting data from 2 tables if records have same date!
>>
>>
>>
>> Sounds like you need to LEFT JOIN:
>>
>>
>>
>> SELECT *
>>
>> FROM CUSTOMERS
>>
>> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE =
>> "02/28/12"
>>
>> WHERE CUSTOMERS.DATE = "02/28/12"
>>
>>
>>
>> But that would only get customers created on 2/28 AND having a call on
>> 2/28
>> OR not call at all on 2/28.
>>
>>
>>
>> This would give you customers created on 2/28 with no calls AND customers
>> created on 2/28 with a call on 2/28:
>>
>>
>>
>> SELECT *
>>
>> FROM CUSTOMERS
>>
>> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
>>
>> WHERE CUSTOMERS.DATE = "02/28/12" AND (CALLS.PHONE IS NULL OR CALLS.DATE =
>> "02/28/12")
>>
>>
> Try this:
>
> SELECT *
> FROM CUSTOMERS
> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
> CUSTOMERS.DATE=CALLS.DATE
> WHERE CUSTOMERS.DATE = "02/28/12"
>
> This will give you a list of all customers for a given date and a list of
> every call they made on that date.  If a customer made no calls on a date,
> then all of the columns for that table will be NULL.
>
> If you only want a list of customers and details about the calls on a date
> then an INNER JOIN is appropriate.  If you want to see the full list of
> customers and any calls on that date use this:
>
> SELECT *
> FROM CUSTOMERS
> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
> CUSTOMERS.DATE=CALLS.DATE = "02/28/12"
>
> If you only want a list of customers that made any calls on a given date,
> you can use the EXISTS comparator like this:
>
> SELECT customers.*
> FROM customers
> WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
> CUSTOMERS.DATE=CALLS.DATE = "02/28/12")
>
> http://dev.mysql.com/doc/**refman/5.5/en/exists-and-not-**
> exists-subqueries.html
>
> It's possible to get you any combination of data you want, we just need
> you to clarify the relationship you are trying to find and how much data
> you really want to get back.
>
> NOTE: the name of the column date is using a reserved word. You may want
> to enclose it in backticks to avoid confusion as in `date`. Also, the
> standard MySQL syntax for date literals uses ISO notation. So instead of
> using "02/28/12" (using double quotes) I expected to see '2012-02-28'
> (using single quotes)
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN



Another gem from Shawn. ;-)

RE: Read_only and InnoDB transactions

[David Lerer]

It may not have an impact on you, but be aware of this severe (imho) bug that 
caused read_pnly to be ignored regardless of running transactions in version 
5.5.8.
See bug#58669 and others.
We upgraded to 5.5.17 where the bug was fixed.
David.

From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com]
Sent: Monday, November 28, 2011 9:20 AM
To: David Lerer
Cc: mysql@lists.mysql.com
Subject: Re: Read_only and InnoDB transactions

MySQL Community Server 5.1.59 on the Centos 5.7

2011/11/28 David Lerer mailto:dle...@us.univision.com>>
What version do you use? David.

-Original Message-
From: Viacheslav Biriukov 
[mailto:v.v.biriu...@gmail.com<mailto:v.v.biriu...@gmail.com>]
Sent: Monday, November 28, 2011 7:09 AM
To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>
Subject: Read_only and InnoDB transactions

Hi all.

>From the Mysql Documentation:

If you attempt to enable read_only while other clients hold explicit table
> locks or have pending transactions, the attempt blocks until the locks are
> released and the transactions end. While the attempt to enable read_only is
> pending, requests by other clients for table locks or to begin transactions
> also block until read_only has been set.



But when I try to set "SET GLOBAL read_only = ON;" It brake running
transaction.

--
Viacheslav Biriukov
BR

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




--
Viacheslav Biriukov
BR
http://biriukov.com



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.

RE: Read_only and InnoDB transactions

[David Lerer]

What version do you use? David.

-Original Message-
From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] 
Sent: Monday, November 28, 2011 7:09 AM
To: mysql@lists.mysql.com
Subject: Read_only and InnoDB transactions

Hi all.

>From the Mysql Documentation:

If you attempt to enable read_only while other clients hold explicit table
> locks or have pending transactions, the attempt blocks until the locks are
> released and the transactions end. While the attempt to enable read_only is
> pending, requests by other clients for table locks or to begin transactions
> also block until read_only has been set.



But when I try to set "SET GLOBAL read_only = ON;" It brake running
transaction.

-- 
Viacheslav Biriukov
BR

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

SSIS and MySQL

[David Stoltz]

Hi Folks,

I'm in need to connect from my SQL 2008R2 (64 bit) server, to my MySQL
database server...

I found this page:
http://www.mysql.com/downloads/connector/net/

This page has the 32 bit drivers, but I *believe* I would need 64 bit,
since it's being installed on the 64 bit SQL server, correct?

Are there 64 bit drivers available? Does anyone have any advice for me?

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: credit where due

[David Giragosian]

On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman  wrote:

> While we have him online, I think we could all take a moment and be
> grateful
> for the contributions of Shawn Green.
>
> When I see the Oracle-bashing on this list, I am often reminded that we
> still have a hard-core MySQL developer who has survived the ride to Sun and
> again to Oracle who is still providing us with timely expert advice.
>
> Please, all of you, think twice before cutting up Oracle for their lack of
> MySQL support.  Shawn has been plying this list forever doling out sound
> advice and I have never heard him complain as we as we indirectly besmirch
> him over and and over.
>
> Thank you Shawn.
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>

I wholeheartedly agree with Michael about Shawn's contributions to this
list, and I have not been bashful about saying so publicly and privately.

David

RE: Concerned : Developer getting There is no 'root'@'%' registered error message

[David Lerer]

Thanks Bier. I see what you mean.
(As a rule we always use SQL SECURITY INVOKER)
David.

-Original Message-
From: Johan De Meersman [mailto:vegiv...@tuxera.be] 
Sent: Thursday, August 18, 2011 4:20 AM
To: David Lerer
Cc: mysql@lists.mysql.com
Subject: Re: Concerned : Developer getting There is no 'root'@'%' registered 
error message

- Original Message -
> From: "David Lerer" 
> 
> Correct me if I am wrong, but my impression is that usage of
> "DEFINER="  in stored procedures has no impact on runtime and
> is actually optional.
> (not to be confused with the clause SQL SECURITY INVOKER which is
> crucial.)

You can also say SQL SECURITY DEFINER, which makes the procedure run with the 
privileges of the user who defined it. At that point, the definer becomes very 
relevant indeed :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

RE: Concerned : Developer getting There is no 'root'@'%' registered error message

[David Lerer]

Correct me if I am wrong, but my impression is that usage of
"DEFINER="  in stored procedures has no impact on runtime and is
actually optional.
(not to be confused with the clause SQL SECURITY INVOKER which is
crucial.)
Note: I use 5.1.32-enterprise-gpl-advanced-log.

David.

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

RE: Import from Quicken 2004 Mac?

[David Brian Chait]

I don't think I have ever heard of anyone directly importing a QIF into any 
relational database, you would have to translate the resulting data into a 
delimited txt file and then import. You may want to check to see if Quickbooks 
has an API that you can use to access the data natively rather than trying to 
move it around from platform to platform.

-Original Message-
From: Jan Steinman [mailto:j...@bytesmiths.com] 
Sent: Thursday, August 11, 2011 2:37 PM
To: David Brian Chait
Cc: mysql@lists.mysql.com
Subject: Re: Import from Quicken 2004 Mac?

On 11 Aug 11, at 14:17, David Brian Chait wrote:

> The QIF file includes a lot of data aside from basic transactions, what 
> exactly are you trying to end up with at the end of the day? Simply a copy of 
> your QB data in Mysql?

That would be a good start. We don't need a complete duplicate, but in my 
experience, it's easier to get it all and winnow out the bits you don't want 
than to selectively import.

That said, we really only need the basic transaction info: date, payee, amount, 
memo, category, account from, account to.

This is to reconcile the chart of accounts (in Quick Books) with project 
management (in MySQL).

> From: Jan Steinman [mailto:j...@bytesmiths.com] 
> Sent: Thursday, August 11, 2011 2:15 PM
> To: mysql@lists.mysql.com
> Subject: Import from Quicken 2004 Mac?
> 
> I'm looking for ways to import QuickBooks 2010 Mac. I've only just started 
> researching this, so feel free to "RTFM" me -- with a proper reference, of 
> course!
> 
> I'll be wanting to set up a process to do this periodically (and hopefully, 
> automagically) for new transactions.
> 
> QB 2010 Mac appears to only export ".IIF" format, which appears to be a 
> variant of the older ".QIF" format, and Google didn't turn up really anything 
> for getting IIF/QIF files into MySQL. The best I could find would be 
> importing them into Excel first, then CSV out of Excel into MySQL, which 
> sounds like a lot of bother and not readily scriptable for routine use. I 
> find it hard to believe I'm the first one to ever attempt this!
> 
> IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import 
> tool, does anyone know of good parsers and translators for IIF/QIF that may 
> be useful?
> 
> Thanks in advance for any advice offered!
> 
> 
> Science uses mathematics to predict the future; economics uses statistics to 
> predict the past. -- Jeff Barton
>  Jan Steinman, EcoReality Co-op 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com
> 


Always do right. This will surprise some people and astonish the rest. -- Mark 
Twain
 Jan Steinman, EcoReality Co-op 


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

RE: Import from Quicken 2004 Mac?

[David Brian Chait]

The QIF file includes a lot of data aside from basic transactions, what exactly 
are you trying to end up with at the end of the day? Simply a copy of your QB 
data in Mysql?

-Original Message-
From: Jan Steinman [mailto:j...@bytesmiths.com] 
Sent: Thursday, August 11, 2011 2:15 PM
To: mysql@lists.mysql.com
Subject: Import from Quicken 2004 Mac?

I'm looking for ways to import QuickBooks 2010 Mac. I've only just started 
researching this, so feel free to "RTFM" me -- with a proper reference, of 
course!

I'll be wanting to set up a process to do this periodically (and hopefully, 
automagically) for new transactions.

QB 2010 Mac appears to only export ".IIF" format, which appears to be a variant 
of the older ".QIF" format, and Google didn't turn up really anything for 
getting IIF/QIF files into MySQL. The best I could find would be importing them 
into Excel first, then CSV out of Excel into MySQL, which sounds like a lot of 
bother and not readily scriptable for routine use. I find it hard to believe 
I'm the first one to ever attempt this!

IIF/QIF seems to be a rather unusual format. Lacking a one-step MySQL import 
tool, does anyone know of good parsers and translators for IIF/QIF that may be 
useful?

Thanks in advance for any advice offered!


Science uses mathematics to predict the future; economics uses statistics to 
predict the past. -- Jeff Barton
 Jan Steinman, EcoReality Co-op 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.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: Hungarian Notation [Was Re: Too many aliases]

[David Lerer]

I join you Arthur. That Hungarian notation is despicable (though I love 
listening to that language, it is different).
I don't find it necessary for a column name to tell me its type.  
But I do like the ability to have all database objects (table, column, trigger, 
index, fk, views, procedures, etc.) sortable and searchable. I use a prefix 
though. My prefix is a number for one reason: Ease of communication with stuff. 
A schema is assigned to a range of numbers. 
Sounds old fashioned? Cobolish? So? 
My 2c. David. 

- Original Message -
From: Arthur Fuller 
To: Martin Gainty 
Cc: mysql@lists.mysql.com 
Sent: Sun Aug 07 19:03:43 2011
Subject: Re: Hungarian Notation [Was Re: Too many aliases]

I despise this sort of notation, and have instead adopted what have
cheerfully named Hungarian Suffix notation, the reason being Signal-To-Noise
ratio. Instead of prefacing everything with some form of prefix, just do the
opposite:

Customer_tbl
Customer_Dead_boo
Customer_DOB_date
Customer_qs (that means Query Select)
Customer_qu (that means Query Update)
Customer_qd (that means Query Delete)
CustomerOrders_tbl
Customer_frm (a form that opens the Customer table; could involve subforms,
but in that case they are named Customer_Orders_fsub,
Customer_Payments_fsub, and so on.

Easy to read, obvious the intent, and easily sortable. Just my opinion.

Arthur

RE: Too many aliases

[David Lerer]

I agree. I use the same column name in all tables where it has the same
function - but I consistently add a suffix or prefix. And yes, it is the
old fashion way David.

-Original Message-
From: h...@tbbs.net [mailto:h...@tbbs.net] 
Sent: Thursday, August 04, 2011 8:26 AM
To: r...@grib.nl
Cc: mysql@lists.mysql.com
Subject: Re: Too many aliases

>>>> 2011/08/03 12:46 +0200, Rik Wasmus >>>>
But the 
main thing is it helps to distinguish tables  in joins having the same
table 
more then once (and of course results from subqueries etc.):

SELECT first.* 
FROM tablename first
LEFT JOIN   tablename second
   ONfirst.some_id = second.some_id
   AND first.id != second.id
WHERE second.id IS NULL 
<<<<<<<<
Well, yes, here it is needful. But it seems to me from most of the
examples that people here post, that they have the idea that it is the
style always to use one-letter aliases, whether it is helpful or not.

Now I do not do this, but I often see examples where a field for one
purpose has in one table one name, and in another table another,
slightly different, name, and then, too, I see alias used, although, in
this case, no table name at all is needed. (I like to use the same field
name in all tables where it has the same function.)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dle...@us.univision.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: Too many aliases

[David Lerer]

I rarely use aliases (unless rarely required in self-join queries).
When I have that option, I create unique columns by prefixing every
table (and its objects) with a number.
Something like:
Create table T1234_Employee
  (C1234_Employee_id number(5),
   C1234_employee_status char(1)...)
  Index X1234_Employee_Id on  Etc.

Yes, the column names may be longer this way, but easy to refer to and
easy to communicate (by specifying a table number). I wonder what others
think about it.

David.

-Original Message-
From: Rik Wasmus [mailto:r...@grib.nl] 
Sent: Wednesday, August 03, 2011 6:47 AM
To: mysql@lists.mysql.com
Subject: Re: Too many aliases

> >>>> 2011/08/02 12:11 +0530, Adarsh Sharma >>>>
> 
> select p.* from table A p, B q where p.id=q.id
> 
> or
> 
> select p.* from table B q , A p where q.id=p.id
> <<<<<<<<
> Why do people constantly change table names for queries, although, as
here,
> it gain them nothing? It often makes for less clarity (for which table
is
> this an alias???).

Depens on your table names. I rather like being able to give a short 
description rather then long table names if someone decided that as a 
tablename. I doubt your example with already short tablenames is one
from real 
life, but if you saw someone doing it would indeed be a waste of time.
But the 
main thing is it helps to distinguish tables  in joins having the same
table 
more then once (and of course results from subqueries etc.):

SELECT first.* 
FROM tablename first
LEFT JOIN   tablename second
   ONfirst.some_id = second.some_id
   AND first.id != second.id
WHERE second.id IS NULL
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dle...@us.univision.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: Which is better

[David Lerer]

Hard to tell. It depends on the cardinality of tables' id (I assume the
IDs are not unique in each of the tables). David.

-Original Message-
From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com] 
Sent: Tuesday, August 02, 2011 2:47 PM
To: Adarsh Sharma
Cc: mysql@lists.mysql.com
Subject: Re: Which is better

On 8/2/2011 02:41, Adarsh Sharma wrote:
> Dear all,
>
> Just want to know which join is better for querying data faster.
>
> I have 2 tables A ( 70 GB ) & B ( 7 MB )
>
> A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.
>
> select p.* from table A p, B q where p.id=q.id
>
> or
>
> select p.* from table B q , A p where q.id=p.id
>
>
> Thanks
>

There is no difference in performance. The optimizer will change the 
sequence it uses to read the tables according to its own rules.

If you had used STRAIGHT JOIN to force a particular execution path, the 
it would normally be faster to read the smaller table first.

-- 
Shawn Green
MySQL 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?unsub=dle...@us.univision.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: URGENT: Change Default Location of where Database Files get written?

[David Brian Chait]

Add:
datadir=/path/to/datadir/mysql

to your my.cnf file and restart mysql.

-Original Message-
From: Tina Matter [mailto:ti...@umich.edu] 
Sent: Friday, May 13, 2011 8:22 AM
To: mysql@lists.mysql.com
Subject: URGENT: Change Default Location of where Database Files get written?

I have a MySQL question that I'm hoping someone can help answer.

We have a linux machine which has MySQL 5.5.8 installed.
It is currently installed in this location:/opt/mysql

When creating a new database, a folder (with the name of the databas) 
gets created in this location:
/opt/mysql/data

Is there any way to change the location of where data is stored?
The database that I need to create is going to have over a billion 
records in it,
so it needs to be in a specific place.

I want the database folder to get created here:

/science/databases/databasename

Thanks for any help.
Tina

-- 
Tina Matter
Web Applications Developer
University of Michigan
Department of Epidemiology
1415 Washington Heights, Suite 4605
Ann Arbor, MI 48109


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.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: problem starting server

[David Brian Chait]

Go to the cmd line and type ps -ef , do you see mysqld listed? If so then it is 
already running.


-Original Message-
From: Gergely Buday [mailto:gbu...@gmail.com] 
Sent: Tuesday, April 26, 2011 5:56 AM
To: mysql@lists.mysql.com
Subject: problem starting server

Hi there,

I installed a mysql server on my Mac using homebrew.

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.91, for apple-darwin10.0 (i386) using
EditLine wrapper

When trying to start it I get

$ mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file
(/usr/local/var/mysql/dbxserver.lan.pid).

What should I do to start MySQL?

- Gergely

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.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 Enterprise support now at Oracle?

[David Giragosian]

On Thu, Mar 10, 2011 at 5:05 PM, Jim McNeely  wrote:

> Shawn Green works for Oracle and has been very helpful, and I am happy to
> eat a little bit of shoe leather!
>
> Thanks Shawn!
>
> Jim
>

Check the archives for Shawn's posts. IMNSHO, they are unparalleled in
clarity and depth and breadth of useful information.

David

RE: centos server's mysql version's problem

[David Brian Chait]

Just so I am clear here, you have 8 different DB servers hosting the same data 
that is being accessed and potentially modified simultaneously? You may want to 
consider the replication issues involved in that sort of arrangement with 
mysql. Aside from that, I am currently hosting an 18gb mysql database that is 
passing roughly 6gb/hr without any issue utilizing a single Dell R710 and mysql 
5.0.77. Your mileage may differ based on the low end hardware/storage that you 
plan to use, the number of writes and the amount of data that you can cache in 
memory.


From: Yang Yang [mailto:dapiy...@gmail.com]
Sent: Sunday, February 27, 2011 12:38 PM
To: David Brian Chait
Cc: Johnny Withers; mysql@lists.mysql.com
Subject: Re: centos server's mysql version's problem

about 8,000,000 visit,and about 40,000,000 sql do

about 2g database,and the large table who aften use about 400m

2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII

2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII


2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII

2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII


2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII

2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII


2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII

2x Intel Xeon Nehalem e5520 - 2.26GHz 1TB SATAII(*4   raid 10)   8gb ram

thanks
2x Intel Xeon Nehalem e5520 - 2.26GHz

500 GB SATAII


2011/2/28 David Brian Chait mailto:dch...@invenda.com>>


> -Original Message-
> From: Yang Yang [mailto:dapiy...@gmail.com<mailto:dapiy...@gmail.com>]
> Sent: Sunday, February 27, 2011 12:29 PM
> To: Johnny Withers
> Cc: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>
> Subject: Re: centos server's mysql version's problem

> thanks johnny

> what about 5.1,did it performance better than 5.0 on centos when it has
> large traffic?

> 5.1 is newer,but i know not newer is better

How much traffic is "large traffic"? How large is your database? How is it 
structured? What type of hardware are you using?

-David

RE: centos server's mysql version's problem

[David Brian Chait]

> -Original Message-
> From: Yang Yang [mailto:dapiy...@gmail.com] 
> Sent: Sunday, February 27, 2011 12:29 PM
> To: Johnny Withers
> Cc: mysql@lists.mysql.com
> Subject: Re: centos server's mysql version's problem

> thanks johnny

> what about 5.1,did it performance better than 5.0 on centos when it has
> large traffic?

> 5.1 is newer,but i know not newer is better


How much traffic is "large traffic"? How large is your database? How is it 
structured? What type of hardware are you using?

-David


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

RE: Replacing MS SQL with MySql

[David Brian Chait]

No, what he is suggesting is that you use Access or MSSQL, and link Mysql to 
either platform via ODBC so that you can use it indirectly. All databases are 
not the same in terms structure and capabilities..you most likely will not be 
able to swap one out for another and make it work directly with your app. If 
there is an absolute requirement to use Mysql then you may be better off 
finding a different front end software that supports it, if not then embrace 
the fact that you will in all likelihood be maintaining a MS DB server for it. 
Not that it is such a bad thing, most corporate environments are heterogeneous 
these days, not geared toward one platform vs. another.

-David

-Original Message-
From: Y z [mailto:yan...@hotmail.com] 
Sent: Wednesday, February 09, 2011 8:32 AM
To: vegiv...@tuxera.be
Cc: mysql@lists.mysql.com
Subject: RE: Replacing MS SQL with MySql



Thanks. The developer cheerily informs me that Access is 'deprecated' and will 
be phased out. So, SQL it is. 

I'm not sure what you mean by 'passthrough'. Do you mean
MYSQL
  |
ODBC
  |
Win app
?

If so, where do I go to learn how to configure MySQL and ODBC to dance together 
nicely, and talk to the app?

Thanks!

> No way to do that directly; however, using the MySQL ODBC connector you can
> get at least a) and c) to play passthrough. Performance will likely suffer,
> though; especially Access' Jet Engine has a tendency to pull in full remote
> datasets instead of passing through the query.
>
>
> On Wed, Feb 9, 2011 at 6:36 AM, Y z  wrote:
>
> >
> > I have a windows app that wants to talk to either a) an access database, b)
> > a MS Sql Express database, or c) a MS Sql 2008 database.
> >
> > Can anyone please point me in the direction of configuring My Sql to
> > imitate any of the above?
> >
> > Thanks!
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
  
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.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: Replacing MS SQL with MySql

[David Brian Chait]

To borrow your line of reasoning, translators can be rather slow and 
unreliable. Adding the extra overhead and complexity is certainly not worth the 
potential gains.


-Original Message-
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De 
Meersman
Sent: Tuesday, February 08, 2011 10:54 PM
To: Reindl Harald
Cc: Y z; mysql@lists.mysql.com
Subject: Re: Replacing MS SQL with MySql

On Wed, Feb 9, 2011 at 7:18 AM, Reindl Harald wrote:

> Am 09.02.2011 06:36, schrieb Y z:
> >
> > I have a windows app that wants to talk to either a) an access database,
> b) a MS
> > Sql Express database, or c) a MS Sql 2008 database.
> >
> > Can anyone please point me in the direction of configuring My Sql to
> > imitate any of the above?
>
> no way if the application has no abstraction-layer
> you can not easy switvh from one rdbms to another
> because in a closed source app you will even not
> can connect
>
> after connect there are hughe differences in many details
>
> so if a application does not support a specific rdbms
> you can not use it
>

Remember, TIMTOWTDI:-)
If someone you want to talk to knows only one language, find an
interpreter. MS Access apparently speaks the same language as his app; and
using ODBC it can also talk MySQL. Link the tables in Access and let the app
use them as if they were regular tables.




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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 do increase memory allocated to MySQL?

[David Brian Chait]

That is exactly what I was worried about. He could try restructuring the data, 
increasing the timeouts to compensate for the long run time, or find better 
hardware. Any other ideas?

From: w...@pythian.com [mailto:w...@pythian.com] On Behalf Of Singer X.J. Wang
Sent: Thursday, February 03, 2011 7:20 PM
To: Yannis Haralambous
Cc: David Brian Chait; mysql@lists.mysql.com; y...@mpi-inf.mpg.de
Subject: Re: How do increase memory allocated to MySQL?

You're query is doing a full table scan.
2011/2/3 Yannis Haralambous 
mailto:yannis.haralamb...@telecom-bretagne.eu>>
just a single table with an ID field (primary index) and three data fields 
(indexed):

id  bigint(20)  NO  PRI NULLauto_increment
yagoid  varchar(32) NO  UNI NULL
yagoleftvarchar(32) NO  MUL NULL
yakoright   varchar(255)NO  NULL

what am I doing wrong?

the query was just

SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'

Le 4 févr. 2011 à 11:14, David Brian Chait a écrit :

> Yannis,
>   How is the data structured? Can you give us an example of the queries 
> that you are trying to run? Do you have indexes in place? A very inefficient 
> query, or poorly structured database can lead to this type of timeout issue 
> on the type of low end hardware that you are using.
>
> Thanks,
> David
>
> -Original Message-
> From: Yannis Haralambous 
> [mailto:yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu>]
> Sent: Thursday, February 03, 2011 5:43 PM
> To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>
> Cc: y...@mpi-inf.mpg.de<mailto:y...@mpi-inf.mpg.de>
> Subject: How do increase memory allocated to MySQL?
>
> Hi everybody,
>
> I have loaded a very big amount of data in my MySQL database (coming from the 
> YAGO project):
>
> -rw-rw  1 yannis  admin   65  3 fév 16:07 db.opt
> -rw-rw  1 yannis  admin   6392030392  3 fév 21:35 wasfoundin.MYD
> -rw-rw  1 yannis  admin  11085793280  4 fév 04:54 wasfoundin.MYI
> -rw-rw  1 yannis  admin 8668  3 fév 16:09 wasfoundin.frm
>
> as you can see the MYI file is 11Gb and the MYD file 6.4Gb.
>
> Whenever I try to send a query through phpmyadmin, it crashes and I get 
> returned to the initial page of phpmyadmin.
> When I send an SQL query directly, it works, but it takes many minutes (a 
> simple SELECT will take about 10 minutes).
>
> I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6 GHz 
> Intel Core 2 with 4Gb of RAM).
>
> What can I do to make phpmyadmin work? Is it a chance to gain more speed when 
> interacting through regular SQL queries, or is it hopeless?
> Where can I find more information about using MySQL with such big tables?
>
> Thanks in advance
>
> --
> ---
> Yannis Haralambous
> Directeur d'études
> ADRESSE TEMPORAIRE :
> University of Aizu
> Aizu-Wakamatsu, Fukushima-ken  965-8580, Japon ADRESSE PERMANENTE :
> Institut Télécom, Télécom Bretagne
> Département Informatique
> UMR CNRS 3192 Lab-STICC
> Technopôle Brest Iroise
> CS 83818, 29238 Brest Cedex 3, France
> Tel: +33 2 29 00 14 27
> Fax: +33 2 29 00 12 82
> Email: 
> yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu>
> Internet: http://omega.enstb.org/yannis
> ICBM address: 48°21'31.57"N 4°34'16.76"W
> Twitter: y_haralambous
> ---
> ...pour distinguer l'extérieur d'un aquarium, mieux vaut n'être pas poisson
>
> ...the ball I threw while playing in the park has not yet reached the ground
>
> Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächtens den 
> Bäumen und Sternen von ihm vorerzählen mögen.
>

--
---
Yannis Haralambous
Directeur d'études
ADRESSE TEMPORAIRE :
University of Aizu
Aizu-Wakamatsu, Fukushima-ken  965-8580, Japon
ADRESSE PERMANENTE :
Institut Télécom, Télécom Bretagne
Département Informatique
UMR CNRS 3192 Lab-STICC
Technopôle Brest Iroise
CS 83818, 29238 Brest Cedex 3, France
Tel: +33 2 29 00 14 27
Fax: +33 2 29 00 12 82
Email: 
yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu>
Internet: http://omega.enstb.org/yannis
ICBM address: 48°21'31.57"N 4°34'16.76"W
Twitter: y_haralambous
---
...pour distinguer l'extérieur d'un aquarium,
mieux vaut n'être pas poisson

...the ball I threw while playing in the park
has not yet reached the ground

Es gab eine Zeit, wo ich nur ungern über Schubert sprechen,
nur Nächtens den Bäumen und Sternen von ihm vorerzählen mögen.





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


--

The best compliment you could give Pythian for our service is a referral.

RE: How do increase memory allocated to MySQL?

[David Brian Chait]

Yannis,
How is the data structured? Can you give us an example of the queries 
that you are trying to run? Do you have indexes in place? A very inefficient 
query, or poorly structured database can lead to this type of timeout issue on 
the type of low end hardware that you are using.

Thanks,
David

-Original Message-
From: Yannis Haralambous [mailto:yannis.haralamb...@telecom-bretagne.eu] 
Sent: Thursday, February 03, 2011 5:43 PM
To: mysql@lists.mysql.com
Cc: y...@mpi-inf.mpg.de
Subject: How do increase memory allocated to MySQL?

Hi everybody,

I have loaded a very big amount of data in my MySQL database (coming from the 
YAGO project): 

-rw-rw  1 yannis  admin   65  3 fév 16:07 db.opt
-rw-rw  1 yannis  admin   6392030392  3 fév 21:35 wasfoundin.MYD
-rw-rw  1 yannis  admin  11085793280  4 fév 04:54 wasfoundin.MYI
-rw-rw  1 yannis  admin 8668  3 fév 16:09 wasfoundin.frm

as you can see the MYI file is 11Gb and the MYD file 6.4Gb.

Whenever I try to send a query through phpmyadmin, it crashes and I get 
returned to the initial page of phpmyadmin.
When I send an SQL query directly, it works, but it takes many minutes (a 
simple SELECT will take about 10 minutes).

I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6 GHz 
Intel Core 2 with 4Gb of RAM).

What can I do to make phpmyadmin work? Is it a chance to gain more speed when 
interacting through regular SQL queries, or is it hopeless?
Where can I find more information about using MySQL with such big tables?

Thanks in advance 

--
---
Yannis Haralambous
Directeur d'études
ADRESSE TEMPORAIRE :
University of Aizu
Aizu-Wakamatsu, Fukushima-ken  965-8580, Japon ADRESSE PERMANENTE :
Institut Télécom, Télécom Bretagne
Département Informatique
UMR CNRS 3192 Lab-STICC
Technopôle Brest Iroise
CS 83818, 29238 Brest Cedex 3, France
Tel: +33 2 29 00 14 27
Fax: +33 2 29 00 12 82
Email: yannis.haralamb...@telecom-bretagne.eu
Internet: http://omega.enstb.org/yannis
ICBM address: 48°21'31.57"N 4°34'16.76"W
Twitter: y_haralambous
---
...pour distinguer l'extérieur d'un aquarium, mieux vaut n'être pas poisson

...the ball I threw while playing in the park has not yet reached the ground

Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächtens den 
Bäumen und Sternen von ihm vorerzählen mögen.


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

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Harkness]

On Fri, Jan 21, 2011 at 4:44 AM, Dotan Cohen  wrote:

> Then I would have to check what values are available when inserting,
> and possibly normalise every so often. I'll think about that, and when
> I have enough data in the database I'll set up a test system to play
> with the possibility.
>

Yes, it's a much more complicated process, and one that I wouldn't enter
into without verifying there was a performance problem. The neat thing is
you could do this at any point as you can renumber the left and right values
at will--as long as you don't *reorder* them.


> I see, thanks. Good point about making sure that the problem exists
>
before trying to fix it, I've seen people optimise away where there is
> no bottleneck.
>

People who love to work on complicated problems often fall victim to
premature optimization because it's fun to do. I was guilty of this myself
earlier in my career, but as CPUs and memory and disk speeds increased over
the decades, it's harder to justify it. At least back then the optimization
that wasn't *necessary* still had a good payoff. Nowadays you can waste days
shaving 10ms off of an operation that takes 1s. Congratulations, you've just
poured $$ down the drain. But boy wasn't that fun! :)

Good luck with the implementation. I'd love to hear how it pans out once
you're done, and I'm sure the list would find it useful as well.

David

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Harkness]

On Thu, Jan 20, 2011 at 12:21 PM, Dotan Cohen  wrote:

> I understood that. My concern is exactly with adding new nodes. There
> is no incrementor (++i) in SQL, so knowingly coding a solution that
> will require incrementing two fields in half the database rows seems
> irresponsible.
>

It only requires updating the category rows. If you have several hundred
categories this is a non-issue. If you have several thousand categories, you
probably have millions of products, and you'd want to do some performance
analysis on it. Even still, this is necessary only when adding new
categories.

If you are doing this often, you could leave spaces in the left and right
values so that you could minimize the number of rows that need to be
updated. The article makes every leaf use x and x+1 for left and right which
forces another update to add a child. If instead you used x and x+20 you'd
leave space for more children without any updates. This could be applied
from top to bottom, starting with the root category getting 0 and MAX_INT
for its values.

However, it's probably not even worth applying that complexity until you
prove that frequent category additions are causing problems. Most systems
will be querying against the categories table far more frequently, and
that's where this model pays off. If you want to see all products in
category X and its subcategories, it's a single *non-recursive* query.
That's huge if you are doing a lot of searches like this.

But what a mess this would be if the two methods go out of sync!
>

Sure, but these values would be maintained by your code--not end-users. It
just comes down to making sure your code is correct through appropriate unit
tests. By moving the logic to a stored procedure, you can ensure the table
is locked during the updates to keep two users from adding a new category
simultaneously.

That pays off more? For the guy writing code or for the database
> memory requirement?
>

Performance-wise. The nested set method looks to be moderately more complex
code-wise, but luckily that is done just once while querying the database is
done again and again. As with all optimizations, it's best to measure and
make sure there's a problem before trying to solve it. Once you've built a
few hierarchical systems, you'll be able to make a gut call up front.

Only two update statements, but they are affecting on average half the
> database's rows!
>

Of a single table: categories. Hopefully you have far more items that get
categorized than you do categories.


> Which do you call the hierarchical model? That term is not used in the
> linked article.
>

Well, both models are hierarchical in the sense that there's a parent-child
relationship. By hierarchical here I mean that the method of implementation
involves each category pointing to its parent directly via a parent_id
column. Searching for all subcategories of category X requires searching
first for all children, then all grandchildren, and so on, resulting in a
recursive query.

Using the nested sets model requires a single non-recursive query to get the
same data.

David

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Harkness]

On Thu, Jan 20, 2011 at 7:00 AM, Richard Quadling wrote:

> I'd recommend using a nested set approach for the tags
> (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
> gives a good explanation on the issues and methodology of nested
> sets).
>

Thanks for the link. That article proposes an interesting way to organize
the categories. Have you implemented this in the wild? Clearly the design
would work as it's pretty simple, and I like that it removes the need for
recursive queries.

Dotan, the Venn diagrams are just used to explain the concept. If you use
the code to determine the left and right values, you can ignore the diagrams
entirely. As long as you're not adding/removing categories every minute,
having to recalculate left and right values isn't that big of a deal.

Also, there's no reason you couldn't keep the parent_id field with the
nested sets. It would come in handy for certain types of queries, though
it's not necessary.

On Thu, Jan 20, 2011 at 11:40 AM, Jerry Schwartz  wrote:

> I disagree. The method I proposed can be extended to any depth, and any
> leaf or branch can be retrieved with a single query.
>

The nested set method can be extended to any depth, and it pays off more the
larger the hierarchy grows. While you can retrieve any branch (all
ancestors) of a node with a single SQL query, the SQL engine itself actually
must perform a recursive query meaning multiple hits on the parent_id index.

On Thu, Jan 20, 2011 at 11:59 AM, Dotan Cohen  wrote:

> I suppose for retrievals this structure has advantages, but unless
> MySQL has a ++ operator (or better yet, one that adds or subtracts 2
> from an int) then it looks to be a pain to add nodes.
>

++ or += wouldn't be any better here than x = x + 2. Once you're modifying
indexed values, you'll pay a much higher price writing to disk than += could
ever save you in CPU cycles. The beauty is that inserting a node requires
only two update statements that will fix *all* categories that need to be
adjusted. Adding categories to the hierarchical model is definitely faster
so it comes down to your insert-to-select ratio. Moving a subtree is also
much easier with the hierarchical model.

David

Re: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Hutto]

On Thu, Jan 20, 2011 at 2:40 PM, Jerry Schwartz  wrote:
>>-Original Message-
>>From: Dotan Cohen [mailto:dotanco...@gmail.com]
>>Sent: Thursday, January 20, 2011 11:25 AM
>>To: Jerry Schwartz
>>Cc: mysql.; php-general.
>>Subject: Re: Organisational question: surely someone has implemented many
>>Boolean values (tags) and a solution exist
>>
>>
>>> As for setting up a hierarchy, that's trickier. One way to handle that is
>>> to
>>> work like libraries do: 10 is "fiction", 10.05 is "crime novels", 10.05.07
>>> is
>>> "British authors", and so forth. Your `tags` table then looks like
>>>
>>
>>Thanks. I prefer the "parent tag" field, though, I feel that it is
>>more flexible.
>>
>>
> [JS] I disagree. The method I proposed can be extended to any depth, and any
> leaf or branch can be retrieved with a single query.

No one argues with method of implementation(not that i explored the
individual ideas), but with that available, you can hierarchy the
concept of your db conceptualization.


>
> 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
>
>
>
>>--
>>Dotan Cohen
>>
>>http://gibberish.co.il
>>http://what-is-what.com
>
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



-- 
The lawyer in me says argue...even if you're wrong. The scientist in
me... says shut up, listen, and then argue. But the lawyer won on
appeal, so now I have to argue due to a court order.

Furthermore, if you could be a scientific celebrity, would you want
einstein sitting around with you on saturday morning, while you're
sitting in your undies, watching Underdog?...Or better yet, would
Einstein want you to violate his Underdog time?

Can you imagine Einstein sitting around in his underware? Thinking
about the relativity between his pubic nardsac, and his Fruit of the
Looms, while knocking a few Dorito's crumbs off his inner brilliant
white thighs, and hailing E = mc**2, and licking the orangy,
delicious, Doritoey crust that layered his genetically rippled
fingertips?

But then again, J. Edgar Hoover would want his pantyhose intertwined
within the equation.

However, I digress, momentarily.

But Einstein gave freely, for humanity, not for gain, other than
personal freedom.

An equation that benefited all, and yet gain is a personal product.

Also, if you can answer it, is gravity anymore than interplanetary static cling?

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

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Hutto]

On Thu, Jan 20, 2011 at 2:26 PM, Dotan Cohen  wrote:
> On Thu, Jan 20, 2011 at 21:24, David Hutto  wrote:
>>> Is this a troll? Am I about to be baited?
>>
>> Baited to deploy what is designed to the consumer's specification?
>> Surely. From what is wanted to what is needed. Troll on that.
>
> Actually, I'm the customer! But assuming that a customer exists, that
> implies compensation, and therefore fair bait.
Then that's different altogether. you get to decide what information
is displayed, and what information is 'sensed', and on what platform.

What do you want to sense and what do you want to display(not to say
I'm an expert, but I like to think in CS)?

>
> --
> Dotan Cohen
>
> http://gibberish.co.il
> http://what-is-what.com
>



-- 
The lawyer in me says argue...even if you're wrong. The scientist in
me... says shut up, listen, and then argue. But the lawyer won on
appeal, so now I have to argue due to a court order.

Furthermore, if you could be a scientific celebrity, would you want
einstein sitting around with you on saturday morning, while you're
sitting in your undies, watching Underdog?...Or better yet, would
Einstein want you to violate his Underdog time?

Can you imagine Einstein sitting around in his underware? Thinking
about the relativity between his pubic nardsac, and his Fruit of the
Looms, while knocking a few Dorito's crumbs off his inner brilliant
white thighs, and hailing E = mc**2, and licking the orangy,
delicious, Doritoey crust that layered his genetically rippled
fingertips?

But then again, J. Edgar Hoover would want his pantyhose intertwined
within the equation.

However, I digress, momentarily.

But Einstein gave freely, for humanity, not for gain, other than
personal freedom.

An equation that benefited all, and yet gain is a personal product.

Also, if you can answer it, is gravity anymore than interplanetary static cling?

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

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Hutto]

> Is this a troll? Am I about to be baited?

Baited to deploy what is designed to the consumer's specification?
Surely. From what is wanted to what is needed. Troll on that.
>
> --
> Dotan Cohen
>
> http://gibberish.co.il
> http://what-is-what.com
>



-- 
The lawyer in me says argue...even if you're wrong. The scientist in
me... says shut up, listen, and then argue. But the lawyer won on
appeal, so now I have to argue due to a court order.

Furthermore, if you could be a scientific celebrity, would you want
einstein sitting around with you on saturday morning, while you're
sitting in your undies, watching Underdog?...Or better yet, would
Einstein want you to violate his Underdog time?

Can you imagine Einstein sitting around in his underware? Thinking
about the relativity between his pubic nardsac, and his Fruit of the
Looms, while knocking a few Dorito's crumbs off his inner brilliant
white thighs, and hailing E = mc**2, and licking the orangy,
delicious, Doritoey crust that layered his genetically rippled
fingertips?

But then again, J. Edgar Hoover would want his pantyhose intertwined
within the equation.

However, I digress, momentarily.

But Einstein gave freely, for humanity, not for gain, other than
personal freedom.

An equation that benefited all, and yet gain is a personal product.

Also, if you can answer it, is gravity anymore than interplanetary static cling?

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

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Hutto]

Pseudo = Design Algorithm
Design Algorithm = Actual Code
Actual Code = Alterable db tables
Alterable db tables = manipulated data through the app interface with data

-- 
The lawyer in me says argue...even if you're wrong. The scientist in
me... says shut up, listen, and then argue. But the lawyer won on
appeal, so now I have to argue due to a court order.

Furthermore, if you could be a scientific celebrity, would you want
einstein sitting around with you on saturday morning, while you're
sitting in your undies, watching Underdog?...Or better yet, would
Einstein want you to violate his Underdog time?

Can you imagine Einstein sitting around in his underware? Thinking
about the relativity between his pubic nardsac, and his Fruit of the
Looms, while knocking a few Dorito's crumbs off his inner brilliant
white thighs, and hailing E = mc**2, and licking the orangy,
delicious, Doritoey crust that layered his genetically rippled
fingertips?

But then again, J. Edgar Hoover would want his pantyhose intertwined
within the equation.

However, I digress, momentarily.

But Einstein gave freely, for humanity, not for gain, other than
personal freedom.

An equation that benefited all, and yet gain is a personal product.

Also, if you can answer it, is gravity anymore than interplanetary static cling?

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

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[David Harkness]

I cannot agree more with the others about using a join table. While it's
tempting to go with your first solution due to fear of performance issues,
you can usually address performance issues with a technical solution.
Addressing problems that arise from a constraining design choice is much
more difficult.

David

Re: GRANT ALL error - newbee

[David Giragosian]

On Wed, Sep 15, 2010 at 12:10 PM, Gary Roach wrote:

> I'm attempting to set up a Linux Apache Mysql PHP  (LAMP) system for the
> first time. On my internal network (behind firewall) I have a computer
> (cruncher) that is acting as the web server. Another computer (supercrunch)
> is being used as the home for Dupal6. I connected to the cruncher system
> from supercrunch with "mysql -u root -h cruncher -p". This seemed to work
> fine. But, one of the setup statements follows along with the result. I
> can't find the error. Help!
>
> mysql> GRANT ALL PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY
> '' WITH GRANT OPTION;
> 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 'PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY 'qatip' WITH
> GRANT OPTION' at line 1
>
> Leaving out the quotes makes no difference.
>
> Gary R
>
> Gary, if that is the actual command, you've misspelled PRIVILEGES.

HTH,

David

Command MySQL Check

[David Florella]

Hi, 

 

I want to check daily a large database. If I execute a "mysqlcheck", its
execution is longer the 8 minutes. 

 

What is the best practice to check tables daily ? medium option? Fast
option? 

 

Regards, 

 

David 

 

RE: phpMyAdmin and other management tools

[David Stoltz]

Folks - I've check out several application people have suggested.

I LOVE Toad for mySQL, and Spotlight for mySQL.

Thanks for all the advice!

-Original Message-
From: Steven Staples [mailto:sstap...@mnsi.net] 
Sent: Friday, July 02, 2010 8:22 AM
To: mysql@lists.mysql.com
Subject: RE: phpMyAdmin and other management tools

I personally use SQLYog, and as for it not having the "query
creation"... it
has a fairly decent query builder, but it is in the paid version, not
the
community one.

I like all the features it has, and I use most of them.  Granted, it is
always a good idea to know how to use the command line, just incase
you're
locked out of it somehow, or if you need to repair, backup or create
users.
But if you're a windoze user, 99% of them don't know what a command line
is
:)


Steven Staples



> -Original Message-
> From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com]
> Sent: July 1, 2010 7:58 AM
> To: David Stoltz
> Cc: mysql@lists.mysql.com
> Subject: Re: phpMyAdmin and other management tools
> 
> Hi Dave,
> 
> I recommend you to use "Toad for MySQL".
> 
> It has wizard for query creation phpMyAdimn, mysql workbench, sqlyog
lack
> this feature.
> 
> Krishna
> 
> On Thu, Jul 1, 2010 at 5:08 PM, David Stoltz  wrote:
> 
> > Hi Folks,
> >
> >
> >
> > I'm currently using phpMyAdmin to manage the mySQL databases. I'm
> > wondering what most people like to use? I know there is "mySQL
> > Workbench", which I haven't really fooled with yet
> >
> >
> >
> > Can anyone comment on what they use and why?
> >
> >
> >
> > Thanks!
> >
> > Dave
> >
> >
> >
> >
> 
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.830 / Virus Database: 271.1.1/2972 - Release Date:
06/30/10
> 02:36:00


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


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

phpMyAdmin and other management tools

[David Stoltz]

Hi Folks,

 

I'm currently using phpMyAdmin to manage the mySQL databases. I'm
wondering what most people like to use? I know there is "mySQL
Workbench", which I haven't really fooled with yet

 

Can anyone comment on what they use and why?

 

Thanks!

Dave

 

RE: INSERT with auto increment

[David Stoltz]

Awesome - thanks all for that clarification!


-Original Message-
From: Michael Dykman [mailto:mdyk...@gmail.com] 
Sent: Wednesday, June 30, 2010 1:42 PM
To: David Stoltz
Cc: mysql@lists.mysql.com
Subject: Re: INSERT with auto increment

generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)


If you don't list the columns, it assumes you are inserting all of them,
so:

INSERT INTO TABLE1 (mycolumn  ) VALUES ('stuff')


This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')

the auto-increment will engage on an insert of 0

 - michael dykman


On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz  wrote:
> Hi All,
>
>
>
> In MS SQL, if the table has an identity field/primary key which is set
> to auto increment, you can leave the value out of an INSERT statement,
> and the next highest value will be automatically inserted...
>
>
>
> For instance, with a two column table I could do "INSERT INTO TABLE1
> VALUES('stuff')"
>
>
>
> I'm having trouble doing the same thing in mySQL...
>
>
>
> In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
> VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
> don't have a matching number of columns.
>
>
>
> The field in question has a foreign key in another table, making this
a
> primary key in theory, but there's nothing in myphpadmin that shows
this
> as a primary key - perhaps this is the problem?
>
>
>
> Need some guidance
>
>
>
> Thanks!
>
> Dave
>
>



-- 
 - 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?unsub=arch...@jab.org

INSERT with auto increment

[David Stoltz]

Hi All,

 

In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...

 

For instance, with a two column table I could do "INSERT INTO TABLE1
VALUES('stuff')"

 

I'm having trouble doing the same thing in mySQL...

 

In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.

 

The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?

 

Need some guidance 

 

Thanks!

Dave

RE: Table creation fail

[David Stoltz]

Actually,

 

That table isn't supposed to have a PK, so I removed that, and it
works...same effect you suggested.

 

Thanks!

 

From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] 
Sent: Wednesday, June 23, 2010 12:29 PM
To: David Stoltz
Cc: mysql@lists.mysql.com
Subject: Re: Table creation fail

 

Hi, 

default cannot be used with primary key.

mysql> CREATE TABLE `testresults` (
->
-> `id_employees` INTEGER DEFAULT NULL ,
-> `id_test_test` INTEGER DEFAULT NULL ,
-> `testdate` DATE DEFAULT NULL ,
-> `result` VARCHAR( 10 ) DEFAULT NULL ,
-> `resultsdescription` MEDIUMTEXT DEFAULT NULL ,
-> `resultsdate` DATE DEFAULT NULL ,
-> `nextdate` DATE DEFAULT NULL ,
-> `ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
-> `givenhere` INTEGER not null primary key);
Query OK, 0 rows affected (0.11 sec)

Krishna



On Wed, Jun 23, 2010 at 9:51 PM, David Stoltz  wrote:

Hi Folks,



I use an online SQL Design tool to design my tables, etc. This generates
script code that I can run in phpMySQL to create the tables, etc.



The below code is causing an error - see below:



CREATE TABLE `testresults` (

`id_employees` INTEGER DEFAULT NULL ,
`id_test_test` INTEGER DEFAULT NULL ,
`testdate` DATE DEFAULT NULL ,
`result` VARCHAR( 10 ) DEFAULT NULL ,
`resultsdescription` MEDIUMTEXT DEFAULT NULL ,
`resultsdate` DATE DEFAULT NULL ,
`nextdate` DATE DEFAULT NULL ,
`ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
`givenhere` INTEGER DEFAULT NULL ,
PRIMARY KEY ( )

);

MySQL said:

#1064 - 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 11



The bolded line above (resultsdate line) seems to be causing the
problemnothing looks wrong to me



Any ideas?



Thanks!

 

Table creation fail

[David Stoltz]

Hi Folks,

 

I use an online SQL Design tool to design my tables, etc. This generates
script code that I can run in phpMySQL to create the tables, etc.

 

The below code is causing an error - see below:

 

CREATE TABLE `testresults` ( 

`id_employees` INTEGER DEFAULT NULL ,
`id_test_test` INTEGER DEFAULT NULL ,
`testdate` DATE DEFAULT NULL ,
`result` VARCHAR( 10 ) DEFAULT NULL ,
`resultsdescription` MEDIUMTEXT DEFAULT NULL ,
`resultsdate` DATE DEFAULT NULL ,
`nextdate` DATE DEFAULT NULL ,
`ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
`givenhere` INTEGER DEFAULT NULL ,
PRIMARY KEY ( ) 

);

MySQL said: 

#1064 - 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 11

 

The bolded line above (resultsdate line) seems to be causing the
problemnothing looks wrong to me

 

Any ideas?

 

Thanks!

RE: Replication : request DELETE is not executed on slave

[David Florella]

Hi, 

Thanks to you and everyone. 

I will test the same request with the ORDER BY clause. 

Regards, 

David. 

-Message d'origine-
De : Mattia Merzi [mailto:mattia.me...@gmail.com] 
Envoyé : mercredi 28 avril 2010 17:54
À : mysql@lists.mysql.com
Objet : Re: Replication : request DELETE is not executed on slave

AFAIR you can use LIMIT with replication only if you use row-based
replication (or mixed), that means that you must use mysql 5.1.

Greetings,

Mattia.


2010/4/28 Tom Worster :
> 16.3.1.9. Replication and LIMIT
> Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT
> statements is not guaranteed, since the order of the rows affected is not
> defined. Such statements can be replicated correctly only if they also
> contain an ORDER BY clause.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dflore...@legos.fr


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

RE: Replication : request DELETE is not executed on slave

[David Florella]

Hi, 

In the MySQL documentation, it is written that the two versions are
compatible to make a replication. 

It seems that if I make a DELETE without the 'LIMIT 7500', the query is
replicated to the slave.

Regards, 

David.


-Message d'origine-
De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] 
Envoyé : mercredi 28 avril 2010 11:15
À : dflore...@legos.fr
Cc : mysql@lists.mysql.com
Objet : Re: Replication : request DELETE is not executed on slave

Hi dflorella,

The important thing about mysql replication is same mysql version for both
master as well as slave should be used. It should be taken as good practice.

You need to check that master and slave are in sync. Is there any error
(replication) on the slave server. Check the mode, strict or some thing
else.

Does the delete command exits in binlog.

Regards,
Krishna


On Wed, Apr 28, 2010 at 2:37 PM, David Florella  wrote:

> Hi,
>
>
>
> I am using MySQL replication :
>
>
>
> -  The version of the master is 4.1.12-log
>
> -  The version of the slave is 5.0.41
>
>
>
> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
> LIMIT 7500", the query is executed on the master but not on the slave.
>
>
>
> Do you know why the request is not executed on the slave?
>
>
>
> Regards,
>
>
>
> David.
>
>


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

Replication : request DELETE is not executed on slave

[David Florella]

Hi,

 

I am using MySQL replication : 

 

-  The version of the master is 4.1.12-log 

-  The version of the slave is 5.0.41

 

When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
LIMIT 7500", the query is executed on the master but not on the slave. 

 

Do you know why the request is not executed on the slave?

 

Regards, 

 

David. 

RE: Make delete requests without impact on a database

[David Florella]

Hi evryone, 

Thank you for the advice. I will analyze the differences between InnoDB and
MyISAM before switching. 

Moreover, there is a lot of indexes on the tables. I was thinking about
tunning the MySQL server. Do you know how can I benchmark the tunning of the
server before doing the tunning?

Regards, 

David. 

-Message d'origine-
De : phark...@gmail.com [mailto:phark...@gmail.com] De la part de Perrin
Harkins
Envoyé : jeudi 15 avril 2010 02:36
À : Dan Nelson
Cc : David Florella; mysql@lists.mysql.com
Objet : Re: Make delete requests without impact on a database

On Wed, Apr 14, 2010 at 10:22 AM, Dan Nelson 
wrote:
> Switch to InnoDB :)

Seconded.  No need to complicate your life with MyISAM workarounds
when InnoDB solves this problem already.

- Perrin




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

Make delete requests without impact on a database

[David Florella]

Hi, 

 

I am using MySQL  version 4.1.12-log. All the databases on it are using
MyISAM database engine. 

 

Every day, I delete almost 9 rows on a table of 3 153 916 rows.

 

To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this request
until all the rows are delete.

 

This works but when I run the request, I can't access to the database (make
INSERT and SELECT requests) during I do the DELETE. 

 

How can I do a "DELETE" without impact on INSERT and SELECT requests done on
the same time?

 

Regards, 

 

David. 

 

Factors for slower mysqldump on superior hardware vrs faster dump on slower hardware. Same install. Why?

[David Taveras]

Hello,

Iam using OpenBSD 4.6 GENERIC kernel with mysql-server .

Machine A (faster)

AMD Athlon(tm) 64 X2 Dual Core Processor 5200+ ("AuthenticAMD"
686-class, 512KB L2 cache) 2.72 GHz
2GB of RAM

wd0 at pciide1 channel 0 drive 0: 
wd0: 16-sector PIO, LBA48, 152627MB, 312581808 sectors
wd0(pciide1:0:0): using PIO mode 4, Ultra-DMA mode 5

Capacity. 160GB. Interface. SATA 3Gb/s. Cache. 8MB. Model ST3160813AS
SATA 3Gb/s 160GB

Machine B:
Dell PowerEdge R200
Intel(R) Xeon(R) CPU X3330 @ 2.66GHz ("GenuineIntel" 686-class) 2.67 GHz

sd0 at scsibus0 targ 0 lun 0:  SCSI3 0/direct fixed
sd0: 237464MB, 512 bytes/sec, 486326272 sec total

Perc6 RAID controller in RAID1 ( i have even tried with one hard disk
and no raid controller and the results are not improved by much) They
are also SATA discs behind.


In fact.. I have also some ogther more sophisticated hardware then
Machine A, Dual Cores, Quad Cores.. and even machine A is faster with
the same mysqldump.

Machine A takes 2.20s
Machines B take 3.30s

Both being idle.


What am I looking in a machine to  make it as fast as machine A ?

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

Re: count children nodes

[David Arroyo Menendez]

Thanks!

2010/2/16 Peter Brawley 

>  David,
>
> >I need count the messages don'tread in a thread.
>
> Have a look at the edge list examples at
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
>
> PB
>
> -
>
> David Arroyo Menendez wrote:
>
> Hello,
>
> I've the next table structure:
>
> CREATE TABLE tx_cc20_mensajes (
> uid int(11) NOT NULL auto_increment,
> pid int(11) DEFAULT '0' NOT NULL,
> tstamp int(11) DEFAULT '0' NOT NULL,
> crdate int(11) DEFAULT '0' NOT NULL,
> cruser_id int(11) DEFAULT '0' NOT NULL,
> deleted tinyint(4) DEFAULT '0' NOT NULL,
> hidden tinyint(4) DEFAULT '0' NOT NULL,
> remitente int(11) DEFAULT '0' NOT NULL,
> destinatario int(11) DEFAULT '0' NOT NULL,
> padre int(11) DEFAULT '0' NOT NULL,
> mensaje text,
> leido tinyint(3) DEFAULT '0' NOT NULL,
>
> PRIMARY KEY (uid),
> KEY parent (pid)
> );
>
> Where padre is the id of the parent message. I need count the messages don't
> read in a thread. How can I do it?
>
> With
> $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
> deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
> (padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
> I am counting only the first level, but I need count the rest of children
> messages. What is the query?
>
> Thanks!
>
>
>
> --
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 
> 07:35:00
>
>
>
>

count children nodes

[David Arroyo Menendez]

Hello,

I've the next table structure:

CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
deleted tinyint(4) DEFAULT '0' NOT NULL,
hidden tinyint(4) DEFAULT '0' NOT NULL,
remitente int(11) DEFAULT '0' NOT NULL,
destinatario int(11) DEFAULT '0' NOT NULL,
padre int(11) DEFAULT '0' NOT NULL,
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,

PRIMARY KEY (uid),
KEY parent (pid)
);

Where padre is the id of the parent message. I need count the messages don't
read in a thread. How can I do it?

With
$query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
(padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
I am counting only the first level, but I need count the rest of children
messages. What is the query?

Thanks!

Re: how things get messed up

[David Giragosian]

On Thu, Feb 11, 2010 at 8:56 AM, Martijn Tonies wrote:

> Hello John,
>
>  About 5 years ago, I was asked to write a php app for my department. The
>> app keeps track of graduate school applicants to my department at the
>> university. The main data elements are the scores each professor gives to
>> each applicant. There are only about 400 applicants each year so even with
>> all the personal data, scores, transcripts,  etc for each student, it's not
>> much. for the first 2 years, it was under a meg of data. Well, then the
>> selection committee asked me to add something so that if a student e-mailed
>> the department a document, say a paper he'd written or a photo of himself,
>> or whatever, it could be tacked on to the info they saw about him while
>> grading the applicant.
>>
>> So I said, "Well, there is only going to be maybe 10 or 20 of those a
>> year. And even if all 400 applicants submit a PDF of a paper they'd written,
>> it would be only 400 docs. 4,000 after 10 years. Yeah, lets just create a
>> documents table in the database and store them in mysql."
>>
>> For the first 2 years, only 2 students sent in documents to attach to
>> their application. I figured I'd wasted my time. Then the next year, the
>> graduate school changed their  web application form to allow students to
>> upload documents. "Fine," I said, "My worst case scenario has already come
>> true. But, well, this is why you plan for the worst case."
>>
>> Then they started taking letters of recommendation as PDF documents. In
>> fact, they started requiring PDF docs. Each student has 3 to 6 letters of
>> recommendation. All in all, I figure we're at about 100 times as many docs
>> in our database as I originally expected and about 10x my worst case
>> scenario.
>>
>> I should either be fired or shot.  Maybe fired *then* shot. Actually, its
>> not as bad as all that. I can pretty easily write a perl script to export
>> the docs to files and access them via a network mounted filesystem. After
>> all, saving myself 5 hours of work 5 years ago is worth what? -- maybe
>> 10hours today? It is amazing how often quick & dirty turns out just being
>> dirty in the end.
>>
>
> Not sure what the problem is really... What are you running into?
>

I think John is just sharing an experience - a lesson learned if you will.
With the same spirit in mind, many projects in my work culture begin with a
specification of, "Just put up anything so our (internal) users can react to
it." Talk about vague. Geesh!  However, a senior programmer told me years
ago that the life of a programmer is often filled with doing, undoing, and
redoing. And not enough appreciation for the work involved. I try to keep
that in mind.

David

Re: Is anything ever equal to NULL?

[David Giragosian]

On Mon, Dec 28, 2009 at 5:41 PM, Carsten Pedersen wrote:

> David Giragosian skrev:
>
>> On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso 
>> wrote:
>>
>>  Will anything ever be equal to NULL in a SELECT query?
>>>
>>
> ...
>
>  What's so special about NULL?
>>>
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
>>
>> Should answer some of your questions, Dante.
>>
>
> Oddly enough, that page fails to mention the <=> operator for which NULL
> does indeed equal NULL.
>
>
> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to
>
> / Carsten
>

Good pick-up, Carsten. And that's definitely a new concept for me.

David



-- 

There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa

Re: Is anything ever equal to NULL?

[David Giragosian]

On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso  wrote:

>
> Will anything ever be equal to NULL in a SELECT query?
>
>  SELECT *
>  FROM sometable
>  WHERE somecolumn = NULL;
>
> I have a real-life query like this:
>
>  SELECT *
>  FROM sometable
>  WHERE somecolumn = NULL OR somecolumn = 'abc';
>
> The 'sometable' contains about 40 million records and in this query, it
> appears that the where clause is doing a sequential scan of the table to
>  find a condition where 'somecolumn' = NULL.  Shouldn't the query parser be
> smart enough to rewrite the above query like this:
>
>  SELECT *
>  FROM sometable
>  WHERE FALSE OR somecolumn = 'abc';
>
> And therefor use the index I have on 'somecolumn'?  When I manually rewrite
> the query, I get the performance I expect but when I leave it as it was,
> it's 100 times slower.
>
> What's so special about NULL?


http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Should answer some of your questions, Dante.

Re: inserting sets of data

[David Giragosian]

On Sat, Dec 12, 2009 at 9:54 AM, Victor Subervi wrote:

> Hi;
> I have a column defined as a set. How do I insert data into that column?
> Please give me an example.
> TIA,
> Victor
>


Lots of examples here:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

David

-- 

There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa

Re: How can I improve this query?

[David Shere]

Tom Worster wrote:
> how about using LEFT JOIN:
>
> SELECT ...
> FROM listings a
> LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber

This gives me a result set of 456,567 lines.  I'm looking for a result
set of 60-70 lines.  (That's how many part numbers we have.)

> and for speed, does Transactions.PartNumber have an index?

Done... I didn't think that you could do that for columns where 
duplicates were allowed.


mos wrote:
> Do the Left Join as the other person said and also replace the
> "Distinct" "Order By" with "Group by PartNumber" and you won't need the
> sort.

SELECT a.PartNumber, count(1)
FROM listings a
LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
group by a.PartNumber

This gives results that almost look right (66 lines), however there are 
some unrealistically high numbers.  The transactions totals for each 
part number, when added together, come to 545,325.  This is obviously 
wrong, because there are only 7000 transactions.


My original query does exactly what I want it to; it just takes 30 
seconds to run.  Whatever improvement (if any is possible) I make to the 
query would need to produce the same results.  Someone has already done 
this comparison by hand, and we've eliminated the listings with no 
transactions, so I can't run this again to see if it still comes up with 
part numbers for which there are listings but no transactions.  It would 
still be useful to know how to speed up the query in the future, though.


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

How can I improve this query?

[David Shere]

select distinct a.PartNumber as 'Part Number', (
   select count(1)
   from Transactions b
   where b.PartNumber = a.PartNumber) as 'No. Sold'
from listings a
order by a.PartNumber

It currently takes 30 seconds to run. Transactions has 1200 records and 
listings has 7000.


Multiple listings can have the same part number, as can transactions. 
We'd like to know how many transactions there are for each part number, 
including those part numbers for which there are listings but no 
transactions.  Given the "and zero transactions" requirement, I can't 
figure out how to do this query with a join.


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

Can not connect to MYSQL server on Windows Vista 64 bit

[David Parham]

I have a vb.net app that was running fine on two XP computers using 
Mysql 5.0 server.   I switched to a new Vista 64 bit computer, and now 
it gives me a message that it can not connect to any mysql hosts.In 
Navicat, when I try to setup the connection, it gives message   "2003 - 
Can't connect to mysql server on Dell1440 (10060)


I have added port 3306 to the windows firewall, and after it didn't 
work, totally turned off the firewall.   Now if I bring up the app, and 
go into a one record at a time update form, the first record will 
appear, but it won't page to the next one without the failure on the 
connection.


Any ideas as to what I need to do to make it work properly.

Thanks,

David


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

Re: Another Join Problem

[David Giragosian]

On Fri, Oct 2, 2009 at 10:53 AM, Victor Subervi wrote:

> Hi;
> I get the following error:
>
> *SQL query:*
>
> SELECT ID, Item
> FROM products
> JOIN categories ON categories.ID = products.Category
> LIMIT 0 , 30;
>
>  *MySQL said:*
>  #1052 - Column 'ID' in field list is ambiguous
>
> Please note the error is about ambiguity. "products" has an ID field and so
> does "categories". If I run the statement taking out the "ID" from the
> select, it runs. So, where is the ambiguity??
> TIA,
> V


Just prefix the ID with either table name like products.ID or categories.ID.

David

Re: Create Syntax (easy)

[David Giragosian]

On Tue, Sep 29, 2009 at 11:09 AM, Victor Subervi wrote:

> Hi;
> Please give me the syntax below such that I can force the insert statements
> to use only selected values ("item1", "item2", "item3"):
>
> create table (field SOMETHING_HERE item1 item2 item3,
> ...
> )
>
> TIA,
> Victor
>


CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
  );
From: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

David

-- 

There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa

Upgrading from 5.0.32 via a replication chain and bug 24432

[David Harrison]

Hi all,

I've got a quite large database (23G) that is running on a 5.0.32
version of MySQL.  I really want to upgrade out of 5.0.32 to the
latest version of 5.1 (or even 5.4) but a straight mysql_upgrade of
the database takes long enough that I'd have serious down-time issues
(last time I benchmarked the upgrade it came in at over day).

To try and work around this I wanted to set up binary replication from
my current database (as master) to a new database.  This has meant
that I have run across bug #24432
(http://bugs.mysql.com/bug.php?id=24432) which means that replication
is broken from my current version to versions above 5.0.34.

The bug listing includes the following replication table:

  master  (-inf, 5.0.23)[5.0.24, 5.0.34]  [5.0.36,+inf)
slave
(-inf, 5.0.23)BUG#20188 both bugs, no error   BUG#20188
[5.0.24, 5.0.34]  this bug, no errorno bugthis bug, no error
[5.0.36, +inf)no bugthis bug, error   no bug

which seems to indicate to me that there's no way out of the version
I'm running aside from a mysql_upgrade.

Does anyone have experience of this bug, or of any options I have that
wouldn't require a significant outage ?

Cheers
Dave

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

Downloading old version of MySQL ?

[David Harrison]

Hi all,

I'm trying to drag a database through the upgrade process from 5.0.32
but I keep running into small cross-version compatibility issues.

At present my live database is running 5.0.32 and is large enough that
it would take too long (read days) to perform an upgrade on, and
drives an important application for us that can't tolerate a
significant outage.  It's also gets frequent writes.

The plan I'm pursuing involves taking a db dump I have from a while
back, importing it into a newer version of MySQL, then letting
replication bring that newer version up to date.  This way I can bring
things along jump by jump until I make it to current.  However I keep
hitting replication bugs that mean I have to do this is small jumps -
for example I was working on 5.0.32 to 5.0.81 and hit this one
http://bugs.mysql.com/bug.php?id=24432.

So it looks like I now need a 5.0.34 install to transition through,
but I can't find anywhere on the MySQL site that offers access to
previous releases like that, does anyone know where I can find them ?

I'm equally open to better solutions to my upgrade approach if anyone
has any :-)

Cheers
Dave

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

More ways to debug mysql slowness..?

[David Taveras]

Hello,

We have a BSD box with the following installed:

mysql-client-5.0.77 multithreaded SQL database (client)
mysql-server-5.0.77 multithreaded SQL database (server)
p5-DBD-mysql-4.010  MySQL drivers for the Perl DBI
php5-mysql-5.2.8mysql database access extensions for php5


We are experiencing intermittent slowdowns on the queries made with PHP to
mysql to the point where pages take a lot of time to load, upon further
investigation with mytop we observe that it only keep an average of 1-2
simultenaous threads and a query time of avg 2-3 seconds.

During which the mysqld process reaches 99% continously for minutes.

We have repaired and optimized the tables, and the DB is 200mb. The storage
engine is MyISAM.

I understand that further optimization can be done to my.cnf , that has been
done a lot but with the same results.. andbefore I go to that path again my
question is:



Iam wondering what other tools exist to load test the mysql daemon, or how
to better debug this situation... more tools must exist out there? Perhaps
there must be a PHP/DB that I can load... and run a stress test like you
would test network issues with speedtest.net just a thought.. I know you
dont compare apples to oranges.

Thanks

David

Re: Anyone using LVM for backing up?

[David Sparks]

Little, Timothy wrote:
> We have a 20 gig db (that includes the MYIs and MYDs and FRMs).
> 
> We are wondering how long LVM snapshots take.. in that how long might
> the DB be read-locked?  Do we have to read-lock it and flush tables?

Take a look at mylvmbackup which takes care of flushing tables, creating and
destroying the snapshot, etc:

http://www.lenzg.net/mylvmbackup/

Expect a serious performance hit while the lvm snapshot is active.

ds


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

Confused about syntax for specific join with 3 tables

[David M. Karr]

I've been doing some experimenting with the data model from the "MySQL" 
book (Addison Wesley).  I have no trouble understanding joins between 
two tables, but I'm finding it's a little confusing when 3 or more 
tables are involved.  I'm going to cite a particular set of tables and a 
specific query.  I would have assumed it would need to be one way, but 
it actually requires a different approach, which I don't quite understand.


Here are the table creation scripts:

CREATE TABLE student
(
  name   VARCHAR(20) NOT NULL,
  sexENUM('F','M') NOT NULL,
  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (student_id)
) ENGINE = InnoDB;

CREATE TABLE grade_event
(
  date DATE NOT NULL,
  category ENUM('T','Q') NOT NULL,
  event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (event_id)
) ENGINE = InnoDB;

CREATE TABLE score
(
  student_id INT UNSIGNED NOT NULL,
  event_id   INT UNSIGNED NOT NULL,
  score  INT NOT NULL,
  score_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (score_id),
  INDEX (student_id),
  FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
  FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;

So, the query I want to build will list the quiz (not test) scores for a 
particular student.


If I were to construct this "logically", I would think the query would 
be this:


select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id = 
score.event_id

where student.student_id = 1 and grade_event.category='Q';

I visualize it as "student" joining to "score" joining to "grade_event".

Unfortunately, this query fails to parse with an unhelpful error message.

The query that works, with the joins out of the order I expected, is the 
following:


select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id = 
score.event_id

where student.student_id = 1 and grade_event.category='Q';

Can someone please go into detail of why what I first tried didn't work, 
and why it needs to be the other way?


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

Re: Resetting MySQL Root Password

[David Giragosian]

On 4/27/09, Jason Todd Slack-Moehrle  wrote:

> Hi All,
>
> CentOS 5.3
>
> I installed MySQL Server via yum and started it.
>
> I tried entering:
>
> mysqladmin -u root password yourrootsqlpassword
> mysqladmin -h server1.example.com -u root password yourrootsqlpassword
>
> But I get:
>
> r...@server1 ~]# /usr/bin/mysqladmin -u root -h localhost password
> mypassword
> /usr/bin/mysqladmin: connect to server at 'localhost' failed
> error: 'Access denied for user 'root'@'localhost' (using password: NO)'
>
> How can I reset this and allow Root access, otherwise nobody has access!
>
> Thanks,
>
> -Jason


You need a -p before the password in your command line.

David

Re: Oracle , what else ?

[David Sparks]

Glyn Astill wrote:
>> Begone Postgres troll!
> 
> Oh the hostility of a scorned mysql user. Joshua has posted no more FUD
> than you mysql chaps have done yourselvs over the past few days. You were
> worried about the future and he's posted a few ideas of how you can
> prepare.

No he didn't.  He posted doom and gloom:

"It will be a supported but second class citizen from Oracle."

"Oracle is not interested in the 1000/yr business. For the most
part that is where MySQL revenue is."

"maintain it long enough to allow MySQL to kill itself."

"I would expect that MySQL in two years likely won't exist except on the
most tertiary level."

One more time: begone Postgres troll!


Switching gears ...

All said, I'm cautiously optimistic that Oracle taking over the reins to Mysql
will benefit all.  Mysql is the long running leader in the open source
database space, and with the DB smarts of Oracle behind it I expect to see the
gap between Mysql and the other open source DB servers widen, not close up.

Mysql is getting better at a pace that is making the other open source DB
servers irrelevant.

ds

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

Re: Oracle , what else ?

[David Sparks]

Joshua D. Drake wrote:
> I would expect that MySQL in two years likely won't exist except on the
> most tertiary level. Most new projects will be developed in either
> PostgreSQL, Interbase or one of the forks (MariaDB, Drizzle).
> 
> Sincerely,
> 
> Joshua D. Drake
> 
> --
> PostgreSQL - XMPP: jdr...@jabber.postgresql.org

Your FUD would be better posted on a Postres list with all the onging
discussions on how Mysql doesn't support foreign keys, transactions, etc.

Begone Postgres troll!

ds

-- 
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 good US Hosting Site?

[David Giragosian]

I would recommend PilotPig << http://www.pilotpig.net/ >>

Good service, good support, reasonably priced, and integrity to boot.

David

How to change where NULL values sort?

[David M. Karr]

I think normally NULL values will sort at the end, correct?  I believe 
there's a way to make NULL values sort at the beginning, but I can't 
remember how to do it.  I just searched a couple of MySQL resources, but 
I couldn't find it.


--
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 runs on 16-cores server

[David Sparks]

> Right now if you want a more scalable *current* version of
> MySQL, you need to look to the Google patches, the Percona builds (and
> Percona XtraDB, a fork of InnoDB), or OurDelta builds.

Is there a webpage somewhere that compares and contrasts the above patchsets?

I thought the Google patches were mostly in the OurDelta patchset?

ds

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

Partitioning suggestion/workaround needed

[David Sparks]

Hi all,

I'm just creating my first partitioned table and have run into a bit of a
snag.  The table primary key is a double and I want to create partitions based
on ranges of the key.

I have read the Partition Limitations section in the docs which states that
the partition key must be, or resolve to an integer.  I can't figure out how
to cast/convert the double value into an integer that is usable by the
partition calculation.

This is basically what I'm trying to do:

create table t1 (
id double not null,
data char(32) not null,
primary key (id)
)
engine=innodb
partition by range(cast(id as int)) (
partition p1 VALUES LESS THAN (1239257000),
partition p2 VALUES LESS THAN (1239258000),
partition p3 VALUES LESS THAN MAXVALUE
);


What workarounds are there to partition a table keyed on a double?

Note: the double value is a Perl HiRes time.

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: how do I select multiple conditions from 1 table column?

[David Giragosian]

On Wed, Apr 1, 2009 at 1:27 PM, PJ  wrote:

> I am trying to select all books whose authors last names begin with I, J
> or K. I have 3 tables: book, author and book_author. The following query
> works with one condition but not with three.
> SELECT * FROM book
>WHERE id IN (SELECT bookID
>FROM book_author WHERE authID IN (SELECT author.id
>FROM author WHERE LEFT(last_name, 1 ) = 'I'));
>
> This does not work:
> SELECT * FROM book
>WHERE id IN (SELECT bookID
>FROM book_author WHERE authID IN (SELECT author.id
>FROM author WHERE LEFT(last_name, 1 ) = 'I' && LEFT(last_name, 1
> ) = 'J' && LEFT(last_name, 1 ) = 'K')) ";
>
> But this produces irrational results - there are no author names with
> the last names starting with I, J or K.
> SELECT * FROM book
>WHERE id IN (SELECT bookID
>FROM book_author WHERE authID IN (SELECT author.id
>FROM author WHERE LEFT(last_name, 1 ) = '$Auth' &
> LEFT(last_name, 1 ) = '$Auth1' & LEFT(last_name, 1 ) = '$Auth2')) ";
>
> I'm a little lost here.
> Could somebody explain, please?
>
> Maybe the LIKE operator would be sufficient:

SELECT * from book
WHERE
last_name LIKE "I%" OR
last_name LIKE "J%" OR
last_name LIKE "K%";

David