Re: need help from the list admin

[Lentes, Bernd]

- Am 1. Apr 2016 um 21:56 schrieb shawn l.green shawn.l.gr...@oracle.com:

> Correct. MyISAM is not a transactional storage engine. It has no concept
> of COMMIT or ROLLBACK. Changes to it are controlled by a full table lock
> and as soon as the change is complete, the table is unlocked and is
> immediately visible to every other session.
> 
> What the replication system has done is to extend the length of that
> lock until the transaction completes to avoid situations where changes
> appear "out of sequence" to what is recorded in the Binary Log.

>> So when transaction is rollbacked, the inserted data in the MyISAM table 
>> remains
>> ?
> 
> Correct. All of the changes that could be undone were undone. MyISAM
> changes can't be undone so they stayed in place.
> 

Aah. OK.

> Try this as an analogy.
> 
> MyISAM tables are like writing directly with ink on paper. If you can
> complete the write, you have changed the row.
> 
> InnoDB tables are like whiteboards. You can undo your pending changes
> before someone else (one of the background threads of the InnoDB engine)
> makes the changes permanent.
> 
> The Binary Log is like a recipe used to rebuild your data in case it
> goes boom. If you start from a backup then repeat the sequence of
> actions as they were recorded in the Binary Log since that backup was
> created, you should wind up with exactly the same data you had before
> the problem.  If there is a problem with that sequence (actions are out
> of order) then rebuilding that data could be a problem.
> 
> Sequence makes a big difference even in less esoteric settings. Try this...
> 
> Start with your phone lying flat on your desk (screen up) pointing
> directly away from you. Roll it 45 degrees to the right. Now lift it
> vertically towards you 90 degrees (maintain the roll).  The phone is now
> pointing straight up but the screen is turned away from you.
> 
> Then try those same actions in reverse order. lift first, then roll it
> to the right. In this case the screen is pointing in your general
> direction but the whole thing is leaning off to one side.

I don't understand the example completely, but i understand
what you want to say: Changing the order of statements
may lead to a different result.

Bernd
 

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: need help from the list admin

[Lentes, Bernd]

- Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com:

>> Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
>> with Statement-Based-Logging or with both ?
>>
>>
> 
> Both.
> 
>
>>
>> I don't understand the example:
>> Does "begin transaction" and "COMMIT" have any influence on the insert ?
>>  From what i understand a myisam table does not support transactions,
>> so it should not care about "begin transaction" and "commit".
>> So the insert should be done immediately. The select on the InnoDB also
>> should not wait, because it's applied without "LOCK IN SHARE MODE".
>> So x lines are added immediately. This is done on the master, written in the 
>> log
>> and then replicated to the slave, which also adds x lines.
>> Then connection 2 deletes 8 rows, one is from the previous insert.
>> First on the master and then on the slave.
>> I assume that the connections are established in the order they appear here
>> (connection 2 is established after the insert in connection 1).
>> So on both 8 rows are deleted.
>>
>>
> 
> 
> You said, "This is done on the master, written in the log and then
> replicated to the slave, "
> 
> The INSERT would not appear in the Binary log until after session 1
> commits. Even if session 1 does a rollback, you would still see the
> entire transaction including the ROLLBACK. We have to do it that way to
> preserve the transaction isolation of the InnoDB data.
> 
> Yes, you read the shorthand correctly and in the correct temporal sequence.
>   session1 did two commands.
>   session2 issued one command.
>   session1 did a commit.
> 
> It does not matter of the sessions were created in that order or not.
> Only the sequence in which the commands are executed matters.
> 
> 
>>
>>
>> Independent from the binlog_format ?
>> Does commit means "write now to the binlog" ?
>>
> 
> Effectively, it does (for InnoDB-based transactions). InnoDB first
> writes the entire transaction to the Binary Log (it was sitting in the
> Binlog cache up until this point) then it pumps the necessary data into
> the REDO log (for disaster recovery). At that point the transaction is
> considered "committed".  In the case of a rollback, there is nothing to
> log in either location, no permanent changes were made to the data.
> However if the transaction that rolled back contained statements that
> changed MyISAM tables, then the entire transaction (all of the work it
> did) needs to be written into the Binary Log and REDO log just to have
> the very last command be "ROLLBACK".   What that will do is create the
> same sequence of data changes on the slave that happened on the master.
> 
> 
In case of a rollback: is the INSERT in the MyISAM table also rollbacked ? 
I think no.


Bernd
 

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: need help from the list admin

[Reindl Harald]

Am 01.04.2016 um 21:09 schrieb Lentes, Bernd:

- Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com:

You said, "This is done on the master, written in the log and then
replicated to the slave, "

The INSERT would not appear in the Binary log until after session 1
commits.


So the INSERT take care about the transaction (begin transaction ... COMMIT)
although it's a MyISAM table ?
Because i read MyISAM does not care about it:
http://stackoverflow.com/questions/8036005/myisam-engine-transaction-support


and hence you should not mix innodb and non-transactional tables, a 
MyISAM table is not and will never be part of a transaction




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Lentes, Bernd]

Sorry for pm !

- Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com:

>>> You would be better served by first converting your MyISAM tables to
>>> InnoDB to stop mixing storage engine behaviors (transactional and
>>> non-transactional) within the scope of a single transaction. But if you
>>> cannot convert them, using MIXED will be a good compromise.
>>
>> Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
>> with Statement-Based-Logging or with both ?
> Both.

Aah ! In the beginning i thought it's just a problem for RBL.

>>> Look at this sequence and think what would happen without that "stronger
>>> locking" you mentioned earlier.
>>>
>>> (connection 1)
>>>begin transaction
>>>INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
>>>   (connection 2)
>>>   DELETE myisam_table WHERE ...  (this removes one of the rows that
>>>   connection 1 just added)
>> (end of connection 2)
>>> (connection 1)
>>>COMMIT


>> I don't understand the example:
>> Does "begin transaction" and "COMMIT" have any influence on the insert ?
>>  From what i understand a myisam table does not support transactions,
>> so it should not care about "begin transaction" and "commit".
>> So the insert should be done immediately. The select on the InnoDB also
>> should not wait, because it's applied without "LOCK IN SHARE MODE".
>> So x lines are added immediately. This is done on the master, written in the 
>> log
>> and then replicated to the slave, which also adds x lines.
>> Then connection 2 deletes 8 rows, one is from the previous insert.
>> First on the master and then on the slave.
>> I assume that the connections are established in the order they appear here
>> (connection 2 is established after the insert in connection 1).
>> So on both 8 rows are deleted.
>>
>>
> 
> 
> You said, "This is done on the master, written in the log and then
> replicated to the slave, "
> 
> The INSERT would not appear in the Binary log until after session 1
> commits. 

So the INSERT take care about the transaction (begin transaction ... COMMIT) 
although it's a MyISAM table ?
Because i read MyISAM does not care about it:
http://stackoverflow.com/questions/8036005/myisam-engine-transaction-support


>> Does commit means "write now to the binlog" ?
>>
> 
> Effectively, it does (for InnoDB-based transactions). InnoDB first
> writes the entire transaction to the Binary Log (it was sitting in the
> Binlog cache up until this point) then it pumps the necessary data into
> the REDO log (for disaster recovery). 

And when in that temporal sequence is the data written to the tablespace ?

> At that point the transaction is
> considered "committed".  In the case of a rollback, there is nothing to
> log in either location, no permanent changes were made to the data.
> However if the transaction that rolled back contained statements that
> changed MyISAM tables, then the entire transaction (all of the work it
> did) needs to be written into the Binary Log and REDO log just to have
> the very last command be "ROLLBACK".   What that will do is create the
> same sequence of data changes on the slave that happened on the master.

So when transaction is rollbacked, the inserted data in the MyISAM table 
remains ?

Thaks again.

Bernd

P.S. i tried several times to rename the subject into something like
"Replication - was "need help from the list admin"", but this mail
is always bounced back because it is recognized as spam !?!
I just renamed the subject !


Bernd
 

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: need help from the list admin

[Lentes, Bernd]

- Am 1. Apr 2016 um 17:52 schrieb shawn l.green shawn.l.gr...@oracle.com:

>> What is true ? when the transaction started or when the first read is 
>> performed ?
 
> Until you need to establish a snapshot of the data, then you don't need
> a snapshot position.
> 
> The transaction physically begins (rows begin to be protected against
> changes by other transactions) with the first read.

OK. But only when the first read is in a transaction and isolation level
is REPEATABLE READ the query is "snapshotted" for further queries, so
seeing the same result ?
When several SELECT are issued not inside a transaction they always get
the current data ?
 
> Consider the alternative: If we started protecting data with the START
> TRANSACTION command we would need to protect every row in every table in
> every database.  That is simply not efficient.

YES.

> We protect the pages that contain the rows that are physically required
> by the individual transaction. This is a much smaller locking footprint
> and is much easier to manage.

Bernd
 

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: need help from the list admin

[shawn l.green]

On 4/1/2016 10:08 AM, Lentes, Bernd wrote:



- On Apr 1, 2016, at 3:12 PM, Bernd Lentes 
bernd.len...@helmholtz-muenchen.de wrote:

Btw:
i read about isolation levels. REPEATABLE READ is the default for InnoDB.
http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_repeatable_read says:

"...so that all queries within a transaction see data from the same snapshot,
that is, the data as it was at the time the transaction started.".

http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read says:

"With the repeatable read isolation level, the snapshot is based on the time
when the first read operation is performed".

What is true ? when the transaction started or when the first read is performed 
?



Until you need to establish a snapshot of the data, then you don't need 
a snapshot position.


The transaction physically begins (rows begin to be protected against 
changes by other transactions) with the first read.


Consider the alternative: If we started protecting data with the START 
TRANSACTION command we would need to protect every row in every table in 
every database.  That is simply not efficient.


We protect the pages that contain the rows that are physically required 
by the individual transaction. This is a much smaller locking footprint 
and is much easier to manage.





Bernd




--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: need help from the list admin

[shawn l.green]

On 4/1/2016 9:12 AM, Lentes, Bernd wrote:

- On Mar 25, 2016, at 9:54 PM, shawn l.green shawn.l.gr...@oracle.com wrote:



"Unsafe" in that sense replies to the fact that certain commands can
have a different effect when processed from the Binary Log than they did
when they were executed originally on the system that wrote the Binary
Log. This would be true for both a point-in-time recovery situation and
for replication. The topic of unsafe commands is covered rather well on
these pages:
http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html

This is particularly true for commands that may cross transactional
boundaries and change non-transactional tables.  The effect of those
commands are apparent immediately to any other user of the server. They
do not rely on the original transaction to complete with a COMMIT. The
workaround we employed was to keep the non-transactional table locked
(to keep others from altering it) until the transaction completes
(COMMIT or ROLLBACK). That way we do our best to make all changes
"permanent" at the same time.



Hi,

oh my god. The more i read the more i'm getting confused. I totally underrated 
replication.
But i will not give up ;-) And i appreciate your help, Shawn.
What do you mean with the workaround ? Does MySQL this automatically or has it 
be done
in the app code ?



It's inside the server. You don't need to do anything as a user.



You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.


Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
with Statement-Based-Logging or with both ?




Both.



Look at this sequence and think what would happen without that "stronger
locking" you mentioned earlier.

(connection 1)
   begin transaction
   INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
  (connection 2)
  DELETE myisam_table WHERE ...  (this removes one of the rows that
  connection 1 just added)

(end of connection 2)

(connection 1)
   COMMIT

When the slave sees this sequence, it will get the command from
Connection2 first (it completed first so it winds up in the Binary Log).
It removed 8 rows on the master but it would only see 7 on the slave.
Why? The 8th row has not been added to the MyISAM table on the slave
because the transaction that does it hasn't been recorded to the Binary
Log yet.

That's why there is stronger locking comes into play. If we had not
blocked connection 2 until connection 1 completed things would be out of
temporally speaking. It's still possible for things to happen out of
sequence on the slave when mixing transactional and non-transactional
tables in the same transaction.



I don't understand the example:
Does "begin transaction" and "COMMIT" have any influence on the insert ?
 From what i understand a myisam table does not support transactions,
so it should not care about "begin transaction" and "commit".
So the insert should be done immediately. The select on the InnoDB also
should not wait, because it's applied without "LOCK IN SHARE MODE".
So x lines are added immediately. This is done on the master, written in the log
and then replicated to the slave, which also adds x lines.
Then connection 2 deletes 8 rows, one is from the previous insert.
First on the master and then on the slave.
I assume that the connections are established in the order they appear here
(connection 2 is established after the insert in connection 1).
So on both 8 rows are deleted.





You said, "This is done on the master, written in the log and then 
replicated to the slave, "


The INSERT would not appear in the Binary log until after session 1 
commits. Even if session 1 does a rollback, you would still see the 
entire transaction including the ROLLBACK. We have to do it that way to 
preserve the transaction isolation of the InnoDB data.


Yes, you read the shorthand correctly and in the correct temporal sequence.
  session1 did two commands.
  session2 issued one command.
  session1 did a commit.

It does not matter of the sessions were created in that order or not. 
Only the sequence in which the commands are executed matters.






This takes us to the next point you have...

The doc says: "Due to concurrency issues, a slave can become
inconsistent when a transaction contains updates to both transactional
and nontransactional tables. MySQL tries to preserve causality among
these statements by writing nontransactional statements to the
transaction cache, which is flushed upon commit. However, problems arise
when modifications done to nontransactional tables on behalf of a
transaction become immediately visible to other connections because
these changes may not be written immediately into the 

Re: need help from the list admin

[Lentes, Bernd]

- On Apr 1, 2016, at 3:12 PM, Bernd Lentes 
bernd.len...@helmholtz-muenchen.de wrote:

Btw:
i read about isolation levels. REPEATABLE READ is the default for InnoDB.
http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_repeatable_read says:

"...so that all queries within a transaction see data from the same snapshot, 
that is, the data as it was at the time the transaction started.".

http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read says:

"With the repeatable read isolation level, the snapshot is based on the time 
when the first read operation is performed".

What is true ? when the transaction started or when the first read is performed 
?


Bernd

 

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: need help from the list admin

[Lentes, Bernd]

- On Mar 25, 2016, at 9:54 PM, shawn l.green shawn.l.gr...@oracle.com wrote:


> "Unsafe" in that sense replies to the fact that certain commands can
> have a different effect when processed from the Binary Log than they did
> when they were executed originally on the system that wrote the Binary
> Log. This would be true for both a point-in-time recovery situation and
> for replication. The topic of unsafe commands is covered rather well on
> these pages:
> http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
> http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html
> 
> This is particularly true for commands that may cross transactional
> boundaries and change non-transactional tables.  The effect of those
> commands are apparent immediately to any other user of the server. They
> do not rely on the original transaction to complete with a COMMIT. The
> workaround we employed was to keep the non-transactional table locked
> (to keep others from altering it) until the transaction completes
> (COMMIT or ROLLBACK). That way we do our best to make all changes
> "permanent" at the same time.
> 

Hi,

oh my god. The more i read the more i'm getting confused. I totally underrated 
replication.
But i will not give up ;-) And i appreciate your help, Shawn.
What do you mean with the workaround ? Does MySQL this automatically or has it 
be done 
in the app code ?
 
> You would be better served by first converting your MyISAM tables to
> InnoDB to stop mixing storage engine behaviors (transactional and
> non-transactional) within the scope of a single transaction. But if you
> cannot convert them, using MIXED will be a good compromise.

Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or
with Statement-Based-Logging or with both ?


> Look at this sequence and think what would happen without that "stronger
> locking" you mentioned earlier.
> 
> (connection 1)
>   begin transaction
>   INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
>  (connection 2)
>  DELETE myisam_table WHERE ...  (this removes one of the rows that
>  connection 1 just added)
   (end of connection 2)
> (connection 1)
>   COMMIT
> 
> When the slave sees this sequence, it will get the command from
> Connection2 first (it completed first so it winds up in the Binary Log).
> It removed 8 rows on the master but it would only see 7 on the slave.
> Why? The 8th row has not been added to the MyISAM table on the slave
> because the transaction that does it hasn't been recorded to the Binary
> Log yet.
> 
> That's why there is stronger locking comes into play. If we had not
> blocked connection 2 until connection 1 completed things would be out of
> temporally speaking. It's still possible for things to happen out of
> sequence on the slave when mixing transactional and non-transactional
> tables in the same transaction.
> 

I don't understand the example:
Does "begin transaction" and "COMMIT" have any influence on the insert ?
>From what i understand a myisam table does not support transactions,
so it should not care about "begin transaction" and "commit".
So the insert should be done immediately. The select on the InnoDB also
should not wait, because it's applied without "LOCK IN SHARE MODE".
So x lines are added immediately. This is done on the master, written in the log
and then replicated to the slave, which also adds x lines.
Then connection 2 deletes 8 rows, one is from the previous insert.
First on the master and then on the slave.
I assume that the connections are established in the order they appear here
(connection 2 is established after the insert in connection 1).
So on both 8 rows are deleted.



> This takes us to the next point you have...
>> The doc says: "Due to concurrency issues, a slave can become
>> inconsistent when a transaction contains updates to both transactional
>> and nontransactional tables. MySQL tries to preserve causality among
>> these statements by writing nontransactional statements to the
>> transaction cache, which is flushed upon commit. However, problems arise
>> when modifications done to nontransactional tables on behalf of a
>> transaction become immediately visible to other connections because
>> these changes may not be written immediately into the binary log.
>> Beginning with MySQL 5.5.2, the binlog_direct_non_transactional_updates
>> variable offers one possible workaround to this issue. By default, this
>> variable is disabled. Enabling binlog_direct_non_transactional_updates
>> causes updates to nontransactional tables to be written directly to the
>> binary log, rather than to the transaction cache.
>> binlog_direct_non_transactional_updates works only for statements that
>> are replicated using the statement-based binary logging format; that is,
>> it works only when the value of binlog_format is STATEMENT, or when
>> binlog_format is MIXED and a given statement is being replicated using
>> the statement-based 

Re: need help from the list admin

[shawn l.green]

On 3/30/2016 1:26 PM, Lentes, Bernd wrote:

- On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote:



So i should use the default (autocommit=1)?


no, you should what is appropriate for your application

if you don't care about inserts/updates triggered by let say a
webrequest are half written due a crash or restart use autocommit


Autocommit means that every statement is committed implicitly. Right ?
Commit works only in conjunction with InnoDB tables and transaction. That's 
what i understand.
I thought when i make e.g. an insert into a InnoDB table, and that insert is 
not done completely (due to a crash, restart, what ever)
it is rolled back automatically after the restart. Is that wrong ?



it depends:  If the transaction made it into the Binary Log (if it is 
enabled) and the REDO log as "committed", then InnoDB will finish the 
commit (put the actual data in its proper place in the data files) after 
recovery. If not, it will rollback and your data remains as it was.


http://dev.mysql.com/doc/refman/5.6/en/innodb-recovery.html



if you care that all or nothing is written use transactions
if you care that way don't mix non-transactional tables with innodb


I'm planning to convert the MyISAM tables to InnoDB.



That will solve many of your data consistency problems (particularly 
those related to how things are recorded in the Binary Log), presuming 
you surround changes that involve multiple commands with transaction 
control commands.


If your sets of data changes only need one command to complete, then the 
overhead of issuing explicit START TRANSACTION and COMMIT commands is 
just going to create work you don't need for your workflow. If you need 
more than one command to make a complete and consistent update to your 
data, then use a transaction. If not, operating in autocommit mode is 
ideal.




Bernd



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: need help from the list admin

[Reindl Harald]

Am 30.03.2016 um 19:26 schrieb Lentes, Bernd:

- On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote:



So i should use the default (autocommit=1)?


no, you should what is appropriate for your application

if you don't care about inserts/updates triggered by let say a
webrequest are half written due a crash or restart use autocommit


Autocommit means that every statement is committed implicitly. Right ?
Commit works only in conjunction with InnoDB tables and transaction. That's 
what i understand.
I thought when i make e.g. an insert into a InnoDB table, and that insert is 
not done completely (due to a crash, restart, what ever)
it is rolled back automatically after the restart. Is that wrong ?


transactions are not about single queries, transactions are all about 
multiple queries when you want them all or nothing written


please do some homework and read https://en.wikipedia.org/wiki/ACID 
which is basic knowledge about databases


the crash safety of innodb has nothing to do with commits



signature.asc
Description: OpenPGP digital signature

RE: need help from the list admin

[Lentes, Bernd]

- On Mar 30, 2016, at 7:04 PM, Reindl Harald h.rei...@thelounge.net wrote:


>> So i should use the default (autocommit=1)?
> 
> no, you should what is appropriate for your application
> 
> if you don't care about inserts/updates triggered by let say a
> webrequest are half written due a crash or restart use autocommit

Autocommit means that every statement is committed implicitly. Right ? 
Commit works only in conjunction with InnoDB tables and transaction. That's 
what i understand.
I thought when i make e.g. an insert into a InnoDB table, and that insert is 
not done completely (due to a crash, restart, what ever)
it is rolled back automatically after the restart. Is that wrong ?

> 
> if you care that all or nothing is written use transactions
> if you care that way don't mix non-transactional tables with innodb

I'm planning to convert the MyISAM tables to InnoDB.


Bernd
 

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: need help from the list admin

[Reindl Harald]

Am 30.03.2016 um 18:56 schrieb Lentes, Bernd:

- On Mar 28, 2016, at 9:53 PM, shawn l.green shawn.l.gr...@oracle.com wrote:

I read that the converting is not difficult. But has the code of our webapp to
be changed ? It's written in php and perl.
What i understand is that inserts/updates/deletions in InnoDB tables have to be
commited. Yes ?


No. The server's default is to have --autocommit=1, which means that
there is an implicit commit at the end of every command. You do not need
to state explicitly "COMMIT" every time you want this to happen.

In fact, disabling autocommit has gotten many new users into trouble
because they did not understand the behavior they changed.


So i should use the default (autocommit=1)?


no, you should what is appropriate for your application

if you don't care about inserts/updates triggered by let say a 
webrequest are half written due a crash or restart use autocommit


if you care that all or nothing is written use transactions
if you care that way don't mix non-transactional tables with innodb



signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Lentes, Bernd]

- On Mar 28, 2016, at 9:53 PM, shawn l.green shawn.l.gr...@oracle.com wrote:

>>
>> I read that the converting is not difficult. But has the code of our webapp 
>> to
>> be changed ? It's written in php and perl.
>> What i understand is that inserts/updates/deletions in InnoDB tables have to 
>> be
>> commited. Yes ?
> 
> No. The server's default is to have --autocommit=1, which means that
> there is an implicit commit at the end of every command. You do not need
> to state explicitly "COMMIT" every time you want this to happen.
> 
> In fact, disabling autocommit has gotten many new users into trouble
> because they did not understand the behavior they changed.

So i should use the default (autocommit=1) ?

 
> Here is a reference from the 5.0 manual to illustrate that this behavior
> has been around for a long time:
> http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

 
Bernd
 

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: need help from the list admin

[Reindl Harald]

Am 28.03.2016 um 21:36 schrieb Lentes, Bernd:

- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote:


Am 27.03.2016 um 14:34 schrieb Lentes, Bernd:

You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.


Is this a big problem ? Something to take care of ? Currently we have a mix.
I will ask the girl who developed it why we have both kinds. I hope i can
convert


surely - when you have non-transactional tables involved in
updates/inserts you can go and forget using transactions at all since
interruption or rollback would not rollback already written changes in
MyISAM tables

transactions are all about consistency - impossible with a mix of InnoDB
and MyISAM tables


I read that the converting is not difficult. But has the code of our webapp to 
be changed ? It's written in php and perl.
What i understand is that inserts/updates/deletions in InnoDB tables have to be 
commited. Yes ?


first please stop place a space before "?" - it hurts :-)

NO - it only has to be commited if you START A TRANSACTION at the begin


This has to be done in the code ? Or can we use the system variable autocommit ?


if you are using automcommit you lose any purpose of having transactions


That means that everything is commited immediately ? Is this a good solution ?


if you don't care about consistency yes


What means "By default, client connections begin with autocommit set to 1" in 
the doc ?


that nobody is starting a transaction automatically because nobody can 
tell when it is finished automatically end hence you need to tell it the 
db server



That every client connection established via perl/php is started with 
autocommit=1 ?


surely


And when does the commit happen ? When the connection is closed ? Is that 
helpful ?


depends - maybe you should start to read what transactions in the 
database world are because than you can answer all of your questions 
above at your own




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[shawn l.green]

Hello Bernd,

On 3/28/2016 3:36 PM, Lentes, Bernd wrote:



- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote:


Am 27.03.2016 um 14:34 schrieb Lentes, Bernd:

You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.


Is this a big problem ? Something to take care of ? Currently we have a mix.
I will ask the girl who developed it why we have both kinds. I hope i can
convert


surely - when you have non-transactional tables involved in
updates/inserts you can go and forget using transactions at all since
interruption or rollback would not rollback already written changes in
MyISAM tables

transactions are all about consistency - impossible with a mix of InnoDB
and MyISAM tables


I read that the converting is not difficult. But has the code of our webapp to 
be changed ? It's written in php and perl.
What i understand is that inserts/updates/deletions in InnoDB tables have to be 
commited. Yes ?


No. The server's default is to have --autocommit=1, which means that 
there is an implicit commit at the end of every command. You do not need 
to state explicitly "COMMIT" every time you want this to happen.


In fact, disabling autocommit has gotten many new users into trouble 
because they did not understand the behavior they changed.



This has to be done in the code ? Or can we use the system variable autocommit ?


You should need to change nothing.


That means that everything is commited immediately ? Is this a good solution ?


It is going to behave better than the data you have now. The changes to 
the tables you will convert from MyISAM to InnoDB will not become 
visible to other sessions until after the COMMIT (implicit or explicit) 
completes. For finer-grained control over data visibility, you need to 
understand the broader topic of transaction isolation.



What means "By default, client connections begin with autocommit set to 1" in 
the doc ?


It means that every command is already running in its own private 
mini-transaction. To start a multi-statement transaction you do not need 
to disable autocommit, you simply need to use the START TRANSACTION 
command.


Here is a reference from the 5.0 manual to illustrate that this behavior 
has been around for a long time:

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html


That every client connection established via perl/php is started with 
autocommit=1 ?


It is as long as:

1) the global variable autocommit=1
2) the client does nothing to change its own session variable to 
autocommit=0



And when does the commit happen ? When the connection is closed ? Is that 
helpful ?



The commit happens at the end of each command. If you need to contain 
multiple commands within a single transaction, use START TRANSACTION and 
COMMIT.





Bernd




--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: need help from the list admin

[Lentes, Bernd]

- On Mar 27, 2016, at 2:49 PM, Reindl Harald h.rei...@thelounge.net wrote:

> Am 27.03.2016 um 14:34 schrieb Lentes, Bernd:
>>> You would be better served by first converting your MyISAM tables to
>>> InnoDB to stop mixing storage engine behaviors (transactional and
>>> non-transactional) within the scope of a single transaction. But if you
>>> cannot convert them, using MIXED will be a good compromise.
>>
>> Is this a big problem ? Something to take care of ? Currently we have a mix.
>> I will ask the girl who developed it why we have both kinds. I hope i can
>> convert
> 
> surely - when you have non-transactional tables involved in
> updates/inserts you can go and forget using transactions at all since
> interruption or rollback would not rollback already written changes in
> MyISAM tables
> 
> transactions are all about consistency - impossible with a mix of InnoDB
> and MyISAM tables

I read that the converting is not difficult. But has the code of our webapp to 
be changed ? It's written in php and perl.
What i understand is that inserts/updates/deletions in InnoDB tables have to be 
commited. Yes ?
This has to be done in the code ? Or can we use the system variable autocommit ?
That means that everything is commited immediately ? Is this a good solution ?
What means "By default, client connections begin with autocommit set to 1" in 
the doc ?
That every client connection established via perl/php is started with 
autocommit=1 ?
And when does the commit happen ? When the connection is closed ? Is that 
helpful ?

Bernd

 

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: need help from the list admin

[Reindl Harald]

Am 27.03.2016 um 14:34 schrieb Lentes, Bernd:

You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.


Is this a big problem ? Something to take care of ? Currently we have a mix.
I will ask the girl who developed it why we have both kinds. I hope i can 
convert


surely - when you have non-transactional tables involved in 
updates/inserts you can go and forget using transactions at all since 
interruption or rollback would not rollback already written changes in 
MyISAM tables


transactions are all about consistency - impossible with a mix of InnoDB 
and MyISAM tables




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Lentes, Bernd]

- Am 25. Mrz 2016 um 21:54 schrieb shawn l.green shawn.l.gr...@oracle.com:

> Hello Bernd,
> 
> Sorry for the delay, I wanted to make sure I had enough time to address
> all of your points.


>> He proposed to have two hosts, and on each is running a MySQL instance
>> as master AND slave. But it's not a "real multi master solution",
>> because pacemaker takes care that the IP for the web app just points to
>> one master. So i don't have the multi-master problems with concurrent
>> inserts (i believe).
> 
> This is wise advice. We (MySQL Support) often recommend exactly the same
> setup:  a master + one(or more) slave(s) using replication to keep the
> slaves in relative sync. I say "relative" because replication is
> asynchronous.
> 
> All writes are directed at the master. Clients that can tolerate the
> natural lag of the replication system can use any available slave for
> read-only queries.
> 

is semi-synchronous a good idea ? I think we just have several 100 inserts per 
day, so i believe the lag should not be a problem.

>> His idea is that host A is master for the slave on host B, and host B is
>> the master for the slave on host A. OK ?
>> Let's imagining that the IP to the web app points to host A, inserts are
>> done to the master on host A and replicated to the slave on host B. Now
>> host A has problems, pacemaker redirects the IP to host B, and
>> everything should be fine.
>> What do you think about this setup ? Where is the advantage to a
>> "classical Master/Slave Replication" ? How should i configure
>> log-slave-updates in this scenario ?
> 
> We have a page on that in the manual (with a diagram):
> http://dev.mysql.com/doc/refman/5.6/en/replication-solutions-switch.html
> 

I will read that.

> 
>> Let's imagine i have two hosts again: Host A is master, host B is slave.
>> Nothing else. No real or pseudo "Multi-Master". IP points to host A.
>> Host A has problems, pacemaker recognizes it, promotes B to master and
>> pivot the IP. Everything should be fine. Where is the disadvantage of
>> this setup compared to the "Multi-Master Replication" in the book ? The
>> OCF ressource agent for mysql should be able to handle the mysql stuff
>> and the RA for the IP pivots the IP.
>>
> 
> Remember to wait for the slave to catch up to the master it lost contact
> with. That way its data is as current as possible. Then redirect your
> clients to the new read-write node in your replication topology.
> 

What is if the slave is behind and the master is gone ? So he has neither 
possibility to be up-to-date nor to catch up.


>>
>> The doc says: "For tables using the MYISAM storage engine, a stronger
>> lock is required on the slave for INSERT statements when applying them
>> as row-based events to the binary log than when applying them as
>> statements. This means that concurrent inserts on MyISAM tables are not
>> supported when using row-based replication."
>> What does this exactly mean ? Concurrent inserts in MyISAM-tables are
>> not possible if using RBL ? Or unsafe in the meaning they create
>> inconsistencies ?
>>
> 
> "Unsafe" in that sense replies to the fact that certain commands can
> have a different effect when processed from the Binary Log than they did
> when they were executed originally on the system that wrote the Binary
> Log. This would be true for both a point-in-time recovery situation and
> for replication. The topic of unsafe commands is covered rather well on
> these pages:
> http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
> http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html

I will read that.
> 
> This is particularly true for commands that may cross transactional
> boundaries and change non-transactional tables.  The effect of those
> commands are apparent immediately to any other user of the server. They
> do not rely on the original transaction to complete with a COMMIT. The
> workaround we employed was to keep the non-transactional table locked
> (to keep others from altering it) until the transaction completes
> (COMMIT or ROLLBACK). That way we do our best to make all changes
> "permanent" at the same time.
> 
> 
>> "RBL (Row Based Logging) and synchronization of nontransactional tables.
>> When many rows are affected, the set of changes is split into several
>> events; when the statement commits, all of these events are written to
>> the binary log. When executing on the slave, a table lock is taken on
>> all tables involved, and then
>> the rows are applied in batch mode. (This may or may not be effective,
>> depending on the engine used for the slave抯 copy of the table.)"
>> What does that mean ? Effective ? Is it creating inconsistencies ? Or
>> just not effective in the sense of slow or inconvinient ?
>>
>> Or should i prefer MIXED for binlog_format ?
>>
> 
> You would be better served by first converting your MyISAM tables to
> InnoDB to stop mixing storage engine behaviors (transactional and
> non-transactional) within 

Re: need help from the list admin

[shawn l.green]

Hello Bernd,

Sorry for the delay, I wanted to make sure I had enough time to address 
all of your points.


On 3/22/2016 7:07 AM, william drescher wrote:

sent for Bernd, and to see if it works from another sender
--
  Lentes, Bernd wrote:
Hi,

i know that there is a list dedicated to replication, but when you have
a look in the archive it's nearly complete empty. Really not busy.
So i hope it's ok if i ask here.
we have a web app which runs a MySQL DB and dynamic webpages with perl
and apache httpd. Webpages serve reading and writing into the db. The db
is important for our own work flow, so i'd like to make it HA. I have
two HP servers and will use SLES 11 SP4 64bit as OS. MySQL is 5.5.47.
For HA i'd like to use pacemaker, which is available in SLES High
Availibility Extension. I have experience in linux, but i'm not a
database administrator nor developer. HA is important for us, we don't
have performance problems.
My first idea was to run the web app and the db in a virtual machine on
the host and in case of a failure of one host pacemaker would run the vm
on the other host. VM would be stored on a FC SAN. I stopped following
this idea. I have bought a book about HA: "..." from Oliver Liebel. It's
only available in german. But i can recommend it, it's very detailed and
well explained.
He proposed to have two hosts, and on each is running a MySQL instance
as master AND slave. But it's not a "real multi master solution",
because pacemaker takes care that the IP for the web app just points to
one master. So i don't have the multi-master problems with concurrent
inserts (i believe).


This is wise advice. We (MySQL Support) often recommend exactly the same 
setup:  a master + one(or more) slave(s) using replication to keep the 
slaves in relative sync. I say "relative" because replication is 
asynchronous.


All writes are directed at the master. Clients that can tolerate the 
natural lag of the replication system can use any available slave for 
read-only queries.



His idea is that host A is master for the slave on host B, and host B is
the master for the slave on host A. OK ?
Let's imagining that the IP to the web app points to host A, inserts are
done to the master on host A and replicated to the slave on host B. Now
host A has problems, pacemaker redirects the IP to host B, and
everything should be fine.
What do you think about this setup ? Where is the advantage to a
"classical Master/Slave Replication" ? How should i configure
log-slave-updates in this scenario ?


We have a page on that in the manual (with a diagram):
http://dev.mysql.com/doc/refman/5.6/en/replication-solutions-switch.html



Let's imagine i have two hosts again: Host A is master, host B is slave.
Nothing else. No real or pseudo "Multi-Master". IP points to host A.
Host A has problems, pacemaker recognizes it, promotes B to master and
pivot the IP. Everything should be fine. Where is the disadvantage of
this setup compared to the "Multi-Master Replication" in the book ? The
OCF ressource agent for mysql should be able to handle the mysql stuff
and the RA for the IP pivots the IP.



Remember to wait for the slave to catch up to the master it lost contact 
with. That way its data is as current as possible. Then redirect your 
clients to the new read-write node in your replication topology.




Now some dedicated questions to replication. I read a lot in the
official documentation, but some things are not clear to me.
In our db we have MyISAM and InnoDB tables.

 From what i read i'd prefer row based replication. The doc says is the
safest approach. But there seems to be still some problems:

The doc says: "For tables using the MYISAM storage engine, a stronger
lock is required on the slave for INSERT statements when applying them
as row-based events to the binary log than when applying them as
statements. This means that concurrent inserts on MyISAM tables are not
supported when using row-based replication."
What does this exactly mean ? Concurrent inserts in MyISAM-tables are
not possible if using RBL ? Or unsafe in the meaning they create
inconsistencies ?



"Unsafe" in that sense replies to the fact that certain commands can 
have a different effect when processed from the Binary Log than they did 
when they were executed originally on the system that wrote the Binary 
Log. This would be true for both a point-in-time recovery situation and 
for replication. The topic of unsafe commands is covered rather well on 
these pages:

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html

This is particularly true for commands that may cross transactional 
boundaries and change non-transactional tables.  The effect of those 
commands are apparent immediately to any other user of the server. They 
do not rely on the original transaction to complete with a COMMIT. The 
workaround we employed was to keep the 

Re: need help from the list admin

[Lentes, Bernd]

- On Mar 23, 2016, at 11:11 AM, william drescher will...@techservsys.com 
wrote:

>>
>> Hi William,
>>
>> thanks for the try. Good idea !
>> Did you change anything ?
>>
>>
>> Bernd
> 
> Yes, in the original document there were some characters that
> were put on the screen as asian pictograph characters.  I
> replaced them with periods:
> I have bought a book
> about HA: "..." from Oliver Liebel
> 
> And I found the same characters in your sig and removed them.
> 
> bill
> 
> 
> 


Hi Bill,

thanks. I will try now to ask again.


Bernd
 

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: need help from the list admin

[william drescher]

On 3/22/2016 7:49 AM, Lentes, Bernd wrote:



- On Mar 22, 2016, at 12:07 PM, william drescher will...@techservsys.com 
wrote:


sent for Bernd, and to see if it works from another sender
--
  Lentes, Bernd wrote:
Hi,

i know that there is a list dedicated to replication, but when
you have a look in the archive it's nearly complete empty. Really
not busy.
So i hope it's ok if i ask here.
we have a web app which runs a MySQL DB and dynamic webpages with
perl and apache httpd. Webpages serve reading and writing into
the db. The db is important for our own work flow, so i'd like to
make it HA. I have two HP servers and will use SLES 11 SP4 64bit
as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which
is available in SLES High Availibility Extension. I have
experience in linux, but i'm not a database administrator nor
developer. HA is important for us, we don't have performance
problems.
My first idea was to run the web app and the db in a virtual
machine on the host and in case of a failure of one host
pacemaker would run the vm on the other host. VM would be stored
on a FC SAN. I stopped following this idea. I have bought a book
about HA: "..." from Oliver Liebel. It's only available in
german. But i can recommend it, it's very detailed and well
explained.
He proposed to have two hosts, and on each is running a MySQL
instance as master AND slave. But it's not a "real multi master
solution", because pacemaker takes care that the IP for the web
app just points to one master. So i don't have the multi-master
problems with concurrent inserts (i believe).
His idea is that host A is master for the slave on host B, and
host B is the master for the slave on host A. OK ?
Let's imagining that the IP to the web app points to host A,
inserts are done to the master on host A and replicated to the
slave on host B. Now host A has problems, pacemaker redirects the
IP to host B, and everything should be fine.
What do you think about this setup ? Where is the advantage to a
"classical Master/Slave Replication" ? How should i configure
log-slave-updates in this scenario ?
Let's imagine i have two hosts again: Host A is master, host B is
slave. Nothing else. No real or pseudo "Multi-Master". IP points
to host A. Host A has problems, pacemaker recognizes it, promotes
B to master and pivot the IP. Everything should be fine. Where is
the disadvantage of this setup compared to the "Multi-Master
Replication" in the book ? The OCF ressource agent for mysql
should be able to handle the mysql stuff and the RA for the IP
pivots the IP.

Now some dedicated questions to replication. I read a lot in the
official documentation, but some things are not clear to me.
In our db we have MyISAM and InnoDB tables.

 From what i read i'd prefer row based replication. The doc says
is the safest approach. But there seems to be still some problems:

The doc says: "For tables using the MYISAM storage engine, a
stronger lock is required on the slave for INSERT statements when
applying them as row-based events to the binary log than when
applying them as statements. This means that concurrent inserts
on MyISAM tables are not supported when using row-based
replication."
What does this exactly mean ? Concurrent inserts in MyISAM-tables
are not possible if using RBL ? Or unsafe in the meaning they
create inconsistencies ?

"RBL (Row Based Logging) and synchronization of nontransactional
tables. When many rows are affected, the set of changes is split
into several events; when the statement commits, all of these
events are written to the binary log. When executing on the
slave, a table lock is taken on all tables involved, and then
the rows are applied in batch mode. (This may or may not be
effective, depending on the engine used for the slave抯 copy of
the table.)"
What does that mean ? Effective ? Is it creating inconsistencies
? Or just not effective in the sense of slow or inconvinient ?

Or should i prefer MIXED for binlog_format ?

The doc says: " If a statement is logged by row and the session
that executed the statement has any temporary tables, logging by
row is used for all subsequent statements (except for those
accessing temporary tables) until all temporary tables in use by
that session are dropped.
This is true whether or not any temporary tables are actually
logged. Temporary tables cannot be logged using row-based format;
thus, once row-based logging is used, all subsequent statements
using that table are unsafe. The server approximates this
condition by treating all statements executed during the session
as unsafe until the session no longer holds any temporary tables."
What does that mean ? Unsafe ? Causing inconsistencies ? Problem
with SBL or RBL ?

The doc says: "Due to concurrency issues, a slave can become
inconsistent when a transaction contains updates to both
transactional and nontransactional tables. MySQL tries to
preserve causality among these statements by writing

Re: need help from the list admin

[Lentes, Bernd]

- On Mar 22, 2016, at 12:07 PM, william drescher will...@techservsys.com 
wrote:

> sent for Bernd, and to see if it works from another sender
> --
>  Lentes, Bernd wrote:
> Hi,
> 
> i know that there is a list dedicated to replication, but when
> you have a look in the archive it's nearly complete empty. Really
> not busy.
> So i hope it's ok if i ask here.
> we have a web app which runs a MySQL DB and dynamic webpages with
> perl and apache httpd. Webpages serve reading and writing into
> the db. The db is important for our own work flow, so i'd like to
> make it HA. I have two HP servers and will use SLES 11 SP4 64bit
> as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which
> is available in SLES High Availibility Extension. I have
> experience in linux, but i'm not a database administrator nor
> developer. HA is important for us, we don't have performance
> problems.
> My first idea was to run the web app and the db in a virtual
> machine on the host and in case of a failure of one host
> pacemaker would run the vm on the other host. VM would be stored
> on a FC SAN. I stopped following this idea. I have bought a book
> about HA: "..." from Oliver Liebel. It's only available in
> german. But i can recommend it, it's very detailed and well
> explained.
> He proposed to have two hosts, and on each is running a MySQL
> instance as master AND slave. But it's not a "real multi master
> solution", because pacemaker takes care that the IP for the web
> app just points to one master. So i don't have the multi-master
> problems with concurrent inserts (i believe).
> His idea is that host A is master for the slave on host B, and
> host B is the master for the slave on host A. OK ?
> Let's imagining that the IP to the web app points to host A,
> inserts are done to the master on host A and replicated to the
> slave on host B. Now host A has problems, pacemaker redirects the
> IP to host B, and everything should be fine.
> What do you think about this setup ? Where is the advantage to a
> "classical Master/Slave Replication" ? How should i configure
> log-slave-updates in this scenario ?
> Let's imagine i have two hosts again: Host A is master, host B is
> slave. Nothing else. No real or pseudo "Multi-Master". IP points
> to host A. Host A has problems, pacemaker recognizes it, promotes
> B to master and pivot the IP. Everything should be fine. Where is
> the disadvantage of this setup compared to the "Multi-Master
> Replication" in the book ? The OCF ressource agent for mysql
> should be able to handle the mysql stuff and the RA for the IP
> pivots the IP.
> 
> Now some dedicated questions to replication. I read a lot in the
> official documentation, but some things are not clear to me.
> In our db we have MyISAM and InnoDB tables.
> 
> From what i read i'd prefer row based replication. The doc says
> is the safest approach. But there seems to be still some problems:
> 
> The doc says: "For tables using the MYISAM storage engine, a
> stronger lock is required on the slave for INSERT statements when
> applying them as row-based events to the binary log than when
> applying them as statements. This means that concurrent inserts
> on MyISAM tables are not supported when using row-based
> replication."
> What does this exactly mean ? Concurrent inserts in MyISAM-tables
> are not possible if using RBL ? Or unsafe in the meaning they
> create inconsistencies ?
> 
> "RBL (Row Based Logging) and synchronization of nontransactional
> tables. When many rows are affected, the set of changes is split
> into several events; when the statement commits, all of these
> events are written to the binary log. When executing on the
> slave, a table lock is taken on all tables involved, and then
> the rows are applied in batch mode. (This may or may not be
> effective, depending on the engine used for the slave抯 copy of
> the table.)"
> What does that mean ? Effective ? Is it creating inconsistencies
> ? Or just not effective in the sense of slow or inconvinient ?
> 
> Or should i prefer MIXED for binlog_format ?
> 
> The doc says: " If a statement is logged by row and the session
> that executed the statement has any temporary tables, logging by
> row is used for all subsequent statements (except for those
> accessing temporary tables) until all temporary tables in use by
> that session are dropped.
> This is true whether or not any temporary tables are actually
> logged. Temporary tables cannot be logged using row-based format;
> thus, once row-based logging is used, all subsequent statements
> using that table are unsafe. The server approximates this
> condition by treating all statements executed during the session
> as unsafe until the session no longer holds any temporary tables."
> What does that mean ? Unsafe ? Causing inconsistencies ? Problem
> with SBL or RBL ?
> 
> The doc says: "Due to concurrency issues, a slave can become
> inconsistent when a transaction 

Re: need help from the list admin

[william drescher]

sent for Bernd, and to see if it works from another sender
--
 Lentes, Bernd wrote:
Hi,

i know that there is a list dedicated to replication, but when 
you have a look in the archive it's nearly complete empty. Really 
not busy.

So i hope it's ok if i ask here.
we have a web app which runs a MySQL DB and dynamic webpages with 
perl and apache httpd. Webpages serve reading and writing into 
the db. The db is important for our own work flow, so i'd like to 
make it HA. I have two HP servers and will use SLES 11 SP4 64bit 
as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which 
is available in SLES High Availibility Extension. I have 
experience in linux, but i'm not a database administrator nor 
developer. HA is important for us, we don't have performance 
problems.
My first idea was to run the web app and the db in a virtual 
machine on the host and in case of a failure of one host 
pacemaker would run the vm on the other host. VM would be stored 
on a FC SAN. I stopped following this idea. I have bought a book 
about HA: "..." from Oliver Liebel. It's only available in 
german. But i can recommend it, it's very detailed and well 
explained.
He proposed to have two hosts, and on each is running a MySQL 
instance as master AND slave. But it's not a "real multi master 
solution", because pacemaker takes care that the IP for the web 
app just points to one master. So i don't have the multi-master 
problems with concurrent inserts (i believe).
His idea is that host A is master for the slave on host B, and 
host B is the master for the slave on host A. OK ?
Let's imagining that the IP to the web app points to host A, 
inserts are done to the master on host A and replicated to the 
slave on host B. Now host A has problems, pacemaker redirects the 
IP to host B, and everything should be fine.
What do you think about this setup ? Where is the advantage to a 
"classical Master/Slave Replication" ? How should i configure 
log-slave-updates in this scenario ?
Let's imagine i have two hosts again: Host A is master, host B is 
slave. Nothing else. No real or pseudo "Multi-Master". IP points 
to host A. Host A has problems, pacemaker recognizes it, promotes 
B to master and pivot the IP. Everything should be fine. Where is 
the disadvantage of this setup compared to the "Multi-Master 
Replication" in the book ? The OCF ressource agent for mysql 
should be able to handle the mysql stuff and the RA for the IP 
pivots the IP.


Now some dedicated questions to replication. I read a lot in the 
official documentation, but some things are not clear to me.

In our db we have MyISAM and InnoDB tables.

From what i read i'd prefer row based replication. The doc says 
is the safest approach. But there seems to be still some problems:


The doc says: "For tables using the MYISAM storage engine, a 
stronger lock is required on the slave for INSERT statements when 
applying them as row-based events to the binary log than when 
applying them as statements. This means that concurrent inserts 
on MyISAM tables are not supported when using row-based 
replication."
What does this exactly mean ? Concurrent inserts in MyISAM-tables 
are not possible if using RBL ? Or unsafe in the meaning they 
create inconsistencies ?


"RBL (Row Based Logging) and synchronization of nontransactional 
tables. When many rows are affected, the set of changes is split 
into several events; when the statement commits, all of these 
events are written to the binary log. When executing on the 
slave, a table lock is taken on all tables involved, and then
the rows are applied in batch mode. (This may or may not be 
effective, depending on the engine used for the slave抯 copy of 
the table.)"
What does that mean ? Effective ? Is it creating inconsistencies 
? Or just not effective in the sense of slow or inconvinient ?


Or should i prefer MIXED for binlog_format ?

The doc says: " If a statement is logged by row and the session 
that executed the statement has any temporary tables, logging by 
row is used for all subsequent statements (except for those 
accessing temporary tables) until all temporary tables in use by 
that session are dropped.
This is true whether or not any temporary tables are actually 
logged. Temporary tables cannot be logged using row-based format; 
thus, once row-based logging is used, all subsequent statements 
using that table are unsafe. The server approximates this 
condition by treating all statements executed during the session 
as unsafe until the session no longer holds any temporary tables."
What does that mean ? Unsafe ? Causing inconsistencies ? Problem 
with SBL or RBL ?


The doc says: "Due to concurrency issues, a slave can become 
inconsistent when a transaction contains updates to both 
transactional and nontransactional tables. MySQL tries to 
preserve causality among these statements by writing 
nontransactional statements to the transaction cache, which is 
flushed 

Re: need help from the list admin

[Lentes, Bernd]

- On Mar 19, 2016, at 3:28 PM, Reindl Harald h.rei...@thelounge.net wrote:

> Am 19.03.2016 um 15:23 schrieb Reindl Harald:
>>
>>
>> Am 19.03.2016 um 15:17 schrieb Lentes, Bernd:
>>> one further question:
>>> if some of my e-mails get through (like this one) and others don't, it
>>> does not depend on theh reputation of our domain or mailserver ? Right ?
>>> So the reason has to be that particular e-Mail?
>>
>> both
>>
>> a spamfilter is typically score based and combines a ton of rules, some
>> add points, some remove points and the decision is made of the summary
>>
>> when you have a bad server reputation you start with a penalty, some
>> other rules hitting and a not well trained bayes makes the rest
>>
>> "How do i have to provide the ip" in case of RBLs?
>> https://en.wikipedia.org/wiki/Reverse_DNS_lookup
> 
> and that your domain even don't provide a "~all" SPF policy if you can't
> or don't want a stricht "-all" makes things not better, typically a
> SPF_PASS gives benefits in spamfilter scorings
> 
> 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.103.20
> 

OK guys. I asked our computing center to provide a SPF ressource record for our 
outgoing mta in the DNS and to take 
care that the ip of our outgoing mta appears on https://www.dnswl.org/ (our 
domain is listed already). I hope they will do.

The score of our mta raised already:


sunhb58820:~ # nslookup 20.103.107.146.score.senderscore.com.
Server: 146.107.1.88
Address:146.107.1.88#53

Non-authoritative answer:
Name:   20.103.107.146.score.senderscore.com
Address: 127.0.4.76
=

76 isn't bad.

But nevertheless it must also have to do with the e-mail itself. I sent it from 
gmx.de and web.de, both werde declined.
My other mails (like this one) arrive.
I shrinked the mail already, but also this did not help.
You can have a look on the two mails i tried: 
https://hmgubox.helmholtz-muenchen.de:8001/d/dc1ec4eb38/

I'm thankful for any hint what else i can do, also with the mail itself.


Bernd


 

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

need help from the list admin

[Lentes, Bernd]

Dear list admin,

i need your help. I'm trying to write an e-Mail to the list for already one 
week. I always get it back because it's classified as spam.
The mail is formatted as plain-text, include neither links nor attachments. I 
don't understand why it's classified as spam. Neither our domain nor the ip of 
our outgoing mailserver appears currently on a blacklist, as far as i see. 
Harald Reindl, a member from the list, tried already to help me, but also he 
couldn't find out why it is rejected.
Can you tell me why it's classified as spam and what i can do that the mail is 
delivered correctly ?
The mail has the subject "Replication and HA - some basic questions".
I wrote already two mails to "list-ad...@mysql.com" but didn't get an answer.

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 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: need help from the list admin

[Reindl Harald]

Am 18.03.2016 um 14:56 schrieb Chris Knipe:

Blah blah blah...

Delivery to the following recipient failed permanently:

  mysql@lists.mysql.com

Technical details of permanent failure:
Your message was rejected by the server for the recipient domain
lists.mysql.com by lists-mx.mysql.com. [137.254.60.71].

The error that the other server returned was:
550 Currently Sending Spam See:
http://www.sorbs.net/lookup.shtml?5.200.22.158

Show me one site, where that IP is, or WAS ever blacklisted?


on sorbs as you quoated yourself, that only idiots block by 
"spam.dnsbl.sorbs.net" ann response 127.0.0.6 instead RBL scoring is a 
different story


51.192.85.209.in-addr.arpa name = mail-qg0-f51.google.com

Name:   51.192.85.209.spam.dnsbl.sorbs.net
Address: 127.0.0.6

Received-SPF: pass (savage.za.org: Sender is authorized to use
 'ckn...@savage.za.org' in 'mfrom' identity (mechanism
 'include:_spf.google.com' matched)) receiver=amysql-list-wsv01.oracle.com;
 identity=mailfrom; envelope-from="ckn...@savage.za.org";
 helo=mail-qg0-f51.google.com; client-ip=209.85.192.51




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Chris Knipe]

On Fri, Mar 18, 2016 at 3:43 PM, Lentes, Bernd <
bernd.len...@helmholtz-muenchen.de> wrote:

> i need your help. I'm trying to write an e-Mail to the list for already
> one week. I always get it back because it's classified as spam.
>


Ditto.  I've pretty much given up on this list...

Re: need help from the list admin

[Reindl Harald]

Am 19.03.2016 um 15:23 schrieb Reindl Harald:



Am 19.03.2016 um 15:17 schrieb Lentes, Bernd:

one further question:
if some of my e-mails get through (like this one) and others don't, it
does not depend on theh reputation of our domain or mailserver ? Right ?
So the reason has to be that particular e-Mail?


both

a spamfilter is typically score based and combines a ton of rules, some
add points, some remove points and the decision is made of the summary

when you have a bad server reputation you start with a penalty, some
other rules hitting and a not well trained bayes makes the rest

"How do i have to provide the ip" in case of RBLs?
https://en.wikipedia.org/wiki/Reverse_DNS_lookup


and that your domain even don't provide a "~all" SPF policy if you can't 
or don't want a stricht "-all" makes things not better, typically a 
SPF_PASS gives benefits in spamfilter scorings


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

[harry@srv-rhsoft:~]$ dig TXT helmholtz-muenchen.de @8.8.8.8
; <<>> DiG 9.10.3-P4-RedHat-9.10.3-12.P4.fc23 <<>> TXT 
helmholtz-muenchen.de @8.8.8.8

;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 25126
;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1

[harry@srv-rhsoft:~]$ dig TXT thelounge.net @8.8.8.8
;; ANSWER SECTION:
thelounge.net.  21599   IN  TXT 
"google-site-verification=XQcET0ij0uOdn8AvlL82t8FoGTthvfPKWRfNjSNTfaM"
thelounge.net.  21599   IN  TXT "v=spf1 a 
ip4:91.118.73.0/24 ip4:95.129.202.170 -all"




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Reindl Harald]

Am 19.03.2016 um 15:17 schrieb Lentes, Bernd:

one further question:
if some of my e-mails get through (like this one) and others don't, it does not 
depend on theh reputation of our domain or mailserver ? Right ?
So the reason has to be that particular e-Mail?


both

a spamfilter is typically score based and combines a ton of rules, some 
add points, some remove points and the decision is made of the summary


when you have a bad server reputation you start with a penalty, some 
other rules hitting and a not well trained bayes makes the rest


"How do i have to provide the ip" in case of RBLs?
https://en.wikipedia.org/wiki/Reverse_DNS_lookup




signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Reindl Harald]

Am 19.03.2016 um 15:08 schrieb Lentes, Bernd:

Ok. I tried again:

pc53200:~ # nslookup 20.103.107.146.score.senderscore.com.
Server: 146.107.8.88
Address:146.107.8.88#53

Non-authoritative answer:
Name:   20.103.107.146.score.senderscore.com
Address: 127.0.4.62

My result is 127.0.4.62. How can i interpret this result?


simple - it's a reputation score
100 = perfect reputation - whitelist score
0 = no reputation, pure spammer

with 62 you make it through postscreen but end with 2.5 points penalty 
in SA and that you had 2 days ago "senderscore.com LISTED 127.0.4.63" 
and now got worser scores shows that your outgoing server sends spam 
(given that we have full reputation 100 there without any actve 
operation, even before i did know about the RBL/DNSWL)


anything below 127.0.4.70 should raise alerts
___

our postscreen-scoring:
score.senderscore.com=127.0.4.[0..20]*2
score.senderscore.com=127.0.4.[0..69]*2
score.senderscore.com=127.0.4.[90..100]*-1
___

our spamassassin scoring:

header   CUST_DNSBL_21 
eval:check_rbl('cust21-lastexternal','score.senderscore.com.','^127\.0\.4\.(1?[0-9]|20)$')

describe CUST_DNSBL_21 score.senderscore.com (senderscore.com High)
scoreCUST_DNSBL_21 1.5

header   CUST_DNSBL_25 
eval:check_rbl('cust25-lastexternal','score.senderscore.com.','^127\.0\.4\.(0?[0-6]?[0-9])$')

describe CUST_DNSBL_25 score.senderscore.com (senderscore.com Medium)
scoreCUST_DNSBL_25 1.0

header   CUST_DNSWL_2 
eval:check_rbl('cust35-lastexternal','score.senderscore.com.','^127\.0\.4\.(9[0-9]|100)$')

describe CUST_DNSWL_2 score.senderscore.com (Low Trust)
scoreCUST_DNSWL_2 -0.1



signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Lentes, Bernd]

Hi,

one further question:
if some of my e-mails get through (like this one) and others don't, it does not 
depend on theh reputation of our domain or mailserver ? Right ?
So the reason has to be that particular e-Mail ?


Bernd
 

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: need help from the list admin

[Lentes, Bernd]

- Am 18. Mrz 2016 um 15:34 schrieb Reindl Harald h.rei...@thelounge.net:

> Am 18.03.2016 um 15:25 schrieb Lentes, Bernd:
>>
>> - Am 18. Mrz 2016 um 14:52 schrieb Johan De Meersman vegiv...@tuxera.be:
>>

> 
> as i already told you offlist
> senderscore.com  LISTED  127.0.4.67
> 
> this *is* a bad reputation
> 
> and more worse: you did not manage to get your server on any DNSWL
> 
> [harry@srv-rhsoft:~]$ nslookup 20.103.107.146.score.senderscore.com.
> Server: 127.0.0.1
> Address:127.0.0.1#53
> Non-authoritative answer:
> Name:   20.103.107.146.score.senderscore.com
> Address: 127.0.4.67

Ok. I tried again:

pc53200:~ # nslookup 20.103.107.146.score.senderscore.com.
Server: 146.107.8.88
Address:146.107.8.88#53

Non-authoritative answer:
Name:   20.103.107.146.score.senderscore.com
Address: 127.0.4.62

My result is 127.0.4.62. How can i interpret this result ? I was looking on 
senderscroe.com to find any explaination, but not have been successfull.
Also i'm redirected to senderscore.org. Is that ok ?
Does that mean my reputation is 62 ? That would be bad. Because if i check the 
ip of our outgoing mailserver (146.107.103.20) in the webinterface, i get a 
reputation of 74, which is not great but hopefully ok.


I also tested sorbs.net:

pc53200:~ # nslookup 20.103.107.146.dnsbl.sorbs.net
Server: 146.107.8.88
Address:146.107.8.88#53

*** Can't find 20.103.107.146.dnsbl.sorbs.net: No answer

pc53200:~ # nslookup 146.107.103.20.dnsbl.sorbs.net
Server: 146.107.8.88
Address:146.107.8.88#53

*** Can't find 146.107.103.20.dnsbl.sorbs.net: No answer

(How do i have to provide the ip ? ) Our mailserver seems not do appear on 
sorbs.net. Is it a good sign ?


Bernd

 

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: need help from the list admin

[Reindl Harald]

Am 18.03.2016 um 15:25 schrieb Lentes, Bernd:


- Am 18. Mrz 2016 um 14:52 schrieb Johan De Meersman vegiv...@tuxera.be:


and yet, both of those messages made it through :-p

Stick your domain in http://mxtoolbox.com to see if there's any problems that
might be worth solving. If the mailserver classifies you as spam, that's
usually caused by something on your side :-)

- Original Message -

From: "Chris Knipe" <sav...@savage.za.org>
To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de>
Cc: "MySql" <mysql@lists.mysql.com>
Sent: Friday, 18 March, 2016 14:46:26
Subject: Re: need help from the list admin



Ditto.  I've pretty much given up on this list...




Neither our outgoing mailserver 
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#)
 nor our domain
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#)
 is listed there.
I checked that before i wrote the e-Mail. If you could help me to point out 
what's wrong on our side i could ask our mail admin to correct it.
Currently i don't have any idea


as i already told you offlist
senderscore.com  LISTED  127.0.4.67

this *is* a bad reputation

and more worse: you did not manage to get your server on any DNSWL

[harry@srv-rhsoft:~]$ nslookup 20.103.107.146.score.senderscore.com.
Server: 127.0.0.1
Address:127.0.0.1#53
Non-authoritative answer:
Name:   20.103.107.146.score.senderscore.com
Address: 127.0.4.67
_

compare with 91.118.73.15 (our outgoing server) which has there the best 
possible reputation (treated as whitelist) and is at the same time on 
the "list.dnswl.org" and "hostkarma.junkemailfilter" while one of both 
would possibly neutralize the BL listing in a scoring system


[harry@srv-rhsoft:~]$ nslookup 15.73.118.91.score.senderscore.com.
Server: 127.0.0.1
Address:127.0.0.1#53
Non-authoritative answer:
Name:   15.73.118.91.score.senderscore.com
Address: 127.0.4.100



signature.asc
Description: OpenPGP digital signature

Re: need help from the list admin

[Chris Knipe]

Ok :-)



On Fri, Mar 18, 2016 at 4:34 PM, Reindl Harald <h.rei...@thelounge.net>
wrote:

>
>
> Am 18.03.2016 um 15:25 schrieb Lentes, Bernd:
>
>>
>> - Am 18. Mrz 2016 um 14:52 schrieb Johan De Meersman
>> vegiv...@tuxera.be:
>>
>> and yet, both of those messages made it through :-p
>>>
>>> Stick your domain in http://mxtoolbox.com to see if there's any
>>> problems that
>>> might be worth solving. If the mailserver classifies you as spam, that's
>>> usually caused by something on your side :-)
>>>
>>> - Original Message -
>>>
>>>> From: "Chris Knipe" <sav...@savage.za.org>
>>>> To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de>
>>>> Cc: "MySql" <mysql@lists.mysql.com>
>>>> Sent: Friday, 18 March, 2016 14:46:26
>>>> Subject: Re: need help from the list admin
>>>>
>>>
>>> Ditto.  I've pretty much given up on this list...
>>>>
>>>
>>>
>> Neither our outgoing mailserver (
>> http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#)
>> nor our domain
>> (
>> http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#)
>> is listed there.
>> I checked that before i wrote the e-Mail. If you could help me to point
>> out what's wrong on our side i could ask our mail admin to correct it.
>> Currently i don't have any idea
>>
>
> as i already told you offlist
> senderscore.com  LISTED  127.0.4.67
>
> this *is* a bad reputation
>
> and more worse: you did not manage to get your server on any DNSWL
>
> [harry@srv-rhsoft:~]$ nslookup 20.103.107.146.score.senderscore.com.
> Server: 127.0.0.1
> Address:127.0.0.1#53
> Non-authoritative answer:
> Name:   20.103.107.146.score.senderscore.com
> Address: 127.0.4.67
> _
>
> compare with 91.118.73.15 (our outgoing server) which has there the best
> possible reputation (treated as whitelist) and is at the same time on the "
> list.dnswl.org" and "hostkarma.junkemailfilter" while one of both would
> possibly neutralize the BL listing in a scoring system
>
> [harry@srv-rhsoft:~]$ nslookup 15.73.118.91.score.senderscore.com.
> Server: 127.0.0.1
> Address:127.0.0.1#53
> Non-authoritative answer:
> Name:   15.73.118.91.score.senderscore.com
> Address: 127.0.4.100
>
>


-- 

Regards,
Chris Knipe

Re: need help from the list admin

[Lentes, Bernd]

- Am 18. Mrz 2016 um 14:52 schrieb Johan De Meersman vegiv...@tuxera.be:

> and yet, both of those messages made it through :-p
> 
> Stick your domain in http://mxtoolbox.com to see if there's any problems that
> might be worth solving. If the mailserver classifies you as spam, that's
> usually caused by something on your side :-)
> 
> - Original Message -
>> From: "Chris Knipe" <sav...@savage.za.org>
>> To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de>
>> Cc: "MySql" <mysql@lists.mysql.com>
>> Sent: Friday, 18 March, 2016 14:46:26
>> Subject: Re: need help from the list admin
> 
>> Ditto.  I've pretty much given up on this list...
> 
> --
> Unhappiness is discouraged and will be corrected with kitten pictures.

Hi Johan,


Neither our outgoing mailserver 
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#)
 nor our domain
(http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#)
 is listed there.
I checked that before i wrote the e-Mail. If you could help me to point out 
what's wrong on our side i could ask our mail admin to correct it.
Currently i don't have any idea.


Bernd
 

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: need help from the list admin

[Chris Knipe]

Blah blah blah...

Delivery to the following recipient failed permanently:

 mysql@lists.mysql.com

Technical details of permanent failure:
Your message was rejected by the server for the recipient domain
lists.mysql.com by lists-mx.mysql.com. [137.254.60.71].

The error that the other server returned was:
550 Currently Sending Spam See:
http://www.sorbs.net/lookup.shtml?5.200.22.158


Show me one site, where that IP is, or WAS ever blacklisted?


--
Chris.




On Fri, Mar 18, 2016 at 3:52 PM, Johan De Meersman <vegiv...@tuxera.be>
wrote:

>
> and yet, both of those messages made it through :-p
>
> Stick your domain in http://mxtoolbox.com to see if there's any problems
> that might be worth solving. If the mailserver classifies you as spam,
> that's usually caused by something on your side :-)
>
> - Original Message -
> > From: "Chris Knipe" <sav...@savage.za.org>
> > To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de>
> > Cc: "MySql" <mysql@lists.mysql.com>
> > Sent: Friday, 18 March, 2016 14:46:26
> > Subject: Re: need help from the list admin
>
> > Ditto.  I've pretty much given up on this list...
>
> --
> Unhappiness is discouraged and will be corrected with kitten pictures.
>



-- 

Regards,
Chris Knipe

Lost connection to MySQL server - need help.

[Jørn Dahl-Stamnes]

Hello,

I got a strange problem related to a production server. It has been working OK 
for months, but yesterday it start to fail. There are several batch scripts 
using the database in addition to a web application using it.

The php scripts running in batch mode began to get:

mysql_connect(): Lost connection to MySQL server at 'reading initial 
communication packet', system error: 111

I stopped the server and restarted it and everything seems to work OK for 
hours but when the load start to increase, the errors begin to appear again.

Today I noticed that after I starte phpMyAdmin and selected one of the 
databases, phpMyAdmin was hanging and the batch scripts began to fail again. 
Seems like the server does not handle much load anymore.


What's strange is the memory usage. The server is a quad core cpu with 48 Gb 
memory, where 28 Gb is allocated to innodb (we mostly use innodb). But when 
using top command, I noticed this:

VIRT: 33.9g
RES: 9.4g
SWAP: 23g

at this time over 11G memory is free. vm.swappiness is set to 0. I find it 
strange that the server is not able to use physical memory but use swap 
instead. The amount of cpu time used for swapping is rather high during sql 
queries. The amount of RESident memory may increase slowly over time but very 
slowly (it can take hours before it increase to 20+ Gb).

[PS: I also got a MySQL server running at a dedicated host at home, where the 
it seem to use the memory as I except it to use:

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  SWAP DATA COMMAND
 1462 mysql 20   0 30.0g  27g 3900 S  0.3 87.3   2633:14 844m  29g mysqld
]


I would like to have some suggestions what I can do to solve this problem.
I have google'd it but found nothing that seem to solve my case.

Server:
  OS: Debian 6
  MySQL: 5.1.61-0+squeeze1

my.cnf:
#
# The MySQL database server configuration file.
#

[client]
port= 3306
socket  = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld].
[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice= 0

[mysqld]
#
# * Basic Settings
#
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port= 3306
basedir = /usr
datadir = /database/mysql
tmpdir  = /tmp
language= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address= 127.0.0.1
## All applications use 127.0.0.1 when connectiong to the db.

#
# * Fine Tuning
#
#key_buffer = 16M
max_allowed_packet  = 64M
thread_stack= 192K
#thread_cache_size   = 8

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#
# * Query Cache Configuration
#
query_cache_limit   = 1M

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#   other settings you may need to change.
#server-id  = 1
#log_bin= /var/log/mysql/mysql-bin.log
expire_logs_days= 10
max_binlog_size = 100M
#binlog_do_db   = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

thread_cache_size = 192
table_cache = 768
## key_buffer = 64M
## sort_buffer_size = 256K
## read_buffer_size = 256K
## read_rnd_buffer_size = 256K
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=2

innodb_open_files=1000
innodb_buffer_pool_size = 28G
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
## innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
## innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 14
innodb_file_per_table

max_connections = 100
binlog_cache_size   = 1M
sort_buffer_size= 16M
join_buffer_size= 16M
ft_min_word_len = 1
ft_max_word_len = 84
ft_stopword_file= ''
default_table_type  = InnoDB
key_buffer  = 2G
read_buffer_size= 2M
read_rnd_buffer_size= 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size   = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads   = 1
myisam_recover

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab 

Re: Lost connection to MySQL server - need help.

[nixofortune]

You might want to comment

bind-address= 127.0.0.1

in your my.cnf and restart mysql server.



On 12/10/13 10:49, Jørn Dahl-Stamnes wrote:

Hello,

I got a strange problem related to a production server. It has been working OK
for months, but yesterday it start to fail. There are several batch scripts
using the database in addition to a web application using it.

The php scripts running in batch mode began to get:

mysql_connect(): Lost connection to MySQL server at 'reading initial
communication packet', system error: 111

I stopped the server and restarted it and everything seems to work OK for
hours but when the load start to increase, the errors begin to appear again.

Today I noticed that after I starte phpMyAdmin and selected one of the
databases, phpMyAdmin was hanging and the batch scripts began to fail again.
Seems like the server does not handle much load anymore.


What's strange is the memory usage. The server is a quad core cpu with 48 Gb
memory, where 28 Gb is allocated to innodb (we mostly use innodb). But when
using top command, I noticed this:

VIRT: 33.9g
RES: 9.4g
SWAP: 23g

at this time over 11G memory is free. vm.swappiness is set to 0. I find it
strange that the server is not able to use physical memory but use swap
instead. The amount of cpu time used for swapping is rather high during sql
queries. The amount of RESident memory may increase slowly over time but very
slowly (it can take hours before it increase to 20+ Gb).

[PS: I also got a MySQL server running at a dedicated host at home, where the
it seem to use the memory as I except it to use:

   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  SWAP DATA COMMAND
  1462 mysql 20   0 30.0g  27g 3900 S  0.3 87.3   2633:14 844m  29g mysqld
]


I would like to have some suggestions what I can do to solve this problem.
I have google'd it but found nothing that seem to solve my case.

Server:
   OS: Debian 6
   MySQL: 5.1.61-0+squeeze1

my.cnf:
#
# The MySQL database server configuration file.
#

[client]
port= 3306
socket  = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld].
[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice= 0

[mysqld]
#
# * Basic Settings
#
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port= 3306
basedir = /usr
datadir = /database/mysql
tmpdir  = /tmp
language= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address= 127.0.0.1
## All applications use 127.0.0.1 when connectiong to the db.

#
# * Fine Tuning
#
#key_buffer = 16M
max_allowed_packet  = 64M
thread_stack= 192K
#thread_cache_size   = 8

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#
# * Query Cache Configuration
#
query_cache_limit   = 1M

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#   other settings you may need to change.
#server-id  = 1
#log_bin= /var/log/mysql/mysql-bin.log
expire_logs_days= 10
max_binlog_size = 100M
#binlog_do_db   = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

thread_cache_size = 192
table_cache = 768
## key_buffer = 64M
## sort_buffer_size = 256K
## read_buffer_size = 256K
## read_rnd_buffer_size = 256K
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=2

innodb_open_files=1000
innodb_buffer_pool_size = 28G
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
## innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
## innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 14
innodb_file_per_table

max_connections = 100
binlog_cache_size   = 1M
sort_buffer_size= 16M
join_buffer_size= 16M
ft_min_word_len = 1
ft_max_word_len = 84
ft_stopword_file= ''
default_table_type  = InnoDB
key_buffer  = 2G
read_buffer_size= 2M
read_rnd_buffer_size= 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size   = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads   

Re: Lost connection to MySQL server - need help.

[Jørn Dahl-Stamnes]

On Saturday 12 October 2013 12:01, nixofortune wrote:
 You might want to comment

 bind-address= 127.0.0.1

 in your my.cnf and restart mysql server.

It does not explain why it works under low load and not under high load.

However, I seem to have found something. When I started phpMyAdmin and 
selected one of the database, the server went away again and I found this 
in /var/log/syslog:

Oct 12 11:53:33 cebycny mysqld: 131012 11:53:33  InnoDB: Assertion failure in 
thread 140182892447488 in file ../../../storage/innobase/handler/ha_innodb.cc 
line
 8066
Oct 12 11:53:33 cebycny mysqld: InnoDB: Failing assertion: auto_inc  0
Oct 12 11:53:33 cebycny mysqld: InnoDB: We intentionally generate a memory 
trap.
Oct 12 11:53:33 cebycny mysqld: InnoDB: Submit a detailed bug report to 
http://bugs.mysql.com.
Oct 12 11:53:33 cebycny mysqld: InnoDB: If you get repeated assertion failures 
or crashes, even
Oct 12 11:53:33 cebycny mysqld: InnoDB: immediately after the mysqld startup, 
there may be
Oct 12 11:53:33 cebycny mysqld: InnoDB: corruption in the InnoDB tablespace. 
Please refer to
Oct 12 11:53:33 cebycny mysqld: InnoDB: 
http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
Oct 12 11:53:33 cebycny mysqld: InnoDB: about forcing recovery.
Oct 12 11:53:33 cebycny mysqld: 09:53:33 UTC - mysqld got signal 6 ;
Oct 12 11:53:33 cebycny mysqld: This could be because you hit a bug. It is 
also possible that this binary
Oct 12 11:53:33 cebycny mysqld: or one of the libraries it was linked against 
is corrupt, improperly built,
Oct 12 11:53:33 cebycny mysqld: or misconfigured. This error can also be 
caused by malfunctioning hardware.
Oct 12 11:53:33 cebycny mysqld: We will try our best to scrape up some info 
that will hopefully help
Oct 12 11:53:33 cebycny mysqld: diagnose the problem, but since we have 
already crashed,
Oct 12 11:53:33 cebycny mysqld: something is definitely wrong and this may 
fail.
Oct 12 11:53:33 cebycny mysqld:
Oct 12 11:53:33 cebycny mysqld: key_buffer_size=2147483648
Oct 12 11:53:33 cebycny mysqld: read_buffer_size=2097152
Oct 12 11:53:33 cebycny mysqld: max_used_connections=8
Oct 12 11:53:33 cebycny mysqld: max_threads=100
Oct 12 11:53:33 cebycny mysqld: thread_count=2
Oct 12 11:53:33 cebycny mysqld: connection_count=2
Oct 12 11:53:33 cebycny mysqld: It is possible that mysqld could use up to
Oct 12 11:53:33 cebycny mysqld: key_buffer_size + (read_buffer_size + 
sort_buffer_size)*max_threads = 3941387 K  bytes of memory
Oct 12 11:53:33 cebycny mysqld: Hope that's ok; if not, decrease some 
variables in the equation.
Oct 12 11:53:33 cebycny mysqld:
Oct 12 11:53:33 cebycny mysqld: Thread pointer: 0x7f7f1bf997c0
Oct 12 11:53:33 cebycny mysqld: Attempting backtrace. You can use the 
following information to find out
Oct 12 11:53:33 cebycny mysqld: where mysqld died. If you see no messages 
after this, something went
Oct 12 11:53:33 cebycny mysqld: terribly wrong...
Oct 12 11:53:33 cebycny mysqld: stack_bottom = 7f7edf81fe88 thread_stack 
0x3
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x29) 
[0x7f7edff62b59]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x483) 
[0x7f7edfd774a3]
Oct 12 11:53:33 cebycny mysqld: /lib/libpthread.so.0(+0xeff0) [0x7f7edf4c9ff0]
Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(gsignal+0x35) [0x7f7eddf6c1b5]
Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(abort+0x180) [0x7f7eddf6efc0]
Oct 12 11:53:33 cebycny 
mysqld: /usr/sbin/mysqld(ha_innobase::innobase_peek_autoinc()+0x8f) 
[0x7f7edfe1fa2f]
Oct 12 11:53:33 cebycny 
mysqld: /usr/sbin/mysqld(ha_innobase::info_low(unsigned int, bool)+0x18f) 
[0x7f7edfe2524f]
Oct 12 11:53:33 cebycny 
mysqld: 
/usr/sbin/mysqld(ha_innobase::update_create_info(st_ha_create_information*)+0x29)
 
[0x7f7edfe256b9]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(+0x49e3dc) [0x7f7edfd953dc]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(mysqld_show_create(THD*, 
TABLE_LIST*)+0x7a8) [0x7f7edfd9d388]
Oct 12 11:53:33 cebycny 
mysqld: /usr/sbin/mysqld(mysql_execute_command(THD*)+0x184a) [0x7f7edfc7cb0a]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(mysql_parse(THD*, char*, 
unsigned int, char const**)+0x3fb) [0x7f7edfc80dbb]
Oct 12 11:53:33 cebycny 
mysqld: /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, 
unsigned int)+0x115a) [0x7f7edfc81f2a]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(do_command(THD*)+0xea) 
[0x7f7edfc8285a]
Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(handle_one_connection+0x235) 
[0x7f7edfc74435]
Oct 12 11:53:33 cebycny mysqld: /lib/libpthread.so.0(+0x68ca) [0x7f7edf4c18ca]
Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(clone+0x6d) [0x7f7ede00992d]
Oct 12 11:53:33 cebycny mysqld:
Oct 12 11:53:33 cebycny mysqld: Trying to get some variables.
Oct 12 11:53:33 cebycny mysqld: Some pointers may be invalid and cause the 
dump to abort.
Oct 12 11:53:33 cebycny mysqld: Query (7f7f1c0dcbc0): SHOW CREATE TABLE 
`calculation`
Oct 12 

Re: Lost connection to MySQL server - need help.

[Andrew Moore]

Could be a crash related to innodb data dictionary being out of sync. Could
be a bug.

http://bugs.mysql.com/bug.php?id=55277
On 12 Oct 2013 11:21, Jørn Dahl-Stamnes sq...@dahl-stamnes.net wrote:

 On Saturday 12 October 2013 12:01, nixofortune wrote:
  You might want to comment
 
  bind-address= 127.0.0.1
 
  in your my.cnf and restart mysql server.

 It does not explain why it works under low load and not under high load.

 However, I seem to have found something. When I started phpMyAdmin and
 selected one of the database, the server went away again and I found this
 in /var/log/syslog:

 Oct 12 11:53:33 cebycny mysqld: 131012 11:53:33  InnoDB: Assertion failure
 in
 thread 140182892447488 in file
 ../../../storage/innobase/handler/ha_innodb.cc
 line
  8066
 Oct 12 11:53:33 cebycny mysqld: InnoDB: Failing assertion: auto_inc  0
 Oct 12 11:53:33 cebycny mysqld: InnoDB: We intentionally generate a memory
 trap.
 Oct 12 11:53:33 cebycny mysqld: InnoDB: Submit a detailed bug report to
 http://bugs.mysql.com.
 Oct 12 11:53:33 cebycny mysqld: InnoDB: If you get repeated assertion
 failures
 or crashes, even
 Oct 12 11:53:33 cebycny mysqld: InnoDB: immediately after the mysqld
 startup,
 there may be
 Oct 12 11:53:33 cebycny mysqld: InnoDB: corruption in the InnoDB
 tablespace.
 Please refer to
 Oct 12 11:53:33 cebycny mysqld: InnoDB:
 http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
 Oct 12 11:53:33 cebycny mysqld: InnoDB: about forcing recovery.
 Oct 12 11:53:33 cebycny mysqld: 09:53:33 UTC - mysqld got signal 6 ;
 Oct 12 11:53:33 cebycny mysqld: This could be because you hit a bug. It is
 also possible that this binary
 Oct 12 11:53:33 cebycny mysqld: or one of the libraries it was linked
 against
 is corrupt, improperly built,
 Oct 12 11:53:33 cebycny mysqld: or misconfigured. This error can also be
 caused by malfunctioning hardware.
 Oct 12 11:53:33 cebycny mysqld: We will try our best to scrape up some info
 that will hopefully help
 Oct 12 11:53:33 cebycny mysqld: diagnose the problem, but since we have
 already crashed,
 Oct 12 11:53:33 cebycny mysqld: something is definitely wrong and this may
 fail.
 Oct 12 11:53:33 cebycny mysqld:
 Oct 12 11:53:33 cebycny mysqld: key_buffer_size=2147483648
 Oct 12 11:53:33 cebycny mysqld: read_buffer_size=2097152
 Oct 12 11:53:33 cebycny mysqld: max_used_connections=8
 Oct 12 11:53:33 cebycny mysqld: max_threads=100
 Oct 12 11:53:33 cebycny mysqld: thread_count=2
 Oct 12 11:53:33 cebycny mysqld: connection_count=2
 Oct 12 11:53:33 cebycny mysqld: It is possible that mysqld could use up to
 Oct 12 11:53:33 cebycny mysqld: key_buffer_size + (read_buffer_size +
 sort_buffer_size)*max_threads = 3941387 K  bytes of memory
 Oct 12 11:53:33 cebycny mysqld: Hope that's ok; if not, decrease some
 variables in the equation.
 Oct 12 11:53:33 cebycny mysqld:
 Oct 12 11:53:33 cebycny mysqld: Thread pointer: 0x7f7f1bf997c0
 Oct 12 11:53:33 cebycny mysqld: Attempting backtrace. You can use the
 following information to find out
 Oct 12 11:53:33 cebycny mysqld: where mysqld died. If you see no messages
 after this, something went
 Oct 12 11:53:33 cebycny mysqld: terribly wrong...
 Oct 12 11:53:33 cebycny mysqld: stack_bottom = 7f7edf81fe88 thread_stack
 0x3
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x29)
 [0x7f7edff62b59]
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x483)
 [0x7f7edfd774a3]
 Oct 12 11:53:33 cebycny mysqld: /lib/libpthread.so.0(+0xeff0)
 [0x7f7edf4c9ff0]
 Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(gsignal+0x35)
 [0x7f7eddf6c1b5]
 Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(abort+0x180)
 [0x7f7eddf6efc0]
 Oct 12 11:53:33 cebycny
 mysqld: /usr/sbin/mysqld(ha_innobase::innobase_peek_autoinc()+0x8f)
 [0x7f7edfe1fa2f]
 Oct 12 11:53:33 cebycny
 mysqld: /usr/sbin/mysqld(ha_innobase::info_low(unsigned int, bool)+0x18f)
 [0x7f7edfe2524f]
 Oct 12 11:53:33 cebycny
 mysqld:
 /usr/sbin/mysqld(ha_innobase::update_create_info(st_ha_create_information*)+0x29)
 [0x7f7edfe256b9]
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(+0x49e3dc)
 [0x7f7edfd953dc]
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(mysqld_show_create(THD*,
 TABLE_LIST*)+0x7a8) [0x7f7edfd9d388]
 Oct 12 11:53:33 cebycny
 mysqld: /usr/sbin/mysqld(mysql_execute_command(THD*)+0x184a)
 [0x7f7edfc7cb0a]
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(mysql_parse(THD*, char*,
 unsigned int, char const**)+0x3fb) [0x7f7edfc80dbb]
 Oct 12 11:53:33 cebycny
 mysqld: /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*,
 unsigned int)+0x115a) [0x7f7edfc81f2a]
 Oct 12 11:53:33 cebycny mysqld: /usr/sbin/mysqld(do_command(THD*)+0xea)
 [0x7f7edfc8285a]
 Oct 12 11:53:33 cebycny mysqld:
 /usr/sbin/mysqld(handle_one_connection+0x235)
 [0x7f7edfc74435]
 Oct 12 11:53:33 cebycny mysqld: /lib/libpthread.so.0(+0x68ca)
 [0x7f7edf4c18ca]
 Oct 12 11:53:33 cebycny mysqld: /lib/libc.so.6(clone+0x6d) [0x7f7ede00992d]
 Oct 12 

Re: Lost connection to MySQL server - need help.

[Jørn Dahl-Stamnes]

On Saturday 12 October 2013 13:07, Andrew Moore wrote:
 Could be a crash related to innodb data dictionary being out of sync. Could
 be a bug.

Seems like a bug yes. However, we had a strange situation yesterday when we 
had several processes in the state copying to tmp table (if i remember the 
exact phrase). After witing 2 seconds, I restarted the server. It seemed 
to work OK until the backup started.

Perhaps we should restore the database that I suspect cause this, in order to 
rebuild the complete database.

-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

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

Re: Lost connection to MySQL server - need help.

[Reindl Harald]

Am 12.10.2013 17:02, schrieb Jørn Dahl-Stamnes:
 On Saturday 12 October 2013 13:07, Andrew Moore wrote:
 Could be a crash related to innodb data dictionary being out of sync. Could
 be a bug.
 
 Seems like a bug yes. However, we had a strange situation yesterday when we 
 had several processes in the state copying to tmp table (if i remember the 
 exact phrase). After witing 2 seconds, I restarted the server. It seemed 
 to work OK until the backup started

so someone did optimize table on a large table
you do yourself not a favour restarting the server in such a moment



signature.asc
Description: OpenPGP digital signature

Re: Lost connection to MySQL server - need help.

[Jørn Dahl-Stamnes]

On Saturday 12 October 2013 17:36, Reindl Harald wrote:
 so someone did optimize table on a large table
 you do yourself not a favour restarting the server in such a moment

7 hours before the server was shut down, we did a alter table to add a primary 
key to a table that is read-only from the web application.

-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

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

Re: Lost connection to MySQL server - need help.

[Reindl Harald]

Am 12.10.2013 19:45, schrieb Jørn Dahl-Stamnes:
 On Saturday 12 October 2013 17:36, Reindl Harald wrote:
 so someone did optimize table on a large table
 you do yourself not a favour restarting the server in such a moment
 
 7 hours before the server was shut down, we did a alter table to add a 
 primary 
 key to a table that is read-only from the web application.

which means the table is most likely completly copied
in a temp file and depending on the table size this
takes time - you killed the alter table i guess



signature.asc
Description: OpenPGP digital signature

Re: Lost connection to MySQL server - need help.

[Chris McKeever]

We had a similar issue a bit back - and although it sounds similar - based
on your followups it probably isnt, but will just toss this out there
anyhows.  We were experiencing connection timeouts when load would ramp up.
 Doing some digging we learned that our firewall between the servers
bandwidth would get consumed by a large wordpress load - and this in
essence backed up the rest of the requests until they timed out.

We fixed that load issue which reduced the data passing through and have
expereinced a significant performance boost in our app let alone reduction
of these timeout issues




On Sat, Oct 12, 2013 at 12:56 PM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 12.10.2013 19:45, schrieb Jørn Dahl-Stamnes:
  On Saturday 12 October 2013 17:36, Reindl Harald wrote:
  so someone did optimize table on a large table
  you do yourself not a favour restarting the server in such a moment
 
  7 hours before the server was shut down, we did a alter table to add a
 primary
  key to a table that is read-only from the web application.

 which means the table is most likely completly copied
 in a temp file and depending on the table size this
 takes time - you killed the alter table i guess

Re: Lost connection to MySQL server - need help.

[Reindl Harald]

sounds like a scheduler issue

did you try deadline?
http://en.wikipedia.org/wiki/Deadline_scheduler

on Linux systems pass elevator=deadline as kernel param

Am 12.10.2013 20:58, schrieb Chris McKeever:
 We had a similar issue a bit back - and although it sounds similar - based
 on your followups it probably isnt, but will just toss this out there
 anyhows.  We were experiencing connection timeouts when load would ramp up.
  Doing some digging we learned that our firewall between the servers
 bandwidth would get consumed by a large wordpress load - and this in
 essence backed up the rest of the requests until they timed out.
 
 We fixed that load issue which reduced the data passing through and have
 expereinced a significant performance boost in our app let alone reduction
 of these timeout issues
 
 On Sat, Oct 12, 2013 at 12:56 PM, Reindl Harald h.rei...@thelounge.netwrote:
 


 Am 12.10.2013 19:45, schrieb Jørn Dahl-Stamnes:
 On Saturday 12 October 2013 17:36, Reindl Harald wrote:
 so someone did optimize table on a large table
 you do yourself not a favour restarting the server in such a moment

 7 hours before the server was shut down, we did a alter table to add a
 primary
 key to a table that is read-only from the web application.

 which means the table is most likely completly copied
 in a temp file and depending on the table size this
 takes time - you killed the alter table i guess




signature.asc
Description: OpenPGP digital signature

Re: Need Help Converting Character Sets

[hsv]

 2012/09/30 11:07 -0700, Mark Phillips 
The data for this table comes from a web page (charet utf8). I copy/paste word 
files into gedit (on linux) and then copy/paste from gedit to a text boxes on 
the web page input form. I had thought I was stripping out all the funky 
characters by using a simple ascii editor like gedit, but obviously not.

After looking at the mysqldump for the table in a hex editor, I discovered I 
have these characters scatter throughout the body and intro columns:
#8220;
#8221; 
#8217;
#8212;
#8230;
#8617;

How do you mean this? Is there an instance of Unicode character
LEFT DOUBLE QUOTATION MARK, or else of the string #8220;?

In any case, this sounds like not an SQL, but general-programming problem; 
furthermore, I suggest that you carefully select a left double quotation mark 
or any of the other punctuation characters (RIGHT DOUBLE QUOTATION MARK, RIGHT 
SINGLE QUOTATION MARK, EM DASH, HORIZONTAL ELLIPSIS, ...) that, it seems, you 
are getting and not liking, and carefully follow your procedure.

It further seems to me that you really do not want that to be UTF-8 string, but 
ASCII, or Latin1.


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

RE: Need Help Converting Character Sets

[Rick James]

(Sorry; these are random notes, not well structured for your questions.)

#...; -- This is an html entity.  It is plain ascii, but _represents_ (in an 
HTML contect) some special character.  Some details:
http://htmltimes.com/html-special-characters.php
http://www.dwheeler.com/essays/quotes-in-html.html

That is, $#...; is not a character set issue in MySQL.  Most CHARACTER SETs 
(latin1, utf8, etc) will handle them the same.  And they will continue to 
render on an HTML page as the 'funky characters'.  That's HTML doing the deed 
in the browser.

The ALTERs did nothing because there was nothing to do.

I don't know what process converted from '' to #8220;.

The utf8 equivalent:
http://www.fileformat.info/info/unicode/char/201c/index.htm
It would be hex e2809c (3 bytes).

The correct way is to get the utf8 encoding from the form, and store that 
in a utf8-declared column in the table.

If you already have utf8 bytes in your latin1 fields, it gets messier.

hex 22 is the 'straight' ascii double quote.  8220 and 8221 are the curved 
double quotes.  A common convention is to translate both curved ones to the 
single straight one.  But that would be work for you to explicitly do.

If you are using PHP, see functions htmlentities() and html_entity_decode().



 -Original Message-
 From: Mark Phillips [mailto:m...@phillipsmarketing.biz]
 Sent: Sunday, September 30, 2012 11:08 AM
 To: Rick James
 Cc: h...@tbbs.net; Mysql List
 Subject: Re: Need Help Converting Character Sets
 
 Thanks to all of you for your very helpful suggestions! I was pulled
 off on a different project for a few days, and I am now just getting
 back to this one. Sorry for my absence.
 
 First, some more information:
 
 My table definition:
 CREATE TABLE `Articles` (
 `articleID` int(11) unsigned NOT NULL AUTO_INCREMENT, `title`
 varchar(200) NOT NULL DEFAULT '', `author` varchar(200) NOT NULL
 DEFAULT '', `body` text NOT NULL, `intro` text NOT NULL, `caption` text
 NOT NULL, `credits` text NOT NULL, `articleDate` date NOT NULL DEFAULT
 '-00-00', `imageTitle` varchar(255) NOT NULL DEFAULT '',
 `imageAltText` varchar(255) NOT NULL DEFAULT '', `imageWidth` float NOT
 NULL DEFAULT '1', `imageHeight` float NOT NULL DEFAULT '1', `imageFile`
 varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`articleID`), FULLTEXT
 KEY `search1` (`title`,`author`,`body`,`caption`,`credits`)
 ) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1
 
 First problem - CHARSET should be utf8.
 
 The data for this table comes from a web page (charet utf8). I
 copy/paste word files into gedit (on linux) and then copy/paste from
 gedit to a text boxes on the web page input form. I had thought I was
 stripping out all the funky characters by using a simple ascii editor
 like gedit, but obviously not.
 
 After looking at the mysqldump for the table in a hex editor, I
 discovered I have these characters scatter throughout the body and
 intro columns:
 #8220;
 #8221;
 #8217;
 #8212;
 #8230;
 #8617;
 
 I tried converting the columns into utf8 with the command ALTER TABLE
 table_name CONVERT TO CHARACTER SET utf8; but all the special
 characters are still there.
 
 I tried converting to blob and back to utf8, and that didn't change
 anything. (I had to first drop the fulltext key to convert to blob).
 ALTER TABLE t1 CHANGE c1 c1 BLOB;
 ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8; The above special
 characters are still in the body column when I view the dump file with
 a hex editor.
 
 Is there a way to replace these special characters with the appropriate
 utf8 characters (eg #8220; - 22 hex) within the text columns body and
 intro columns?
 
 Thanks,
 
 Mark
 
 On Fri, Sep 28, 2012 at 7:59 AM, Rick James rja...@yahoo-inc.com
 wrote:
 
  Thanks for that link!  That's another subtle issue I had not noted.
 
  There are so many combinations, that it is hard to say do this:
  * Incoming bytes are latin1 / utf8 / Microsquish control characters.
  * You do/don't have SET NAMES (or equivalent)
  * The database/table/column is declared latin1/utf8/other.
  * The problem is on ingestion / on retrieval.
 
  The thing mentioned involved 2 steps:
  ALTER TABLE ... MODIFY COLUMN  BINARY (or BLOB);  -- to forget any
  charset knowledge ALTER TABLE ... MODIFY COLUMN  CHARACTER SET ...;
  -- coming from BINARY, this does not check the encoding.
  (sorry, don't have the link handy)
 
   -Original Message-
   From: h...@tbbs.net [mailto:h...@tbbs.net]
   Sent: Thursday, September 27, 2012 2:24 PM
   To: Mark Phillips
   Cc: Mysql List
   Subject: Re: Need Help Converting Character Sets
  
2012/09/24 16:28 -0700, Mark Phillips 
   I have a table, Articles, of news articles (in English) with three
   text columns for the intro, body, and caption. The data came from a
   web page, and the content was cut and pasted from other sources. I
   am finding that there are some non utf-8 characters in these three
   text columns. I would like to (1) convert these text fields

Re: Need Help Converting Character Sets

[Mark Phillips]

Thanks to all of you for your very helpful suggestions! I was pulled off on
a different project for a few days, and I am now just getting back to this
one. Sorry for my absence.

First, some more information:

My table definition:
CREATE TABLE `Articles` (
`articleID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL DEFAULT '',
`author` varchar(200) NOT NULL DEFAULT '',
`body` text NOT NULL,
`intro` text NOT NULL,
`caption` text NOT NULL,
`credits` text NOT NULL,
`articleDate` date NOT NULL DEFAULT '-00-00',
`imageTitle` varchar(255) NOT NULL DEFAULT '',
`imageAltText` varchar(255) NOT NULL DEFAULT '',
`imageWidth` float NOT NULL DEFAULT '1',
`imageHeight` float NOT NULL DEFAULT '1',
`imageFile` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`articleID`),
FULLTEXT KEY `search1` (`title`,`author`,`body`,`caption`,`credits`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1

First problem - CHARSET should be utf8.

The data for this table comes from a web page (charet utf8). I copy/paste
word files into gedit (on linux) and then copy/paste from gedit to a text
boxes on the web page input form. I had thought I was stripping out all the
funky characters by using a simple ascii editor like gedit, but obviously
not.

After looking at the mysqldump for the table in a hex editor, I discovered
I have these characters scatter throughout the body and intro columns:
#8220;
#8221;
#8217;
#8212;
#8230;
#8617;

I tried converting the columns into utf8 with the command
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;
but all the special characters are still there.

I tried converting to blob and back to utf8, and that didn't change
anything. (I had to first drop the fulltext key to convert to blob).
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The above special characters are still in the body column when I view the
dump file with a hex editor.

Is there a way to replace these special characters with the appropriate
utf8 characters (eg #8220; - 22 hex) within the text columns body and
intro columns?

Thanks,

Mark

On Fri, Sep 28, 2012 at 7:59 AM, Rick James rja...@yahoo-inc.com wrote:

 Thanks for that link!  That's another subtle issue I had not noted.

 There are so many combinations, that it is hard to say do this:
 * Incoming bytes are latin1 / utf8 / Microsquish control characters.
 * You do/don't have SET NAMES (or equivalent)
 * The database/table/column is declared latin1/utf8/other.
 * The problem is on ingestion / on retrieval.

 The thing mentioned involved 2 steps:
 ALTER TABLE ... MODIFY COLUMN  BINARY (or BLOB);  -- to forget any charset
 knowledge
 ALTER TABLE ... MODIFY COLUMN  CHARACTER SET ...;  -- coming from BINARY,
 this does not check the encoding.
 (sorry, don't have the link handy)

  -Original Message-
  From: h...@tbbs.net [mailto:h...@tbbs.net]
  Sent: Thursday, September 27, 2012 2:24 PM
  To: Mark Phillips
  Cc: Mysql List
  Subject: Re: Need Help Converting Character Sets
 
   2012/09/24 16:28 -0700, Mark Phillips 
  I have a table, Articles, of news articles (in English) with three text
  columns for the intro, body, and caption. The data came from a web
  page, and the content was cut and pasted from other sources. I am
  finding that there are some non utf-8 characters in these three text
  columns. I would like to (1) convert these text fields to be strict
  utf-8 and then (2) fix the input page to keep all new submissions utf-
  8.
 
  91) For the first step, fixing the current database, I tried:
 
  update Articles set body = CONVERT(body USING ASCII);
 
  However, when I checked one of the articles I found an apostrophe had
  been converted into a question mark. (FWIW, the apostrophe was one of
  those offending non utf-8 characters):
 
  Before conversion: I stepped into the observatory?s control room ...
 
  After conversion: I stepped into the observatory?s control room...
 
  Is there a better way to accomplish my first goal, without reading each
  article and manually making the changes?
  
  I do not remember where on the MySQL website this is, but there was an
  article about converting from character sets in version 4 to those in
  version 5, when UTF-8 first was supported. It sounds to me that maybe
  the tricks shown there would be useful to you, since, in effect,
  through MySQL MySQL was fooled into accepting for UTF-8 that which was
  not. Conversion to binary string was mentioned.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql

RE: Need Help Converting Character Sets

[Rick James]

Thanks for that link!  That's another subtle issue I had not noted.

There are so many combinations, that it is hard to say do this:
* Incoming bytes are latin1 / utf8 / Microsquish control characters.
* You do/don't have SET NAMES (or equivalent)
* The database/table/column is declared latin1/utf8/other.
* The problem is on ingestion / on retrieval.

The thing mentioned involved 2 steps:
ALTER TABLE ... MODIFY COLUMN  BINARY (or BLOB);  -- to forget any charset 
knowledge
ALTER TABLE ... MODIFY COLUMN  CHARACTER SET ...;  -- coming from BINARY, this 
does not check the encoding.
(sorry, don't have the link handy)

 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Thursday, September 27, 2012 2:24 PM
 To: Mark Phillips
 Cc: Mysql List
 Subject: Re: Need Help Converting Character Sets
 
  2012/09/24 16:28 -0700, Mark Phillips 
 I have a table, Articles, of news articles (in English) with three text
 columns for the intro, body, and caption. The data came from a web
 page, and the content was cut and pasted from other sources. I am
 finding that there are some non utf-8 characters in these three text
 columns. I would like to (1) convert these text fields to be strict
 utf-8 and then (2) fix the input page to keep all new submissions utf-
 8.
 
 91) For the first step, fixing the current database, I tried:
 
 update Articles set body = CONVERT(body USING ASCII);
 
 However, when I checked one of the articles I found an apostrophe had
 been converted into a question mark. (FWIW, the apostrophe was one of
 those offending non utf-8 characters):
 
 Before conversion: I stepped into the observatory?s control room ...
 
 After conversion: I stepped into the observatory?s control room...
 
 Is there a better way to accomplish my first goal, without reading each
 article and manually making the changes?
 
 I do not remember where on the MySQL website this is, but there was an
 article about converting from character sets in version 4 to those in
 version 5, when UTF-8 first was supported. It sounds to me that maybe
 the tricks shown there would be useful to you, since, in effect,
 through MySQL MySQL was fooled into accepting for UTF-8 that which was
 not. Conversion to binary string was mentioned.
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

Re: Need Help Converting Character Sets

[hsv]

 2012/09/24 16:28 -0700, Mark Phillips 
I have a table, Articles, of news articles (in English) with three text
columns for the intro, body, and caption. The data came from a web page,
and the content was cut and pasted from other sources. I am finding that
there are some non utf-8 characters in these three text columns. I would
like to (1) convert these text fields to be strict utf-8 and then (2) fix
the input page to keep all new submissions utf-8.

91) For the first step, fixing the current database, I tried:

update Articles set body = CONVERT(body USING ASCII);

However, when I checked one of the articles I found an apostrophe had been
converted into a question mark. (FWIW, the apostrophe was one of those
offending non utf-8 characters):

Before conversion: I stepped into the observatory’s control room ...

After conversion: I stepped into the observatory?s control room...

Is there a better way to accomplish my first goal, without reading each
article and manually making the changes? 

I do not remember where on the MySQL website this is, but there was an article 
about converting from character sets in version 4 to those in version 5, when 
UTF-8 first was supported. It sounds to me that maybe the tricks shown there 
would be useful to you, since, in effect, through MySQL MySQL was fooled into 
accepting for UTF-8 that which was not. Conversion to binary string was 
mentioned.


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

Re: Need Help Converting Character Sets

[Derek Downey]

To go along with what Rick is saying, this link might help you: 
http://dba.stackexchange.com/questions/10467/how-to-convert-control-characters-in-mysql-from-latin1-to-utf-8

I remember doing a bunch of converting HEX() control characters (such as an 
apostrophe copied from a Word document) before attempting the SET NAMES.

Derek Downey


On Sep 24, 2012, at 7:53 PM, Rick James wrote:

 If you have a mixture of encodings, you are in deep doodoo.
 
 This page describes some debugging techniques and some issues:
   http://mysql.rjweb.org/doc.php/charcoll
 
 That apostrophe might be MicroSquish's smart quote.
 
 Can you provide SELECT HEX(the_field) FROM... ?  We (or the above page) might 
 be able to interpret the character.
 
 To prevent future char set issues, you must know what encoding the source is. 
  Then, with SET NAMES (etc), you tell mysqld that the bytes you have in hand 
 are encoded that way.  mysqld will then convert those bytes to the character 
 set of declared for the column they go in.  (Presumably, all the text columns 
 will be declared utf8 or utf8mb4.)
 
 -Original Message-
 From: Mark Phillips [mailto:m...@phillipsmarketing.biz]
 Sent: Monday, September 24, 2012 4:28 PM
 To: Mysql List
 Subject: Need Help Converting Character Sets
 
 I have a table, Articles, of news articles (in English) with three text
 columns for the intro, body, and caption. The data came from a web
 page, and the content was cut and pasted from other sources. I am
 finding that there are some non utf-8 characters in these three text
 columns. I would like to (1) convert these text fields to be strict
 utf-8 and then (2) fix the input page to keep all new submissions utf-
 8.
 
 91) For the first step, fixing the current database, I tried:
 
 update Articles set body = CONVERT(body USING ASCII);
 
 However, when I checked one of the articles I found an apostrophe had
 been converted into a question mark. (FWIW, the apostrophe was one of
 those offending non utf-8 characters):
 
 Before conversion: I stepped into the observatory's control room ...
 
 After conversion: I stepped into the observatory?s control room...
 
 Is there a better way to accomplish my first goal, without reading each
 article and manually making the changes?
 
 (2) For the second goal, insuring that all future articles are utf-8,
 do I need to change the table structure or the insert query to insure I
 get the correct utf-8 characters into the database?
 
 Thanks,
 
 Mark
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 

Need Help Converting Character Sets

[Mark Phillips]

I have a table, Articles, of news articles (in English) with three text
columns for the intro, body, and caption. The data came from a web page,
and the content was cut and pasted from other sources. I am finding that
there are some non utf-8 characters in these three text columns. I would
like to (1) convert these text fields to be strict utf-8 and then (2) fix
the input page to keep all new submissions utf-8.

91) For the first step, fixing the current database, I tried:

update Articles set body = CONVERT(body USING ASCII);

However, when I checked one of the articles I found an apostrophe had been
converted into a question mark. (FWIW, the apostrophe was one of those
offending non utf-8 characters):

Before conversion: I stepped into the observatory’s control room ...

After conversion: I stepped into the observatory?s control room...

Is there a better way to accomplish my first goal, without reading each
article and manually making the changes?

(2) For the second goal, insuring that all future articles are utf-8, do I
need to change the table structure or the insert query to insure I get the
correct utf-8 characters into the database?

Thanks,

Mark

RE: Need Help Converting Character Sets

[Rick James]

If you have a mixture of encodings, you are in deep doodoo.

This page describes some debugging techniques and some issues:
   http://mysql.rjweb.org/doc.php/charcoll

That apostrophe might be MicroSquish's smart quote.

Can you provide SELECT HEX(the_field) FROM... ?  We (or the above page) might 
be able to interpret the character.

To prevent future char set issues, you must know what encoding the source is.  
Then, with SET NAMES (etc), you tell mysqld that the bytes you have in hand are 
encoded that way.  mysqld will then convert those bytes to the character set of 
declared for the column they go in.  (Presumably, all the text columns will be 
declared utf8 or utf8mb4.)

 -Original Message-
 From: Mark Phillips [mailto:m...@phillipsmarketing.biz]
 Sent: Monday, September 24, 2012 4:28 PM
 To: Mysql List
 Subject: Need Help Converting Character Sets
 
 I have a table, Articles, of news articles (in English) with three text
 columns for the intro, body, and caption. The data came from a web
 page, and the content was cut and pasted from other sources. I am
 finding that there are some non utf-8 characters in these three text
 columns. I would like to (1) convert these text fields to be strict
 utf-8 and then (2) fix the input page to keep all new submissions utf-
 8.
 
 91) For the first step, fixing the current database, I tried:
 
 update Articles set body = CONVERT(body USING ASCII);
 
 However, when I checked one of the articles I found an apostrophe had
 been converted into a question mark. (FWIW, the apostrophe was one of
 those offending non utf-8 characters):
 
 Before conversion: I stepped into the observatory's control room ...
 
 After conversion: I stepped into the observatory?s control room...
 
 Is there a better way to accomplish my first goal, without reading each
 article and manually making the changes?
 
 (2) For the second goal, insuring that all future articles are utf-8,
 do I need to change the table structure or the insert query to insure I
 get the correct utf-8 characters into the database?
 
 Thanks,
 
 Mark

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

Re: Need help for performance tuning with Mysql

[Prabhat Kumar]

Check performance with script :

http://mysqltuner.pl/mysqltuner.pl - Variable tunning
http://hackmysql.com/mysqlidxchk - Unused Index

Thanks,
Prabhat

On Thu, May 24, 2012 at 4:32 PM, Rick James rja...@yahoo-inc.com wrote:

 Thanks.  I got tired of answering the same questions about buffer_pool and
 key_buffer over and over on forums.mysql.com; now I just point people at
 that page.

 INT(1) and INT(50) are identical -- and take 4 bytes.  See TINYINT,
 BIGINT, etc.  Also, UNSIGNED is probably wanted in more places than you
 have it.

 555KB is not very big.  But a table scan (as indicated by the EXPLAIN)
 costs something.

 select  * -- Is this what you are really fetching?  If not, we
 can discuss a covering index.
from  thold_data
where  thold_enabled='on' -- 2-valued flag?  Not likely to be useful in
 an index, esp. not by itself
  AND  data_id = 91633;   -- Probably the best bet.

 Recommend:
 INDEX(data_id)  -- or UNIQUE, if it is unique
 INDEX(data_id, thold_enabled)  -- or the opposite order; this probably
 would not be noticeable better.

 `notify_default` enum('on','off') default NULL
 Did you really mean to have 3 values (on, off, NULL)?



  -Original Message-
  From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
   Sent: Wednesday, May 23, 2012 7:10 PM
  To: mysql@lists.mysql.com
  Subject: Re: Need help for performance tuning with Mysql
 
  Rick
 
  Thank you for the reply.
 
  1. There are only a few things worth tuning -- see
  http://mysql.rjweb.org/doc.php/memory (they don't include the ones you
  tried)
  
 
The page is really cool. Its very simple and easy to understand.
 
  2. Instead INDEXes and schema design must be studied.  Please provide:
  SHOW CREATE TABLE
 
| thold_data | CREATE TABLE `thold_data` (
  `id` int(11) NOT NULL auto_increment,
  `rra_id` int(11) NOT NULL default '0',
  `data_id` int(11) NOT NULL default '0',
  `thold_hi` varchar(100) default NULL,
  `thold_low` varchar(100) default NULL,
  `thold_fail_trigger` int(10) unsigned default NULL,
  `thold_fail_count` int(11) NOT NULL default '0',
  `thold_alert` int(1) NOT NULL default '0',
  `thold_enabled` enum('on','off') NOT NULL default 'on',
  `bl_enabled` enum('on','off') NOT NULL default 'off',
  `bl_ref_time` int(50) unsigned default NULL,
  `bl_ref_time_range` int(10) unsigned default NULL,
  `bl_pct_down` int(10) unsigned default NULL,
  `bl_pct_up` int(10) unsigned default NULL,
  `bl_fail_trigger` int(10) unsigned default NULL,
  `bl_fail_count` int(11) unsigned default NULL,
  `bl_alert` int(2) NOT NULL default '0',
  `lastread` varchar(100) default NULL,
  `oldvalue` varchar(100) NOT NULL default '',
  `repeat_alert` int(10) unsigned default NULL,
  `notify_default` enum('on','off') default NULL,
  `notify_extra` varchar(255) default NULL,
  `host_id` int(10) default NULL,
  `syslog_priority` int(2) default '3',
  `cdef` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `rra_id` (`rra_id`)
) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |
 
  SHOW TABLE SIZE
 
You must be mentioning about the show table status
 
  mysql show table status where name = thold_data;
  +++-++--++-
  +-+--+---+-
  ---+-+-
  +-+---+--+
  +-+
  | 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 |
  +++-++--++-
  +-+--+---+-
  ---+-+-
  +-+---+--+
  +-+
  | thold_data | MyISAM |  10 | Dynamic| 6161 | 90 |
  555128 | 281474976710655 |   140288 | 0 |  70258 |
  2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24
  10:41:47 | latin1_swedish_ci | NULL || |
  +++-++--++-
  +-+--+---+-
  ---+-+-
  +-+---+--+
  +-+
  1 row in set (0.00 sec)
 
  EXPLAIN SELECT
 
I have seen the following select query in the slow query log.
I also saw update queries as well.
 
  mysql explain select * from thold_data where thold_enabled='on' AND
  mysql data_id = 91633

Re: Need help for performance tuning with Mysql

[Yu Watanabe]

Alex

Thank you for the advice.

Probably, we will put index (key) on both columns.

Thanks,
Yu

Alex Schaft さんは書きました:
On 2012/05/24 07:37, Alex Schaft wrote:
 You are selecting a record based on the value of data_id and
 thold_enabled, but don't have an index on either? Add an index for both.
 If data_id is unique, then you would only need an index on that.

 Alex


On second thought, an index on thold_enabled won't mean much I think, so
either leave it off or create an index on data_id plus thold_enabled.
Someone more knowledgeable may correct me.

Alex


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




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

Re: Need help for performance tuning with Mysql

[Yu Watanabe]

Johnny

Thank you for the reply.

Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give you the best
performance increase.

  The problem is mainly on MyISAM engine.

Also, what kind of hard disks do you have the data files on? Raid? No raid?

  The server has no RAID.

Thanks,
Yu

Johnny Withers さんは書きました:
I don't see any attachments.

First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process
is painless.

Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give you the best
performance increase.

Also, what kind of hard disks do you have the data files on? Raid? No raid?

Sent from my iPad

On May 22, 2012, at 9:08 PM, Yu Watanabe yu.watan...@jp.fujitsu.com wrote:

 Hello all.

 I would like to ask for advice with performance tuning with MySQL.

 Following are some data for my server.

 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1-enterprise

 Attached file
 # my.cnf.txt  : my.cnf information
 # mysqlext_20120522131034.log : variable and status information from 
 mysqladmin

 I have 2 database working with high load.

 I wanted to speed up my select and update queries not by
 optimizing the query itself but tuning the my.cnf.

 I have referred to following site,
 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

 and read Hiperformance Mysql vol.2 ,
 and increased the following values,

 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size

 but made not much difference.

 According to the ps and sar result

 *1 PS result
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028

 *2 SAR
 Average CPU user 25%
sys  5%
io   3%

 I assume that MySQL can work more but currently not.

 I am considersing to off load 1 high load database to
 seperate process and make MySQL work in multiple process.

 It would be a great help if people in this forum can give
 us an adivice for the tuning.

 Best Regards,
 Yu Watanabe


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

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




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

RE: Need help for performance tuning with Mysql

[Rick James]

Thanks.  I got tired of answering the same questions about buffer_pool and 
key_buffer over and over on forums.mysql.com; now I just point people at that 
page.

INT(1) and INT(50) are identical -- and take 4 bytes.  See TINYINT, BIGINT, 
etc.  Also, UNSIGNED is probably wanted in more places than you have it.

555KB is not very big.  But a table scan (as indicated by the EXPLAIN) costs 
something.

select  * -- Is this what you are really fetching?  If not, we can 
discuss a covering index.
from  thold_data
where  thold_enabled='on' -- 2-valued flag?  Not likely to be useful in an 
index, esp. not by itself
  AND  data_id = 91633;   -- Probably the best bet.

Recommend:
INDEX(data_id)  -- or UNIQUE, if it is unique
INDEX(data_id, thold_enabled)  -- or the opposite order; this probably would 
not be noticeable better.

`notify_default` enum('on','off') default NULL
Did you really mean to have 3 values (on, off, NULL)?



 -Original Message-
 From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
 Sent: Wednesday, May 23, 2012 7:10 PM
 To: mysql@lists.mysql.com
 Subject: Re: Need help for performance tuning with Mysql
 
 Rick
 
 Thank you for the reply.
 
 1. There are only a few things worth tuning -- see
 http://mysql.rjweb.org/doc.php/memory (they don't include the ones you
 tried)
 
 
   The page is really cool. Its very simple and easy to understand.
 
 2. Instead INDEXes and schema design must be studied.  Please provide:
 SHOW CREATE TABLE
 
   | thold_data | CREATE TABLE `thold_data` (
 `id` int(11) NOT NULL auto_increment,
 `rra_id` int(11) NOT NULL default '0',
 `data_id` int(11) NOT NULL default '0',
 `thold_hi` varchar(100) default NULL,
 `thold_low` varchar(100) default NULL,
 `thold_fail_trigger` int(10) unsigned default NULL,
 `thold_fail_count` int(11) NOT NULL default '0',
 `thold_alert` int(1) NOT NULL default '0',
 `thold_enabled` enum('on','off') NOT NULL default 'on',
 `bl_enabled` enum('on','off') NOT NULL default 'off',
 `bl_ref_time` int(50) unsigned default NULL,
 `bl_ref_time_range` int(10) unsigned default NULL,
 `bl_pct_down` int(10) unsigned default NULL,
 `bl_pct_up` int(10) unsigned default NULL,
 `bl_fail_trigger` int(10) unsigned default NULL,
 `bl_fail_count` int(11) unsigned default NULL,
 `bl_alert` int(2) NOT NULL default '0',
 `lastread` varchar(100) default NULL,
 `oldvalue` varchar(100) NOT NULL default '',
 `repeat_alert` int(10) unsigned default NULL,
 `notify_default` enum('on','off') default NULL,
 `notify_extra` varchar(255) default NULL,
 `host_id` int(10) default NULL,
 `syslog_priority` int(2) default '3',
 `cdef` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `rra_id` (`rra_id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |
 
 SHOW TABLE SIZE
 
   You must be mentioning about the show table status
 
 mysql show table status where name = thold_data;
 +++-++--++-
 +-+--+---+-
 ---+-+-
 +-+---+--+
 +-+
 | 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 |
 +++-++--++-
 +-+--+---+-
 ---+-+-
 +-+---+--+
 +-+
 | thold_data | MyISAM |  10 | Dynamic| 6161 | 90 |
 555128 | 281474976710655 |   140288 | 0 |  70258 |
 2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24
 10:41:47 | latin1_swedish_ci | NULL || |
 +++-++--++-
 +-+--+---+-
 ---+-+-
 +-+---+--+
 +-+
 1 row in set (0.00 sec)
 
 EXPLAIN SELECT
 
   I have seen the following select query in the slow query log.
   I also saw update queries as well.
 
 mysql explain select * from thold_data where thold_enabled='on' AND
 mysql data_id = 91633;
 ++-++--+---+--+
 -+--+--+-+
 | id | select_type | table  | type | possible_keys | key  | key_len
 | ref  | rows | Extra   |
 ++-++--+---+--+
 -+--+--+-+
 |  1 | SIMPLE  | thold_data | ALL  | NULL

Re: Need help for performance tuning with Mysql

[Andrew Moore]

Yu,

The upgrade to 5.5 that Jonny advises should NOT your first action. If
MySQL is mis-configured on 5.0 it will likely be misconfigured on 5.1 and
5.5. Test your application thoroughly on the new version before heeding
that advice. Read the change logs and known bugs. Running the upgrade might
seem painless but if you have some legacy feature in place then things will
not work how you may expect them to.

Review your needs and see if a switch to innodb storage engine will give
you any performance gain. The locking differences alone might make this
worthwhile. TEST it.

You did not state your data and index size. You will benefit from having
enough RAM so that your 'working' data set fits to memory. This isn't
possible/practical for large data but if you have a 5G dataset and 8G
available memory you might not need to rush out and spend money.

If you're heavily using MyISAM, review and tune the MyISAM related buffers.
If you are working mostly with InnoDB tune those variables. Measure, change
measure again. It might be an iterative process but you will learn lots
along the way.

Good luck.

Andy

On Wed, May 23, 2012 at 5:44 AM, Tsubasa Tanaka yoku0...@gmail.com wrote:

 Hello, Yu-san,
 (へろへろな英語で申し訳ないです)

 Can I think that you already tweaked Index on the tables?
 if you yet,please create apt indexes.

 MyISAM caches only Index without data.
 i take way for decreasing disk seek,
  1) create more indexes on the tables,if the tables doesn't update quite
 often.
   including data into index forcibly.
   this makes slow for insert and update,and this is dirty idea,i think.
(よくSELECTされるカラムをINDEXに含めてしまいます。
ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に
かなり美しくない策です。。)
  2) tune filesystem and disk drive parameter for datadir.
   MyISAM table's data caches only in the filesystem cache.
   But i regret that i don't have knowledge around filesystem.

 あまり力になれなくて申し訳ないです。

 regards,


 ts. tanaka//

 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
  Hello Tsubasa.
 
  Thank you for the reply. (返信ありがとうございます。)
 
  Our high loaded DB are both INNODB and MyISAM.
  Espicially , on MyISAM.
 
  I will consider the tuning of innodb_buffer_pool_size as well.
 
  Do you know the tips for how to tune the disk access for MyISAM?
 
  Thanks,
  Yu
 
  Tsubasa Tanaka さんは書きました:
 Hello,
 
 I seem your mysqld doesn't use enough memory.
 
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028
 
 if your mysqld uses InnoDB oftenly,
 edit innodb_buffer_pool_size in you my.cnf.
 
 
 http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
 
 
 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size
 
 but made not much difference.
 
 It is solution for only sql's large result,i think.
 if you doesn't recognize that problem causes large result,
 you should approach other way,too.
 
 regards,
 
 
 ts. tanaka//
 
 2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
  Also following is the free command result.
 
  total   used   free sharedbuffers
 cached
  Mem:   81623807843676 318704  0  95632
  5970892
  -/+ buffers/cache:17771526385228
  Swap:  8032492  235608008932
 
  Thanks,
  Yu
 
 
  Yu Watanabe さんは書きました:
 Hello all.
 
 I would like to ask for advice with performance tuning with MySQL.
 
 Following are some data for my server.
 
 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1-enterprise
 
 Attached file
 # my.cnf.txt  : my.cnf information
 # mysqlext_20120522131034.log : variable and status information from
 mysqladmin
 
 I have 2 database working with high load.
 
 I wanted to speed up my select and update queries not by
 optimizing the query itself but tuning the my.cnf.
 
 I have referred to following site,
 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
 
 and read Hiperformance Mysql vol.2 ,
 and increased the following values,
 
 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size
 
 but made not much difference.
 
 According to the ps and sar result
 
 *1 PS result
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028
 
 *2 SAR
 Average CPU user 25%
 sys  5%
 io   3%
 
 I assume that MySQL can work more but currently not.
 
 I am considersing to off load 1 high load database to
 seperate process and make MySQL work in multiple process.
 
 It would be a great help if people in this forum can give
 us an adivice for the tuning.
 
 Best Regards,
 Yu Watanabe
 
 __
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 --
 

Re: Need help for performance tuning with Mysql

[Ananda Kumar]

Hi,
How much ever tuning you do at my.cnf will not help much, if you do not
tune your sql's.

Your first priority should be tune sql's, which will give you good
performance even with decent memory allocations and other settings

regards
anandkl

On Wed, May 23, 2012 at 3:45 PM, Andrew Moore eroomy...@gmail.com wrote:

 Yu,

 The upgrade to 5.5 that Jonny advises should NOT your first action. If
 MySQL is mis-configured on 5.0 it will likely be misconfigured on 5.1 and
 5.5. Test your application thoroughly on the new version before heeding
 that advice. Read the change logs and known bugs. Running the upgrade might
 seem painless but if you have some legacy feature in place then things will
 not work how you may expect them to.

 Review your needs and see if a switch to innodb storage engine will give
 you any performance gain. The locking differences alone might make this
 worthwhile. TEST it.

 You did not state your data and index size. You will benefit from having
 enough RAM so that your 'working' data set fits to memory. This isn't
 possible/practical for large data but if you have a 5G dataset and 8G
 available memory you might not need to rush out and spend money.

 If you're heavily using MyISAM, review and tune the MyISAM related buffers.
 If you are working mostly with InnoDB tune those variables. Measure, change
 measure again. It might be an iterative process but you will learn lots
 along the way.

 Good luck.

 Andy

 On Wed, May 23, 2012 at 5:44 AM, Tsubasa Tanaka yoku0...@gmail.com
 wrote:

  Hello, Yu-san,
  (へろへろな英語で申し訳ないです)
 
  Can I think that you already tweaked Index on the tables?
  if you yet,please create apt indexes.
 
  MyISAM caches only Index without data.
  i take way for decreasing disk seek,
   1) create more indexes on the tables,if the tables doesn't update quite
  often.
including data into index forcibly.
this makes slow for insert and update,and this is dirty idea,i think.
 (よくSELECTされるカラムをINDEXに含めてしまいます。
 ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に
 かなり美しくない策です。。)
   2) tune filesystem and disk drive parameter for datadir.
MyISAM table's data caches only in the filesystem cache.
But i regret that i don't have knowledge around filesystem.
 
  あまり力になれなくて申し訳ないです。
 
  regards,
 
 
  ts. tanaka//
 
  2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
   Hello Tsubasa.
  
   Thank you for the reply. (返信ありがとうございます。)
  
   Our high loaded DB are both INNODB and MyISAM.
   Espicially , on MyISAM.
  
   I will consider the tuning of innodb_buffer_pool_size as well.
  
   Do you know the tips for how to tune the disk access for MyISAM?
  
   Thanks,
   Yu
  
   Tsubasa Tanaka さんは書きました:
  Hello,
  
  I seem your mysqld doesn't use enough memory.
  
  Date   Time  CPU%  RSS VSZ
  2012/5/22  21:00:39  109   294752  540028
  
  if your mysqld uses InnoDB oftenly,
  edit innodb_buffer_pool_size in you my.cnf.
  
  
 
 http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
  
  
  table_cache
  thread_cache_size
  tmp_table_size
  max_heap_table_size
  
  but made not much difference.
  
  It is solution for only sql's large result,i think.
  if you doesn't recognize that problem causes large result,
  you should approach other way,too.
  
  regards,
  
  
  ts. tanaka//
  
  2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
   Also following is the free command result.
  
   total   used   free sharedbuffers
  cached
   Mem:   81623807843676 318704  0  95632
   5970892
   -/+ buffers/cache:17771526385228
   Swap:  8032492  235608008932
  
   Thanks,
   Yu
  
  
   Yu Watanabe さんは書きました:
  Hello all.
  
  I would like to ask for advice with performance tuning with MySQL.
  
  Following are some data for my server.
  
  CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
  Memory : 8GB
  OS : RHEL 4.4 x86_64
  MySQL  : MySQL 5.0.50sp1-enterprise
  
  Attached file
  # my.cnf.txt  : my.cnf information
  # mysqlext_20120522131034.log : variable and status information from
  mysqladmin
  
  I have 2 database working with high load.
  
  I wanted to speed up my select and update queries not by
  optimizing the query itself but tuning the my.cnf.
  
  I have referred to following site,
  http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
  
  and read Hiperformance Mysql vol.2 ,
  and increased the following values,
  
  table_cache
  thread_cache_size
  tmp_table_size
  max_heap_table_size
  
  but made not much difference.
  
  According to the ps and sar result
  
  *1 PS result
  Date   Time  CPU%  RSS VSZ
  2012/5/22  21:00:39  109   294752  540028
  
  *2 SAR
  Average CPU user 25%
  sys  5%
  io   3%
  
  I assume that MySQL can work more but currently not.
  
  I am considersing to off load 1 high load database to
  seperate process and make MySQL work in multiple process.
  

RE: Need help for performance tuning with Mysql

[Rick James]

100% CPU -- A slow query.  Tuning will not help.  Period.

1. There are only a few things worth tuning -- see 
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)

2. Instead INDEXes and schema design must be studied.  Please provide:
SHOW CREATE TABLE
SHOW TABLE SIZE
EXPLAIN SELECT ...
 

 -Original Message-
 From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
 Sent: Tuesday, May 22, 2012 7:07 PM
 To: mysql@lists.mysql.com
 Subject: Need help for performance tuning with Mysql
 
 Hello all.
 
 I would like to ask for advice with performance tuning with MySQL.
 
 Following are some data for my server.
 
 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1-enterprise
 
 Attached file
 # my.cnf.txt  : my.cnf information
 # mysqlext_20120522131034.log : variable and status information from
 mysqladmin
 
 I have 2 database working with high load.
 
 I wanted to speed up my select and update queries not by optimizing the
 query itself but tuning the my.cnf.
 
 I have referred to following site,
 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
 
 and read Hiperformance Mysql vol.2 ,
 and increased the following values,
 
 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size
 
 but made not much difference.
 
 According to the ps and sar result
 
 *1 PS result
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028
 
 *2 SAR
 Average CPU user 25%
 sys  5%
 io   3%
 
 I assume that MySQL can work more but currently not.
 
 I am considersing to off load 1 high load database to seperate process
 and make MySQL work in multiple process.
 
 It would be a great help if people in this forum can give us an adivice
 for the tuning.
 
 Best Regards,
 Yu Watanabe


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

Re: Need help for performance tuning with Mysql

[Yu Watanabe]

Rick

Thank you for the reply.

1. There are only a few things worth tuning -- see 
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)


  The page is really cool. Its very simple and easy to understand.

2. Instead INDEXes and schema design must be studied.  Please provide:
SHOW CREATE TABLE

  | thold_data | CREATE TABLE `thold_data` (
`id` int(11) NOT NULL auto_increment,
`rra_id` int(11) NOT NULL default '0',
`data_id` int(11) NOT NULL default '0',
`thold_hi` varchar(100) default NULL,
`thold_low` varchar(100) default NULL,
`thold_fail_trigger` int(10) unsigned default NULL,
`thold_fail_count` int(11) NOT NULL default '0',
`thold_alert` int(1) NOT NULL default '0',
`thold_enabled` enum('on','off') NOT NULL default 'on',
`bl_enabled` enum('on','off') NOT NULL default 'off',
`bl_ref_time` int(50) unsigned default NULL,
`bl_ref_time_range` int(10) unsigned default NULL,
`bl_pct_down` int(10) unsigned default NULL,
`bl_pct_up` int(10) unsigned default NULL,
`bl_fail_trigger` int(10) unsigned default NULL,
`bl_fail_count` int(11) unsigned default NULL,
`bl_alert` int(2) NOT NULL default '0',
`lastread` varchar(100) default NULL,
`oldvalue` varchar(100) NOT NULL default '',
`repeat_alert` int(10) unsigned default NULL,
`notify_default` enum('on','off') default NULL,
`notify_extra` varchar(255) default NULL,
`host_id` int(10) default NULL,
`syslog_priority` int(2) default '3',
`cdef` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `rra_id` (`rra_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |

SHOW TABLE SIZE

  You must be mentioning about the show table status

mysql show table status where name = thold_data;
+++-++--++-+-+--+---++-+-
+-+---+--++-+
| 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 |
+++-++--++-+-+--+---++-+-
+-+---+--++-+
| thold_data | MyISAM |  10 | Dynamic| 6161 | 90 |  
555128 | 281474976710655 |   140288 | 0 |  70258 | 
2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24 
10:41:47 | latin1_swedish_ci | NULL || | 
+++-++--++-+-+--+---++-+-
+-+---+--++-+
1 row in set (0.00 sec)

EXPLAIN SELECT

  I have seen the following select query in the slow query log.
  I also saw update queries as well.

mysql explain select * from thold_data where thold_enabled='on' AND data_id = 
91633;
++-++--+---+--+-+--+--+-+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  
| rows | Extra   |
++-++--+---+--+-+--+--+-+
|  1 | SIMPLE  | thold_data | ALL  | NULL  | NULL | NULL| NULL 
| 6161 | Using where | 
++-++--+---+--+-+--+--+-+
1 row in set (0.06 sec)

If cache size tuning is not an option ,
do you think that following action would be an choice to faten the queries 
little bit more?

1. depriving the database and setup as an another process. (multiple mysql 
processes)
2. Move the MYD, MYI, frm to ram disk (/dev/shm)

Thanks,
Yu

Rick James さんは書きました:
100% CPU -- A slow query.  Tuning will not help.  Period.

1. There are only a few things worth tuning -- see 
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)

2. Instead INDEXes and schema design must be studied.  Please provide:
SHOW CREATE TABLE
SHOW TABLE SIZE
EXPLAIN SELECT ...
 

 -Original Message-
 From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
 Sent: Tuesday, May 22, 2012 7:07 PM
 To: mysql@lists.mysql.com
 Subject: Need help for performance tuning with Mysql
 
 Hello all.
 
 I would like to ask for advice with performance tuning with MySQL.
 
 Following are some data for my server.
 
 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1

Re: Need help for performance tuning with Mysql

[Alex Schaft]

On 2012/05/24 04:10, Yu Watanabe wrote:
 2. Instead INDEXes and schema design must be studied.  Please provide:
 SHOW CREATE TABLE
   | thold_data | CREATE TABLE `thold_data` (
 `id` int(11) NOT NULL auto_increment,
 `rra_id` int(11) NOT NULL default '0',
 `data_id` int(11) NOT NULL default '0',
 `thold_hi` varchar(100) default NULL,
 `thold_low` varchar(100) default NULL,
 `thold_fail_trigger` int(10) unsigned default NULL,
 `thold_fail_count` int(11) NOT NULL default '0',
 `thold_alert` int(1) NOT NULL default '0',
 `thold_enabled` enum('on','off') NOT NULL default 'on',
 `bl_enabled` enum('on','off') NOT NULL default 'off',
 `bl_ref_time` int(50) unsigned default NULL,
 `bl_ref_time_range` int(10) unsigned default NULL,
 `bl_pct_down` int(10) unsigned default NULL,
 `bl_pct_up` int(10) unsigned default NULL,
 `bl_fail_trigger` int(10) unsigned default NULL,
 `bl_fail_count` int(11) unsigned default NULL,
 `bl_alert` int(2) NOT NULL default '0',
 `lastread` varchar(100) default NULL,
 `oldvalue` varchar(100) NOT NULL default '',
 `repeat_alert` int(10) unsigned default NULL,
 `notify_default` enum('on','off') default NULL,
 `notify_extra` varchar(255) default NULL,
 `host_id` int(10) default NULL,
 `syslog_priority` int(2) default '3',
 `cdef` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `rra_id` (`rra_id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |
 EXPLAIN SELECT
   I have seen the following select query in the slow query log.
   I also saw update queries as well.

 mysql explain select * from thold_data where thold_enabled='on' AND data_id 
 = 91633;
 ++-++--+---+--+-+--+--+-+
 | id | select_type | table  | type | possible_keys | key  | key_len | ref 
  | rows | Extra   |
 ++-++--+---+--+-+--+--+-+
 |  1 | SIMPLE  | thold_data | ALL  | NULL  | NULL | NULL| 
 NULL | 6161 | Using where | 
 ++-++--+---+--+-+--+--+-+
 1 row in set (0.06 sec)

 If cache size tuning is not an option ,
 do you think that following action would be an choice to faten the queries 
 little bit more?
You are selecting a record based on the value of data_id and
thold_enabled, but don't have an index on either? Add an index for both.
If data_id is unique, then you would only need an index on that.

Alex

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

Re: Need help for performance tuning with Mysql

[Alex Schaft]

On 2012/05/24 07:37, Alex Schaft wrote:
 You are selecting a record based on the value of data_id and
 thold_enabled, but don't have an index on either? Add an index for both.
 If data_id is unique, then you would only need an index on that.

 Alex


On second thought, an index on thold_enabled won't mean much I think, so
either leave it off or create an index on data_id plus thold_enabled.
Someone more knowledgeable may correct me.

Alex


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

Need help for performance tuning with Mysql

[Yu Watanabe]

Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf. 

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values, 

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ   
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to 
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe


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

Re: Need help for performance tuning with Mysql

[Yu Watanabe]

Also following is the free command result.

 total   used   free sharedbuffers cached
Mem:   81623807843676 318704  0  956325970892
-/+ buffers/cache:17771526385228
Swap:  8032492  235608008932

Thanks,
Yu


Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf. 

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values, 

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ   
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to 
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

__


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


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

Re: Need help for performance tuning with Mysql

[Tsubasa Tanaka]

Hello,

I seem your mysqld doesn't use enough memory.

Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

if your mysqld uses InnoDB oftenly,
edit innodb_buffer_pool_size in you my.cnf.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size


table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

It is solution for only sql's large result,i think.
if you doesn't recognize that problem causes large result,
you should approach other way,too.

regards,


ts. tanaka//

2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
 Also following is the free command result.

 total   used   free sharedbuffers cached
 Mem:   81623807843676 318704  0  956325970892
 -/+ buffers/cache:17771526385228
 Swap:  8032492  235608008932

 Thanks,
 Yu


 Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from 
mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf.

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values,

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

__


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


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


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

Re: Need help for performance tuning with Mysql

[Johnny Withers]

I don't see any attachments.

First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process
is painless.

Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give you the best
performance increase.

Also, what kind of hard disks do you have the data files on? Raid? No raid?

Sent from my iPad

On May 22, 2012, at 9:08 PM, Yu Watanabe yu.watan...@jp.fujitsu.com wrote:

 Hello all.

 I would like to ask for advice with performance tuning with MySQL.

 Following are some data for my server.

 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1-enterprise

 Attached file
 # my.cnf.txt  : my.cnf information
 # mysqlext_20120522131034.log : variable and status information from 
 mysqladmin

 I have 2 database working with high load.

 I wanted to speed up my select and update queries not by
 optimizing the query itself but tuning the my.cnf.

 I have referred to following site,
 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

 and read Hiperformance Mysql vol.2 ,
 and increased the following values,

 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size

 but made not much difference.

 According to the ps and sar result

 *1 PS result
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028

 *2 SAR
 Average CPU user 25%
sys  5%
io   3%

 I assume that MySQL can work more but currently not.

 I am considersing to off load 1 high load database to
 seperate process and make MySQL work in multiple process.

 It would be a great help if people in this forum can give
 us an adivice for the tuning.

 Best Regards,
 Yu Watanabe


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

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

Re: Need help for performance tuning with Mysql

[Yu Watanabe]

Hello Tsubasa.

Thank you for the reply. (返信ありがとうございます。)

Our high loaded DB are both INNODB and MyISAM.
Espicially , on MyISAM. 

I will consider the tuning of innodb_buffer_pool_size as well.

Do you know the tips for how to tune the disk access for MyISAM?

Thanks,
Yu

Tsubasa Tanaka さんは書きました:
Hello,

I seem your mysqld doesn't use enough memory.

Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

if your mysqld uses InnoDB oftenly,
edit innodb_buffer_pool_size in you my.cnf.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size


table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

It is solution for only sql's large result,i think.
if you doesn't recognize that problem causes large result,
you should approach other way,too.

regards,


ts. tanaka//

2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
 Also following is the free command result.

 total   used   free sharedbuffers cached
 Mem:   81623807843676 318704  0  956325970892
 -/+ buffers/cache:17771526385228
 Swap:  8032492  235608008932

 Thanks,
 Yu


 Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from 
mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf.

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values,

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

__


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


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


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




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

Re: Need help for performance tuning with Mysql

[Tsubasa Tanaka]

Hello, Yu-san,
(へろへろな英語で申し訳ないです)

Can I think that you already tweaked Index on the tables?
if you yet,please create apt indexes.

MyISAM caches only Index without data.
i take way for decreasing disk seek,
 1) create more indexes on the tables,if the tables doesn't update quite often.
   including data into index forcibly.
   this makes slow for insert and update,and this is dirty idea,i think.
(よくSELECTされるカラムをINDEXに含めてしまいます。
ただし、SELECT * FROMで呼ばれることが多い場合には使えない上に
かなり美しくない策です。。)
 2) tune filesystem and disk drive parameter for datadir.
   MyISAM table's data caches only in the filesystem cache.
   But i regret that i don't have knowledge around filesystem.

あまり力になれなくて申し訳ないです。

regards,


ts. tanaka//

2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
 Hello Tsubasa.

 Thank you for the reply. (返信ありがとうございます。)

 Our high loaded DB are both INNODB and MyISAM.
 Espicially , on MyISAM.

 I will consider the tuning of innodb_buffer_pool_size as well.

 Do you know the tips for how to tune the disk access for MyISAM?

 Thanks,
 Yu

 Tsubasa Tanaka さんは書きました:
Hello,

I seem your mysqld doesn't use enough memory.

Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

if your mysqld uses InnoDB oftenly,
edit innodb_buffer_pool_size in you my.cnf.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size


table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

It is solution for only sql's large result,i think.
if you doesn't recognize that problem causes large result,
you should approach other way,too.

regards,


ts. tanaka//

2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
 Also following is the free command result.

 total   used   free sharedbuffers cached
 Mem:   81623807843676 318704  0  956325970892
 -/+ buffers/cache:17771526385228
 Swap:  8032492  235608008932

 Thanks,
 Yu


 Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from 
mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf.

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values,

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

__


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


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


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




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


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

need help with -- unknown variable

[Brown, Charles]

Hello All ~ I attempted to do a mysqldump when I got this message  -- see below:

mysqldump: unknown variable 'datadir=/var/lib/mysql'

Please can someone help me.

Thanks






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

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

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

Thank you.

Re: need help with -- unknown variable

[Andrew Moore]

Check if this is in the [mysqldump] section of your my.cnf file(s). Of
course it might not be a valid option in mysqldump...I haven't checked...
On 13 Jun 2011 17:00, Brown, Charles cbr...@bmi.com wrote:
 Hello All ~ I attempted to do a mysqldump when I got this message -- see
below:

 mysqldump: unknown variable 'datadir=/var/lib/mysql'

 Please can someone help me.

 Thanks





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

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

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

 Thank you.
 

RE: need help with -- unknown variable

[Brown, Charles]

Hello ~ Thanks Andrew - it worked. It worked after  I commented it out on 
my.cnf file.  Any idea what's the purpose of that statement. I inherited this 
system.

Thanks all for quick response



[mysqld]
server-id=2
relay-log=naxbmisq04-relay-bin
set-variable=max_connect_error=100
max_allowed_packet=1048576000
thread_cache_size=16
max_connections=200
query_cache_type=1
query_cache_size=32768000
table_cache=800
max_heap_table_size=32768000
[mysqldump]
max_allowed_packet=1048576000
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




From: Andrew Moore [mailto:eroomy...@gmail.com]
Sent: Monday, June 13, 2011 11:13 AM
To: Brown, Charles
Cc: mysql@lists.mysql.com
Subject: Re: need help with -- unknown variable


Check if this is in the [mysqldump] section of your my.cnf file(s). Of course 
it might not be a valid option in mysqldump...I haven't checked...
On 13 Jun 2011 17:00, Brown, Charles cbr...@bmi.commailto:cbr...@bmi.com 
wrote:
 Hello All ~ I attempted to do a mysqldump when I got this message -- see 
 below:

 mysqldump: unknown variable 'datadir=/var/lib/mysql'

 Please can someone help me.

 Thanks





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

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

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

 Thank you.
 


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

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

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

Thank you.

Re: need help creating relation

[Jo�o C�ndido de Souza Neto]

I could be wrong but I think your problem is the unique index.

-- 
João Cândido de Souza Neto

robert rottermann rob...@redcor.ch escreveu na mensagem 
news:4dd967a8.5040...@redcor.ch...
 Hi there,

 I would like to create a table that optionally links to an other table.

 The field company_id in the following table should be either Null or link 
 to the table tblCompany (`id` ).
 How do I do that. The way I it now, I can not enter records where the 
 field company_id is null.

 Thanks
 robert

 drop table IF EXISTS tblAgentCompanyMapper;
 CREATE  TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` (
   `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' ,
   `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used 
 by energiecluster adresses' ,
   `name` VARCHAR(128) NOT NULL ,
   `url` VARCHAR(256) NULL ,
   INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) ,
   PRIMARY KEY (`siaid`) ,
   CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1`
 FOREIGN KEY (`company_id` )
 REFERENCES `energie_2`.`tblCompany` (`id` )
 ON DELETE SET NULL
 ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8;
 



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

need help creating relation

[robert rottermann]

Hi there,

I would like to create a table that optionally links to an other table.

The field company_id in the following table should be either Null or link to the 
table tblCompany (`id` ).
How do I do that. The way I it now, I can not enter records where the field 
company_id is null.


Thanks
robert

drop table IF EXISTS tblAgentCompanyMapper;
CREATE  TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` (
  `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' ,
  `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used by 
energiecluster adresses' ,

  `name` VARCHAR(128) NOT NULL ,
  `url` VARCHAR(256) NULL ,
  INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) ,
  PRIMARY KEY (`siaid`) ,
  CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1`
FOREIGN KEY (`company_id` )
REFERENCES `energie_2`.`tblCompany` (`id` )
ON DELETE SET NULL
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


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

Re: Need help with query

[S�ndor Hal�sz]

 2011/03/15 17:51 -0500, LAMP 
Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
item_id int,
org_id int,
) ENGINE=MyISAM



Need to select all (distinct) org_id they have item_id 34, 36, 58 and  
63. All of them, not only some of them.

Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

I now noticed the aggregate function GROUP_CONCAT:

select org_id,GROUP_CONCAT(DISTINCT item_id, ORDER BY item_id) AS itemset
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having itemset = '34,36,58,63'


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

Re: Need help with query

[Roy Lyseng]

Hi!

I think that the query that you have proposed is the best possible for the 
problem.

However, if there are duplicates in the orders table, then
  HAVING COUNT(item_id) = 4
should be replaced with
  HAVING COUNT(DISTINCT item_id) = 4

(I assume that you meant item_id and not org_id in the COUNT function).

Thanks,
Roy

On 17.03.11 18.00, LAMP wrote:

Yes, that was my question. Though, since English is not my first language, let
me try to post it again:

There is a list of all orgs and items org bough, from table called orders

item_id org_id
34 2607
34 2607
34 1520
36 2607
36 1520
36 8934
38 28
38 15
38 5
38 13
58 2607
58 2607
58 7295
58 1649
58 7295
58 1520
63 2607
63 2607
63 8871
63 7295
63 1520
65 15
65 20
95 1520
95 1520
95 7295
98 1520
98 7295


select org_id from orders where item_id in (34. 36. 58. 63) will give me a 
result

5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree?

What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of
them. Result should be only orgs 2607 and 1520.

I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id they have
item_id 34, 36, 58 and 63. All of them, not only some of them. 

That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure what you
DO want with your query. Why are 2607 and 1520 the only right answers?


Because they are. I look at the database and manually found the result I have
to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for item_ids
34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst
the desired item_ids


actually, there is mistake in my query, it should say having count(org_id) = 
4
and, yes, that's what I want. I can get the correct list using the query I
posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify EXACTLY
what you want. Giving an incomplete or contradictory description of you want
only wastes both your time and mine.


As I stated earlier, English is not my first language and I was trying to do my
best. Sorry for confusing you.




--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version of MySQL
that you are using. I don't currently have any version of MySQL installed so
I can't try this myself to be sure it works in your version of MySQL.

--
Rhino


your query will give me every org_id that has ANY of item_id., I need org_id
that has ALL of item_id. right?
result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_id org_id
34 2607
34 2607
34 1520
36 2607
36 1520
36 8934
38 28
38 15
38 5
38 13
58 2607
58 2607
58 7295
58 1649
58 7295
58 1520
63 2607
63 2607
63 8871
63 7295
63 1520
65 15
65 20
95 1520
95 1520
95 7295
98 1520
98 7295


Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63.
All of them, not only some of them.

Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP










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

Re: Need help with query

[LAMP]

On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote:


Indeed, I don't thing there is.

Just be sure that each record has an unique combination of org_id  
and item_id, otherwise you might end up with an org_id that, for  
example, references 4 times item_id 34 in 4 different records, but  
no other item_ids. This is obvisouly not what you want.


Geert-Jan


Correct. That's why I use select distinct org_id, item_id in sub- 
query.


Is here anybody from mysql development team, to suggest to build IN  
ALL function?

:-)






2011/3/17 LAMP l...@afan.net
First I was thinking there is function IN ALL or something like  
that, since there are functions IN and EXISTS. And I would be able  
to make a query something like this

   select distinct org_id, item_id
   from orders
   where item_id in all (34, 36, 58, 63)
 order by org_id asc

But, there isn't any. :-(


The correct query is

select r.org_id
from
(
select distinct a.org_id, a.item_id
from orders a
where a.item_id in (34, 36, 58, 63)
order by a.org_id asc
) r
group by r.org_id
having count(*) = 4








On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote:

 What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them.


Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php 
.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will  
give me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:


Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure  
what you DO want with your query. Why are 2607 and 1520 the only  
right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify  
EXACTLY what you want. Giving an incomplete or contradictory  
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was  
trying to do my best. Sorry for confusing you.




--
Rhino

On 2011-03-15 20:35, LAMP wrote:

On Mar 15, 2011, at 6:18 PM, Rhino wrote:


All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version  
of MySQL that you are using. I don't currently have any version of  
MySQL installed so I can't try this myself to be sure it works in  
your version of MySQL.


--
Rhino

your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871




On 2011-03-15 18:51, LAMP wrote:
Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select 

Re: Need help with query

[S�ndor Hal�sz]

 2011/03/18 08:49 -0500, LAMP 
Is here anybody from mysql development team, to suggest to build IN  
ALL function?

There is a problem here: the basic operation is on the record, each record by 
each record, all by itself. The solution to your problem entails acting on more 
distinct records until enough have been encountered.

If you imagine the table input to a program that checks for hits, you will see 
the problem. The program reads its input, for every number of the four that you 
want matched it holds on to its mate until that mate is matched with all four 
of the chosen. It is a global condition, and SQL works one record at a time. 
Global conditions are detected only through the summary functions.


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

Re: Need help with query

[LAMP]

Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will give  
me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58, 63.  
every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure  
what you DO want with your query. Why are 2607 and 1520 the only  
right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify  
EXACTLY what you want. Giving an incomplete or contradictory  
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was trying  
to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the  
version of MySQL that you are using. I don't currently have any  
version of MySQL installed so I can't try this myself to be sure  
it works in your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP

Re: Need help with query

[Peter Brawley]

 What I need is a list of orgs they bought all of items 34, 36, 58, 
63. every of them.


Some solutions under What else did buyers of X buy at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first 
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called orders

item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will give 
me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58. 
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58, 63. 
every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id 
they have item_id 34, 36, 58 and 63. All of them, not only some of 
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure 
what you DO want with your query. Why are 2607 and 1520 the only 
right answers?


Because they are. I look at the database and manually found the 
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for 
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of 
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having 
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the 
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify 
EXACTLY what you want. Giving an incomplete or contradictory 
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was trying 
to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version 
of MySQL that you are using. I don't currently have any version of 
MySQL installed so I can't try this myself to be sure it works in 
your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I 
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58 
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP









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

Re: Need help with query

[LAMP]

First I was thinking there is function IN ALL or something like that,  
since there are functions IN and EXISTS. And I would be able to make a  
query something like this

select distinct org_id, item_id
from orders
where item_id in all (34, 36, 58, 63)
 order by org_id asc

But, there isn't any. :-(


The correct query is

select r.org_id
from
(
 select distinct a.org_id, a.item_id
 from orders a
 where a.item_id in (34, 36, 58, 63)
 order by a.org_id asc
) r
group by r.org_id
having count(*) = 4







On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote:

 What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them.


Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php 
.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will  
give me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not  
sure what you DO want with your query. Why are 2607 and 1520 the  
only right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please  
clarify EXACTLY what you want. Giving an incomplete or  
contradictory description of you want only wastes both your time  
and mine.


As I stated earlier, English is not my first language and I was  
trying to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the  
version of MySQL that you are using. I don't currently have any  
version of MySQL installed so I can't try this myself to be sure  
it works in your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36,  
58 and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP










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

Need help with query

[LAMP]

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58 and  
63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP

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

Re: Need help with query

[LAMP]

On Mar 15, 2011, at 6:18 PM, Rhino wrote:



 All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version  
of MySQL that you are using. I don't currently have any version of  
MySQL installed so I can't try this myself to be sure it works in  
your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I need  
org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP




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

Re: need Help - Mysqldump issue

[Christophe DUMONET]

Hello,
Thank for your help
 I just try mysqldump with --quick or --opt option ...  to avoid 
out of memory  problem but


-- dump fails with  --max_allowed_packet=2048M and --quick :
r...@pcjahia01:/# /usr/bin/mysqldump  -A  --max_allowed_packet=2048M 
--quick --default-character-set=UTF8 -u root -p   /var/tmp/testbackup01.sql

Enter password:
mysqldump: Error 5: Out of memory (Needed 702898072 bytes) when dumping 
table `jahia_sl2_version_content` at row: 0



-- dump succeed with --max_allowed_packet=4096M and --quick ( but the 
computer freezes a lot  )


I thought using  --quick option avoid mysqldump out of memory 
problem...but I still need --max_allowed_packet=4096M ...


Computer total memory is 8Gb, I run a J2EE application which own 4Gb, 
additionally a 3Gb mysql database, altought I use mysqldump with --quick


Do you think I need more physical memory ?

Bests,
Christophe.


 Christophe Dumonet
 Centre de Ressources Informatiques
 Institut Francais de Mecanique Avancee (IFMA)
 Campus des Cezeaux
 BP 265
 63175 AUBIERE Cedex
 Tel : +33 - 4.73.28.80.64
 Fax : +33 - 4.73.28.81.00
 Mail : christophe.dumo...@ifma.fr



Le 19/11/2010 10:46, Christophe DUMONET a écrit :

Hello,
Starting today,  my daily database backup script does not work :-( 
with mysqldump typically Out of memory error.

So, I try to change max_allowed_packet option value, but I don't succeed
(mysql run on ubuntu 10.04 OS with  5.1.41-3ubuntu12.7 0 mysql version.)

On the last successfull backup, database size was : 2,59 Go

Here is some of my test :

(with  --max_allowed_packet option  = 512M )
/usr/bin/mysqldump  -A  --max_allowed_packet=512M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql

Enter password:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' 
bytes when dumping table `jahia_sl2_version_content` at row: 0


(with  --max_allowed_packet = 1024M or 2048 option : )
/usr/bin/mysqldump  -A  --max_allowed_packet=1024M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql

Enter password:
mysqldump: Out of memory (Needed 1405796107 bytes)
mysqldump: Couldn't allocate memory

(with  --max_allowed_packet = 4096M  option : )
/usr/bin/mysqldump  -A  --skip-quick --max_allowed_packet=4096M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql
Warning: option 'max_allowed_packet': unsigned value 4294967296 
adjusted to 2147483648

Enter password:
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server


Adding --skip-opt --quick option does not solve the issue

Adding  --skip-quick, the error is :
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server


Here is my config : /etc/mysql/my.cnf

[client]
port= 3306
socket  = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice= 0

[mysqld]

user= mysql
socket  = /var/run/mysqld/mysqld.sock
port= 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir  = /tmp
skip-external-locking
bind-address= 127.0.0.1
key_buffer  = 16M
max_allowed_packet  = 4096M
thread_stack= 192K
thread_cache_size   = 8
myisam-recover = BACKUP
query_cache_limit   = 1M
query_cache_size= 16M

log_error= /var/log/mysql/error.log

expire_logs_days= 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer  = 16M

includedir /etc/mysql/conf.d/

Any help would be appreciate !!
Bests



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

need Help - Mysqldump issue

[Christophe DUMONET]

Hello,
Starting today,  my daily database backup script does not work :-( with 
mysqldump typically Out of memory error.

So, I try to change max_allowed_packet option value, but I don't succeed
(mysql run on ubuntu 10.04 OS with  5.1.41-3ubuntu12.7 0 mysql version.)

On the last successfull backup, database size was : 2,59 Go

Here is some of my test :

(with  --max_allowed_packet option  = 512M )
/usr/bin/mysqldump  -A  --max_allowed_packet=512M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql

Enter password:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes 
when dumping table `jahia_sl2_version_content` at row: 0


(with  --max_allowed_packet = 1024M or 2048 option : )
/usr/bin/mysqldump  -A  --max_allowed_packet=1024M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql

Enter password:
mysqldump: Out of memory (Needed 1405796107 bytes)
mysqldump: Couldn't allocate memory

(with  --max_allowed_packet = 4096M  option : )
/usr/bin/mysqldump  -A  --skip-quick --max_allowed_packet=4096M 
--default-character-set=UTF8 -u root -p   /tmp/testbackup01.sql
Warning: option 'max_allowed_packet': unsigned value 4294967296 adjusted 
to 2147483648

Enter password:
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server


Adding --skip-opt --quick option does not solve the issue

Adding  --skip-quick, the error is :
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server


Here is my config : /etc/mysql/my.cnf

[client]
port= 3306
socket  = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice= 0

[mysqld]

user= mysql
socket  = /var/run/mysqld/mysqld.sock
port= 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir  = /tmp
skip-external-locking
bind-address= 127.0.0.1
key_buffer  = 16M
max_allowed_packet  = 4096M
thread_stack= 192K
thread_cache_size   = 8
myisam-recover = BACKUP
query_cache_limit   = 1M
query_cache_size= 16M

log_error= /var/log/mysql/error.log

expire_logs_days= 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer  = 16M

includedir /etc/mysql/conf.d/

Any help would be appreciate !!
Bests
--


 Christophe Dumonet
 Centre de Ressources Informatiques
 Institut Francais de Mecanique Avancee (IFMA)
 Campus des Cezeaux
 BP 265
 63175 AUBIERE Cedex
 Tel : +33 - 4.73.28.80.64
 Fax : +33 - 4.73.28.81.00
 Mail : christophe.dumo...@ifma.fr




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

Re: Need Help Writing Simple Query

[Mark Phillips]

On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong 
cuong.m...@vienthongso.com wrote:

 Hi Mark,
 Please test this query:
 select test1.*, (select name from test2 where test2.id=test1.`v_id` limit
 1) as name_1,
 (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2
 from test1;

 - test1 table:
 col1v_idh_id
 America 1   2

 - test2 table:
 id  name
 2   SAM
 1   UNCLE

 - Original Message -
 From: Mark Phillips m...@phillipsmarketing.biz
 To: Mysql List mysql@lists.mysql.com
 Sent: Monday, July 26, 2010 8:29:00 AM
 Subject: Need Help Writing Simple Query

 I have been away from sql for awhile, and can't seem to figure out how to
 write a simple query for two tables.

 Table 1 has many columns, two of which are hID and vID. Table 2 has two
 columns, ID and name. The hID and vID in table 1 correspond to the IDs in
 table 2. I want to make a query so I get all the columns from table 1, but
 substitute the names from table 2 for the hID and vID values. For example,

 Table 1:
 col 1, col 2, hID, vID, col 3
 AB1 2 C

 Table 2:
 ID, name
 1fred
 2sam

 Query result:
 col1, col 2, hName, vName, col 3
 A   Bfred  sam   C

 Thanks!

 Mark

 --
 Best Regards,
 Cuongmc.

 --
 Nguyen Manh Cuong
 Phong Ky Thuat - Cong ty Vien Thong So - VTC
 Dien thoai: 0912051542
 Gmail : philipscu...@gmail.com
 YahooMail : philipscu...@yahoo.com


Thanks! That did the trick.

Mark

Need Help Writing Simple Query

[Mark Phillips]

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C

Thanks!

Mark

Re: Need Help Writing Simple Query

[Nguyen Manh Cuong]

Hi Mark,
Please test this query:
select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as 
name_1, 
(select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 
from test1;

- test1 table:
col1v_idh_id
America 1   2

- test2 table: 
id  name
2   SAM
1   UNCLE

- Original Message -
From: Mark Phillips m...@phillipsmarketing.biz
To: Mysql List mysql@lists.mysql.com
Sent: Monday, July 26, 2010 8:29:00 AM
Subject: Need Help Writing Simple Query

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C

Thanks!

Mark

-- 
Best Regards,
Cuongmc.

-- 
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscu...@gmail.com
YahooMail : philipscu...@yahoo.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: Need Help Writing Simple Query

[John List]

On 07/25/2010 09:29 PM, Mark Phillips wrote:

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C
  


select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1,
Table2 as hTable,
Table2 as vTable
where hId = hTable.ID and vID = vTable.ID

or

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1
left join Table2 as hTable on hID = hTable.ID
left join Table2 as vTable on vID = vTable.ID




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

Re: Need help with query optimization

[John Daisley]

It may only be returning 51 rows but its having to read significantly more.

Get rid of the derived table join if possible. Something like

SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName,
TAP.State,
TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
S.Region, S.District,Sum(Pts.Points) Total_Points
FROM TorchAwardParticipants TAP
 JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID
 JOIN Chapters C On C.ID http://c.id/=M.ChapterID
 JOIN Schools S On S.ID http://s.id/=C.SchoolID
 JOIN TorchAwardSelAct  Pts ON Pts.AchievementID=TAP.ID http://tap.id/
WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL
AND Pts.LocalApproveStatus='A'
GROUP BY TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI,
TAP.LastName, TAP.State,
TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
S.Region, S.District
ORDER BY TAP.LastName, TAP.FirstName, Sum(Pts.Points)
Regards
John



On Tue, Mar 16, 2010 at 6:17 PM, Jesse j...@msdlg.com wrote:

 I have the following query:

 SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI,
 TAP.LastName, TAP.State,
 TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
 S.Region, S.District,Pts.TotPoints
 FROM TorchAwardParticipants TAP
  JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID
  JOIN Chapters C On C.ID http://c.id/=M.ChapterID
  JOIN Schools S On S.ID http://s.id/=C.SchoolID
  JOIN (SELECT AchievementID,Sum(Points) As TotPoints
 FROM TorchAwardSelAct TASA
 WHERE LocalApproveStatus='A'
 GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/
 WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT
 NULL
 ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints

 The TorchAwardParticipants table has about 84,000 records in it.
 The query takes almost 40 seconds to return the data, which is only 51
 rows.
 An EXPLAIN returns the following:

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

 ++-+++-+---+-+-++-+
 |  1 | PRIMARY | derived2 | ALL| NULL| NULL |
 NULL| NULL|   4382 | Using temporary; Using filesort
 |
 |  1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID |
 PRIMARY | 4   | Pts.AchievementID   |  1 | Using where |
 |  1 | PRIMARY | M  | eq_ref | PRIMARY,IX_Members_3|
 PRIMARY | 4   | bpa.TAP.CurrentMemberID |  1 | |
 |  1 | PRIMARY | C  | eq_ref | PRIMARY,IX_Chapters_1   |
 PRIMARY | 4   | bpa.M.ChapterID |  1 | |
 |  1 | PRIMARY | S  | eq_ref | PRIMARY |
 PRIMARY | 4   | bpa.C.SchoolID  |  1 | |
 |  2 | DERIVED | TASA   | index  | NULL|
 AchievementID | 5   | NULL| 161685 | Using where |

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

 What is the best way to optimize this query so that it doesn't take 40
 seconds to return the dataset?

 Jesse



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk

Need help with query optimization

[Jesse]

I have the following query:

SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State,
TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
S.Region, S.District,Pts.TotPoints
FROM TorchAwardParticipants TAP
  JOIN Members M On M.ID=TAP.CurrentMemberID
  JOIN Chapters C On C.ID=M.ChapterID
  JOIN Schools S On S.ID=C.SchoolID
  JOIN (SELECT AchievementID,Sum(Points) As TotPoints
 FROM TorchAwardSelAct TASA
 WHERE LocalApproveStatus='A'
 GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.ID
WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL
ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints

The TorchAwardParticipants table has about 84,000 records in it.
The query takes almost 40 seconds to return the data, which is only 51 rows.
An EXPLAIN returns the following:
++-+++-+---+-+-++-+
| id | select_type | table  | type   | possible_keys   | key 
| key_len | ref | rows   | Extra 
|

++-+++-+---+-+-++-+
|  1 | PRIMARY | derived2 | ALL| NULL| NULL 
| NULL| NULL|   4382 | Using temporary; Using 
filesort |
|  1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY 
| 4   | Pts.AchievementID   |  1 | Using where 
|
|  1 | PRIMARY | M  | eq_ref | PRIMARY,IX_Members_3| PRIMARY 
| 4   | bpa.TAP.CurrentMemberID |  1 | 
|
|  1 | PRIMARY | C  | eq_ref | PRIMARY,IX_Chapters_1   | PRIMARY 
| 4   | bpa.M.ChapterID |  1 | 
|
|  1 | PRIMARY | S  | eq_ref | PRIMARY | PRIMARY 
| 4   | bpa.C.SchoolID  |  1 | 
|
|  2 | DERIVED | TASA   | index  | NULL| 
AchievementID | 5   | NULL| 161685 | Using where 
|

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

What is the best way to optimize this query so that it doesn't take 40 
seconds to return the dataset?


Jesse



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

Re: Need help with query optimization

[Ananda Kumar]

Can you please show us the indexes on both the tables.

regards
anandkl

On Tue, Mar 16, 2010 at 11:47 PM, Jesse j...@msdlg.com wrote:

 I have the following query:

 SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI,
 TAP.LastName, TAP.State,
 TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,
 S.Region, S.District,Pts.TotPoints
 FROM TorchAwardParticipants TAP
  JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID
  JOIN Chapters C On C.ID http://c.id/=M.ChapterID
  JOIN Schools S On S.ID http://s.id/=C.SchoolID
  JOIN (SELECT AchievementID,Sum(Points) As TotPoints
 FROM TorchAwardSelAct TASA
 WHERE LocalApproveStatus='A'
 GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/
 WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT
 NULL
 ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints

 The TorchAwardParticipants table has about 84,000 records in it.
 The query takes almost 40 seconds to return the data, which is only 51
 rows.
 An EXPLAIN returns the following:

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

 ++-+++-+---+-+-++-+
 |  1 | PRIMARY | derived2 | ALL| NULL| NULL |
 NULL| NULL|   4382 | Using temporary; Using filesort
 |
 |  1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID |
 PRIMARY | 4   | Pts.AchievementID   |  1 | Using where |
 |  1 | PRIMARY | M  | eq_ref | PRIMARY,IX_Members_3|
 PRIMARY | 4   | bpa.TAP.CurrentMemberID |  1 | |
 |  1 | PRIMARY | C  | eq_ref | PRIMARY,IX_Chapters_1   |
 PRIMARY | 4   | bpa.M.ChapterID |  1 | |
 |  1 | PRIMARY | S  | eq_ref | PRIMARY |
 PRIMARY | 4   | bpa.C.SchoolID  |  1 | |
 |  2 | DERIVED | TASA   | index  | NULL|
 AchievementID | 5   | NULL| 161685 | Using where |

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

 What is the best way to optimize this query so that it doesn't take 40
 seconds to return the dataset?

 Jesse



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

I NEED HELP ON 'SOURCE(\.) MYSQL

[kebede teferi]

Hi, I'm very new to this and I need help.

What I want to do is to execute a source command statement from cmd to create 
tables and populate them on a data base that I created in Mysql.  Here is what 
I did at CMD:
*I changed the directory from c:\ to mysql
*At mysql prompt i used -u root -p that prompted me to give my password.
*I put in my password and I was allowed to access the mysql server.
*I used the 'the use --database' command and I can see the database I created 
is active and waiting to be worked on.
*then to run the script which is on a note pad with the name create and file 
type: sql I typed the following at the mysql prompt:
*source create.sql;
  I get the error message '2' and '22' whichever way I tried the source 
command.  I appreciate your help

Kebede Teferi




  

Re: I NEED HELP ON 'SOURCE(\.) MYSQL

[Jesper Wisborg Krogh]

How did you type the source command? Did you remember not to quote the file 
name? E.g.

mysql SOURCE schema.sql

is the correct way.

Jesper

On Tue, 2 Feb 2010 10:38:08 kebede teferi wrote:
 Hi, I'm very new to this and I need help.

 What I want to do is to execute a source command statement from cmd to
 create tables and populate them on a data base that I created in Mysql. 
 Here is what I did at CMD: *I changed the directory from c:\ to mysql
 *At mysql prompt i used -u root -p that prompted me to give my password.
 *I put in my password and I was allowed to access the mysql server.
 *I used the 'the use --database' command and I can see the database I
 created is active and waiting to be worked on. *then to run the script
 which is on a note pad with the name create and file type: sql I typed the
 following at the mysql prompt: *source create.sql;
   I get the error message '2' and '22' whichever way I tried the source
 command.  I appreciate your help

 Kebede Teferi


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

Re: I NEED HELP ON 'SOURCE(\.) MYSQL

[John List]

On 02/01/2010 06:38 PM, kebede teferi wrote:

Hi, I'm very new to this and I need help.

What I want to do is to execute a source command statement from cmd to create 
tables and populate them on a data base that I created in Mysql.  Here is what 
I did at CMD:
*I changed the directory from c:\ to mysql
*At mysql prompt i used -u root -p that prompted me to give my password.
*I put in my password and I was allowed to access the mysql server.
*I used the 'the use --database' command and I can see the database I created 
is active and waiting to be worked on.
*then to run the script which is on a note pad with the name create and file 
type: sql I typed the following at the mysql prompt:
*source create.sql;
  I get the error message '2' and '22' whichever way I tried the source 
command.  I appreciate your help

Kebede Teferi
  


mysql looks in the current directory for the source file. That the 
directory where you ran the mysql client from. The quickest fix is to 
give the complete path to the file, e.g. source /home/me/mysourcefile.sql


(You say yoiu changed your current directory to mysql but didn't give us 
the complete path. There are multiple mysql directories in most 
configurations.)


--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: I NEED HELP ON 'SOURCE(\.) MYSQL

[Carlos Proal]

Error 2 is  No such file or directory

So, probably the file is not in the right path or you need to explicitly 
provide the path to the file.


Carlos


On 2/1/2010 5:38 PM, kebede teferi wrote:

Hi, I'm very new to this and I need help.

What I want to do is to execute a source command statement from cmd to create 
tables and populate them on a data base that I created in Mysql.  Here is what 
I did at CMD:
*I changed the directory from c:\  to mysql
*At mysql prompt i used -u root -p that prompted me to give my password.
*I put in my password and I was allowed to access the mysql server.
*I used the 'the use --database' command and I can see the database I created 
is active and waiting to be worked on.
*then to run the script which is on a note pad with the name create and file 
type: sql I typed the following at the mysql prompt:
*source create.sql;
   I get the error message '2' and '22' whichever way I tried the source 
command.  I appreciate your help

Kebede Teferi





   



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

Re: I NEED HELP ON 'SOURCE(\.) MYSQL

[Colin Streicher]

what everyone else said... also, is there a longer version of the error? or is 
it literally error 2 
or error 22?

Colin

On February 1, 2010 06:38:08 pm kebede teferi wrote:
 Hi, I'm very new to this and I need help.
 
 What I want to do is to execute a source command statement from cmd to
  create tables and populate them on a data base that I created in Mysql. 
  Here is what I did at CMD: *I changed the directory from c:\ to mysql
 *At mysql prompt i used -u root -p that prompted me to give my password.
 *I put in my password and I was allowed to access the mysql server.
 *I used the 'the use --database' command and I can see the database I
  created is active and waiting to be worked on. *then to run the script
  which is on a note pad with the name create and file type: sql I typed the
  following at the mysql prompt: *source create.sql;
   I get the error message '2' and '22' whichever way I tried the source
  command.  I appreciate your help
 
 Kebede Teferi
 

-- 
Your love life will be happy and harmonious.

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

Re: I NEED HELP ON 'SOURCE(\.) MYSQL

[sureshkumarilu]

Give the ful path of file name with in '' and try it out 

Thanks
Suresh Kuna
MySQL DBA
--Original Message--
From: Colin Streicher
To: mysql@lists.mysql.com
Cc: kebede teferi
ReplyTo: co...@obviouslymalicious.com
Subject: Re: I NEED HELP ON 'SOURCE(\.) MYSQL
Sent: Feb 2, 2010 7:33 AM

what everyone else said... also, is there a longer version of the error? or is 
it literally error 2 
or error 22?

Colin

On February 1, 2010 06:38:08 pm kebede teferi wrote:
 Hi, I'm very new to this and I need help.
 
 What I want to do is to execute a source command statement from cmd to
  create tables and populate them on a data base that I created in Mysql. 
  Here is what I did at CMD: *I changed the directory from c:\ to mysql
 *At mysql prompt i used -u root -p that prompted me to give my password.
 *I put in my password and I was allowed to access the mysql server.
 *I used the 'the use --database' command and I can see the database I
  created is active and waiting to be worked on. *then to run the script
  which is on a note pad with the name create and file type: sql I typed the
  following at the mysql prompt: *source create.sql;
   I get the error message '2' and '22' whichever way I tried the source
  command.  I appreciate your help
 
 Kebede Teferi
 

-- 
Your love life will be happy and harmonious.

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



Sent from BlackBerry® on Airtel