RE: io thread very slow copying binlogs

[Michael Dykman]

If you are confident that it's not the network, that pretty much leaves RAM or 
disk as the source of your problem. Assuming that you are seeing no performance 
issues on the master (I expect you would have mentioned), let's focus on the 
slave.

The behaviour you describe sounds to me like you have entered swap space: 
everythingthing is working fine, just in incredibly slow motion.
What does vmstat report?  Try someithng like 
  $ vmstat 2 60
and see if any swap traffic can be observed?

How about cat /proc//status ? What does VmSwap suggest?

 - michael dykman

-Original Message-
From: Brad Barnett [mailto:mysql-general-l...@l8r.net] 
Sent: Friday, January 20, 2017 10:45 AM
To: mysql@lists.mysql.com
Subject: Re: io thread very slow copying binlogs



Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least not 
one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log increasing in 
size by a M every 4 or 5 seconds or so.  About 200kbyte/sec / 1.6Mbit/sec right 
now.

It seems very steady too.  As in, if I look at bytes, they're constantly 
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker" <morgan.toc...@oracle.com> wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk 
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth 
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto 
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL 
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#
> option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be 
> considered broken faster.  If you have the throughput on a graph it 
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

--
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: Query Summary Help...

[Michael Dykman]

I'm not at a terminal but have you tried grouping by p.pk_ProductID instead
of i.fk...? It is the actual value you are selecting as well as being on
the primary table in the query.

On Thu, Oct 22, 2015, 5:18 PM Don Wieland  wrote:

> Hi gang,
>
> I have a query:
>
> SELECT
> p.pk_ProductID,
> p.Description,
> i.Quantity
>
> FROM invoice_invoicelines_Product p
> JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND
> i.fk_InvoiceID IN (1,2,3)
>
> WHERE p.pk_ProductID IN (1,2,3);
>
> It produces a list like the following:
>
> 1,Banana,3
> 2,Orange,1
> 2,Orange,4
> 3,Melon,3
> 3,Melon,3
>
> I want to SUM the i.Quantity per ProductID, but I am unable to get the
> scope/syntax correct. I was expecting the following would work:
>
> SELECT
> p.pk_ProductID,
> p.Description,
> SUM(i.Quantity)
>
> FROM invoice_invoicelines_Product p
> JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND
> i.fk_InvoiceID IN (1,2,3)
>
> WHERE p.pk_ProductID IN (1,2,3)
> GROUP BY i.fk_ProductID;
>
> but it is not working.
>
>
> Little help please. Thanks!
>
>
> Don Wieland
> d...@pointmade.net
> http://www.pointmade.net
> https://www.facebook.com/pointmade.band
>
>
>
>
>

Re: Query Summary Help...

[Michael Dykman]

One more guess:

Try explicitly aliasing the fields of interest and using those aliases
exclusively throughout the rest of the expression.

SELECT
p.pk_ProductID as pid,
p.Description as dsc,
SUM(i.Quantity) as totl

FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON pid = i.fk_ProductID

WHERE pid IN (1,2,3)
AND i.fk_InvoiceID IN (1,2,3)
GROUP BY pid;

Note that I moved the invoiceID clause out of the join condition into the
where filter. The ON clause should only contain expressions of relational
interest.

On Thu, Oct 22, 2015, 6:00 PM Don Wieland <d...@pointmade.net> wrote:

>
> > On Oct 22, 2015, at 2:41 PM, Michael Dykman <mdyk...@gmail.com> wrote:
> >
> > I'm not at a terminal but have you tried grouping by p.pk_ProductID
> instead
> > of i.fk...? It is the actual value you are selecting as well as being on
> > the primary table in the query.
>
> Yeah I tried that - actually the SUM I need is on the JOIN relationship -
> results should be:
>
> 1,Banana,3
> 2,Orange,5
> 3,Melon,6
>
> Thanks!
>
> Don Wieland
> d...@pointmade.net
> http://www.pointmade.net
> https://www.facebook.com/pointmade.band
>
>
>
>
>

Re: Very strange issue while trying to login remotely

[Michael Dykman]

it will not prompt for a password with the '-p'

try this:
mysql --host 5.6.7.8 -uroot db_name -p

I assume you do have a password set?

On Mon, May 25, 2015 at 11:24 AM, Ajay Garg ajaygargn...@gmail.com wrote:

 Hi All.

 I have two servers, 1.2.3.4 and 5.6.7.8
 I wish to login remotely FROM 1.2.3.4 onto 5.6.7.8.


 a)
 On 5.6.7.8,

 mysql show grants for 'root'@'%';
 +-+
 | Grants for root@%   |
 +-+
 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
 +-+
 1 row in set (0.00 sec)




 b)
 From 1.2.3.4, I do

 mysql --host=5.6.7.8 -uroot db_name
 ERROR 1045 (28000): Access denied for user 'root'@'1.2.3.4' (using
 password: NO)


 I am at complete loss, I have done all that I could find from google.
 I will be really, really grateful for ideas that could help me recover from
 this mess, that is aching me since last 3 hours :(


 Thanks and Regards,
 Ajay


 --
 Regards,
 Ajay




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

 May the Source be with you.

Re: time stamp specific to columns

[Michael Dykman]

A trigger is far simpler than remodelling your data and adding extra
queries.  They are nothing to be afraid of.

On Thu, Apr 9, 2015 at 10:46 AM, h...@tbbs.net wrote:

 On 2015/04/08 11:42, Andrew Wallace wrote:

 I think you'd have to do that with a trigger.


 Yes, one can do that with a trigger, but it is a real pain. MySQL now
 allows
 (new.a,new.b,new.c,new.d)  (old.a,old.b,old.c,old.d)
 but one needs to beware of NULL. Maybe it is better to split off the
 timestampy part to another table, and join them when needed.


  On 4/8/15 6:36 AM, Martin Mueller wrote:

 I understand how a timestamp column automatically changes when there is a
 change in a data row. Is it possible to limit the update to changes in
 particular columns? I have a table where I care about changes in any of
 four different columns, but I don¹t care about changes in other
 columns or
 added columns.

 Is there a command that says ³update the time stamp if and only if there
 is a change in columns a, b,c, or d


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




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

 May the Source be with you.

Re: ssh basics

[Michael Dykman]

There are several weird hybrid ways of installing MySQL on Mac (the native
install as provided by Apple, tar or dmg from Oracle, Darwin Ports, brew
and Fink, if the latter is still around) all of which are utterly mutually
incompatible.  But once it is up, it's behaviour is the same as a typical
unix install, once you have guessed where the files went.

On Sun, Mar 22, 2015 at 6:54 PM, Thufir hawat.thu...@gmail.com wrote:

 Lucio Chiappetti lucio at lambrate.inaf.it writes:
 ..
  Conversely, I have some private databases on my machine B. While I
  maintain them locally with full access, I grant select access to an user
  on another machine C. In this case he runs the mysql client on C which
  connects to the mysqld on my machine B (actually he runs shell scripts
  which do it).
 
  I guess all this very simple arrangements should cover the needs of the
 OP
  as they do for me..
 ..

 Unless you have to switch to Mac, which has a strange hybrid way of
 installing MySQL...

 It's clear that ssh is the better solution, even if a bit more work to
 setup, sometimes.



 -Thufir


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




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

 May the Source be with you.

Re: Help with REGEXP

[Michael Dykman]

Trying to pattern match ip addresses is a famous anti-pattern; it's one of
those things like you feel like it should work, but it won't.

Your case, however, is pretty specific. taking advantage of the limited
range (I will assume you only wanted 4 sections of IPv4)

this should come close:

10[.]\d{1,3}[.](224|225|226|227|228|229|23\d))[.]\d{1.3}

On Thu, Mar 19, 2015 at 9:39 AM, Paul Halliday paul.halli...@gmail.com
wrote:

 I am trying to pick out a range of IP addresses using REGEXP but
 failing miserably :)

 The pattern I want to match is:

 10.%.224-239.%.%

 The regex I have looks like this:

 AND INET_NTOA(src_ip) REGEXP
 '\d{1,3}\\.\d{1,3}\.(22[4-9]|23[0-9])\\.\d{1,3}'

 but, go fish. Thoughts?


 Thanks!

 --
 Paul Halliday
 http://www.pintumbler.org/

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




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

 May the Source be with you.

Re: update and control flow

[Michael Dykman]

​You can use your login inline with nested IF expressions:

insert into foo(id,comment)
 values(17, IF(WORD like 'a%','a',IF(word like 'b%','b',null)));​



On Tue, Dec 9, 2014 at 9:50 AM, wagnerbianchi.com m...@wagnerbianchi.com
wrote:

 You can do that, but, perhaps the only chance to have it updating a row
 based on a condition is developing a Stored Procedure or even having a
 BEFORE Trigger associated with the main table. Those ways, you can test the
 sent value and decide on what UPDATE you will execute afterwards. Consider
 that this is just an opinion since I'm not part of the problem and cannot
 analyse all the requirements.

 WB

 2014-12-09 12:25 GMT-02:00 Martin Mueller martinmuel...@northwestern.edu
 :

  I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it
 would
  be very useful for update operations, but I can't get it right.
 
  If I read the documentation correctly, it should be possible to say
  something like
 
  UPDATE X
 
  if WORD like 'a%' SET COMMENT = 'a'
  elseif WORD like 'b%' SET COMMENT = 'b'
  END IF
 
 
  But this gives me an error message. What am I doing wrong?
 
  MM
 
  Martin Mueller
 
  Professor emeritus of English and Classics
  Northwestern University
 
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 




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

 May the Source be with you.

Re: forum vs email [was: Re: table-for-column]

[Michael Dykman]

I have been a resident of this list for a very long time. In the early
days, this was the only place to get reliable information about what was
then a relatively obscure database system. Now, local and online bookstores
have shelves full of books, many of them authored by list regulars. We have
expert forums which have become more more mature and tens of thousands of
example projects readily accessible on github and Google code. And,  lest
we forget, as the product and the documentation matured,  fewer desperate
situations arose. The list of not such a critical last resort as it once
was.

It's true that the list lost a lot of steam after the Oracle acquisition
and Monty's rants had a polarizing effect. Since then, it has been low
traffic with few threads of much interest.

In spite of the rapid rise of NoSql, managed instances of MySQL on a cloud
have become a major commodity. The relational model is not dead and
reliable implementations will always be in demand.

On Sat, 6 Dec 2014 15:53 Jigal van Hemert ji...@xs4all.nl wrote:

 Hi,
 On 05/12/2014 20:54, Jan Steinman wrote:
  From: Johan De Meersman vegiv...@tuxera.be
 
  I've long wanted to - but never quite got around to - write a forum
 that integrated a mailing list. Bar mail clients that don't handle list
 threads well, it really doesn't seem such a difficult task.
  There actually seem to be a lot of these around. I'm on several that
 send me email when there are new forum postings.

 On typo3.org there used to be mailing lists only in a distant past.
 Later on newsgroups were set up which communicate with the mailing lists
 (newsgroups are the central source of messages).
 Rather recently a forum was built on top of the newsgroup data (FUD
 forum was used). Users on all three message sources can easily
 communicate with eachother.
 Only some mail clients have difficulty keeping the threading headers in
 tact, but other than that there are no real issues.

 --
 Met vriendelijke groet,

 Jigal van Hemert.


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

Re: MySQL dying?

[Michael Dykman]

Please gentlemen,

It is a valid question if a somewhat hackneyed one.

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

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

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

 On 11/24/2014 10:00 AM, Johan De Meersman wrote:
 
  - Original Message -
  From: Ruben Safir ru...@mrbrklyn.com
  Subject: Re: MySQL dying?
 
  Well, this mailing list is dead.  This is a mailing list that used to
  handle 70+ questions a day, or more.
 
 
  Is that why you feel the need to troll on posts from two years ago?
 
  If you think it's dead, unsubscribe and go install MSSQL. If not, either
 ask a question or stop wasting bandwidth.
 
 
  Bye now.
 


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

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

 You have a bad attitude man, and it sucks.

 Now, back to business..

 Ruben

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




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

 May the Source be with you.

Re: Stored Procedure help

[Michael Dykman]

why do you need the 'order by' in your update at all?  The statement, if
innodb, will certainly be atomic; the order in which they are updated means
nothing.
 On Jul 13, 2014 11:46 PM, kitlenv kitl...@gmail.com wrote:

 maybe try 'order by sort_id desc'?


 On Mon, Jul 14, 2014 at 12:42 PM, Don Wieland d...@pointmade.net wrote:

  I am trying to create this stored procedure, but can't understand why my
  editor is chocking on it. Little help please:
 
  DELIMITER //
  CREATE PROCEDURE `reset_sortid` (IN category INT(11))
  BEGIN
  DECLARE a INT;
  SET a = 0;
  UPDATE
  documents SET sort_id = (a := a + 1)
  WHERE
  document_category = category
  ORDER BY
  sort_id;
  END
  //
 
 
  Don Wieland
  d...@pointmade.net
  http://www.pointmade.net
  https://www.facebook.com/pointmade.band
 
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

Re: error 29, file not found (errcode: 13)

[Michael Dykman]

Often, one uses /tmp or set up an appropriately premoissioned folder under /var

On Mon, Jun 23, 2014 at 10:52 AM, thufir hawat.thu...@gmail.com wrote:
 Apparently this error is because MySQL can't read my home directory?  Fair
 enough, but I don't quite follow.  Where would be a good location for the
 CSV file, then?

thufir@dur:~$
thufir@dur:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights
reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql
mysql LOAD DATA  INFILE '/home/thufir/make_year_model.csv' INTO
TABLE vehicles.vehicles FIELDS TERMINATED BY ',' LINES TERMINATED BY
'\n';
ERROR 29 (HY000): File '/home/thufir/make_year_model.csv' not found
(Errcode: 13)
mysql
mysql quit
Bye
thufir@dur:~$
thufir@dur:~$ cat /home/thufir/make_year_model.csv
make1,model1,2012,604,buy now
make2,model2,2013,780,need to sell
make3,model3,2001,780,cheap
thufir@dur:~$



 thanks,

 Thufir




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

 May the Source be with you.

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

Re: SHOW FULL COLUMNS QUERIES hogging my CPU

[Michael Dykman]

The advice to 'avoid LIKE in general' is a little strong.  LIKE is
very useful and does not always cause inefficient queries, although
the possibility is there.

However, there is one form which must be avoided at all costs: the one
where the glob-text matcher is the first character in that string.
LIKE '%'  or LIKE '%foobar'  are both equally costly as, no matter
what index there might be on that column, the query will have to visit
every single row to test the match, therefore inducing a full table
scan.  putting it early in the expression is equally dangerous, but
how dangerous depends on how much data you have:  LIKE 'a%' avoids
visiting every row but it still has to test against a significant
subset of all rows:  If you have 100 million rows, this will still
cause your query to visit a very large number of them.

So, I would have to ask: how many records are in that table? How many
columns?  is it a table or a view?

On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com wrote:
 On 6/2/2014 7:18 PM, Reindl Harald wrote:


 Am 02.06.2014 15:35, schrieb Jatin Davey:

 I am no expert with mysql and databases. Hence seeking out some help on
 this forum.

 Basically i got a query dump of my application during its operation. I
 had collected the queries for about 4 hours.
 Ran some scripts on the number of queries being sent to the databases.

 The query file was a whopping 4 GB is size. Upon analyzing the queries i
 found that there were a total of 30
 million queries made to the Database out of which 10 million queries were
 only doing SHOW FULL COLUMN queries.

 The SHOW FULL COLUMN queries were of the format as below:

 SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM
 `db_private_admin` LIKE '%'

 This is causing considerable cpu usage in %user_time in my system

 fix your application - there is no single reason to run such
 queries 10 million times because the result won't change all
 the time

 and avoid like in general


 Our application does not send such queries to the DB. I have searched
 through my entire code and we dont run such queries. It has something to do
 with a layer below our application. But i am not sure as to where it is.

 Thanks
 Jatin


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




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

 May the Source be with you.

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

Re: multilple mysql engines, one set of shared table spaces?

[Michael Dykman]

As far as I know, the only way this is possible is if your entire
database is formatted as MyISAM.  In that case, multiple MySQL
processes, each started with external-locking enabled, may safely
share a data folder.  The contention will almost certainly kill you as
far as performance goes..   and if you are thinking of trying this
using NFS mounted files or Samba, all bets are off as file locks
can/will/do NOT work leading to inevitable bad race conditions..

You might explore this for some details.

http://dev.mysql.com/doc/refman/5.0/en/external-locking.html

In short, it's a bad, bad idea.


On Wed, May 14, 2014 at 2:11 PM, Bruce Ferrell bferr...@baywinds.org wrote:
 OK, put away the flamethrowers, I KNOW it's dumb.

 I've been asked for the upteenth time is this possible and if so under what
 conditions?

 So I pose the question to the community, is it? Under what conditions?  Is
 it reliable or not?

 Are there authoritative references to support the answers?

 Inquiring minds want to know

 Thanks in advance

 Bruce Ferrell


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




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

 May the Source be with you.

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

Re: Decode Json in MySQL query

[Michael Dykman]

Short answer, no.  There is nothing in MySQL to facilitate this. In
general, storing structured data as a blob (JSON, CSV, XML-fragment,
etc..) is an anti-pattern in a relational environment.  There are
NoSQL solutions that provide the facility: Mongo comes to mind; there
are some others, I am sure.



On Thu, Mar 20, 2014 at 2:59 PM, Karr Abgarian a...@apple.com wrote:
 Hi, you probably want to perform this conversion on your client.   There are 
 JSON parser libraries available for Java, PHP and the like.   Cheers, Karr

 On Mar 20, 2014, at 11:35 AM, Sukhjinder K. Narula narula...@gmail.com 
 wrote:

 Hello,
 I would like to know if there is a way to decode the json string stored in
 one of the fields as text without using triggers or stored procedures.
 What I want to do is is within the query, I would like to get one row per
 element within the json string.
 For example: the json string is as follow:

 [
  {
name : Abc,
age : 20
  },
  {
name : Xyz,
age : 18
  }
 ]

 and after query, I want result as:
 NameAge
 Abc   20
 Xyz   18


 Would this be possible, I greatly appreciate any help regarding this
 matter.

 Many Thanks,
 Sukhjinder


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




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

 May the Source be with you.

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

Re: Precedence in WHERE clauses.

[Michael Dykman]

My advice to you is to make use of the EXPLAIN facility which
porovides the most accurate information about how MySQL is going to
treat your query.

Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row.  I would suggest you temporarily memoize that like so:

select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday  ;

SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate  @yesterday);

On Tue, Mar 18, 2014 at 3:10 PM, Christophe t...@stuxnet.org wrote:
 Hi list,

 I'd like to get your advice about precedence in where clauses in MySQL
 (5.0.51, and 5.1.66 in this case / from lenny and squeeze Debian
 packages ).

 Considering the following simple query :

 SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
 ('PENDING', 'ACCEPTED') AND SubscribeDate  DATE_SUB(NOW(), INTERVAL 24
 HOUR);

 Which of these filters are processed first ?

 I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
 being processed first, but I can't really find any useful information
 about this .

 Is there any performance impact on query processing, about the order of
 WHERE clauses ?

 Regards,
 Christophe.


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




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

 May the Source be with you.

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

Re: regexp in conditional

[Michael Dykman]

I think you just have a misplaced parenthesis.  try:
 SELECT IF(r REGEXP '^ED[HMUZ][0-9]$', 'yes', 'no') FROM s;



On Tue, Jan 7, 2014 at 2:22 PM, Larry Martell larry.mart...@gmail.comwrote:

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

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

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

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




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

 May the Source be with you.

Re: How do I mysqldump different database tables to the same .sql file?

[Michael Dykman]

There is a good reason that the USE database is not output in those dumps..
 it would make the tool very difficult to use for moving data around.

If I might suggest, a simple workaround is to create a shell script along
these lines..  you might to do something a little more sophisticated.

#
#!/bin/sh

echo  USE `database1`;  outflfile.sql
mysqldump -(firstsetofoptions)  outfile.sql
echo  USE `database2`;  outflfile.sql
mysqldump -(secondsetofoptions)  outfile.sql




On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent dae...@daevid.com wrote:

 I'm working on some code where I am trying to merge two customer accounts
 (we get people signing up under different usernames, emails, or just create
 a new account sometimes). I want to test it, and so I need a way to restore
 the data in the particular tables. Taking a dump of all the DBs and tables
 is not feasible as it's massive, and importing (with indexes) takes HOURS.
 I
 just want only the tables that are relevant. I can find all the tables that
 have `customer_id` in them with this magic incantation:

 SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS`
 WHERE
 `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`

 Then I crafted this, but it pukes on the db name portion. :-(

 mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
 --complete-insert --insert-ignore --create-options --quick --force
 --set-charset --disable-keys --quote-names --comments --verbose --tables
 member_sessions.users_last_login support.tickets mydb1.clear_passwords
 mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
 mydb1.customers_free_tracking mydb1.customers_log
 mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
 mydb1content.actors_comments mydb1content.actor_collections
 mydb1content.actor_likes_users mydb1content.collections
 mydb1content.dvd_likes_users mydb1content.free_videos
 mydb1content.genre_collections mydb1content.playlists
 mydb1content.poll_votes mydb1content.scenes_comments
 mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
 mydb1content.scene_likes_users mydb1content.videos_downloaded
 mydb1content.videos_viewed  merge_backup.sql

 -- Connecting to localhost...
 mysqldump: Got error: 1049: Unknown database
 'member_sessions.users_last_login' when selecting the database
 -- Disconnecting from localhost...

 I searched a bit and found that it seems I have to split this into multiple
 statements and append like I'm back in 1980. *sigh*

 mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
 --complete-insert --insert-ignore --create-options --quick --force
 --set-charset --disable-keys --quote-names --comments --verbose --database
 member_sessions --tables users_last_login  merge_backup.sql
 mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
 --complete-insert --insert-ignore --create-options --quick --force
 --set-charset --disable-keys --quote-names --comments --verbose --database
 support --tables tickets  merge_backup.sql
 mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
 --complete-insert --insert-ignore --create-options --quick --force
 --set-charset --disable-keys --quote-names --comments --verbose --database
 mydb1 --tables clear_passwords customers customers_free
 customers_free_tracking customers_log customers_subscriptions
 customers_transactions players  merge_backup.sql
 mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
 --complete-insert --insert-ignore --create-options --quick --force
 --set-charset --disable-keys --quote-names --comments --verbose --database
 content --tables actors_comments actor_collections actor_likes_users
 collections dvd_likes_users free_videos genre_collections playlists
 poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
 scene_likes_users videos_downloaded videos_viewed  merge_backup.sql

 The critical flaw here is that the mysqldump program does NOT put the
 necessary USE DATABASE statement in each of these dumps since there is
 only one DB after the -database apparently. UGH. Nor do I see a command
 line
 option to force it to output this seemingly obvious statement.

 It's a pretty significant shortcoming of mysqldump if you ask me that I
 can't do it the way I had it in the first example since that's pretty much
 standard SQL convetion of db.table.column format. And even more baffling is
 why it wouldn't dump out the USE statement always even if there is only
 one DB. It's a few characters and would save a lot of headaches in case
 someone tried to dump their .sql file into the wrong DB on accident.

 Plus it's not easy to edit a 2.6GB file to manually insert these USE
 lines.

 Is there a way to do this with some command line option I'm not seeing in
 the man page?




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

 May the Source be with you.

Re: how to create unique key for long varchar?

[Michael Dykman]

The odds against the eventuality you are worried about are astronomically
high.  Much serious work on the internet would fall apart were that not
true.  Collision is simply not going to happen within the next several
hundred thousand years.
On Nov 5, 2013 9:59 PM, Li Li fancye...@gmail.com wrote:

 I prefer your solution in that it's something like Optimistic Locking.
 but the problem is that if I define md5 as unique key and there exists
 2 different urls with the same md5. I can't insert the second url
 anymore

 On Tue, Nov 5, 2013 at 11:55 PM, Dan Nelson dnel...@allantgroup.com
 wrote:
  In the last episode (Nov 05), Li Li said:
  I want to create a table with a long varchar column, maybe it's the
 url.
  according to dns spec, the url's max length is fixed. but I have
  to deal with url having long params such as
  a.html?q=fl=
  I want the url is unique when inserting it.
  I googled and found
  http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql
  this post suggests use md5 of url. But in theory, there will be
  conflict that two different urls will have the same md5(even it's
  probablitiy is very small). I want to a absolute correct solution.
  one method i can come up with is using select ... for update
  1. begin transaction
  2. select url from tb where md5='' for update
  3. if the url is not exist, insert into this url; else do nothing
 
  It might be more efficient to optimize for the common case here.  The
  assumption is that an md5 (or sha1 or sha2) hash collision is extremely
  unlikely, so you could just insert your new row, and if you get a
 duplicate
  entry for primary key error, then you can select url from tb where
  md5='' , and compare the retreived url with the one you want to
 insert.
 
  --
  Dan Nelson
  dnel...@allantgroup.com

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

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

[Michael Dykman]

Consider:
update table1 set field1 = if( :var,:var,field1), ...

Can be in a procedure but doesn't have to be.
On Oct 28, 2013 5:28 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:

 Hi Shawn

 Thanks for your reply.  Maybe my example wasn't detailed enough.  Basically
 the snippet of the UPDATE statement I provided shows updating only 1 field.
  However in my live working example, I have about 20 possible fields that
 might need to be updated if the variable passed for each field is NOT
 NULL.

 Therefore, I felt this needs to be done at database level in the stored
 procedure.  How can I accomplish this.

 Thanks
 Neil


 On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green shawn.l.gr...@oracle.com
 wrote:

  Hello Neil,
 
  On 10/28/2013 2:06 PM, Neil Tompkins wrote:
 
  Hi
 
  If I have a update statement like
 
  UPDATE MY_TABLE
  SET FieldName1 = Now(), FieldName2 = :MyVariable
  WHERE FieldName3 = 'Y'
 
  How can I only update the FieldName2 field if the value of MyVariable is
  NOT NULL ?
 
  Thanks
  Neil
 
 
  This needs to be a decision you make at the application level to not
  execute the UPDATE command in the first place. Not every decision needs
 to
  be made by the database. Plus, it will save you the time of a full
 network
  round trip just to get a result from the server that you affected 0 rows
  (parsing, optimizing, executing).
 
  Now, if this was just a typo and your :MyVariable was meant to be
  @MyVariable (a MySQL user variable) then you can put that test in the
 WHERE
  clause of the command
 
  UPDATE MY_TABLE
  SET FieldName1 = Now(), FieldName2 = @MyVariable
  WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL
 
  --
  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
 
 

Re: restore problem

[Michael Dykman]

I would suggest that you test your backup file on another full-featured
server to determine that it is a valid first.  I have done a little work
with the raspberry pi and I doubt that the mysql distribution for that
platform comes with all the features your server-class ubuntu does, so it
is quite possible that you backup file is trying to take advantage of some
facilities available on the source host that are not available on your
lightweight target.



On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote:

 Hello,

 Thanks for your quick reply.

 I use WEBMIN and within the Webmin modules you can make simply a SQL
 backup, which is in fact a flat file consisting of MYSQL commands.

 Explains this enough?

 Thanks, BR



 Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto 
 luisforchesa...@gmail.com het volgende geschreven:

  How did you backed up the MySQL?
 
  Att.
  Luis H. Forchesatto
 
  Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu:
  Hello all,
 
  As this is my first post to this list (though reading for some time
 yet), I maybe not complete in asking my question. Apologies for that.
 
  On the other hand, this is not a prio 1 problem, so if it takes some
 mailing, that's ok.
 
  I have a website on my private webserver, which consist of some kind of
 a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and
 the same kind of LAMP-server.
 
  I'm trying to restore the SQL-backup from the old machine to restore
 with the raspberry. While finding the file and starting the restore is no
 problem, I get the following message and a full stop:
 
  SELECT MAX( version )
  FROM `phpmyadmin`.`pma_tracking`
  WHERE `db_name` = 'bbz'
  AND `table_name` = 'wp_links VALUES'
  AND FIND_IN_SET( 'INSERT', tracking ) 0
 
  MySQL retourneerde: b_help.png
 
  #1100 - Table 'pma_tracking' was not locked with LOCK TABLES
 
  Since I do a full restore I'm processing the younameit.SQL file
 
  I'm bad in interpreting the error messag, don't understand what it
 exactly mens and what I can do to cure this problem.
 
  Anyone a suggestion?
 
  Thanks in advance, BR




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

 May the Source be with you.

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

[Michael Dykman]

Nick,

You should have answered your own question in the text.

The MySql TIMESTAMP type is, as all other timestamps in the *nix
world, a count of seconds since epoch time.  The Java function you are
using yields MILLI-seconds.  Divide it by 1000 and you should be good
to go.


On Wed, Aug 21, 2013 at 6:03 PM, Nick Khamis sym...@gmail.com wrote:
 Hello Everyone,

 We have the following mysql timetampe field

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

 When trying to insert a long value in there:

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

 We are presented with the following error:

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


 Our environments is:

 JDBC Driver = 5.1.26
 Mysql = 5.5


 show variables like 'time_zone%';
 +---++
 | Variable_name | Value  |
 +---++
 | time_zone | +00:00 |
 +---++

 SELECT @@global.sql_mode;
 +---+
 | @@global.sql_mode |
 +---+
 |   |
 +---+

 Not sure why I am getting this error.


 Thanks in Advance,

 Nick.



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

 May the Source be with you.

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

Re: best way to copy a innodb table

[Michael Dykman]

Another technique to avoid impact to the source database is to create your
target as MyISAM, pump your records into that (no ACID overhead) and at the
end :

ALTER mytable engine=InnoDb

The alter can take awhile but it will impose no strain on the source server
at all.


On Tue, Jul 2, 2013 at 3:48 AM, Arjun na...@yahoo.com wrote:

 Well, the easy way to chunk the inserts is by use of limit. Here is what I
 used for one of my projects:

 Insert ignore into t1 (f1, f2, f3)
 Select f1, f2, f3 from t2 limit 100, 100

 Inserts 1M records at a time starting from 1M th record in t2 and you can
 keep
 incrementing this offset as you progress. This will help in monitoring the
 table inserts and at the same time move chunks of records from source
 table.

 Enjoy!


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




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

 May the Source be with you.

Re: string-likeness

[Michael Dykman]

I will second Rick's approach and have implemented something very
similar for a client when soundex feel short of expectation.  It
worked very well.

On Mon, Jun 3, 2013 at 5:43 PM, Rick James rja...@yahoo-inc.com wrote:
 Soundex is the 'right' approach, but it needs improvement.  So, find an 
 improvement, then do something like this...
 Store the Soundex value in a column of its own, INDEX that column, and JOIN 
 on that column using =.  Thus, ...
 * You have spent the effort to convert to Soundex once, not on every call.
 * Multiple strings will have the same Soundex, but generally not many will 
 have the same.  Hence, the JOIN won't be 1:1, but rather some small number.

 Other approaches (eg, Levenshtein) need both strings in the computation.  It 
 _may_ be possible to work around that by the following.
 Let's say you wanted to a match if
 * one letter was dropped or added or changed, or
 * one pair of adjacent letters was swapped.
 Then...  For a N-letter word, store N+1 rows:
 * The word, as is,
 * The N words, each shortened by one letter.
 Then an equal match on that hacked column will catch single 
 dropped/added/changed letter with only N+1 matches.
 (Minor note:  doubled letters make the count less than N+1.)

 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Monday, June 03, 2013 8:30 AM
 To: mysql@lists.mysql.com
 Subject: string-likeness

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


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


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




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

 May the Source be with you.

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

Re: SSH tunnels and non root accounts get the server service or the configuration file could not be found

[Michael Dykman]

Your windows-based client never sees the files on the linux server.
All the tunnel provides is the ability to treat port 3306 of your
linux box as-if it was a port local to your windows system.  It does
not, can not make file access transparent across those systems.

On Tue, May 28, 2013 at 8:05 PM, Miguel González
miguel_3_gonza...@yahoo.es wrote:
 Dear all,

   Not sure if this the right mailing list address for asking this.

   Server running Centos and MySQL.

Client is a windows xp machine.

I have setup a SSH tunnel with putty and run mysql administrator. It
 works fine with the root account.

With a non-root account I get

the server service or the configuration file could not be found. I can
 log on but I can't see the databases that I should be allowed to see.

Running a mysql -h 127.0.0.1 -u myuser -p mypassword from linux works
 fine

 I have created a .my.cnf file in the home folder with 600 permissions in
 the linux box and filled it with:

 [client]

 pass='mypass'
 user=myuser

 Server configuration file is under /etc/my.cnf.

 What am I doing wrong?

 Regards,

 Miguel

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




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

 May the Source be with you.

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

Re: Bug in BETWEEN same DATETIME

[Michael Dykman]

where cast(transaction_date as date) BETWEEN '2013-04-16' AND

This approach might be problematic in that it requires that every row in
the source table be examined so that it's transaction_date can be casted.
 The original formulation is more efficient as it allows an index on
transaction_date to be used, if one exists.
WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND
'2013-04-16 23:59:59'

Although you probably get the result you want by just incrementing the day
on the upper-limit.
WHERE `transaction_date` BETWEEN '2013-04-16 AND '2013-04-17'

 - michael dykman


On Thu, May 23, 2013 at 5:07 PM, Peterson, Timothy R 
timothy_r_peter...@uhc.com wrote:

 You probably want
 where cast(transaction_date as date) BETWEEN '2013-04-16' AND
 '2013-04-16'
 That works on my test case

 You could also change the where clause to be = date and  date+1



 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Thursday, May 23, 2013 3:56 PM
 To: mysql@lists.mysql.com
 Subject: Bug in BETWEEN same DATETIME

 I just noticed what I consider to be a bug; and related, has this been
 fixed
 in later versions of MySQL?

 We are using:
 mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
 5.2

 If you use BETWEEN and the same date for both parts (i.e. you want a
 single
 day) it appears that the operator isn't smart enough to consider the
 full
 day in the cases where the column is a DATETIME

 http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operato
 r_be
 tween

 WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'

 I actually have to format it like this to get results

 WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
 11:59:59'

 As it appears that in the first instance it defaults the time to
 00:00:00
 always, as verified by this:

 WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'

 So,  I think it's probably safe to assume that if someone is using the
 BETWEEN on datetime columns, their intent more often than not is to get
 the
 full 24 hour period, not the 0 seconds it currently pulls by default.

 I also tried these hacks as per the web page above, but this doesn't
 yield
 results either

 WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
 CAST('2013-04-16' AS DATE)
 WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
 CAST('2013-04-16' AS DATETIME)

 This one works, but I fail to see how it's any more beneficial than
 using a
 string without the CAST() overhead?

 WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
 AND
 CAST('2013-04-16 11:59:59' AS DATETIME)

 Or is there some other magical incantation that is supposed to be used
 (without me manually appending the time portion)?

 This e-mail, including attachments, may include confidential and/or
 proprietary information, and may be used only by the person or entity
 to which it is addressed. If the reader of this e-mail is not the intended
 recipient or his or her authorized agent, the reader is hereby notified
 that any dissemination, distribution or copying of this e-mail is
 prohibited. If you have received this e-mail in error, please notify the
 sender by replying to this message and delete this e-mail immediately.


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




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

 May the Source be with you.

Re: auto-increment more than one field

[Michael Dykman]

refer to http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html





On Sun, May 12, 2013 at 9:39 AM, rounak jain rounak.m...@gmail.com wrote:

 I have a table which needs two fields with auto-increment.
 I have the found the answer here:

 http://stackoverflow.com/questions/13642915/mysql-table-with-more-than-one-auto-incremented-
 coloumn?rq=1

 I am  using MySqlWorkbench. I can see Triggers section below the table. I
 don't know how to use the trigger script suggested in the link. Thanks.




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

 May the Source be with you.

Re: Triggers

[Michael Dykman]

Your question is far too vague to answer.

What are your requirements?  What considerations are you worried about?


On Fri, May 10, 2013 at 2:55 PM, Aastha aast...@gmail.com wrote:

 If triggers use complex business rules and large transaction.
 What would we be recommendations?
 I need three possible ways.

 Thanks,




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

 May the Source be with you.

Re: Chain Replication QUestion

[Michael Dykman]

That is correct.


On Mon, May 6, 2013 at 11:06 AM, Richard Reina gatorre...@gmail.com wrote:

 To activate log-slave-updates do I just add log-slave-updates to the
 my.cnf file?



 2013/4/30, Manuel Arostegui man...@tuenti.com:
  2013/4/30 Richard Reina gatorre...@gmail.com
 
  I have a few slaves set up on my local network that get updates from
  my main mysql database master. I was hoping to turn one into a master
  while keeping it a slave so that I can set up a chain.  Does anyone
  know where I can find a how to or other documentation for this
  specific task?
 
 
  It is quite easy:
 
  Enable log-slave-updates in the slave you want to be a master.
  Do a mysqldump -e --master-data=2 and put that mysqldump in the future
  slaves. Take a look at the first lines of the mysqldump where you'll find
  the position and logfile those slaves need to start the replication from.
  You can also use xtrabackup if you like.
 
  Manuel.
 

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




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

 May the Source be with you.

Re: MySQL on Mac OS 10.8

[Michael Dykman]

Larry,

Nothing in the mysql startup files ever removes any directories of any kind.

At a guess: only my clients who work on Macs ever report this king of
'disappearing folder' behaviour.  And every time it turn out to be Time
Machine.  Ask around on those lists.


On Tue, Apr 16, 2013 at 12:34 PM, Larry Martell larry.mart...@gmail.comwrote:

 I just set up mysql on Mac OS 10.8. Each time after the machine is
 rebooted the server fails to start with:

 2013-04-13 14:09:54 1 [ERROR] /usr/local/mysql/bin/mysqld: Can't
 create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No
 such file or directory)

 Because the /var/run/mysqld dir does not exist. I have to create it
 manually then the server starts. But I have to do this after each
 reboot, which is a pain. Anyone know why the /var/run/mysqld dir gets
 deleted, or how to get around this?

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




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

 May the Source be with you.

Re: replication problem

[Michael Dykman]

Trimurthy,

you will have to describe the method you are using to setup
replication.  The error message seems plain but an observer could not
reasonably guess what caused it without more information.

 - michael dykman


On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote:
 hi list,   i am trying to set up replication but i couldn't
 complete because of the following error
 Could not find first log file name in binary log index file can any one
 please help me.







   Normal
   0




   false
   false
   false

   EN-US
   X-NONE
   AR-SA







































































































































































 Thanks  Kind Regards,

  TRIMURTHY



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

 May the Source be with you.

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

Re: Failed to setup SSL

[Michael Dykman]

I had noticed that the paths to your certificates were expressed  as
relative paths. I think at least part of Reindl's recommendation was to
express fully qualified paths to your certs and to examined the permissions
on those files carefully. SSL is very particular about rejecting  security
files which have too-permisive permissions..

also, considered tailing /var/log/secure

 On 2012-11-24 8:05 PM, Jackie Zhang jackie.qq.zh...@gmail.com wrote:

Dear Reindl,

Thanks a lot for the reply!

I tried your scripts (the only difference is the openssl.cnf because I
don't have it)

Unfortunately, I still failed to start the server with the same message:

121124 17:00:06 [Warning] Failed to setup SSL
121124 17:00:06 [Warning] SSL error: Failed to set ciphers to use

Do you have any idea from the log message?

Best regards,
Jackie


On Sat, Nov 24, 2012 at 4:02 PM, Reindl Harald h.rei...@thelounge.net
wrote:




 Am 25.11.2012 00:30, schrieb Jackie Zhang:
  Hello everyone,
 
  I want to setup SSL fo...

Fwd: Basic SELECT help

[Michael Dykman]

response did not go to the list..


I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;

in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'table' where type=2


As INTERSECT is not avilable under mysql, we will have to go the JOIN route

select distinct a.id from mytable a
inner join mytable b on (a.id=b.id)
where a.type= 2 and b.type = 5;

 - michael dykman

On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:
 Hi,

 I'm struggling with what I think is a basic select but can't think how to
 do it : My data is

 id,type

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

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

 Any ideas ?

 Neil



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

 May the Source be with you.


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

 May the Source be with you.

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

Re: Basic SELECT help

[Michael Dykman]

Keep joining I think. In the absence of intersect (which incurs the cost of
a query per type anyhow ), this join pattern is the only option I can think
of.

On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
wrote:

Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 
  response did not go to the list..
 
 
  I assume that you mean the id must be associated with bo...

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

Re: Basic SELECT help

[Michael Dykman]

Of course there is a cost for the join, each link being a distinct
lookup query but that is the same cost the INTERSECT would impose.

It is not a bad as multiple joins generally might be as all the
lookups are against the same key in the same table which should keep
that index in ram. (type is indexed, yes?)

As you no doubt have noticed, the problem with these solutions:

   SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

is that they returns ids that have ANY of those values which is not
what you are asking for,

If your dataset is HUGE, there might be a performance problem which
might force you to reformulate as:

create temporary table `mytemp` select id, type from `mytable` WHERE
type IN(x,y,z);

select distinct a.id from `mytemp` a
  inner join `mytemp` b on (a.id=b.id)
  where a.type= 2 and b.type = 5;
  -- repeat inner join as needed

drop table mytemp;


On Thu, Nov 22, 2012 at 10:09 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:

 Do you know if I had multiple joins there would be a performance issue ?


 On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman mdyk...@gmail.com wrote:

 Keep joining I think. In the absence of intersect (which incurs the cost
 of a query per type anyhow ), this join pattern is the only option I can
 think of.

 On 2012-11-22 10:01 AM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Michael,

 Thanks this kind of works if I'm checking two types.  But what about if I
 have 5 types ?

 On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman mdyk...@gmail.com wrote:

 
  response did not go to the list..
 
 
  I assume that you mean the id must be associated with bo...

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






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

 May the Source be with you.

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

Re: Basic SELECT help

[Michael Dykman]

Assuming that (id,type) is unique in the source data, that is a pretty
elegant method:

 select id from
 (select distinct id, count(*)
 from my_table
 where type in (2,5)
 group by id
 having count(*) = 2)a;


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

 May the Source be with you.

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

Re: Basic SELECT help

[Michael Dykman]

On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins
neil.tompk...@googlemail.com wrote:

 By unique you mean that no id and type would be duplicated like

 1,1
 1,1

 Yes it isn't possible for duplicate id and type in more than 1 row
Yes, that's exactly what I meant.


 - mdyk...@gmail.com

 May the Source be with you.

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

Re: Basic SELECT help

[Michael Dykman]

Mogens,

Platform could not be less relevant to a question of MySql syntax.
The techniques we have been discussing have been available to every
version of MySql post v3.23 and the class/job function he is applying
it to is neither relevant to the problem nor any of our business,
unless he volunteers to share it. Excepting only the working
assumption that he is using a MySql version released in this century,
I don't know how this would have informed my analysis or response.

 - michael dykman

On Thu, Nov 22, 2012 at 4:00 PM, Mogens Melander mog...@fumlersoft.dk wrote:

 On Thu, November 22, 2012 15:45, Neil Tompkins wrote:
 Basically I only what to return the IDs that have both types.


 And that's exactly what below statement will return.

 You forgot to include what platform you are on,
 which version of MySQL you are running and
 what class you are attending.

 All necessary information to provide a sufficient help.


 On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
 marek.gutow...@gmail.comwrote:

 SELECT DISTINCT id FROM table WHERE type IN ('2','5')

 should work


 On 22 November 2012 14:30, Neil Tompkins
 neil.tompk...@googlemail.comwrote:

 Hi,

 I'm struggling with what I think is a basic select but can't think how
 to
 do it : My data is

 id,type

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

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

 Any ideas ?

 Neil




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




 --
 Mogens Melander
 +66 8701 33224

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


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




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

 May the Source be with you.

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

Re: Re: UDF behaves non-deterministic

[Michael Dykman]

C is not an inherently thread-safe language.  Several of the standard
library functions use static data, which gets stepped on during concurrent
operation.  Many of those do have thread-safe equivalents on many platforms
such as strtok/strtok_r (the latter being the safe one).

If you are confident you are not using statics or globals in your code
directly, you will need to identify each function you do call.  Start by
reading the man page for that function (if it's in the C stdlib, there is a
man page for it) which should tell you if it is safe or not; for those
which are not, the man page will likely suggest a threadsafe alternative if
one is available.  If none are available, you might have to consider a
mutex.

 - michael dykman


On Mon, Nov 5, 2012 at 9:28 AM, Stefan Kuhn stef...@web.de wrote:

 Hi Dan,

 thanks for your answer. The UDF only contains functions (the one called in
 sql plus two functions called in it). There are no variables outside them
 and nothing is declared static. All variables inside the functions are
 declared just like double x=0; etc. I am not an expert on C, but my
 understanding is that these values are separate for each call of the
 function and don't influence each other. Do you have a suggestion what I
 should look for in my c code? Or do I need to make the code thread-safe in
 that sense that concurrent executions are prevented by monitors or
 semaphors or so (no idea about what this is called in c)?
 Stefan

 The first thing I would do is examine your UDF and ensure that it is
 thread-safe. No global variables, no static variables within functions,
 etc. Also make sure that any libc functions you call that are documented
 as
 non-threadsafe are wrapped by a mutex or otherwise protected against
 multiple simultaneous access.
 
 http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html
 
 As for debugging, you should be able to write things to stderr which will
 show up in the mysql logfile, or you could open your own logfile and write
 to that.

 --
 Dan Nelson
 dnel...@allantgroup.com




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




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

 May the Source be with you.

Re: UDF behaves non-deterministic

[Michael Dykman]

A couple of questions present.

You mention that selecting from the whole table takes 5-10s so I assume you
have a lot of records.
  is the data not in flux? are you sure?
  these conflict queries are all on the same server?

i would have structured the query like so:
  select *, udf(column,'value') AS u from table order by u;

I suspect it might reduce the number of udf invocations..  the order by
clause is frequently referred to in the process of sorting.. keeping that
static instead of dynamic might sanitize your issue.

On 2012-11-04 4:24 PM, Stefan Kuhn stef...@web.de wrote:

Hi all,
I have a weired (for me at least) problem with a user defined function,
written in C. The function seems to return different results in different
runs (the code of the function does not contain random elements). Basically,
the function calculates a score based on a column in a table and an input
value. So I do something like this:
select * from table order by udf(column, 'input_value') desc;
For my understanding, this should give the same result always. But if I run
many statements (execution is from a java program and I can do it in
parallel
threads) so that they overlap (the udf on a large table takes 5-10 s on a
slow machine), the results of some queries are different. If I have enough
time between statements, it seems to work, i. e. the result is always the
same. I would have thought the statements are independent, even if executed
on different jdbc connections in parallel.
Does somebody have an idea?
Or could somebody give an idea on debugging? Normally I would try to debug
the
code to see what goes on, but how can I do this in a udf? Can I log in the
udf?
Thanks for any hints,
Stefan

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

Re: index

[Michael Dykman]

Innodb stores it's indexes internally in the datafile; they do consume
storage, but that space is shared with the data.

innodb_per_file will, in most cases, enhance performance in a
high-concurrency environment.  It also can simplify administration,
and help limit your liabilities in the event of catastrophic
filesystem error.

 - md

On Tue, Oct 30, 2012 at 3:18 AM, Trimurthy trimur...@tulassi.com wrote:
 hi lists  1. does the indexes require additional storage other than
 the table space storage.  2. is there any performance difference
 will be there, if we go for innodb_file_per_table.






   Normal
   0




   false
   false
   false

   EN-US
   X-NONE
   AR-SA







































































































































































 Thanks  Kind Regards,

  TRIMURTHY




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

 May the Source be with you.

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

Re: Unknown database error

[Michael Dykman]

The contents of datadir is typically owned by the mysql user and group.
Certain attributes on the datadir itself (ls -ld $datadir) can affect the
permissions of newly-created files and directories.  You need to talk to
you sysadmin,  with a eye towards doing something like

chown -R mysql:mysql $datadir

I honestly cannot think of a scenario where the datadir should be
permissioned any other way.

On 2012-10-29 6:26 AM, javad bakhshi javadbakh...@yahoo.com wrote:

Hi,

I get an Unknown database error when I am trying to connect to mysql using
C API.

I have created the database in mysql prompt as root:

CREATE DATABASE Lr0;

I am pretty sure that the problem is a ownership issue, because when I do
ls -l in my datadir I get:

[javad64@udbl64 data]$ ls -l
total 20532
-rw-rw 1 javad64 mysql   10485760 Oct 26 14:10 ibdata1
-rw-rw 1 javad64 mysql5242880 Oct 26 14:10 ib_logfile0
-rw-rw 1 javad64 mysql5242880 Oct 19 15:28 ib_logfile1
drwx-- 2 javad64 javad64 4096 Oct 26 14:35 Lr0
drwx-- 2 javad64 mysql   4096 Oct 22 11:04 mysql
drwx-- 2 javad64 mysql   4096 Oct 22 11:04 test

which shows Lr0 doesn't have mysql ownership.

my question is:
1- Why does this happen? While I am creating the
database in mysql prompt as root.

2- How can I fix it? I don't have root access in the system I am using
so the chown solution is off table.



Best regards,
Javad Bakhshi,
Computer Science M.Sc
Department of IT, Uppsala University

Re: error 13

[Michael Dykman]

The reason this is significant is because we are speaking about INFILE
use.  The server processes infile commands assuming the file is already
stored on the server. LOCAL INFILE tells mysql that file is local to the
mysql client and must be moved to the server before processing.  I suspect
that this has been your issue all along.

On 2012-10-18 1:37 PM, kalin ka...@el.net wrote:


not sure i follow. what do you mean connect?!

there is a server (mysqld) and a client (mysql). the client gets to the
server via the socket. like on any other unix machine.

how did i connect mysql to what exactly?




On 10/18/12 6:42 AM, Ananda Kumar wrote:

 
  how did u connect mysql on your laptop
 
  On Thu, Oct 18, 2012 at 1:19 AM, kalin ka...@el.net

  mailto:ka...@el.net wrote:
 
 
  thanks amanda...  the local worked for some reason...
 
 ...

  mailto:anan...@gmail.com mailto:anan...@gmail.com wrote:
 
   does both d...
  mailto:rja...@yahoo-inc.com


  mailto:rja...@yahoo-inc.com wrote:
 
   SELinux ?
 
 ...

   mailto:mysql@lists.mysql.com
  mailto:mysql@lists.mysql.com
 ...

   mailto:ka...@el.net mailto:ka...@el.net wrote:

   ...
  mailto:pengli...@gmail.com


  mailto:pengli...@gmail.com wrote:
 
  H...

   mailto:ka...@el.net mailto:ka...@el.net wrote:
 
...

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

[Michael Dykman]

your now() statement is getting executed for every row on the select.  try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.

On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote:

Will do.

mysql  SHOW GLOBAL VARIABLES LIKE '%log%';
+-+-+
| Variable_name   | Value
|
+-+-+
| back_log| 50
|
| binlog_cache_size   | 32768
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format   | MIXED
|
| expire_logs_days| 5
|
| general_log | OFF
|
| general_log_file| /var/run/mysqld/mysqld.log
|
| innodb_flush_log_at_trx_commit  | 2
|
| innodb_flush_log_at_trx_commit_session  | 3
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_block_size   | 512
|
| innodb_log_buffer_size  | 8388608
|
| innodb_log_file_size| 2145386496
|
| innodb_log_files_in_group   | 2
|
| innodb_log_group_home_dir   | ./
|
| innodb_mirrored_log_groups  | 1
|
| innodb_overwrite_relay_log_info | OFF
|
| log | OFF
|
| log_bin | ON
|
| log_bin_trust_function_creators | ON
|
| log_bin_trust_routine_creators  | ON
|
| log_error   | /var/log/mysql-error.log
|
| log_output  | FILE
|
| log_queries_not_using_indexes   | ON
|
| log_slave_updates   | OFF
|
| log_slow_admin_statements   | OFF
|
| log_slow_filter |
|
| log_slow_queries| ON
|
| log_slow_rate_limit | 1
|
| log_slow_slave_statements   | OFF
|
| log_slow_sp_statements  | ON
|
| log_slow_timestamp_every| OFF
|
| log_slow_verbosity  | microtime
|
| log_warnings| 1
|
| max_binlog_cache_size   | 18446744073709547520
|
| max_binlog_size | 104857600
|
| max_relay_log_size  | 0
|
| relay_log   | /var/log/mysql/mysqld-relay-bin
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_space_limit   | 0
|
| slow_query_log  | ON
|
| slow_query_log_file | /var/log/mysql/mysql-slow.log
|
| slow_query_log_microseconds_timestamp   | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_log_update  | ON
|
| suppress_log_warning_1592   | OFF
|
| sync_binlog | 0
|
| use_global_log_slow_control | none
|
+-+-+
51 rows in set (0.01 sec)

Here is full output, but writing happens ONLY if
log_queries_not_using_indexes turned ON.

Query takes:
# Query_time: 0.291280  Lock_time: 0.50  Rows_sent: 0  Rows_examined:
133876  Rows_affected: 0  Rows_read: 1
# Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: F229398
SET timestamp=1350389078;
SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
service, account, id, sms_type, mclass, mwi, coding, compress, validity,
deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;



2012/10/16 Shawn Green shawn.l.gr...@oracle.com

 On 10/15/2012 7:15 PM, spameden wrote:

 T...

Re: Odd Behavior During Replication Start-Up

[Michael Dykman]

I have to agree with Harald on this: filesystem snapshots are not an
effective way to clone innodb databases.  The rsync-based method
described has worked for me in large scale data situations very
reliably.

 - michael dykman

On Tue, Oct 16, 2012 at 3:20 PM, Reindl Harald h.rei...@thelounge.net wrote:


 Am 16.10.2012 20:18, schrieb Tim Gustafson:
 InnoDB: The log sequence number in ibdata files does not match
 InnoDB: the log sequence number in the ib_logfiles!
 121016 10:40:20  InnoDB: Database was not shut down normally!

 So, I went back to the master server, backed up the foo database and
 dropped and re-created it, and then restored the data, and repeated
 the whole process, but then I just get the same error for another pair
 of database names.  I did this three times before giving up.  No data
 appears to be corrupted at all on the master server.

 you can not simply copy a single database in this state
 innodb is much more complex like myisam

 * rsync on the master while it runs LOCAL
 * stop the master
 * rsync a second time to get a fast diff-sync
 * stop the salve
 * rsync the master-backup to the slave
 * start replication

 IMHO this is the only fast, safe and consistent way to
 start a replication - and yes FS snapshots are REALLY
 bad for such things

 i am doing the above since many years now

 BTW: you should take care that slave and master have the SAME mysql-version!





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

 May the Source be with you.

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

Re: Odd Behavior During Replication Start-Up

[Michael Dykman]

load data from master never worked for innodb.

On 2012-10-16 3:52 PM, Tim Gustafson t...@soe.ucsc.edu wrote:

Thanks for all the responses; I'll respond to each of them in turn below:


 you can not simply copy a single database in this state
 innodb is much more complex like myisam...
I know; that's why I rsync'd the entire /var/db/mysql folder (which
includes the ib_logfile and ibdata files, as well as all other
database and table data), not just individual databases.  I also made
sure that flush tables with read lock had been executed before
creating the snapshot.  The steps I followed were verbatim what the
MySQL documentation said to do.  The MySQL documentation even mentions
ZFS snapshots as an effective way to make a backup:

http://dev.mysql.com/doc/refman/5.5/en/flush.html


 I have to agree with Harald on this: filesystem snapshots are not an
 effective way to clone inn...
I'm confused: in the first sentence, you say snapshots are bad (which
directly contradicts the official MySQL documentation), and in the
second sentence you say rsync is good.  Why would an rsync of a file
system snapshot not be good enough?  By the way: I forgot to mention
that I also did create a snapshot when the MySQL server on db-01 was
actually shut down, and got the same sort of results.


 You can do replication flawlessly for InnoDB tables without stopping
 master at all.

 what yo...
All total, we have approximately 125GB of MySQL databases.  That
command would take hours to run.  During that time, no new
transactions could be committed to any of our databases, and
performance for read-only queries would be seriously affected.
Further, we have a combination of MyISAM and InnoDB databases and
tables, and the --single-transaction parameter to mysqldump does not
lock MyISAM tables.  There used to be a MySQL command that basically
did all that in one statement (LOAD DATA FROM MASTER), but they
dropped it because of the difficulties in getting all the master data
that way.


-- 

Tim Gustafson
t...@soe.ucsc.edu
831-459-5354
Baskin Engineering, Room 313A

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

Re: error 13

[Michael Dykman]

is your mysql client on the same host as the mysql server? if not, google
the docs for 'local infile'

On 2012-10-16 10:45 PM, Lixun Peng pengli...@gmail.com wrote:

Hi,

What's the dir permissions?

For example, if we have a file in /a/b/file, dir a is 644, even though file
is 777, we can't access file, too.


On Wed, Oct 17, 2012 at 10:31 AM, kalin ka...@el.net wrote:



 hi all..

 this hasn't happe...
--
Senior MySQL Developer @ Taobao.com

Mobile Phone: +86 18658156856 (Hangzhou)
Gtalk: penglixun(at)gmail.com
Twitter: http://www.twitter.com/plinux
Blog: http://www.penglixun.com

Re: innodb_lock_wait_timeout

[Michael Dykman]

In my implementation we found no need to establish a new connection after a
lock timeout but just retried on the existing connection. We did instigate
a sleep timeout of 10 ms which theoretically increased on each iteration
but we never had to try a third time even under very heavy load.

On 2012-10-12 10:02 AM, Reindl Harald h.rei...@thelounge.net wrote:



Am 12.10.2012 15:39, schrieb Markus Falb:

 With a low timeout the connection will be terminated sooner, but if the
 application retries anot...
usually if you implement a db-layer with reconnect on error
you will also make a sleep before re-connect

below the relevant snippet of my since years used mysql-layer
this is from the connect-method, the query()-method itself
does disconect/connect on recoverable errors and try the same
query again after a succesfull re-connect

the intention here was to allow restart mysqld at every time
without breaking webserver-requests, usually you do not recognize
the short lag, and yes - this sort of error-handling relaxes locks

 $rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '', $flags);
 if(!$rw)
 {
  for($retry=1; $retry=240; $retry++)
  {
   $this-conn = @mysqli_init();
   if($this-ssl)
   {
if($this-ssl_crt === '')
{
 $this-ssl_crt = 'dummy.crt';
}
/** SSL aktivieren */
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
   }
   $rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
   if($rw)
   {
$this-conn = @mysqli_init();
if($this-ssl)
{
 if($this-ssl_crt === '')
 {
  $this-ssl_crt = 'dummy.crt';
 }
 $this-conn-ssl_set($this-ssl_key, $this-ssl_crt,
$this-ssl_ca, NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
break;
   }
   usleep(62500);
  }
  if(!$rw)
  {
   $this-conn = 0;
   $this-error(mysqli_connect_error());
  }
 }

Re: RE: innodb_lock_wait_timeout

[Michael Dykman]

The original poster mentioned that he is not using transactions explicitly.
Some transactions may still occur as a side effect of some operations under
certain conditions and, in a busy high load environment, cannot be entirely
avoided. Having some experience with this, I can report that it is safe and
highly effective to retry at the application layer.  With a site supporting
1.5M users/day, we set a loop to retry up to 3 times..  Out of tens of
millions of writes/day,  we only hit the lock/timeout a couple hundred
times, and never needed the second retry.

On 2012-10-11 12:36 PM, Rick James rja...@yahoo-inc.com wrote:

A 50-second 'transaction' is much too long.

If you have slow queries, let's see them, together with SHOW CREATE TABLE,
SHOW TABLE STATUS, and EXPLAIN SELECT.  Quite possibly we can make them run
faster, thereby eliminating your problem.




 -Original Message-
 From: Akshay Suryavanshi [mailto:akshay.suryavansh...@gmail.com]
...

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

[Michael Dykman]

What is the result if you echo that line instead of running it?  ie:

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

I'm not clear exactly what the text is of the command you are trying to run.

 - michael dykman

On Wed, Oct 3, 2012 at 9:35 AM, Morning Star
morning.star.c...@gmail.com wrote:
 Hi guys,
 i have a problem when trying to pass shell variable to the SET data
 type in parentheses.
 i have a variable like this:

 $ echo $var
 value1,value2,value3

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

 the result:
 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the
 right syntax to use near 'value1,value2,value3' at line 1

 what do i have to do? please help me.

 Greetings,

 Marco

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




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

 May the Source be with you.

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

Re: user not able to login from localhost

[Michael Dykman]

On Mon, Sep 24, 2012 at 1:55 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
 i have given select/insert/update/delete rights to a user on a specific 
 database, from localhost. when i try to login to mysql using the uid, i get 
 error:



 ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using 
 password: YES)


 what other privilege do i need to give this user?

The permission set you describe should be complete, so look more
closely at what was actually granted and how you are trying to log in.

The obvious questions:

How did you create and grant privileges to that user? Did you use:

grant select,insert,update,delete on mydb.* to mysql@localhost
identified by password('password');

or what variant exactly?

When you are trying to log in, are you doing that explicitly from the
same server or are you accessing remotely?

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

 May the Source be with you.

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

Re: Mysql cluster installation error

[Michael Dykman]

If all you need to transfer is schema, do it the same way you would any
other table type: use mysqldump with the - - no-data option.

On 2012-09-23 1:29 PM, Aastha aast...@gmail.com wrote:

Thanks Nitin.
I specied the location of my.ini while starting the SQL node and it worked
fine.

I have anothe rquestion :
How to connect the cluster and reomte host. And i have to copy a schema
from one Mysql clsuter to another. How do i do that.

Regards,


On Sun, Sep 23, 2012 at 3:13 AM, Nitin Mehta ntn...@yahoo.com wrote:

 Hi Aastha,

 I'm not 10...

Re: Mysql cluster installation error

[Michael Dykman]

If your remote host is not configured as a sql node to your cluster,  you
don't need to just to import the schema.   Run mysqldump on any client
machine specifying any of your configured sql nodes via -host=.

On 2012-09-23 1:40 PM, Aastha aast...@gmail.com wrote:

Thanks!
And how do i connect the cluster from the remote host.
When i try to connect one of the SQL node through remote host it says
access denied.
WHile the same is working fine from local host.
Kindly help.
Thanks!


On Sun, Sep 23, 2012 at 12:35 PM, Michael Dykman mdyk...@gmail.com wrote:

 If all you need to ...

Re: Licensing question about mysql_com.h

[Michael Dykman]

Your code might not qualify for the linking excepetion, but users of
your code can use the inking exception to licence their product
however they choose.

 - michael dykman

On Mon, Apr 9, 2012 at 2:43 PM, James Ots my...@jamesots.com wrote:
 I don't think I can use a linking exception when I license my code, as
 the GPL says I must license my code with the same licence that the
 original code used.

 James Ots

 On 8 April 2012 00:52, Michael Dykman mdyk...@gmail.com wrote:
 Not quite true.  Your driver would likely have to be published under
 GPL but that allows the linking exception which allows users of your
 driver to avoid having to open-source their own works which utilize
 the driver.    Should someone decide to code bug fixes or extensions
 for your driver, those would necessarily be GPL.

  - michael dykman

 On Sat, Apr 7, 2012 at 6:52 PM, James Ots my...@jamesots.com wrote:
 I am writing a MySQL connector for the Dart programming language. I
 was hoping to licence it under the BSD Licence, but since it uses
 modified parts of mysql_com.h, which is licensed under the GPL, I'm
 guessing that I'll have to licence my connector under the GPL as well?
 And therefore, anyone who used the connector would also have to
 licence their software under the GPL too?

 Am I correct about this? I looked at the FOSS exception, but it
 doesn't seem to apply in this case.

 James Ots

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




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

  May the Source be with you.

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


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




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

 May the Source be with you.

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

Re: Licensing question about mysql_com.h

[Michael Dykman]

Not quite true.  Your driver would likely have to be published under
GPL but that allows the linking exception which allows users of your
driver to avoid having to open-source their own works which utilize
the driver.Should someone decide to code bug fixes or extensions
for your driver, those would necessarily be GPL.

 - michael dykman

On Sat, Apr 7, 2012 at 6:52 PM, James Ots my...@jamesots.com wrote:
 I am writing a MySQL connector for the Dart programming language. I
 was hoping to licence it under the BSD Licence, but since it uses
 modified parts of mysql_com.h, which is licensed under the GPL, I'm
 guessing that I'll have to licence my connector under the GPL as well?
 And therefore, anyone who used the connector would also have to
 licence their software under the GPL too?

 Am I correct about this? I looked at the FOSS exception, but it
 doesn't seem to apply in this case.

 James Ots

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




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

 May the Source be with you.

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

Re: OT: SQL Question

[Michael Dykman]

A many-to-many is generally best accomplished with a third linking
table which contains the ids of the 2 records being linked ie.

create table tflink (
flightid int;
teacherid int;
);

On Fri, Mar 23, 2012 at 10:28 PM, Mark Phillips
m...@phillipsmarketing.biz wrote:
 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



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

 May the Source be with you.

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

Re: One inst has 39 columns- the other 40

[Michael Dykman]

Bear in mind, MySQL Cluster/NDB runs independently of the mysql nodes
used to ast as clients to that cluster.  This is really not an issue.

 - michael dykman

On Thu, Mar 22, 2012 at 1:03 AM, Nuno Tavares nuno.tava...@dri.pt wrote:
 Charles,

 You should run SELECT @@version on both nodes. It looks like you have
 different version' schemas.

 -NT


 Em 22-03-2012 04:41, Brown, Charles escreveu:

 Look man, there has to be someone out there that can tell me why one user
 table has 5 extra columns. Is it version related or my sysprog person missed
 out on a step. Its hard to believe that this problem is unique to my site.

 Host    char(60)        NO      PRI
 User    char(16)        NO      PRI
 Password        char(41)        NO
 Select_priv     enum('N','Y')   NO              N
 Insert_priv     enum('N','Y')   NO              N
 Update_priv     enum('N','Y')   NO              N
 Delete_priv     enum('N','Y')   NO              N
 Create_priv     enum('N','Y')   NO              N
 Drop_priv       enum('N','Y')   NO              N
 Reload_priv     enum('N','Y')   NO              N
 Shutdown_priv   enum('N','Y')   NO              N
 Process_priv    enum('N','Y')   NO              N
 File_priv       enum('N','Y')   NO              N
 Grant_priv      enum('N','Y')   NO              N
 References_priv enum('N','Y')   NO              N
 Index_priv      enum('N','Y')   NO              N
 Alter_priv      enum('N','Y')   NO              N
 Show_db_priv    enum('N','Y')   NO              N
 Super_priv      enum('N','Y')   NO              N
 Create_tmp_table_priv   enum('N','Y')   NO              N
 Lock_tables_priv        enum('N','Y')   NO              N
 Execute_priv    enum('N','Y')   NO              N
 Repl_slave_priv enum('N','Y')   NO              N
 Repl_client_priv        enum('N','Y')   NO              N
 Create_view_priv        enum('N','Y')   NO              N
 Show_view_priv  enum('N','Y')   NO              N
 Create_routine_priv     enum('N','Y')   NO              N
 Alter_routine_priv      enum('N','Y')   NO              N
 Create_user_priv        enum('N','Y')   NO              N
 Event_priv      enum('N','Y')   NO              N
 Trigger_priv    enum('N','Y')   NO              N
 Create_tablespace_priv  enum('N','Y')   NO              N
 ssl_type        enum('','ANY','X509','SPECIFIED')       NO
 ssl_cipher      blob    NO
 x509_issuer     blob    NO
 x509_subject    blob    NO
 max_questions   int(11) unsigned        NO              0
 max_updates     int(11) unsigned        NO              0
 max_connections int(11) unsigned        NO              0
 max_user_connections    int(11) unsigned        NO              0
 plugin  char(64)        YES
 authentication_string   text    YES

 
        Host    char(60)        NO
        User    char(16)        NO
        Password        char(41)        NO
        Select_priv     enum('N','Y')   NO              N
        Insert_priv     enum('N','Y')   NO              N
        Update_priv     enum('N','Y')   NO              N
        Delete_priv     enum('N','Y')   NO              N
        Create_priv     enum('N','Y')   NO              N
        Drop_priv       enum('N','Y')   NO              N
        Reload_priv     enum('N','Y')   NO              N
        Shutdown_priv   enum('N','Y')   NO              N
        Process_priv    enum('N','Y')   NO              N
        File_priv       enum('N','Y')   NO              N
        Grant_priv      enum('N','Y')   NO              N
        References_priv enum('N','Y')   NO              N
        Index_priv      enum('N','Y')   NO              N
        Alter_priv      enum('N','Y')   NO              N
        Show_db_priv    enum('N','Y')   NO              N
        Super_priv      enum('N','Y')   NO              N
        Create_tmp_table_priv   enum('N','Y')   NO              N
        Lock_tables_priv        enum('N','Y')   NO              N
        Execute_priv    enum('N','Y')   NO              N
        Repl_slave_priv enum('N','Y')   NO              N
        Repl_client_priv        enum('N','Y')   NO              N
        Create_view_priv        enum('N','Y')   NO              N
        Show_view_priv  enum('N','Y')   NO              N
        Create_routine_priv     enum('N','Y')   NO              N
        Alter_routine_priv      enum('N','Y')   NO              N
        Create_user_priv        enum('N','Y')   NO              N
        ssl_type        enum('','ANY','X509','SPECIFIED')       NO

        ssl_cipher      blob    NO
        x509_issuer     blob    NO
        x509_subject    blob    NO
        max_questions   int(11) unsigned        NO              0
        max_updates     int(11) unsigned        NO              0
        max_connections int(11) unsigned        NO              0
        max_user_connections    int(11) unsigned        NO              0





 -Original Message-
 From: Rik Wasmus [mailto:r...@grib.nl]
 Sent: Wednesday, March 21, 2012 5:08 AM
 To: mysql

Re: does the number of column affect performance

[Michael Dykman]

not to mention, updating any field on table 2 requires the entire
contents to be written out again instead of being able to effect those
columns individually.

Quite a few NoSL solutions work on a similar model which, while useful
in places, is decidedly NOT relational.  If you are *certain* that the
primary key is the only key you will ever need and it is acceptable to
read/write all fields together each and every time, then perhaps one
of those products will suit you.  MySQL is a Relational Database
Management System and best suited for relational database management.

Don't take this as a specific recommendation. My experience with NoSQL
systems suggests that in many cases the application would have been
better off with a relational engine underneath.  All too often, a lack
of up-front analysis lures developers and architects into thinking
that the relational properties are not important, only to find out
later in the project that they are critical.,

 - michael dykman

On Tue, Feb 28, 2012 at 2:41 PM, Paul DuBois paul.dub...@oracle.com wrote:

 On Feb 28, 2012, at 9:59 AM, Zheng Li wrote:

 for example
 there are 2 tables to save same data
 table A has 10 columns: a primary key column and 9 blob column
 table B has 2 columns : a primary key column and 1 blob column which 
 includes all data in 2nd~10th columns of table A

 are there any differences in performance when selecting, inserting, 
 updating, and deleting data.

 Sure. For example, with table A, you can select only those blob columns 
 you're interested in. With B, you have to select all of them if you want 
 *any* of them.


 --
 Paul DuBois
 Oracle Corporation / MySQL Documentation Team
 Madison, Wisconsin, USA
 www.mysql.com


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




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

 May the Source be with you.

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

Re: two 5.1 servers, different behaviour

[Michael Dykman]

That error is coming from neither the MySQL server nor from
DBVisualizer.  That is coming from your JDBC driver.  Check the
version of that and research the effect of configuration options.

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html


 - michael dykman

On Fri, Feb 24, 2012 at 10:44 AM, William Bulley w...@umich.edu wrote:
 I am using DBvisualizer to inspect two MySQL 5.1 databases on two
 different systems.  The two different systems are nearly identical
 in the O/S and the version of MySQL (5.1.58 vs 5.1.60).  Both the
 systems have MySQL configured in the same way (see below) and are
 successfully using MySQL underneath a Perl CGI application.

 mysql5.1.60% cat /usr/local/etc/mysql/my.cnf
 [mysqld]
 max_allowed_packet = 16M
 #skip-networking

 mysql5.1.58% cat /usr/local/etc/mysql/my.cnf
 [mysqld]
 max_allowed_packet = 16M
 #skip-networking

 I can successfully connect to the 5.1.60 server using DBvisualizer,
 but I get the following well-known error when I try to connect to the
 5.1.58 server on the other system:

   com.mysql.jdbc.PacketTooBigException: Packet for query is too
   large (7696217  1048576). You can change this value on the server
   by setting the max_allowed_packet' variable.

 I have contacted the support folks at DBvisualizer sending them the
 following stack trace when the above exception occurs.  They have no
 clue and suggested I contact the MySQL community which I am now doing.

  =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

 Product:  DbVisualizer Personal 8.0
 Build:  #1689 (2011/06/16 12:10)
 Java VM:  OpenJDK Client VM
 Java Version:  1.6.0
 Java Vendor:  Sun Microsystems Inc.
 OS Name:  FreeBSD
 OS Arch:  i386
 OS Version:  8.2-STABLE

 13:32:29 [DEBUG AWT-EventQueue-1 DbVisualizerGUI.?] Init Seconds: 4.630 
 splash display: 1.238 window display: 4.629
 13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic 
 ClassLoader: com.mysql.jdbc.Driver
 13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic 
 ClassLoader: com.mysql.jdbc.Driver
 13:32:39 [DEBUG Thread-6 G.?] Connecting: myapp test/development server
 13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader: 
 com.mysql.jdbc.Driver
 13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader: 
 com.mysql.jdbc.Driver
 13:32:39 [DEBUG pool-1-thread-1 D.?] RootConnection: 
 Driver.acceptsURL(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp)
 13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection: 
 Driver.connect(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp, {user=, 
 password=})
 13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection: EXCEPTION - 
 com.mysql.jdbc.PacketTooBigException: Packet for query is too large (7696217 
  1048576). You can change this value on the server by setting the 
 max_allowed_packet' variable.

  =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

 I could easily upgrade to 5.1.60 on the failing system, but I'd rather
 not have to do that unless that is the called for solution.

 I would be greatful for any hints or suggestions as to how I might go
 about correcting this problem.  Thank you in advance.

 Regards,

 web...

 --
 William Bulley                     Email: w...@umich.edu

 72 characters width template -|

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




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

 May the Source be with you.

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

Re: two 5.1 servers, different behaviour

[Michael Dykman]

I see your point.  Configuration files aside, what do you get when you
query the servers themselves with:

show variables like 'max_allowed_packet'

There *might* be something in your start-up scripts overriding that
config setting.

 - md

On Fri, Feb 24, 2012 at 11:57 AM, William Bulley w...@umich.edu wrote:
 According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 11:42:

 That error is coming from neither the MySQL server nor from
 DBVisualizer.  That is coming from your JDBC driver.  Check the
 version of that and research the effect of configuration options.

 http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

 Thanks for the reply.

 I understood that the error came from the JBDC driver because of the
 Java class path given in the exception text:

   com.mysql.jdbc.PacketTooBigException

 But that isn't the issue.  The same DbVisualizer instance works just
 fine when connecting to the MySQL 5.1.60 server on system A, but fails
 with the query too large exception when trying to connect to the
 MySQL 5.1.58 server on system B.  It strains credulity to think that
 the same DbVisualizer instance would use two different JBDC drivers
 when the DbVisualizer connect string begins with jdbc:mysql://...
 in both cases.

 I found this file in my DbVisualizer installation directory:

   unix% cat /usr/local/share/dbvis/jdbc/mysql/README
   MySQL-AB JDBC Driver
   
   Version:    5.1.16
   Files:      mysql.jar
   Reference:  http://www.mysql.com

   DbVis Software AB is a MySQL Network Certified Partner
   and have the right to distribute the Connector/J driver.

 So what I can't explain is why it works for one and not the other.

 Regards,

 web...

 --
 William Bulley                     Email: w...@umich.edu

 72 characters width template -|



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

 May the Source be with you.

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

Re: two 5.1 servers, different behaviour

[Michael Dykman]

At this point, I would not know what else to do except fire up
wireshark and start debugging the packets.

Are both servers on the same subnet?
Is your DBVisualizer client local to either of these or on the same
subnet as one and not another?

You mentioned a minor version difference between the servers..  Have
you read the relevant release notes between those versions?

This looks like an ugly one.  I don't envy you.

 - michael dykman

On Fri, Feb 24, 2012 at 1:09 PM, William Bulley w...@umich.edu wrote:
 According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 12:58:

 I see your point.  Configuration files aside, what do you get when you
 query the servers themselves with:

     show variables like 'max_allowed_packet'

 There *might* be something in your start-up scripts overriding that
 config setting.

 The query on the 5.1.60 server returned this:

   mysql show variables like 'max_allowed_packet';
   ++--+
   | Variable_name      | Value    |
   ++--+
   | max_allowed_packet | 16777216 |
   ++--+
   1 row in set (0.00 sec)

   mysql

 And from the 5.1.58 server this:

   mysql show variables like 'max_allowed_packet';
   ++--+
   | Variable_name      | Value    |
   ++--+
   | max_allowed_packet | 16777216 |
   ++--+
   1 row in set (0.01 sec)

   mysql

 This is what I exepected since the my.cnf files are configured
 identically on the two servers as I indicated earlier.

 BTW, this query was run by us yesterday in an attempt to debug
 this issue.  At this point we were stumped and called it a day.  :-(

 Today, I got the less than helpful from the support folks at
 DbVisualizer and then I contacted the MySQL community.   :-)

 Regards,

 web...

 --
 William Bulley                     Email: w...@umich.edu

 72 characters width template -|



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

 May the Source be with you.

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

Re: two 5.1 servers, different behaviour

[Michael Dykman]

On Fri, Feb 24, 2012 at 1:46 PM, William Bulley w...@umich.edu wrote:
 Are both servers on the same subnet?

 Negative.

*maybe* your router is doing some packet mangling?  Wild shot in the dark..

 Is your DBVisualizer client local to either of these or on the same
 subnet as one and not another?

 The DbVisualizer client tool is local to the 5.1.60 server.

Perhaps try hitting that 'local' server' from a remote client to see
if the effect is the same? Is this the one misbehaving?

 You mentioned a minor version difference between the servers..  Have
 you read the relevant release notes between those versions?

 I think that might be a next step, but even more expedient would be
 upgrading the 5.1.58 server to 5.1.60 or 5.1.61 version and retest.

Agreed!

 This looks like an ugly one.  I don't envy you.

 Gee, thanks for those words of encouragement - NOT!   :-)

What I meant to say was: I can't think of a better way to spend a
weekend.  Have fun!

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

 May the Source be with you.

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

Re: Stored Procedure Debugging?

[Michael Dykman]

In my community, interest would be VERY high.  I often counsel putting
logic on the server; the biggest point of relunctance is the
difficulty debugging.

 - michael dykman

On Wed, Feb 15, 2012 at 10:45 AM, Martijn Tonies m.ton...@upscene.com wrote:
 Hi all,

 As you probably now, we created Database Workbench, a developer
 tool for MySQL and other DBMSses.

 This tool includes a Stored Routine Debugger for several DBMSses, including
 Firebird and InterBase, but not MySQL.

 Both Firebird and InterBase do not provide a debugging API, so our
 tool emulates stored code behaviour at the client side.

 As far as I know, MySQL doesn't have a debugging interface either.

 I'm wondering if there would be a market to add such emulation to
 Database Workbench.

 For info and screenshots, see:
 http://upscene.com/documentation/dbw4/tools_debugger_basics.htm

 Would you like to be able to debug stored routines like this?


 With regards,

 Martijn Tonies
 Upscene Productions
 http://www.upscene.com

 Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
 Anywhere, MySQL, InterBase, NexusDB and Firebird!

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




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

 May the Source be with you.

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

Re: conditional updating

[Michael Dykman]

untested:

update  set mydate = IF(mydate =  '-00-00', now(), mydate)

 - michael dykman

On Thu, Feb 9, 2012 at 8:14 AM, william drescher
will...@techservsys.com wrote:
 I want to update a date field in a record. if the date in the field is
 -00-00 I want to change it to the current date.  I would appreciate
 suggestions or links on how to do this.

 Yup, tried reading the manual, but need a bit of help.
 I will be updating another field at the same time.

 bill


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




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

 May the Source be with you.

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

Re: conditional updating

[Michael Dykman]

He did mention that there was another field he was updating, which
implies that the state of the date field was not the only condition.

 - michael

On Thu, Feb 9, 2012 at 9:22 AM, Johnny Withers joh...@pixelated.net wrote:
 So, add your other criteria to the where clause, you failed to say
 there were other conditions in your first email.

 Sent from my iPad

 On Feb 9, 2012, at 7:56 AM, william drescher will...@techservsys.com wrote:

 On 2/9/2012 8:22 AM, Johnny Withers wrote:
 Update table set mydate=now() where mydate='-00-00'; should do it.
 can't do that because the record is selected by other criteria.
 Thanks

 bill



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




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

 May the Source be with you.

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

Re: Tuning mysql

[Michael Dykman]

Good advice, all of it.  What hasn't been said and should be noted: in
most cases, the bottleneck is the queries themselves.  You will
generally get a lot more boost from tuning those than from any
configuration tweaking (excepting the pathological cases).

 - michael dykman


On Thu, Feb 9, 2012 at 10:52 AM, Giovanni Bechis bi...@snb.it wrote:
 Grant emailgr...@gmail.com wrote:
 I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should
 change any settings.  I'm using mysql with a website on the same
 server so I have skip-networking, and I increased key_buffer and
 innodb_buffer_pool_size from 16M to 256M.  Everything else is default.
 Should I consider changing these or any other settings?

 pt-variable-advisor from percona-toolkit 
 (http://www.percona.com/downloads/percona-toolkit/2.0.3/)
  Giovanni
 --
 /*
  * SnB - Hosting and software solutions
  * http://www.snb.it
  */


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




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

 May the Source be with you.

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

Re: Filesystem choice

[Michael Dykman]

If what you are looking for is performance optimization, you might
want to consider:

http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html

 - michael dykman

On Tue, Feb 7, 2012 at 1:31 PM, List Man list@bluejeantime.com wrote:
 Ext4 is faster to me.


 LS



 - Original Message -
 From: rickytato rickytato rickyt...@r2consulting.it
 To: mysql@lists.mysql.com
 Sent: Tuesday, February 7, 2012 1:19:32 PM
 Subject: Filesystem choice

 Hi,
 I'm my new server I've to decided what filesystem to used.
 The server are dual amd six core 2.4GHz, 32GB ram, and 4x 300GB SAS 15krpm
 raid10 with perc700 512MB raid controller.

 I've to chosse between xfs and ext4; ext4 with
 noatime,nodiratime,data=writeback,barrier=0,nobh,commit=100,errors=remount-ro

 and formatted with
 -b 4096 -E stride=16,stripe-width=32

 is right choice or nobarrier is too unsafe? Only for mysql partition, non
 for the root.


 rr

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




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

 May the Source be with you.

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

Re: Filesystem choice

[Michael Dykman]

In the case of using raw devices (which I'm not really sold on in
general, but there are cases when performance is all), we ran our
backups from a slave replica.

On Tue, Feb 7, 2012 at 1:42 PM, Sameh Attia sat...@gmail.com wrote:
 Hi,
   Check these:

 http://www.enterprisestorageforum.com/storage-hardware/test-plan-for-linux-file-system-fsck-testing.html
 http://www.enterprisestorageforum.com/sans/features/article.php/3749926
 http://www.enterprisestorageforum.com/storage-hardware/the-state-of-file-systems-technology-problem-statement.html
 http://www.enterprisestorageforum.com/storage-technology/the-future-of-storage-devices-and-tiering-software.html
 http://www.enterprisestorageforum.com/storage-hardware/linux-file-system-fsck-testingthe-results-are-in.html

 Regards
 Sameh Attia
 --
 - Failure is not an option; it is a built-in feature in Windows.
 - The two basic principles of system administration:

  * For minor problems, reboot
  * For major problems, reinstall

 dc -e
 '603178305900664311156641389051003470569569613466992253686426210705237258P'


 On Tue, Feb 7, 2012 at 8:31 PM, List Man list@bluejeantime.com wrote:

 Ext4 is faster to me.


 LS



 - Original Message -
 From: rickytato rickytato rickyt...@r2consulting.it
 To: mysql@lists.mysql.com
 Sent: Tuesday, February 7, 2012 1:19:32 PM
 Subject: Filesystem choice

 Hi,
 I'm my new server I've to decided what filesystem to used.
 The server are dual amd six core 2.4GHz, 32GB ram, and 4x 300GB SAS 15krpm
 raid10 with perc700 512MB raid controller.

 I've to chosse between xfs and ext4; ext4 with

 noatime,nodiratime,data=writeback,barrier=0,nobh,commit=100,errors=remount-ro

 and formatted with
 -b 4096 -E stride=16,stripe-width=32

 is right choice or nobarrier is too unsafe? Only for mysql partition, non
 for the root.


 rr

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





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

 May the Source be with you.

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

Re: Filesystem choice

[Michael Dykman]

Thank you for that Claudio.

To be fair, we studied them a few years ago for a high-traffic website
we were migrating from 4.0 to 5.0.  The proof-of-concept was sound and
tested well but concerns from the sysadmin team kept that model from
going to production.

Again, I did not mean that to appear to be a recommendation, just an
item on the checklist to consider if performance-at-all-costs is the
goal.

 - michael dykman


On Tue, Feb 7, 2012 at 3:19 PM, Claudio Nanni claudio.na...@gmail.com wrote:
 Micheal,

 I have the feeling that no one on this planet uses raw devices with mysql,
 I might be wrong but I think InnoDB is kind of 'optimized' to leverage the
 filesystem facilities,
 but I would really like an InnoDB expert opinion here.

 Claudio

 2012/2/7 Michael Dykman mdyk...@gmail.com

 In the case of using raw devices (which I'm not really sold on in
 general, but there are cases when performance is all), we ran our
 backups from a slave replica.

 On Tue, Feb 7, 2012 at 1:42 PM, Sameh Attia sat...@gmail.com wrote:
  Hi,
    Check these:
 
 
  http://www.enterprisestorageforum.com/storage-hardware/test-plan-for-linux-file-system-fsck-testing.html
  http://www.enterprisestorageforum.com/sans/features/article.php/3749926
 
  http://www.enterprisestorageforum.com/storage-hardware/the-state-of-file-systems-technology-problem-statement.html
 
  http://www.enterprisestorageforum.com/storage-technology/the-future-of-storage-devices-and-tiering-software.html
 
  http://www.enterprisestorageforum.com/storage-hardware/linux-file-system-fsck-testingthe-results-are-in.html
 
  Regards
  Sameh Attia
  --
  - Failure is not an option; it is a built-in feature in Windows.
  - The two basic principles of system administration:
 
   * For minor problems, reboot
   * For major problems, reinstall
 
  dc -e
 
  '603178305900664311156641389051003470569569613466992253686426210705237258P'
 
 
  On Tue, Feb 7, 2012 at 8:31 PM, List Man list@bluejeantime.com
  wrote:
 
  Ext4 is faster to me.
 
 
  LS
 
 
 
  - Original Message -
  From: rickytato rickytato rickyt...@r2consulting.it
  To: mysql@lists.mysql.com
  Sent: Tuesday, February 7, 2012 1:19:32 PM
  Subject: Filesystem choice
 
  Hi,
  I'm my new server I've to decided what filesystem to used.
  The server are dual amd six core 2.4GHz, 32GB ram, and 4x 300GB SAS
  15krpm
  raid10 with perc700 512MB raid controller.
 
  I've to chosse between xfs and ext4; ext4 with
 
 
  noatime,nodiratime,data=writeback,barrier=0,nobh,commit=100,errors=remount-ro
 
  and formatted with
  -b 4096 -E stride=16,stripe-width=32
 
  is right choice or nobarrier is too unsafe? Only for mysql partition,
  non
  for the root.
 
 
  rr
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:    http://lists.mysql.com/mysql
 
 



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

  May the Source be with you.

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




 --
 Claudio



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

 May the Source be with you.

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

Re: mysql won't start with service, but starts with mysqld_safe

[Michael Dykman]

On Mon, Feb 6, 2012 at 3:34 PM, Larry Martell larry.mart...@gmail.com wrote:
 On Mon, Feb 6, 2012 at 1:26 PM, Shawn Green (MySQL)
 shawn.l.gr...@oracle.com wrote:
 On 2/4/2012 19:57, Larry Martell wrote:

 Just installed mysql on centos 6.2. When I try to start it with service I
 get:

 #service mysqld start
 MySQL Daemon failed to start.
 Starting mysqld:                                           [FAILED]

 Nothing at all is written to the error log.

 But if I start it with mysqld_safe it comes up and works fine.

 Anyone know what could be going on here?

 -larry


 If the daemon is attempting to change users during startup, then you must be
 root when you start it.  Otherwise, become the user `mysql` then start the
 daemon (service) under the proper credentials.

 http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_user

 I was root when issuing the 'service mysqld start' command. I just
 tried it as the mysql user, and it failed in the same way.

 I traced the service mysqld start and I saw that it invoked
 /etc/init.d/mysqld (which invokes mysqld_safe). When I invoke
 /etc/init.d/mysqld (or mysqld_safe) the server comes up fine. Very
 odd.

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




I haven't seen it mentioned, so I'll give it a shot:  Have you checked
the permissions on your data directory?  If you are getting no message
in your .err file thhat is often the cause.  It should be owned by the
mysql user.

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

 May the Source be with you.

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

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

[Michael Dykman]

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

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

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

 - michael dykman

2012/2/5 Halász Sándor h...@tbbs.net:
 2012/02/04 19:13 -0800, Rajeev Prasad 
 MySQL datetime field type keeps data as: -MM-DD HH:mm:SS is there a way 
 to store this data as: /MM/DD HH:mm:SS or going much further (optionally) 
 can we store as: MM/DD/ HH:mm:SS  ?   if not then whats the best way to 
 reformat the cell value from -MM-DD to MM/DD/YYY
 
 That is MySQL s string format, and that is what you get. That said, there is 
 a function DATE_FORMAT (look it up) that lets one change its look. Its format 
 argument is quite ugly.


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




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

 May the Source be with you.

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

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

[Michael Dykman]

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

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

 - michael

On Sun, Feb 5, 2012 at 11:07 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
 thx Michael,

 but the page says:

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


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

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

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

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

 - michael dykman

 2012/2/5 Halász Sándor h...@tbbs.net:
 2012/02/04 19:13 -0800, Rajeev Prasad 
 MySQL datetime field type keeps data as: -MM-DD HH:mm:SS is there a way 
 to store this data as: /MM/DD HH:mm:SS or going much further 
 (optionally) can we store as: MM/DD/ HH:mm:SS  ?   if not then whats the 
 best way to reformat the cell value from -MM-DD to MM/DD/YYY
 
 That is MySQL s string format, and that is what you get. That said, there is 
 a function DATE_FORMAT (look it up) that lets one change its look. Its 
 format argument is quite ugly.


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




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

  May the Source be with you.

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



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

 May the Source be with you.

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

Re: MYD, MYI and TMD files

[Michael Dykman]

This is still the list although more quiet than it used to be.

Repairing a table is already a fragile process..  I would not try to
interrupt it if the data has no backup.

 - michael dykman

On Mon, Jan 23, 2012 at 11:04 PM, kalin m ka...@el.net wrote:


 hey. this list used to be pretty active. did anything change? is there
 another place to ask questions like these?

 thanks...


 On 1/23/12 5:02 PM, kalin m wrote:


 hi all..

 i started a repair on a table that has an MYD file of 9.2 gigs. the MYI
 file is 7.7 gigs. the TMD file, which i'm assuming is a temporary file
 used in the repair process, is currently 400mb. this has been going on
 for about 30 min now. the question is does the TMD file need to get
 anywhere near the size of any of this MYD or MYI files in order to get
 this repair done?! and if so why is it so slow?

 i'm not really planning to stop the repair command cause i read
 somewhere that this might not be a good idea. can i stop it without
 risking any data loss or table damage?

 thanks...


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




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

 May the Source be with you.

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

Re: MYD, MYI and TMD files

[Michael Dykman]

I couldn't say.  I have not had particularly good luck doing MyISAM repairs.

 - md

On Mon, Jan 23, 2012 at 11:49 PM, kalin m ka...@el.net wrote:

 ok. thanks. i thought so. it's been about 8 hrs so far. and the TMD file
 is about half the MYD/MYI file. although MYI is about a gig smaller than
 the MYD. which one has the TDM to reach in size in order for the repair
 to be complete?

 thanks.


 On 1/23/12 11:27 PM, Michael Dykman wrote:
 This is still the list although more quiet than it used to be.

 Repairing a table is already a fragile process..  I would not try to
 interrupt it if the data has no backup.

  - michael dykman

 On Mon, Jan 23, 2012 at 11:04 PM, kalin m ka...@el.net wrote:


 hey. this list used to be pretty active. did anything change? is there
 another place to ask questions like these?

 thanks...


 On 1/23/12 5:02 PM, kalin m wrote:


 hi all..

 i started a repair on a table that has an MYD file of 9.2 gigs. the MYI
 file is 7.7 gigs. the TMD file, which i'm assuming is a temporary file
 used in the repair process, is currently 400mb. this has been going on
 for about 30 min now. the question is does the TMD file need to get
 anywhere near the size of any of this MYD or MYI files in order to get
 this repair done?! and if so why is it so slow?

 i'm not really planning to stop the repair command cause i read
 somewhere that this might not be a good idea. can i stop it without
 risking any data loss or table damage?

 thanks...


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







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

 May the Source be with you.

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

Re: Editing/form interface

[Michael Dykman]

Mysql is a backend service and has no such application-level tools.

You can, however, use MS-Access (or any other such tool) and use MySL
as a backend via an ODBC driver.

 - micael dykman


On Wed, Jan 18, 2012 at 3:34 PM, Tim Johnson t...@akwebsoft.com wrote:
 Are there any utilities available that will enable the quick design
 and implementation of forms for editing and adding records?

 I.E. something like M$-Access or OpenOffice form designer/wizards.
 thanks
 --
 Tim
 tim at tee jay forty nine dot com or akwebsoft dot com
 http://www.akwebsoft.com

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




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

 May the Source be with you.

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

Re: Editing/form interface

[Michael Dykman]

Tim,

I'm a dyed-in-the-wool console user myself.  I find the GUI tools make
it quicker getting a project started, slower getting a project done
so, no, I can't recommend a thing.

 - michael


On Wed, Jan 18, 2012 at 7:11 PM, Tim Johnson t...@akwebsoft.com wrote:
 * Michael Dykman mdyk...@gmail.com [120118 13:00]:
 Mysql is a backend service and has no such application-level tools.

 You can, however, use MS-Access (or any other such tool) and use MySL
 as a backend via an ODBC driver.
  Thanks Michael. I have use MS-Access in the past, but we no longer
  use Windows here. I've tried with Open Office, but it is not
  nearly as kind to work with as Access. Do you have any other
  recommendations?
 --
 Tim
 tim at tee jay forty nine dot com or akwebsoft dot com
 http://www.akwebsoft.com

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




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

 May the Source be with you.

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

Re: Aborted connection 102

[Michael Dykman]

102 is just the run-time assigned connection id.  It appears that the
server bailed on a client connection due to a timeout.  This suggests
that either you have/had network issues, an error on the client side
or someone just killed a client connection without closing properly.
Unless you are seeing a lot of these, don't waste your time..  it
looks like operational white-noise.

 - michael dykman
On Wed, Dec 14, 2011 at 9:39 AM, Rafael Valenzuela rav...@gmail.com wrote:
 Hi everybody,
 I have this notice ,
 111214 11:55:53 [Warning] Aborted connection 102 to db: 'proninop_proninop'
 user: 'pronino' host: 'xxx.xxx.xxx.xxx' (Got timeout reading communication
 packets)
 I have watched in this site
 http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html but I have
 not seen the number 102.
 Any idea?
 thanks a lot

 --
 Mit forever
 My Blog http://www.redcloverbi.wordpress.com
 My Faborite 
 Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm
 http://www.technologyreview.com/



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

 May the Source be with you.

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

Re: PID and LOG file

[Michael Dykman]

mysqld_safe might not know where your config file is located.  You can
specify it's location at the command line with
--defaults-file=file_name

alternatively, you could specify the location of your log file thus:
--log-error=file_name

 other options found at
   http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html

 - michael dykman

On Tue, Nov 15, 2011 at 7:31 AM, javad bakhshi javadbakh...@yahoo.com wrote:
 Hi,

 I am trying to install Mysql on linux which I don't have a root access. I 
 have done the folowing :

 1. Download source files from http://dev.mysql.com/downloads/mysql/ and 
 un-tar thwm
 2. configure the instalation using ./configure as:
     CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions 
 -fno-rtti ; \
 ./configure --prefix=/bubo/home/h10/javad/scratch/mysql \
     --enable-assembler \
     --with-mysqld-ldflags=-all-static \
     --with-client-ldflags=-all-static \
     --with-mysqld-user=javad \
     
 --with-unix-socket-path=/bubo/home/h10/javad/scratch/mysql/tmp/mysql.sock \
     --localstatedir=/bubo/home/h10/javad/scratch/mysql/share \
         --datadir=/bubo/home/h10/javad/scratch/mysql/data\
             --enable-thread-safe-client

 3. execute ./bin/mysql_install_db
 4. execute bin/mysqld_safe  to run mysql. but at this point I get the 
 folowing error:
     Starting mysqld daemon with databases from 
 /bubo/home/h10/javad/scratch/mysql/data
     ./bin/mysqld_safe: line 394: /var/log/mysqld.log: Permission denied
     ./bin/mysqld_safe: line 402: /var/log/mysqld.log: Permission denied
     STOPPING server from pid file 
 /bubo/home/h10/javad/scratch/mysql/data/the.pid
     tee: /var/log/mysqld.log: Permission denied
     15 13:14:18  mysqld ended
     tee: /var/log/mysqld.log: Permission denied

 I don't know why I get this error since I have changed the my.cnf to
     err-log=/bubo/home/h10/javad/scratch/mysql/data/the.log
     pid-file=/bubo/home/h10/javad/scratch/mysql/data/the.pid





 Best regards,
 Javad Bakhshi,




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

Re: MySQL install on OS X Lion does not create root account

[Michael Dykman]

You showed us a link which explains how you uninstalled MySQL.
Perhaps you could tell us how you installed it?  IT would certainly
shed some light on the subject.  If you are using a pre-built package
for OS/X, the issue is likely with the package.  If you are installing
from source, there is an initialization step you may have missed.

 - michael dykman


On Tue, Nov 15, 2011 at 4:21 PM, Martin Mueller
martinmuel...@northwestern.edu wrote:
 I have been struggling with installing MySQL 5.5.17 on a Mac running OS
 10.7. My machine had 5.5.15 on it.

 I keep getting user denied access messages (Error 1045).   I thought at
 first it was a matter of a password not being flushed from a previous
 installation and went through repeated install and uninstall routines, using
 the instructions by Rob Allen at
 (http://akrabat.com/computing/uninstalling-mysql-on-mac-os-x-leopard/)
 about:blank .

 I also followed various instructions in the MySQL documentation for
 resetting lost passwords. But the results persuaded me that the problem is
 something else:  the installer does not create a root account or a mysql
 account in the user table. Thus the update command for resetting the root
 password executes correctly but update 0 rows. And logging in with the
 skip-grants-table lets you address some queries to the mysql user table.

 Select count(user) from user returns 0, as does select user, password
 from user. So it appears that the installation routine (from the disk
 image) does not populate the user table with accounts from which you could
 then perform other activities.

 Does anybody have any idea what is going on here or what I could be doing
 wrong?








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

Re: Cleaning up old files

[Michael Dykman]

Those are your binary logs.  They store the data stream to be consumed
by your slaves.
.http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Are you using replication?  If so, you need to go to each of your
slaves and run the command 'SHOW MASTER STATUS;'. That will tell you
which files are currently being consumed by your slaves. Anything
older than the oldest one can be safely deleted.  (the older ones are
those with the lowest numbers embedded in the filename.

If you are not using replication,  erase them all.  You might also
want to turn of 'log-bin' in your configs.

 - michael dykman


On Mon, Nov 14, 2011 at 1:01 PM, Rob Tanner rtan...@linfield.edu wrote:

 Hi,

 In my MySQL directory, I have more than a few gig and a half sized files, 
 mysql-bin.01, mysql-bin.01 and et cetera.  They date from today all 
 the way back to early 2010.  I don't know exactly what those files are but I 
 would like to delete as many as are no longer is use since I had a 40GB 
 partition fill up over the weekend which resulted in bringing down our web 
 server.  So what are those files and can I delete all but the most recent?

 Thanks.


 Rob Tanner
 UNIX Services Manager
 Linfield College, McMinnville Oregon




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

Re: setting max_allowed_packet dynamically problem

[Michael Dykman]

If you changed the value in the .cnf and restarting the server did not pick
up he change, I would hazard a guess that the .cnf file you edited is not
the one your server is reading.

Check your paths and make sure you are editing the correct file.  MySL
never rewrites it's own config files to reflect manually changed values.

On Thu, Nov 3, 2011 at 4:37 PM, List Man list@bluejeantime.com wrote:

 I am running Server version: 5.1.45-log MySQL Community Server (GPL) and I
 attempted to change max packet with the following:  SET GLOBAL
 max_allowed_packet=16*1024*1024; but it did not work properly.  The
 configuration did not change by using the show variables command.  I
 changed the configuration file (my.cnf) and restarted the server and the
 variable stayed the same.  Does anyone have any ideas?

 LS






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

 May the Source be with you.

credit where due

[Michael Dykman]

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.

Re: How to select the id of 2+ records for given user?

[Michael Dykman]

I'm afraid that what you are looking for simply cannot be done with MySQL
alone.  You will need to pare your results at the application layer.
 Remember that rows have no inherent order except for conforming to any
ORDER BY clause contained within the query.

 - md

On Wed, Oct 19, 2011 at 1:27 PM, Dotan Cohen dotanco...@gmail.com wrote:

 Assuming a table such this:
 | ID |  messageID  | userID |
 ||-||
 | 1  | 345 | 71 |
 | 2  | 984 | 71 |
 | 3  | 461 | 72 |
 | 4  | 156 | 73 |
 | 5  | 441 | 73 |
 | 6  | 489 | 73 |
 | 7  | 483 | 74 |
 | 8  | 523 | 74 |
 | 9  | 723 | 74 |

 I need the second, third, fourth, etc messageID for each userID. So I
 would get a results table such as:
 | ID |  messageID  | userID |
 ||-||
 | 2  | 984 | 71 |
 | 5  | 441 | 73 |
 | 6  | 489 | 73 |
 | 7  | 483 | 74 |
 | 9  | 723 | 74 |

 I've tried playing with count and group by and limit, but I've not
 found a solution. I can easily get all the rows and then remove the
 rows that I don't need in PHP, but I'd still like to know if an
 all-MySQL solution is possible.

 Thanks!

 --
 Dotan Cohen

 http://gibberish.co.il
 http://what-is-what.com

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




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

 May the Source be with you.

Re: Starting up the server

[Michael Dykman]

Generally speaking, we are not a windows-oriented group but: are you running
your task manager as an administrator when you start it?

If you google for manually starting services windows 7, that will likely
tell you what you need to know.  Your present issue is with your OS, not
MySQL.

 - michael dykman

On Sat, Oct 15, 2011 at 8:24 PM, AndrewMcHorney andrewmchor...@cox.netwrote:

 Hello

 I installed the server a while back but never did anything with it. I would
 like to get going. How does one start the server. In my task manager under
 the service list I see a mysql service but it is stopped. If I try to start
 the process I get an access denied. I am running under Windows 7.

 Andrew


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/mysql?unsub=mdyk...@gmail.com




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

 May the Source be with you.

Re: MySQL-devel.tar.gz Availability

[Michael Dykman]

Go to: http://dev.mysql.com/downloads/mysql/

and pick source code from the platform list.

 - md

On Thu, Oct 13, 2011 at 11:25 AM, Nick Khamis sym...@gmail.com wrote:

 Hello Everyone,

 I was wondering if the MySQL devel files tar was available for download? I
 was
 only able to find rpm on the mysql site.

 Thanks in Advance,

 Nick.




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

 May the Source be with you.

Re: Monitor Locked query

[Michael Dykman]

Your instrumentation script should be invoking SHOW PROCESSLIST and
parsing the status fields.

 - md

On Mon, Oct 10, 2011 at 2:16 PM, Angela liu yyll2...@yahoo.com wrote:

 Hi, Folks:

 Have any idea how to monitor Locked queries with Nagios?

 Thanks




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

 May the Source be with you.

Re: A Newbie question about make and the term.c file

[Michael Dykman]

I can't help directly with the error message (the warning seems fairly
harmless), but may I inquire why you are building MySQL instead of using one
of the prepared binaries?  Compiling under OS/X can be pretty harrowing.

 - michael dykman

On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote:

 I am trying to install mysql 5.1.59 on my ppc running os x and I get this
 error message in the term.c file.

 cc1: warnings being treated as errors
 term.c: In function ‘term_set’:
 term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers
 from pointer target type
 term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers
 from pointer target type
 make[2]: *** [term.o] Error 1
 make[1]: *** [all-recursive] Error 1
 make: *** [all-recursive] Error 1

 I can't figure out what it means or how to fix it. Help please.

 Peter




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

 May the Source be with you.

Re: optimizer in function

[Michael Dykman]

I think the only clues the optimizer consults with regard to UDFs is the
'characteristic' provided at the time you create the routine.

from http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
*

*

*characteristic*:
COMMENT '*string*'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }


 - michael dykman
2011/10/8 Halász Sándor h...@tbbs.net

 Does the optimizer look into function called from query?

 In my queries the expression (SELECT hwyl FROM Stock) / (SELECT
 regularPayment FROM Stock), where Stock is a one-record table, often is
 repeated. The optimizer sees that, and makes the ratio a constant, and I can
 afford to be clear. If that expression were within a function called from
 the same spot, would the optimizer look into the function and see the same
 effectiv constant? or is it better to make it an argument to the function?


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




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

 May the Source be with you.

Re: MySQL Indexes

[Michael Dykman]

When a query selects on field_a and field_b, that index can be used.  If
querying on field_a alone, the index again is useful.  Query on field_b
alone however, that first index is of no use to you.

On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote:

 This thread has sparked my interest. What is the difference between an
 index on (field_a, field_b) and an index on (field_b, field_a)?


 On 10/06/2011 07:43 PM, Nuno Tavares wrote:

 Neil, whenever you see multiple fields you'd like to index, you should
 consider, at least:

 * The frequency of each query;
 * The occurrences of the same field in multiple queries;
 * The cardinality of each field;

 There is a tool Index Analyzer that may give you some hints, and I
 think it's maatkit that has a tool to run a query log to find good
 candidates - I've seen it somewhere, I believe

 Just remember that idx_a(field_a,field_b) is not the same, and is not
 considered for use, the same way as idx_b(field_b,field_a).

 -NT


 Em 07-10-2011 00:22, Michael Dykman escreveu:

 Only one index at a time can be used per query, so neither strategy is
 optimal.  You need at look at the queries you intend to run against the
 system and construct indexes which support them.

  - md

 On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
 neil.tompk...@googlemail.com**wrote:

  Maybe that was a bad example.  If the query was name = 'Red' what index
 should I create ?

 Should I create a index of all columns used in each query or have a
 index
 on individual column ?


 On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com  wrote:

 For the first query, the obvious index on score will give you optimal
 results.

 The second query is founded on this phrase: Like '%Red%'  and no index
 will help you there.  This is an anti-pattern, I am afraid.  The only
 way
 your database can satisfy that expression is to test each and every
 record
 in the that database (the test itself being expensive as infix finding
 is
 iterative).  Perhaps you should consider this approach instead:
  http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
 natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 
 http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
 natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

 On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@**
 googlemail.com neil.tompk...@googlemail.com
 neil.tompk...@googlemail.com  wrote:

  Hi,

 Can anyone help and offer some advice with regards MySQL indexes.
  Basically
 we have a number of different tables all of which have the obviously
 primary
 keys.  We then have some queries using JOIN statements that run slowly
 than
 we wanted.  How many indexes are recommended per table ?  For example
 should
 I have a index on all fields that will be used in a WHERE statement ?
  Should the indexes be created with multiple fields ?  A example  of
 two
 basic queries

 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 ORDER BY score DESC


 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 AND name Like '%Red%'
 ORDER BY score DESC

 How many indexes should be created for these two queries ?

 Thanks,
 Neil




 --
  - michael dykman
  -mdyk...@gmail.commdykman@**gmail.com 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=mdyk...@gmail.comhttp://lists.mysql.com/mysql?unsub=mdyk...@gmail.com




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

 May the Source be with you.

Re: MySQL Indexes

[Michael Dykman]

How heavily a given table is queried does not directly affect the index
size, only the number and depth of the indexes.

No, it is not that unusual to have the index file bigger.  Just make sure
that every index you have is justified by the queries you are making against
the table.

 - md


On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil
neil.tompk...@googlemail.comwrote:

 Is it normal practice for a heavily queried MYSQL tables to have a index
 file bigger than the data file ?


 On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman mdyk...@gmail.com wrote:

 Only one index at a time can be used per query, so neither strategy is
 optimal.  You need at look at the queries you intend to run against the
 system and construct indexes which support them.

  - md

 On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Maybe that was a bad example.  If the query was name = 'Red' what index
 should I create ?

 Should I create a index of all columns used in each query or have a index
 on individual column ?


 On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote:

 For the first query, the obvious index on score will give you optimal
 results.

 The second query is founded on this phrase: Like '%Red%'  and no index
 will help you there.  This is an anti-pattern, I am afraid.  The only way
 your database can satisfy that expression is to test each and every record
 in the that database (the test itself being expensive as infix finding is
 iterative).  Perhaps you should consider this approach instead:
  http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

 On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil 
 neil.tompk...@googlemail.com
 neil.tompk...@googlemail.com wrote:

 Hi,

 Can anyone help and offer some advice with regards MySQL indexes.
  Basically
 we have a number of different tables all of which have the obviously
 primary
 keys.  We then have some queries using JOIN statements that run slowly
 than
 we wanted.  How many indexes are recommended per table ?  For example
 should
 I have a index on all fields that will be used in a WHERE statement ?
  Should the indexes be created with multiple fields ?  A example  of two
 basic queries

 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 ORDER BY score DESC


 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 AND name Like '%Red%'
 ORDER BY score DESC

 How many indexes should be created for these two queries ?

 Thanks,
 Neil




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

  May the Source be with you.




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

  May the Source be with you.





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

 May the Source be with you.

Re: MySQL Indexes

[Michael Dykman]

No, I don't think it can be called.  It is a direct consequence of the
relational paradigm.  Any implementation of an RDBMS has the same
characteristic.

 - md

On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald h.rei...@thelounge.netwrote:

 but could this not be called a bug?

 Am 07.10.2011 18:08, schrieb Michael Dykman:
  When a query selects on field_a and field_b, that index can be used.  If
  querying on field_a alone, the index again is useful.  Query on field_b
  alone however, that first index is of no use to you.
 
  On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote:
 
  This thread has sparked my interest. What is the difference between an
  index on (field_a, field_b) and an index on (field_b, field_a)?
 
 
  On 10/06/2011 07:43 PM, Nuno Tavares wrote:
 
  Neil, whenever you see multiple fields you'd like to index, you should
  consider, at least:
 
  * The frequency of each query;
  * The occurrences of the same field in multiple queries;
  * The cardinality of each field;
 
  There is a tool Index Analyzer that may give you some hints, and I
  think it's maatkit that has a tool to run a query log to find good
  candidates - I've seen it somewhere, I believe
 
  Just remember that idx_a(field_a,field_b) is not the same, and is not
  considered for use, the same way as idx_b(field_b,field_a).
 
  -NT
 
 
  Em 07-10-2011 00:22, Michael Dykman escreveu:
 
  Only one index at a time can be used per query, so neither strategy is
  optimal.  You need at look at the queries you intend to run against
 the
  system and construct indexes which support them.
 
   - md
 
  On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
  neil.tompk...@googlemail.com**wrote:
 
   Maybe that was a bad example.  If the query was name = 'Red' what
 index
  should I create ?
 
  Should I create a index of all columns used in each query or have a
  index
  on individual column ?
 
 
  On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com  wrote:
 
  For the first query, the obvious index on score will give you optimal
  results.
 
  The second query is founded on this phrase: Like '%Red%'  and no
 index
  will help you there.  This is an anti-pattern, I am afraid.  The only
  way
  your database can satisfy that expression is to test each and every
  record
  in the that database (the test itself being expensive as infix
 finding
  is
  iterative).  Perhaps you should consider this approach instead:
   http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
  natural-language.html
 http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 
  http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
  natural-language.html
 http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 
  On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@**
  googlemail.com neil.tompk...@googlemail.com
  neil.tompk...@googlemail.com  wrote:
 
   Hi,
 
  Can anyone help and offer some advice with regards MySQL indexes.
   Basically
  we have a number of different tables all of which have the obviously
  primary
  keys.  We then have some queries using JOIN statements that run
 slowly
  than
  we wanted.  How many indexes are recommended per table ?  For
 example
  should
  I have a index on all fields that will be used in a WHERE statement
 ?
   Should the indexes be created with multiple fields ?  A example  of
  two
  basic queries
 
  SELECT auto_id, name, score
  FROM test_table
  WHERE score  10
  ORDER BY score DESC
 
 
  SELECT auto_id, name, score
  FROM test_table
  WHERE score  10
  AND name Like '%Red%'
  ORDER BY score DESC
 
  How many indexes should be created for these two queries ?
 
  Thanks,
  Neil
 
 
 
 
  --
   - michael dykman
   -mdyk...@gmail.commdykman@**gmail.com 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=mdyk...@gmail.com
 http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
 
 
 
 

 --

 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm




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

 May the Source be with you.

Re: MySQL Indexes

[Michael Dykman]

The second index you specified '(field_b, field_a)' would be usable when
querying on field_b alone, or both fields in conjunction.  This particular
index is of no value should you be querying 'field_a' alone.  Then that
first index '(field_a, field_b)' would apply.

 - md

On Fri, Oct 7, 2011 at 2:55 PM, Neil Tompkins
neil.tompk...@googlemail.comwrote:

 Can you give more information as to why the second index would be of no use
 ?

 On 7 Oct 2011, at 18:24, Michael Dykman mdyk...@gmail.com wrote:

  No, I don't think it can be called.  It is a direct consequence of the
  relational paradigm.  Any implementation of an RDBMS has the same
  characteristic.
 
  - md
 
  On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald h.rei...@thelounge.net
 wrote:
 
  but could this not be called a bug?
 
  Am 07.10.2011 18:08, schrieb Michael Dykman:
  When a query selects on field_a and field_b, that index can be used.
  If
  querying on field_a alone, the index again is useful.  Query on field_b
  alone however, that first index is of no use to you.
 
  On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com
 wrote:
 
  This thread has sparked my interest. What is the difference between an
  index on (field_a, field_b) and an index on (field_b, field_a)?
 
 
  On 10/06/2011 07:43 PM, Nuno Tavares wrote:
 
  Neil, whenever you see multiple fields you'd like to index, you
 should
  consider, at least:
 
  * The frequency of each query;
  * The occurrences of the same field in multiple queries;
  * The cardinality of each field;
 
  There is a tool Index Analyzer that may give you some hints, and I
  think it's maatkit that has a tool to run a query log to find good
  candidates - I've seen it somewhere, I believe
 
  Just remember that idx_a(field_a,field_b) is not the same, and is not
  considered for use, the same way as idx_b(field_b,field_a).
 
  -NT
 
 
  Em 07-10-2011 00:22, Michael Dykman escreveu:
 
  Only one index at a time can be used per query, so neither strategy
 is
  optimal.  You need at look at the queries you intend to run against
  the
  system and construct indexes which support them.
 
  - md
 
  On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
  neil.tompk...@googlemail.com**wrote:
 
  Maybe that was a bad example.  If the query was name = 'Red' what
  index
  should I create ?
 
  Should I create a index of all columns used in each query or have a
  index
  on individual column ?
 
 
  On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com  wrote:
 
  For the first query, the obvious index on score will give you
 optimal
  results.
 
  The second query is founded on this phrase: Like '%Red%'  and no
  index
  will help you there.  This is an anti-pattern, I am afraid.  The
 only
  way
  your database can satisfy that expression is to test each and every
  record
  in the that database (the test itself being expensive as infix
  finding
  is
  iterative).  Perhaps you should consider this approach instead:
  http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
  natural-language.html
  http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 
  http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
  natural-language.html
  http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 
  On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@**
  googlemail.com neil.tompk...@googlemail.com
  neil.tompk...@googlemail.com  wrote:
 
  Hi,
 
  Can anyone help and offer some advice with regards MySQL indexes.
  Basically
  we have a number of different tables all of which have the
 obviously
  primary
  keys.  We then have some queries using JOIN statements that run
  slowly
  than
  we wanted.  How many indexes are recommended per table ?  For
  example
  should
  I have a index on all fields that will be used in a WHERE
 statement
  ?
  Should the indexes be created with multiple fields ?  A example
  of
  two
  basic queries
 
  SELECT auto_id, name, score
  FROM test_table
  WHERE score  10
  ORDER BY score DESC
 
 
  SELECT auto_id, name, score
  FROM test_table
  WHERE score  10
  AND name Like '%Red%'
  ORDER BY score DESC
 
  How many indexes should be created for these two queries ?
 
  Thanks,
  Neil
 
 
 
 
  --
  - michael dykman
  -mdyk...@gmail.commdykman@**gmail.com 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=mdyk...@gmail.com
  http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
 
 
 
 
 
  --
 
  Mit besten Grüßen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, Hofmühlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
  icq: 154546673, http://www.thelounge.net/
 
  http://www.thelounge.net/signature.asc.what.htm
 
 
 
 
  --
  - michael dykman
  - mdyk...@gmail.com
 
  May the Source be with you.




-- 
 - michael dykman

Re: MySQL anemic GIS support

[Michael Dykman]

Somebody feel to jump in and contradict me here, but I have never had any
love from the MySQL GIS stack.  For the very few functions it does support,
the performance has been abysmal and I generally find myself hacking
together UDFs against columns of FLOAT and avoiding POINT altogether.

 - md

On Fri, Oct 7, 2011 at 10:41 PM, René Fournier m...@renefournier.com wrote:

 Anyone have any idea on if/when MySQL will get real GIS support?


 http://mysqldbnews.blogspot.com/2007/10/does-mysql-gis-make-grade.html

 …is what I'm referring to. Specifically, the factor that many functions are
 quietly replaced with MBRContains(). This makes it, for example, not
 possible to determine with certainty (in SQL) if a point lies within a
 non-rectangular polygon.

 I ask because I'm looking at moving a big part of our applications to
 Postgresql, and, well, I'd rather not have to.

 …Rene
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com




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

 May the Source be with you.

Re: MySQL Indexes

[Michael Dykman]

For the first query, the obvious index on score will give you optimal
results.

The second query is founded on this phrase: Like '%Red%'  and no index
will help you there.  This is an anti-pattern, I am afraid.  The only way
your database can satisfy that expression is to test each and every record
in the that database (the test itself being expensive as infix finding is
iterative).  Perhaps you should consider this approach instead:
http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com
 wrote:

 Hi,

 Can anyone help and offer some advice with regards MySQL indexes.
  Basically
 we have a number of different tables all of which have the obviously
 primary
 keys.  We then have some queries using JOIN statements that run slowly than
 we wanted.  How many indexes are recommended per table ?  For example
 should
 I have a index on all fields that will be used in a WHERE statement ?
  Should the indexes be created with multiple fields ?  A example  of two
 basic queries

 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 ORDER BY score DESC


 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 AND name Like '%Red%'
 ORDER BY score DESC

 How many indexes should be created for these two queries ?

 Thanks,
 Neil




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

 May the Source be with you.

Re: 4 minute slow on select count(*) from table - myisam type

[Michael Dykman]

I am curious..  Are you the only client on this database or or there other
connections doing work in the background?  A busy insert/update heavy
application could cause these effects.

 - michael dykman

On Thu, Oct 6, 2011 at 12:35 PM, Joey L mjh2...@gmail.com wrote:

 i did google search - myisam is faster...i am not really doing any
 transaction stuff.
 thanks

 On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore eroomy...@gmail.com wrote:
  Sorry, hit send by accident there! *face palm*
  Just had a quick scan of the report. You've got 2 1GB disks in software
 raid
  - RAID1 or RAID5? I can also see you're creating a lot of temporary files
 on
  disk. I think in your previous email that your biggest table's index(s)
 were
  larger then the keybuffer size. I would suspect that you're disk bound
 with
  limited IO performance through 2 disks and effectively 1 if in a mirrored
  configuration. The stats show that you're configured for MyISAM and that
  you're tables are taking reads and writes (read heavy though), MyISAM
  doesn't like high concurrency mixed workloads such as yours, it will
 cause
  locking and maybe thats why your count has such a delay. Such activity
 may
  be better suited to InnoDB engine (you must configure and tune for this,
 not
  JUST change the engine).
  HTH
  Andy
 
 
  On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore eroomy...@gmail.com
 wrote:
 
  Joey, does your 'large' table get
 
  On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com wrote:
 
  here is mysqlreport ---
  
  root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
  Use of uninitialized value $is in multiplication (*) at ./mysqlreport
  line 829.
  Use of uninitialized value in formline at ./mysqlreport line 1227.
  MySQL 5.1.49-3-log   uptime 0 0:25:5Thu Oct  6 10:20:49
 2011
 
  __ Key
 _
  Buffer used   727.43M of   2.00G  %Used:  35.52
   Current 963.24M%Usage:  47.03
  Write hit  29.41%
  Read hit   99.79%
 
  __ Questions
 ___
  Total  50.20k33.4/s
   QC Hits  32.56k21.6/s  %Total:  64.87
   DMS  12.28k 8.2/s   24.46
   Com_  3.21k 2.1/s6.39
   COM_QUIT  2.89k 1.9/s5.76
   -Unknown745 0.5/s1.48
  Slow 10 s  68 0.0/s0.14  %DMS:   0.55  Log: OFF
  DMS12.28k 8.2/s   24.46
   SELECT   11.09k 7.4/s   22.10 90.36
   UPDATE  539 0.4/s1.07  4.39
   INSERT  384 0.3/s0.77  3.13
   DELETE  260 0.2/s0.52  2.12
   REPLACE   0   0/s0.00  0.00
  Com_3.21k 2.1/s6.39
   set_option1.10k 0.7/s2.20
   show_fields   1.03k 0.7/s2.05
   admin_comma 707 0.5/s1.41
 
  __ SELECT and Sort
 _
  Scan1.65k 1.1/s %SELECT:  14.87
  Range 493 0.3/s4.44
  Full join 310 0.2/s2.79
  Range check   339 0.2/s3.06
  Full rng join   0   0/s0.00
  Sort scan 887 0.6/s
  Sort range628 0.4/s
  Sort mrg pass   0   0/s
 
  __ Query Cache
 _
  Memory usage5.96M of  16.00M  %Used:  37.25
  Block Fragmnt   5.17%
  Hits   32.56k21.6/s
  Inserts 5.66k 3.8/s
  Insrt:Prune   5.66k:1 3.8/s
  Hit:Insert 5.76:1
 
  __ Table Locks
 _
  Waited513 0.3/s  %Total:   3.62
  Immediate  13.65k 9.1/s
 
  __ Tables
 __
  Open 1024 of 1024%Cache: 100.00
  Opened 14.96k 9.9/s
 
  __ Connections
 _
  Max used   70 of  100  %Max:  70.00
  Total   2.89k 1.9/s
 
  __ Created Temp
 
  Disk table  1.34k 0.9/s
  Table   2.35k 1.6/sSize:  32.0M
  File5 0.0/s
 
  __ Threads
 _
  Running32 of   37
  Cached  0 of8  %Hit:  93.26
  Created   195 0.1/s
  Slow0   0/s
 
  __ Aborted
 _
  Clients 0   0/s
  Connects2 0.0/s
 
  __ Bytes
 ___
  Sent  100.33M   66.7k/s
  Received   12.48M8.3k/s

Re: MySQL Indexes

[Michael Dykman]

Only one index at a time can be used per query, so neither strategy is
optimal.  You need at look at the queries you intend to run against the
system and construct indexes which support them.

 - md

On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
neil.tompk...@googlemail.comwrote:

 Maybe that was a bad example.  If the query was name = 'Red' what index
 should I create ?

 Should I create a index of all columns used in each query or have a index
 on individual column ?


 On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote:

 For the first query, the obvious index on score will give you optimal
 results.

 The second query is founded on this phrase: Like '%Red%'  and no index
 will help you there.  This is an anti-pattern, I am afraid.  The only way
 your database can satisfy that expression is to test each and every record
 in the that database (the test itself being expensive as infix finding is
 iterative).  Perhaps you should consider this approach instead:
  http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
 http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

 On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com
 neil.tompk...@googlemail.com wrote:

 Hi,

 Can anyone help and offer some advice with regards MySQL indexes.
  Basically
 we have a number of different tables all of which have the obviously
 primary
 keys.  We then have some queries using JOIN statements that run slowly
 than
 we wanted.  How many indexes are recommended per table ?  For example
 should
 I have a index on all fields that will be used in a WHERE statement ?
  Should the indexes be created with multiple fields ?  A example  of two
 basic queries

 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 ORDER BY score DESC


 SELECT auto_id, name, score
 FROM test_table
 WHERE score  10
 AND name Like '%Red%'
 ORDER BY score DESC

 How many indexes should be created for these two queries ?

 Thanks,
 Neil




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

  May the Source be with you.




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

 May the Source be with you.

Re: Query Optimization

[Michael Dykman]

There are a few things gonig on, but mainly it is the ORs that are killing
you.

As your  require OR to examine two distinct columns, both of equal relevance
to the query, MySQL: is left with no choice but to do a full table scan on
what might be (at a guess) a very larger table.  No amount of indexing will
fix this for the query presented.

You would be better off writing it as two distinct queires, each concerned
with conditions on a single column (open_dt and close_dt) and then UNIONing
the results. In this form, the indexes have a chance of being engaged.

Once the indexes are engaged, you probably want to your DATE/DATETIME
strings into actual DATEs or DATATIMEs, thus:

 ...
   (open_dt = DATE('2011-08-30 00:00:00'))
 ...

In it's current state, the DATE fields are being converted to strings
implicitly for every row tested which further frustrates index usage as the
index is against the quasi-numeric DATE, not the string representation which
your current implementation appears to expect.  This query would also
suddenly begin to fail entirely if the DEFAULT_DATE_FORMAT gets modified by
an admin or a future release of MySQL.  The explicit casting I have
suggested will protect you against that,

 - michael dykman`
On Tue, Aug 30, 2011 at 12:45 PM, Brandon Phelps bphe...@gls.com wrote:

 Hello,

 I have the following query I'd like to optimize a bit:

 SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA( sc.src_address ) AS src_address,
sc.src_port,
INET_NTOA( sc.dst_address ) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
 FROM firewall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
 WHERE
(open_dt = '2011-08-30 00:00:00' OR close_dt = '2011-08-30
 00:00:00')
AND (open_dt = '2011-08-30 12:36:53' OR close_dt = '2011-08-30
 12:36:53')
 ORDER BY rcvd DESC
 LIMIT 0 , 10

 Currently I have an index on the rcvd column which seems to be working
 based on the output of EXPLAIN:

 id  select_type table   typepossible_keys   key key_len
 ref rowsExtra
 1   SIMPLE  sc  index   open_dt ndx_rcvd4
 NULL10  Using where
 1   SIMPLE  spm eq_ref  PRIMARY PRIMARY 2
 syslog.sc.src_port  1
 1   SIMPLE  dpm eq_ref  PRIMARY PRIMARY 2
 syslog.sc.dst_port  1

 However the query is still fairly slow for some reason, any ideas how I
 could speed it up with additional indexes, etc?

 The values I am using in the WHERE clause are variable and change each
 time.

 The table has around 23 million records right now but is expected to
 continue to grow up to a potential 150 million.

 Here is the table schema:
 CREATE TABLE IF NOT EXISTS `firewall_connections` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `open_dt` datetime NOT NULL,
  `close_dt` datetime NOT NULL,
  `protocol` smallint(6) NOT NULL,
  `src_address` int(10) unsigned NOT NULL,
  `src_port` smallint(5) unsigned NOT NULL,
  `dst_address` int(10) unsigned NOT NULL,
  `dst_port` smallint(5) unsigned NOT NULL,
  `sent` int(10) unsigned NOT NULL,
  `rcvd` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ndx_rcvd` (`rcvd`),
  KEY `ndx_sent` (`sent`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 Thanks in advance!

 --
 Brandon

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/mysql?unsub=mdyk...@gmail.com




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

 May the Source be with you.

Re: Arrays

[Michael Dykman]

2011/8/27 Halász Sándor h...@tbbs.net

  2011/08/26 13:58 -0700, javad bakhshi 
  Thanks guys for the help. but my problem seems to stand unsolved.
 
 Right, no arrays. Nothing is left but table. I used a temporary table, but
 note that MySQL also does not let table be returned, or passed in. The
 table-name will be *sigh* global.



I think you missed one key word: temporary as in
  CREATE TEMPORARY TABLE ...

Temporary tables are only visible to the connection that creates them and
they are automatically dropped when that connection closes,. Concurrent
connections can create temporary tables with the exact same name with no
fear of them conflicting or affecting each other's data.  They work like a
charm even in high load, multi-concurrency environments.


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

 May the Source be with you.

Re: a lesson in query writing and (maybe) a bug report

[Michael Dykman]

It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index.  binary columns all suffer from this.

 - michael dykman


On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer ddyer-my...@real-me.net wrote:


 The innocuous change was to add an index for is_robot which is true
 for 6 out of 20,000 records and null for the rest.

 My complaint/question/observation is not how to optimize the query
 that went awry, but to be alarmed that a venerable and perfectly
 serviceable query, written years ago and ignored ever since, suddenly
 brought the system crashing down after making a seemingly innocuous
 change intended to make a marginal improvement on an unrelated query.

 I had previously believed that tinkering the schema by adding indexes was a
 safe activity.  It's as though I add a shortcut to my regular commute
 and caused a massive traffic jam when the entire traffic flow tried to
 follow me.

 (Both tables are ok according to analyze table)


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




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

 May the Source be with you.

Re: Arrays

[Michael Dykman]

The only solution you have:

let your server-side procedure populate a temporary table and select from
that when the procedure is complete.  Depending on the thresholds you set in
your mysql configuration, that memory table might very well reside in RAM
anyhow.  I doubt that speed will be an issue.

 - michael dykman

2011/8/26 javad bakhshi javadbakh...@yahoo.com



  Thanks guys for the help. but my problem seems to stand unsolved.



 
 From: Johan De Meersman vegiv...@tuxera.be
 To: Halász Sándor h...@tbbs.net
 Cc: mysql@lists.mysql.com
 Sent: Friday, August 26, 2011 7:04 PM
 Subject: Re: Arrays

 - Original Message -
  From: Halász Sándor h...@tbbs.net
 
  Hi, I would like to create a function in Mysql that returns an Array
  of Numbers. I am trying to run a big amount of stream of data on
  Mysql and I can't afford the time to store the data into a table and
  retrieve it later. 
  Are there any arrays at all in MySQL?

 Arrays; no. Functions can return simple numeric/string values; I'm not sure
 wether or not you can return a recordset from a stored procedure.


 --
 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=javadbakh...@yahoo.com




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

 May the Source be with you.