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

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

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
 

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 

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

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

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

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

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

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

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

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

Re: need help with relational tables/fields

[Wolfgang Schaefer]

AndrewJames schrieb:
 Hey,

 i have a table called users which has my users in it, each have a uid
 field.
 I also have a stories table which has stories in it each with a sid field
 for each story but also a uid field so i know which user the story
 belongs
 to.

 i want to write a query that will display the story depending on the
 user..
 basically i guess it works like a word press blog. eg, Andrew logs
 into the
 site and only his stories are displayed, but if john logs in, only his
 stories are displayed.

 Here are my tables

 mysql describe stories;
 +---+---+--+-+---++

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

 | sid   | int(8)| NO   | PRI | NULL  |
 auto_increment |
 | uid   | int(8)| NO   | MUL | NULL 
 ||
 | story | text  | NO   | | NULL 
 ||
 | storyDATE | timestamp | NO   | | CURRENT_TIMESTAMP
 ||
 +---+---+--+-+---++

 4 rows in set (0.02 sec)

 mysql describe users;
 +---+-+--+-+-++
 | Field | Type| Null | Key | Default | Extra  |
 +---+-+--+-+-++
 | uid   | int(8)  | NO   | PRI | NULL| auto_increment |
 | username  | varchar(12) | NO   | | NULL||
 | password  | varchar(32) | NO   | | NULL||
 | firstName | varchar(15) | NO   | | NULL||
 | lastName  | varchar(15) | NO   | | NULL||
 +---+-+--+-+-++
 5 rows in set (0.01 sec)


I guess you include uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}


cheers,
wolfgang

-- 
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 relational tables/fields

[Wolfgang Schaefer]

AndrewJames schrieb:
 Hey,

 i have a table called users which has my users in it, each have a uid
 field.
 I also have a stories table which has stories in it each with a sid field
 for each story but also a uid field so i know which user the story
 belongs
 to.

 i want to write a query that will display the story depending on the
 user..
 basically i guess it works like a word press blog. eg, Andrew logs
 into the
 site and only his stories are displayed, but if john logs in, only his
 stories are displayed.

 Here are my tables

 mysql describe stories;
 +---+---+--+-+---++

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

 | sid   | int(8)| NO   | PRI | NULL  |
 auto_increment |
 | uid   | int(8)| NO   | MUL | NULL 
 ||
 | story | text  | NO   | | NULL 
 ||
 | storyDATE | timestamp | NO   | | CURRENT_TIMESTAMP
 ||
 +---+---+--+-+---++

 4 rows in set (0.02 sec)

 mysql describe users;
 +---+-+--+-+-++
 | Field | Type| Null | Key | Default | Extra  |
 +---+-+--+-+-++
 | uid   | int(8)  | NO   | PRI | NULL| auto_increment |
 | username  | varchar(12) | NO   | | NULL||
 | password  | varchar(32) | NO   | | NULL||
 | firstName | varchar(15) | NO   | | NULL||
 | lastName  | varchar(15) | NO   | | NULL||
 +---+-+--+-+-++
 5 rows in set (0.01 sec)



I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}


cheers,
wolfgang



-- 
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 relational tables/fields

[Wolfgang Schaefer]

AndrewJames schrieb:
 Hey,

 i have a table called users which has my users in it, each have a uid
 field.
 I also have a stories table which has stories in it each with a sid field
 for each story but also a uid field so i know which user the story
 belongs
 to.

 i want to write a query that will display the story depending on the
 user..
 basically i guess it works like a word press blog. eg, Andrew logs
 into the
 site and only his stories are displayed, but if john logs in, only his
 stories are displayed.

 Here are my tables

 mysql describe stories;
 +---+---+--+-+---++

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

 | sid   | int(8)| NO   | PRI | NULL  |
 auto_increment |
 | uid   | int(8)| NO   | MUL | NULL 
 ||
 | story | text  | NO   | | NULL 
 ||
 | storyDATE | timestamp | NO   | | CURRENT_TIMESTAMP
 ||
 +---+---+--+-+---++

 4 rows in set (0.02 sec)

 mysql describe users;
 +---+-+--+-+-++
 | Field | Type| Null | Key | Default | Extra  |
 +---+-+--+-+-++
 | uid   | int(8)  | NO   | PRI | NULL| auto_increment |
 | username  | varchar(12) | NO   | | NULL||
 | password  | varchar(32) | NO   | | NULL||
 | firstName | varchar(15) | NO   | | NULL||
 | lastName  | varchar(15) | NO   | | NULL||
 +---+-+--+-+-++
 5 rows in set (0.01 sec)




I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}


cheers,
wolfgang



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

[Andy Shellam]

Hi,

abdulazeez alugo wrote:
  

Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob

Hi Alugo,


Hi Andy,

Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. 

  
Ah, I see your point. I'm guessing by your code you're using PHP? If 
so call mysql_insert_id() after you've inserted your record into tbl1. 
That will give you the ID of the auto increment column - see the note in 
the below link about bigint columns if your data type is bigint.


http://uk3.php.net/mysql_insert_id

Hope this helps,
Andy



 


Yes I'm using PHP. Can it be something like

function newPost_tbl1($id, $entry, $text)

{

$conn;

$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)

 VALUES ('$id','$entry', $text)', 
$conn);

$tbl1_id = mysql_insert_id($conn);

}

  
Yep that's about it.  You said that tbl1_id is an auto-increment column, 
why are you including it in the insert query?  You should just need 
entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id 
field.


I hope you've just missed out everything on the $conn line just for 
short-hand, because $conn needs to be a valid connection resource 
(result from mysql_connect) before passing it to mysql_query.  Also on 
your MySQL query line, you've started the insert command string with a 
quote  but terminated it with a single apostrophe, this terminator 
should also be a quote.  Your $text should also be enclosed with a 
single apostrophe, and don't forget to clean your input ($entry and 
$text) otherwise you'll be vulnerable to SQL injection and XSS attacks.


Andy

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

[abdulazeez alugo]

 Date: Mon, 20 Apr 2009 08:06:05 +0100
 From: andy-li...@networkmail.eu
 To: defati...@hotmail.com
 CC: mysql@lists.mysql.com
 Subject: Re: Need help with mysql prob
 
 Hi,
 
 abdulazeez alugo wrote:
  
  Date: Sun, 19 Apr 2009 23:19:56 +0100
  From: andy-li...@networkmail.eu
  To: defati...@hotmail.com
  CC: mysql@lists.mysql.com
  Subject: Re: Need help with mysql prob
 
  Hi Alugo,
  
  Hi Andy,
 
  Thanks for your prompt response. However, since tbl1_id has an 
  auto_increment value in tbl1, mysql is actually generating the values for 
  it automatically. 
 
  
  Ah, I see your point. I'm guessing by your code you're using PHP? If 
  so call mysql_insert_id() after you've inserted your record into tbl1. 
  That will give you the ID of the auto increment column - see the note in 
  the below link about bigint columns if your data type is bigint.
 
  http://uk3.php.net/mysql_insert_id
 
  Hope this helps,
  Andy
  
 
  
 
  Yes I'm using PHP. Can it be something like
 
  function newPost_tbl1($id, $entry, $text)
 
  {
 
  $conn;
 
  $result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
 
  VALUES ('$id','$entry', $text)', $conn);
 
  $tbl1_id = mysql_insert_id($conn);
 
  }
 
  
 Yep that's about it. You said that tbl1_id is an auto-increment column, 
 why are you including it in the insert query? You should just need 
 entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id 
 field.
 
 I hope you've just missed out everything on the $conn line just for 
 short-hand, because $conn needs to be a valid connection resource 
 (result from mysql_connect) before passing it to mysql_query. Also on 
 your MySQL query line, you've started the insert command string with a 
 quote  but terminated it with a single apostrophe, this terminator 
 should also be a quote. Your $text should also be enclosed with a 
 single apostrophe, and don't forget to clean your input ($entry and 
 $text) otherwise you'll be vulnerable to SQL injection and XSS attacks.
 
 Andy

 

Hi Andy,

Thank you very much you have been really very helpful. All those mistakes you 
pointed at in the script about the apostrophe and others, are simple mistakes I 
just made in the rush of typing the message; and yes $conn is the result from 
mysql_connect. In my original script I clean my script to avoid mysql injection.

However, if you look at the code very well, you'll realize that I have stored 
the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is 
defined within a function newPost_tbl1($id, $entry, $text). My problem now is 
how to access that variable in the function that inserts into the child table 
so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2.

Thanks in advance.

Alugo.



_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx

Re: Need help with mysql prob

[Andy Shellam]

Hi Alugo,

abdulazeez alugo wrote:


Hi Andy,
Thank you very much you have been really very helpful. All those 
mistakes you pointed at in the script about the apostrophe and others, 
are simple mistakes I just made in the rush of typing the message; and 
yes $conn is the result from mysql_connect. In my original script I 
clean my script to avoid mysql injection.


No worries, just thought I'd point it out in case ;-)

However, if you look at the code very well, you'll realize that I have 
stored the result of the mysql_insert-id($conn) in a php variable 
$tbl1_id and this is defined within a function *newPost_tbl1($id, 
$entry, $text).* My problem now is how to access that variable in the 
function that inserts into the child table so that I'll have the same 
value for tbl1_id in tbl1 and tbl1_id in tbl2.


Why not just return it from the function?


$tbl1_id = mysql_insert_id();
return $tbl1_id;


Then in the code that calls newPost_tbl1 ...

$tbl1_id = newPost_tbl1($id, $entry, $text);
newPost_tbl2($tbl1_id, );

Regards,
Andy

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

[abdulazeez alugo]

 Date: Mon, 20 Apr 2009 13:03:14 +0100
 From: andy-li...@networkmail.eu
 To: defati...@hotmail.com
 CC: mysql@lists.mysql.com
 Subject: Re: Need help with mysql prob
 
 Hi Alugo,
 
 abdulazeez alugo wrote:
 
  Hi Andy,
  Thank you very much you have been really very helpful. All those 
  mistakes you pointed at in the script about the apostrophe and others, 
  are simple mistakes I just made in the rush of typing the message; and 
  yes $conn is the result from mysql_connect. In my original script I 
  clean my script to avoid mysql injection.
 
 No worries, just thought I'd point it out in case ;-)
 
  However, if you look at the code very well, you'll realize that I have 
  stored the result of the mysql_insert-id($conn) in a php variable 
  $tbl1_id and this is defined within a function *newPost_tbl1($id, 
  $entry, $text).* My problem now is how to access that variable in the 
  function that inserts into the child table so that I'll have the same 
  value for tbl1_id in tbl1 and tbl1_id in tbl2.
 
 Why not just return it from the function?
 
 
 $tbl1_id = mysql_insert_id();
 return $tbl1_id;
 
 
 Then in the code that calls newPost_tbl1 ...
 
 $tbl1_id = newPost_tbl1($id, $entry, $text);
 newPost_tbl2($tbl1_id, );
 
 Regards,
 Andy
 
Thank you very much Andy,

Now my problem is solved.

Cheers.

Alugo Abdulazeez.

_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us

Re: Need help with mysql prob

[Andy Shellam]

Hi,

Now I have successfully created a relationship between the two tables but how 
do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in 
tbl2???
  


MySQL handles this for you.  Simply INSERT the value into tbl1, then 
INSERT the value of tbl1_id in tbl2.  Then try INSERT'ing a value into 
tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the 
insert will fail.


Thanks,
Andy

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

[abdulazeez alugo]

 

 Date: Sun, 19 Apr 2009 22:50:20 +0100
 From: andy-li...@networkmail.eu
 To: defati...@hotmail.com
 CC: mysql@lists.mysql.com
 Subject: Re: Need help with mysql prob
 
 Hi,
  Now I have successfully created a relationship between the two tables but 
  how do I make sure the value of tbl1_id in tbl1 is equal to the value of 
  tbl1_id in tbl2???
  
 
 MySQL handles this for you. Simply INSERT the value into tbl1, then 
 INSERT the value of tbl1_id in tbl2. Then try INSERT'ing a value into 
 tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the 
 insert will fail.
 
 Thanks,
 Andy

 

Hi Andy,

Thanks for your prompt response. However, since tbl1_id has an auto_increment 
value in tbl1, mysql is actually generating the values for it automatically. 


_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us

Re: Need help with mysql prob

[Andy Shellam]

Hi Alugo,

Hi Andy,

Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. 
  


Ah, I see your point.  I'm guessing by your code you're using PHP?  If 
so call mysql_insert_id() after you've inserted your record into tbl1.  
That will give you the ID of the auto increment column - see the note in 
the below link about bigint columns if your data type is bigint.


http://uk3.php.net/mysql_insert_id

Hope this helps,
Andy

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

[Tom Nugent]

Hi Abdul-

you may want to check this thread out regarding the same issue...

http://www.webmasterworld.com/php/3565843.htm


On Apr 19, 2009, at 3:23 PM, abdulazeez alugo wrote:



Hi guys,

I'm having a really terrible problem with my mysql coding and I'ld  
appreciate any help I can get on this cause it's really driving me  
crazy.


I have two tables and I've succeeded in creating a one to many  
relationship between both table but the problem I'm having is that I  
dont know how to put the value of the foreign key into the table.  
The tales are as below:


N.B: I'm using php-mysql

//Table 1

$result= mysql_query(CREATE TABLE tbl1 (tbl1_id INT UNSIGNED NOT  
NULL AUTO_INCREMENT,


 
PRIMARY KEY(tbl1_id),


 
entrytitle VARCHAR(50),


  
entrytext TEXT) ENGINE=InnoDB)




//Table2

$result = mysql_query(CREATE TABLE tbl2 (tbl2_id INT UNSIGNED NOT  
NULL AUTO_INCREMENT,


 
PRIMARY KEY(tbl2_id),


 
tbl1_id INT UNSIGNED NOT NULL,


 
name VARCHAR(50),


 
title VARCHAR(50),


  
comment TEXT,


  
INDEX (tbl1_id),


  
FOREIGN KEY(tbl1_id) REFERENCES tbl1(tbl1_id))ENGINE=InnoDB)




Now I have successfully created a relationship between the two  
tables but how do I make sure the value of tbl1_id in tbl1 is equal  
to the value of tbl1_id in tbl2???


Thanks in anicipation of your useful suggestions.



Alugo Abdulazeez.

www.frangeovic.com

_
Invite your mail contacts to join your friends list with Windows  
Live Spaces. It's easy!

http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us



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

[abdulazeez alugo]

 

 Date: Sun, 19 Apr 2009 23:19:56 +0100
 From: andy-li...@networkmail.eu
 To: defati...@hotmail.com
 CC: mysql@lists.mysql.com
 Subject: Re: Need help with mysql prob
 
 Hi Alugo,
  Hi Andy,
 
  Thanks for your prompt response. However, since tbl1_id has an 
  auto_increment value in tbl1, mysql is actually generating the values for 
  it automatically. 
  
 
 Ah, I see your point. I'm guessing by your code you're using PHP? If 
 so call mysql_insert_id() after you've inserted your record into tbl1. 
 That will give you the ID of the auto increment column - see the note in 
 the below link about bigint columns if your data type is bigint.
 
 http://uk3.php.net/mysql_insert_id
 
 Hope this helps,
 Andy

 

Yes I'm using PHP. Can it be something like

function newPost_tbl1($id, $entry, $text)

{

$conn;

$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)

 VALUES ('$id','$entry', $text)', 
$conn);

$tbl1_id = mysql_insert_id($conn);

}

 

Is the above code the best way to go about it?

Thanks in advance.

_
Drag n’ drop—Get easy photo sharing with Windows Live™ Photos.

http://www.microsoft.com/windows/windowslive/products/photos.aspx

Re: need help with query...

[Jason Pruim]

On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:

I hate when somebody put in Subject line something like I just did  
but after 15 minutes to try to be specific just with one short  
sentence - I gave up. So, you can hate me - I understand (though,  
help with my problem too) :-)



I have let say 3 tables people, organization, addresses. and they  
are linked to each other with column person_id.
to select person by specified person_id it's not big deal, really  
simple. though, I wonder how can I select records from these three  
tables but somehow marked which table it belongs?


e.g.

select p.first_name, p.last_name, p.status, p.date_registered,  
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,  
a.city, a.state, a.zip

from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and  
a.person_id=p.person_id


I need somehow, together with result data, info which table data  
belogs?


e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}

thanks for any help.

-afan



It may not be the best option, but when I've done that type of stuff  
in the past, I add another field to the database and call it like  
List and put People in the people database. and then you could  
just query the field List and display it how ever you needed.



--
Jason Pruim
japr...@raoset.com
616.399.2355

Re: need help with query...

[Andy Shellam]

Hi Afan

Why not prefix your field names with the table name?

select
 p.first_name AS person_first_name,
 p.last_name AS person_last_name,
 p.status AS person_status,
 p.date_registered AS person_date_registered,
 o.org_id AS organization_org_id,
 o.org_name AS organization_org_name,
 o.org_department AS organization_org_department,
 a.addres1 AS addresses_address1,
 a.address2 AS addresses_address2,
 a.city AS addresses_city,
 a.state AS addresses_state,
 a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and 
a.person_id=p.person_id


Then in PHP (which I guess you're using from your example) do something 
like:


// Get every record from the database ($result is your MySQL result from 
mysql_query)

while ($row = mysql_fetch_assoc($result))
{
   $result = Array();

   // Run through each field in the row
   foreach ($row as $field = $value)
   {
  // Split the field into 2 segments split by _
  $fieldSplit = explode('_', $field, 1);

  // $fieldSplit will be, for example, Array(0 = 'person', 1 = 
'first_name')


  $result[$fieldSplit[0]][$fieldSplit[1]] = $value;

  // Now you should be able to access the person's first name using 
$result['person']['first_name']

   }
}

This code may not be perfect as I've just typed it out from memory so it 
may take a bit of tweaking.


Thanks,
Andy

Jason Pruim wrote:


On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:

I hate when somebody put in Subject line something like I just did 
but after 15 minutes to try to be specific just with one short 
sentence - I gave up. So, you can hate me - I understand (though, 
help with my problem too) :-)



I have let say 3 tables people, organization, addresses. and they are 
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really 
simple. though, I wonder how can I select records from these three 
tables but somehow marked which table it belongs?


e.g.

select p.first_name, p.last_name, p.status, p.date_registered, 
o.org_id, o.org_name, o.org_department, a.addres1, a.address2, 
a.city, a.state, a.zip

from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and 
a.person_id=p.person_id


I need somehow, together with result data, info which table data belogs?

e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}

thanks for any help.

-afan



It may not be the best option, but when I've done that type of stuff 
in the past, I add another field to the database and call it like 
List and put People in the people database. and then you could 
just query the field List and display it how ever you needed.



--
Jason Pruim
japr...@raoset.com
616.399.2355






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

[Jerry Schwartz]

-Original Message-
From: Lamp Lists [mailto:lamp.li...@yahoo.com]
Sent: Wednesday, December 17, 2008 2:57 PM
To: mysql@lists.mysql.com
Subject: need help with query...

...snip...

I have let say 3 tables people, organization, addresses. and they are
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?

[JS] Admittedly I'm not really good with this stuff, but I think this works:

SELECT 'table1' AS tablename, person_id FROM table1 WHERE person_id = 123
UNION
SELECT 'table2', person_id FROM table2 WHERE person_id = 123
UNION
SELECT 'table3', person_id FROM table2 WHERE person_id = 123;

That would give you all of the tables in which a particular person_id is
found.

I don't know if this suits your needs.


Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.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 with query...

[Jerry Schwartz]

-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
  p.first_name AS person_first_name,
  p.last_name AS person_last_name,
  p.status AS person_status,
  p.date_registered AS person_date_registered,
  o.org_id AS organization_org_id,
  o.org_name AS organization_org_name,
  o.org_department AS organization_org_department,
  a.addres1 AS addresses_address1,
  a.address2 AS addresses_address2,
  a.city AS addresses_city,
  a.state AS addresses_state,
  a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id

[JS] That would only retrieve a person_id if it is all three tables. I'm not
sure that's what is wanted.




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

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id

Then in PHP (which I guess you're using from your example) do something like:

// Get every record from the database ($result is your MySQL result from 
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
   $result = Array();

   // Run through each field in the row
   foreach ($row as $field = $value)
   {
  // Split the field into 2 segments split by _
  $fieldSplit = explode('_', $field, 1);

  // $fieldSplit will be, for example, Array(0 = 'person', 1 = 
'first_name')

  $result[$fieldSplit[0]][$fieldSplit[1]] = $value;

  // Now you should be able to access the person's first name using 
$result['person']['first_name']
   }
}

This code may not be perfect as I've just typed it out from memory so it may 
take a bit of tweaking.

Thanks,
Andy




Hi Andy,
the reason I can't use this because fields (columns) in select statement 
(p.first_name, p.last_name,...) are actually dynamically created. In my project 
different client will select different fields to be shown. 99% will select 
first_name, and last_name, but some don't care about date_registered, some will 
need more org data... 

actually, it will be more this way:

SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...

where 
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip

I hope I'm more clear now?

Though, I can do something as you suggested while creating $selected_fields
:-)

Thanks




















Jason Pruim wrote:
 
 On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
 
 I hate when somebody put in Subject line something like I just did but after 
 15 minutes to try to be specific just with one short sentence - I gave up. 
 So, you can hate me - I understand (though, help with my problem too) :-)
 
 
 I have let say 3 tables people, organization, addresses. and they are linked 
 to each other with column person_id.
 to select person by specified person_id it's not big deal, really simple. 
 though, I wonder how can I select records from these three tables but 
 somehow marked which table it belongs?
 
 e.g.
 
 select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, 
 o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
 from people p, organization o, addresses a
 where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
 
 I need somehow, together with result data, info which table data belogs?
 
 e.g.
 not exactly this way but something like this:
 $data = array(
 'people' = array('lamp', 'lists', 1, '2008-12-12'),
 'organization' = array(56, 'Lamp List', 'web'),
 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
 }
 
 thanks for any help.
 
 -afan
 
 
 It may not be the best option, but when I've done that type of stuff in the 
 past, I add another field to the database and call it like List and put 
 People in the people database. and then you could just query the field List 
 and display it how ever you needed.
 
 
 -- Jason Pruim
 japr...@raoset.com
 616.399.2355
 
 
 
 



  

Re: need help with query...

[Andy Shellam]

Jerry Schwartz wrote:

-Original Message-
From: Andy Shellam [mailto:andy-li...@networkmail.eu]
Sent: Wednesday, December 17, 2008 3:29 PM
To: Lamp Lists
Cc: mysql@lists.mysql.com
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
 p.first_name AS person_first_name,
 p.last_name AS person_last_name,
 p.status AS person_status,
 p.date_registered AS person_date_registered,
 o.org_id AS organization_org_id,
 o.org_name AS organization_org_name,
 o.org_department AS organization_org_department,
 a.addres1 AS addresses_address1,
 a.address2 AS addresses_address2,
 a.city AS addresses_city,
 a.state AS addresses_state,
 a.zip AS addresses_zip


from people p, organization o, addresses a
  

where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id



[JS] That would only retrieve a person_id if it is all three tables. I'm not
sure that's what is wanted.
  


That was the original query as specified by the original poster - I just 
added the AS xxx to each field he'd selected.



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

[Andy Shellam]

Hi,



Hi Andy,
the reason I can't use this because fields (columns) in select 
statement (p.first_name, p.last_name,...) are actually dynamically 
created. In my project different client will select different fields 
to be shown. 99% will select first_name, and last_name, but some don't 
care about date_registered, some will need more org data...


actually, it will be more this way:

SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...

where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, 
a.city, a.state, a.zip

or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip


So just tag AS table_field_name to each field when you're building 
your list of $selected_fields - e.g.


$selected_fields = p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name


You don't have to use the full table name either - for example in the 
following statement, you would then access the data using 
$result['p']['first_name'];


$selected_fields = p.first_name AS p_first_name, p.last_name AS 
p_last_name, o.org_name AS o_org_name


This approach is actually easier if you're creating the query 
dynamically, because you don't have to manually type a load of AS xxx 
statements after every field.


I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.


Andy

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

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...

Hi,

 
 Hi Andy,
 the reason I can't use this because fields (columns) in select statement 
 (p.first_name, p.last_name,...) are actually dynamically created. In my 
 project different client will select different fields to be shown. 99% will 
 select first_name, and last_name, but some don't care about date_registered, 
 some will need more org data...
 
 actually, it will be more this way:
 
 SELECT {$selected_fields} FROM people p, organization o. addresses a
 WHERE ...
 
 where
 $selected_fields = p.first_name, p.last_name, o.org_name
 or
 $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
 a.state, a.zip
 or
 $selected_fields = o.org_name, a.address, a.city, a.state, a.zip

So just tag AS table_field_name to each field when you're building your list 
of $selected_fields - e.g.

$selected_fields = p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name

You don't have to use the full table name either - for example in the following 
statement, you would then access the data using $result['p']['first_name'];

$selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name, 
o.org_name AS o_org_name

This approach is actually easier if you're creating the query dynamically, 
because you don't have to manually type a load of AS xxx statements after 
every field.

I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.

Andy




Yup! That'll do it!

Thanks Andy
;-)



  

Re: Need Help

[Jim Lyons]

what rate of pay are you offering?

On Mon, Nov 24, 2008 at 5:13 AM, Tanveer Bhurani
[EMAIL PROTECTED]wrote:

 Dear All,

 I am looking for help as I want to make a website like orkut.

 can u plz help me in designing the Data Table and Queries

 --
 Thanks  Regards,
 Tanveer Bhurani
 The Bend on the road is not the end of the Road..., Unless you forget
 to make the turn




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

Re: Need Help Migrating DB from MySQL 5.0.x to MySQL 4.0.x

[Werner D.]

Shaun Adams schrieb:
 When I perform a dump in mysql5 to  mysql 4 DB, I get the error (below).
 Does anyone know how I can resolve this?
 
  
 
 QUERY (windows server from the cmd prompt)
 
 mysqldump --lock-tables --user=root [SOURCE DB] | mysql --user=[USERNAME]
 --password=[PASSWORD] --host=[HOST]  [TARGET DB]
 
  
 
 ERROR MESSAGE RETURNED
 
 ERROR 1193 (0) at line 23: Unknown system variable
 'character_set_client'
 
 mysqldump: Got errno 22 on write
 

try the mysqldump-option --compatible=mysql40


Werner

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

Re: Need help to query with timestamp in C++

[Kandy Wong]

Hi Saul,

I need to use C++ and I'm not writing a web application.
Thanks anyway.

Kandy

 I have done queries to the database in PHP with variables like month but
 easily can select from a range of time and data to produce the same
 results, the output goes directly to the web so if that is what you are
 seeking for, I can help with PHP.

 Saul

 Kandy Wong wrote:
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy




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




-- 
Kandy Wong
Scientific Programmer Analyst
TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics)
4004 Wesbrook Mall
Vancouver, BC, Canada, V6T 2A3
Phone: (604) 222- 1047 ext. 6193
Email: [EMAIL PROTECTED]

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

Re: Need help to query with timestamp in C++

[Dušan Pavlica]

Hi Kandy,

this could be the query you are looking for. It should return record 
with the closest timestamp to your required time:


(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1

HTH,
Dusan

Kandy Wong napsal(a):

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy


  


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

Re: Need help to query with timestamp in C++

[walter harms]

Kandy Wong wrote:
 Hi Saul,
 
 I need to use C++ and I'm not writing a web application.
 Thanks anyway.



you can do something like:

select min(abs(timediff(targettime,timestamp))) from table where 
condition ;

if you use the libmysql you can get the result as strings back (the method i 
prefer) and convert them
in what ever you need.

re,
 wh




 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy



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


 
 

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