Re: master-slave replication sync problems.

[Todd Lyons]

On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine  wrote:
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:

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

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

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

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

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

Database design help

[Neil Tompkins]

Hi

I've a soccer application consisting of managers, teams players and  
fixtures/results. Basically each manager will get points for each game  
which will depend on the result.


What would be the best table design bearing in mind that a manager can  
move to a different club.


My thought was to have a field in the fixtures/results table for the  
manager points but i think that I will also need a users field so that  
I can remember which points belong to which manager.


Is this the correct approach??

Cheers Neil

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

RE: FW: Even or Odds numbers

[Michael . Coll-Barth]

My apologies.  With your example; SELECT MOD( X, 2 ), it will do exactly as you 
say it will do.

-Original Message-
From: Christoph Boget [mailto:christoph.bo...@gmail.com] 
Sent: Tuesday, August 31, 2010 2:26 PM
To: Coll-Barth, Michael
Cc: mysql@lists.mysql.com
Subject: Re: FW: Even or Odds numbers

>> where X is your number (or column name).  If 0, it's even if
>> 1 it's odd.
> I think you mean, if it is non-zero, then it is odd.

If you're MODding using 2 as the second argument, it's always going to
be 0 or 1.  2 either divides in to the number evenly, having a
remainder of 0, or it'll have a remainder of 1.  The former indicates
the number is even, the latter odd.

thnx,
Christoph

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=michael.coll-ba...@verizonwireless.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: FW: Even or Odds numbers

[Christoph Boget]

>> where X is your number (or column name).  If 0, it's even if
>> 1 it's odd.
> I think you mean, if it is non-zero, then it is odd.

If you're MODding using 2 as the second argument, it's always going to
be 0 or 1.  2 either divides in to the number evenly, having a
remainder of 0, or it'll have a remainder of 1.  The former indicates
the number is even, the latter odd.

thnx,
Christoph

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

RE: Even or Odds numbers

[Daevid Vincent]

Not sure what you're trying to do, but if all you want to do is toggle
between two things (as in row color zebra stripes or something) then I
would suggest you not use some MOD() routine and instead just flip a
boolean.

For example in PHP just do:

  

just sayin'.

> -Original Message-
> From: Mike Blezien [mailto:mick...@frontiernet.net] 
> Sent: Tuesday, August 31, 2010 11:13 AM
> To: jcbo...@yahoo.com
> Cc: MySQL List
> Subject: Re: Even or Odds numbers
> 
> - Original Message - 
> From: "Christoph Boget" 
> To: "Mike Blezien" 
> Cc: "MySQL List" 
> Sent: Tuesday, August 31, 2010 1:06 PM
> Subject: Re: Even or Odds numbers
> 
> 
> >> is there a function, using MySQL 5.0v, that can detect if 
> a numerical value
> >> is either an Even or Odd number
> >
> > MOD()
> >
> > 
> http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.
> html#function_mod
> >
> > SELECT MOD( X, 2 )
> >
> > where X is your number (or column name).  If 0, it's even 
> if 1 it's odd.
> >
> > thnx,
> > Christoph
> >
> 
> Thanks that should do the trick. Appreciate the other 
> response too. Big help :)
> 
> Mike(mickalo)Blezien
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Thunder Rain Internet Publishing
> Custom Programming & Web Hosting Services
> http://www.thunder-rain.com/
> Office: 1.712.395.0670
> Skype Contact: cgimickalo
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Even or Odds numbers

[Mike Blezien]

- Original Message - 
From: "Christoph Boget" 

To: "Mike Blezien" 
Cc: "MySQL List" 
Sent: Tuesday, August 31, 2010 1:06 PM
Subject: Re: Even or Odds numbers



is there a function, using MySQL 5.0v, that can detect if a numerical value
is either an Even or Odd number


MOD()

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod

SELECT MOD( X, 2 )

where X is your number (or column name).  If 0, it's even if 1 it's odd.

thnx,
Christoph



Thanks that should do the trick. Appreciate the other response too. Big help :)

Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Custom Programming & Web Hosting Services
http://www.thunder-rain.com/
Office: 1.712.395.0670
Skype Contact: cgimickalo
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 



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

FW: Even or Odds numbers

[Michael . Coll-Barth]

 

> -Original Message-
> From: Christoph Boget [mailto:christoph.bo...@gmail.com] 

> http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.
html#function_mod
> 
> SELECT MOD( X, 2 )
> 
> where X is your number (or column name).  If 0, it's even if 
> 1 it's odd.

I think you mean, if it is non-zero, then it is odd.

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

Re: Even or Odds numbers

[Christoph Boget]

> is there a function, using MySQL 5.0v, that can detect if a numerical value
> is either an Even or Odd number

MOD()

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod

SELECT MOD( X, 2 )

where X is your number (or column name).  If 0, it's even if 1 it's odd.

thnx,
Christoph

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

RE: Even or Odds numbers

[Jay Blanchard]

[snip]
is there a function, using MySQL 5.0v, that can detect if a numerical
value is 
either an Even or Odd number
[/snip]

You can use modulus
http://www.roseindia.net/sql/mysql-example/mysql-modulus.shtml


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

Even or Odds numbers

[Mike Blezien]

Hello,

is there a function, using MySQL 5.0v, that can detect if a numerical value is 
either an Even or Odd number


Thanks,

Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Custom Programming & Web Hosting Services
http://www.thunder-rain.com/
Office: 1.712.395.0670
Skype Contact: cgimickalo
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 



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

RE: Performance Tunning

[Gavin Towey]

This is a good place to start:
https://launchpad.net/mysql-tuning-primer


-Original Message-
From: Johnny Withers [mailto:joh...@pixelated.net]
Sent: Tuesday, August 31, 2010 5:38 AM
To: Johan De Meersman
Cc: kranthi kiran; mysql@lists.mysql.com
Subject: Re: Performance Tunning

So, it's not just me that is stuck in this infinite loop? I thought I had
gone mad!

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

On Tue, Aug 31, 2010 at 5:23 AM, Johan De Meersman wrote:

> 1. Find out what is slow
> 2. Fix it
> 3. GOTO 1
>
> On Tue, Aug 31, 2010 at 11:13 AM, kranthi kiran
> wrote:
>
> > Hi All,
> >  In performance tunning what are the steps can follow,please help
> > me
> >
> > Thanks & Regards,
> > Kranthi kiran
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.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: Fast Index Creation and fill factor

[Dan Nelson]

In the last episode (Aug 30), Kyong Kim said:
> I've been going through the 5.1 manual and exploring the new features.
> 
> To add a secondary index to an existing table, InnoDB scans the table, and
> sorts the rows using memory buffers and temporary files in order by the
> value(s) of the secondary index key column(s).  The B-tree is then built
> in key-value order, which is more efficient than inserting rows into an
> index in random order with respect to the key values.  Because the B-tree
> nodes are split when they fill, building the index in this way results in
> a higher fill-factor for the index, making it more efficient for
> subsequent access.
> 
> I've been running some tests on SELECT using indexes built via plugin and
> builtin and the results are similar (though I think the test cases are
> flawed due to low cardinality of the column being indexed and queried).
>
> My question is...wouldn't the eventual fill factors be similar whether the
> pages are split during unordered B-tree build or ordered build?  I'm
> having a tough time visualizing this scenario.  Any insight into this will
> be much appreciated.

The big difference is how the leaf nodes get filled.  With sequential
insertion, the record that "overflows" a leaf block would go into a new
block anyway so there's no need to split the original one.  With random
insertion, the new record is probably not going to be at the beginning or
the end of the block, so the block gets split in half and each new block
starts out 50% full.

http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html

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

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

[SOLVED] Re: collation problems

[mysql]

Hi Ananda
that worked fine:

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

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

mysql>

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

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

mysql>

Thank you very much

suomi

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

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

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

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

mysql> desc suomi_contacts2;

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

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

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

mysql>

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





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

Re: collation problems

[mysql]

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

thanks so much.

suomi

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

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

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

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

mysql> desc suomi_contacts2;

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

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

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

mysql>

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





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

Re: collation problems

[Ananda Kumar]

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



2010/8/31 mysql 

> On 2010-08-31 15:17, Ananda Kumar wrote:
> > desc suomi_contacts2;
>
> mysql> desc suomi_contacts2;
>
> +--+--+--+-+---+-+
> | Field| Type | Null | Key | Default   |
> Extra   |
>
> +--+--+--+-+---+-+
> | name | text | YES  | | NULL  |
>|
> | firm | text | YES  | | NULL  |
>|
> | title| text | YES  | | NULL  |
>|
> | phone| text | YES  | | NULL  |
>|
> | phone_std| text | YES  | | NULL  |
>|
> | fax  | text | YES  | | NULL  |
>|
> | mail | text | YES  | | NULL  |
>|
> | standard_mail| text | YES  | | NULL  |
>|
> | comment  | text | YES  | | NULL  |
>|
> | status   | text | YES  | | NULL  |
>|
> | url  | text | YES  | | NULL  |
>|
> | businesscategory | text | YES  | | NULL  |
>|
> | address  | text | YES  | | NULL  |
>|
> | addon| text | YES  | | NULL  |
>|
> | givenname| text | YES  | | NULL  |
>|
> | history  | longtext | YES  | | NULL  |
>|
> | favorit  | text | YES  | | NULL  |
>|
> | last_update  | timestamp| NO   | | CURRENT_TIMESTAMP |
> on update CURRENT_TIMESTAMP |
> | task_link| int(11)  | YES  | | NULL  |
>|
> | counter  | int(10) unsigned | NO   | PRI | NULL  |
> auto_increment  |
>
> +--+--+--+-+---+-+
> 20 rows in set (0.00 sec)
>
> mysql>
>
> --
>  MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
>
>

Re: collation problems

[mysql]

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

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

mysql>

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

Re: collation problems

[Ananda Kumar]

can u please give the output of
desc suomi_contacts2;

2010/8/31 mysql 

> Hi Ananda
>
> table structure is:
>
> mysql> show full columns from suomi_contacts2;
>
> +--+--+---+--+-+---+-+-+-+
> | Field| Type | Collation | Null | Key |
> Default   | Extra   | Privileges
>  | Comment |
>
> +--+--+---+--+-+---+-+-+-+
> | name | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | firm | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | title| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | phone| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | phone_std| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | fax  | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | mail | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | standard_mail| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | comment  | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | status   | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | url  | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | businesscategory | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | address  | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | addon| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | givenname| text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | history  | longtext | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | favorit  | text | latin1_swedish_ci | YES  | |
> NULL  | |
> select,insert,update,references | |
> | last_update  | timestamp| NULL  | NO   | |
> CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
> select,insert,update,references | |
> | task_link| int(11)  | NULL  | YES  | |
> NULL  | |
> select,insert,update,references | |
> | counter  | int(10) unsigned | NULL  | NO   | PRI |
> NULL  | auto_increment  |
> select,insert,update,references | |
>
> +--+--+---+--+-+---+-+-+-+
> 20 rows in set (0.00 sec)
>
> mysql>
>
> suomi
>
> On 2010-08-31 14:52, Ananda Kumar wrote:
> > can u please list out the table structure...as collation can also be set
> > at column level
> > regards
> > anandkl
> >
> > On Tue, Aug 31, 2010 at 6:00 PM, mysql   > > wrote:
> >
> > Hi listers
> > mysql server here is
> >
> > mysql-server-5.1.48-2.fc13.x86_64
> >
> > this morning i created a message with a literal string in chinese in
> > it. the messages in the application i used are stored in a mysql
> > database, when you submit them, like in a "sent" folder.
> > With this chinese literal in it, i, however, got
> > ERROR 1271 (HY000): Illeg

Re: collation problems

[mysql]

Hi Ananda

table structure is:

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

mysql>

suomi

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

Re: collation problems

[Ananda Kumar]

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

regards
anandkl

On Tue, Aug 31, 2010 at 6:00 PM, mysql  wrote:

> Hi listers
> mysql server here is
>
> mysql-server-5.1.48-2.fc13.x86_64
>
> this morning i created a message with a literal string in chinese in it.
> the messages in the application i used are stored in a mysql database, when
> you submit them, like in a "sent" folder.
> With this chinese literal in it, i, however, got
> ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'
>
> when i sent the message.
>
> without the chinese literal, the message was stored in the mysql db
> correctly.
>
> i, then, changed the connection names to utf8 and collation to utf8_bin and
> then the session parameters looked like:
>
> mysql> set names "utf8";
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> set collation_connection = "utf8_bin";
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> show session variables like "character_set%";
> +--++
> | Variable_name| Value  |
> +--++
> | character_set_client | utf8   |
> | character_set_connection | utf8   |
> | character_set_database   | latin1 |
> | character_set_filesystem | binary |
> | character_set_results| utf8   |
> | character_set_server | latin1 |
> | character_set_system | utf8   |
> | character_sets_dir   | /usr/share/mysql/charsets/ |
> +--++
> 8 rows in set (0.00 sec)
>
> mysql> show session variables like "collation%";
> +--+---+
> | Variable_name| Value |
> +--+---+
> | collation_connection | utf8_bin  |
> | collation_database   | latin1_swedish_ci |
> | collation_server | latin1_swedish_ci |
> +--+---+
> 3 rows in set (0.00 sec)
>
> mysql>
>
> i thought, if you set the collation_connection to utf8_bin, you may send
> fairly everything to mysql.
>
>
> but still, when i tried to concat the new string (including the chinese
> characers) i got:
>
> mysql> update suomi_contacts2 set history = concat(now(), ' ', ''
> ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi
> Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever
> happen to you.\r\n> 葛斯克 愛德華 /  台北市八德路四段\r\n>\r\n\r\n \r\nsuomi\r\n', ' ',
> '--- ', history) where counter = 1127;
> ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'
>
>
>
> Question: What did i miss? what do i have to change?
>
> Thanks in advance
>
> suomi
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
>
>

Re: Performance Tunning

[Johnny Withers]

So, it's not just me that is stuck in this infinite loop? I thought I had
gone mad!

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

On Tue, Aug 31, 2010 at 5:23 AM, Johan De Meersman wrote:

> 1. Find out what is slow
> 2. Fix it
> 3. GOTO 1
>
> On Tue, Aug 31, 2010 at 11:13 AM, kranthi kiran
> wrote:
>
> > Hi All,
> >  In performance tunning what are the steps can follow,please help
> > me
> >
> > Thanks & Regards,
> > Kranthi kiran
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

collation problems

[mysql]

Hi listers
mysql server here is

mysql-server-5.1.48-2.fc13.x86_64

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

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

when i sent the message.

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


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


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

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

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

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

mysql>

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



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


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

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



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

Thanks in advance

suomi





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

Re: MySQL SUM on two columns

[Tompkins Neil]

Hi,

I also wondered, if there is any way to LIMIT the products.rating for each
language to say the top 25 ?

Cheers
Neil

On Tue, Aug 31, 2010 at 12:11 PM, Tompkins Neil <
neil.tompk...@googlemail.com> wrote:

> Perfect.  Exactly what I was looking for.
>
> Cheers John.
>
> Regards,
> Neil
>
> On Tue, Aug 31, 2010 at 12:06 PM, John Daisley  > wrote:
>
>> SELECT products_sales.language, SUM(products.rating) AS products_rating
>>
>> FROM products_sales
>> INNER JOIN products ON products_sales.products_id = products.products_id
>> WHERE products.enabled = 1
>> AND products_sales.language IN ('EN','ES')
>> GROUP BY products_sales.language
>>
>>
>> Will give the sum for each language.
>>
>> On 31 August 2010 11:42, Tompkins Neil wrote:
>>
>>> Hi,
>>>
>>> I've the following basic SUM for our products based on a rating.
>>>
>>> SELECT SUM(products.rating) AS products_rating
>>> FROM products_sales
>>> INNER JOIN products ON products_sales.products_id = products.products_id
>>> WHERE products.enabled = 1
>>> AND products_sales.language = 'EN'
>>>
>>> This works fine, however I also want to SUM the ES language within the
>>> same
>>> query.  Is this possible to do, or should I just query the table twice,
>>> replacing the language parameter ?
>>>
>>> Thanks.
>>>
>>> Neil
>>>
>>
>>
>>
>> --
>> John Daisley
>>
>> Certified MySQL 5 Database Administrator
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> Telephone: +44 (0)7918 621621
>> Email: john.dais...@butterflysystems.co.uk
>>
>
>

Re: Performance Tunning

[Jangita]

On 31/08/2010 12:23 p, Johan De Meersman wrote:

1. Find out what is slow
2. Fix it
3. GOTO 1

Good one Johan, Performance tuning depends alot on your table types, 
your server, the version of MySQL, how you client applications access 
your database, the size of your data, type of queries, indexes, data 
types: basically each setup is different.


This might be a good start
http://www.mysqlperformanceblog.com/

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

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

Re: MySQL SUM on two columns

[Tompkins Neil]

Perfect.  Exactly what I was looking for.

Cheers John.

Regards,
Neil

On Tue, Aug 31, 2010 at 12:06 PM, John Daisley
wrote:

> SELECT products_sales.language, SUM(products.rating) AS products_rating
>
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language IN ('EN','ES')
> GROUP BY products_sales.language
>
>
> Will give the sum for each language.
>
> On 31 August 2010 11:42, Tompkins Neil wrote:
>
>> Hi,
>>
>> I've the following basic SUM for our products based on a rating.
>>
>> SELECT SUM(products.rating) AS products_rating
>> FROM products_sales
>> INNER JOIN products ON products_sales.products_id = products.products_id
>> WHERE products.enabled = 1
>> AND products_sales.language = 'EN'
>>
>> This works fine, however I also want to SUM the ES language within the
>> same
>> query.  Is this possible to do, or should I just query the table twice,
>> replacing the language parameter ?
>>
>> Thanks.
>>
>> Neil
>>
>
>
>
> --
> John Daisley
>
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.dais...@butterflysystems.co.uk
>

Re: MySQL SUM on two columns

[John Daisley]

SELECT products_sales.language, SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language IN ('EN','ES')
GROUP BY products_sales.language


Will give the sum for each language.

On 31 August 2010 11:42, Tompkins Neil  wrote:

> Hi,
>
> I've the following basic SUM for our products based on a rating.
>
> SELECT SUM(products.rating) AS products_rating
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language = 'EN'
>
> This works fine, however I also want to SUM the ES language within the same
> query.  Is this possible to do, or should I just query the table twice,
> replacing the language parameter ?
>
> Thanks.
>
> Neil
>



-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

Re: MySQL SUM on two columns

[Tompkins Neil]

Sorry I don't want to SUM the varchar columns.  This is the parameter passed
that defines the two different queries.



On Tue, Aug 31, 2010 at 11:48 AM, misiaQ  wrote:

> I don't think that SUM will work for varchar columns.
> If I got your point right - you need to use WITH ROLLUP grouping
> http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
>
> see second example on that page.
>
> regards,
> m
>
> -Original Message-
> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
> Sent: Tuesday, August 31, 2010 11:43 AM
> To: [MySQL]
> Subject: MySQL SUM on two columns
>
> Hi,
>
> I've the following basic SUM for our products based on a rating.
>
> SELECT SUM(products.rating) AS products_rating
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language = 'EN'
>
> This works fine, however I also want to SUM the ES language within the same
> query.  Is this possible to do, or should I just query the table twice,
> replacing the language parameter ?
>
> Thanks.
>
> Neil
>
>
> --
> Saloon gier - to nas wyroznia!
> Sprawdz >>> http://linkint.pl/f27e0
>
>

RE: MySQL SUM on two columns

[misiaQ]

I don't think that SUM will work for varchar columns. 
If I got your point right - you need to use WITH ROLLUP grouping
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

see second example on that page.

regards,
m

-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] 
Sent: Tuesday, August 31, 2010 11:43 AM
To: [MySQL]
Subject: MySQL SUM on two columns

Hi,

I've the following basic SUM for our products based on a rating.

SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'

This works fine, however I also want to SUM the ES language within the same
query.  Is this possible to do, or should I just query the table twice,
replacing the language parameter ?

Thanks.

Neil


--
Saloon gier - to nas wyroznia!
Sprawdz >>> http://linkint.pl/f27e0


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

MySQL SUM on two columns

[Tompkins Neil]

Hi,

I've the following basic SUM for our products based on a rating.

SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'

This works fine, however I also want to SUM the ES language within the same
query.  Is this possible to do, or should I just query the table twice,
replacing the language parameter ?

Thanks.

Neil

Re: Performance Tunning

[Johan De Meersman]

1. Find out what is slow
2. Fix it
3. GOTO 1

On Tue, Aug 31, 2010 at 11:13 AM, kranthi kiran
wrote:

> Hi All,
>  In performance tunning what are the steps can follow,please help
> me
>
> Thanks & Regards,
> Kranthi kiran
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

kranthi kiran wants to chat

[kranthi kiran]

---

kranthi kiran wants to stay in better touch using some of Google's coolest new
products.

If you already have Gmail or Google Talk, visit:
http://mail.google.com/mail/b-7b9f42b217-2ea453372e-yAnBROFNjKN29DW37cRa_kmftE8
You'll need to click this link to be able to chat with kranthi kiran.

To get Gmail - a free email account from Google with over 2,800 megabytes of
storage - and chat with kranthi kiran, visit:
http://mail.google.com/mail/a-7b9f42b217-2ea453372e-yAnBROFNjKN29DW37cRa_kmftE8

Gmail offers:
- Instant messaging right inside Gmail
- Powerful spam protection
- Built-in search for finding your messages and a helpful way of organizing
  emails into "conversations"
- No pop-up ads or untargeted banners - just text ads and related information
  that are relevant to the content of your messages

All this, and its yours for free. But wait, there's more! By opening a Gmail
account, you also get access to Google Talk, Google's instant messaging
service:

http://www.google.com/talk/

Google Talk offers:
- Web-based chat that you can use anywhere, without a download
- A contact list that's synchronized with your Gmail account
- Free, high quality PC-to-PC voice calls when you download the Google Talk
  client

We're working hard to add new features and make improvements, so we might also
ask for your comments and suggestions periodically. We appreciate your help in
making our products even better!

Thanks,
The Google Team

To learn more about Gmail and Google Talk, visit:
http://mail.google.com/mail/help/about.html
http://www.google.com/talk/about.html

(If clicking the URLs in this message does not work, copy and paste them into
the address bar of your browser).

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

kranthi kiran wants to chat

[kranthi kiran]

---

kranthi kiran wants to stay in better touch using some of Google's coolest new
products.

If you already have Gmail or Google Talk, visit:
http://mail.google.com/mail/b-7b9f42b217-34446b6de3-Dp2zHVPU5AhNWkYs9pgMnHMgaM0
You'll need to click this link to be able to chat with kranthi kiran.

To get Gmail - a free email account from Google with over 2,800 megabytes of
storage - and chat with kranthi kiran, visit:
http://mail.google.com/mail/a-7b9f42b217-34446b6de3-Dp2zHVPU5AhNWkYs9pgMnHMgaM0

Gmail offers:
- Instant messaging right inside Gmail
- Powerful spam protection
- Built-in search for finding your messages and a helpful way of organizing
  emails into "conversations"
- No pop-up ads or untargeted banners - just text ads and related information
  that are relevant to the content of your messages

All this, and its yours for free. But wait, there's more! By opening a Gmail
account, you also get access to Google Talk, Google's instant messaging
service:

http://www.google.com/talk/

Google Talk offers:
- Web-based chat that you can use anywhere, without a download
- A contact list that's synchronized with your Gmail account
- Free, high quality PC-to-PC voice calls when you download the Google Talk
  client

We're working hard to add new features and make improvements, so we might also
ask for your comments and suggestions periodically. We appreciate your help in
making our products even better!

Thanks,
The Google Team

To learn more about Gmail and Google Talk, visit:
http://mail.google.com/mail/help/about.html
http://www.google.com/talk/about.html

(If clicking the URLs in this message does not work, copy and paste them into
the address bar of your browser).

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

RE: Performance Tunning

[kranthi kiran]

Hi All,
  In performance tunning what are the steps can follow,please help
me

Thanks & Regards,
Kranthi kiran