Query optimizer-miss with unqualified expressions, bug or feature?

[Ben Clewett]

I have noticed that an unqualified boolean expression cannot be 
optimized by MySQL to use an index in 5.6.24.


For example:

CREATE TABLE t (
  i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a BOOLEAN NOT NULL,
  KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps 
something else?


Thanks,

Ben Clewett.




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

Re: Query optimizer-miss with unqualified expressions, bug or feature?

[Ben Clewett]

Hi Roy,

Thanks for the clear explanation.

I guess (hypothetically) the optimizer could see if it has a key, and 
then use two starts: one on 'a > 0' and one on 'a < 0', taking a union 
of the result?  Which might make a significant result to something?


Ben.


On 2015-10-19 14:19, Roy Lyseng wrote:

Hi Ben,

On 19.10.15 15.10, Ben Clewett wrote:


I have noticed that an unqualified boolean expression cannot be 
optimized by

MySQL to use an index in 5.6.24.

For example:

CREATE TABLE t (
   i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   a BOOLEAN NOT NULL,
   KEY a (a)
) ENGINE=InnoDB;

This will hit key 'a':

SELECT * FROM t WHERE a = TRUE;

This will return the same results, but not use key 'a':

SELECT * FROM t WHERE a;

Is this a bug, or deliberate behaviour, or a missing feature, or perhaps
something else?


MySQL does not have a true boolean type, so this is actually 
interpreted as


  SELECT * FROM t WHERE a <> 0;

The optimizer is not able to see that "a <> 0" means "a = 1", and 
hence no index will be used.


Thanks,
Roy



Thanks,

Ben Clewett.








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

Re: Refresh slave state

[Ben RUBSON]

2015-06-25 14:02 GMT+02:00 wagnerbianchi.com m...@wagnerbianchi.com:
 Some additional information here, just my 2 cents.

 (...)

 Just checking in: using two servers in replication, idle servers, on the
 slave side I configured globally the slave_net_timeout=1 and log_warnings=2,
 as I'm using 5.6 for these tests. The interest here is to check the
 reconnection made by the slave and with that, the restart of Binlog Dump
 Thread on the master. Looking at the MySQL Error log...

 #: slave error log - reported every 5 secs
 2015-06-25 11:38:21 2598 [Warning] Storing MySQL user name or password
 information in the master info repository is not secure and is therefore not
 recommended. Please consider using the USER and PASSWORD connection options
 for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
 information.
 2015-06-25 11:38:26 2598 [Warning] Storing MySQL user name or password
 information in the master info repository is not secure and is therefore not
 recommended. Please consider using the USER and PASSWORD connection options
 for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
 information.
 2015-06-25 11:38:31 2598 [Warning] Storing MySQL user name or password
 information in the master info repository is not secure and is therefore not
 recommended. Please consider using the USER and PASSWORD connection options
 for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more
 information.

 Here we can see clearly that the slave is reconnecting every sec, since
 those messages appearing on the error log are showing us the same behavior
 that happens when one issue a START SLAVE (with no user and password and
 even SSL) using e.g. the mysql client.

 #: master side error log - reported every 3 secs
 2015-06-25 11:41:05 2648 [Note] Start binlog_dump to master_thread_id(120)
 slave_server(256380), pos(mysql-bin.02, 120)
 2015-06-25 11:41:08 2648 [Note] Start binlog_dump to master_thread_id(121)
 slave_server(256380), pos(mysql-bin.02, 120)
 2015-06-25 11:41:11 2648 [Note] Start binlog_dump to master_thread_id(122)
 slave_server(256380), pos(mysql-bin.02, 120)

 We can see that the master Binlog Dump Thread is re-initialized as well when
 the Slave I/O Thread reconnects.

 BTW, SHOW PROCESSLIST delays at least 10 seconds to report that a slave has
 died when we can see a new connection is made observing the increment of the
 thread id.

 (...)

 Even having the SLAVE reconnecting on every second, the slave error log
 reports that reconnection every 5 secs, the SHOW PROCESSLIST reports a new
 thread id every 10 secs, the master report the start of Binlog Dump Thread
 on every 3 secs.

 From here, we need to investigate more...


Wagner, thank you very much for your experience.

And when you make your network link to die (remove cable / ifconfig
down / iptables...) between your master and your slave, how long does
your Binlog Dump process stay up ?



 2015-06-25 2:48 GMT-03:00 Ben RUBSON ben.rub...@gmail.com:

 2015-06-22 13:45 GMT+02:00 Ben RUBSON ben.rub...@gmail.com:

  2015-06-19 12:08 GMT+02:00 Ben RUBSON ben.rub...@gmail.com:
 
  2015-06-18 22:52 GMT+02:00 shawn l.green shawn.l.gr...@oracle.com:
 
  On 6/18/2015 2:10 PM, Ben RUBSON wrote:
 
  Hello,
 
  In order for the slave to quickly show a communication issue between
  the master and the slave, I set slave_net_timeout to 10.
  show slave status then quickly updates, perfect.
 
  I would also like the master to quickly show when the slave is no
  more
  reachable.
 
  However, show processlist and show slave hosts take a very long
  time to update their status when the slave has gone.
  Is there any way to have a refresh rate of about 10 seconds, as I did
  on slave side ?
 
  There are two situations to consider
 
  1) The slave is busy re-trying.  It will do this a number of times
  then
  eventually disconnect itself. If it does disconnect itself, the
  processlist
  report will show it as soon as that happens.
 
  Yes, I confirm.
 
  2) The connection between the master and slave died (or the slave
  itself is
  lost).  In this case, the server did not receive any I am going to
  disconnect message from its client (the slave). So as far as the
  server is
  concerned, it is simply sitting in a wait expecting the client to
  eventually
  send in a new command packet.
 
  That wait is controlled by --wait-timeout.  Once an idle client
  connection
  hits that limit, the server is programmed to think the idiot on the
  other
  end of this call has hung up on me so it simply closes its end of the
  socket. There are actually two different timers that could be used,
  --wait-timeout or --interactive-timeout and which one is used to
  monitor the
  idle socket depends entirely on if the client did or did not set the
  'interactive flag' when it formed the connection. MySQL slaves do not
  use
  that flag.
 
  Now, if the line between the two systems died in the middle

Re: Refresh slave state

[Ben RUBSON]

2015-06-22 13:45 GMT+02:00 Ben RUBSON ben.rub...@gmail.com:

 2015-06-19 12:08 GMT+02:00 Ben RUBSON ben.rub...@gmail.com:
 
 2015-06-18 22:52 GMT+02:00 shawn l.green shawn.l.gr...@oracle.com:
 
 On 6/18/2015 2:10 PM, Ben RUBSON wrote:
 
 Hello,
 
 In order for the slave to quickly show a communication issue between
 the master and the slave, I set slave_net_timeout to 10.
 show slave status then quickly updates, perfect.
 
 I would also like the master to quickly show when the slave is no more
 reachable.
 
 However, show processlist and show slave hosts take a very long
 time to update their status when the slave has gone.
 Is there any way to have a refresh rate of about 10 seconds, as I did
 on slave side ?
 
 There are two situations to consider
 
 1) The slave is busy re-trying.  It will do this a number of times then
 eventually disconnect itself. If it does disconnect itself, the processlist
 report will show it as soon as that happens.
 
 Yes, I confirm.
 
 2) The connection between the master and slave died (or the slave itself is
 lost).  In this case, the server did not receive any I am going to
 disconnect message from its client (the slave). So as far as the server is
 concerned, it is simply sitting in a wait expecting the client to eventually
 send in a new command packet.
 
 That wait is controlled by --wait-timeout.  Once an idle client connection
 hits that limit, the server is programmed to think the idiot on the other
 end of this call has hung up on me so it simply closes its end of the
 socket. There are actually two different timers that could be used,
 --wait-timeout or --interactive-timeout and which one is used to monitor the
 idle socket depends entirely on if the client did or did not set the
 'interactive flag' when it formed the connection. MySQL slaves do not use
 that flag.
 
 Now, if the line between the two systems died in the middle of a
 conversation (an actual data transfer) then a shorter -net-write-timeout or
 --net-read-timeout would expire and the session would die then.
 
 This is the interesting part yes, when the connection dies (whatever
 the link status is at this moment, idle or not).
 So I set wait_timeout=10.
 
 When the link is up, we clearly see that the idle connection is reset
 every 10 seconds : the show processlist clearly shows that the slave
 TCP source port changes, and time is reset from 10 to 0.
 Perfect.
 
 Well this behavior is due to slave_net_timeout, not to wait_timeout.
 So neither wait_timeout nor interactive_timeout (expected),
 net_read_timeout, net_write_timeout helped.
 
 However, when the link dies, the Binlog Dump process stays in the
 show processlist, I have to wait more than 1000 seconds for it to
 disappear.
 I made tests adding interactive_timeout=10, net_read_timeout=10 and
 net_write_timeout=10, however the behavior is the same.
 
 Did I miss something ?
 
 Of course goal is to monitor replication, from the slave (done and
 working thanks to slave_net_timeout), but from the master too (some
 more tuning needed), as we never know which one will be able to
 transmit the alert properly.
 
 Thank you very much Shawn.

Hello,

Would you have any further advice on this topic please ?

Thank you again,

Best regards,

Ben


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

Re: Refresh slave state

[Ben RUBSON]

2015-06-19 12:08 GMT+02:00 Ben RUBSON ben.rub...@gmail.com:

 2015-06-18 22:52 GMT+02:00 shawn l.green shawn.l.gr...@oracle.com:

 On 6/18/2015 2:10 PM, Ben RUBSON wrote:

 Hello,

 In order for the slave to quickly show a communication issue between
 the master and the slave, I set slave_net_timeout to 10.
 show slave status then quickly updates, perfect.

 I would also like the master to quickly show when the slave is no more
 reachable.

 However, show processlist and show slave hosts take a very long
 time to update their status when the slave has gone.
 Is there any way to have a refresh rate of about 10 seconds, as I did
 on slave side ?

 There are two situations to consider

 1) The slave is busy re-trying.  It will do this a number of times then
 eventually disconnect itself. If it does disconnect itself, the processlist
 report will show it as soon as that happens.

 Yes, I confirm.

 2) The connection between the master and slave died (or the slave itself is
 lost).  In this case, the server did not receive any I am going to
 disconnect message from its client (the slave). So as far as the server is
 concerned, it is simply sitting in a wait expecting the client to eventually
 send in a new command packet.

 That wait is controlled by --wait-timeout.  Once an idle client connection
 hits that limit, the server is programmed to think the idiot on the other
 end of this call has hung up on me so it simply closes its end of the
 socket. There are actually two different timers that could be used,
 --wait-timeout or --interactive-timeout and which one is used to monitor the
 idle socket depends entirely on if the client did or did not set the
 'interactive flag' when it formed the connection. MySQL slaves do not use
 that flag.

 Now, if the line between the two systems died in the middle of a
 conversation (an actual data transfer) then a shorter -net-write-timeout or
 --net-read-timeout would expire and the session would die then.

 This is the interesting part yes, when the connection dies (whatever
 the link status is at this moment, idle or not).
 So I set wait_timeout=10.

 When the link is up, we clearly see that the idle connection is reset
 every 10 seconds : the show processlist clearly shows that the slave
 TCP source port changes, and time is reset from 10 to 0.
 Perfect.

Well this behavior is due to slave_net_timeout, not to wait_timeout.
So neither wait_timeout nor interactive_timeout (expected),
net_read_timeout, net_write_timeout helped.

 However, when the link dies, the Binlog Dump process stays in the
 show processlist, I have to wait more than 1000 seconds for it to
 disappear.
 I made tests adding interactive_timeout=10, net_read_timeout=10 and
 net_write_timeout=10, however the behavior is the same.

 Did I miss something ?

 Of course goal is to monitor replication, from the slave (done and
 working thanks to slave_net_timeout), but from the master too (some
 more tuning needed), as we never know which one will be able to
 transmit the alert properly.

 Thank you very much Shawn.

Thank you again,

Best regards,

Ben

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

Re: Refresh slave state

[Ben RUBSON]

2015-06-18 22:52 GMT+02:00 shawn l.green shawn.l.gr...@oracle.com:

 On 6/18/2015 2:10 PM, Ben RUBSON wrote:

 Hello,

 In order for the slave to quickly show a communication issue between
 the master and the slave, I set slave_net_timeout to 10.
 show slave status then quickly updates, perfect.

 I would also like the master to quickly show when the slave is no more
 reachable.

 However, show processlist and show slave hosts take a very long
 time to update their status when the slave has gone.
 Is there any way to have a refresh rate of about 10 seconds, as I did
 on slave side ?

 There are two situations to consider

 1) The slave is busy re-trying.  It will do this a number of times then
 eventually disconnect itself. If it does disconnect itself, the processlist
 report will show it as soon as that happens.

Yes, I confirm.

 2) The connection between the master and slave died (or the slave itself is
 lost).  In this case, the server did not receive any I am going to
 disconnect message from its client (the slave). So as far as the server is
 concerned, it is simply sitting in a wait expecting the client to eventually
 send in a new command packet.

 That wait is controlled by --wait-timeout.  Once an idle client connection
 hits that limit, the server is programmed to think the idiot on the other
 end of this call has hung up on me so it simply closes its end of the
 socket. There are actually two different timers that could be used,
 --wait-timeout or --interactive-timeout and which one is used to monitor the
 idle socket depends entirely on if the client did or did not set the
 'interactive flag' when it formed the connection. MySQL slaves do not use
 that flag.

 Now, if the line between the two systems died in the middle of a
 conversation (an actual data transfer) then a shorter -net-write-timeout or
 --net-read-timeout would expire and the session would die then.

This is the interesting part yes, when the connection dies (whatever
the link status is at this moment, idle or not).
So I set wait_timeout=10.

When the link is up, we clearly see that the idle connection is reset
every 10 seconds : the show processlist clearly shows that the slave
TCP source port changes, and time is reset from 10 to 0.
Perfect.

However, when the link dies, the Binlog Dump process stays in the
show processlist, I have to wait more than 1000 seconds for it to
disappear.
I made tests adding interactive_timeout=10, net_read_timeout=10 and
net_write_timeout=10, however the behavior is the same.

Did I miss something ?

Of course goal is to monitor replication, from the slave (done and
working thanks to slave_net_timeout), but from the master too (some
more tuning needed), as we never know which one will be able to
transmit the alert properly.

Thank you very much Shawn.

Best regards,

Ben

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

Refresh slave state

[Ben RUBSON]

Hello,

In order for the slave to quickly show a communication issue between
the master and the slave, I set slave_net_timeout to 10.
show slave status then quickly updates, perfect.

I would also like the master to quickly show when the slave is no more
reachable.

However, show processlist and show slave hosts take a very long
time to update their status when the slave has gone.
Is there any way to have a refresh rate of about 10 seconds, as I did
on slave side ?

Thank you !

Ben

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

Core Dump

[Ben Clewett]

Dear MySQL,

Using 5.1.56, I have experienced this core dump.  Is there anybody out 
there qualified to give an opinion on this?


Many thanks,

Ben Clewett.

Thread pointer: 0x7fd5280dbd90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7fd51edf8100 thread_stack 0x4
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8b591e]
/usr/sbin/mysqld(handle_segfault+0x351)[0x5e4ca1]
/lib64/libpthread.so.0[0x7fda95f35a90]
/usr/sbin/mysqld(my_hash_sort_simple+0x3d)[0x8c8a5d]
/usr/sbin/mysqld(hp_hashnr+0x20a)[0x77b16a]
/usr/sbin/mysqld(hp_search+0x66)[0x77c386]
/usr/sbin/mysqld(heap_rnext+0x12f)[0x77eb9f]
/usr/sbin/mysqld(_ZN7ha_heap10index_nextEPh+0x2d)[0x779fcd]
/usr/sbin/mysqld(_ZN7handler15index_next_sameEPhPKhj+0x3a)[0x6d5e5a]
/usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_range+0x2f)[0x6d50af]
/usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x14e)[0x6b73ae]
/usr/sbin/mysqld[0x6d1246]
/usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyyb+0x904)[0x67a994]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x36f7)[0x5f8a07]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjPPKc+0x3d0)[0x5faec0]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x7b6)[0x5fb686]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe6)[0x5fc716]
/usr/sbin/mysqld(handle_one_connection+0x246)[0x5eed26]
/lib64/libpthread.so.0[0x7fda95f2e070]
/lib64/libc.so.6(clone+0x6d)[0x7fda953f213d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7fd521a122a0): is an invalid pointer
Connection ID (thread ID): 47159998
Status: NOT_KILLED


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

Re: Basic SELECT help

[Ben Mildren]

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

On 22 November 2012 15:01, 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 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



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

Re: Basic SELECT help

[Ben Mildren]

Ah read it quickly and misread your requirement.  Joins are likely FTW
here.  The alternative would be to do something like this, but I'd opt
for the joins if you have a reasonably sized data set:

SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';

On 22 November 2012 15:10, Ben Mildren ben.mild...@gmail.com wrote:
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

 On 22 November 2012 15:01, 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 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



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

Re: Basic SELECT help

[Ben Mildren]

*HAVING typelist = 'x,y,z';

On 22 November 2012 15:25, Ben Mildren ben.mild...@gmail.com wrote:
 Ah read it quickly and misread your requirement.  Joins are likely FTW
 here.  The alternative would be to do something like this, but I'd opt
 for the joins if you have a reasonably sized data set:

 SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
 WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z';

 On 22 November 2012 15:10, Ben Mildren ben.mild...@gmail.com wrote:
 SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;

 On 22 November 2012 15:01, 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 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



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

Re: Query Cache Crashing

[Ben Clewett]

Hi Rick,

Thanks for the advise.  I have now set my query-cache to zero.

(I take your point about query cache too large.  I understand that a 
smaller cache size, and the use of the SQL_NO_CACHE and SQL_CACHE 
directives can be used to control which queries are cached.  Therefore 
trying to get the advantage without the disadvantage.  But this is a lot 
of work to change every query we ever run!)


I am working on reproducing the error, but so far it appears to be random.

Ben



On 2012-10-09 18:44, Rick James wrote:

As for the crash, I don't know.  Instead, I recommend either shrinking the size 
(if you use a big QC) or turning it off.  This would make the issue go away.

ANY modification to a particular table leads to ALL entries in the Query cache 
being purged.  For that reason, we (Yahoo) almost never use the QC on any of 
our many servers.

Please provide
SHOW GLOBAL VARIABLES LIKE 'query%';
SHOW GLOBAL STATUS LIKE 'Qc%';

Because of inefficiencies in 'pruning', having a query_cache_size bigger than 
50M may actually degrade performance.

In you have a reproducible test case, submit to bugs.mysql.com .



-Original Message-
From: Ben Clewett [mailto:b...@clewett.org.uk]
Sent: Tuesday, October 09, 2012 4:47 AM
To: mysql@lists.mysql.com
Subject: Query Cache Crashing

Hi MySql,

Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be
exact) I have experienced problems with the query cache.  I am
wondering if I am the only one?

Twice I have had a core-dump (show at end of mail) on two separate
servers running this version.

Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for
every connection:

Waiting for query cache lock

This resulted in connections building until the limit was hit.  I could
only cure this with a 'kill -9' on the mysqld process.

Are any other users experiencing this?  Do any users know of a fix?

Thanks!

Ben Clewett.


Thread pointer: 0x7f6ea014cf90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 410220e8 thread_stack 0x4
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5]
/usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383]
/lib64/libpthread.so.0[0x7f6f491b2b30]
/usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_ta
ble+0x1b)[0x5bd1ab]

/usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_b
lock+0x7a)[0x5bdb5a]

/usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23
Query_cache_block_table+0x7e)[0x5be8ee]

/usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block
_tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1]

/usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_L
ISTjP23Query_cache_block_table+0x176)[0x5bfa76]

/usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_b
lockP10TABLE_LISTj+0x15)[0x5bfbb5]

/usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e
)[0x5bff5e]

/usr/sbin/mysqld[0x5eb02d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba
]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13
21)[0x5f4861]

/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807]
/usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874]
/lib64/libpthread.so.0[0x7f6f491ab040]
/lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d]

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

Query Cache Crashing

[Ben Clewett]

Hi MySql,

Since upgrading to 5.5.27 (5.5.27-ndb-7.2.8-cluster-gpl-log to be exact) 
I have experienced problems with the query cache.  I am wondering if I 
am the only one?


Twice I have had a core-dump (show at end of mail) on two separate 
servers running this version.


Now I had a complete lock, where 'SHOW PROCESSLIST' showed this for 
every connection:


Waiting for query cache lock

This resulted in connections building until the limit was hit.  I could 
only cure this with a 'kill -9' on the mysqld process.


Are any other users experiencing this?  Do any users know of a fix?

Thanks!

Ben Clewett.


Thread pointer: 0x7f6ea014cf90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 410220e8 thread_stack 0x4
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x81c7f5]
/usr/sbin/mysqld(handle_fatal_signal+0x403)[0x6e9383]
/lib64/libpthread.so.0[0x7f6f491b2b30]
/usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_table+0x1b)[0x5bd1ab] 

/usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_block+0x7a)[0x5bdb5a] 

/usr/sbin/mysqld(_ZN11Query_cache27invalidate_query_block_listEP3THDP23Query_cache_block_table+0x7e)[0x5be8ee] 

/usr/sbin/mysqld(_ZN11Query_cache12insert_tableEjPcP23Query_cache_block_tablejhPFcP3THDS0_jPyEy+0x91)[0x5bf7e1] 

/usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_LISTjP23Query_cache_block_table+0x176)[0x5bfa76] 

/usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_blockP10TABLE_LISTj+0x15)[0x5bfbb5] 

/usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x39e)[0x5bff5e] 


/usr/sbin/mysqld[0x5eb02d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x269b)[0x5f0aeb]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x18a)[0x5f2cba]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1321)[0x5f4861] 


/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x68d807]
/usr/sbin/mysqld(handle_one_connection+0x54)[0x68d874]
/lib64/libpthread.so.0[0x7f6f491ab040]
/lib64/libc.so.6(clone+0x6d)[0x7f6f4847c08d]

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

[Ben Mildren]

Also ensure you issue: FLUSH PRIVILEGES; after making any changes to
permissions..

On 24 September 2012 20:09, Rick James rja...@yahoo-inc.com wrote:
 That says that your password in not correct.  You have not gotten to specific 
 privileges.

 Did you previously do
 GRANT ... TO myuser@localhost IDENTFIED BY '...';
 ?

 If you can get in via root, do
 SHOW GRANTS FOR myuser@localhost;
 SELECT * FROM mysql.user WHERE user = 'myuser';

 -Original Message-
 From: Rajeev Prasad [mailto:rp.ne...@yahoo.com]
 Sent: Monday, September 24, 2012 10:56 AM
 To: mysql list
 Subject: user not able to login from localhost

 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?

 --
 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: Search based where claused and stored proc

[Ben Wiechman]

Ben Wiechman
Network Administrator
Wisper High Speed Internet
Office: 866.394.7737
Direct: 320.256.0184
Cell: 320.247.3224
b...@wisper-wireless.com



 -Original Message-
 From: Gary Smith [mailto:g...@primeexalia.com]
 Sent: Friday, March 27, 2009 12:59 PM
 To: mysql@lists.mysql.com
 Subject: [MySQL] Search based where claused and stored proc
 
 I'm working on a small project of re-implementing all of the sql for a
 web site.  The task is pretty trivial but overall there are some minor
 things that I'm trying to code through.
 
 We've moved much of the logic over to stored procs and call them with
 parameterized queries.  This works well since there isn't much inject
 attack possibility on these.  Now I have one query left, which allows
 for an arbitrary number of search parameters, all using AND.
 
 Has anyone accomplished coverting something like this to a stored proc
 in mysql?
 
 Logically I could pass in the parameters in as an array of words, or a
 wordlist to be broken up inside the proc, but I don't want to spend a
 bunch of time either reinventing the wheel or working to a goal that
 can't be accomplished.
 
 We could build the base query dynamically in the code using standard sql
 and bind the parameters to it that way but since we've moved everything
 else to procs I figured I'd look into this as well.
 
 BTW, this is a project I brought onto after they found they had a sql
 injection bug in there code that was exploited...
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=b...@meltel.com
 
 




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

Query Help

[Ben Wiechman]

I keep hacking at this but haven't been able to get it right yet. 

 

I have two tables

Userinfo contains a login, User's Name, Group Name

Log contains login, host, datetime of last login

 

 

What I need to do is return user information (userinfo.name/groupname) of
users that have logged into a particular host last. i.e. A user can log into
any of the hosts but I want to know which one they were on last. Each time
they log the username, host and datetime of login are recorded in the log
table. I've been able to return the last login to the host but I'm at a loss
to get the list of all users that used the host last or all users that have
ever logged into that host.  

 

 

RE: Query Help

[Ben Wiechman]

Thanks for the input! That is close to what I need, however not exactly. It
will give me the last time a user logged into the host in question but I
want to prune users who have since logged into a different host. Basically
find out how many users are logged into a given host or who are not
currently logged in but have not logged into a different host since they
logged out of the target. 

For perspective the host is a wireless access point with fixed clients. A
host that hasn't logged in somewhere else we can (somewhat) safely assume is
still pointed at the AP in question but is power off, or has its connection
interrupted for some other reason. If they have logged in somewhere else we
assume that the hardware was moved to a new location and installed there. 

The query I came up with for some reason doesn't seem to correctly order the
dates so if they have logged into the host in question the information is
returned. This produces too many results as some of those users have since
migrated to a different access point.

-Original Message-
From: Andrew Wallo [mailto:theme...@microneil.com] 
Sent: Tuesday, February 10, 2009 12:05 PM
To: Ben Wiechman
Subject: Re: Query Help


Select login, SQL MAX(DateTime) from Log groupby Log.Login ( Gives you the 
most recent login for a user - on any host. )

Select login, SQLMax(DateTime) from Log groupby Log.Login, Host ORDER BY 
HOST ASC, DATETIME DESC

(Should give you the largest, i.e. most recent, date for each user on each 
host, oganized by host, in descending login order. I think... )



- Original Message - 
From: Ben Wiechman b...@meltel.com
To: mysql@lists.mysql.com
Sent: Tuesday, February 10, 2009 12:31 PM
Subject: Query Help


I keep hacking at this but haven't been able to get it right yet.



 I have two tables

 Userinfo contains a login, User's Name, Group Name

 Log contains login, host, datetime of last login





 What I need to do is return user information (userinfo.name/groupname) of
 users that have logged into a particular host last. i.e. A user can log 
 into
 any of the hosts but I want to know which one they were on last. Each time
 they log the username, host and datetime of login are recorded in the log
 table. I've been able to return the last login to the host but I'm at a 
 loss
 to get the list of all users that used the host last or all users that 
 have
 ever logged into that host.





 





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

RE: Query Help

[Ben Wiechman]

Alright to reply to myself I can return the information but have been unable
to return the last row... instead it always returns the first row. Tried
max, tried min, tried converting the datetime to a timestamp with the same
results... 

mysql SELECT da_userinfo.UserName, da_userinfo.Name, radacct.AcctStartTime
as LoginTime, radacct.AcctStopTime as LogoutTime
- FROM radacct, da_userinfo
- WHERE da_userinfo.Username = radacct.Username
- AND `NASIPAddress` = '172.17.6.100'
- GROUP BY radacct.`UserName`
- HAVING MAX( radacct.`AcctStartTime` );
+--+-+-+
-+
| UserName | Name| LoginTime   | LogoutTime
|
+--+-+-+
-+
| 0010E70A8004 | User1   | 2009-02-09 09:16:24 | 2009-02-10
04:42:08 |
| 0010E70A80A8 | User2  | 2009-02-09 14:31:20 |
2009-02-10 09:57:42 |
| 0010E70A812D | User3 | 2009-02-09 13:19:51 |
2009-02-10 07:43:08 |
| 0010E70A8336 | User4 | 2009-02-05 14:10:41 |
2009-02-10 02:36:41 |
| 0010E70A833B | User5  | 2009-02-06 17:45:15 |
2009-02-09 20:27:21 |
| 0010E72A2258 | User6| 2009-01-29 13:40:42 | 2009-01-29
14:03:29 |
| 0010E72A2957 | User7  | 2009-02-09 15:16:06 | 2009-02-09
16:02:56 |
| 0010E72AA91C | User8 | 2009-02-03 09:45:36 | 2009-02-10
07:41:22 |
| 0010E7C2F6AF | 07B | 2009-01-28 16:13:44 | 2009-01-28
16:15:43 |
+--+-+-+
-+
16 rows in set (0.00 sec)

mysql select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004';
+--+--+-+-+
| UserName | Host | LoginTime   | LogoutTime  |
+--+--+-+-+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:02 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:12 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:18:30 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:39:21 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:44:42 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:12:34 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:14:05 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:18:51 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-10 04:42:08 | -00-00 00:00:00 |
+--+--+-+-+
10 rows in set (0.00 sec)

mysql select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004' HAVING MAX( AcctStartTime );
+--+--+-+-+
| UserName | Host | LoginTime   | LogoutTime  |
+--+--+-+-+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
+--+--+-+-+
1 row in set (0.00 sec)

-Original Message-
From: Ben Wiechman [mailto:b...@meltel.com] 
Sent: Tuesday, February 10, 2009 11:32 AM
To: mysql@lists.mysql.com
Subject: Query Help

I keep hacking at this but haven't been able to get it right yet. 

 

I have two tables

Userinfo contains a login, User's Name, Group Name

Log contains login, host, datetime of last login

 

 

What I need to do is return user information (userinfo.name/groupname) of
users that have logged into a particular host last. i.e. A user can log into
any of the hosts but I want to know which one they were on last. Each time
they log the username, host and datetime of login are recorded in the log
table. I've been able to return the last login to the host but I'm at a loss
to get the list of all users that used the host last or all users that have
ever logged into that host.  

 

 




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

RE: Query Help

[Ben Wiechman]

Awesome... that works. Had to add a where clause to limit it to a specific
host.

The explain for that looks... interesting. 

Thanks

-Original Message-
From: ddevaudre...@intellicare.com [mailto:ddevaudre...@intellicare.com] 
Sent: Tuesday, February 10, 2009 12:47 PM
To: Ben Wiechman
Cc: mysql@lists.mysql.com
Subject: RE: Query Help


Ben Wiechman b...@meltel.com wrote on 02/10/2009 01:30:14 PM:

 Thanks for the input! That is close to what I need, however not exactly.
It
 will give me the last time a user logged into the host in question but I
 want to prune users who have since logged into a different host.
Basically
 find out how many users are logged into a given host or who are not
 currently logged in but have not logged into a different host since they
 logged out of the target.

Figure out the last time each user logged in to any host:

SELECT login, MAX(datetime)as lastlogindate
FROM Log
GROUP BY login

So use that query as a derived table to get the rest of the info (untested
SQL):

SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime
FROM Userinfo
INNER JOIN
  (SELECT login, MAX(datetime)as lastlogindate
  FROM Log
  GROUP BY login) AS lastlogin
  ON Userinfo.login=lastlogin.login
INNER JOIN Log  ON lastlogin.login=Log.login AND
lastlogin.lastlogindate=Log.datetime

Hope that helps.

Donna





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

Question of Relationship between tables

[Ben A.H.]

Hello,

I'm having conceptualizing the correct relationship for what seems a very 
simple scenario:

Scenario:
I have a standard USERS table...
USERS have a list of FRIENDS, these can be other members or also non 
members... Similar to facebook...

My main issue is conceptualizing the relationship for member to member 
contacts.

TABLES:
USER:
emailID (PK)
userName
Password
Address
Etc

FRIEND:
emailID (PK)
friendEmailID (PK)

RELATIONSHIPS:

USER.emailID (1) --- FRIEND.emailID (many)
USER.emailID (many) --- FRIEND.friendEmailID (1)

Does this work or is this a cyclical many-to-many relationship? (1 User can 
have many friends, 1 friend can belong to many users)... If so, what's the 
correct (normalized) way of representing this? 



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

Foreign Keys

[Ben A.H.]

Does using foreign keys simply enforce referential integrity OR can it also 
speed up JOIN queries? 



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

Re: Foreign Keys

[Ben A.H.]

I know that this is the standard means of dealing with a  many to many 
relationship, I'm just not sure it fits here.



 USER:

emailID (PK)

userName

Password

Address

Etc



 FRIEND:

emailID (PK)



 USER_FRIEND

user_emailID (PK)

friend_emailID (PK)



So if I want a list of USER [EMAIL PROTECTED]'s friends:

SELECT friend_emailID from USER_FRIEND

WHERE user_emailID=[EMAIL PROTECTED]



In this (and pretty much every case), the FRIEND table is useless and 
doesn't make sense logically.



*I THINK I EXPLAINED THINGS INCORRECTLY*

Let me try again:

* I'm not sure if it's even a true many to many relationship as this is 
actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD 
in THE SAME TABLE!



A USER is:



USER

  emailID

  userName



A friend is really just another RECORD in the USER table.



i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE:



USER:

  emailID

  userName

 |1

 |many

FRIEND:

  emailID

 |many

 |1

USER:

  emailID

  username



(i.e. it's two records in the same USER table)



How are relationships between records in the same table usually dealt with 
in terms of design? Implementation?



ThanX,



Ben

Jim Lyons [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
 Indexes speed up joins.  Foreign keys should be indexes themselves, so 
 they
 can also speed up joins.  If the FK is not an index, it won't help.  So,
 index your FKs

 On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote:

 Does using foreign keys simply enforce referential integrity OR can it 
 also
 speed up JOIN queries?



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




 -- 
 Jim Lyons
 Web developer / Database administrator
 http://www.weblyons.com
 



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

RE: Foreign Keys

[Ben A. Hilleli]

So you are talking about parent-child relationships in a single table, or
in the technical jargon 

reflexive relationships. See www.artfulsoftware.com
http://www.artfulsoftware.com/  for detailed examples of how this is done,


but here is the thumbnail sketch:

--

Actually a Parent-childREN relationship. 

A USER can have 0 to many friends. 

This person can also BE THE friend of 0 to many other USERS.

 

AKA: I (A user) can have many friends (other users), I can be the friend
(user) of many users



The table has to have a ParentID column (call it what you want) that
points, in the case of a 

Friend, to the Friend Of Whom UserID column

--

Not sure what you mean as aren't you describing a 1 to 1 relation?

 

I'll check the site mentioned, thank-you so much!

 


Ben A. Hilleli

Programmer / Analyst






 

  _  

From: Arthur Fuller [mailto:[EMAIL PROTECTED] 
Sent: October 8, 2008 2:55 PM
To: Ben A.H.
Cc: mysql@lists.mysql.com
Subject: Re: Foreign Keys

 

So you are talking about parent-child relationships in a single table, or in
the technical jargon reflexive relationships. See www.artfulsoftware.com
http://www.artfulsoftware.com/  for detailed examples of how this is done,
but here is the thumbnail sketch:

 

The table has to have a ParentID column (call it what you want) that points,
in the case of a Friend, to the Friend Of Whom UserID column. 

 

This simple statement avoids the complexity of the situation where a Friend
can have Friends and so on. That problem requires detailed explanation,
which you can obtain from the site mentioned above. See also Joe Celko's
books.

 

Arthur

On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] wrote:

I know that this is the standard means of dealing with a  many to many
relationship, I'm just not sure it fits here.



 USER:

emailID (PK)

userName

Password

Address

Etc



 FRIEND:

emailID (PK)



 USER_FRIEND

user_emailID (PK)

friend_emailID (PK)



So if I want a list of USER [EMAIL PROTECTED]'s friends:

SELECT friend_emailID from USER_FRIEND

WHERE user_emailID=[EMAIL PROTECTED]



In this (and pretty much every case), the FRIEND table is useless and
doesn't make sense logically.



*I THINK I EXPLAINED THINGS INCORRECTLY*

Let me try again:

* I'm not sure if it's even a true many to many relationship as this is
actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD
in THE SAME TABLE!



A USER is:



USER

 emailID

 userName



A friend is really just another RECORD in the USER table.



i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE:



USER:

 emailID

 userName

|1

|many

FRIEND:

 emailID

|many

|1

USER:

 emailID

 username



(i.e. it's two records in the same USER table)



How are relationships between records in the same table usually dealt with
in terms of design? Implementation?



ThanX,



Ben

Jim Lyons [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]

 Indexes speed up joins.  Foreign keys should be indexes themselves, so
 they
 can also speed up joins.  If the FK is not an index, it won't help.  So,
 index your FKs

 On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote:

 Does using foreign keys simply enforce referential integrity OR can it
 also
 speed up JOIN queries?



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




 --
 Jim Lyons
 Web developer / Database administrator
 http://www.weblyons.com http://www.weblyons.com/ 





--

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

To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

 

Re: Relational Databasing on busy webserver

[Ben A.H.]

 It sounds like what you're looking for is an ENUM value:

 http://dev.mysql.com/doc/refman/5.0/en/enum.html

 Bear in mind when using this data-type that if you do want to add a new
 value (such as a new state/country), you will have to perform an ALTER
 TABLE statement, which can take some time to execute over a table
 storing a lot of data. Using the relational method means you'd just have
 to add one row to a table, which is significantly faster.

Just to clarify:
Wouldn't the ALTER TABLE though just have to be run once? and on the 
backend?
I guess what I'm asking is: would this affect web-users in any way?







 Thanks

 On Mon, 2008-09-22 at 17:12 -0400, Ben A.H. wrote:
 Hello,

 We are setting up a relatively common web application which collects 
 user
 information... Right off the bat our system will have over 200,000 USER
 RECORDS so having an efficient database  lookup scheme is critical.
 I am a programmer/developer with some education in databasing but my 
 forte
 is programming hence this odd (I think) question...

 Obviously we'll have the standard USER table  a bunch of supporting 
 tables.

 For items like STATE/PROVINCE standard database logic would dictate I 
 setup:

 USER
name
email
.etc...
StateID  (foreign key)


 STATE
StateID
StateName

 But I often wonder if there's any benefit in having a State table...

 For one, new States/Provinces are not that likely, removal of these 
 entities
 is also unlikely (except maybe Quebec :-)) so the chances of having to 
 make
 alternations to the State table near nil. It raises the question of 
 whether
 or not a State Table is even necessary.
 Using a traditional SQL State table, I'd have to do an SQL query to 
 populate
 User State/Province options every time the New User Registration form 
 is
 shown - isn't this needless overhead?!
 Would my webforms not load faster if State/Province information was
 hard-coded as options for a combobox? Is this something people have
 experimented with?

 There are various other fields that I believe could be handled like this 
 for
 a cumulative performance boost. For example: country, state/province,
 gender, industry, occupation, ethnicity, language are all options that
 aren't going to change that often. Then again, when we do have to display
 the users choice; for example if user.countryID=3 we'd have to have a way 
 to
 turn that number 3 into the word Canada when we display the users
 profile... I'd probably do this via XML lookup.

 Has anyone experimented with the benefits/tradeoffs of such a scheme? As
 I've said, we are dealing with at least 200,000 user records, probably
 300,000-400,000 in the next year. The User table contains at least 50
 attributes, 15 of which are Foreign Keys that link to tables that will
 likely never change (the users choices for countryID, stateID,
 OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID,
 industryID, occupationID)...

 ThanX in advance
 Ben



 -- 
 Ian Simpson
 System Administrator
 MyJobGroup

 



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

Re: Relational Databasing on busy webserver

[Ben A.H.]

Hello all,

Thank-you for all of your help, I was really surprised by the speed  
quality of responses. Below is a table I've created based on some reading I 
did following everyone's suggestions (I hope the table shows correctly)... 
I'm leaning towards the pure Relational as I like having all data in a 
singular, easily conceptualized  exported datastore but we may go with the 
ENUM approach if it provides a performance boost. Feel free to comment...


 Relational
 Enum
 Scripted

  Maintainability
 +

  (Simplest, known in industry, other programmers can easily 
understand/maintain)
 +

  (Almost as simple as Relational, known in industry, other programmers 
can easily understand/maintain)
 -

  (Will have 2 datastores, the SQL DB  XML or hard-coded values; NOT 
complicated, but not as intuitively maintainable)

  Scalability
 +

   (Easily Scalable, simplified by the fact there's 1 datastore. Adding 
a State as easy as adding record to db, no code changes needed!)
 +

   (Easily Scalable, simplified by the fact there's 1 datastore. Adding 
a State as easy as adding new ENUM to db, no code changes needed!)
 N/-

  (Not as Easily scalable, adding/removing field means changing code 
which always has risks; of course done right: we'd just add new code to an 
XML file)

  Code Complexity
 +

   (Super simple, easy to understand, GET DATA:connect, query, show;

  WRITE DATA validate, connect, write)
 +

   (Super simple, easy to understand, GET DATA:connect, query, show;

  WRITE DATA validate, connect, write)
 -

  (Alittle more involved

  GET DATA: connect, query, turn codes into words via code, showd

  WRITE DATA: similar to Relational/Enum

  Performance
 N or -
 N or -
 +

  Could potentially increase performance by taking load off SQL server.

  Portability (Using w/ 3rd party System or future external application)
 +

  Integrating w/ 3rd party SW or new applications simple.
 +  (?)

  Integrating w/ 3rd party SW or new applications probably like 
Relational. Are ENUMS handled/returned via normal SELECT queries? (if so, 
just like relational)
 -

  Would have to write the decodify module for any outside system. Isn't 
intuitively portable.



ThanX,

Ben


David Ashley [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
 On Tue, Sep 23, 2008 at 11:13 AM, Olexandr Melnyk [EMAIL PROTECTED] 
 wrote:

  On 9/23/08, David Ashley [EMAIL PROTECTED] wrote:

 For example, for the 50 states, a lot of programmers would put this 
 logic
 in
 the web script and just store the two-letter postal code in the database
 table (but with no separate table for states).  The mapping from MI 
 to
 Michigan would occur in the web scripts; as would the logic to know 
 that
 XQ is not a state.


 Keep in mind that, as you add more countries, maintaining in-scipt
 code/name mappings of state codes will become more and more cumbersome.
 Another problem will arise if you want to view user information, along 
 with
 full country and state names, from a different programming language.

 Plus, if the same query is run very often and table is almost static,
 chances are high that the result will be in query cache.


 The different programming language issue is one I hadn't thought about.
 Good catch.

 I program in PHP only, and I run queries manually only if I need to debug
 something hard.  But I could see the issue you cited arising in a more
 complex environment.  I could even see a mixed web application where the
 web presence is a mixture of PHP, Python, and Perl.

 Good catch.
 



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

Re: Relational Databasing on busy webserver (Benchmark of Enum?!)

[Ben A.H.]

I figured that was what you meant... I guess my table didn't work (see above 
message...don't ya' love plaintext :-O)...

Has anyone ever tried to benchmark the difference between utilizing ENUMs 
vs. traditional relational databasing? I would think ENUM is ideal for items 
I specified at the beginning of this thread, items I would think would be 
part of MANY (if not MOST) databases (state, country, gender, industry, 
occupation, referredFrom, ethnicity, position)... In my case, it would allow 
me to eliminate 15+ tables...

I'm just wondering why database ENUMS aren't used more often... (what's the 
catch)



Olexandr Melnyk [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
 On Tue, Sep 23, 2008 at 6:13 PM, Olexandr Melnyk [EMAIL PROTECTED] 
 wrote:

 Plus, if the same query is run very often and table is almost static,
 chances are high that the result will be in query cache.


 Just realized that I haven't mentioned that this sentence is related to
 storing states in the database, rather than in the application layer.

 -- 
 Sincerely yours,
 Olexandr Melnyk
 http://omelnyk.net/
 



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

Relational Databasing on busy webserver

[Ben A.H.]

Hello,

We are setting up a relatively common web application which collects user 
information... Right off the bat our system will have over 200,000 USER 
RECORDS so having an efficient database  lookup scheme is critical.
I am a programmer/developer with some education in databasing but my forte 
is programming hence this odd (I think) question...

Obviously we'll have the standard USER table  a bunch of supporting tables.

For items like STATE/PROVINCE standard database logic would dictate I setup:

USER
   name
   email
   .etc...
   StateID  (foreign key)


STATE
   StateID
   StateName

But I often wonder if there's any benefit in having a State table...

For one, new States/Provinces are not that likely, removal of these entities 
is also unlikely (except maybe Quebec :-)) so the chances of having to make 
alternations to the State table near nil. It raises the question of whether 
or not a State Table is even necessary.
Using a traditional SQL State table, I'd have to do an SQL query to populate 
User State/Province options every time the New User Registration form is 
shown - isn't this needless overhead?!
Would my webforms not load faster if State/Province information was 
hard-coded as options for a combobox? Is this something people have 
experimented with?

There are various other fields that I believe could be handled like this for 
a cumulative performance boost. For example: country, state/province, 
gender, industry, occupation, ethnicity, language are all options that 
aren't going to change that often. Then again, when we do have to display 
the users choice; for example if user.countryID=3 we'd have to have a way to 
turn that number 3 into the word Canada when we display the users 
profile... I'd probably do this via XML lookup.

Has anyone experimented with the benefits/tradeoffs of such a scheme? As 
I've said, we are dealing with at least 200,000 user records, probably 
300,000-400,000 in the next year. The User table contains at least 50 
attributes, 15 of which are Foreign Keys that link to tables that will 
likely never change (the users choices for countryID, stateID, 
OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID, 
industryID, occupationID)...

ThanX in advance
Ben



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

Normalization / Foreign Key Question

[Ben A. Hilleli]

Hello,

 

A bit embarrassed, I have been away from the databasing side of system
development for quite a while so am a little rusty. This seems like a simple
issue.

 

I have a USER table:

 

userID

fName

lName

address

address2

city

province

country

referrerID

 

userID is the unique primary key.

 

My question pertains to referrerID. The referrerID specifies which OTHER
USER referred this new user. Thus it is a foreign key that links to a userID
in another user record. I do not recall ever linking attributes from the
same table (even for different records) so I have a hunch that the way to do
this properly is either:

 

(A)

 

User UserReferrer

-  ---

userID (the new userID)---userID 

 referrerID (the userID of
the referrer)

 

or (B)

just store referrerID as a number and use a simple query to get the info for
the referrer when it is required.

 

Select name from USER

Where userID=currentUsersID

 

 

Which is the correct way of doing this? If both, which would be ideal, this
will be a relatively large database with approx 250,000+ users, not all will
have referrerIDs, I would wager maybe 1-10% of these people will have been
referred. Priorities for us are simplicity/scalability, efficiency (in that
order) 

 

   Ben A. Hilleli

   Programmer / Analyst

 

 

Multi-Language Web Content

[Ben A. Hilleli]

Hi,

 

I am in the planning stages of a multi-language web-content  user
management system.

 

I may not be here to support this app forever so priority is on
maintainability, scalability (pref. by less experienced programmers or
laymen)

 

We have a bunch of questionnaire type pages (name, address, sex, tv watching
habits, salary etc.) that are populated dynamically based on database and
xml (more below)

Users fill out these pages, and that information is stored

 

I want to store the information for these pages (i.e. the available options
for salary, the question-text for the salary question) in a mysql database

BUT this site is multilingual so options etc. will need to be stored for
English, French (with room for future languages as well).

 

I would also like congruity between language, i.e. if you pick Engineer
occupation type in French or English, I want your user record to have the
same code (i.e. whether picked Manager(En) or Gerant(Manager in French), the
user's occupation should=5 (the code for Engineer/Gerant)

 

Obviously this poses a problem for 'normal' relational-databases as one
record can have one ID and I don't think having a FrenchName  EnglishName
for each would be that efficient.

 

My partner has suggested not using a traditional database but a combination
of mySQL and XML.

The mySQL would basically store user records, with codes for most options
(i.e. Occupation would = a number from 1 to 20, salary would = a number from
1 - 5 (representing a salary range), while the XML would store the meaning
of these codes (i.e. we would run an sql query to get the number 5, then run
that past the xml to get the named value (i.e. Engineer) for it.

 

Similarly for the Web Content management, we'd store most of it in XML.


I hope this post makes sense to you. My general preference is to stick to a
normal relational database. Which is the better approach, a big database or
the dbase/xml version.

 

 

   Ben

  

 

Weird, intermittent behavior: SHOW FIELDS queries taking 2 seconds!

[Ben Sprecher]

Hi folks -

I've searched the web in vain to try to find anyone else with this
problem. My computer will intermittently start having all SHOW
FIELDS queries take 2 seconds. Not 1. Not 3. Always 2. The problem
appears suddenly for no apparent reason - sometimes immediately after
restarting the computer, sometimes after I've been working for a long
time, sometimes it doesn't happen at all. It also sometimes disappears
on its own, although usually I need to restart to make it go away.

The database backs a Ruby on Rails application. Here is an excerpt of
what the Rails development log looks like when the problem is
happening (notice how other queries are running at a reasonable speed,
only the SHOW FIELDS queries are 2 seconds):

 begin development log excerpt 

  ←[4;35;1mShopperGroup Columns (2.00)←[0m   ←[0mSHOW FIELDS FROM
`shopper_groups`←[0m
s`←[0m
  ←[4;36;1mShopperGroup Load (0.00)←[0m   ←[0;1mSELECT * FROM
`shopper_groups` WHERE (`shopper_groups`.`id` = 1) ←[0
m
  ←[4;35;1mStore Columns (2.00)←[0m   ←[0mSHOW FIELDS FROM `stores`←[0m
m
  ←[4;36;1mStore Load (0.00)←[0m   ←[0;1mSELECT * FROM `stores`
WHERE (`stores`.`id` = 1) ←[0m
  ←[4;35;1mPurchase Load (0.609000)←[0m   ←[0mSELECT * FROM
`purchases` ORDER BY purchased_on desc LIMIT 1←[0m
  ←[4;36;1mPurchase Columns (2.016000)←[0m   ←[0;1mSHOW FIELDS FROM
`purchases`←[0m
0m

 end development log excerpt 

I was thinking the problem might be in Rails, but it doesn't seem to
be. I logged into the mysql command-line client and ran some SHOW
FIELDS commands from there. Same thing - 2 seconds per query:

mysql show fields from accounts;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | varchar(50) | NO   | | NULL||
+---+-+--+-+-++
2 rows in set (2.02 sec)

However, a few minutes later, the problem magically went away:

mysql show fields from accounts;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | varchar(50) | NO   | | NULL||
+---+-+--+-+-++
2 rows in set (0.00 sec)

And then I ran a few queries over several minutes to show the problem
reappearing.

mysql select (now());
+-+
| (now()) |
+-+
| 2008-06-28 22:37:32 |
+-+
1 row in set (0.03 sec)

mysql show fields from accounts;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | varchar(50) | NO   | | NULL||
+---+-+--+-+-++
2 rows in set (0.01 sec)

/** NOW, I WAITED UNTIL THE BEHAVIOR RETURNED (ABOUT 2 MINUTES) **/

mysql show fields from accounts;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL| auto_increment |
| name  | varchar(50) | NO   | | NULL||
+---+-+--+-+-++
2 rows in set (2.00 sec)

mysql select (now());
+-+
| (now()) |
+-+
| 2008-06-28 22:39:32 |
+-+
1 row in set (0.00 sec)

If anyone knows what could be causing this, I would greatly appreciate
your insight.


Thanks,

Ben

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

Re: Incorrect information in file: './maindb/users.frm'

[Ben Clewett]

It could be the size of your InnoDB log files.  Or something else which 
stops InnoDB running.


I once changed server and by mistake changed the size of the 
innodb_log_file_size.  The result was that InnoDB could not start and 
instead dissabled its self.  Therefore every table I created became MyISAM.


Check the error.log.  If this is the case it will say

Also do a SHOW ENGINS, may show something...

Ben


Stut wrote:

On 5 Jun 2008, at 00:41, Phil wrote:

Just a very quick guess but is innobd engine running ?

SHOW STATUS like '%inno%'


Output shown below. I've just finished importing the backup file again 
(into a different DB) and it's ignored the engine=innodb on each create 
table and has used MyISAM instead. Trying to alter a table yields the 
following...


mysql alter table users engine=InnoDB;
Query OK, 853097 rows affected, 1 warning (1 min 35.71 sec)
Records: 853097  Duplicates: 0  Warnings: 0

mysql show warnings;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1266 | Using storage engine MyISAM for table 'users' |
+-+--+---+
1 row in set (0.00 sec)

Show status output...

mysql SHOW STATUS like '%inno%';
+---++
| Variable_name | Value  |
+---++
| Com_show_innodb_status| 0  |
| Innodb_buffer_pool_pages_data | 0  |
| Innodb_buffer_pool_pages_dirty| 0  |
| Innodb_buffer_pool_pages_flushed  | 0  |
| Innodb_buffer_pool_pages_free | 196608 |
| Innodb_buffer_pool_pages_latched  | 0  |
| Innodb_buffer_pool_pages_misc | 0  |
| Innodb_buffer_pool_pages_total| 196608 |
| Innodb_buffer_pool_read_ahead_rnd | 0  |
| Innodb_buffer_pool_read_ahead_seq | 0  |
| Innodb_buffer_pool_read_requests  | 0  |
| Innodb_buffer_pool_reads  | 0  |
| Innodb_buffer_pool_wait_free  | 0  |
| Innodb_buffer_pool_write_requests | 0  |
| Innodb_data_fsyncs| 0  |
| Innodb_data_pending_fsyncs| 0  |
| Innodb_data_pending_reads | 0  |
| Innodb_data_pending_writes| 0  |
| Innodb_data_read  | 0  |
| Innodb_data_reads | 1  |
| Innodb_data_writes| 0  |
| Innodb_data_written   | 0  |
| Innodb_dblwr_pages_written| 0  |
| Innodb_dblwr_writes   | 0  |
| Innodb_log_waits  | 0  |
| Innodb_log_write_requests | 0  |
| Innodb_log_writes | 0  |
| Innodb_os_log_fsyncs  | 0  |
| Innodb_os_log_pending_fsyncs  | 0  |
| Innodb_os_log_pending_writes  | 0  |
| Innodb_os_log_written | 0  |
| Innodb_page_size  | 16384  |
| Innodb_pages_created  | 0  |
| Innodb_pages_read | 0  |
| Innodb_pages_written  | 0  |
| Innodb_row_lock_current_waits | 0  |
| Innodb_row_lock_time  | 0  |
| Innodb_row_lock_time_avg  | 0  |
| Innodb_row_lock_time_max  | 0  |
| Innodb_row_lock_waits | 0  |
| Innodb_rows_deleted   | 0  |
| Innodb_rows_inserted  | 0  |
| Innodb_rows_read  | 0  |
| Innodb_rows_updated   | 0  |
+---++
44 rows in set (0.00 sec)

Anyone have any ideas?

Thanks.

-Stut



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

Re: Show indexing status

[Ben Clewett]

As far as I am aware, the index's are built on import.

It may be that the key-buffer or innodb-buffer (depending on engine), 
and the query cache, are all cold.  May take a day or so to build them 
up depending on size and load.


Other than that there must be some external difference.  Is it the same 
server?  Same config file?  Same default engine?


Ben

Stut wrote:

Hi,

I just finished restoring a 22gig SQL dump but the server is not 
performing anywhere near where it should be. I'm assuming this is 
because it's still rebuilding indexes on the imported tables.


Is there any way to see the indexing status so I can gauge how far it's 
got?


Thanks.

-Stut



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

Possible Mutex Problem

[Ben Clewett]

Under 5.0.41 I have having problems of CPU sitting at exactly 100% load 
on a single CPU for very long periods of time, like 10 hours.


I traced this command:

mysql SHOW MUTEX STATUS;

Which returned 1281006 lines, ending with:

+-+--+--+
| File| Line | OS_waits |
+-+--+--+
...
...
| buf0buf.c   |  497 |0 |
| buf0buf.c   |  494 |0 |
| buf0buf.c   |  497 |0 |
| buf0buf.c   |  494 |0 |
| buf0buf.c   |  545 |  1159932 |
| fil0fil.c   | 1293 |  398 |
| srv0start.c | 1201 |0 |
| srv0start.c | 1194 |0 |
| srv0start.c | 1172 |0 |
| dict0mem.c  |   90 |0 |
| dict0mem.c  |   90 |0 |
| srv0srv.c   |  875 |50886 |
| srv0srv.c   |  872 |   113162 |
| thr0loc.c   |  229 |1 |
| mem0pool.c  |  205 |25482 |
| sync0sync.c | 1289 |0 |
+-+--+--+

(All other lines are as same as first two.)

Is there an InnoDB expert who can tell me whether this is indicating a 
problem?


Regards,

Ben


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

Re: Replication for reporting

[Ben Clewett]

You might be able to do it with the federated engine:

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

Fire a trigger on your main tables which update some row in a foreign 
MySql database used for accounting.  I've not tried this but the theory 
is sound.


Ben


Andrey Dmitriev wrote:

Is it true that a single mysql server can be a slave to only one master?

So if you need to replicate a dozen databases into a single reporting 
server, you need to have a chain of a dozen servers in between? Someone 
shared that to me, but it didn't make much sense.


In oracle we have the following options to replicate:
Snapshots (also known as materialized views) for individual tables
Standby (similar to mysql, but replicates the entire db)
Streams (pick whatever you need out of sql stream)

So for reporting purposes from multiple db's, you'd mostly likely pick 
snapsohts or streams.


Thanks,
andrey



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

Replication advise

[Ben Clewett]

Dear MySql,

Hope somebody might give some advise about replication!

On 5.1.24, I have something simple like:

CREATE TABLE big_f_er (
  id BIGINT UNSINGED NOT NULL AUTO_INCREMENT,
  t TIMESTAMP NULL NOT NULL,
  PRIMARY KEY (id, t),
  KEY (t)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(t) )
(
  PARTITION p0 VALUES LESS THAN (2006),
  PARTITION p1 VALUES LESS THAN (2007),
  PARTITION p2 VALUES LESS THAN (2008),
  (etc...)
);

Into which I plan to dump several terabytes of an exiting v.large table.

Looking at EXPLAIN PARTITIONS I have noticed that these SQL statement 
correctly select the partition:


  SELECT WHERE t =
  SELECT WHERE t IN

But these select all partition tables:

  SELECT WHERE t 
  SELECT WHERE t BETWEEN

So, my question is, without having to re-write several billion lines of 
code, is there a way I can make all these examples correctly select the 
partition table?


Any help very welcome!

Regards,

Ben

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

Re: Query execution time - MySQL

[Ben Clewett]

If you using C++ then you can use this:

http://developer.gimp.org/api/2.0/glib/glib-Timers.html

I use this in my code, does an excelent job.

Also you may want to look at the 'slow log' in mysql which will show, to 
the nearest second, the length of queries


Ben

Neil Tompkins wrote:

Hi Craig,
 
Thanks for your detailed reply.  Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished.
 
So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query.
 
Regards

Neil


Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL 
PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much 
detail there (but I'll go off what you provided...). Some people limit the 
actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be 
accomplished, MySQL has built-in functionality to measure the time is takes 
queries to take place so it can ... limit them. So, in essence, I guess we can 
extract that data and get it back to you for whatever usage statistic you are 
looking to measure. ( See: 
http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
 ) or for usage in JDBC by calling the setQueryTimeout() function of a 
Statement object...and so forth.HOWEVER - Just so you know, if you execute the 
query MANUALLY via the command-line of MySQL it will tell you how long the 
query took. Just use normal SQL syntax, execute

the query on the table and VOILA! Your answer:mysql queryormysqlrun the query 
(use the below quoted/threaded example as a starting place to write your own 
query...?)Take a look at this thread (it basically explains the answer with a 
bit more detail on what the output will 
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404

SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT 
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the 
results are the following: The first number is the time it took MySQL server to 
send the result set to the client. The second number (in parens) is the time it 
took MySQL server to execute the query itself.
TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or 
wherever you are running the query...). Many things come into factoring why it 
takes longer or shorter. So this is why I asked if you are attempting to optimize 
or what not, but that is whole new story. ((  What Operating System are 
you running? This would be helpful to give you the step-by-step, so to speak. Or 
perhaps provide us with a bit more information***Also, if you are looking to 
perhaps make it so queries take shorter times (optimization effort) to execute a 
little bit more about your MySQL database setup and machine(s) would be beneficial 
to us as well. ))Let me know if you have any questions.Standing by and I hope this 
helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache
On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote:
Hi,When performing a SQL query like SELECT Name FROM Customers.  How do I 
obtain the time in which the query took to execute like 1.5 seconds 
etcThanks,Neil_All
 new Live Search at 
Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_
Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay 
today!
http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/


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

Re: Query execution time - MySQL

[Ben Clewett]

Hi Neil,

If your using Linux then you have to install the glib RPM's in the usual 
way.  I don't know about other platforms, but I am sure there will be a 
version of glib out there...


Also ensure the correct include and link directives are in your 
Makefile, which you can get (on Linux) using the commands:


# glib-config --cflags
# glib-config --libs

Ben

Neil Tompkins wrote:
Thanks Ben, but I don't appear to have the header file glib.h in my 
libraries.
 
Neil


 



  Date: Wed, 14 May 2008 12:39:09 +0100
  From: [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  CC: [EMAIL PROTECTED]; mysql@lists.mysql.com
  Subject: Re: Query execution time - MySQL
 
  If you using C++ then you can use this:
 
  http://developer.gimp.org/api/2.0/glib/glib-Timers.html
 
  I use this in my code, does an excelent job.
 
  Also you may want to look at the 'slow log' in mysql which will show, to
  the nearest second, the length of queries
 
  Ben
 
  Neil Tompkins wrote:
   Hi Craig,
  
   Thanks for your detailed reply. Basically what I'm trying to 
extract is the time taken from when I execute the mysql query in my C++ 
Builder program until the time the query has finished.

  
   So my question is can I build in to my SQL query SELECT Name FROM 
Customers the time the query actually took or do I need to do this 
outside of my query.

  
   Regards
   Neil
  
  
   Date: Wed, 14 May 2008 07:21:04 -0400From: 
[EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query 
execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not 
much detail there (but I'll go off what you provided...). Some people 
limit the actual MySQL system for times it TAKES MySQL to execute 
queries. For THIS to be accomplished, MySQL has built-in functionality 
to measure the time is takes queries to take place so it can ... limit 
them. So, in essence, I guess we can extract that data and get it back 
to you for whatever usage statistic you are looking to measure. ( See: 
http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html 
) or for usage in JDBC by calling the setQueryTimeout() function of a 
Statement object...and so forth.HOWEVER - Just so you know, if you 
execute the query MANUALLY via the command-line of MySQL it will tell 
you how long the query took. Just use normal SQL syntax, execute
  the query on the table and VOILA! Your answer:mysql queryormysqlrun 
the query (use the below quoted/threaded example as a starting place to 
write your own query...?)Take a look at this thread (it basically 
explains the answer with a bit more detail on what the output will 
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404
   SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) 
SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) 
I believe the results are the following: The first number is the time it 
took MySQL server to send the result set to the client. The second 
number (in parens) is the time it took MySQL server to execute the query 
itself.
   TOTAL TIME will EQUAL A + B (for total time it took on your 
server/P.C. or wherever you are running the query...). Many things come 
into factoring why it takes longer or shorter. So this is why I asked if 
you are attempting to optimize or what not, but that is whole new story. 
((  What Operating System are you running? This would be helpful to 
give you the step-by-step, so to speak. Or perhaps provide us with a bit 
more information***Also, if you are looking to perhaps make it so 
queries take shorter times (optimization effort) to execute a little bit 
more about your MySQL database setup and machine(s) would be beneficial 
to us as well. ))Let me know if you have any questions.Standing by and I 
hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | 
#apache
   On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins 
[EMAIL PROTECTED] wrote:
   Hi,When performing a SQL query like SELECT Name FROM Customers. How 
do I obtain the time in which the query took to execute like 1.5 seconds 
etcThanks,Neil_All 
new Live Search at 
Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/

   _
   Great deals on almost anything at eBay.co.uk. Search, bid, find and 
win on eBay today!

   http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]

 



Miss your Messenger buddies when on-the-go? Get Messenger on your 
Mobile! http://clk.atdmt.com/UKM/go/msnnkmgl001001ukm/direct/01/


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

Re: Data Modelling

[Ben Clewett]

Table level locking is inherent to MyIsam.

Look into partitioning, as this breaks table into two or more other 
tables which will lock separately.


Or use InnoDB:

ALTER TABLE ... SET ENGINE=InnoDB;  (I think)

Ben


Krishna Chandra Prajapati wrote:

Hi,

I am looking for a solution to reduce the table locks as mytop shows that
table gets locked very frequently. During report generation.

Thanks,
Prajapati

On Tue, May 13, 2008 at 1:10 PM, Ananda Kumar [EMAIL PROTECTED] wrote:


what is the reason for creating main_dup. If your thinking of taking a
backup of all the changes from  main table, then the trigger also will have
to wait, till the locks on the main table are released.

This trigger would add another feather to the lock/load on the machine.

regards
anandkl


On 5/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote:

Hi,

Generally, in data modelling there are some independent table, user
related
tables, master tables and one main table which is related with most of
the
tables. Almost in all the queries main table is involved to fetch the
`id`.
In this way main table is used maximum. some times the main table gets
locked due to the other query. When the hits on database server
increases
the locking time will increase.

Is there any way to reduce the locking time of main table.
for eg main_dup can be created. To reflect all the changes from main
table
to main_dup trigger can be used. So main_dup can be used to reduce the
locking time of table.

The above is my idea. Is there any other way to reduce the locking
period.

--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]








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

Re: Data Modelling

[Ben Clewett]

If you use InnoDB you should not have a problem as it used row-level 
locking and isolated transitions.


Other than that you can split your tables into smaller ones using either 
partitioning or the federated engine...


Ben




Krishna Chandra Prajapati wrote:

Hi,

Generally, in data modelling there are some independent table, user related
tables, master tables and one main table which is related with most of the
tables. Almost in all the queries main table is involved to fetch the `id`.
In this way main table is used maximum. some times the main table gets
locked due to the other query. When the hits on database server increases
the locking time will increase.

Is there any way to reduce the locking time of main table.
 for eg main_dup can be created. To reflect all the changes from main table
to main_dup trigger can be used. So main_dup can be used to reduce the
locking time of table.

The above is my idea. Is there any other way to reduce the locking period.



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

Re: Block size of filesystem

[Ben Clewett]

I would use as large a block size as you dare, especially with InnoDB. 
Makes reading and writing faster as custs down seek time as cuts down 
disk fragmenation and avoids block table reads.  With MyIsam you have 
lots of files, but if you only have a few again might work well with a 
large block size.


Also have a look at the stripe size of your raid system, might work well 
aligning them if you can.  This URL also gives some tips for an ext3 
file system on RAID, look for 'stride':


http://insights.oetiker.ch/linux/raidoptimization.html

Ben

Iñigo Medina García wrote:

Hi friends,

we're developing a new web application that works with a db around 1gb
and 30 tables.

We work with linux, and I'm evaluating the benefit of making an specific
partition (ext3) for mysql in order to have a bigger Block Size. Block
Size by default in / partition is 4096.

Do you think that could be good? Any experience?

 Iñigo



*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

InnoDB Log Optimisation

[Ben Clewett]

Dear MySql,

I am trying to optimise InnoDB, and trying to find out how much of the 
innodb log file contains row data which has not been written to storage.


Therefore I can optimize the size of the log, keeping it low to reduce 
crash recovery time yet high enough to be useful.


I can see the varaible 'Innodb_data_pending_writes' which may be what I 
need.  This is zero, I am not sure whether this means I have no 
uncommitted data in the log, or something else.


Can't find anything much on Google.

If any member can help me, would be very usesful :)

Regards,

Ben.


*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

Re: InnoDB Log Optimisation

[Ben Clewett]

Thanks for the idea.  Unfortunately nothing I can easily use (for 
instance in MySql Administrator) to log and monitor the lag in bytes 
between log writes and row data writes. :)


Iñigo Medina García wrote:

Hi Ben,


Dear MySql,

I am trying to optimise InnoDB, and trying to find out how much of the
innodb log file contains row data which has not been written to storage.

Therefore I can optimize the size of the log, keeping it low to reduce
crash recovery time yet high enough to be useful.


:-)


I can see the varaible 'Innodb_data_pending_writes' which may be what I
need.  This is zero, I am not sure whether this means I have no
uncommitted data in the log, or something else.

Can't find anything much on Google.

If any member can help me, would be very usesful :)


What spits SHOW INNODB STATUS\G ?

Iñigo


Regards,

Ben.


*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court,
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE.
*






*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

Re: Timestamp and the On Update Current_Timestamp clause

[Ben Clewett]

SHOW CREATE TABLE ...

Martijn Tonies wrote:

Hi,

How does one know if ON UPDATE CURRENT_TIMESTAMP was specified when
creating a column? How do I retrieve this bit of info from the metadata
queries?
(also MySQL 4.1)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com




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

Visio Stencils

[Ben Wiechman]

Does anyone have a set of MySQL Visio stencils? Does such a beast exist?

 

Ben Wiechman



 

Re: auto_increment

[Ben Clewett]

Are you sure, I just get:

CREATE TABLE ...

ERROR 1075 (42000): Incorrect table definition; there can be only one 
auto column and it must be defined as a key


On version 5.0.41.  What version are you using?



Hiep Nguyen wrote:

hi list,

reading manual on mysql regarding auto_increment with multiple-column 
index:


CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

+++-+
| grp| id | name|
+++-+
| fish   |  1 | lax |
| mammal |  1 | dog |
| mammal |  2 | cat |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+++-+

my question is what id would be if i:

UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND 
`name`='ostrich' LIMIT 1;


i'm confused on auto_increment now.

thanks



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

Re: auto_increment

[Ben Clewett]

You are right, I've tried 5.0.18 and 5.0.45 which work.

There must have been a bug in 5.0.41 with which I used test the question...

I belive the question has been answered by now anyway :)

Ben


Sebastian Mendel wrote:

Ben Clewett schrieb:

Are you sure, I just get:

CREATE TABLE ...

ERROR 1075 (42000): Incorrect table definition; there can be only one 
auto column and it must be defined as a key


the mentioned CREATE TABLE is fine and works



On version 5.0.41.  What version are you using?


this works on all versions, and the example is from the MySQL manual

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html



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

Re: Can't get a login shell for some databases

[Ben Clewett]

Pam Astor wrote:
The ANSI-SQL syntax is to just use GRANT to create users.  You will 
still need to use GRANT twice for both users: joe@'%' and joe@'localhost'.


But I find the MySql syntax for creating user with CREATE USER and then 
GRANT easier to follow:


CREATE USER joe@'%' IDENTIFIED BY 'whateverpassword';
CREATE USER joe@'localhost' IDENTIFIED BY 'whateverpassword';

GRANT ALL ON db1.* TO joe@'%';
GRANT ALL ON db1.* TO joe@'localhost';

If you see what I mean...

Just remember that a user always has a host, and you should always use 
the two together.


Ben


Thanks Ben,

 OK Got it,

One more thing, I have already created these users and don't want to mess their
passwords up or break their associated php scripts access.

So how do I grant users, who already have a password, localhost
access?


A user is always paired with a host.  Therefore your user @'localhost' 
is a new user.  You will have to create with same password as existing :)


Ben






_
Going green? See the top 12 foods to eat organic.
http://green.msn.com/galleries/photos/photos.aspx?gid=164ocid=T003MSN51N1653A


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

Re: History of changed rows

[Ben Clewett]

No problem.

I do this using three triggers on Insert, Update and Delete.  Then 
update a log file who's schema starts:


CREATE TABLE ?_log (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  transact ENUM ('I','U','D') NOT NULL,
  key_from_table ??? NOT NULL,
  KEY (key_from_table),
  field_1 ?? ,
  field_2 ??,
  ...
  field_n ??

I don't know a way of copying over every field accept long-hand in the 
triggers.


Hope this is useful...

Ben


C K wrote:

Hi all.
How can we manage the history of changed rows in the database. I have some
idea but not yet implemented. By using triggers to make a copy of the row
being changed to the another table in the same db. Is there any way to only
save the changed fields data and field name? Any other idea?
Thanks
CPK



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

Re: Can't get a login shell for some databases

[Ben Clewett]

A user in MySql is not just a username, but a username and a host.

The host of '%' denotes all hosts accept 'localhost'.

Therefore you usually require two entries for each user:

CREATE USER ben@'%' INDENTIFIED BY 'ben';
CREATE USER ben@'localhost' INDENTIFIED BY 'ben';

Does this help?

Ben


Pam Astor wrote:
connectiong from shell means connecting as localhost by default, connecting 
from PHP can be some different server and/or PHP uses the full IP 
address/hostname of the server


check/compare the privileges for your users for 'localhost' and '%'




I ran “SELECT * from mysql.user;” the command generated a
list.  For root it showed in the host
column local host, the second row for root showed the name of the web server
the mysql install is on.  The third and
fourth rows showed no users, however the third and fourth rows showed in the
host columns localhost and the server name, respectively.



 The fifth and sixth lines showed the first non root user I created
and for this user it showed in the host Colum the % character, and the other
line for this user showed localhost as the host.



 The seventh line shows the second non root user I created –
it has just one line and shows localhost as the host.  All the rest of the 
users I created show the
% character in the host column. 




 No IP addresses are listed anywhere.



_
More immediate than e-mail? Get instant access with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008


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

Re: MySQL Slave

[Ben Clewett]

Check the error.log on both server and client, should show where 
connected and disconnected.


Or write a small program to execute SHOW SLAVE STATUS every minute and 
log the results.  Which I belive is done for you in the MySql dashboard 
program suit.


Ben

Kaushal Shriyan wrote:

Hi

Is there a way to find out when the MySQL Slave has been restarted.

Thanks and Regards

Kaushal



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

Re: Can't get a login shell for some databases

[Ben Clewett]

Pam Astor wrote:

A user in MySql is not just a username, but a username and a host.

The host of '%' denotes all hosts accept 'localhost'.

Therefore you usually require two entries for each user:

CREATE USER ben@'%' INDENTIFIED BY 'ben';
CREATE USER ben@'localhost' INDENTIFIED BY 'ben';

Does this help?




Makes sense, BUT, isn't the info between the last set of tickmarks '' the 
password for

the user?


Yes.



When I created the users, I ran the command:

grant usage on db1.* to joe identified by 'whateverpassword';

Then I ran a second grant command:

grant select, drop, etc, on db1.* to joe;



The ANSI-SQL syntax is to just use GRANT to create users.  You will 
still need to use GRANT twice for both users: joe@'%' and joe@'localhost'.


But I find the MySql syntax for creating user with CREATE USER and then 
GRANT easier to follow:


CREATE USER joe@'%' IDENTIFIED BY 'whateverpassword';
CREATE USER joe@'localhost' IDENTIFIED BY 'whateverpassword';

GRANT ALL ON db1.* TO joe@'%';
GRANT ALL ON db1.* TO joe@'localhost';

If you see what I mean...

Just remember that a user always has a host, and you should always use 
the two together.


Ben








I should probally say that my 5.0.22 MySQL database is on a centos 5.1 box.
_
Pack up or back up–use SkyDrive to transfer files or keep extra copies. Learn 
how.
http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_packup_042008


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

Re: MySQl 5.0 optimization problem

[Ben Clewett]

Are you using MyIsam or InnoDB?  Or something else?

In either case the speed to get a COUNT() is largely down to the speed 
if your disks and size of disk caching.  A COUNT() forces the system to 
read every row in order to count them, and any large table is probably 
larger than your caches.


In some ways this is not so important, since it is unusual for a query 
to want to read every row of a table.  (I have 250GB tables which have 
excellent performance but would take minutes to count every row :)


It might be better to consider the type of queries you will be using, 
and the type of table, and optimise for that...


Ben

Victor Danilchenko wrote:

Hi,

I am trying to optimize our DB server. We have one table which has 
1.3M entries, and the keys are GUIDs (so the key space is large). 
However, I have it all indexed. The performance was iffy, though, so I 
increased memory allocation, and the searches on the indexed fields seem 
to be OK now. Still, a simple count(field) on this table still takes, 
like, 2 minutes! I am guessing i am missing something obvious, but I 
read through a few MySQL optimization guides, and it seems like i am 
covering my bases.


Here is the relevant slice of my my.cnf:

#
# * Fine Tuning
#
key_buffer  = 256M
max_allowed_packet  = 64M
thread_stack= 512K
thread_cache_size   = 32
#
# * Query Cache Configuration
#
query_cache_limit   = 32M
query_cache_size= 256M
query_cache_type= 1
table_cache = 512
sort_buffer_size= 32M


I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are 
all indexed, but I can't imagine why a simple count() would take so 
long, when the actual query by value on the same field is effectively 
instant (after my cache setting expansion).


Does anyone have an idea of what I am missing? Also, if you think 
any of the above settings seem wrong for a server with 1GB of RAM, 
please let me know.




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

Re: MySQl 5.0 optimization problem

[Ben Clewett]

Hi,

Can you please post your query?  I also need to know your table type as 
different settings effect different table types?


You are right that a SELECT COUNT(*) WHERE field = 'value' should hit 
the index, but does depend on your query.


You might also try EXPLAIN before your query, which will show the 
approximate number of rows you are hitting.  Look at SHOW TABLE STATUS 
which will give an idea of the size of the rows and indexs.


Marry one to the other and it will give an idea of the cache settings to 
get the query into cache.  But still depends a lot on the table type!


Ben


Wm Mussatto wrote:

On Fri, April 11, 2008 06:47, Ben Clewett wrote:

Are you using MyIsam or InnoDB?  Or something else?

In either case the speed to get a COUNT() is largely down to the speed
if your disks and size of disk caching.  A COUNT() forces the system to
read every row in order to count them, and any large table is probably
larger than your caches.

In some ways this is not so important, since it is unusual for a query
to want to read every row of a table.  (I have 250GB tables which have
excellent performance but would take minutes to count every row :)

It might be better to consider the type of queries you will be using,
and the type of table, and optimise for that...

Ben

Victor Danilchenko wrote:

Hi,

I am trying to optimize our DB server. We have one table which has
1.3M entries, and the keys are GUIDs (so the key space is large).
However, I have it all indexed. The performance was iffy, though, so I
increased memory allocation, and the searches on the indexed fields seem
to be OK now. Still, a simple count(field) on this table still takes,
like, 2 minutes! I am guessing i am missing something obvious, but I
read through a few MySQL optimization guides, and it seems like i am
covering my bases.

Here is the relevant slice of my my.cnf:

#
# * Fine Tuning
#
key_buffer  = 256M
max_allowed_packet  = 64M
thread_stack= 512K
thread_cache_size   = 32
#
# * Query Cache Configuration
#
query_cache_limit   = 32M
query_cache_size= 256M
query_cache_type= 1
table_cache = 512
sort_buffer_size= 32M


I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are
all indexed, but I can't imagine why a simple count() would take so
long, when the actual query by value on the same field is effectively
instant (after my cache setting expansion).

Does anyone have an idea of what I am missing? Also, if you think
any of the above settings seem wrong for a server with 1GB of RAM,
please let me know.

If the field you are counting is the first field in an index I would think
it would go much faster (system will just use the index).  If some of your
counts are fast and some are slow (you said iffy) that might explain the
difference.

--
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154






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

Re: Replication

[Ben Clewett]

It depends how public your database is.  If you ever need to shut off 
one client then you might use separate users.  Otherwise it's just 
annoying complexity, use a single user.


Remember to use a different 'server-id' for each client!

Ben

Krishna Chandra Prajapati wrote:

Hi,

I am setting up a replication with 1 master and multiple slave. My question
is can i use the same user (from master) for setting up replication on
multiple slaves. Is there any advantage for replication by creating
different user for different slave.

Thanks  Regards,


--
Ben Clewett
Road Tech Computer Systems Ltd
http://www.tachomaster.co.uk
http://www.roadrunner.uk.com
01923 46
Shenley Hall
Rectory Lane
Shenley
Hertfordshire WD7 9AN


*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

Re: Help with ORDER BY using two colomns

[Ben Clewett]

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, however I 
would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days to the 
 update dates so they would place themselves in the correct position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to the 
update field and if the customer does not answer within 10 days, to re 
insert them into the list.


But as the update timestamp will be 10 days old, I would like to add 10 
days to the update while inserting them to the list (not changing the 
actual value inserted in the database just add 10 days during the 
reordering process.). I hope my explanation in understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it just that
case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else created
end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show
you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks in advance :)

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






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





--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/





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

Re: Help with ORDER BY using two colomns

[Ben Clewett]

I think the easiest is to create a new logical column with the correct 
ordering, something like:


SELECT *, IF(update != '', update, date) AS o
FROM my_table ORDER BY o DESC;

I note that both 'update' and 'date' are reserved works :)

Also worth noting that this cannot be assigned an index and is therefore 
only good for small amounts of data.


-

If you have lots of data then you need a better solution.  Set 'update' 
to NULL where there is no value and add the key:


 KEY(update,date)

Then order by this key:

 ORDER BY update DESC, date DESC.

Which when I try it gives:

+-++--+
| num | date   | update   |
+-++--+
|   5 | 40 |   90 |
|   2 | 10 |   60 |
|   6 | 50 | NULL |
|   4 | 30 | NULL |
|   3 | 20 | NULL |
|   1 |  1 | NULL |
+-++--+

Ben



Richard wrote:

Hello,
I've got a table which containes two date colomns.
The first one is called `date` and the second `update`
In the first one I put the ticket creation date, and on update I add or 
change the update value.
So the update colomn does not contain a value until the first update has 
been done.
I would like to order the tickets by their last update value. And if 
this value does not exist use the date value.


at the moment I use this :

ORDER BY `date` DESC
and I would like to replace it by something like this :

ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

I know this code is completly wrong, just to try and show you what I 
need ...


Here is an example of what I want to achieve

num|date|update
---
1|1|   
2|10|60
3|20|   
4|30|   
5|40|90
6|50|   


The required result would be :

num|date|update
---
5|40|90
2|10|60
6|50|   
4|30|   
3|20|   
1|1|   


Thanks in advance :)



*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

[Ben Clewett]

Richard,

No problem, glad it works.  But note: this is not scalable.  If you have 
more than a few hundred rows, you may want to think about a better 
solution, like storing the order field permanetly and giving it an index :)


Ben

Richard wrote:

Thanks, it works like a charm :)

Ben Clewett a écrit :

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, however I 
would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days to 
the  update dates so they would place themselves in the correct 
position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to 
the update field and if the customer does not answer within 10 days, 
to re insert them into the list.


But as the update timestamp will be 10 days old, I would like to add 
10 days to the update while inserting them to the list (not changing 
the actual value inserted in the database just add 10 days during the 
reordering process.). I hope my explanation in understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it just 
that

case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else 
created

end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show
you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks in advance :)

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






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





--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/










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

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

[Ben Clewett]

Richard,

The query I gave you required the column 'o' to be calculated for each 
row at the time of gathering the data.  When all rows have been 
gathered, the data will be stored and sorted in a temporary table.  This 
temporary table will be in memory or on disk depending on the setting of 
the variable 'tmp_table_size'.  Then the result will be pruned to the 
LIMIT and sent.


You can see that the LIMIT does not help, MySql needs to know what the 
top rows will be, before it discards the bottom ones.  It can't know 
this until all data has been gathered and sorted.


This is all quite a lot of work for your MySql.  Hence it is not 
regarded as scalable.




Alternatively, store the order permanently on the table as a new field 
and give it an index:


ALTER TABLE  ADD o INT NOT NULL,
 ADD KEY(o);

Now the job of MySql is easy, the query just follows the index.  No 
temporary tables, no scanning all rows and no sorting before sending, 
and the LIMIT now works as you would hope.  This is highly scalable.


But you have to maintain the ordering field.  If your brave, do it with 
a trigger :)


Ben


Richard wrote:

Thanks,

This is for the unanswered list of questions, so the output list (not 
the list stored in the mysql database) should never go over 100.


by scalable, do you mean alot of ressources being used or a long wait 
for the answer? Because I belive I Could just use a simple limit if I 
needed to have a limited number of results on one page.


Every time a question is answered the update date will change, and the 
status could also change. So I don't see how to easily do this by 
creating another table.


Ben Clewett a écrit :

Richard,

No problem, glad it works.  But note: this is not scalable.  If you 
have more than a few hundred rows, you may want to think about a 
better solution, like storing the order field permanetly and giving it 
an index :)


Ben

Richard wrote:

Thanks, it works like a charm :)

Ben Clewett a écrit :

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, however 
I would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days 
to the  update dates so they would place themselves in the correct 
position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to 
the update field and if the customer does not answer within 10 
days, to re insert them into the list.


But as the update timestamp will be 10 days old, I would like to 
add 10 days to the update while inserting them to the list (not 
changing the actual value inserted in the database just add 10 days 
during the reordering process.). I hope my explanation in 
understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it 
just that

case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else 
created

end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value

Re: To replicate or not to replicate that is the question

[Ben Clewett]

This is something I do with replication.

You can replicate a list of tables, see the 'replicate_do_table' config 
option.


Then you can euse the 'read_only' option.  Replication still works but 
nothing else.  But it would give you more options by using GRANT instead 
of 'read-only':


CREATE USER dmz@'%' IDENTIFIED BY ...;
GRANT SELECT ON *.* TO dmz@'%';

Then you can have a 'dba' user which can still work on the database:

CREATE USER dba@'%' IDENTIFIED BY ...;
GRANT ALL ON *.* TO dba@'%';

Ben


David Ruggles wrote:

I need to put a read-only copy of a single table on a database on another
server so remote customers can have read access to it. I built a new MySQL
server and placed in my DMZ. I can use SSH forwarding to access it from
outside. Now I need to get a copy of the table from my internal MySQL server
to this new server. I was originally planning on using replication, however
the more I research it, the less I think it will do what I want.

Here are my requirements:
1) Minimal changes to the internal server, can't take it down.
2) Single table for now, but may need more later.
3) New server will NEVER have write access.
4) Doesn't have to be real-time, but as close as possible would be nice.

Does any one have any suggestions, or is replication the way to go?

Thanks,

David Ruggles
CCNA MCSE (NT) CNA A+
Network EngineerSafe Data, Inc.
(910) 285-7200  [EMAIL PROTECTED]






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

Re: To replicate or not to replicate that is the question

[Ben Roberts]

 I need to put a read-only copy of a single table on a database another
 server so remote customers can have read access to it.

I use SQLYob Job Agent to synchronise two MySQL databases. See 
http://www.webyog.com/en/ for more information.


I use SSH to so port forwarding from one box to the other, and then use 
the SQLYog Job Agent via cron every minute to sync any changes from 
specified tables on the source DB to the remote DB. You can do it 
without the SSH component if you don't care about an encrypted tunnel.


You could also look into the mysqltookit (now called maatkit) at 
http://www.maatkit.org/ - there's a tool called mk-table-sync (see 
http://maatkit.sourceforge.net/doc/mk-table-sync.html for more info). 
This works a little differently to the SQLYob Job Agent so you might 
find it more suitable depending upon your application.


There's a nice comparison between these two solutions at 
http://www.xaprb.com/blog/2007/04/05/mysql-table-sync-vs-sqlyog-job-agent/


Hope this is helpful.

Ben


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

Cross database queries

[Ben Roberts]

Hello,

Does anybody have any info on cross-database joins?

i.e. doing a join across various tables that are located inside 
different databases (albeit on the same database server).


I'm interesested in any potential pitfalls, limitations, and performance 
issues. There doesn't appear to be much information about this on the 
web (at least not that I can find so far).


Does querying multiple databases in a single query count as multiple 
connections?


I'm using the InnoDB engine. Do foreign key constraints work across 
different databases too?


Is using cross-database joins considered good practice?

Thanks in advance for any help.

Ben


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

Cisco Access Registrar MySQL Cluster

[Ben Wiechman]

Cisco's Access Registrar supports MySQL 4.x. Does anyone have any experience
trying to make it work with MySQL Cluster?

 

Ben Wiechman

Network Admin

Wisper High Speed Internet

[EMAIL PROTECTED]

 

Re: external mysqldump

[Ben Clewett]

Try:

 mysql -u root -p 

mysql GRANT SUPER ON *.* TO myuser@'%';
mysql GRANT SUPER ON *.* TO myuser@'localhost';


Andre Hübner wrote:

Hi List,

i wrote this alrready in mysql-forum a few days ago, but did not get any 
answer. :(


i try to do backup with mysqldump from external host with routines.
mysqldump -R -h my.host.name -u myuser -p'mypass' mydb  filename.sql
I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION 
`countuser`!
It works if i do the same mysqldumLine directly on the server where db 
is installed.
In mysql-table myuser has same privileges for % and locklhost. Are there 
some further restrictions for external connects?

I dont have an idea what to change now.

Thank you
Andre



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

Re: external mysqldump

[Ben Clewett]

I use a specific user (backup) for my backups, therefore user privileges 
are not effected.  Is there something like this which you could use?


Andre Hübner wrote:

Thank you for answering.
is there a way to do without granting super-privileg?  for security 
reasons i cannot grant too high privileges for normal db-users.

rights should be limited to own db.
Thanks
Andre

- Original Message - From: Ben Clewett [EMAIL PROTECTED]
To: Andre Hübner [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 20, 2008 9:46 AM
Subject: Re: external mysqldump



Try:

 mysql -u root -p 

mysql GRANT SUPER ON *.* TO myuser@'%';
mysql GRANT SUPER ON *.* TO myuser@'localhost';


Andre Hübner wrote:

Hi List,

i wrote this alrready in mysql-forum a few days ago, but did not get 
any answer. :(


i try to do backup with mysqldump from external host with routines.
mysqldump -R -h my.host.name -u myuser -p'mypass' mydb  filename.sql
I got error: myuser has insufficent privileges to SHOW CREATE 
FUNCTION `countuser`!
It works if i do the same mysqldumLine directly on the server where 
db is installed.
In mysql-table myuser has same privileges for % and locklhost. Are 
there some further restrictions for external connects?

I dont have an idea what to change now.

Thank you
Andre





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

Re: Backup table structure, not data

[Ben Clewett]

# mysqldump --help

look for the flag --no-data

Ben

Esbach, Brandon wrote:

Is there any way to backup a complete database structure
(tables/fields/indexes/etc), without the data?  Or even get a creation
script per table?

At present the only way I can think of is to restore a backup to another
server and just delete records (a legacy database with data hitting over
12GB, might take some time) - but there's gotta be an easier way to do
it...



*
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

 Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
 Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
 Registered in England No: 02017435, Registered Address: Charter Court, 
 Midland Road, Hemel Hempstead,  Hertfordshire, HP2 5GE. 
*


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

Re: select unique ?

[Ben Clewett]

Try:

SELECT DISTINCT Colour FROM table;

Or, if you want to do it correctly:

SELECT Colour FROM table GROUP BY color;

Richard wrote:

Hello,

I don't know if it is possible to do this with mysql alone ...
Here goes :
I've got a database list which is like to following :

Num|Name|Colour
---
1|Harry|Red
2|Tom|Blue
3|Jane|Green
4|Philip|Red
5|Sarah|Red
6|Robert|Blue


And from this table I wish to get a list of used colours.

The correct answer would be :

Colour
-
Red
Blue
Green

The answer I don't want :

Colour

Red
Blue
Green
Red
Red
Blue

How would I achieve the first result with mysql ? Is it possible?


Thanks in advance,

Richard



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

Re: Access limited after restore

[Ben Clewett]

Hi Philip,

If you are worried about permissions, go to your mysql root directory 
and enter:


# chown -R mysql.mysql .

The UID and GID should not make any difference.  I don't think this 
would effect your ability to log in.


If this is a new installation of the server, check the default MySql 
config file does not clash with yours, from the file /etc/my.cnf


When you restored your database, did you restore it over the top of a 
default MySql installation?  Did your backup image contain a copy of the 
'mysql' database?  This database contains your passwords.  After 
over-writing the 'mysql' database, restart MySql.  If you have not 
over-written this database, then you have lost your users and 
permissions.  But you should be able to log in as 'root' with no password.


Also check your error.log.  Chances are this will tell you what is wrong :)

Ben



Philip Weingart wrote:

Hi, all,

I had a server crash a few weeks ago and had to
restore my mySQL installation from backup after
reinstalling Fedora.

After the restore operation, I was no longer able to
log into mySQL, either as root or as myself. I believe
this may be because the UID in the recovered database
is now different from the UID in my installation,
although I'm having a hard time believing root will
have a different UID after a standard installation.

I've been living with this for a while because my
daily operation doesn't require anything other than
the ability to write to the DB through Wordpress, and
that's working fine. 


However, today I attempted to delete a comment, and
found that I could not. So, now I have a reason to try
to fix this. Hence my questions:

1) Why would restoring from a backup wreck my ability
to log into the database?

2) How can I go about reestablishing access to mySQL
in a way that does not require blowing away all the
data and starting over?

Thanks.

Phil W.


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs




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

Disk Benchmark Tool

[Ben Clewett]

Dear List,

I am after a tool which benchmarks a file system in the same way that a 
MySql InnoDB system is likely to access it.


That is, small random reads and writes in a very large file.

For instance, the tool 'bonnie++' is no good for this as is concentrates 
too much on sequential read and write.


If any members can suggest what might be appropriate, I would be 
interested in knowing :)


Regards,

Ben Clewett.

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

Re: MySql at 100% CPU

[Ben Clewett]

No worries :)

 Good luck with your problem!

Apparently not, since no member of the MySql team wants to tell me and 
this mailing list what scenario's will cause a low-load MySql system 
lock at 100% CPU for hours on end.


Another of my servers has just entered this mode about half an hour ago. 
 I include the xload graph just to show I am not imagining this!


Another guess: these servers are quite new and growing at several gig a 
week.  Does InnoDB have some mode whereby it checks all tables after 
some threshold of time, size or row count?  Which is likely to take 
hours and put the CPU load at 100%?


If four of my ten MySql servers are doing this regularly, I can't be the 
only person with this problem.  Can somebody from MySql please let me 
know that on earth you are doing to my CPU's??


Regards,

Ben

BTW, who controls InnoDB?  Is it Oracle or Sun?


Kevin Hunter wrote:

At 12:08p -0500 on 17 Jan 2008, Ben Clewett wrote:
The package you show gives the same output as the 'SHOW PROCESSLIST' 
output, which I included in my previous email :)


Heh, that'll teach me to actually /use/ the package before recommending 
it. :-P  (I just found out about it when that article came out, and 
haven't yet had a chance to try it out.)


Good luck with your problem!

Kevin




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

MySql at 100% CPU

[Ben Clewett]

Dear MySql,

I hope you can help me trace a problem.

Every few days one of my 5.0.45 will stick at 100% CPU whilst not doing 
very much.


The server is a replication master and slave, but idle at this time.  I 
am running 100% InnoDB and there is nothing in the error log.


It will stay that way for minutes or hours, or until it is restarted. 
This load cannot be accounted for by the processlist.


I need to know why MySql does this.  If any person knows how I an 
identify the problem, please let me know!


Thanks!

Ben Clewett.


# top

top - 08:19:50 up 5 days,  9:52,  1 user,  load average: 1.40, 1.76, 1.09
Tasks: 129 total,   1 running, 128 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.3%us,  0.2%sy,  0.0%ni, 74.5%id,  0.0%wa,  0.0%hi,  0.0%si, 
0.0%st

Mem:  10235936k total,  9946508k used,   289428k free,   179584k buffers
Swap: 10490436k total,  124k used, 10490312k free,  5449908k cached

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
 6296 mysql 15   0 4163m 3.6g 5728 S  100 37.1 342:14.36 mysqld



mysql show processlist;

++-+--+---+-+--+---+--+
| Id | User| Host | db| 
Command | Time | State 
   | Info 
   |

++-+--+---+-+--+---+--+
| 386295 | system user |  | NULL  | 
Connect |  488 | Waiting for master to send event 
   | NULL 
   |
| 386296 | system user |  | NULL  | 
Connect |0 | Has read all relay log; waiting for the slave I/O 
thread to update it | NULL 
   |
| 386340 | slave   | hp-tm-07.roadtech.private:53998  | NULL  | 
Binlog Dump |  408 | Has sent all binlog to slave; waiting for binlog to 
be updated| NULL 
 |
| 386667 | root| localhost| NULL  | 
Query   |0 | NULL 
   | show processlist 
   |
| 386669 | webservice  | fls-16-05.roadtech.private:44277 | par_4 | 
Query   |1 | Sending data 
   | SELECT COUNT(*) FROM - WHERE comp_code = 
'---'  AND veh_code =  '---'  AND t BETWEEN   |

++-+--+---+-+--+---+--+




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

Re: slaves to specific tables

[Ben Clewett]

I do it all the time, look at:

--replicate-do-table

In:  http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

Section of my mysql.conf:

# Replication Options
replicate_do_table  = db.member
replicate_do_table  = db.company
replicate_do_table  = db.site
replicate_do_table  = (etc)

Ben

Paul Berry wrote:

hey guys - is it possible to have a lightweight slave setup? so that you
only take X amount of tables from the DB
curious what good solutios are out there





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

Re: MySql at 100% CPU

[Ben Clewett]

Hi Kevin,

Thanks for the advise.

The package you show gives the same output as the 'SHOW PROCESSLIST' 
output, which I included in my previous email :)


The problem I have is that the process list, by 'mytop' or 'SHOW 
PROCESSLIST' shows the server doing noting.


Yet 'top' and other tools show MySQL using 100% CPU

I really need any kind MySQL guru who can give me some advise on how to 
track down and stop my four MySQL servers doing what ever they doing...


I think it might be something to do with replication, as it's only my 
severs which are replication slaves and masters at the same time who 
tend to do this 100% CPU thing for hours on end...


Kind regards,

Ben


Kevin Hunter wrote:

At 3:25a -0500 on 17 Jan 2008, Ben Clewett wrote:
I need to know why MySql does this.  If any person knows how I an 
identify the problem, please let me know!


I think this package helped another person out on this list a couple 
days ago:


http://debaday.debian.net/2007/12/26/mytop-a-top-clone-for-mysql/

HTH,

Kevin



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

Odd item in MySql error log

[Ben Clewett]

MySql 5.0.41
SUSE 10.2
Linux dbms-04-r1 2.6.18.2-34-default #1 SMP Mon Nov 27 11:46:27 UTC 2006 
x86_64 x86_64 x86_64 GNU/Linux


Dear MySql,

I have an odd item in my error log, I wonder if you can tell me what 
this means?  Also whether should do anything about it?


(This table once corrupted before, I could not start MySql, and had 
several hours downtime recovering it...)



071219  0:00:15  InnoDB: Error: trying to declare trx to enter InnoDB, but
InnoDB: it already is declared.
TRANSACTION 0 3825296174, ACTIVE 0 sec, process no 6929, OS thread id 
1141946688, thread declared inside InnoDB 0

mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 16, query id 158 fls-16-03.roadtech.private 172.16.14.12 
sdi update
INSERT INTO terminal_log (mem_code, terminal_id, last_call, latitude, 
longitude, speed_knots, course, cablink_string, ip_addr, temperature) 
VALUES ( NULL , '35126600409511509' , '2007-12-18 23:59:58' , 
'5.150556400501e+01' , '-3.696216681689e-01' , 
'8.00016653e-02' , '1.0


Many thanks!

Ben Clewett.

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

InnoDB table which would not unlock

[Ben Clewett]

Dear MySql,

Using 5.0.41 I had a single innodb table which would not unlock.  I 
wonder if this might be a bug, or an issue that is known to be fixed in 
later versions?


Any DML like this example:

  UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running'

Would result in:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

However there were no visible locks:

show open tables like 'ws_queue';
+-+--++-+
| Database| Table| In_use | Name_locked |
+-+--++-+
| Web_Members | ws_queue |  0 |   0 |
+-+--++-+

This seems to be an error, and was fixed by bouncing the server, 
something I do not like doing in the middle of a working day.


At the time Innodb status showed the following.  Can any person help me 
understand what this is telling me?


---TRANSACTION 0 3683516087, ACTIVE 6 sec, process no 18537, OS thread 
id 1149135168 starting index read

mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216
MySQL thread id 2738280, query id 50900786 172.16.16.39 dba Updating
UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running'
--- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5619722 n bits 240 index `PRIMARY` of 
table `Web_Members/ws_queue` trx id 0 3683516087 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 20; compact format; 
info bits 0
 0: len 4; hex 0002b3f8; asc ;; 1: len 6; hex db8363ad; asc 
 c ;; 2: len 7; hex 1a87d72205; asc   ;; 3: len 1; hex 80; asc 
 ;; 4: len 1; hex 01; asc  ;; 5: len 4; hex 475fbf76; asc G_ v;; 6: len 
7; hex 63685f61637473; asc ch_acts;; 7: len 8; hex 3236434539424134; asc 
26CE9BA4;; 8: len 8; hex 00107523; asc   u#;; 9: len 4; hex 
8000; asc ;; 10: len 8; hex 4a414d455349524c; asc JAMESIRL;; 11: 
len 4; hex 800086bc; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; 
asc ;; 14: SQL NULL; 15: len 4; hex 8000; asc ;; 16: len 4; hex 
8001; asc ;; 17: len 1; hex 80; asc  ;; 18: len 4; hex 64626d73; 
asc dbms;; 19: len 1; hex 00; asc  ;;


Regards,

Ben Clewett.




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

Simple Query

[Ben Wiechman]

I need help writing what is probably a rather simple query.

 

I have two tables. The first contains several columns, but most importantly
an id column.

 

The second is has two columns, an id that corresponds with the id in the
first table, and a value.

 

For every row in the first table I'd like to insert a row into the second
with a set value.

 

I tried this but it is not working. I'm not very familiar with subqueries as
you can see.

 

insert into table_2 ( id, value ) values ( (select id from table_1), '1' );

 

Ben Wiechman

[EMAIL PROTECTED]

 

MySql priority

[Ben Clewett]

Sorry if this has been asked many times before...

I have 5 MySql systems running on a single server.

I want one to run with maximum priority, the other four to run with minimum.

Can this me archived using something as simple as 'nice':

# nice -10 mysqld --defaults-file=his_priorty
# nice 10 mysqld --defaults-file=low_priorty_1
# nice 10 mysqld --defaults-file=low_priorty_2
# nice 10 mysqld --defaults-file=low_priorty_3
# nice 10 mysqld --defaults-file=low_priorty_4

Or is there a better way that people use?

Thanks in advance...

Ben

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

Re: Field size for UTF-8 characters

[Ben Clewett]

From my experience with InnoDB,

IF the field is an index, it will use 3 bytes per character.  So 
VARCHAR(50) = 150 bytes, when fully populated.  (+ 1 for the length = 
151 bytes.)


IF the field is not an index, each character will consume between 1 and 
3 chars.  So VARCHAR(50) = 51 - 151 chars, when fully populated.


Ben

Cathy Murphy wrote:

I am limiting text to 50 chars in mysql field by varchar(50) ( UTF-8
enabled)
but what if the user enters 50 japanese chars, does mysql accomodate it OR
we have to consider some buffer during design ?




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

Replication constantly restarting

[Ben Clewett]

MySql,

I am running 5.0.41 on a master and four replication slaves, all 64 bit 
Linux.


In the error.log on all four replication servers I keep seeing:

070626  8:34:23 [Note] Slave: received end packet from server, apparent 
master shutdown:
070626  8:34:23 [Note] Slave I/O thread: Failed reading log event, 
reconnecting to retry, log 'dbms-04-r1.02' position 396367467
070626  8:34:23 [Note] Slave: connected to master 
'[EMAIL PROTECTED]:3306',replication resumed in log 
'dbms-04-r1.02' at position 396367467


Approximately once every second.  I have used another master server on 
AIX, same version, which does the same thing.  The servers are side by 
site on the same gigabit switch.


The replication does work, but it's a bit messy.

Any ideas how to get round this problem?

Ben




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

Re: Replication constantly restarting

[Ben Clewett]

Ravi,

Got it, thanks!!

Ben


Ravi Prasad wrote:

Make sure each of the replication slaves uses different server_id.

--Ravi


Ben Clewett wrote:

MySql,

I am running 5.0.41 on a master and four replication slaves, all 64 
bit Linux.


In the error.log on all four replication servers I keep seeing:

070626  8:34:23 [Note] Slave: received end packet from server, 
apparent master shutdown:
070626  8:34:23 [Note] Slave I/O thread: Failed reading log event, 
reconnecting to retry, log 'dbms-04-r1.02' position 396367467
070626  8:34:23 [Note] Slave: connected to master 
'[EMAIL PROTECTED]:3306',replication resumed in log 
'dbms-04-r1.02' at position 396367467


Approximately once every second.  I have used another master server on 
AIX, same version, which does the same thing.  The servers are side by 
site on the same gigabit switch.


The replication does work, but it's a bit messy.

Any ideas how to get round this problem?

Ben









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

can mysql use an index where like used

[Ben Edwards]

Can Mysql 4.20 use an index where like 'xx%' is in the where
clause against the column.

i.e. index table1(col1)

from table1
where table1.col1 like 'something%'

Regards,
Ben
--
Ben Edwards - Bristol, UK
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)

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

Getting list of queries run against a database

[Ben Edwards]

We are having a problem with out mysql database (4.2) and think we may
have indexes missing.  What we are trying to do is find out the most
popular queries that run.  We know there are not may and that they are
relatively simple.

Does anyone know of a tool that allows us to see what queries (i.e.
via a log file) are/have been run against the database.  If it counts
how may times/how much resources each query uses that would be good.
The icing on the cake would be a prog that told us what queries were
doing full table scans and other expensive operations.

Regards,
Ben
--
Ben Edwards - Bristol, UK
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)

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

Change in behaviour in version 5.0.41

[Ben Clewett]

Dear MySql,

I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 with 
date comparisons.


In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True.
In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False.
In 5.1.6-alpha:   '2007-06-15' = '2007-06-15 00:00:00' is True.

This has caused us a few problems.  Is this the way things should be, 
because this change does not seem right?


I am also very worried that this behaviour revert when we role out 5.1?

Does any member know whether this is a bug, or just an anoying feature?

Regards,

Ben


To Replicate:

CREATE TABLE t (d DATE);
INSERT INTO t VALUES ('2007-06-15');
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00';
+--+
| COUNT(*) |
+--+
|0 |
+--+
SELECT COUNT(*) FROM t WHERE d = '2007-06-15';
+--+
| COUNT(*) |
+--+
|1 |
+--+




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

Re: Innodb tablespace

[Ben Clewett]

 Hi all,

 Are there any reasons why one would NOT use separate ibd files for each
 table

Fragmentation for one.

A single file can re-use empty space from deleted rows for any added 
rows.  A single file can only re-use space from that one file.


Therefore the sum table size will be larger with many files.  Depending 
on how much data you regularly delete.


(Fragmentation also occurs when row sizes are increased.)

The file system is also an issue.  Lots of files require a good file 
system, like Reiserfs, and not FAT32.  A single file is just as 
efficient with any file system.


But I don't believe there are any IO difference.  The same number of 
file handles are used, whether they all access one file or 1000 files.


Personally I like a single file, it's easier to administer and HotBackup 
works.


But, if any IonnDB developers read this mailing list, we really need to 
be able to break this file using defined table space, as with Oracle. 
Then have as many/few files as we like


Ben




Olaf Stein wrote:

Hi all,

Are there any reasons why one would NOT use separate ibd files for each
table (--innodb_file_per_table). It seems logical to me to separate what
does not belong together logically (different databases), but I as the
shared tablespace is the default I wonder if it has nay advantages I am not
aware of

Thanks
Olaf





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

Re: Innodb tablespace

[Ben Clewett]

Olaf Stein wrote:

Hi all,

Are there any reasons why one would NOT use separate ibd files for each
table (--innodb_file_per_table). It seems logical to me to separate what
does not belong together logically (different databases), but I as the
shared tablespace is the default I wonder if it has nay advantages I am not
aware of


Fragmentation for one.

A single file can re-use empty space from deleted data for any added 
tables and rows.  A single file can only re-use space from that one file.


Therefore the sum table size will be larger with many files.  Depending 
on how much data you regularly delete.


(Fragmentation also occurs when row sizes are increased.)

There are file system problems as well.  Many files rely on a good file 
system, like Reiserfs, and not, say, Fat32.  Many files also result in a 
heavier hit on a journaling file system.  Important if you are using a 
lot of files, like many hundreds.


But I don't believe there is any IO difference.  The same number of file 
handles are used, whether they all access one file or 1000 files...


Also worth noting that an external single file is still used with 
file-per-table, which I suspect is used for referential constraint 
storage.  Therefore a complete division by database is not possible, all 
databases still use this one file.


Personally I like a single file, it's easier to administer and HotBackup 
works.


But, if any IonnDB developers read this mailing list, we really need to 
be able to break this file using defined table space, as with Oracle. 
Then have as many/few files as we like.


Ben




Thanks
Olaf





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

need help with delete query

[Ben Liu]

I'm trying to delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run a series
of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
 item_id
 ord_id

orders (order details such as person's name, address, phone number, etc)
 ord_id
 cust_id

I have set all shopping carts or initiated/incomplete orders so that
orders.cust_id=-1

I wish to clear all shopping carts so I need to delete two sets of items:
1) All records in order_items where order_items.ord_id=orders.ord_id and
orders.cust_id=-1
2) All records in orders where orders.cust_id=-1

I can do #2 easily enough, but I can't figure out how to write the delete
query for #1.

I've tried:

DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1

DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1


Neither of them seem to work.

Thanks for any help.

~Ben

Re: need help with delete query

[Ben Liu]

Thanks Brent, good tip. Works like a charm.

On Jun 14, 2007, at 7:42 PM, Brent Baisley wrote:

Here's a little trick. Get your DELETE query working as a SELECT.  
Then replace everything before FROM with DELETE tablename.


SELECT order_items.ord_id FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1

...becomes...

DELETE order_items FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1

You may have to tweak it a little, but the DELETE tablename FROM  
option is something many people miss. Although I'm pretty sure it's  
covered in the manual.



On 6/14/07, Ben Liu [EMAIL PROTECTED] wrote: I'm trying to  
delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run  
a series

of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
  item_id
  ord_id

orders (order details such as person's name, address, phone number,  
etc)

  ord_id
  cust_id

I have set all shopping carts or initiated/incomplete orders so that
orders.cust_id=-1

I wish to clear all shopping carts so I need to delete two sets  
of items:
1) All records in order_items where  
order_items.ord_id=orders.ord_id and

orders.cust_id=-1
2) All records in orders where orders.cust_id=-1

I can do #2 easily enough, but I can't figure out how to write the  
delete

query for #1.

I've tried:

DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1

DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1


Neither of them seem to work.

Thanks for any help.

~Ben




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

Re: MySql Host through Heartbeat

[Ben Clewett]

What I know is that:

Heartbeat with MySQL uses two IP's.  That of the server, and that of the 
resource MySql.  The former is fixed, the latter moves with MySQL when 
it's moved to another server.


The one I need is the hostname of the physical server, not the resource.

I've installed 5.0.41 and have found that the 'hostname' variable does 
report the hostname of the physical server.  I have no idea how it does 
it :)


I have my solution, thanks for the help,

Ben Clewett.



Baron Schwartz wrote:

Gerald L. Clark wrote:

Baron Schwartz wrote:

Gerald L. Clark wrote:


Ben Clewett wrote:


Dear MySQL,

I'm running 5.0.26 through Heartbeat.  Which seems to work well, 
even as a replication slave and Heartbeat continously stopping and 
starting the server.


The Heartbeat moves MySQL around from server to server when a 
failure occures.  I am trying to find a way for MySQL to report the 
server host name on which it's currently sitting.  Without any luck.


Would any kind members know of a way of getting this information 
from MySQL?


Many thanks,

Ben



Heartbeat moves the IP address around as well as the services.
Your hostname should not change.



DNS won't change, but the server's /etc/hostname will, right?

Disclaimer: I'm no expert on this... I didn't even know the IP 
address moved too.  I should read about Heartbeat.


Baron

Do you actually have /etc/hostname?
RHEL and Centos do not.
They do have an entry in /etc/sysconfig/network


Debian and Gentoo have /etc/hostname and /etc/conf.d/hostname, 
respectively.  I would think this is what /usr/bin/hostname uses, and 
probably where the hostname server variable gets set from in MySQL 5.0.41.


Baron




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

MySql Host through Heartbeat

[Ben Clewett]

Dear MySQL,

I'm running 5.0.26 through Heartbeat.  Which seems to work well, even as 
a replication slave and Heartbeat continously stopping and starting the 
server.


The Heartbeat moves MySQL around from server to server when a failure 
occures.  I am trying to find a way for MySQL to report the server host 
name on which it's currently sitting.  Without any luck.


Would any kind members know of a way of getting this information from MySQL?

Many thanks,

Ben


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

Re: MySql Host through Heartbeat

[Ben Clewett]

Baron,

Thanks for the fast reply.

I like the idea of piping in the servername to a small table on startup. 
 Since this will only change on startup, sounds like an excellent idea.


Or I may upgrade to above 5.0.41...

Regards,

Ben


Baron Schwartz wrote:

Hi,

Ben Clewett wrote:

Dear MySQL,

I'm running 5.0.26 through Heartbeat.  Which seems to work well, even 
as a replication slave and Heartbeat continously stopping and starting 
the server.


The Heartbeat moves MySQL around from server to server when a failure 
occures.  I am trying to find a way for MySQL to report the server 
host name on which it's currently sitting.  Without any luck.


Would any kind members know of a way of getting this information from 
MySQL?


I only know of two ways, though there may be more.

1) The hostname system variable, which was added in 5.0.41 
(http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-41.html)


2) Perhaps a UDF that makes a system call.

There might be some external ways to do it also.  For example, create a 
table with a single row, and have a startup script replace the value in 
it with the server's hostname upon startup.  Then you can query this value.


Cheers
Baron




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

mysqldump not consistent

[Ben Clewett]

Dear MySql,

Using 5.0.26 I am trying to get a consistent image of some tables using 
mysqldump.  This is for replication.  All my tables are InnoDB.


I am using:

# mysqldump h host \
--master-data=1 \
--single-transaction \
database

I was hoping that the '--master-data=1' would report the coordinates of 
the bin-log when the transaction was started.


But it seems to report the coordinates at the end of the dump. 
Therefore I am missing data.  My replication (most of the time) soon 
crashes with something like:


Cannot add or update a child row: a foreign key constraint fails

Of course I may be doing something wrong.

Would any person have a better idea of getting a consistent snapshot 
with correct coordinates?


Many thanks,

Ben.



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

Bash script array from MySQL query - HELP Please!!!

[Ben Benson]

 

I'm having problems getting a monitoring script to run.  

 

I've put the troublesome bit in a separate script just to test, and it goes
like this:

 

declare -a HNdeclares the array HN

 

HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to a
new element in the array

 

echo ${#HN} echo's number of elements in array

 

for ((i=0;i${#HN};i++)); do

 

echo ${HN[${i}]}  echo value of each element.

 

done

 

Seems simple enough yeah?! Well if I run echo SELECT url FROM hosts |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.

 

Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by 'url'
it seems to generate 569 elements! 

 

Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!

 

Many thanks in advance,

 

Ben Benson

 

Re: Database Replication Fallover

[Ben Clewett]

Hi Scott,

Thanks for the information.  I'll look into linux-ha seriously.

Regards,

Ben

Scott Tanner wrote:

Ben,
 
  Check out Linux-ha ( http://linux-ha.org). We are using version 1 at my company, which is fairly easy to set up and there are a lot of good articles on it.  With this you can create a virtual address to fail-over between systems, run commands / scripts during a fail over, and even kill the other node (stonith) if you have supporting hardware. 

  For our setup, we have the client apps accessing the db through the virtual IP address. We have 2 mysql config files, one for the master and one for the slave (actually we have one m4'd config file, but you get the idea). During failover, we restart mysql on the slave using the master config file and transfer the virtual IP over. 


  Here are a couple of key points for setting this up:

  1. Slave must be running with the 'log-slave-updates' option.
  2. After you run a back issue a reset master on the slave server. 



Hope this helps,
Scott Tanner



On Mon, Apr 02, 2007 at 08:43:35AM +0100, Ben Clewett wrote:

I forgot to mention that I am running Linux.

If anybody has some idea of software which can do this, I'd be very 
interested.


Regards,

Ben

Ben Clewett wrote:

Dear MySql,

I'm looking into availability and wonder if any member might be able to 
help me.


I have two databases, one Primary and one full Replication.

Normally the primary is used for data input, reports are drawn from the 
replication.


If I loose the Primary, do any members have any software they can 
recommend which:


- Stops the replication daemon.
- Sets the replication server to Read/Write.
- Shuts down the primary.
- Routes traffic to the replication.

Any advise or ideas would be very useful...

Regards,

Ben Clewett.






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









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

Re: Database Replication Fallover

[Ben Clewett]

I forgot to mention that I am running Linux.

If anybody has some idea of software which can do this, I'd be very 
interested.


Regards,

Ben

Ben Clewett wrote:

Dear MySql,

I'm looking into availability and wonder if any member might be able to 
help me.


I have two databases, one Primary and one full Replication.

Normally the primary is used for data input, reports are drawn from the 
replication.


If I loose the Primary, do any members have any software they can 
recommend which:


- Stops the replication daemon.
- Sets the replication server to Read/Write.
- Shuts down the primary.
- Routes traffic to the replication.

Any advise or ideas would be very useful...

Regards,

Ben Clewett.







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

Database Replication Fallover

[Ben Clewett]

Dear MySql,

I'm looking into availability and wonder if any member might be able to 
help me.


I have two databases, one Primary and one full Replication.

Normally the primary is used for data input, reports are drawn from the 
replication.


If I loose the Primary, do any members have any software they can 
recommend which:


- Stops the replication daemon.
- Sets the replication server to Read/Write.
- Shuts down the primary.
- Routes traffic to the replication.

Any advise or ideas would be very useful...

Regards,

Ben Clewett.




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

Make problem

[POON Ben]

Hi,
 
I'm trying to install mysql on solaris 10, sparc.  I'm using gcc 3.4.6.
I downloaded the source, and did the configure using the recommended:
 
CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors \
-fno-exceptions -fno-rtti ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static
 
Then I went ahead and tried a make, i get this:
make[3]: *** [libedit.a] Error 1
make[3]: Leaving directory
`/tmp/ben/mysql-5.0.37/cmd-line-utils/libedit'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/tmp/ben/mysql-5.0.37/cmd-line-utils'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/tmp/ben/mysql-5.0.37'
 
I tried looking in the config.log file, i *think* these are the problem
parts...
 
configure:4135: gcc -c -O3conftest.c 5
conftest.c:2: error: syntax error before me
configure:4141: $? = 1
configure: failed program was:
| #ifndef __cplusplus
|   choke me
| #endif

conftest.cc: In function `int main()':
conftest.cc:31: error: `exit' was not declared in this scope
configure:4769: $? = 1
configure: failed program was:
| /* confdefs.h.  */

configure:23124: checking term.h usability
configure:23136: gcc -c -O3conftest.c 5
In file included from conftest.c:103:
/usr/include/term.h:1034: error: syntax error before bool
/usr/include/term.h:1060: error: syntax error before SGTTY
/usr/include/term.h:1081: error: syntax error before sgr_mode
/usr/include/term.h:1082: error: syntax error before sgr_faked
/usr/include/term.h:1092: error: syntax error before funckeystarter
/usr/include/term.h:1094: error: syntax error before _fl_rawmode
/usr/include/term.h:1101: error: syntax error before _input_queue
/usr/include/term.h:1108: error: syntax error before '*' token
/usr/include/term.h:: error: syntax error before bit_vector
/usr/include/term.h:1115: error: syntax error before check_turn_off
/usr/include/term.h:1116: error: syntax error before non_faked_mode
/usr/include/term.h:1117: error: syntax error before _cur_pair
/usr/include/term.h:1118: error: syntax error before '*' token
/usr/include/term.h:1120: error: syntax error before '}' token
/usr/include/term.h:1207: error: syntax error before int
/usr/include/term.h:1207: error: `vidputs' declared as function
returning a function
/usr/include/term.h:1207: error: syntax error before ')' token
configure:23142: $? = 1

configure:6554: gcc -E  conftest.cc
conftest.cc:41:28: ac_nonexistent.h: No such file or directory

configure:23124: checking select.h usability
configure:23136: gcc -c -O3conftest.c 5
conftest.c:90:20: select.h: No such file or directory
configure:23142: $? = 1

configure:23124: checking sys/vadvise.h usability
configure:23136: gcc -c -O3conftest.c 5
conftest.c:102:25: sys/vadvise.h: No such file or directory
configure:23142: $? = 1

and a whole bunch more...
But it appears that this conftest and confdefs thing show up a lot...
Is there something I did wrong? or am I even looking at the right
things?  Sorry to ask so many questions.  I just started to use unix and
compiling and installing...  If i'm posting in the wrong mailing list,
please let me know where I should post.
 
Thank you very much,
Ben

Don't have libmysqlclient.so

[POON Ben]

Hi everyone,
 
I've just installed MySQL 5.0.37 from source.  One of my other programs
require libmysqlclient.so.10  I was under the impression that if I
installed MySQL from source, it will be made.  I looked at
/usr/local/lib and there is no libmysqlclient.so.10 or even a MySQL
folder...
 
Can anyone tell me how i can get it or where I can find it?  I am using
sparc solaris 10.
 
Thank you very much,
Ben

MySql locking during BLOB upload

[Ben Clewett]

Dear MySql,

I am finding my version 5.1.6 and 4.1.9 sometimes locking up during the 
passing of BLOB data.  Size between 25 and 250 KB.


If I repeat the transaction it will pass.

Statistically it occurs about 1 in 30 INSERTs or UPDATEs or a blob.

Non-Blob data to the same table will never lock.

mysqladmin will show an entry 'Sleep'.

The only ever solution is to kill my connection and try again.

There is no one table, no one server and no one version of MySql which 
does this, and the problem is not repeatable.


Has anybody experienced this and maybe knows what I can do?

Regards,

Ben


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

Re: Query Cache

[Ben Clewett]

Chris,

Thanks for clearing that up.  You are right, I don't want the general 
log or the mysql shell history.


I'm not happy that I can't get at the Query Cache.  I am sure there are 
tools which MySql developers must use to test the cache.  I'll have to 
look at the source code directories...


Regards,

Ben

Chris wrote:

Mohsen Pahlevanzadeh wrote:

Ben Clewett wrote:


Hi Mohsen,

Thanks for the reply.

I'm looking in the home directory, as well as the other directories 
used by mysql and I can't see any files which I do not recognise.  (I 
am UNIX so there are no hidden files.)


I am also a little confuses as the Query Cache is entirely a memory 
structure, does it use a file?


You're talking about different things.

Mohsen thinks you want to see the the last queries that were run, which 
are stored in the ~/.mysql_history file.


Ben wants to see what queries are stored in the query cache 
(http://dev.mysql.com/doc/refman/4.1/en/query-cache.html).


I don't think you can get a list of queries that mysql has stored in the 
cache.





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

Re: deadlocks on concurrent INSERTS

[Ben de Luca]

What does a `show innodb status show`?


On 07/12/2006, at 2:44 AM, James Neff wrote:


Greetings,

I've got 6 Java client applications (running on 6 different PC's,  
including one of them on the database server) reading data from a  
flat file and inserting data into a table on my database:



mysqld Ver 5.1.12-beta-log for unknown-linux-gnu on x86_64 (MySQL  
Community Server (GPL))



There are total about 12 million lines of data to read from these  
flat files and INSERT into a Innodb table on the database. Each  
client application has 20 threads running which run some validation  
code on the record and then perform the insert. I have not exceeded  
my max_connections.


Around insert number 450,000 some of the clients started getting  
the following error:
Exception in Processor.DataArchiveInsert, trying to insert into  
data_archve, e:
java.sql.SQLException: Lock wait timeout exceeded; try restarting  
transaction


The 4 clients which did not recieve the error have stopped (but  
have not thrown errors) and when I view the connections from MySQL  
Administrator I see 4 INSERT statements to the same table just  
which have been running for 40926 seconds.


The code for this is a simple INSERT statement in Java :

mysqlStatement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
   ResultSet rsRecordsInsert =  
mysqlStatement.getGeneratedKeys();

   while (rsRecordsInsert.next())
 {
   recordID = rsRecordsInsert.getInt(1);
   }
  
rsRecordsInsert.close();



There are no errors in the databases error log nor anything in the  
slow log.


I don't even know what exactly is wrong here other than it ain't  
working.
Can someone lead me down the path to diagnose this problem and also  
fix it?


Thanks in advance,

--

James Neff


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




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

Re: Query Cache

[Ben Clewett]

Hi Mohsen,

Thanks for the advise.  On my AIX system as root, dot files not hidden. 
 For instance my mysql home directory looks like:


drwxr-xr-x  16 mysqlmysql  4096 04 Dec 20:18 .
drwxr-xr-x  31 root system 4096 09 Nov 15:12 ..
drwxr-xr-x   2 mysqlmysql  4096 13 Oct 2005  bin
drwxr-xr-x   3 mysqlmysql   256 04 Dec 20:05 etc
drwxr-xr-x   2 mysqlmysql   256 13 Oct 2005  info
drwxr-xr-x   3 mysqlmysql   256 07 Oct 2005  lib
drwxr-xr-x   2 mysqlmysql   256 13 Oct 2005  libexec
drwxr-xr-x   3 mysqlmysql  4096 04 Dec 23:50 logs
drwxr-xr-x   2 mysqlmysql   256 07 Oct 2005  lost+found
drwxr-xr-x   3 mysqlmysql   256 12 Oct 2005  man
-rw-rw   1 mysqlstaff 7 04 Dec 20:18 mysql.pid
srwxrwxrwx   1 mysqlstaff 0 04 Dec 20:18 mysql.sock
drwxr-xr-x   6 mysqlmysql  4096 13 Oct 2005  mysql-test
drwxr-xr-x   3 mysqlmysql   256 07 Oct 2005  share
drwxr-xr-x   5 mysqlmysql  4096 13 Oct 2005  sql-bench
drwxr-xr-x   2 mysqlmysql   256 05 Dec 10:23 tmp

There is no file for the Query Cache.

I am sure that since this is a memory structure, there must be a command 
in MySql or an admin tool which can be used to expose the Queries.


If anybody knows this really would be very useful.

Regards, Ben.


Mohsen Pahlevanzadeh wrote:

Ben Clewett wrote:


Hi Mohsen,

Thanks for the reply.

I'm looking in the home directory, as well as the other directories 
used by mysql and I can't see any files which I do not recognise.  (I 
am UNIX so there are no hidden files.)


I am also a little confuses as the Query Cache is entirely a memory 
structure, does it use a file?


If you have the name of the Query Cache file, this would be very useful.

Regards,

Ben.


Mohsen Pahlevanzadeh wrote:

Ben Clewett wrote:


Dear MySql,

Can you please tell me if there is a tool to view the queries stored 
in the Query Cache?


Regards,

Ben Clewett.


Yes,If you see to mysql homeDIR,You see a hidden file that it's 
contain of your queries.






Hidden files are started with a dot in UNIX world.
If you see them,You must type ls -a
--Mohsen





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