Re: does anyone else have problems sending mails to this list ?

[Reindl Harald]

Am 22.03.2016 um 02:05 schrieb Jan Steinman:

please refrain from answering when you have no clue how DNS blacklists are 
working and what about others are talking


Well, excuse me! Someone put on their bossy pants this morning!

A simple “That’s not what’s going on here” would have been much nicer


deserved by what when one is answering to a over days ongoing thread 
without read what's topic?




signature.asc
Description: OpenPGP digital signature

Re: does anyone else have problems sending mails to this list ?

[Jan Steinman]

> please refrain from answering when you have no clue how DNS blacklists are 
> working and what about others are talking

Well, excuse me! Someone put on their bossy pants this morning!

A simple “That’s not what’s going on here” would have been much nicer.

J


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

Re: does anyone else have problems sending mails to this list ?

[Reindl Harald]

Am 20.03.2016 um 20:22 schrieb Reindl Harald:



Am 20.03.2016 um 20:16 schrieb Jan Steinman:

From: Reindl Harald 
Date: 17 March 2016 at 06:27:22 PDT

Am 17.03.2016 um 14:06 schrieb Lentes, Bernd:

i still can't send my question to the ML. Our outgoing Mailer has a
new IP which is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
our domain is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
my E-Mail include neither any link nor an attachment, it's formatted
as plain-text but i still get it back:


at least not terrible good
senderscore.com LISTED 127.0.4.63


Uhm… the 127 Class A network is the “super local network,” used for
processes on the same physical machine.

You need to look at other Received: headers than the ones that start
with 127


please refrain from answering when you have no clue how DNS blacklists
are working and what about others are talking - thank you!

here is your homework for today:
https://en.wikipedia.org/wiki/DNSBL

BTW: one of my jobs is to build up spamfilters for hundrets of users
including maintain own local DNSBL/DNSWL services for scoring


if the wiki page is to long for you:
https://www.spamhaus.org/zen/

Return CodesData Source Contains
127.0.0.2   Direct UBE sources, spam operations & spam services
127.0.0.3   Direct snowshoe spam sources detected via automation
127.0.0.4-7 CBL (3rd party exploits such as proxies, trojans, etc.)
127.0.0.10-11 		End-user Non-MTA IP addresses set by ISP outbound mail 
policy




signature.asc
Description: OpenPGP digital signature

Re: does anyone else have problems sending mails to this list ?

[Reindl Harald]

Am 20.03.2016 um 20:16 schrieb Jan Steinman:

From: Reindl Harald 
Date: 17 March 2016 at 06:27:22 PDT

Am 17.03.2016 um 14:06 schrieb Lentes, Bernd:

i still can't send my question to the ML. Our outgoing Mailer has a new IP 
which is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
 our domain is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
 my E-Mail include neither any link nor an attachment, it's formatted as plain-text 
but i still get it back:


at least not terrible good
senderscore.com LISTED  127.0.4.63


Uhm… the 127 Class A network is the “super local network,” used for processes 
on the same physical machine.

You need to look at other Received: headers than the ones that start with 127


please refrain from answering when you have no clue how DNS blacklists 
are working and what about others are talking - thank you!


here is your homework for today:
https://en.wikipedia.org/wiki/DNSBL

BTW: one of my jobs is to build up spamfilters for hundrets of users 
including maintain own local DNSBL/DNSWL services for scoring




signature.asc
Description: OpenPGP digital signature

Re: does anyone else have problems sending mails to this list ?

[Jan Steinman]

> From: Reindl Harald 
> Date: 17 March 2016 at 06:27:22 PDT
> 
> Am 17.03.2016 um 14:06 schrieb Lentes, Bernd:
>> i still can't send my question to the ML. Our outgoing Mailer has a new IP 
>> which is not listed
>> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
>>  our domain is not listed
>> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
>>  my E-Mail include neither any link nor an attachment, it's formatted as 
>> plain-text but i still get it back:
> 
> at least not terrible good
> senderscore.com LISTED127.0.4.63

Uhm… the 127 Class A network is the “super local network,” used for processes 
on the same physical machine.

You need to look at other Received: headers than the ones that start with 127.

Jan


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

Re: does anyone else have problems sending mails to this list ?

[Lentes, Bernd]

Hi,

i still can't send my question to the ML. Our outgoing Mailer has a new IP 
which is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
 our domain is not listed 
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
 my E-Mail include neither any link nor an attachment, it's formatted as 
plain-text but i still get it back:

"This is the mail system at host mtaextp1.scidom.de.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

   The mail system

: host lists-mx.mysql.com[137.254.60.71] said: 552 spam
score exceeded threshold (#5.6.1) (in reply to end of DATA command)"

Can anyone points out the error ?

Thanks.


Bernd



-- 
Bernd Lentes 

Systemadministration 
institute of developmental genetics 
Gebäude 35.34 - Raum 208 
HelmholtzZentrum München 
bernd.len...@helmholtz-muenchen.de 
phone: +49 (0)89 3187 1241 
fax: +49 (0)89 3187 2294 

Wer Visionen hat soll zum Hausarzt gehen 
Helmut Schmidt

- Am 9. Mrz 2016 um 12:56 schrieb Reindl Harald h.rei...@thelounge.net:

> Am 09.03.2016 um 11:44 schrieb Lentes, Bernd:
>> i tried yesterday several times to send an e-Mail to this list. E-Mail is
>> formatted as plain-text, has no links included and no attachment but is
>> declined as spam. This is what is bounced:
>>
>> : host lists-mx.mysql.com[137.254.60.71] said: 552 
>> spam
>>  score exceeded threshold (#5.6.1) (in reply to end of DATA command)
>>
>> Any ideas?
> 
> surely:
> 
> spam.dnsbl.sorbs.net  LISTED  127.0.0.6
> bl.spamcannibal.org   LISTED  127.0.0.2
> 
> Received-SPF: none (helmholtz-muenchen.de: No applicable sender policy
>  available) receiver=amysql-list-wsv01.oracle.com; identity=mailfrom;
>  envelope-from="bernd.len...@helmholtz-muenchen.de";
>  helo=mtaextp1.scidom.de; client-ip=146.107.3.105
> 
> PTR: mtaextp1.scidom.de
> Country: DE (Germany)
> ISP: Helmholtz Zentrum Muenchen


Helmholtz Zentrum Muenchen
Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH)
Ingolstaedter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe
Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen 
(komm.)
Registergericht: Amtsgericht Muenchen HRB 6466
USt-IdNr: DE 129521671


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

Re: does anyone else have problems sending mails to this list ?

[Reindl Harald]

Am 17.03.2016 um 14:06 schrieb Lentes, Bernd:

i still can't send my question to the ML. Our outgoing Mailer has a new IP 
which is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#),
 our domain is not listed
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#),
 my E-Mail include neither any link nor an attachment, it's formatted as plain-text 
but i still get it back:


at least not terrible good
senderscore.com LISTED  127.0.4.63

would be 1.0 SpamAssasin points on our setup, not much but not a good 
sign, hence we use 50 DNSBL/DNSWL with different scores and it's worth 
to catch most spam while prevent false positives



"This is the mail system at host mtaextp1.scidom.de.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

The mail system

: host lists-mx.mysql.com[137.254.60.71] said: 552 spam
 score exceeded threshold (#5.6.1) (in reply to end of DATA command)"

Can anyone points out the error ?


not without the full message

try to send me the same mail (subject, content, attachments whatever) 
and i can bounce you back a SpamAssassin report




signature.asc
Description: OpenPGP digital signature

Re: does anyone else have problems sending mails to this list ?

[Reindl Harald]

Am 09.03.2016 um 11:44 schrieb Lentes, Bernd:

i tried yesterday several times to send an e-Mail to this list. E-Mail is 
formatted as plain-text, has no links included and no attachment but is 
declined as spam. This is what is bounced:

: host lists-mx.mysql.com[137.254.60.71] said: 552 spam
 score exceeded threshold (#5.6.1) (in reply to end of DATA command)

Any ideas?


surely:

spam.dnsbl.sorbs.netLISTED  127.0.0.6
bl.spamcannibal.org LISTED  127.0.0.2

Received-SPF: none (helmholtz-muenchen.de: No applicable sender policy
 available) receiver=amysql-list-wsv01.oracle.com; identity=mailfrom;
 envelope-from="bernd.len...@helmholtz-muenchen.de";
 helo=mtaextp1.scidom.de; client-ip=146.107.3.105

PTR: mtaextp1.scidom.de
Country: DE (Germany)
ISP: Helmholtz Zentrum Muenchen




signature.asc
Description: OpenPGP digital signature

Re: does anyone else have problems sending mails to this list ?

[Olivier Nicole]

Bernd,

> : host lists-mx.mysql.com[137.254.60.71] said: 552 spam
> score exceeded threshold (#5.6.1) (in reply to end of DATA command)
>
> Any ideas ?

Whatever provider you are using to send mail has been blacklisted?

It happens with some ISP who are not diligent enough to remove the
spammers from their networks, legitimate customers get penalized.

Best regards,

Olivier

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

does anyone else have problems sending mails to this list ?

[Lentes, Bernd]

Hi,

i tried yesterday several times to send an e-Mail to this list. E-Mail is 
formatted as plain-text, has no links included and no attachment but is 
declined as spam. This is what is bounced:

: host lists-mx.mysql.com[137.254.60.71] said: 552 spam
score exceeded threshold (#5.6.1) (in reply to end of DATA command)

Any ideas ?

Thanks.


Bernd

-- 
Bernd Lentes 

Systemadministration 
institute of developmental genetics 
Gebäude 35.34 - Raum 208 
HelmholtzZentrum München 
bernd.len...@helmholtz-muenchen.de 
phone: +49 (0)89 3187 1241 
fax: +49 (0)89 3187 2294 

Wer Visionen hat soll zum Hausarzt gehen 
Helmut Schmidt


Helmholtz Zentrum München


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

FW: MySQL Problems

[Hans Scheffers]

Hi,


We
 are running on opensuse that defaults to a mariadb instead of mysql 
install, so we were running icinga 1.9.2 / idoutils 1.9.2 on a opensuse 
ppc system, with mariadb 5.5.33. This generated a lot of errors in de 
database (with restarts):

InnoDB: Assertion failure in thread 70366933971360 in file buf0buf.ic line 1277

Some googling showed this could be a problem in Percona XtraDB, which is as 
INNODB replacement in MariaDB.

Next
 step: install MySQL Server instead of MariaDB. We were able to install 
MySQL-5.5.6 in PPC, and the number of MySQL crashes went down to 1 in 2 
days now (instead of 8 crashes a night). For now this is acceptable, and
 we started with icinga-web on a separate system. We now have the 
following setup:

LPAR 1 (OpenSuSE 13.1) - Icinga /idoutils (1.9.2)
LPAR 2 (OpenSuSE 13.1) - MySQL 5.5.6
X86 (OpenSuSE 12.2) - Icinga-Web 1.9.2

When
 we log in in icinga-web we get a screen showing we don't have enough 
permissions, and no servers show up. After debugging, googling a.s.o. I 
found  that if we install the databases on mariaDB 5.5.33 (local on 
X86), we get all the hosts  services.
Installing icinga-web db local, and icinga db remote (LPAR2), we can see the 
hosts, but not the services.

I tried the following statement in the different DB's (X86 DB is a restore of  
the LPAR2 dump):

LPAR2:
MySQL
 [nagios] select count(*) from icinga_hosts join icinga_objects on 
icinga_hosts.host_object_id=icinga_objects.object_id join 
icinga_hoststatus on 
icinga_hoststatus.host_object_id=icinga_objects.object_id;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)

X86:
mysql
 select count(*) from icinga_hosts join icinga_objects on 
icinga_hosts.host_object_id=icinga_objects.object_id join 
icinga_hoststatus on 
icinga_hoststatus.host_object_id=icinga_objects.object_id;
+--+
| count(*) |
+--+
|  153 |
+--+
1 row in set (0.00 sec)

Both statements are executed as root user in mysql (so all grants should be OK) 
and are on the same database.
The only difference I see is the mysql / mariadb version, it looks like the 
syntax in the newer mysql has been changed.

Anyone can shed a light on this ;)

Hans Scheffers

AIX / Linux Systeembeheer

  

RE: Concurrent read performance problems

[Rick James]

Please provide SHOW CREATE TABLE and SHOW TABLE STATUS for each table.
It smells like there is an inconsistency in the datatype of facts.accounts.id 
and what it is JOINing to.

Also provide the full SELECT.
How much RAM do you have?

 -Original Message-
 From: Johan De Meersman [mailto:vegiv...@tuxera.be]
 Sent: Sunday, August 11, 2013 2:16 PM
 To: Brad Heller
 Cc: Johnny Withers; MySQL General List
 Subject: Re: Concurrent read performance problems
 
 Good to hear. A word of warning, though: make sure you don't have more
 connections allocating those buffers than your machine can handle memory-
 wise, or you'll start swapping and performance will REALLY go down the
 drain.
 
 A query/index based solution would still be preferred. Could you for
 instance materialize that subselect and periodically refresh it? Other
 tricks may also be available.
 
 Brad Heller b...@cloudability.com wrote:
 Johan, your suggestion to tweak max_heap_table_size and tmp_table_size
 fixed the issue. Bumping them both to 512MB got our performance back
 on-par. I came up with a way to avoid the contention using a complex
 set of temp tables, but performance was abysmal.
 
 By reverting to the more straight-forward query with the subselect as
 well as tweaking the max_healp_table_size and tmp_table_size I saw no
 resource contention causing slowdowns, as well as a 12x performance
 boost.
 Thanks
 for your help!
 
 *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 |
 Skype:
 brad.heller | @bradhe http://www.twitter.com/bradhe |
 @cloudabilityhttp://www.twitter.com/cloudability
 
 
 On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman
 vegiv...@tuxera.bewrote:
 
  True, which is why I said I suspected file-based sort :-) At one
 million
  rows, that seems to be an accurate guess, too. Still on the phone,
 though,
  and in bed. I'll read the thread better tomorrow, but you might get
 some
  benefit from cutting out the subselect if that's possible.
 
  If you have plenty of memory, have a look at the max_temp_table_size
 and
  max_heap_table variables, too; those decide when the sort goes to
 disk.
 
 
  Johnny Withers joh...@pixelated.net wrote:
 
  Just because it says filrsort doesn't mean it'll create a file on
 disk.
  Table schema and full query would be helpful here too
 
 
 http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort
 -mean-in-mysql/
  On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com
 wrote:
 
   Yes sorry, here's the explain. It was taken from MariaDB 5.5.32.
 Looks like
   there is a lot of filesort goin' on here. Also note that I'm only
 using the
   first two fields of the covering index (intentionally).
 
 
   +--+-++---+
  --
  ++-+
  --
  +-+
  --
  +
   | id   | select_type | table  | type  |
  possible_keys
  | key| key_len | ref
 | rows| Extra
   |
 
   +--+-++---+
  --
  ++-+
  --
  +-+
  --
  +
   |1 | PRIMARY | derived2 | ALL   | NULL
   | NULL   | NULL| NULL
  | 1004685 | Using temporary;
 Using
   filesort   |
   |2 | DERIVED | accounts   | range |
   PRIMARY,unique_account_identifiers | unique_account_identifiers |
 257 |
   NULL  |   3 |
 Using
   where; Using index; Using temporary; Using filesort |
   |2 | DERIVED | facts  | ref   | covering
   | covering   | 4   |
   facts.accounts.id|  334895 |
 Using
   where
 |
 
   +--+-++---+
  --
  ++-+
  --
  +-+
  --
  +
 
 
   *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514
 |
   Skype:
   brad.heller | @bradhe http://www.twitter.com/bradhe |
  @cloudabilityhttp://www.twitter.com/cloudability
 
 
   On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman
 vegiv...@tuxera.be
 
  wrote:
 
 
   On my phone now, but it smells of file-based sorting, making disk
 access
   the bottleneck. Can you provide the explain?
 
 
   Brad Heller b...@cloudability.com wrote:
 
 
   Hey list, first time posting here so apologies if this is the
 wrong
 
   forum
 
   for this but I'm really out of options on how to solve this
 problem!
 
   *Short version

Re: Concurrent read performance problems

[Johan De Meersman]

On my phone now, but it smells of file-based sorting, making disk access the 
bottleneck. Can you provide the explain?

Brad Heller b...@cloudability.com wrote:
Hey list, first time posting here so apologies if this is the wrong
forum
for this but I'm really out of options on how to solve this problem!

*Short version:*

1. High concurrent reads, performing the same well-indexed query type
to
the same two tables.
2. No additional traffic at all--just reads from these two tables. No
writes anywhere.
3. Very fast (sub-second) when server is only servicing one request at
a
time.
4. Very slow (30s+) when server is servicing 2-3 requests at a time.

Questions:

1. What resource is under contention for servicing read-only queries if
you
have a large buffer pool and a plenty-big thread cache?
2. What parameters can I tune to increase concurrent reads to these two
tables?

*Long version:*

I've got a MySQL server that has only about 50 connections open to it
at
any given time. It basically only has one OLAP query type being ran
against
it that amounts to something like this:

SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate)
FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;

These queries are well indexed and run very well
individually--sub-second,
usually even faster. When I run a few of these queries simultaneously
(2-3
on my laptop, 6-7 on our production boxes) performance grinds to a
halt:
Consistently about 30 seconds to service a query.

Ideally, I'd like to be able to run 50+ of these queries concurrently.

I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on
both
machines. I've tried tweaking the following my.cnf parameters to be
higher:

thread_concurrency = 20
thread_cache_size = 340
innodb_buffer_pool_size=2G

A few other my.cnf parameters that I have set:

innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
innodb_file_per_table=1
skip-external-locking
innodb_log_files_in_group=2
innodb_log_file_size=2000M
max_allowed_packet=64M

Thanks in advance,

Brad Heller

-- 
Sent from Kaiten Mail. Please excuse my brevity.

Re: Concurrent read performance problems

[Brad Heller]

Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like
there is a lot of filesort goin' on here. Also note that I'm only using the
first two fields of the covering index (intentionally).

+--+-++---+++-+---+-+---+
| id   | select_type | table  | type  | possible_keys
   | key| key_len | ref
  | rows| Extra
|
+--+-++---+++-+---+-+---+
|1 | PRIMARY | derived2 | ALL   | NULL
| NULL   | NULL| NULL
   | 1004685 | Using temporary; Using
filesort   |
|2 | DERIVED | accounts   | range |
PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 |
NULL  |   3 | Using
where; Using index; Using temporary; Using filesort |
|2 | DERIVED | facts  | ref   | covering
| covering   | 4   |
facts.accounts.id|  334895 | Using
where
  |
+--+-++---+++-+---+-+---+


*Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype:
brad.heller | @bradhe http://www.twitter.com/bradhe |
@cloudabilityhttp://www.twitter.com/cloudability


On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.bewrote:

 On my phone now, but it smells of file-based sorting, making disk access
 the bottleneck. Can you provide the explain?


 Brad Heller b...@cloudability.com wrote:

 Hey list, first time posting here so apologies if this is the wrong forum
 for this but I'm really out of options on how to solve this problem!

 *Short version:*


 1. High concurrent reads, performing the same well-indexed query type to
 the same two tables.
 2. No additional traffic at all--just reads from these two tables. No
 writes anywhere.
 3. Very fast (sub-second) when server is only servicing one request at a
 time.
 4. Very slow (30s+) when server is servicing 2-3 requests at a time.

 Questions:

 1. What resource is under contention for servicing read-only queries if you
 have a large buffer pool and a plenty-big thread cache?
 2. What parameters can I tune to increase concurrent reads to these two
 tables?

 *Long version:*


 I've got a MySQL server that has only about 50 connections open to it at
 any given time. It basically only has one OLAP q
  uery
 type being ran against
 it that amounts to something like this:

 SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate)
 FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;

 These queries are well indexed and run very well individually--sub-second,
 usually even faster. When I run a few of these queries simultaneously (2-3
 on my laptop, 6-7 on our production boxes) performance grinds to a halt:
 Consistently about 30 seconds to service a query.

 Ideally, I'd like to be able to run 50+ of these queries concurrently.

 I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both
 machines. I've tried tweaking the following my.cnf parameters to be higher:

 thread_concurrency = 20
 thread_cache_size = 340
 innodb_buffer_pool_size=2G

 A few other my.cnf parameters that I have set:

 innodb_file_format=Barracuda
 innodb_file_format_max=Barracuda
 innodb_file_per_table=1
 skip-external-locking
 innodb_log_files_in_group=2
 innodb_log_file_size=2000M
 max_allowed_packet=64M

 Thanks in advance,

 Brad Heller


 --
 Sent from Kaiten Mail. Please excuse my brevity.

Re: Concurrent read performance problems

[Johnny Withers]

Just because it says filrsort doesn't mean it'll create a file on disk.
Table schema and full query would be helpful here too

http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/
On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote:

 Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like
 there is a lot of filesort goin' on here. Also note that I'm only using the
 first two fields of the covering index (intentionally).


 +--+-++---+++-+---+-+---+
 | id   | select_type | table  | type  | possible_keys
| key| key_len | ref
   | rows| Extra
 |

 +--+-++---+++-+---+-+---+
 |1 | PRIMARY | derived2 | ALL   | NULL
 | NULL   | NULL| NULL
| 1004685 | Using temporary; Using
 filesort   |
 |2 | DERIVED | accounts   | range |
 PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 |
 NULL  |   3 | Using
 where; Using index; Using temporary; Using filesort |
 |2 | DERIVED | facts  | ref   | covering
 | covering   | 4   |
 facts.accounts.id|  334895 | Using
 where
   |

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


 *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 |
 Skype:
 brad.heller | @bradhe http://www.twitter.com/bradhe |
 @cloudabilityhttp://www.twitter.com/cloudability


 On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be
 wrote:

  On my phone now, but it smells of file-based sorting, making disk access
  the bottleneck. Can you provide the explain?
 
 
  Brad Heller b...@cloudability.com wrote:
 
  Hey list, first time posting here so apologies if this is the wrong
 forum
  for this but I'm really out of options on how to solve this problem!
 
  *Short version:*
 
 
  1. High concurrent reads, performing the same well-indexed query type to
  the same two tables.
  2. No additional traffic at all--just reads from these two tables. No
  writes anywhere.
  3. Very fast (sub-second) when server is only servicing one request at a
  time.
  4. Very slow (30s+) when server is servicing 2-3 requests at a time.
 
  Questions:
 
  1. What resource is under contention for servicing read-only queries if
 you
  have a large buffer pool and a plenty-big thread cache?
  2. What parameters can I tune to increase concurrent reads to these two
  tables?
 
  *Long version:*
 
 
  I've got a MySQL server that has only about 50 connections open to it at
  any given time. It basically only has one OLAP q
   uery
  type being ran against
  it that amounts to something like this:
 
  SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate)
  FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;
 
  These queries are well indexed and run very well
 individually--sub-second,
  usually even faster. When I run a few of these queries simultaneously
 (2-3
  on my laptop, 6-7 on our production boxes) performance grinds to a halt:
  Consistently about 30 seconds to service a query.
 
  Ideally, I'd like to be able to run 50+ of these queries concurrently.
 
  I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on
 both
  machines. I've tried tweaking the following my.cnf parameters to be
 higher:
 
  thread_concurrency = 20
  thread_cache_size = 340
  innodb_buffer_pool_size=2G
 
  A few other my.cnf parameters that I have set:
 
  innodb_file_format=Barracuda
  innodb_file_format_max=Barracuda
  innodb_file_per_table=1
  skip-external-locking
  innodb_log_files_in_group=2
  innodb_log_file_size=2000M
  max_allowed_packet=64M
 
  Thanks in advance,
 
  Brad Heller
 
 
  --
  Sent from Kaiten Mail. Please excuse my brevity.
 

Re: Concurrent read performance problems

[Johan De Meersman]

True, which is why I said I suspected file-based sort :-) At one million rows, 
that seems to be an accurate guess, too. Still on the phone, though, and in 
bed. I'll read the thread better tomorrow, but you might get some benefit from 
cutting out the subselect if that's possible.

If you have plenty of memory, have a look at the max_temp_table_size and 
max_heap_table variables, too; those decide when the sort goes to disk.

Johnny Withers joh...@pixelated.net wrote:
Just because it says filrsort doesn't mean it'll create a file on disk.
Table schema and full query would be helpful here too

http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/
On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote:

 Yes sorry, here's the explain. It was taken from MariaDB 5.5.32.
Looks like
 there is a lot of filesort goin' on here. Also note that I'm only
using the
 first two fields of the covering index (intentionally).



+--+-++---+++-+---+-+---+
 | id   | select_type | table  | type  | possible_keys
| key| key_len | ref
   | rows| Extra
 |


+--+-++---+++-+---+-+---+
 |1 | PRIMARY | derived2 | ALL   | NULL
 | NULL   | NULL| NULL
| 1004685 | Using temporary; Using
 filesort   |
 |2 | DERIVED | accounts   | range |
 PRIMARY,unique_account_identifiers | unique_account_identifiers | 257
|
 NULL  |   3 |
Using
 where; Using index; Using temporary; Using filesort |
 |2 | DERIVED | facts  | ref   | covering
 | covering   | 4   |
 facts.accounts.id|  334895 |
Using
 where
   |


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


 *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 |
 Skype:
 brad.heller | @bradhe http://www.twitter.com/bradhe |
 @cloudabilityhttp://www.twitter.com/cloudability


 On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman
vegiv...@tuxera.be
 wrote:

  On my phone now, but it smells of file-based sorting, making disk
access
  the bottleneck. Can you provide the explain?
 
 
  Brad Heller b...@cloudability.com wrote:
 
  Hey list, first time posting here so apologies if this is the
wrong
 forum
  for this but I'm really out of options on how to solve this
problem!
 
  *Short version:*
 
 
  1. High concurrent reads, performing the same well-indexed query
type to
  the same two tables.
  2. No additional traffic at all--just reads from these two tables.
No
  writes anywhere.
  3. Very fast (sub-second) when server is only servicing one
request at a
  time.
  4. Very slow (30s+) when server is servicing 2-3 requests at a
time.
 
  Questions:
 
  1. What resource is under contention for servicing read-only
queries if
 you
  have a large buffer pool and a plenty-big thread cache?
  2. What parameters can I tune to increase concurrent reads to
these two
  tables?
 
  *Long version:*
 
 
  I've got a MySQL server that has only about 50 connections open to
it at
  any given time. It basically only has one OLAP q
   uery
  type being ran against
  it that amounts to something like this:
 
  SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1
aggregate)
  FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;
 
  These queries are well indexed and run very well
 individually--sub-second,
  usually even faster. When I run a few of these queries
simultaneously
 (2-3
  on my laptop, 6-7 on our production boxes) performance grinds to a
halt:
  Consistently about 30 seconds to service a query.
 
  Ideally, I'd like to be able to run 50+ of these queries
concurrently.
 
  I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results
on
 both
  machines. I've tried tweaking the following my.cnf parameters to
be
 higher:
 
  thread_concurrency = 20
  thread_cache_size = 340
  innodb_buffer_pool_size=2G
 
  A few other my.cnf parameters that I have set:
 
  innodb_file_format=Barracuda
  innodb_file_format_max=Barracuda
  

Re: Concurrent read performance problems

[Brad Heller]

Johan, your suggestion to tweak max_heap_table_size and tmp_table_size
fixed the issue. Bumping them both to 512MB got our performance back
on-par. I came up with a way to avoid the contention using a complex set of
temp tables, but performance was abysmal.

By reverting to the more straight-forward query with the subselect as well
as tweaking the max_healp_table_size and tmp_table_size I saw no resource
contention causing slowdowns, as well as a 12x performance boost. Thanks
for your help!

*Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype:
brad.heller | @bradhe http://www.twitter.com/bradhe |
@cloudabilityhttp://www.twitter.com/cloudability


On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 True, which is why I said I suspected file-based sort :-) At one million
 rows, that seems to be an accurate guess, too. Still on the phone, though,
 and in bed. I'll read the thread better tomorrow, but you might get some
 benefit from cutting out the subselect if that's possible.

 If you have plenty of memory, have a look at the max_temp_table_size and
 max_heap_table variables, too; those decide when the sort goes to disk.


 Johnny Withers joh...@pixelated.net wrote:

 Just because it says filrsort doesn't mean it'll create a file on disk.
 Table schema and full query would be helpful here too

 http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/
 On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote:

  Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like
  there is a lot of filesort goin' on here. Also note that I'm only using the
  first two fields of the covering index (intentionally).


  +--+-++---+
 --
 ++-+
 --
 +-+
 --
 +
  | id   | select_type | table  | type  |
 possible_keys
 | key| key_len | ref
| rows| Extra
  |

  +--+-++---+
 --
 ++-+
 --
 +-+
 --
 +
  |1 | PRIMARY | derived2 | ALL   | NULL
  | NULL   | NULL| NULL
 | 1004685 | Using temporary; Using
  filesort   |
  |2 | DERIVED | accounts   | range |
  PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 |
  NULL  |   3 | Using
  where; Using index; Using temporary; Using filesort |
  |2 | DERIVED | facts  | ref   | covering
  | covering   | 4   |
  facts.accounts.id|  334895 | Using
  where
|

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


  *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 |
  Skype:
  brad.heller | @bradhe http://www.twitter.com/bradhe |
  @cloudabilityhttp://www.twitter.com/cloudability


  On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be

 wrote:


  On my phone now, but it smells of file-based sorting, making disk access
  the bottleneck. Can you provide the explain?


  Brad Heller b...@cloudability.com wrote:


  Hey list, first time posting here so apologies if this is the wrong

  forum

  for this but I'm really out of options on how to solve this problem!

  *Short version:*


  1. High concurrent reads, performing the same well-indexed query type to
  the same two tables.
  2. No additional traffic at all--just reads from these two tables. No


 writes anywhere.
  3. Very fast (sub-second) when server is only servicing one request at a
  time.
  4. Very slow (30s+) when server is servicing 2-3 requests at a time.

  Questions:

  1. What resource is under contention for servicing read-only queries if

  you

  have a large buffer pool and a plenty-big thread cache?
  2. What parameters can I tune to increase concurrent reads to these two
  tables?

  *Long version:*


  I've got a MySQL server that has only about 50 connections open to it at
  any given time. It basically only has one OLAP q
   uery
  type being ran against
  it that amounts to something like this:

  S
  ELECT
 (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate)
  FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;

  These queries are well indexed and run very well

  individually--sub-second,

  usually 

Re: Concurrent read performance problems

[Johan De Meersman]

Good to hear. A word of warning, though: make sure you don't have more 
connections allocating those buffers than your machine can handle memory-wise, 
or you'll start swapping and performance will REALLY go down the drain.

A query/index based solution would still be preferred. Could you for instance 
materialize that subselect and periodically refresh it? Other tricks may also 
be available.

Brad Heller b...@cloudability.com wrote:
Johan, your suggestion to tweak max_heap_table_size and tmp_table_size
fixed the issue. Bumping them both to 512MB got our performance back
on-par. I came up with a way to avoid the contention using a complex
set of
temp tables, but performance was abysmal.

By reverting to the more straight-forward query with the subselect as
well
as tweaking the max_healp_table_size and tmp_table_size I saw no
resource
contention causing slowdowns, as well as a 12x performance boost.
Thanks
for your help!

*Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 |
Skype:
brad.heller | @bradhe http://www.twitter.com/bradhe |
@cloudabilityhttp://www.twitter.com/cloudability


On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman
vegiv...@tuxera.bewrote:

 True, which is why I said I suspected file-based sort :-) At one
million
 rows, that seems to be an accurate guess, too. Still on the phone,
though,
 and in bed. I'll read the thread better tomorrow, but you might get
some
 benefit from cutting out the subselect if that's possible.

 If you have plenty of memory, have a look at the max_temp_table_size
and
 max_heap_table variables, too; those decide when the sort goes to
disk.


 Johnny Withers joh...@pixelated.net wrote:

 Just because it says filrsort doesn't mean it'll create a file on
disk.
 Table schema and full query would be helpful here too


http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/
 On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com
wrote:

  Yes sorry, here's the explain. It was taken from MariaDB 5.5.32.
Looks like
  there is a lot of filesort goin' on here. Also note that I'm only
using the
  first two fields of the covering index (intentionally).


  +--+-++---+
 --
 ++-+
 --
 +-+
 --
 +
  | id   | select_type | table  | type  |
 possible_keys
 | key| key_len | ref
| rows| Extra
  |

  +--+-++---+
 --
 ++-+
 --
 +-+
 --
 +
  |1 | PRIMARY | derived2 | ALL   | NULL
  | NULL   | NULL| NULL
 | 1004685 | Using temporary;
Using
  filesort   |
  |2 | DERIVED | accounts   | range |
  PRIMARY,unique_account_identifiers | unique_account_identifiers |
257 |
  NULL  |   3 |
Using
  where; Using index; Using temporary; Using filesort |
  |2 | DERIVED | facts  | ref   | covering
  | covering   | 4   |
  facts.accounts.id|  334895 |
Using
  where
|

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


  *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514
|
  Skype:
  brad.heller | @bradhe http://www.twitter.com/bradhe |
  @cloudabilityhttp://www.twitter.com/cloudability


  On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman
vegiv...@tuxera.be

 wrote:


  On my phone now, but it smells of file-based sorting, making disk
access
  the bottleneck. Can you provide the explain?


  Brad Heller b...@cloudability.com wrote:


  Hey list, first time posting here so apologies if this is the
wrong

  forum

  for this but I'm really out of options on how to solve this
problem!

  *Short version:*


  1. High concurrent reads, performing the same well-indexed query
type to
  the same two tables.
  2. No additional traffic at all--just reads from these two
tables. No


 writes anywhere.
  3. Very fast (sub-second) when server is only servicing one
request at a
  time.
  4. Very slow (30s+) when server is servicing 2-3 requests at a
time.

  Questions:

  1. What resource is under contention for servicing read-only
queries if

  you

  have a large buffer pool and a plenty-big thread cache?
  2. What parameters can I tune to increase concurrent reads to
these two
  tables?

  *Long version:*


  

Concurrent read performance problems

[Brad Heller]

Hey list, first time posting here so apologies if this is the wrong forum
for this but I'm really out of options on how to solve this problem!

*Short version:*

1. High concurrent reads, performing the same well-indexed query type to
the same two tables.
2. No additional traffic at all--just reads from these two tables. No
writes anywhere.
3. Very fast (sub-second) when server is only servicing one request at a
time.
4. Very slow (30s+) when server is servicing 2-3 requests at a time.

Questions:

1. What resource is under contention for servicing read-only queries if you
have a large buffer pool and a plenty-big thread cache?
2. What parameters can I tune to increase concurrent reads to these two
tables?

*Long version:*

I've got a MySQL server that has only about 50 connections open to it at
any given time. It basically only has one OLAP query type being ran against
it that amounts to something like this:

SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate)
FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...;

These queries are well indexed and run very well individually--sub-second,
usually even faster. When I run a few of these queries simultaneously (2-3
on my laptop, 6-7 on our production boxes) performance grinds to a halt:
Consistently about 30 seconds to service a query.

Ideally, I'd like to be able to run 50+ of these queries concurrently.

I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both
machines. I've tried tweaking the following my.cnf parameters to be higher:

thread_concurrency = 20
thread_cache_size = 340
innodb_buffer_pool_size=2G

A few other my.cnf parameters that I have set:

innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
innodb_file_per_table=1
skip-external-locking
innodb_log_files_in_group=2
innodb_log_file_size=2000M
max_allowed_packet=64M

Thanks in advance,

Brad Heller

Re: problems with relationships created by mysql

[rounak jain]

oops..sorryproblem seems to be with base...so i meant to post it in
base mailing list


On Mon, May 13, 2013 at 9:37 AM, rounak jain rounak.m...@gmail.com wrote:

 I have connected MySql to Base. I created some relationships in MySql.
 They are properly visible in the Relationships chart in Base. See image.
 http://imgur.com/qtM8fVN,jwvJpen

 Other than that, they are non-existent. For example, while I try to create
 a sub-form, I see that the option to create subform via existing
 relationship is disabled. See image.
 http://imgur.com/qtM8fVN,jwvJpen#1

 Kindly guide me.

Problems compiling 5.6.10 under CygWIN for Windows

[Michael Steele]

I'm using CygWIN to compile MySQL 5.6.10 for Windows. Using the source code
(Generic Linux (Architecture Independent), Compressed TAR Archive) Version
5.6.10.

 

I have no problems compiling  (Generic Linux (Architecture Independent),
Compressed TAR Archive) Version 5.5.29 source code.

 

This is the output.

 

[ 27%] Building CXX object
storage/innobase/CMakeFiles/innobase.dir/fts/fts0que.cc.o

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/fts/fts0que.cc: In function
`dberr_t fts_query_phrase_search(fts_query_t*, const fts_string_t*)':

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/fts/fts0que.cc:2359:
warning: 'token' might be used uninitialized in this function [ 27%]
Building CXX object
storage/innobase/CMakeFiles/innobase.dir/fts/fts0sql.cc.o

[ 27%] Building CXX object
storage/innobase/CMakeFiles/innobase.dir/fts/fts0tlex.cc.o

[ 27%] Building CXX object
storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o

In file included from
/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.h:647,

 from
/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:95:

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic: In
function `int fts_write_object_id(ib_id_t, char*)':

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38:
error: expected `)' before PRIx64

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38:
warning: conversion lacks type at end of format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38:
warning: conversion lacks type at end of format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic: In
function `ulint fts_read_object_id(ib_id_t*, const char*)':

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51:
error: expected `)' before PRIx64

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51:
warning: conversion lacks type at end of format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51:
warning: conversion lacks type at end of format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc: In
function `int innobase_close_connection(handlerton*, THD*)':

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3733:
error: expected `)' before PRIu64

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3735:
warning: spurious trailing `%' in format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3735:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc: In
function `dberr_t calc_row_difference(upd_t*, uchar*, uchar*, TABLE*,
uchar*, ulint, row_prebuilt_t*, THD*)':

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6855:
error: expected `)' before PRIu64

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6857:
warning: spurious trailing `%' in format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6857:
warning: too many arguments for format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6867:
error: expected `)' before PRIu64

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6873:
warning: spurious trailing `%' in format

/cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6873:
warning: too many arguments for format

storage/innobase/CMakeFiles/innobase.dir/build.make:1023: recipe for target
`storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o' failed

make[2]: ***
[storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o] Error 1

CMakeFiles/Makefile2:755: recipe for target
`storage/innobase/CMakeFiles/innobase.dir/all' failed

make[1]: *** [storage/innobase/CMakeFiles/innobase.dir/all] Error 2

Makefile:146: recipe for target `all' failed

make: *** [all] Error 2

 

Best regards,

Michael 

 

problems with mysql and php after system software upgrade

[Wayne G Leslie]

During a recent system software upgrade (CentOS) on the front-end of our 
computing cluster, the mysql area was upgraded without our realizing that it 
was going to be.  Now, php and mysql do not communicate  well with one another. 
The version of mysql on the system after the upgrade is 5.1.61.  The mysql 
originally used was 5.0.45.  PhP was not upgraded and sits at version 5.3.3.

Execution of a simple php script on our web server

?php
DEFINE('DB_USER','root');
DEFINE('DB_PASSWORD', 'XXX');
DEFINE('DB_HOST', 'localhost');
DEFINE('DB_NAME', 'mysql');

$dbc = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);

if (!$dbc) {
die('Connect Error (' . mysql_errno() . ') ' . mysql_error());

$db_selected = mysql_select_db(DB_NAME);

if (!$db_selected) {
die ('Can\'t use' . DB_NAME . ': ' . mysql_error());
}

echo 'Success... Your connection to the Database is working';
}
?

gives the result - Connect Error (1045) Access denied for user 
'root'@'localhost' (using password: YES)

While I can access mysql in a simple fashion [mysql --user=root --pass= 
gives me a mysql prompt], a straightforward mysqladmin command (mysqladmin 
version) gives a similar error to the Connect Error above:

mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Do I have a msyql/php version mismatch?  I have tried a number of potential 
remedies I found on line but have been unsuccessful to this point.  Guidance 
from experts would be much appreciated.

Wayne Leslie

RE: problems with INNODB tables

[Rick James]

Some mixture.  Perhaps 35% of RAM for buffer_pool and 10% for key_buffer.  It 
depends on which needs more caching.

Note:  The key_buffer does not need to be bigger than the total of all MyISAM 
indexes (Index_length in SHOW TABLE STATUS, or size of .MYI files).  The 
buffer_pool does not need to be bigger than the total of data+index for InnoDB 
files.

 -Original Message-
 From: Malka Cymbalista [mailto:malki.cymbali...@weizmann.ac.il]
 Sent: Wednesday, April 25, 2012 3:15 AM
 To: Rick James; Andrés Tello
 Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
 Subject: RE: problems with INNODB tables
 
 Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory
 where it tells you to do one thing if using MYIASM tables and another
 if using INNODB tables.  We are using both. Any suggestions?
 Thanks for any help.
 
 Malki Cymbalista
 Webmaster, Weizmann Institute of Science
 malki.cymbali...@weizmann.ac.il
 08-9343036
 
 
 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Monday, April 23, 2012 9:42 PM
 To: Andrés Tello; Malka Cymbalista
 Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
 Subject: RE: problems with INNODB tables
 
 Check your memory usage according to
 http://mysql.rjweb.org/doc.php/memory
 
  -Original Message-
  From: Andrés Tello [mailto:mr.crip...@gmail.com]
  Sent: Monday, April 23, 2012 9:00 AM
  To: Malka Cymbalista
  Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
  Subject: Re: problems with INNODB tables
 
  Weird, I use a lot Innodb, and no issue, I even kill bravely the
 mysql
  process with pkill -9 -f mysql
 
  Y suppose the way drupal is being programed.
  PHP open and closes database connections each time a webpage with db
  access is issued.
  When a php exceution ends and the apache webserver have fullfilled
 the
  http request, again, php memory is freed and connections closed...
  UNLESS:.. you are using a mem cached db connection, wich I doubt it
  since drupal doens't requiere one, or using persistent connections,
  again, I doubt it, because persistante database connections aren't
  recommended to innodb tables...
 
  Mysql server by default can handles 100 conections, if you get to
  thata limit you need to fine tune the number of connections allowed.
 
  show full processlist can give you a better idea of what is going on,
  connections with the sleep status, are open connections with no
  currently no transacctions...
 
  I never use script based stop, I always use mysqladmin -u root -p -h
  localhost shutdown which properly tells mysql to flush tables and
  terminate.
 
  I can almost bet that you are using Ubuntu... ubuntu had given me
  sometimes very hard times because of the edgy  code they use to
 use,
  ext4 last version, and so on... what can you tell us about that?
 
  How much amount of memory you have?
  How much concurrent apache/php users you have?
  Can you provide more cuantitive data please? Hardware, php version,
  distro, kernel...
 
  Cheers...
 
 
 
  To start, 100 process is quite a lot, something isn't fine. Each time
 
  On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista 
  malki.cymbali...@weizmann.ac.il wrote:
 
   We are running MySQL version 5.0.45 on a Linux machine. Over the
   past few months we have been having several problems:
  
   1.   Our mysql processes have increased the memory used from
  about .3%
   per process to 8% per process
  
   2.   We sometimes can have over 100 processes running which
  brings the
   machine to its knees and we have to stop and start MySQL in order
 to
   kill all the processes. We think that maybe the processes are not
   finishing normally and are just hanging around.
  
   3.   The machine is a web server and in the last few months we
  are
   moving over to drupal 7 to build our sites and Drupal 7 requires
  INNODB
   tables.   Sometimes, when we restart MySQL using the commands
   /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that
  were
   built in drupal 7 do not come up.  In order for the INNODB tables
 to
   work, we have to stop mysql, rename the ibdata1 file, copy it back
   to
   ibdata1 and then restart mysql. Otherwise the INNODB tables are not
  accessable.
  
  
  
   In the past all our tables were MYIASM.  Our problems started as we
   started using more and more INNODB tables. Is there anything
 special
   that has to be done to configure MySQL when using INNODB tables?
   We clearly have a problem  but we have no idea where to start
  looking. Our
   error logs don't show anything.   If anyone has any suggestions, we
  will be
   happy to hear them.
   We are considering hiring a consultant who is an expert in MySQL.
 We
   are in Israel and we are open to suggestions.
  
   Thanks for any help.
  
   Malki Cymbalista
   Webmaster, Weizmann Institute of Science
  
 
 malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il
   
   08-9343036
  
  

--
MySQL General

RE: problems with INNODB tables

[Malka Cymbalista]

Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it 
tells you to do one thing if using MYIASM tables and another if using INNODB 
tables.  We are using both. Any suggestions?
Thanks for any help.

Malki Cymbalista
Webmaster, Weizmann Institute of Science
malki.cymbali...@weizmann.ac.il
08-9343036


-Original Message-
From: Rick James [mailto:rja...@yahoo-inc.com] 
Sent: Monday, April 23, 2012 9:42 PM
To: Andrés Tello; Malka Cymbalista
Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
Subject: RE: problems with INNODB tables

Check your memory usage according to
http://mysql.rjweb.org/doc.php/memory

 -Original Message-
 From: Andrés Tello [mailto:mr.crip...@gmail.com]
 Sent: Monday, April 23, 2012 9:00 AM
 To: Malka Cymbalista
 Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
 Subject: Re: problems with INNODB tables
 
 Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql 
 process with pkill -9 -f mysql
 
 Y suppose the way drupal is being programed.
 PHP open and closes database connections each time a webpage with db 
 access is issued.
 When a php exceution ends and the apache webserver have fullfilled the 
 http request, again, php memory is freed and connections closed...
 UNLESS:.. you are using a mem cached db connection, wich I doubt it 
 since drupal doens't requiere one, or using persistent connections, 
 again, I doubt it, because persistante database connections aren't 
 recommended to innodb tables...
 
 Mysql server by default can handles 100 conections, if you get to 
 thata limit you need to fine tune the number of connections allowed.
 
 show full processlist can give you a better idea of what is going on, 
 connections with the sleep status, are open connections with no 
 currently no transacctions...
 
 I never use script based stop, I always use mysqladmin -u root -p -h 
 localhost shutdown which properly tells mysql to flush tables and 
 terminate.
 
 I can almost bet that you are using Ubuntu... ubuntu had given me 
 sometimes very hard times because of the edgy  code they use to use,
 ext4 last version, and so on... what can you tell us about that?
 
 How much amount of memory you have?
 How much concurrent apache/php users you have?
 Can you provide more cuantitive data please? Hardware, php version, 
 distro, kernel...
 
 Cheers...
 
 
 
 To start, 100 process is quite a lot, something isn't fine. Each time
 
 On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista  
 malki.cymbali...@weizmann.ac.il wrote:
 
  We are running MySQL version 5.0.45 on a Linux machine. Over the 
  past few months we have been having several problems:
 
  1.   Our mysql processes have increased the memory used from
 about .3%
  per process to 8% per process
 
  2.   We sometimes can have over 100 processes running which
 brings the
  machine to its knees and we have to stop and start MySQL in order to 
  kill all the processes. We think that maybe the processes are not 
  finishing normally and are just hanging around.
 
  3.   The machine is a web server and in the last few months we
 are
  moving over to drupal 7 to build our sites and Drupal 7 requires
 INNODB
  tables.   Sometimes, when we restart MySQL using the commands
  /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that
 were
  built in drupal 7 do not come up.  In order for the INNODB tables to 
  work, we have to stop mysql, rename the ibdata1 file, copy it back 
  to
  ibdata1 and then restart mysql. Otherwise the INNODB tables are not
 accessable.
 
 
 
  In the past all our tables were MYIASM.  Our problems started as we 
  started using more and more INNODB tables. Is there anything special 
  that has to be done to configure MySQL when using INNODB tables?
  We clearly have a problem  but we have no idea where to start
 looking. Our
  error logs don't show anything.   If anyone has any suggestions, we
 will be
  happy to hear them.
  We are considering hiring a consultant who is an expert in MySQL. We 
  are in Israel and we are open to suggestions.
 
  Thanks for any help.
 
  Malki Cymbalista
  Webmaster, Weizmann Institute of Science
 
 malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il
  
  08-9343036
 
 

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

Re: problems with INNODB tables

[Andrés Tello]

switch to innodb...

and use one_file_per_table

I use both, but I try to use myisam for cataloges.

Innodb and myisam are truly different engines, they do things completely
different, for example, with myisam you have parameters to configure the
size of the memory for the indexes, and several others, meanwhile most of
innodb performase is bound to innodb buffer pools, and with the newerst
mysql version, yo
u can have several innodb buffer pools lowering your mutex wait a lot...

Also you can switch from myisam to innodb quickly, for that type of tasks,
I do a mysqldump with tab formatted texts because it gives 2 files per
table, 1 file with the sql query to create de database and other, tab
delimited file with all the data of that table, to be used with mysqlimport

For a properly recommendation, we would need to know much more about the
system using the database, some statistics...

What is the database used for?




On Wed, Apr 25, 2012 at 5:14 AM, Malka Cymbalista 
malki.cymbali...@weizmann.ac.il wrote:

 Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memorywhere it 
 tells you to do one thing if using MYIASM tables and another if
 using INNODB tables.  We are using both. Any suggestions?
 Thanks for any help.

 Malki Cymbalista
 Webmaster, Weizmann Institute of Science
 malki.cymbali...@weizmann.ac.il
 08-9343036


 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Monday, April 23, 2012 9:42 PM
 To: Andrés Tello; Malka Cymbalista
 Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
 Subject: RE: problems with INNODB tables

 Check your memory usage according to
 http://mysql.rjweb.org/doc.php/memory

  -Original Message-
  From: Andrés Tello [mailto:mr.crip...@gmail.com]
  Sent: Monday, April 23, 2012 9:00 AM
  To: Malka Cymbalista
  Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
  Subject: Re: problems with INNODB tables
 
  Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql
  process with pkill -9 -f mysql
 
  Y suppose the way drupal is being programed.
  PHP open and closes database connections each time a webpage with db
  access is issued.
  When a php exceution ends and the apache webserver have fullfilled the
  http request, again, php memory is freed and connections closed...
  UNLESS:.. you are using a mem cached db connection, wich I doubt it
  since drupal doens't requiere one, or using persistent connections,
  again, I doubt it, because persistante database connections aren't
  recommended to innodb tables...
 
  Mysql server by default can handles 100 conections, if you get to
  thata limit you need to fine tune the number of connections allowed.
 
  show full processlist can give you a better idea of what is going on,
  connections with the sleep status, are open connections with no
  currently no transacctions...
 
  I never use script based stop, I always use mysqladmin -u root -p -h
  localhost shutdown which properly tells mysql to flush tables and
  terminate.
 
  I can almost bet that you are using Ubuntu... ubuntu had given me
  sometimes very hard times because of the edgy  code they use to use,
  ext4 last version, and so on... what can you tell us about that?
 
  How much amount of memory you have?
  How much concurrent apache/php users you have?
  Can you provide more cuantitive data please? Hardware, php version,
  distro, kernel...
 
  Cheers...
 
 
 
  To start, 100 process is quite a lot, something isn't fine. Each time
 
  On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista 
  malki.cymbali...@weizmann.ac.il wrote:
 
   We are running MySQL version 5.0.45 on a Linux machine. Over the
   past few months we have been having several problems:
  
   1.   Our mysql processes have increased the memory used from
  about .3%
   per process to 8% per process
  
   2.   We sometimes can have over 100 processes running which
  brings the
   machine to its knees and we have to stop and start MySQL in order to
   kill all the processes. We think that maybe the processes are not
   finishing normally and are just hanging around.
  
   3.   The machine is a web server and in the last few months we
  are
   moving over to drupal 7 to build our sites and Drupal 7 requires
  INNODB
   tables.   Sometimes, when we restart MySQL using the commands
   /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that
  were
   built in drupal 7 do not come up.  In order for the INNODB tables to
   work, we have to stop mysql, rename the ibdata1 file, copy it back
   to
   ibdata1 and then restart mysql. Otherwise the INNODB tables are not
  accessable.
  
  
  
   In the past all our tables were MYIASM.  Our problems started as we
   started using more and more INNODB tables. Is there anything special
   that has to be done to configure MySQL when using INNODB tables?
   We clearly have a problem  but we have no idea where to start
  looking. Our
   error logs don't show anything.   If anyone has

Re: problems with INNODB tables

[Andrés Tello]

Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql
process with pkill -9 -f mysql

Y suppose the way drupal is being programed.
PHP open and closes database connections each time a webpage with db access
is issued.
When a php exceution ends and the apache webserver have fullfilled the http
request, again, php memory is freed and connections closed... UNLESS:.. you
are using a mem cached db connection, wich I doubt it since drupal doens't
requiere one, or using persistent connections, again, I doubt it, because
persistante database connections aren't recommended to innodb tables...

Mysql server by default can handles 100 conections, if you get to thata
limit you need to fine tune the number of connections allowed.

show full processlist can give you a better idea of what is going on,
connections with the sleep status, are open connections with no currently
no transacctions...

I never use script based stop, I always use
mysqladmin -u root -p -h localhost shutdown
which properly tells mysql to flush tables and terminate.

I can almost bet that you are using Ubuntu... ubuntu had given me sometimes
very hard times because of the edgy  code they use to use, ext4 last
version, and so on... what can you tell us about that?

How much amount of memory you have?
How much concurrent apache/php users you have?
Can you provide more cuantitive data please? Hardware, php version, distro,
kernel...

Cheers...



To start, 100 process is quite a lot, something isn't fine. Each time

On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista 
malki.cymbali...@weizmann.ac.il wrote:

 We are running MySQL version 5.0.45 on a Linux machine. Over the past few
 months we have been having several problems:

 1.   Our mysql processes have increased the memory used from about .3%
 per process to 8% per process

 2.   We sometimes can have over 100 processes running which brings the
 machine to its knees and we have to stop and start MySQL in order to kill
 all the processes. We think that maybe the processes are not finishing
 normally and are just hanging around.

 3.   The machine is a web server and in the last few months we are
 moving over to drupal 7 to build our sites and Drupal 7 requires INNODB
 tables.   Sometimes, when we restart MySQL using the commands
 /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were
 built in drupal 7 do not come up.  In order for the INNODB tables to work,
 we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and
 then restart mysql. Otherwise the INNODB tables are not accessable.



 In the past all our tables were MYIASM.  Our problems started as we
 started using more and more INNODB tables. Is there anything special that
 has to be done to configure MySQL when using INNODB tables?
 We clearly have a problem  but we have no idea where to start looking. Our
 error logs don't show anything.   If anyone has any suggestions, we will be
 happy to hear them.
 We are considering hiring a consultant who is an expert in MySQL. We are
 in Israel and we are open to suggestions.

 Thanks for any help.

 Malki Cymbalista
 Webmaster, Weizmann Institute of Science
 malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il
 08-9343036

RE: problems with INNODB tables

[Rick James]

Check your memory usage according to
http://mysql.rjweb.org/doc.php/memory

 -Original Message-
 From: Andrés Tello [mailto:mr.crip...@gmail.com]
 Sent: Monday, April 23, 2012 9:00 AM
 To: Malka Cymbalista
 Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun
 Subject: Re: problems with INNODB tables
 
 Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql
 process with pkill -9 -f mysql
 
 Y suppose the way drupal is being programed.
 PHP open and closes database connections each time a webpage with db
 access is issued.
 When a php exceution ends and the apache webserver have fullfilled the
 http request, again, php memory is freed and connections closed...
 UNLESS:.. you are using a mem cached db connection, wich I doubt it
 since drupal doens't requiere one, or using persistent connections,
 again, I doubt it, because persistante database connections aren't
 recommended to innodb tables...
 
 Mysql server by default can handles 100 conections, if you get to thata
 limit you need to fine tune the number of connections allowed.
 
 show full processlist can give you a better idea of what is going on,
 connections with the sleep status, are open connections with no
 currently no transacctions...
 
 I never use script based stop, I always use mysqladmin -u root -p -h
 localhost shutdown which properly tells mysql to flush tables and
 terminate.
 
 I can almost bet that you are using Ubuntu... ubuntu had given me
 sometimes very hard times because of the edgy  code they use to use,
 ext4 last version, and so on... what can you tell us about that?
 
 How much amount of memory you have?
 How much concurrent apache/php users you have?
 Can you provide more cuantitive data please? Hardware, php version,
 distro, kernel...
 
 Cheers...
 
 
 
 To start, 100 process is quite a lot, something isn't fine. Each time
 
 On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista 
 malki.cymbali...@weizmann.ac.il wrote:
 
  We are running MySQL version 5.0.45 on a Linux machine. Over the past
  few months we have been having several problems:
 
  1.   Our mysql processes have increased the memory used from
 about .3%
  per process to 8% per process
 
  2.   We sometimes can have over 100 processes running which
 brings the
  machine to its knees and we have to stop and start MySQL in order to
  kill all the processes. We think that maybe the processes are not
  finishing normally and are just hanging around.
 
  3.   The machine is a web server and in the last few months we
 are
  moving over to drupal 7 to build our sites and Drupal 7 requires
 INNODB
  tables.   Sometimes, when we restart MySQL using the commands
  /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that
 were
  built in drupal 7 do not come up.  In order for the INNODB tables to
  work, we have to stop mysql, rename the ibdata1 file, copy it back to
  ibdata1 and then restart mysql. Otherwise the INNODB tables are not
 accessable.
 
 
 
  In the past all our tables were MYIASM.  Our problems started as we
  started using more and more INNODB tables. Is there anything special
  that has to be done to configure MySQL when using INNODB tables?
  We clearly have a problem  but we have no idea where to start
 looking. Our
  error logs don't show anything.   If anyone has any suggestions, we
 will be
  happy to hear them.
  We are considering hiring a consultant who is an expert in MySQL. We
  are in Israel and we are open to suggestions.
 
  Thanks for any help.
 
  Malki Cymbalista
  Webmaster, Weizmann Institute of Science
 
 malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il
  
  08-9343036
 
 

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

Re: Problems Connecting with SSL

[Joerg Bruehe]

Hi Tim, all!


Sorry, but I have to start with a negative remark:
Why, oh why, don't you mention the version number you are using?

I hope it is some roughly current 5.1, but I'd rather like you to tell it.


Tim Gustafson wrote:
 I have three mySQL server, all of which are SSL-enable and all of which are 
 generally accessible from remote clients over SSL.
 
 However, one of my FreeBSD mySQL clients is having problems connecting to any 
 of the servers.  Running mySQL under truss reports the following:
 

Looking at this output tells something went terribly wrong:
file descriptor 2 is mishandled.

 --
 clock_gettime(13,{1298593067.0 })  = 0 (0x0)
 open(/dev/urandom,O_RDONLY,00)   = 4 (0x4)
 read(4,\a\^V\M-)\^C\M-L{`\M^O\M^@\M-v...,32) = 32 (0x20)
 sendto(2,\^V\^C\^A\0Y\^A\0\0U\^C\^A\M-m...,94,0x0,NULL,0x0) = 94 (0x5e)
 recvfrom(2,\^V,1,0x2,NULL,0x0)   = 1 (0x1)
 ioctl(2,FIONREAD,0xd784)   = 0 (0x0)
 recvfrom(2,\^V\^C\^A\0J\^B\0\0F\^C\^AMf\M-u...,1819,0x0,NULL,0x0) = 1819 
 (0x71b)

In the above lines, socket calls 'sendto()' and 'recvfrom()' are applied
to FD 2. Whatever the transmitted data may be, this should not be done:
FD 2 is standard error, so it is a file and no socket, and receiving
from it doesn't make any sense at all.

As the problem happens only for one of your combinations, I suspect that
 for some unknown reason FD 2 was closed in that run, and then this
number got returned when a socket was to be created.
Again, this should not happen: The only reason to close FD 2 should be
to re-open standard error to some other destination, which is unlikely
for the client to do.

 [[...]]
 shutdown(2,SHUT_RDWR)  = 0 (0x0)
 close(2)   = 0 (0x0)

So FD 2 is closed now.

 fstat(1,{ mode=crw--w ,inode=113,size=0,blksize=4096 }) = 0 (0x0)
 ioctl(1,TIOCGETA,0xe390)   = 0 (0x0)
 write(2,ERROR 2026 (HY000): ,20) ERR#9 'Bad file descriptor'
 write(2,SSL connection error,20) ERR#9 'Bad file descriptor'
 write(2,\n,1)ERR#9 'Bad file 
 descriptor'

... but with a closed FD 2 there is no chance to output an error message.
(The operations on FD 1 also look suspicious to me.)

 write(1,\a,1)= 1 (0x1)
 close(4)   = 0 (0x0)
 process exit, rval = 1
 --
 
 Can anyone tell me, based on the truss output above, what might be going 
 wrong?  I'm using the exact same command line options and ssl-ca file that I 
 am using on other clients, which can connect successfully.

I can't really tell what happened. It might be that file descriptor 2
(and probably also 1) were closed without good reason, and then creating
a socket (or opening some file) returned these descriptors.

You might check the preceding truss output for a close(2), IMO that
would likely be an error - but I have no idea why it happened.

 
 (As an aside: feature request: better SSL connection debugging, please!)

I assume your SSL problems might just be a consequence of inappropriate
actions on FDs 1 and 2. Did you do any redirection, are you running into
disk full, or is there any similar rsk on your stdout and stderr?


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


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

Re: Problems Connecting with SSL

[Tim Gustafson]

 Sorry, but I have to start with a negative remark:
 Why, oh why, don't you mention the version number you
 are using? I hope it is some roughly current 5.1, but
 I'd rather like you to tell it.

Doh!  Right you are!  Sorry, that was totally my bad.

mysql select version();
++
| version()  |
++
| 5.1.51-log | 
++

The server and the client are both running FreeBSD as follows:

FreeBSD 8.1-RELEASE-p2

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
t...@soe.ucsc.edu
831-459-5354

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

Re: Problems Connecting with SSL

[Tim Gustafson]

 Interesting that you can request advice from finland vs
 calling from redwood shores CA for technical advice 
 
 first take a look at: 
 http://dev.mysql.com/doc/refman/5.0/en/secure-basics.html 
 
 once you've determined the algorithm you will want to implement
 take a look at SSL 1,2,3 and TLS 1.0,1.1,1.2 and determine which
 protocol works best for you 
 
 http://en.wikipedia.org/wiki/Transport_Layer_Security
 
 feel free to ping me offline for implementation details 

In my original post, I stated that I had SSL connections up and running on all 
but this one client.  I'm intimately familiar with the inner workings of SSL 
and TLS, and I'm positive that the servers are configured correctly, and that 
in general my clients are all able to connect using SSL.  It's just this one 
client that is failing, and absent any useful debugging information, I'm having 
a hard time understanding why.

As to why I didn't call Redwood Shores: I didn't realize that Oracle was in the 
business of giving out free tech support.  I just e-mailed the mySQL general 
support list, which is usually what I do when I need help from an Open Source 
software group.  :)

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
t...@soe.ucsc.edu
831-459-5354

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

Re: Problems Connecting with SSL

[Tim Gustafson]

 perhaps a misconfiguration on the failing client .. 
 can you go to successful client, enable DEBUG level
 debugging and log the connection and then go to the
 failing client..enable DEBUG level debugging and log
 the connection to the failing client this would determine
 deltas between the failing and successful connections and
 you compare each log side-by-side to ascertain the point
 of failure. joerg ..any thoughts on this matter??

Well that's exactly my question: how does one enable debugging of this kind of 
connection on the mySQL client?  I get no more information when I use 
--verbose --verbose --verbose than when I don't.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
t...@soe.ucsc.edu
831-459-5354

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

Re: Problems Connecting with SSL

[Joerg Bruehe]

Hi!


Martin Gainty wrote:
 [[...]]
 
 joerg ..any thoughts on this matter??

Sorry - none.
I never dealt with the inner workings of the SSL library.

Just seeing file descriptor 2 (standard error) used for socket
operations makes me assume *very* strongly that something went wrong
well before that part:
For some (until now: unknown) reason standard error must have been
closed, so that FD 2 became available for the socket creation, and in a
MYSQL client application I don't see any reason to ever close standard
error.

I propose Tim should check the full truss output for some preceding
close(2). Hopefully, the surroundings of that call will give a hint what
went wrong.
(I hope it was caused by some write error, disk full, permission
problem, ... which Tim can fix. I of course agree such an error should
not make the program do a close(2), but it might happen nonetheless.)

That's all I can say here.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


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

Problems Connecting with SSL

[Tim Gustafson]

I have three mySQL server, all of which are SSL-enable and all of which are 
generally accessible from remote clients over SSL.

However, one of my FreeBSD mySQL clients is having problems connecting to any 
of the servers.  Running mySQL under truss reports the following:

--
clock_gettime(13,{1298593067.0 })= 0 (0x0)
open(/dev/urandom,O_RDONLY,00) = 4 (0x4)
read(4,\a\^V\M-)\^C\M-L{`\M^O\M^@\M-v...,32)   = 32 (0x20)
sendto(2,\^V\^C\^A\0Y\^A\0\0U\^C\^A\M-m...,94,0x0,NULL,0x0) = 94 (0x5e)
recvfrom(2,\^V,1,0x2,NULL,0x0) = 1 (0x1)
ioctl(2,FIONREAD,0xd784) = 0 (0x0)
recvfrom(2,\^V\^C\^A\0J\^B\0\0F\^C\^AMf\M-u...,1819,0x0,NULL,0x0) = 1819 
(0x71b)
clock_gettime(13,{1298593067.0 })= 0 (0x0)
clock_gettime(13,{1298593067.0 })= 0 (0x0)
gettimeofday({1298593067.844592 },0x0)   = 0 (0x0)
sendto(2,\^V\^C\^A\0F\^P\0\0B\0@\M-B\M^Y...,134,0x0,NULL,0x0) = 134 (0x86)
recvfrom(2,\^U,1,0x2,NULL,0x0) = 1 (0x1)
ioctl(2,FIONREAD,0xd784) = 0 (0x0)
recvfrom(2,\^U\^C\^A\0\^B\^B\n\^V\0\0\^B...,33,0x0,NULL,0x0) = 33 (0x21)
close(3) = 0 (0x0)
shutdown(2,SHUT_RDWR)= 0 (0x0)
close(2) = 0 (0x0)
fstat(1,{ mode=crw--w ,inode=113,size=0,blksize=4096 }) = 0 (0x0)
ioctl(1,TIOCGETA,0xe390) = 0 (0x0)
write(2,ERROR 2026 (HY000): ,20)   ERR#9 'Bad file descriptor'
write(2,SSL connection error,20)   ERR#9 'Bad file descriptor'
write(2,\n,1)  ERR#9 'Bad file descriptor'
write(1,\a,1)  = 1 (0x1)
close(4) = 0 (0x0)
process exit, rval = 1
--

Can anyone tell me, based on the truss output above, what might be going wrong? 
 I'm using the exact same command line options and ssl-ca file that I am using 
on other clients, which can connect successfully.

(As an aside: feature request: better SSL connection debugging, please!)

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
t...@soe.ucsc.edu
831-459-5354



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

Problems storing a blob using php.

[Richard Riley]

Debian squeeze, mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu
(i486) using readline 6.1, PHP 5.3.3-2 with Suhosin-Patch (cli) (built:
Oct 21 2010 18:58:27).

I'm not sure if this group or the php one is more appropriate but
thought I would try here first as it is perhaps a mysql setting I am
missing.

I have been unable to get prepared statements to store a blob.

using this test code:-

function saveImage($event)
{
$stmt = getDB()-prepare(INSERT INTO images (image) VALUES (?));
$null = NULL;
$stmt-bind_param(b, $null);
$fp = fopen(common-images/takeaway.png, r);
while (!feof($fp)) {
$stmt-send_long_data(0, fread($fp, 8192)); /* I also tried 
get_file_contents */
}
fclose($fp);
$stmt-execute();
logtext($stmt-error);
}


I get the following error in my home brew log file: Incorrect arguments to 
mysqld_stmt_execute

I have checked that fp is valid.

My table is defined as follows:-

,
| CREATE TABLE `images` (
|   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|   `fkey` int(10) unsigned DEFAULT NULL,
|   `image` blob,
|   `name` varchar(30) NOT NULL,
|   `type` varchar(30) NOT NULL,
|   PRIMARY KEY (`id`),
|   KEY `deleteimages` (`fkey`),
|   CONSTRAINT `deleteimages` FOREIGN KEY (`fkey`) REFERENCES `events` (`id`) 
ON DELETE CASCADE
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8
`

I'm sure it must be something glaringly obvious but being a bit of a
noob to mysql I'm not sure what to do to narrow it down. I can set the
blob field directly. Its the send_long_data that seems to cause the arg
error at the execute phase.

Any debugging hits/configure hints would be much appreciated.



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

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi,

We're chaning it to INT(9). Apparently someone remembered to change the type
of data in this field from an alphanumeric value to an INT(9).

I'm going to change this asap.

Thanks

BR
AJ

On Mon, Sep 6, 2010 at 5:17 AM, mos mo...@fastmail.fm wrote:

 At 04:44 AM 9/3/2010, Alexandre Vieira wrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len
 | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY | 23
 | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ


 Alexandre,
 Do you have UserId declared as CHAR? It looks numeric to me. If it is
 stored as an integer then don't use the ' ' in the select statement
 otherwise it needs to convert it.
 If UserId values are integers and you have the column defined as CHAR, then
 declare the column UserId as integer or BigInt and the searches should be
 faster than searching on CHAR.

 Mike




  On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net
 wrote:

  What about an explain of this query:
 
 
  SELECT * FROM clientinfo WHERE userid='182106617';
 
  -JW
 
 
  On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com
 wrote:
 
  John, Johnny,
 
  Thanks for the prompt answer.
 
  mysql SHOW CREATE TABLE clientinfo;
 
 
 ++--+
  | Table  | Create
  Table
  |
 
 
 ++--+
  | clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY  (`userid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
 
 ++--+
  1 row in set (0.00 sec)
  mysql SHOW INDEX FROM clientinfo;
 
 
 +++--+--+-+---+-+--++--++-+
  | Table  | Non_unique | Key_name | Seq_in_index | Column_name |
  Collation | Cardinality | Sub_part | Packed | Null | Index_type |
 Comment |
 
 
 +++--+--+-+---+-+--++--++-+
  | clientinfo |  0 | PRIMARY  |1 | userid  |
  A |  460056 | NULL | NULL   |  | BTREE  |
   |
 
 
 +++--+--+-+---+-+--++--++-+
  1 row in set (0.00 sec)
 
 
  SELECT * FROM clientinfo WHERE userid='182106617';
 
  UPDATE clientinfo SET
 
 units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
  WHERE userid='152633876';
 
  INSERT INTO clientinfo VALUES
  ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
  units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
  notification=0, package='D', user_type='PRE';
 
  DELETE FROM clientinfo WHERE units='155618918';
 
  There are no other type of queries.
 
  We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of
 RAM.
 
  We also run some 

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi List,

In a 20m interval in our max load I have:

OS WAIT ARRAY INFO: reservation count 637, signal count 625
Mutex spin waits 0, rounds 19457, OS waits 428
RW-shared spins 238, OS waits 119; RW-excl spins 13, OS waits 8

(The values are the difference between the start and end of this 20m
interval)

The machine has 2 CPU's and usually has 40-50% of idle CPU.

Our workload consists on lots of parallel simple queries (SELECTs and
UPDATEs with a simple condition on the PK) on a 500k record/40MB table with
an INDEX on the PK.

| innodb_sync_spin_loops  | 20 |
| innodb_thread_concurrency   | 16 |
| innodb_thread_sleep_delay   | 1  |

I've been sampling my innodb status and there are always 16 queries inside
InnoDB and some 20-30 in queue. Therefore lowering thread_sleep_delay won't
help.

Since I have 47 spin rounds per OS Wait, would innodb gain something with
rising sync_spin_loops a little bit?

Also, should I be capping thread_concurrency with a 2 CPU machine?

Unfortunately this machine only has 2 RAID1 disks. I can't spread the disk
load (datafile/logfiles) between disks.

extended device statistics
r/sw/s   kr/s   kw/s wait actv wsvc_t asvc_t  %w  %b device
0.0   83.70.0  379.3  0.0  1.00.1   11.5   1  94 d2 (/var)

Usually the iostat busy indicator is near 100%.

Any hints on something I could tune to have less OS Waits and help with
the Disk I/O?

=
100906 18:33:40 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 47 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 266140, signal count 259267
Mutex spin waits 0, rounds 7407879, OS waits 179189
RW-shared spins 93878, OS waits 46196; RW-excl spins 9473, OS waits 7311

---
LOG
---
Log sequence number 62 2833945222
Log flushed up to   62 2833944847
Last checkpoint at  62 2828803314
1 pending log writes, 0 pending chkp writes
18419416 log i/o's done, 37.64 log i/o's/second

--
ROW OPERATIONS
--
16 queries inside InnoDB, 27 queries in queue
1 read views open inside InnoDB
Main thread id 11, state: sleeping
Number of rows inserted 603196, updated 9006533, deleted 111028, read
30145300
0.17 inserts/s, 18.49 updates/s, 0.00 deletes/s, 41.47 reads/s

If nothing else can be done I'll advise the client to acquire new HW for
this BD.

By the way, upgrading from 5.0.45-log to 5.1.50 would make a huge difference
in terms of performance?

BR
AJ

On Mon, Sep 6, 2010 at 10:46 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi,

 We're chaning it to INT(9). Apparently someone remembered to change the
 type of data in this field from an alphanumeric value to an INT(9).

 I'm going to change this asap.

 Thanks

 BR
 AJ


 On Mon, Sep 6, 2010 at 5:17 AM, mos mo...@fastmail.fm wrote:

 At 04:44 AM 9/3/2010, Alexandre Vieira wrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len
 | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY | 23
 | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ


 Alexandre,
 Do you have UserId declared as CHAR? It looks numeric to me. If it is
 stored as an integer then don't use the ' ' in the select statement
 otherwise it needs to convert it.
 If UserId values are integers and you have the column defined as CHAR,
 then declare the column UserId as integer or BigInt and the searches should
 be faster than searching on CHAR.

 Mike




  On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net
 wrote:

  What about an explain of this query:
 
 
  SELECT * FROM clientinfo WHERE userid='182106617';
 
  -JW
 
 
  On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com
 wrote:
 
  John, Johnny,
 
  Thanks for the prompt answer.
 
  mysql SHOW CREATE TABLE clientinfo;
 
 
 ++--+
  | Table  | Create
  Table
  |
 
 
 

Re: Performance problems on MySQL

[Shawn Green (MySQL)]

On 9/3/2010 3:15 PM, Johnny Withers wrote:

It seems that when your index is PRIMARY on InnoDB tables, it's magic and is
part of the data thereby it is not included in the index_length field.

I have never noticed this. I don't think adding a new index will make a
difference.

You could try moving your log files to a different disk array than where
your data is. If you have binary and query logging enabled, it's probably a
good idea.

Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of 
the data:


http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

That explains why there is no length to this index.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

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

Re: Performance problems on MySQL

[mos]

At 04:44 AM 9/3/2010, Alexandre Vieira wrote:

Hi Johnny,

mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
++-++---+---+-+-+---+--+---+
| id | select_type | table  | type  | possible_keys | key | key_len
| ref   | rows | Extra |
++-++---+---+-+-+---+--+---+
|  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY | 23
| const |1 |   |
++-++---+---+-+-+---+--+---+
1 row in set (0.53 sec)

Thanks

BR
AJ


Alexandre,
 Do you have UserId declared as CHAR? It looks numeric to me. If it is 
stored as an integer then don't use the ' ' in the select statement 
otherwise it needs to convert it.
If UserId values are integers and you have the column defined as CHAR, then 
declare the column UserId as integer or BigInt and the searches should be 
faster than searching on CHAR.


Mike




On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote:

 What about an explain of this query:


 SELECT * FROM clientinfo WHERE userid='182106617';

 -JW


 On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote:

 John, Johnny,

 Thanks for the prompt answer.

 mysql SHOW CREATE TABLE clientinfo;

 
++--+

 | Table  | Create
 Table
 |

 
++--+

 | clientinfo | CREATE TABLE `clientinfo` (
   `userid` varchar(21) NOT NULL default '',
   `units` float default NULL,
   `date_last_query` datetime default NULL,
   `last_acc` int(10) unsigned default NULL,
   `date_last_units` datetime default NULL,
   `notification` int(10) unsigned NOT NULL default '0',
   `package` char(1) default NULL,
   `user_type` varchar(5) default NULL,
   PRIMARY KEY  (`userid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 
++--+

 1 row in set (0.00 sec)
 mysql SHOW INDEX FROM clientinfo;

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

 | Table  | Non_unique | Key_name | Seq_in_index | Column_name |
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment |


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

 | clientinfo |  0 | PRIMARY  |1 | userid  |
 A |  460056 | NULL | NULL   |  | 
BTREE  | |


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

 1 row in set (0.00 sec)


 SELECT * FROM clientinfo WHERE userid='182106617';

 UPDATE clientinfo SET
 
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0

 WHERE userid='152633876';

 INSERT INTO clientinfo VALUES
 ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
 units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
 notification=0, package='D', user_type='PRE';

 DELETE FROM clientinfo WHERE units='155618918';

 There are no other type of queries.

 We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

 We also run some other applications in the server, but nothing that
 consumes all the CPU/Memory. The machine has almost 1GB of free memory and
 50% of idle CPU time at any time.

 TIA

 BR
 Alex


 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




--
Alexandre 

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi Johnny,

mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
++-++---+---+-+-+---+--+---+
| id | select_type | table  | type  | possible_keys | key | key_len
| ref   | rows | Extra |
++-++---+---+-+-+---+--+---+
|  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY | 23
| const |1 |   |
++-++---+---+-+-+---+--+---+
1 row in set (0.53 sec)

Thanks

BR
AJ

On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote:

 What about an explain of this query:


 SELECT * FROM clientinfo WHERE userid='182106617';

 -JW


 On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote:

 John, Johnny,

 Thanks for the prompt answer.

 mysql SHOW CREATE TABLE clientinfo;

 ++--+
 | Table  | Create
 Table
 |

 ++--+
 | clientinfo | CREATE TABLE `clientinfo` (
   `userid` varchar(21) NOT NULL default '',
   `units` float default NULL,
   `date_last_query` datetime default NULL,
   `last_acc` int(10) unsigned default NULL,
   `date_last_units` datetime default NULL,
   `notification` int(10) unsigned NOT NULL default '0',
   `package` char(1) default NULL,
   `user_type` varchar(5) default NULL,
   PRIMARY KEY  (`userid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 ++--+
 1 row in set (0.00 sec)
 mysql SHOW INDEX FROM clientinfo;

 +++--+--+-+---+-+--++--++-+
 | Table  | Non_unique | Key_name | Seq_in_index | Column_name |
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

 +++--+--+-+---+-+--++--++-+
 | clientinfo |  0 | PRIMARY  |1 | userid  |
 A |  460056 | NULL | NULL   |  | BTREE  | |

 +++--+--+-+---+-+--++--++-+
 1 row in set (0.00 sec)


 SELECT * FROM clientinfo WHERE userid='182106617';

 UPDATE clientinfo SET
 units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
 WHERE userid='152633876';

 INSERT INTO clientinfo VALUES
 ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
 units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
 notification=0, package='D', user_type='PRE';

 DELETE FROM clientinfo WHERE units='155618918';

 There are no other type of queries.

 We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

 We also run some other applications in the server, but nothing that
 consumes all the CPU/Memory. The machine has almost 1GB of free memory and
 50% of idle CPU time at any time.

 TIA

 BR
 Alex


 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
Alexandre Vieira - nul...@gmail.com

Re: Performance problems on MySQL

[Jangita]

On 02/09/2010 6:05 p, Alexandre Vieira wrote:

Hi Jangita,

I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's
246MB of 350MB free.

| Innodb_buffer_pool_pages_data | 6020   |
| Innodb_buffer_pool_pages_dirty| 1837   |
| Innodb_buffer_pool_pages_flushed  | 673837 |
| Innodb_buffer_pool_pages_free | 15779  |
| Innodb_buffer_pool_pages_latched  | 0  |
| Innodb_buffer_pool_pages_misc | 601|
| Innodb_buffer_pool_pages_total| 22400  |
| Innodb_buffer_pool_read_ahead_rnd | 1  |
| Innodb_buffer_pool_read_ahead_seq | 0  |
| Innodb_buffer_pool_read_requests  | 48471963   |
| Innodb_buffer_pool_reads  | 3497   |
| Innodb_buffer_pool_wait_free  | 0  |
| Innodb_buffer_pool_write_requests | 21700478   |

Why would I need to increase?

Thanks

BR
AJ
I'm guessing (just a guess) that you have alot free buffer_pool_size 
because mysql doesn't use it because it cannot fit information into it 
at one go so doesn't use it at all? Try and up it; if it doesn't work 
you could always set it back.


--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.com

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

Re: Performance problems on MySQL

[Johnny Withers]

Ok, so I'm stumped?

What kind of hardware is behind this thing?

-JW

On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key | key_len
 | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY | 23
 | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi,

I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it
makes a little difference but not enough for the application to run in real
time processing.

It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

MySQL is eating 179MB of RAM and 5,4% of CPU.

  PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow
and we have ~50% of free CPU time.

Currently I can't use the replication server since the application running
on top if this BD can only talk to 1 data source.
At the moment it's also not possible to change the application in order to
make it use the DB more wisely.

Basically we have a table with lots of selects, lots of update, lots of
inserts and deletes. Data manipulation is random, doesn't follow any
specific pattern. All working concurrently.

A big bottleneck is:

8 queries inside InnoDB, 28 queries in queue
1 read views open inside InnoDB

Increasing innodb_thread_concurrency might help without causing any problems
to the overall performance.

Makes total sense if you read:
http://peter-zaitsev.livejournal.com/9138.html

Thanks in advance.

BR
AJ

On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.net wrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





-- 
Alexandre Vieira - nul...@gmail.com

Re: Performance problems on MySQL

[Johnny Withers]

What does

SHOW TABLE STATUS LIKE 'table_name'

Say about this table?

-JW

On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi,

 I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it
 makes a little difference but not enough for the application to run in real
 time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
 grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application running
 on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in order to
 make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots of
 inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi,

mysql SHOW TABLE STATUS LIKE 'clientinfo';
+++-++++-+-+--+---++-+-++---+--+++
| Name   | Engine | Version | Row_format | Rows   | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment|
+++-++++-+-+--+---++-+-++---+--+++
| clientinfo | InnoDB |  10 | Compact| 504762 | 82 |
41500672 |   0 |0 | 0 |   NULL |
2010-09-01 03:21:36 | NULL| NULL   | latin1_swedish_ci |
NULL || InnoDB free: 276480 kB |
+++-++++-+-+--+---++-+-++---+--+++
1 row in set (0.02 sec)

BR
AJ

On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.net wrote:

 What does

 SHOW TABLE STATUS LIKE 'table_name'

 Say about this table?

 -JW


 On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi,

 I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and
 it makes a little difference but not enough for the application to run in
 real time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
 grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application running
 on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in order to
 make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots of
 inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
Alexandre Vieira - nul...@gmail.com

Re: Performance problems on MySQL

[Johnny Withers]

Very confusing...

Why is index_length zero ?

On top of that, there's only 500K rows in the table with a data size of
41MB. Maybe InnoDB is flushing to disk too often?

What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output
girds)



  --

*Johnny Withers*
jwith...@ecashsoftware.com
601.919.2275 x112
 [image: eCash Software Systems]


On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira nul...@gmail.com wrote:

 Hi,

 mysql SHOW TABLE STATUS LIKE 'clientinfo';

 +++-++++-+-+--+---++-+-++---+--+++
 | Name   | Engine | Version | Row_format | Rows   | Avg_row_length |
 Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
 Create_time | Update_time | Check_time | Collation |
 Checksum | Create_options | Comment|

 +++-++++-+-+--+---++-+-++---+--+++
 | clientinfo | InnoDB |  10 | Compact| 504762 | 82 |
 41500672 |   0 |0 | 0 |   NULL |
 2010-09-01 03:21:36 | NULL| NULL   | latin1_swedish_ci |
 NULL || InnoDB free: 276480 kB |

 +++-++++-+-+--+---++-+-++---+--+++
 1 row in set (0.02 sec)

 BR
 AJ


 On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote:

 What does

 SHOW TABLE STATUS LIKE 'table_name'

 Say about this table?

 -JW


 On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi,

 I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and
 it makes a little difference but not enough for the application to run in
 real time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
 grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application
 running on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in order
 to make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots of
 inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Performance problems on MySQL

[Alexandre Vieira]

   | Avg_row_length |
 Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
 Create_time | Update_time | Check_time | Collation |
 Checksum | Create_options | Comment|

 +++-++++-+-+--+---++-+-++---+--+++
 | clientinfo | InnoDB |  10 | Compact| 504762 | 82
 |41500672 |   0 |0 | 0 |   NULL
 | 2010-09-01 03:21:36 | NULL| NULL   | latin1_swedish_ci |
 NULL || InnoDB free: 276480 kB |

 +++-++++-+-+--+---++-+-++---+--+++
 1 row in set (0.02 sec)

 BR
 AJ


 On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote:

 What does

 SHOW TABLE STATUS LIKE 'table_name'

 Say about this table?

 -JW


 On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi,

 I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and
 it makes a little difference but not enough for the application to run in
 real time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
 grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application
 running on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in order
 to make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots of
 inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
Alexandre Vieira - nul...@gmail.com

Re: Performance problems on MySQL

[Johnny Withers]

 of iostat -dxk 60 ? (run for a minute+ to get 2 output
 girds)



   --

 *Johnny Withers*
 jwith...@ecashsoftware.com
 601.919.2275 x112
  [image: eCash Software Systems]


 On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi,

 mysql SHOW TABLE STATUS LIKE 'clientinfo';

 +++-++++-+-+--+---++-+-++---+--+++
 | Name   | Engine | Version | Row_format | Rows   | Avg_row_length |
 Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
 Create_time | Update_time | Check_time | Collation |
 Checksum | Create_options | Comment|

 +++-++++-+-+--+---++-+-++---+--+++
 | clientinfo | InnoDB |  10 | Compact| 504762 | 82
 |41500672 |   0 |0 | 0 |   NULL
 | 2010-09-01 03:21:36 | NULL| NULL   | latin1_swedish_ci |
 NULL || InnoDB free: 276480 kB |

 +++-++++-+-+--+---++-+-++---+--+++
 1 row in set (0.02 sec)

 BR
 AJ


 On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote:

 What does

 SHOW TABLE STATUS LIKE 'table_name'

 Say about this table?

 -JW


 On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi,

 I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK
 and it makes a little difference but not enough for the application to run
 in real time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
 grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application
 running on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in order
 to make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots of
 inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers 
 joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Performance problems on MySQL

[Alexandre Vieira]

 the VARCHAR(23) on the userid PK
 and it makes a little difference but not enough for the application to 
 run
 in real time processing.

 It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

 MySQL is eating 179MB of RAM and 5,4% of CPU.

   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
   6229 mysql 455M  179M sleep   580   0:03.11 5,4% mysqld/68

 The machine has ~1GB of free memory. MySQL and InnoDB has free pages
 to grow and we have ~50% of free CPU time.

 Currently I can't use the replication server since the application
 running on top if this BD can only talk to 1 data source.
 At the moment it's also not possible to change the application in
 order to make it use the DB more wisely.

 Basically we have a table with lots of selects, lots of update, lots
 of inserts and deletes. Data manipulation is random, doesn't follow any
 specific pattern. All working concurrently.

 A big bottleneck is:

 8 queries inside InnoDB, 28 queries in queue

 1 read views open inside InnoDB

 Increasing innodb_thread_concurrency might help without causing any
 problems to the overall performance.

 Makes total sense if you read:
 http://peter-zaitsev.livejournal.com/9138.html

 Thanks in advance.

 BR
 AJ


 On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers 
 joh...@pixelated.netwrote:

 Ok, so I'm stumped?

 What kind of hardware is behind this thing?

 -JW

 On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira 
 nul...@gmail.comwrote:

 Hi Johnny,

 mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';

 ++-++---+---+-+-+---+--+---+
 | id | select_type | table  | type  | possible_keys | key |
 key_len | ref   | rows | Extra |

 ++-++---+---+-+-+---+--+---+
 |  1 | SIMPLE  | clientinfo | const | PRIMARY   | PRIMARY |
 23  | const |1 |   |

 ++-++---+---+-+-+---+--+---+
 1 row in set (0.53 sec)

 Thanks

 BR
 AJ





 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




 --
 Alexandre Vieira - nul...@gmail.com




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
Alexandre Vieira - nul...@gmail.com

Re: Performance problems on MySQL

[Johnny Withers]

It seems that when your index is PRIMARY on InnoDB tables, it's magic and is
part of the data thereby it is not included in the index_length field.

I have never noticed this. I don't think adding a new index will make a
difference.

You could try moving your log files to a different disk array than where
your data is. If you have binary and query logging enabled, it's probably a
good idea.



  --

*Johnny Withers*
jwith...@ecashsoftware.com
601.919.2275 x112
 [image: eCash Software Systems]


On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira nul...@gmail.com wrote:

 Hi,

 When creating a table in MySQL with a PK it automatically creates an INDEX,
 correct?

 The Index_Length: 0 is rather strange..I've created a new INDEX on top of
 my PK column on my test system and Index_Length shows a big value different
 from 0. Do you think this might have any impact?

 mysql show index from gwtraffic.clientinfo;


 ++++--+-+---+-+--++--++-+
 | Table  | Non_unique | Key_name   | Seq_in_index | Column_name |
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

 ++++--+-+---+-+--++--++-+
 | clientinfo |  0 | PRIMARY|1 | userid  |
 A |  548216 | NULL | NULL   |  | BTREE  | |
 | clientinfo |  1 | userid_idx |1 | userid  |
 A |  548216 | NULL | NULL   |  | BTREE  | |

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

 2 rows in set (0.01 sec)

 mysql show table status LIKE 'clientinfo';


 +++-++++-+-+--+---++-+-++---+--++-+
 | Name   | Engine | Version | Row_format | Rows   | Avg_row_length |
 Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
 Create_time | Update_time | Check_time | Collation |
 Checksum | Create_options | Comment |

 +++-++++-+-+--+---++-+-++---+--++-+
 | clientinfo | InnoDB |  10 | Compact| 548216 | 62 |
 34144256 |   0 |  5783552 | 0 |   NULL |
 2010-09-03 17:38:16 | NULL| NULL   | latin1_swedish_ci |
 NULL || InnoDB free: 1214464 kB |


 +++-++++-+-+--+---++-+-++---+--++-+
 1 row in set (0.00 sec)

 I'm trying to stress my test DB but can't measure any different results
 with or without the second INDEX.

 Regarding the disks.. the DB is updated 20+ times every second. Writing the
 log, checkpoint to disk, etc.. can cause that much load?

 BR
 AJ

Performance problems on MySQL

[Alexandre Vieira]

Hi list,

I'm having some performance problems on my 5.0.45-log DB running on Solaris
8 (V240).

We only have one table and two apps selecting, updating, inserting and
deleting massively and randomly from this table.

The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
one condition on an unique varchar indexed column.

The table has 500k records and has been OPTIMIZED 32h ago.

I've ran some sampling and:

A SELECT costs between 400ms and 600ms.
An UPDATE costs between 800ms and 1300ms.
A DELETE costs between 900ms and 1300ms
An INSERT costs always 900ms 2000ms.

At any given time the DB is handling 60-80 operations every second. It does
not scale any more than this because all the application connections to the
DB are being used and waiting for the DB to move. Our application queues
requests and it lags our clients.

The perl mysqltuner only whines about Query cache disabled but since I get
an ~20 updates every second I can't get any query cache hits, so I disabled
it.

If it makes any difference, we're replicating everything to another server
that don't serve any queries.

The DB has a 32 hour uptime.

Any help is most welcome.

You can find my.cnf, show status and show innodb status below.

Kind regards
Alex

###
my.cnf:

sql-mode=STRICT_ALL_TABLES
old_passwords   =1
skip-bdb
max_connections =100
max_allowed_packet  =1M
table_cache =512
sort_buffer_size=2M
read_buffer_size=4M
read_rnd_buffer_size=8M
thread_cache_size   =16
query_cache_limit   =32M
thread_concurrency  =8
max_heap_table_size =28M
tmp_table_size  =12M
innodb_buffer_pool_size =350M
innodb_additional_mem_pool_size =15M
innodb_log_buffer_size  =6M
innodb_flush_log_at_trx_commit  =1
innodb_lock_wait_timeout=50

###

mysql show status where Value NOT LIKE 0;
+---++
| Variable_name | Value  |
+---++
| Aborted_clients   | 88 |
| Aborted_connects  | 37590  |
| Binlog_cache_use  | 2148392|
| Bytes_received| 1117   |
| Bytes_sent| 8772   |
| Com_change_db | 1  |
| Com_delete| 4  |
| Com_insert| 3  |
| Com_select| 2  |
| Com_show_databases| 1  |
| Com_show_fields   | 3  |
| Com_show_status   | 2  |
| Com_show_tables   | 1  |
| Compression   | OFF|
| Connections   | 276096 |
| Created_tmp_files | 5  |
| Created_tmp_tables| 4  |
| Flush_commands| 1  |
| Handler_commit| 14 |
| Handler_prepare   | 14 |
| Handler_read_key  | 8  |
| Handler_read_rnd_next | 263|
| Handler_write | 395|
| Innodb_buffer_pool_pages_data | 6019   |
| Innodb_buffer_pool_pages_dirty| 1858   |
| Innodb_buffer_pool_pages_flushed  | 593993 |
| Innodb_buffer_pool_pages_free | 15784  |
| Innodb_buffer_pool_pages_misc | 597|
| Innodb_buffer_pool_pages_total| 22400  |
| Innodb_buffer_pool_read_ahead_rnd | 1  |
| Innodb_buffer_pool_read_requests  | 42797013   |
| Innodb_buffer_pool_reads  | 3497   |
| Innodb_buffer_pool_write_requests | 19096507   |
| Innodb_data_fsyncs| 4319683|
| Innodb_data_pending_fsyncs| 1  |
| Innodb_data_read  | 60231680   |
| Innodb_data_reads | 3514   |
| Innodb_data_writes| 4496721|
| Innodb_data_written   | 1259458560 |
| Innodb_dblwr_pages_written| 593993 |
| Innodb_dblwr_writes   | 12967  |
| Innodb_log_write_requests | 2111208|
| Innodb_log_writes | 4285654|
| Innodb_os_log_fsyncs  | 4303114|
| Innodb_os_log_pending_fsyncs  | 1  |
| Innodb_os_log_written | 3264897024 |
| Innodb_page_size  | 16384  |
| Innodb_pages_created  | 2476   |
| Innodb_pages_read | 3543   |
| Innodb_pages_written  | 593993 |
| Innodb_row_lock_time  | 1339668|
| Innodb_row_lock_time_avg  | 379|
| Innodb_row_lock_time_max  | 10631  |
| Innodb_row_lock_waits | 3531   |
| Innodb_rows_deleted

Re: Performance problems on MySQL

[Johnny Withers]

Can you show us the table structure and sample queries?

On Thursday, September 2, 2010, Alexandre Vieira nul...@gmail.com wrote:
 Hi list,

 I'm having some performance problems on my 5.0.45-log DB running on Solaris
 8 (V240).

 We only have one table and two apps selecting, updating, inserting and
 deleting massively and randomly from this table.

 The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
 one condition on an unique varchar indexed column.

 The table has 500k records and has been OPTIMIZED 32h ago.

 I've ran some sampling and:

 A SELECT costs between 400ms and 600ms.
 An UPDATE costs between 800ms and 1300ms.
 A DELETE costs between 900ms and 1300ms
 An INSERT costs always 900ms 2000ms.

 At any given time the DB is handling 60-80 operations every second. It does
 not scale any more than this because all the application connections to the
 DB are being used and waiting for the DB to move. Our application queues
 requests and it lags our clients.

 The perl mysqltuner only whines about Query cache disabled but since I get
 an ~20 updates every second I can't get any query cache hits, so I disabled
 it.

 If it makes any difference, we're replicating everything to another server
 that don't serve any queries.

 The DB has a 32 hour uptime.

 Any help is most welcome.

 You can find my.cnf, show status and show innodb status below.

 Kind regards
 Alex

 ###
 my.cnf:

 sql-mode                        =STRICT_ALL_TABLES
 old_passwords                   =1
 skip-bdb
 max_connections                 =100
 max_allowed_packet              =1M
 table_cache                     =512
 sort_buffer_size                =2M
 read_buffer_size                =4M
 read_rnd_buffer_size            =8M
 thread_cache_size               =16
 query_cache_limit               =32M
 thread_concurrency              =8
 max_heap_table_size             =28M
 tmp_table_size                  =12M
 innodb_buffer_pool_size         =350M
 innodb_additional_mem_pool_size =15M
 innodb_log_buffer_size          =6M
 innodb_flush_log_at_trx_commit  =1
 innodb_lock_wait_timeout        =50

 ###

 mysql show status where Value NOT LIKE 0;
 +---++
 | Variable_name                     | Value      |
 +---++
 | Aborted_clients                   | 88         |
 | Aborted_connects                  | 37590      |
 | Binlog_cache_use                  | 2148392    |
 | Bytes_received                    | 1117       |
 | Bytes_sent                        | 8772       |
 | Com_change_db                     | 1          |
 | Com_delete                        | 4          |
 | Com_insert                        | 3          |
 | Com_select                        | 2          |
 | Com_show_databases                | 1          |
 | Com_show_fields                   | 3          |
 | Com_show_status                   | 2          |
 | Com_show_tables                   | 1          |
 | Compression                       | OFF        |
 | Connections                       | 276096     |
 | Created_tmp_files                 | 5          |
 | Created_tmp_tables                | 4          |
 | Flush_commands                    | 1          |
 | Handler_commit                    | 14         |
 | Handler_prepare                   | 14         |
 | Handler_read_key                  | 8          |
 | Handler_read_rnd_next             | 263        |
 | Handler_write                     | 395        |
 | Innodb_buffer_pool_pages_data     | 6019       |
 | Innodb_buffer_pool_pages_dirty    | 1858       |
 | Innodb_buffer_pool_pages_flushed  | 593993     |
 | Innodb_buffer_pool_pages_free     | 15784      |
 | Innodb_buffer_pool_pages_misc     | 597        |
 | Innodb_buffer_pool_pages_total    | 22400      |
 | Innodb_buffer_pool_read_ahead_rnd | 1          |
 | Innodb_buffer_pool_read_requests  | 42797013   |
 | Innodb_buffer_pool_reads          | 3497       |
 | Innodb_buffer_pool_write_requests | 19096507   |
 | Innodb_data_fsyncs                | 4319683    |
 | Innodb_data_pending_fsyncs        | 1          |
 | Innodb_data_read                  | 60231680   |
 | Innodb_data_reads                 | 3514       |
 | Innodb_data_writes                | 4496721    |
 | Innodb_data_written               | 1259458560 |
 | Innodb_dblwr_pages_written        | 593993     |
 | Innodb_dblwr_writes               | 12967      |
 | Innodb_log_write_requests         | 2111208    |
 | Innodb_log_writes                 | 4285654    |
 | Innodb_os_log_fsyncs              | 4303114    |
 | Innodb_os_log_pending_fsyncs      | 1          |
 | Innodb_os_log_written             | 3264897024 |
 | Innodb_page_size                  | 16384      |
 | Innodb_pages_created              | 2476       |
 | Innodb_pages_read                 | 3543       |
 | Innodb_pages_written              | 593993

Re: Performance problems on MySQL

[John Daisley]

What is the hardware spec? Anything else running on the box?

Why are you replicating but not making use of the slave?

Can you post the output of SHOW CREATE TABLE?

Regards
John

On 2 September 2010 12:50, Alexandre Vieira nul...@gmail.com wrote:

 Hi list,

 I'm having some performance problems on my 5.0.45-log DB running on Solaris
 8 (V240).

 We only have one table and two apps selecting, updating, inserting and
 deleting massively and randomly from this table.

 The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
 one condition on an unique varchar indexed column.

 The table has 500k records and has been OPTIMIZED 32h ago.

 I've ran some sampling and:

 A SELECT costs between 400ms and 600ms.
 An UPDATE costs between 800ms and 1300ms.
 A DELETE costs between 900ms and 1300ms
 An INSERT costs always 900ms 2000ms.

 At any given time the DB is handling 60-80 operations every second. It does
 not scale any more than this because all the application connections to the
 DB are being used and waiting for the DB to move. Our application queues
 requests and it lags our clients.

 The perl mysqltuner only whines about Query cache disabled but since I
 get
 an ~20 updates every second I can't get any query cache hits, so I disabled
 it.

 If it makes any difference, we're replicating everything to another server
 that don't serve any queries.

 The DB has a 32 hour uptime.

 Any help is most welcome.

 You can find my.cnf, show status and show innodb status below.

 Kind regards
 Alex

 ###
 my.cnf:

 sql-mode=STRICT_ALL_TABLES
 old_passwords   =1
 skip-bdb
 max_connections =100
 max_allowed_packet  =1M
 table_cache =512
 sort_buffer_size=2M
 read_buffer_size=4M
 read_rnd_buffer_size=8M
 thread_cache_size   =16
 query_cache_limit   =32M
 thread_concurrency  =8
 max_heap_table_size =28M
 tmp_table_size  =12M
 innodb_buffer_pool_size =350M
 innodb_additional_mem_pool_size =15M
 innodb_log_buffer_size  =6M
 innodb_flush_log_at_trx_commit  =1
 innodb_lock_wait_timeout=50

 ###

 mysql show status where Value NOT LIKE 0;
 +---++
 | Variable_name | Value  |
 +---++
 | Aborted_clients   | 88 |
 | Aborted_connects  | 37590  |
 | Binlog_cache_use  | 2148392|
 | Bytes_received| 1117   |
 | Bytes_sent| 8772   |
 | Com_change_db | 1  |
 | Com_delete| 4  |
 | Com_insert| 3  |
 | Com_select| 2  |
 | Com_show_databases| 1  |
 | Com_show_fields   | 3  |
 | Com_show_status   | 2  |
 | Com_show_tables   | 1  |
 | Compression   | OFF|
 | Connections   | 276096 |
 | Created_tmp_files | 5  |
 | Created_tmp_tables| 4  |
 | Flush_commands| 1  |
 | Handler_commit| 14 |
 | Handler_prepare   | 14 |
 | Handler_read_key  | 8  |
 | Handler_read_rnd_next | 263|
 | Handler_write | 395|
 | Innodb_buffer_pool_pages_data | 6019   |
 | Innodb_buffer_pool_pages_dirty| 1858   |
 | Innodb_buffer_pool_pages_flushed  | 593993 |
 | Innodb_buffer_pool_pages_free | 15784  |
 | Innodb_buffer_pool_pages_misc | 597|
 | Innodb_buffer_pool_pages_total| 22400  |
 | Innodb_buffer_pool_read_ahead_rnd | 1  |
 | Innodb_buffer_pool_read_requests  | 42797013   |
 | Innodb_buffer_pool_reads  | 3497   |
 | Innodb_buffer_pool_write_requests | 19096507   |
 | Innodb_data_fsyncs| 4319683|
 | Innodb_data_pending_fsyncs| 1  |
 | Innodb_data_read  | 60231680   |
 | Innodb_data_reads | 3514   |
 | Innodb_data_writes| 4496721|
 | Innodb_data_written   | 1259458560 |
 | Innodb_dblwr_pages_written| 593993 |
 | Innodb_dblwr_writes   | 12967  |
 | Innodb_log_write_requests | 2111208|
 | Innodb_log_writes | 4285654|
 | Innodb_os_log_fsyncs  | 4303114|
 | Innodb_os_log_pending_fsyncs  | 1  |
 | Innodb_os_log_written | 3264897024 |
 | Innodb_page_size  | 16384  |
 | Innodb_pages_created  | 2476

Performance problems on MySQL

[Alexandre Vieira]

John, Johnny,

Thanks for the prompt answer.

mysql SHOW CREATE TABLE clientinfo;
++--+
| Table  | Create
Table
|
++--+
| clientinfo | CREATE TABLE `clientinfo` (
  `userid` varchar(21) NOT NULL default '',
  `units` float default NULL,
  `date_last_query` datetime default NULL,
  `last_acc` int(10) unsigned default NULL,
  `date_last_units` datetime default NULL,
  `notification` int(10) unsigned NOT NULL default '0',
  `package` char(1) default NULL,
  `user_type` varchar(5) default NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--+
1 row in set (0.00 sec)
mysql SHOW INDEX FROM clientinfo;
+++--+--+-+---+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---+-+--++--++-+
| clientinfo |  0 | PRIMARY  |1 | userid  |
A |  460056 | NULL | NULL   |  | BTREE  | |
+++--+--+-+---+-+--++--++-+
1 row in set (0.00 sec)


SELECT * FROM clientinfo WHERE userid='182106617';

UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
WHERE userid='152633876';

INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';

DELETE FROM clientinfo WHERE units='155618918';

There are no other type of queries.

We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.

TIA

BR
Alex


On Thu, Sep 2, 2010 at 1:52 PM, John Daisley daisleyj...@googlemail.comwrote:

 What is the hardware spec? Anything else running on the box?

 Why are you replicating but not making use of the slave?

 Can you post the output of SHOW CREATE TABLE?

 Regards
 John

Re: Performance problems on MySQL

[Jangita]

On 02/09/2010 4:46 p, Alexandre Vieira wrote:

John, Johnny,

Thanks for the prompt answer.


...

We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.

TIA

BR
Alex


Increase innodb_buffer_pool_size say to 1GB?
--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.com

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

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi Jangita,

I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB
of 350MB free.

| Innodb_buffer_pool_pages_data | 6020   |
| Innodb_buffer_pool_pages_dirty| 1837   |
| Innodb_buffer_pool_pages_flushed  | 673837 |
| Innodb_buffer_pool_pages_free | 15779  |
| Innodb_buffer_pool_pages_latched  | 0  |
| Innodb_buffer_pool_pages_misc | 601|
| Innodb_buffer_pool_pages_total| 22400  |
| Innodb_buffer_pool_read_ahead_rnd | 1  |
| Innodb_buffer_pool_read_ahead_seq | 0  |
| Innodb_buffer_pool_read_requests  | 48471963   |
| Innodb_buffer_pool_reads  | 3497   |
| Innodb_buffer_pool_wait_free  | 0  |
| Innodb_buffer_pool_write_requests | 21700478   |

Why would I need to increase?

Thanks

BR
AJ

On Thu, Sep 2, 2010 at 4:47 PM, Jangita jang...@jangita.com wrote:

 On 02/09/2010 4:46 p, Alexandre Vieira wrote:

 John, Johnny,

 Thanks for the prompt answer.

  ...

  We also run some other applications in the server, but nothing that
 consumes
 all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
 idle CPU time at any time.

 TIA

 BR
 Alex


 Increase innodb_buffer_pool_size say to 1GB?
 --
 Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
 Skype: jangita | GTalk: jangita.nyag...@gmail.com

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




-- 
Alexandre Vieira - nul...@gmail.com

RE: Performance problems on MySQL

[Travis Ard]

Have you considered adding a secondary index on the units column for your
delete queries?

DELETE FROM clientinfo WHERE units='155618918';

-Original Message-
From: Alexandre Vieira [mailto:nul...@gmail.com] 
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; joh...@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL

John, Johnny,

Thanks for the prompt answer.

mysql SHOW CREATE TABLE clientinfo;
++--




+
| Table  | Create
Table
|
++--




+
| clientinfo | CREATE TABLE `clientinfo` (
  `userid` varchar(21) NOT NULL default '',
  `units` float default NULL,
  `date_last_query` datetime default NULL,
  `last_acc` int(10) unsigned default NULL,
  `date_last_units` datetime default NULL,
  `notification` int(10) unsigned NOT NULL default '0',
  `package` char(1) default NULL,
  `user_type` varchar(5) default NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--




+
1 row in set (0.00 sec)
mysql SHOW INDEX FROM clientinfo;
+++--+--+-+-
--+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| clientinfo |  0 | PRIMARY  |1 | userid  |
A |  460056 | NULL | NULL   |  | BTREE  | |
+++--+--+-+-
--+-+--++--++-+
1 row in set (0.00 sec)


SELECT * FROM clientinfo WHERE userid='182106617';

UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
=now(),notification=0
WHERE userid='152633876';

INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';

DELETE FROM clientinfo WHERE units='155618918';

There are no other type of queries.

We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.

TIA

BR
Alex


On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
daisleyj...@googlemail.comwrote:

 What is the hardware spec? Anything else running on the box?

 Why are you replicating but not making use of the slave?

 Can you post the output of SHOW CREATE TABLE?

 Regards
 John





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

Re: Performance problems on MySQL

[Alexandre Vieira]

Hi Travis,

Sorry, bad copy/paste. That DELETE statement is wrong.

The application executes:

DELETE FROM clientinfo WHERE userid='x';

BR
AJ

On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard travis_...@hotmail.com wrote:

 Have you considered adding a secondary index on the units column for your
 delete queries?

 DELETE FROM clientinfo WHERE units='155618918';

 -Original Message-
 From: Alexandre Vieira [mailto:nul...@gmail.com]
 Sent: Thursday, September 02, 2010 8:46 AM
 To: John Daisley; joh...@pixelated.net
 Cc: mysql@lists.mysql.com
 Subject: Performance problems on MySQL

 John, Johnny,

 Thanks for the prompt answer.

 mysql SHOW CREATE TABLE clientinfo;

 ++--

 

 

 

 
 +
 | Table  | Create
 Table
 |

 ++--

 

 

 

 
 +
 | clientinfo | CREATE TABLE `clientinfo` (
  `userid` varchar(21) NOT NULL default '',
  `units` float default NULL,
  `date_last_query` datetime default NULL,
  `last_acc` int(10) unsigned default NULL,
  `date_last_units` datetime default NULL,
  `notification` int(10) unsigned NOT NULL default '0',
  `package` char(1) default NULL,
  `user_type` varchar(5) default NULL,
  PRIMARY KEY  (`userid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 ++--

 

 

 

 
 +
 1 row in set (0.00 sec)
 mysql SHOW INDEX FROM clientinfo;

 +++--+--+-+-
 --+-+--++--++-+
 | Table  | Non_unique | Key_name | Seq_in_index | Column_name |
 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

 +++--+--+-+-
 --+-+--++--++-+
 | clientinfo |  0 | PRIMARY  |1 | userid  |
 A |  460056 | NULL | NULL   |  | BTREE  | |

 +++--+--+-+-
 --+-+--++--++-+
 1 row in set (0.00 sec)


 SELECT * FROM clientinfo WHERE userid='182106617';

 UPDATE clientinfo SET

 units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
 =now(),notification=0
 WHERE userid='152633876';

 INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
 ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
 date_last_units=now(), notification=0, package='D', user_type='PRE';

 DELETE FROM clientinfo WHERE units='155618918';

 There are no other type of queries.

 We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

 We also run some other applications in the server, but nothing that
 consumes
 all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
 idle CPU time at any time.

 TIA

 BR
 Alex


 On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
 daisleyj...@googlemail.comwrote:

  What is the hardware spec? Anything else running on the box?
 
  Why are you replicating but not making use of the slave?
 
  Can you post the output of SHOW CREATE TABLE?
 
  Regards
  John
 
 
 




-- 
Alexandre Vieira - nul...@gmail.com

collation problems

[mysql]

Hi listers
mysql server here is

mysql-server-5.1.48-2.fc13.x86_64

this morning i created a message with a literal string in chinese in it. 
the messages in the application i used are stored in a mysql database, 
when you submit them, like in a sent folder.

With this chinese literal in it, i, however, got
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'

when i sent the message.

without the chinese literal, the message was stored in the mysql db 
correctly.


i, then, changed the connection names to utf8 and collation to utf8_bin 
and then the session parameters looked like:


mysql set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql set collation_connection = utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql show session variables like character_set%;
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results| utf8   |
| character_set_server | latin1 |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.00 sec)

mysql show session variables like collation%;
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | utf8_bin  |
| collation_database   | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)

mysql

i thought, if you set the collation_connection to utf8_bin, you may send 
fairly everything to mysql.



but still, when i tried to concat the new string (including the chinese 
characers) i got:


mysql update suomi_contacts2 set history = concat(now(), ' ', '' 
,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi 
Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will 
ever happen to you.\r\n 葛斯克 愛德華 /  台北市八德路四段\r\n\r\n\r\n 
\r\nsuomi\r\n', ' ', '--- ', history) where counter 
= 1127;

ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'



Question: What did i miss? what do i have to change?

Thanks in advance

suomi





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

Re: collation problems

[Ananda Kumar]

can u please list out the table structure...as collation can also be set at
column level

regards
anandkl

On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com wrote:

 Hi listers
 mysql server here is

 mysql-server-5.1.48-2.fc13.x86_64

 this morning i created a message with a literal string in chinese in it.
 the messages in the application i used are stored in a mysql database, when
 you submit them, like in a sent folder.
 With this chinese literal in it, i, however, got
 ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'

 when i sent the message.

 without the chinese literal, the message was stored in the mysql db
 correctly.

 i, then, changed the connection names to utf8 and collation to utf8_bin and
 then the session parameters looked like:

 mysql set names utf8;
 Query OK, 0 rows affected (0.00 sec)

 mysql set collation_connection = utf8_bin;
 Query OK, 0 rows affected (0.00 sec)

 mysql show session variables like character_set%;
 +--++
 | Variable_name| Value  |
 +--++
 | character_set_client | utf8   |
 | character_set_connection | utf8   |
 | character_set_database   | latin1 |
 | character_set_filesystem | binary |
 | character_set_results| utf8   |
 | character_set_server | latin1 |
 | character_set_system | utf8   |
 | character_sets_dir   | /usr/share/mysql/charsets/ |
 +--++
 8 rows in set (0.00 sec)

 mysql show session variables like collation%;
 +--+---+
 | Variable_name| Value |
 +--+---+
 | collation_connection | utf8_bin  |
 | collation_database   | latin1_swedish_ci |
 | collation_server | latin1_swedish_ci |
 +--+---+
 3 rows in set (0.00 sec)

 mysql

 i thought, if you set the collation_connection to utf8_bin, you may send
 fairly everything to mysql.


 but still, when i tried to concat the new string (including the chinese
 characers) i got:

 mysql update suomi_contacts2 set history = concat(now(), ' ', ''
 ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi
 Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever
 happen to you.\r\n 葛斯克 愛德華 /  台北市八德路四段\r\n\r\n\r\n \r\nsuomi\r\n', ' ',
 '--- ', history) where counter = 1127;
 ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'



 Question: What did i miss? what do i have to change?

 Thanks in advance

 suomi





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

Re: collation problems

[mysql]

Hi Ananda

table structure is:

mysql show full columns from suomi_contacts2;
+--+--+---+--+-+---+-+-+-+
| Field| Type | Collation | Null | Key |
Default   | Extra   | Privileges
  | Comment |
+--+--+---+--+-+---+-+-+-+
| name | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| firm | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| title| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| phone| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| phone_std| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| fax  | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| mail | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| standard_mail| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| comment  | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| status   | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| url  | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| businesscategory | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| address  | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| addon| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| givenname| text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| history  | longtext | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| favorit  | text | latin1_swedish_ci | YES  | |
NULL  | |
select,insert,update,references | |
| last_update  | timestamp| NULL  | NO   | |
CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
select,insert,update,references | |
| task_link| int(11)  | NULL  | YES  | |
NULL  | |
select,insert,update,references | |
| counter  | int(10) unsigned | NULL  | NO   | PRI |
NULL  | auto_increment  |
select,insert,update,references | |
+--+--+---+--+-+---+-+-+-+
20 rows in set (0.00 sec)

mysql

suomi

On 2010-08-31 14:52, Ananda Kumar wrote:
 can u please list out the table structure...as collation can also be set 
 at column level
 regards
 anandkl
 
 On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com 
 mailto:my...@ayni.com wrote:
 
 Hi listers
 mysql server here is
 
 mysql-server-5.1.48-2.fc13.x86_64
 
 this morning i created a message with a literal string in chinese in
 it. the messages in the application i used are stored in a mysql
 database, when you submit them, like in a sent folder.
 With this chinese literal in it, i, however, got
 ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'
 
 when i sent the message.
 
 without the chinese literal, the message was stored in the mysql db
 correctly.
 
 i, then, changed the connection names to utf8 and collation to
 utf8_bin and then the 

Re: collation problems

[Ananda Kumar]

can u please give the output of
desc suomi_contacts2;

2010/8/31 mysql my...@ayni.com

 Hi Ananda

 table structure is:

 mysql show full columns from suomi_contacts2;

 +--+--+---+--+-+---+-+-+-+
 | Field| Type | Collation | Null | Key |
 Default   | Extra   | Privileges
  | Comment |

 +--+--+---+--+-+---+-+-+-+
 | name | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | firm | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | title| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | phone| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | phone_std| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | fax  | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | mail | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | standard_mail| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | comment  | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | status   | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | url  | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | businesscategory | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | address  | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | addon| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | givenname| text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | history  | longtext | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | favorit  | text | latin1_swedish_ci | YES  | |
 NULL  | |
 select,insert,update,references | |
 | last_update  | timestamp| NULL  | NO   | |
 CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 select,insert,update,references | |
 | task_link| int(11)  | NULL  | YES  | |
 NULL  | |
 select,insert,update,references | |
 | counter  | int(10) unsigned | NULL  | NO   | PRI |
 NULL  | auto_increment  |
 select,insert,update,references | |

 +--+--+---+--+-+---+-+-+-+
 20 rows in set (0.00 sec)

 mysql

 suomi

 On 2010-08-31 14:52, Ananda Kumar wrote:
  can u please list out the table structure...as collation can also be set
  at column level
  regards
  anandkl
 
  On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com
   mailto:my...@ayni.com wrote:
 
  Hi listers
  mysql server here is
 
  mysql-server-5.1.48-2.fc13.x86_64
 
  this morning i created a message with a literal string in chinese in
  it. the messages in the application i used are stored in a mysql
  database, when you submit them, like in a sent folder.
  With this chinese literal in it, i, however, got
  ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'
 
  when i sent the message.
 
  without 

Re: collation problems

[mysql]

On 2010-08-31 15:17, Ananda Kumar wrote:
 desc suomi_contacts2;

mysql desc suomi_contacts2;
+--+--+--+-+---+-+
| Field| Type | Null | Key | Default   |
Extra   |
+--+--+--+-+---+-+
| name | text | YES  | | NULL  |
|
| firm | text | YES  | | NULL  |
|
| title| text | YES  | | NULL  |
|
| phone| text | YES  | | NULL  |
|
| phone_std| text | YES  | | NULL  |
|
| fax  | text | YES  | | NULL  |
|
| mail | text | YES  | | NULL  |
|
| standard_mail| text | YES  | | NULL  |
|
| comment  | text | YES  | | NULL  |
|
| status   | text | YES  | | NULL  |
|
| url  | text | YES  | | NULL  |
|
| businesscategory | text | YES  | | NULL  |
|
| address  | text | YES  | | NULL  |
|
| addon| text | YES  | | NULL  |
|
| givenname| text | YES  | | NULL  |
|
| history  | longtext | YES  | | NULL  |
|
| favorit  | text | YES  | | NULL  |
|
| last_update  | timestamp| NO   | | CURRENT_TIMESTAMP |
on update CURRENT_TIMESTAMP |
| task_link| int(11)  | YES  | | NULL  |
|
| counter  | int(10) unsigned | NO   | PRI | NULL  |
auto_increment  |
+--+--+--+-+---+-+
20 rows in set (0.00 sec)

mysql

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

Re: collation problems

[Ananda Kumar]

did u try changing the collation for history column to UTF8
and try the update.



2010/8/31 mysql my...@ayni.com

 On 2010-08-31 15:17, Ananda Kumar wrote:
  desc suomi_contacts2;

 mysql desc suomi_contacts2;

 +--+--+--+-+---+-+
 | Field| Type | Null | Key | Default   |
 Extra   |

 +--+--+--+-+---+-+
 | name | text | YES  | | NULL  |
|
 | firm | text | YES  | | NULL  |
|
 | title| text | YES  | | NULL  |
|
 | phone| text | YES  | | NULL  |
|
 | phone_std| text | YES  | | NULL  |
|
 | fax  | text | YES  | | NULL  |
|
 | mail | text | YES  | | NULL  |
|
 | standard_mail| text | YES  | | NULL  |
|
 | comment  | text | YES  | | NULL  |
|
 | status   | text | YES  | | NULL  |
|
 | url  | text | YES  | | NULL  |
|
 | businesscategory | text | YES  | | NULL  |
|
 | address  | text | YES  | | NULL  |
|
 | addon| text | YES  | | NULL  |
|
 | givenname| text | YES  | | NULL  |
|
 | history  | longtext | YES  | | NULL  |
|
 | favorit  | text | YES  | | NULL  |
|
 | last_update  | timestamp| NO   | | CURRENT_TIMESTAMP |
 on update CURRENT_TIMESTAMP |
 | task_link| int(11)  | YES  | | NULL  |
|
 | counter  | int(10) unsigned | NO   | PRI | NULL  |
 auto_increment  |

 +--+--+--+-+---+-+
 20 rows in set (0.00 sec)

 mysql

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

Re: collation problems

[mysql]

Hi Ananda
not sofar. But if you recommend it, i will give it a try.

thanks so much.

suomi

On 2010-08-31 15:41, Ananda Kumar wrote:

did u try changing the collation for history column to UTF8
and try the update.

2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com

On 2010-08-31 15:17, Ananda Kumar wrote:
  desc suomi_contacts2;

mysql desc suomi_contacts2;

+--+--+--+-+---+-+
| Field| Type | Null | Key | Default   |
Extra   |

+--+--+--+-+---+-+
| name | text | YES  | | NULL  |
|
| firm | text | YES  | | NULL  |
|
| title| text | YES  | | NULL  |
|
| phone| text | YES  | | NULL  |
|
| phone_std| text | YES  | | NULL  |
|
| fax  | text | YES  | | NULL  |
|
| mail | text | YES  | | NULL  |
|
| standard_mail| text | YES  | | NULL  |
|
| comment  | text | YES  | | NULL  |
|
| status   | text | YES  | | NULL  |
|
| url  | text | YES  | | NULL  |
|
| businesscategory | text | YES  | | NULL  |
|
| address  | text | YES  | | NULL  |
|
| addon| text | YES  | | NULL  |
|
| givenname| text | YES  | | NULL  |
|
| history  | longtext | YES  | | NULL  |
|
| favorit  | text | YES  | | NULL  |
|
| last_update  | timestamp| NO   | | CURRENT_TIMESTAMP |
on update CURRENT_TIMESTAMP |
| task_link| int(11)  | YES  | | NULL  |
|
| counter  | int(10) unsigned | NO   | PRI | NULL  |
auto_increment  |

+--+--+--+-+---+-+
20 rows in set (0.00 sec)

mysql

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





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

[SOLVED] Re: collation problems

[mysql]

Hi Ananda
that worked fine:

mysql alter table suomi_contacts2 modify history longtext character set 
utf8 collate utf8_bin;

Query OK, 6327 rows affected (0.34 sec)
Records: 6327  Duplicates: 0  Warnings: 0

mysql

mysql update suomi_contacts2 set history = concat(now(), ' ', '' 
,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi 
Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will 
ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n\r 
\nsuomi\r\n044 280 22 44\r\n079 239 29 01\r\n', ' ', 
'--- ', history) where counter = 1127;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql

Thank you very much

suomi

On 2010-08-31 15:41, Ananda Kumar wrote:

did u try changing the collation for history column to UTF8
and try the update.

2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com

On 2010-08-31 15:17, Ananda Kumar wrote:
  desc suomi_contacts2;

mysql desc suomi_contacts2;

+--+--+--+-+---+-+
| Field| Type | Null | Key | Default   |
Extra   |

+--+--+--+-+---+-+
| name | text | YES  | | NULL  |
|
| firm | text | YES  | | NULL  |
|
| title| text | YES  | | NULL  |
|
| phone| text | YES  | | NULL  |
|
| phone_std| text | YES  | | NULL  |
|
| fax  | text | YES  | | NULL  |
|
| mail | text | YES  | | NULL  |
|
| standard_mail| text | YES  | | NULL  |
|
| comment  | text | YES  | | NULL  |
|
| status   | text | YES  | | NULL  |
|
| url  | text | YES  | | NULL  |
|
| businesscategory | text | YES  | | NULL  |
|
| address  | text | YES  | | NULL  |
|
| addon| text | YES  | | NULL  |
|
| givenname| text | YES  | | NULL  |
|
| history  | longtext | YES  | | NULL  |
|
| favorit  | text | YES  | | NULL  |
|
| last_update  | timestamp| NO   | | CURRENT_TIMESTAMP |
on update CURRENT_TIMESTAMP |
| task_link| int(11)  | YES  | | NULL  |
|
| counter  | int(10) unsigned | NO   | PRI | NULL  |
auto_increment  |

+--+--+--+-+---+-+
20 rows in set (0.00 sec)

mysql

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





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

Re: master-slave replication sync problems.

[Todd Lyons]

On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine nor...@khine.net wrote:
 hello,
 i have a working master-slave replication, the problem i find is that
 if i restart the MASTER there is a difference in the MASTER_LOG_FILE
 and MASTER_LOG_POS on the SLAVE.

 what is the correct way to keep the two slaves in sync even after i
 restart the server. the way i do it now is to:

As one other stated, it should not lose position when you stop and
start the master or slave in a controlled manner.  And also stated was
that the way you were doing it was causing a loss of data, and you'll
need to use maatkit to fix it, or if you can afford some downtime,
stop mysql on both and copy the whole database(s) from master to slave
(and reconfigure replication of course).

On the slave, I'd be curious what the result was when you did only a
STOP SLAVE; and then START SLAVE; because in my estimation,
chances are it would have picked right up where it was supposed to.

Your issue is likely that your slave has a large slave_net_timeout
value (default is 3600 seconds IIRC).  That means your slave will take
1 hour to finally give up on the replication connection (that the
master closed on it), close the connection, and then reopen it.  Lower
that setting to something smaller, I've gone as low as 30 seconds, but
I would only do that on a reliable network.  Try lowering it to 300 (5
minutes) on your slave.  When you restart the master, the slave should
timeout after only 5 minutes and then resume replication.

-- 
Regards...      Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.  -- Marcus Aurealius

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

master-slave replication sync problems.

[Norman Khine]

hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.

what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:

[MASTER]
mysql show master status;

+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)

[SLAVE]
mysql stop slave;
mysql change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql start slave;

is this correct or is there a better way to do this?

thanks

-- 
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: master-slave replication sync problems.

[Ananda Kumar]

since u r starting slave by postion beyond master, and if some of the
changes are already present on slave, it would error out with duplicate.

You need
show slave status\G;
slave stop;
show slave status\G;..wait for few min..than again
show slave status\G;just to make sure...slave is in complete sync with
master...
Here u will see the master log file and position.
U need to use this, next time u start ur slave to sync with master.

regards
anandkl

On Thu, Aug 26, 2010 at 6:34 PM, Norman Khine nor...@khine.net wrote:

 hello,
 i have a working master-slave replication, the problem i find is that
 if i restart the MASTER there is a difference in the MASTER_LOG_FILE
 and MASTER_LOG_POS on the SLAVE.

 what is the correct way to keep the two slaves in sync even after i
 restart the server. the way i do it now is to:

 [MASTER]
 mysql show master status;


 +--+--+--+--+
 | mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
 mysql,information_schema |

 +--+--+--+--+
 1 row in set (0.00 sec)

 [SLAVE]
 mysql stop slave;
 mysql change master to MASTER_HOST='master.domain.com',
 MASTER_USER='replicator', MASTER_PASSWORD='xxx',
 MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
 mysql start slave;

 is this correct or is there a better way to do this?

 thanks

 --
 ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
 ǝq s,ʇǝן ʇǝʎ
 % .join( [ {'*':'@','^':'.'}.get(c,None) or
 chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: master-slave replication sync problems.

[a . smith]

That is really bad, you will loose changes.

You shouldnt have to do anything when rebooting either the master or  
slave. If the master is down, then the slave recieves no updates. If  
the slave is down, when it comes back up it checks the master log pos  
and plays thro any changes that are necessary to bring it up to date...






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

Re: master-slave replication sync problems.

[Ananda Kumar]

True,
But some times, this does not work and u need to know the master bin-log
file and position to start, so that there is no loss of data.

regards
anandkl

On Thu, Aug 26, 2010 at 6:39 PM, a.sm...@ukgrid.net wrote:

 That is really bad, you will loose changes.

 You shouldnt have to do anything when rebooting either the master or slave.
 If the master is down, then the slave recieves no updates. If the slave is
 down, when it comes back up it checks the master log pos and plays thro any
 changes that are necessary to bring it up to date...






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

Re: master-slave replication sync problems.

[Norman Khine]

i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?



On Thu, Aug 26, 2010 at 3:09 PM,  a.sm...@ukgrid.net wrote:
 That is really bad, you will loose changes.

 You shouldnt have to do anything when rebooting either the master or slave.
 If the master is down, then the slave recieves no updates. If the slave is
 down, when it comes back up it checks the master log pos and plays thro any
 changes that are necessary to bring it up to date...








-- 
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: master-slave replication sync problems.

[Ananda Kumar]

Yes,
You need to note down the master bin-log file name and position on the
slave, this is a must.

regards
anandkl




On Thu, Aug 26, 2010 at 7:34 PM, Norman Khine nor...@khine.net wrote:

 i see, so the best is to just stop slave and then check the master
 status, and when the master status syncs then i start the slave?



 On Thu, Aug 26, 2010 at 3:09 PM,  a.sm...@ukgrid.net wrote:
  That is really bad, you will loose changes.
 
  You shouldnt have to do anything when rebooting either the master or
 slave.
  If the master is down, then the slave recieves no updates. If the slave
 is
  down, when it comes back up it checks the master log pos and plays thro
 any
  changes that are necessary to bring it up to date...
 
 
 
 
 



 --
 ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
 ǝq s,ʇǝן ʇǝʎ
 % .join( [ {'*':'@','^':'.'}.get(c,None) or
 chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: master-slave replication sync problems.

[a . smith]

Quoting Norman Khine nor...@khine.net:


i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?





Well Im willing to hear from others experiences, but if you really  
shouldnt have to do anything. If you want you can issue a stop slave  
before the reboot and a start slave after, but it shouldnt make any  
difference. And Im not sure what you refer to when you talk about the  
master status syncing. The master will continue to increment the  
binlog when changes are made regardless of whether the slave is up. If  
the slave has been rebooted and is behind the master binlog pos it  
should automatically sync those changes when started...





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

Re: master-slave replication sync problems.

[Ananda Kumar]

Smith,
I never said, this wont work.Some times, there are chances of lossing data.

regards
anandkl



On Thu, Aug 26, 2010 at 8:48 PM, a.sm...@ukgrid.net wrote:

 Quoting Norman Khine nor...@khine.net:

 i see, so the best is to just stop slave and then check the master
 status, and when the master status syncs then i start the slave?




 Well Im willing to hear from others experiences, but if you really shouldnt
 have to do anything. If you want you can issue a stop slave before the
 reboot and a start slave after, but it shouldnt make any difference. And Im
 not sure what you refer to when you talk about the master status syncing.
 The master will continue to increment the binlog when changes are made
 regardless of whether the slave is up. If the slave has been rebooted and is
 behind the master binlog pos it should automatically sync those changes when
 started...





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

Re: master-slave replication sync problems.

[jitendra ranjan]

Hi,
 
The best way to use sync the master and slave is using mk-checksum tools. just 
google it and use the the tools for online sync of master and slave.
 
Jeetendra Ranjan
MySQL DBA

--- On Thu, 26/8/10, Norman Khine nor...@khine.net wrote:


From: Norman Khine nor...@khine.net
Subject: master-slave replication sync problems.
To: mysql@lists.mysql.com
Date: Thursday, 26 August, 2010, 6:34 PM


hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.

what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:

[MASTER]
mysql show master status;

+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)

[SLAVE]
mysql stop slave;
mysql change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql start slave;

is this correct or is there a better way to do this?

thanks

-- 
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: master-slave replication sync problems.

[a . smith]

Quoting Ananda Kumar anan...@gmail.com:


Smith,
I never said, this wont work.Some times, there are chances of lossing data.

regards
anandkl




If you have experience of this fair enough. Theres no reason it should  
make any difference, as everything is based upon the binlog file and  
position...





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

Re: master-slave replication sync problems.

[a . smith]

Quoting jitendra ranjan jitendra_ran...@yahoo.com:


Hi,
 
The best way to use sync the master and slave is using mk-checksum  
tools. just google it and use the the tools for online sync of  
master and slave.

 
Jeetendra Ranjan
MySQL DBA

Yes, data integrity isnt guaranteed with MySQL replication. So if you  
want to be sure good idea to use the maartkit tools...
Tho this isnt necessarily answering the original question, which is to  
do with the master and slave not showing the same binlog file and log  
position. If replication is working at all, these will be in sync  
(assuming the changes on the master arent happening faster than  
replication can keep up).





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

RE: master-slave replication sync problems.

[Daevid Vincent]

ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost

show slave status\G

If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...

*** 1. row ***
 Slave_IO_State:
Master_Host: 10.10.10.45
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: No
  Slave_SQL_Running: Yes

start slave; show slave status\G

Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.

*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.41
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: Yes
  Slave_SQL_Running: No
Replicate_Do_DB: agis_core_2008
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 1061
 Last_Error: Error 'Duplicate key name 'id_operator'' on
query. 

The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.

mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Repeat the above statements over and over until you see two YES rows.

*** 1. row ***

   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

 -Original Message-
 From: Norman Khine [mailto:nor...@khine.net] 
 Sent: Thursday, August 26, 2010 6:05 AM
 To: mysql@lists.mysql.com
 Subject: master-slave replication sync problems.
 
 hello,
 i have a working master-slave replication, the problem i find is that
 if i restart the MASTER there is a difference in the MASTER_LOG_FILE
 and MASTER_LOG_POS on the SLAVE.
 
 what is the correct way to keep the two slaves in sync even after i
 restart the server. the way i do it now is to:
 
 [MASTER]
 mysql show master status;
 
 +--+--+--+
 --+
 | mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
 mysql,information_schema |
 +--+--+--+
 --+
 1 row in set (0.00 sec)
 
 [SLAVE]
 mysql stop slave;
 mysql change master to MASTER_HOST='master.domain.com',
 MASTER_USER='replicator', MASTER_PASSWORD='xxx',
 MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
 mysql start slave;
 
 is this correct or is there a better way to do this?
 
 thanks


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

Re: master-slave replication sync problems.

[Shawn Green (MySQL)]

Hello List,

On 8/26/2010 3:00 PM, Daevid Vincent wrote:

ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost

show slave status\G

If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...

*** 1. row ***
 Slave_IO_State:
Master_Host: 10.10.10.45
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: No
  Slave_SQL_Running: Yes

start slave; show slave status\G

Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.

*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.41
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: Yes
  Slave_SQL_Running: No
Replicate_Do_DB: agis_core_2008
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 1061
 Last_Error: Error 'Duplicate key name 'id_operator'' on
query. 

The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.

mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Repeat the above statements over and over until you see two YES rows.

*** 1. row ***

   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes


-Original Message-
From: Norman Khine [mailto:nor...@khine.net] 
Sent: Thursday, August 26, 2010 6:05 AM

To: mysql@lists.mysql.com
Subject: master-slave replication sync problems.

hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.

what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:

[MASTER]
mysql show master status;

+--+--+--+
--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+
--+
1 row in set (0.00 sec)

[SLAVE]
mysql stop slave;
mysql change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql start slave;

is this correct or is there a better way to do this?

thanks





To me, it appears that many of you are not fully versed in the theory of 
operations for how MySQL replication actually functions. Granted, there 
are two formats for replication (ROW and STATEMENT) but the general 
process remains the same. Here is a nutshell summary of the process.


** on the master **

m1) The MySQL master is instructed to change some data.

m2) The data is changed and the results are committed to disk. If you 
rollback the changes before you commit them, then there is nothing to 
replicate. Only the InnoDB engine supports this type of rollback.


m3) The change committed in step 2 is written to the binary log

(repeat from step m1 until the Master is shutdown)


** on the slave - the IO thread **
(assuming that the slave is already configured with compatible data, a 
starting position, and the proper credentials to act as a slave)


o1) The SLAVE IO thread requests information from the master's binary 
logs. This information is identified by a file name and a byte offset 
from the start of that file


o2) The SLAVE IO thread copies all available information from the 
master's binary logs into a local copy of those logs known as the relay 
logs.


(repeat from o1 until the SLAVE IO thread is stopped(by error or by 
command) or the slave is shutdown)


** on the slave - the SQL thread **

s1) Once an unapplied change has been completely buffered into the relay 
logs, the SLAVE SQL thread attempts to apply the change to the slave's 
data.


s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the 
correct format) into the slave's binary log.


(repeat from s1 until the SLAVE SQL thread is stopped (by error or by 
command) or the slave is shutdown)


**
As you can tell by this very simplified process description, there is no 
attempt to rectify one dataset to the other. Replication operates under 
the principle that if you perform identical changes to identical sets of 
data, you will end up with identical end results.


Various replication filters can omit certain changes

Problems posting to the list lists.mysql.com

[Frank Becker]

Hello,

I tried to response to a posting on the list named in the subject. But 
it is rejected with the following message:


 SMTP error from remote mail server after end of data:
host lists.mysql.com [213.136.52.31]: 552 spam score exceeded 
threshold (#5.6.1)



Neither this emailaddress is used for spam purposes nor I offered products.

Please, how can I use the list so that I can response to posts?

I read the list via gmane.org

Best regards
Frank Becker

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

Re: Problems posting to the list lists.mysql.com

[mos]

At 10:51 AM 2/15/2010, Frank Becker wrote:

Hello,

I tried to response to a posting on the list named in the subject. But it 
is rejected with the following message:


 SMTP error from remote mail server after end of data:
host lists.mysql.com [213.136.52.31]: 552 spam score exceeded 
threshold (#5.6.1)



Neither this emailaddress is used for spam purposes nor I offered products.

Please, how can I use the list so that I can response to posts?

I read the list via gmane.org

Best regards
Frank Becker


Frank,
 This has happened before. Please contact  webmas...@mysql.com and 
they may be able to solve the problem.


http://www.google.ca/#q=552+spam+score+exceeded+threshold+mysqlhl=ensa=2fp=8e6c6930b7d53e73

Mike 



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

Re: Problems posting to the list lists.mysql.com

[Octavian Rasnita]

Is the MySQL mailing list admin subscribed to the list? I have also sent a 
message or more telling about this issue, with no response.

--
Octavian

mos mo...@fastmail.fm wrote in message 
news:6.0.0.22.2.20100215120015.02cd4...@mail.messagingengine.com...
 At 10:51 AM 2/15/2010, Frank Becker wrote:
Hello,

I tried to response to a posting on the list named in the subject. But it 
is rejected with the following message:

  SMTP error from remote mail server after end of data:
 host lists.mysql.com [213.136.52.31]: 552 spam score exceeded 
 threshold (#5.6.1)


Neither this emailaddress is used for spam purposes nor I offered 
products.

Please, how can I use the list so that I can response to posts?

I read the list via gmane.org

Best regards
Frank Becker

 Frank,
  This has happened before. Please contact  webmas...@mysql.com and 
 they may be able to solve the problem.

 http://www.google.ca/#q=552+spam+score+exceeded+threshold+mysqlhl=ensa=2fp=8e6c6930b7d53e73

 Mike 




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

Problems with slave_skip_errors on replication

[Wagner Bianchi]

Hi friends,

Last weekend I made an environment that use a MySQL Server version 4.1 that
was defined to be the MASTER and other one version 5.1 defined as SLAVE.

Because the application that was concept working over exception, often the
SLAVE server got new error and replication stops.

Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you
may see specified after this massage, but, the replication continue stops,
even after this configurations.

mysql show variables like 'slave_skip_errors';
+---+---+
| Variable_name | Value |
+---+---+
| slave_skip_errors | 1 |
+---+---+
1 row in set (0.00 sec)
mysql show slave status\G
*** 1. row ***
   Slave_IO_State: Queueing master event to the relay log
  Master_Host: 172.28.8.70
  Master_User: slave
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: bmg58-bin.000265
  Read_Master_Log_Pos: 251871
   Relay_Log_File: pid-file-relay-bin.07
Relay_Log_Pos: 961348
Relay_Master_Log_File: bmg58-bin.03
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '731493' for key
'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
ocu_data, ocu_obs, login_responsavel, ocu_ip)
VALUES( null, 67,
'C986CC89AC1C071835E341D18011D25Z', now(),
'x.', 'x.sp', null)'
 Skip_Counter: 0
  Exec_Master_Log_Pos: 952913
  Relay_Log_Space: 264590369
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '731493' for key
'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
ocu_data, ocu_obs, login_responsavel, ocu_ip)
VALUES( null, 67,
'C986CC89AC1C071835E341D18011D25Z', now(),
'x', 'x.sp', null)'
1 row in set (0.00 sec)

Have other thing to do, or this problem is made by the mix of versions?

Best regards!
-- 
Wagner Bianchi - Web System Developer and Database Administrator
Phone: (31) 8654-9510 / 3272-0226
E-mail: wagnerbianch...@gmail.com
Lattes: http://lattes.cnpq.br/2041067758113940
Twitter: http://twitter.com/wagnerbianchi
Skype: infodbacet

Re: Problems with slave_skip_errors on replication

[Suresh Kuna]

Hi Wagner,

You have to start the server with the option as below for skipping the
error.

--slave-skip-errorr= 1062 or all

1062  - will skip the your error as the error number is 1062 and all will
skip all the errors.

You have to mention specific error numbers to skip the same.


-- 
Thanks
Suresh Kuna
MySQL DBA


On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi
wagnerbianch...@gmail.comwrote:

 Hi friends,

 Last weekend I made an environment that use a MySQL Server version 4.1 that
 was defined to be the MASTER and other one version 5.1 defined as SLAVE.

 Because the application that was concept working over exception, often the
 SLAVE server got new error and replication stops.

 Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you
 may see specified after this massage, but, the replication continue stops,
 even after this configurations.

 mysql show variables like 'slave_skip_errors';
 +---+---+
 | Variable_name | Value |
 +---+---+
 | slave_skip_errors | 1 |
 +---+---+
 1 row in set (0.00 sec)
 mysql show slave status\G
 *** 1. row ***
   Slave_IO_State: Queueing master event to the relay log
  Master_Host: 172.28.8.70
  Master_User: slave
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: bmg58-bin.000265
  Read_Master_Log_Pos: 251871
   Relay_Log_File: pid-file-relay-bin.07
Relay_Log_Pos: 961348
Relay_Master_Log_File: bmg58-bin.03
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '731493' for key
 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
 INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
 ocu_data, ocu_obs, login_responsavel, ocu_ip)
 VALUES( null, 67,
 'C986CC89AC1C071835E341D18011D25Z', now(),
 'x.', 'x.sp', null)'
 Skip_Counter: 0
  Exec_Master_Log_Pos: 952913
  Relay_Log_Space: 264590369
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '731493' for key
 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
 INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
 ocu_data, ocu_obs, login_responsavel, ocu_ip)
 VALUES( null, 67,
 'C986CC89AC1C071835E341D18011D25Z', now(),
 'x', 'x.sp', null)'
 1 row in set (0.00 sec)

 Have other thing to do, or this problem is made by the mix of versions?

 Best regards!
 --
 Wagner Bianchi - Web System Developer and Database Administrator
 Phone: (31) 8654-9510 / 3272-0226
 E-mail: wagnerbianch...@gmail.com
 Lattes: http://lattes.cnpq.br/2041067758113940
 Twitter: http://twitter.com/wagnerbianchi
 Skype: infodbacet

Re: Problems with slave_skip_errors on replication

[Wagner Bianchi]

Ok, Suresh. . .I started MySQL with slave_skip_errors = all and It solve the
problem!

Thanks.
WB
2010/1/25 Suresh Kuna sureshkumar...@gmail.com

 Hi Wagner,

 You have to start the server with the option as below for skipping the
 error.

 --slave-skip-errorr= 1062 or all

 1062  - will skip the your error as the error number is 1062 and all will
 skip all the errors.

 You have to mention specific error numbers to skip the same.


 --
 Thanks
 Suresh Kuna
 MySQL DBA



 On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi wagnerbianch...@gmail.com
  wrote:

 Hi friends,

 Last weekend I made an environment that use a MySQL Server version 4.1
 that
 was defined to be the MASTER and other one version 5.1 defined as SLAVE.

 Because the application that was concept working over exception, often the
 SLAVE server got new error and replication stops.

 Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as
 you
 may see specified after this massage, but, the replication continue stops,
 even after this configurations.

 mysql show variables like 'slave_skip_errors';
 +---+---+
 | Variable_name | Value |
 +---+---+
 | slave_skip_errors | 1 |
 +---+---+
 1 row in set (0.00 sec)
 mysql show slave status\G
 *** 1. row ***
   Slave_IO_State: Queueing master event to the relay log
  Master_Host: 172.28.8.70
  Master_User: slave
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: X58-bin.000265
  Read_Master_Log_Pos: 251871
   Relay_Log_File: pid-file-relay-bin.07
Relay_Log_Pos: 961348
Relay_Master_Log_File: X58-bin.03
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '731493' for key
 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
 INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
 ocu_data, ocu_obs, login_responsavel, ocu_ip)
 VALUES( null, 67,
 'C986CC89AC1C071835E341D18011D25Z', now(),
 'x.', 'x.sp', null)'
 Skip_Counter: 0
  Exec_Master_Log_Pos: 952913
  Relay_Log_Space: 264590369
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '731493' for key
 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT
 INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo,
 ocu_data, ocu_obs, login_responsavel, ocu_ip)
 VALUES( null, 67,
 'C986CC89AC1C071835E341D18011D25Z', now(),
 'x', 'x.sp', null)'
 1 row in set (0.00 sec)

 Have other thing to do, or this problem is made by the mix of versions?

 Best regards!

Binary log problems

[Jørn Dahl-Stamnes]

I tried to look at the binary logs by using phpMyAdmin, but got an error:

SQL error:
SHOW BINLOG EVENTS LIMIT 0, 30;

MySQL said:
#1220 - Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O 
error.

From the error-file:

100111 16:15:40 [ERROR] Error in Log_event::read_log_event(): 'Found invalid 
event in binary log', data_len: 100, event_type: 2
100111 16:16:51 [ERROR] Error in Log_event::read_log_event(): 'Found invalid 
event in binary log', data_len: 100, event_type: 2
100111 16:17:08 [ERROR] Error in Log_event::read_log_event(): 'Found invalid 
event in binary log', data_len: 100, event_type: 2

What is causing this? How can I fix this?

I'm using version mySQL 5.1.42.

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

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

Problems with auto_increment updating when (i think) it shouldn't

[Proemial]

Hey folks.  I'm getting some weird behaviour out of Auto_increment.
If I enter a attempt to INSERT a row into a table with a UNIQUE index,
where the insert would violate uniqueness of existing data, I'm seeing
the auto_increment increase even though the insert fails.

The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

First noticed through a script operating over ODBC, but replicated by
hand through the query browser.

I couldn't see anything in the ref manual stating this as standard
behaviour -- but I easily could have missed something there.  Can
someone point me in the right direction?

Thank you!
Martin

Using Mysql 5.1.34
TEST CASE:

CREATE TABLE  `test`.`test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
UNIQUE KEY `index_2` (`name`)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert some values


1, 'test'
2, 'test2'
3, 'test3'


SHOW TABLE STATUS
Nametest_table
Engine  InnoDB
Version 10
Row_format  Compact
Rows3
Avg_row_length  5461
Data_length 16384
Max_data_length 0
Index_length16384
Data_free   0
Auto_increment  4
Create_time 2009-08-07 09:33:04
Update_time 
Check_time  
Collation   latin1_swedish_ci
Checksum
Create_options  
Comment 

---
INSERT INTO test.test_table (name) VALUES ('test')

SHOW TABLE STATUS
Nametest_table
...
Auto_increment  5

---
INSERT IGNORE test.test_table (name) VALUES ('test')

SHOW TABLE STATUS
Nametest_table
...
Auto_increment  6



-- 
---
This is a signature.

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

Re: Problems with auto_increment updating when (i think) it shouldn't

[Johnny Withers]

It will also update the auto_increment column when you ROLLBACK a failed
insert:

mysql USE test;
Database changed
mysql SELECT * FROM t1\G
Empty set (0.00 sec)
mysql DROP TABLE t1;
Query OK, 0 rows affected (0.06 sec)
mysql
mysql CREATE TABLE t1(
- id INT UNSIGNED NOT NULL AUTO_INCREMENT,
- c1 VARCHAR(255),
- PRIMARY KEY(id)
- ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST1');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST2');
Query OK, 1 row affected (0.00 sec)
mysql ROLLBACK;
Query OK, 0 rows affected (0.02 sec)
mysql SHOW CREATE TABLE t1\G
*** 1. row ***
   Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB *AUTO_INCREMENT=3* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql SELECT * FROM t1\G
Empty set (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST3');
Query OK, 1 row affected (0.03 sec)
mysql SELECT * FROM t1\G
*** 1. row ***
id: 3
c1: TEST3
1 row in set (0.00 sec)
mysql

I believe this is how it has to work. In the event that I start a
transaction, then another transaction starts, mine fails, the other
completes and commit's, it has to get ID #3 and not ID #1. At the time the
transaction was taking place, ID #1 and #2 were in use.

Essentially, your SQL statement is a single transaction with AUTO_COMMIT set
to '1'.



On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote:

 Hey folks.  I'm getting some weird behaviour out of Auto_increment.
 If I enter a attempt to INSERT a row into a table with a UNIQUE index,
 where the insert would violate uniqueness of existing data, I'm seeing
 the auto_increment increase even though the insert fails.

 The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

 First noticed through a script operating over ODBC, but replicated by
 hand through the query browser.

 I couldn't see anything in the ref manual stating this as standard
 behaviour -- but I easily could have missed something there.  Can
 someone point me in the right direction?

 Thank you!
 Martin

 Using Mysql 5.1.34
 TEST CASE:

 CREATE TABLE  `test`.`test_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
 UNIQUE KEY `index_2` (`name`)
 )
 ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 insert some values

 
 1, 'test'
 2, 'test2'
 3, 'test3'
 

 SHOW TABLE STATUS
 Nametest_table
 Engine  InnoDB
 Version 10
 Row_format  Compact
 Rows3
 Avg_row_length  5461
 Data_length 16384
 Max_data_length 0
 Index_length16384
 Data_free   0
 Auto_increment  4
 Create_time 2009-08-07 09:33:04
 Update_time
 Check_time
 Collation   latin1_swedish_ci
 Checksum
 Create_options
 Comment

 ---
 INSERT INTO test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Nametest_table
 ...
 Auto_increment  5

 ---
 INSERT IGNORE test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Nametest_table
 ...
 Auto_increment  6



 --
 ---
 This is a signature.

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




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Problems with auto_increment updating when (i think) it shouldn't

[Proemial]

Hmm, that makes sense.  I should have thought of that.  Thanks!

On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withersjoh...@pixelated.net wrote:
 It will also update the auto_increment column when you ROLLBACK a failed
 insert:

 mysql USE test;
 Database changed
 mysql SELECT * FROM t1\G
 Empty set (0.00 sec)
 mysql DROP TABLE t1;
 Query OK, 0 rows affected (0.06 sec)
 mysql
 mysql CREATE TABLE t1(
     - id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     - c1 VARCHAR(255),
     - PRIMARY KEY(id)
     - ) ENGINE=InnoDB;
 Query OK, 0 rows affected (0.13 sec)
 mysql START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST1');
 Query OK, 1 row affected (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST2');
 Query OK, 1 row affected (0.00 sec)
 mysql ROLLBACK;
 Query OK, 0 rows affected (0.02 sec)
 mysql SHOW CREATE TABLE t1\G
 *** 1. row ***
    Table: t1
 Create Table: CREATE TABLE `t1` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `c1` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)
 mysql SELECT * FROM t1\G
 Empty set (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST3');
 Query OK, 1 row affected (0.03 sec)
 mysql SELECT * FROM t1\G
 *** 1. row ***
 id: 3
 c1: TEST3
 1 row in set (0.00 sec)
 mysql

 I believe this is how it has to work. In the event that I start a
 transaction, then another transaction starts, mine fails, the other
 completes and commit's, it has to get ID #3 and not ID #1. At the time the
 transaction was taking place, ID #1 and #2 were in use.

 Essentially, your SQL statement is a single transaction with AUTO_COMMIT set
 to '1'.



 On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote:

 Hey folks.  I'm getting some weird behaviour out of Auto_increment.
 If I enter a attempt to INSERT a row into a table with a UNIQUE index,
 where the insert would violate uniqueness of existing data, I'm seeing
 the auto_increment increase even though the insert fails.

 The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

 First noticed through a script operating over ODBC, but replicated by
 hand through the query browser.

 I couldn't see anything in the ref manual stating this as standard
 behaviour -- but I easily could have missed something there.  Can
 someone point me in the right direction?

 Thank you!
 Martin

 Using Mysql 5.1.34
 TEST CASE:

 CREATE TABLE  `test`.`test_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
 UNIQUE KEY `index_2` (`name`)
 )
 ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 insert some values

 
 1, 'test'
 2, 'test2'
 3, 'test3'
 

 SHOW TABLE STATUS
 Name    test_table
 Engine  InnoDB
 Version 10
 Row_format      Compact
 Rows    3
 Avg_row_length  5461
 Data_length     16384
 Max_data_length 0
 Index_length    16384
 Data_free       0
 Auto_increment  4
 Create_time     2009-08-07 09:33:04
 Update_time
 Check_time
 Collation       latin1_swedish_ci
 Checksum
 Create_options
 Comment

 ---
 INSERT INTO test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Name    test_table
 ...
 Auto_increment  5

 ---
 INSERT IGNORE test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Name    test_table
 ...
 Auto_increment  6



 --
 ---
 This is a signature.

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




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
---
This is a signature.

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

Replication problems: slave fails to update

[Proemial]

I'm trying to resolve a frustrating replication problem with my databases.

The master contains a number of schema, only using Innodb tables.
Updates happen regularly, usually using bulk inserts of the form
INSERT ... ON DUPLICATE UPDATE.  Data is mostly numbers.  The missing
queries contain no non-deterministic functions, no BLOB/TEXT fields,
no triggers or procedures, and no variables.  The queries are
generated by various C programs, connecting through Connector/ODBC.

typical table (explain results):
'nodeID', 'int(10) unsigned', 'NO', 'PRI', '', ''
'calculationID', 'int(10) unsigned', 'NO', 'PRI', '', ''
'columnID', 'tinyint(3) unsigned', 'NO', 'PRI', '1', ''
'value', 'double', 'NO', '', '', ''
'lastUpdate', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update
CURRENT_TIMESTAMP

typical bulk insert:
INSERT INTO risk.risk_node_tree (nodeID, calculationID, columnID,
value, lastUpdate)
VALUES (1,2,1,1000,null),... ON DUPLICATE KEY UPDATE value=VALUES(value)

Replication is active, and I am able to test it by doing single point
modifications through the query browser.

However, the bulk inserts seem to vanish: The Master updates, but the
slave does not.  There are no errors in the log file.  SHOW SLAVE
STATUS states no problems.

I have attempted changing binlog_format, and have received the same
results on all three settings.  The Master has no settings to ignore
any particular schema.

Using the query browser, and running the same exact query with the
same user, results in the query properly replicating.

Both instances are 5.1.34.

This is causing me to have to resynchronize the databases every night,
which is getting to be something of a chore.

Does anyone have any idea what might be happening, or could suggest an
avenue of investigation?  Any help would be greatly appreciated.

Martin

-- 
---
This is a signature.

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

Re: Problems After MySql 5.1.34 - THANKS

[Johnny Stork]

Thanks to everyone that offered all the valuable suggestions. SImply 
replaced the shared-community with shared-compat and all seesm fine. 
Will live with, or deal with, the missmatch in php libraries later, but 
for now everything seems to work.


Thanks again to everyone that offered such detailed and helpful 
suggestions :)


Gary Smith wrote:
You are right.  I misspoke regarding mysql - php - apache hell.  It happens anytime an interface changes.  



From: Mark [ad...@asarian-host.net]
Sent: Thursday, May 07, 2009 5:57 AM
To: mysql@lists.mysql.com
Subject: RE: Problems After MySql 5.1.34

Gary wrote:

  

Welcome to the hell that is php + apache + mysql. If you upgrade your
MySql (especially major versions 5.0 = 5.1) you will also need to
recompile php against the new MySql client libs. We've had very
limited success trying to get it to work otherwise.



Well, you don't actually have to recompile PHP entirely, of course: just
its mysql.so extension.

@TS: Other than that, you basically need to recompile *everything* (or its
mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
so you'd need to build DBD:mysql as well (same for Python, etc).

Walter wrote:

  

Any (major)upgrade of mysql client requires the dependent subsystem to
upgrade also. Anything else would be careless since you do not know if the
interface has changed.



Actually, you *do* know: that's what the changelog is for. :) When C
header changes are made, an upgrade is in order. If not, when upgrading
between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
won't need to recompile all system-wide MySQL client extensions. I've done
this many times, without issue: you just need to be absolutely sure no
header changes were made (when in doubt, recompile).

I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
working just fine, but I spent several hours recompiling MySL client
stuff; without doing so, your apps will likely behave erratically, or just
segfault altogether.

This isn't a MySL hell exclusively, btw. You'll get the same issue
upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
beast.

- Mark


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@openenterprise.ca

  


--
_
Johnny Stork

Open Enterprise Solutions
Empowering Business With Open Solutions
http://www.openenterprise.ca

Mountain Hosting
Secure Hosting Solutions for Business
http://www.mountainhosting.ca


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

Re: Problems After MySql 5.1.34

[walter harms]

there is nothing you can make.
Any (major)upgrade of mysql client requires the dependent subsystem to upgrade 
also.
Anything else would be careless since you do not know if the interface has 
changed.

basicly you can install both version of libraries and hope for the best. i 
would do this only as last
rescue if an update is not possible. it is in general not clever.

re,
 wh

Gary Smith schrieb:
 Johnny, 
 
 Welcome to the hell that is php + apache + mysql.  If you upgrade your MySql 
 (especially major versions 5.0 = 5.1) you will also need to recompile php 
 against the new MySql client libs.  We've had very limited success trying to 
 get it to work otherwise.
 
 This is why you are receiving the error through PHP.
 
 Of course, I could be wrong, in which case I know people will probably jump 
 me for it.  If this is the case, please do as I would like to be wrong here 
 as it would make my compiling life easier every time I update MySql on all of 
 my boxes.
 
 Gary
 
 
 From: Johnny Stork [li...@openenterprise.ca]
 Sent: Wednesday, May 06, 2009 1:03 PM
 Cc: mysql@lists.mysql.com
 Subject: Re: Problems After MySql 5.1.34
 
 Typo, moved from 5.0.67 to 5.1.34
 
 Johnny Stork wrote:
 I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from
 5.64. Access to the db seesm fine from the shell, phpmyadmin or even
 the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or
 running a pear update produces the errors below. I created a couple
 of sl but this did not seem to fix the problem. Below is the error and
 contents of /usr/lib




 r...@asterisk:~# pear update
 PHP Warning:  PHP Startup: Unable to load dynamic library
 '/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15:
 version `libmysqlclient_15' not found (required by
 /usr/lib/php/modules/mysql.so) in Unknown on line 0
 PHP Warning:  PHP Startup: Unable to load dynamic library
 '/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15:
 version `libmysqlclient_15' not found (required by
 /usr/lib/php/modules/mysqli.so) in Unknown on line 0
 Segmentation fault



 r...@asterisk:~# ls -la /usr/lib/libmy*

 lrwxrwxrwx 1 root root  26 May  6 09:52
 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0
 lrwxrwxrwx 1 root root  28 May  6 11:26
 /usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so
 lrwxrwxrwx 1 root root  28 May  6 11:18
 /usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so
 lrwxrwxrwx 1 root root  26 May  6 09:52
 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0
 -rwxr-xr-x 1 root root 2052884 Mar 31 22:48
 /usr/lib/libmysqlclient_r.so.16.0.0
 lrwxrwxrwx 1 root root  24 May  6 09:52 /usr/lib/libmysqlclient.so
 - libmysqlclient.so.16.0.0
 lrwxrwxrwx 1 root root  26 May  6 11:14
 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so
 lrwxrwxrwx 1 root root  24 May  6 09:52
 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0
 -rwxr-xr-x 1 root root 2044464 Mar 31 22:48
 /usr/lib/libmysqlclient.so.16.0.0


 
 --


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

Re: Problems After MySql 5.1.34

[Joerg Bruehe]

Hi Johnny, all!


Johnny Stork wrote:
 Typo, moved from 5.0.67 to 5.1.34

Ok, that helps - I checked the contents.

 
 Johnny Stork wrote:
 I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from
 5.64. Access to the db seesm fine from the shell, phpmyadmin or even
 the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or
 running a pear update produces the errors below. I created a couple
 of sl but this did not seem to fix the problem. Below is the error and
 contents of /usr/lib

Somewhere during this upgrade you seem to have lost the shared libraries
of the MySQL client side which you had with version 5.0.67:
  /usr/lib/libmysqlclient_r.so.15.0.0
  /usr/lib/libmysqlclient.so.15.0.0
MySQL 5.0 comes with version 15 of these libraries,
MySQL 5.1 comes with version 16.


How these are handled depends on how you install MySQL (assuming Linux):

a) If you use tar.gz packages, then MySQL 5.0 has brought version 15
   and created symlinks from the unversioned name to the versioned:
 /usr/lib/libmysqlclient.so - /usr/lib/libmysqlclient.so.15
 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so.15.0.0
   /usr/lib/libmysqlclient.so.15.0.0 would be a real file.
   (Similar for libmysqlclient_r.so)

   Then, upgrading to 5.1 would bring version 16:
 /usr/lib/libmysqlclient.so - /usr/lib/libmysqlclient.so.16
 /usr/lib/libmysqlclient.so.16 - /usr/lib/libmysqlclient.so.16.0.0
   Again, /usr/lib/libmysqlclient.so.16.0.0 would be a real file.

   Note that this installation changes the symlimk for the unversioned
   name, but would not touch version 15, there still is
 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so.15.0.0
   /usr/lib/libmysqlclient.so.15.0.0 would still exist as a real file.

b) If you use RPMs, then installing MySQL 5.0 has set up version 15 of
   the client libraries in the same way.
   However, the upgrade to MySQL 5.1 would have removed the files
   brought by 5.0 and just installed the new version 16
   (specifically, the 5.1 shared RPM does this).

   If you use applications that were built against older versions of the
   MySQL client libraries (like Trixbox), you must not lose these
   older files.

   For this purpose, we provide shared-compat RPMs, they contain the
   current and all previous versions of the client libraries.
   See this contents listing:

prompt rpm -qlvp MySQL-shared-compat-5.1.34-0.glibc23.i386.rpm | cut 
-c1-11,37-
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so - 
 libmysqlclient.so.16.0.0
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so.10 - 
 libmysqlclient.so.10.0.0
 -rwxr-xr-x224606 Apr  5 23:56 /usr/lib/libmysqlclient.so.10.0.0
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so.12 - 
 libmysqlclient.so.12.0.0
 -rwxr-xr-x221492 Apr  5 23:56 /usr/lib/libmysqlclient.so.12.0.0
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so.14 - 
 libmysqlclient.so.14.0.0
 -rwxr-xr-x   1267848 Apr  5 23:56 /usr/lib/libmysqlclient.so.14.0.0
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so.15 - 
 libmysqlclient.so.15.0.0
 -rwxr-xr-x   1942232 Apr  5 23:56 /usr/lib/libmysqlclient.so.15.0.0
 lrwxrwxrwx24 Apr  5 23:56 /usr/lib/libmysqlclient.so.16 - 
 libmysqlclient.so.16.0.0
 -rwxr-xr-x   1989112 Apr  5 23:56 /usr/lib/libmysqlclient.so.16.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so - 
 libmysqlclient_r.so.16.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.10 - 
 libmysqlclient_r.so.10.0.0
 -rwxr-xr-x230560 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.10.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.12 - 
 libmysqlclient_r.so.12.0.0
 -rwxr-xr-x225752 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.12.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.14 - 
 libmysqlclient_r.so.14.0.0
 -rwxr-xr-x   1274796 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.14.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.15 - 
 libmysqlclient_r.so.15.0.0
 -rwxr-xr-x   1950652 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.15.0.0
 lrwxrwxrwx26 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.16 - 
 libmysqlclient_r.so.16.0.0
 -rwxr-xr-x   1999100 Apr  5 23:56 /usr/lib/libmysqlclient_r.so.16.0.0
 lrwxrwxrwx21 Apr  5 23:56 /usr/lib/libndbclient.so - 
 libndbclient.so.2.0.0
 lrwxrwxrwx21 Apr  5 23:56 /usr/lib/libndbclient.so.2 - 
 libndbclient.so.2.0.0
 -rwxr-xr-x759988 Apr  5 23:56 /usr/lib/libndbclient.so.2.0.0

   For comparison, this is the 5.1.34 shared RPM:

 rpm -qlvp MySQL-shared-5.1.34-0.glibc23.i386.rpm | cut -c1-11,37-
 lrwxrwxrwx24 Mar 31 22:47 /usr/lib/libmysqlclient.so - 
 libmysqlclient.so.16.0.0
 lrwxrwxrwx24 Mar 31 22:47 /usr/lib/libmysqlclient.so.16 - 
 libmysqlclient.so.16.0.0
 -rwxr-xr-x   1998728 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0
 lrwxrwxrwx26 Mar 31 22:47 /usr/lib/libmysqlclient_r.so - 
 

Re: Problems After MySql 5.1.34

[Joerg Bruehe]

Hi Gary, all,


Gary Smith wrote:
 Johnny, 
 
 Welcome to the hell that is php + apache + mysql.  If you upgrade your MySql 
 (especially major versions 5.0 = 5.1) you will also need to recompile php 
 against the new MySql client libs.  We've had very limited success trying to 
 get it to work otherwise.

Which other approach(es) did you try?

 
 This is why you are receiving the error through PHP.
 
 Of course, I could be wrong, in which case I know people will probably jump 
 me for it.  If this is the case, please do as I would like to be wrong here 
 as it would make my compiling life easier every time I update MySql on all of 
 my boxes.

Please see my other mail:

- Using tar.gz, the old version of the client libs should not be
  touched when you upgrade MySQL.

- Using RPMs, you need to install shared-compat (and not shared) if
  you are using application binaries built against older version(s).

If you found any problems with this, please tell us!


We are considering to change the contents of shared-compat RPMs, so
that they would not replace shared any more but just complement it
(bring just the old libs, not the current one).
The advantage would be that you then can install (or uninstall) them
without affecting your clients built against the current version,
especially those coming from MySQL directly.
However, this would mean a change in usage pattern for existing users,
and we are not yet sufficiently sure that our users would welcome it.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


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

RE: Problems After MySql 5.1.34

[Mark]

Gary wrote:

 Welcome to the hell that is php + apache + mysql. If you upgrade your
 MySql (especially major versions 5.0 = 5.1) you will also need to
 recompile php against the new MySql client libs. We've had very
 limited success trying to get it to work otherwise.

Well, you don't actually have to recompile PHP entirely, of course: just
its mysql.so extension.

@TS: Other than that, you basically need to recompile *everything* (or its
mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
so you'd need to build DBD:mysql as well (same for Python, etc).

Walter wrote:

 Any (major)upgrade of mysql client requires the dependent subsystem to
 upgrade also. Anything else would be careless since you do not know if the
 interface has changed.

Actually, you *do* know: that's what the changelog is for. :) When C
header changes are made, an upgrade is in order. If not, when upgrading
between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
won't need to recompile all system-wide MySQL client extensions. I've done
this many times, without issue: you just need to be absolutely sure no
header changes were made (when in doubt, recompile).

I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
working just fine, but I spent several hours recompiling MySL client
stuff; without doing so, your apps will likely behave erratically, or just
segfault altogether.

This isn't a MySL hell exclusively, btw. You'll get the same issue
upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
beast.

- Mark


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

RE: Problems After MySql 5.1.34

[Mark]

Depends on your OS, I guess. On FreeBSD you can just go to the

'php5-extensions' port, run a 'make config', and deselect everything but

the MySQL extension. Then it will only build mysql.so for you. Very easy.

:)

 

- Mark

 

 

From: Martin Gainty [mailto:mgai...@hotmail.com] 
Sent: donderdag 7 mei 2009 15:15
To: ad...@asarian-host.net; mysql@lists.mysql.com
Subject: RE: Problems After MySql 5.1.34

 

Gary

this isnt mysql's fault that php has such abysmal build tools..try
requesting a web developer to simply download php and run the proprietary
binary tools
after a week of sleepless nights and endless searching for the right
dll/so/lib and endless searching for the right tools i guarantee they will
call no joy 
and implement the webapp in Java!

Martin 
__ 
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de
déni et de confidentialité

This message is confidential. If you should not be the intended receiver,
then we ask politely to report. Each unauthorized forwarding or
manufacturing of a copy is inadmissible. This message serves only for the
exchange of information and has no legal binding effect. Due to the easy
manipulation of emails we cannot take responsibility over the the contents.


Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.


Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.






 From: ad...@asarian-host.net
 Subject: RE: Problems After MySql 5.1.34
 Date: Thu, 7 May 2009 12:57:42 +
 To: mysql@lists.mysql.com
 
 Gary wrote:
 
  Welcome to the hell that is php + apache + mysql. If you upgrade your
  MySql (especially major versions 5.0 = 5.1) you will also need to
  recompile php against the new MySql client libs. We've had very
  limited success trying to get it to work otherwise.
 
 Well, you don't actually have to recompile PHP entirely, of course: just
 its mysql..so extension.
 
 @TS: Other than that, you basically need to recompile *everything* (or its
 mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
 so you'd need to build DBD:mysql as well (same for Python, etc).
 
 Walter wrote:
 
  Any (major)upgrade of mysql client requires the dependent subsystem to
  upgrade also. Anything else would be careless since you do not know if
the
  interface has changed.
 
 Actually, you *do* know: that's what the changelog is for. :) When C
 header changes are made, an upgrade is in order. If not, when upgrading
 between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
 won't need to recompile all system-wide MySQL client extensions. I've done
 this many times, without issue: you just need to be absolutely sure no
 header changes were made (when in doubt, recompile)..
 
 I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
 working just fine, but I spent several hours recompiling MySL client
 stuff; without doing so, your apps will likely behave erratically, or just
 segfault altogether.
 
 This isn't a MySL hell exclusively, btw. You'll get the same issue
 upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
 beast.
 
 - Mark
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

  _  

Windows Live™: Keep your life in sync. Check it out.
http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009 

RE: Problems After MySql 5.1.34

[Gary Smith]

Typically we see the problems with RH/Cent when you upgrade through those 
channels and then do an install of the 5.1.x series on top of that.  I tried it 
about 9 months ago, so the details are fuzzy.  I do know that in attempts to 
recover we extracted the RPM contents and tried to copy them manually to 
satisfy the dependency but in the end that didn't work for us for some reason.

We ended up just creating a new RPM for 5.1.x and then recompiling the 
dependent apps against that and storing all of them in our own repo for yum 
update.


From: joerg.bru...@sun.com [joerg.bru...@sun.com]
Sent: Thursday, May 07, 2009 2:40 AM
To: Gary Smith
Cc: mysql@lists.mysql.com
Subject: Re: Problems After MySql 5.1.34

Hi Gary, all,


Gary Smith wrote:
 Johnny,

 Welcome to the hell that is php + apache + mysql.  If you upgrade your MySql 
 (especially major versions 5.0 = 5.1) you will also need to recompile php 
 against the new MySql client libs.  We've had very limited success trying to 
 get it to work otherwise.

Which other approach(es) did you try?


 This is why you are receiving the error through PHP.

 Of course, I could be wrong, in which case I know people will probably jump 
 me for it.  If this is the case, please do as I would like to be wrong here 
 as it would make my compiling life easier every time I update MySql on all of 
 my boxes.

Please see my other mail:

- Using tar.gz, the old version of the client libs should not be
  touched when you upgrade MySQL.

- Using RPMs, you need to install shared-compat (and not shared) if
  you are using application binaries built against older version(s).

If you found any problems with this, please tell us!


We are considering to change the contents of shared-compat RPMs, so
that they would not replace shared any more but just complement it
(bring just the old libs, not the current one).
The advantage would be that you then can install (or uninstall) them
without affecting your clients built against the current version,
especially those coming from MySQL directly.
However, this would mean a change in usage pattern for existing users,
and we are not yet sufficiently sure that our users would welcome it.


Regards,
Jörg

--
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


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

RE: Problems After MySql 5.1.34

[Gary Smith]

You are right.  I misspoke regarding mysql - php - apache hell.  It happens 
anytime an interface changes.  


From: Mark [ad...@asarian-host.net]
Sent: Thursday, May 07, 2009 5:57 AM
To: mysql@lists.mysql.com
Subject: RE: Problems After MySql 5.1.34

Gary wrote:

 Welcome to the hell that is php + apache + mysql. If you upgrade your
 MySql (especially major versions 5.0 = 5.1) you will also need to
 recompile php against the new MySql client libs. We've had very
 limited success trying to get it to work otherwise.

Well, you don't actually have to recompile PHP entirely, of course: just
its mysql.so extension.

@TS: Other than that, you basically need to recompile *everything* (or its
mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
so you'd need to build DBD:mysql as well (same for Python, etc).

Walter wrote:

 Any (major)upgrade of mysql client requires the dependent subsystem to
 upgrade also. Anything else would be careless since you do not know if the
 interface has changed.

Actually, you *do* know: that's what the changelog is for. :) When C
header changes are made, an upgrade is in order. If not, when upgrading
between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
won't need to recompile all system-wide MySQL client extensions. I've done
this many times, without issue: you just need to be absolutely sure no
header changes were made (when in doubt, recompile).

I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
working just fine, but I spent several hours recompiling MySL client
stuff; without doing so, your apps will likely behave erratically, or just
segfault altogether.

This isn't a MySL hell exclusively, btw. You'll get the same issue
upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
beast.

- Mark


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

Problems After MySql 5.1.34

[Johnny Stork]

I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 
5.64. Access to the db seesm fine from the shell, phpmyadmin or even the 
Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a 
pear update produces the errors below. I created a couple of sl but 
this did not seem to fix the problem. Below is the error and contents of 
/usr/lib





r...@asterisk:~# pear update
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: version 
`libmysqlclient_15' not found (required by 
/usr/lib/php/modules/mysql.so) in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: 
version `libmysqlclient_15' not found (required by 
/usr/lib/php/modules/mysqli.so) in Unknown on line 0

Segmentation fault



r...@asterisk:~# ls -la /usr/lib/libmy*

lrwxrwxrwx 1 root root  26 May  6 09:52 /usr/lib/libmysqlclient_r.so 
- libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root  28 May  6 11:26 
/usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so
lrwxrwxrwx 1 root root  28 May  6 11:18 
/usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so
lrwxrwxrwx 1 root root  26 May  6 09:52 
/usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0
-rwxr-xr-x 1 root root 2052884 Mar 31 22:48 
/usr/lib/libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root  24 May  6 09:52 /usr/lib/libmysqlclient.so 
- libmysqlclient.so.16.0.0
lrwxrwxrwx 1 root root  26 May  6 11:14 
/usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so
lrwxrwxrwx 1 root root  24 May  6 09:52 
/usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0
-rwxr-xr-x 1 root root 2044464 Mar 31 22:48 
/usr/lib/libmysqlclient.so.16.0.0



--
_
Johnny Stork

Open Enterprise Solutions
Empowering Business With Open Solutions
http://www.openenterprise.ca

Mountain Hosting
Secure Hosting Solutions for Business
http://www.mountainhosting.ca

Re: Problems After MySql 5.1.34

[Johnny Stork]

Typo, moved from 5.0.67 to 5.1.34

Johnny Stork wrote:
I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 
5.64. Access to the db seesm fine from the shell, phpmyadmin or even 
the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or 
running a pear update produces the errors below. I created a couple 
of sl but this did not seem to fix the problem. Below is the error and 
contents of /usr/lib





r...@asterisk:~# pear update
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: 
version `libmysqlclient_15' not found (required by 
/usr/lib/php/modules/mysql.so) in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 
'/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: 
version `libmysqlclient_15' not found (required by 
/usr/lib/php/modules/mysqli.so) in Unknown on line 0

Segmentation fault



r...@asterisk:~# ls -la /usr/lib/libmy*

lrwxrwxrwx 1 root root  26 May  6 09:52 
/usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root  28 May  6 11:26 
/usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so
lrwxrwxrwx 1 root root  28 May  6 11:18 
/usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so
lrwxrwxrwx 1 root root  26 May  6 09:52 
/usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0
-rwxr-xr-x 1 root root 2052884 Mar 31 22:48 
/usr/lib/libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root  24 May  6 09:52 /usr/lib/libmysqlclient.so 
- libmysqlclient.so.16.0.0
lrwxrwxrwx 1 root root  26 May  6 11:14 
/usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so
lrwxrwxrwx 1 root root  24 May  6 09:52 
/usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0
-rwxr-xr-x 1 root root 2044464 Mar 31 22:48 
/usr/lib/libmysqlclient.so.16.0.0





--
_
Johnny Stork

Open Enterprise Solutions
Empowering Business With Open Solutions
http://www.openenterprise.ca

Mountain Hosting
Secure Hosting Solutions for Business
http://www.mountainhosting.ca


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