Pivot Query in MySQL

[VR Venugopal Rao]

Thanks Jay, I got the solution.
Thanks for all the members responding.

With warm regards,
VR Venugopal Rao 

-Original Message-
From: Jay Blanchard [mailto:jblanch...@pocket.com] 
Sent: 28 April 2010 16:58
To: VR Venugopal Rao; MySQL
Subject: RE: Pivot Query in 

[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of 

Project Code   R&D   STP

1007304--04---04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896



__

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupp...@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


__

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
__


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

Re: compiling errors on mysql 5.1.46 on win64

[Zardosht Kasheff]

I have verified that 5.1.45 does NOT have this problem.

Here is another question: how are the windows MySQL builds produced?
It is not from the instructions provided on mysql.com, otherwise these
problems would be caught.

Thanks
-Zardosht

On Wed, Apr 28, 2010 at 5:59 PM, Zardosht Kasheff  wrote:
> inlining responses
>
> On Wed, Apr 28, 2010 at 4:58 PM, Michael Dykman  wrote:
>> I'm not a windows guy and haven't done C in a Microsoft environment
>> since XP was just released, so don't take my suggestions as
>> authoritative but:
>>
>> Are you running the batch file from within Visual Studio or are you
>> running the batch file at the command line?
> from within VS08
>
>>
>> Which version of the MS compiler are you using?
> Visual Studio 08
>
>> Which version of MySQL are you trying to compile?  It looks from your
>> prefixes that you might be building a high-availability release..
>> Where did the sources come from?
> 5.1.46, 5.1.43 compiles fine
>
>>
>> Is the InnoDB plugin the first thing it tries to build?  That would
>> seem odd...  I would expect to see the core compiled before the
>> plugins..
>>
> No, I build the entire solution
>
>> If your problems are focussed exclusively on the InnoDB plugin, then
>> perhaps you want to seek some advice from the InnoDB guys who provide
>> it.
>>
>>
>>  - michael dykman
>>
>> On Wed, Apr 28, 2010 at 4:34 PM, Zardosht Kasheff  wrote:
>>> I am using Visual Studio 2008. I run "win\configure" followed by
>>> "win\build_vs9_x64.bat", and then open the generated MySQL.sln from
>>> Visual Studio 2008. Do Build->Build Solution, and I get the errors
>>>
>>> On Wed, Apr 28, 2010 at 4:31 PM, Michael Dykman  wrote:
 What compiler are you using?  Is this being done via a make file or
 are you just trying to compile from a default MS Developer Studio?

 On Wed, Apr 28, 2010 at 3:51 PM, Zardosht Kasheff  
 wrote:
> I was told I should ask this on this list.
>
> Any ideas?
> Thanks
> -Zardosht
>
>
> -- Forwarded message --
> From: Zardosht Kasheff 
> Date: Fri, Apr 23, 2010 at 9:01 PM
> Subject: compiling errors on mysql 5.1.46 on win64
> To: wi...@lists.mysql.com
>
>
> Hello all,
>
> I know this is a win32 list, but I could not find a better list to ask
> this question. Running Visual Studio 2008, I do the following:
>
> win\configure
> win\build_vs9.bat
>
> Then I open MySQL.sln, run Build Solution, and I get the following
> errors in the innodb plugin. Any ideas what is wrong?
>
> Also, how does MySQL build the community 5.1.46 windows release?
>
> Thanks
> -Zardosht
>
> 1>-- Build started: Project: ha_innodb_plugin, Configuration:
> Release x64 --
> 1>Compiling...
> 1>ha0ha.c
> 1>.\ha\ha0ha.c(419) : error C2143: syntax error : missing ';' before 
> 'type'
> 1>.\ha\ha0ha.c(445) : error C2065: 'n_bufs' : undeclared identifier
> 1>.\ha\ha0ha.c(445) : warning C4244: '=' : conversion from 'ulint' to
> 'int', possible loss of data
> 1>.\ha\ha0ha.c(448) : error C2065: 'n_bufs' : undeclared identifier
> 1>.\ha\ha0ha.c(452) : error C2065: 'n_bufs' : undeclared identifier
> 1>buf0flu.c
> 1>.\buf\buf0flu.c(160) : error C2143: syntax error : missing ';' before 
> 'type'
> 1>.\buf\buf0flu.c(161) : error C2143: syntax error : missing ';' before 
> 'const'
> 1>.\buf\buf0flu.c(169) : error C2065: 'b2' : undeclared identifier
> 1>.\buf\buf0flu.c(169) : error C2223: left of '->oldest_modification'
> must point to struct/union
> 1>.\buf\buf0flu.c(170) : error C2065: 'b1' : undeclared identifier
> 1>.\buf\buf0flu.c(170) : error C2223: left of '->oldest_modification'
> must point to struct/union
> 1>.\buf\buf0flu.c(174) : error C2065: 'b2' : undeclared identifier
> 1>.\buf\buf0flu.c(174) : error C2223: left of '->oldest_modification'
> must point to struct/union
> 1>.\buf\buf0flu.c(175) : error C2065: 'b1' : undeclared identifier
> 1>.\buf\buf0flu.c(175) : error C2223: left of '->oldest_modification'
> must point to struct/union
> 1>.\buf\buf0flu.c(180) : error C2065: 'b2' : undeclared identifier
> 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
> struct/union
> 1>.\buf\buf0flu.c(180) : error C2065: 'b1' : undeclared identifier
> 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
> struct/union
> 1>.\buf\buf0flu.c(183) : error C2065: 'b2' : undeclared identifier
> 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
> struct/union
> 1>.\buf\buf0flu.c(183) : error C2065: 'b1' : undeclared identifier
> 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
> struct/union
> 1>.\buf\buf0flu.c(183) : fatal error C1903: unable to recover from
> previous error(s); stopping compilation
> 1>Generati

Re: compiling errors on mysql 5.1.46 on win64

[Zardosht Kasheff]

inlining responses

On Wed, Apr 28, 2010 at 4:58 PM, Michael Dykman  wrote:
> I'm not a windows guy and haven't done C in a Microsoft environment
> since XP was just released, so don't take my suggestions as
> authoritative but:
>
> Are you running the batch file from within Visual Studio or are you
> running the batch file at the command line?
from within VS08

>
> Which version of the MS compiler are you using?
Visual Studio 08

> Which version of MySQL are you trying to compile?  It looks from your
> prefixes that you might be building a high-availability release..
> Where did the sources come from?
5.1.46, 5.1.43 compiles fine

>
> Is the InnoDB plugin the first thing it tries to build?  That would
> seem odd...  I would expect to see the core compiled before the
> plugins..
>
No, I build the entire solution

> If your problems are focussed exclusively on the InnoDB plugin, then
> perhaps you want to seek some advice from the InnoDB guys who provide
> it.
>
>
>  - michael dykman
>
> On Wed, Apr 28, 2010 at 4:34 PM, Zardosht Kasheff  wrote:
>> I am using Visual Studio 2008. I run "win\configure" followed by
>> "win\build_vs9_x64.bat", and then open the generated MySQL.sln from
>> Visual Studio 2008. Do Build->Build Solution, and I get the errors
>>
>> On Wed, Apr 28, 2010 at 4:31 PM, Michael Dykman  wrote:
>>> What compiler are you using?  Is this being done via a make file or
>>> are you just trying to compile from a default MS Developer Studio?
>>>
>>> On Wed, Apr 28, 2010 at 3:51 PM, Zardosht Kasheff  
>>> wrote:
 I was told I should ask this on this list.

 Any ideas?
 Thanks
 -Zardosht


 -- Forwarded message --
 From: Zardosht Kasheff 
 Date: Fri, Apr 23, 2010 at 9:01 PM
 Subject: compiling errors on mysql 5.1.46 on win64
 To: wi...@lists.mysql.com


 Hello all,

 I know this is a win32 list, but I could not find a better list to ask
 this question. Running Visual Studio 2008, I do the following:

 win\configure
 win\build_vs9.bat

 Then I open MySQL.sln, run Build Solution, and I get the following
 errors in the innodb plugin. Any ideas what is wrong?

 Also, how does MySQL build the community 5.1.46 windows release?

 Thanks
 -Zardosht

 1>-- Build started: Project: ha_innodb_plugin, Configuration:
 Release x64 --
 1>Compiling...
 1>ha0ha.c
 1>.\ha\ha0ha.c(419) : error C2143: syntax error : missing ';' before 'type'
 1>.\ha\ha0ha.c(445) : error C2065: 'n_bufs' : undeclared identifier
 1>.\ha\ha0ha.c(445) : warning C4244: '=' : conversion from 'ulint' to
 'int', possible loss of data
 1>.\ha\ha0ha.c(448) : error C2065: 'n_bufs' : undeclared identifier
 1>.\ha\ha0ha.c(452) : error C2065: 'n_bufs' : undeclared identifier
 1>buf0flu.c
 1>.\buf\buf0flu.c(160) : error C2143: syntax error : missing ';' before 
 'type'
 1>.\buf\buf0flu.c(161) : error C2143: syntax error : missing ';' before 
 'const'
 1>.\buf\buf0flu.c(169) : error C2065: 'b2' : undeclared identifier
 1>.\buf\buf0flu.c(169) : error C2223: left of '->oldest_modification'
 must point to struct/union
 1>.\buf\buf0flu.c(170) : error C2065: 'b1' : undeclared identifier
 1>.\buf\buf0flu.c(170) : error C2223: left of '->oldest_modification'
 must point to struct/union
 1>.\buf\buf0flu.c(174) : error C2065: 'b2' : undeclared identifier
 1>.\buf\buf0flu.c(174) : error C2223: left of '->oldest_modification'
 must point to struct/union
 1>.\buf\buf0flu.c(175) : error C2065: 'b1' : undeclared identifier
 1>.\buf\buf0flu.c(175) : error C2223: left of '->oldest_modification'
 must point to struct/union
 1>.\buf\buf0flu.c(180) : error C2065: 'b2' : undeclared identifier
 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
 struct/union
 1>.\buf\buf0flu.c(180) : error C2065: 'b1' : undeclared identifier
 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
 struct/union
 1>.\buf\buf0flu.c(183) : error C2065: 'b2' : undeclared identifier
 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
 struct/union
 1>.\buf\buf0flu.c(183) : error C2065: 'b1' : undeclared identifier
 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
 struct/union
 1>.\buf\buf0flu.c(183) : fatal error C1903: unable to recover from
 previous error(s); stopping compilation
 1>Generating Code...
 1>Build log was saved at
 "file://c:\mysql-5.1.46\storage\innodb_plugin\ha_innodb_plugin.dir\Release\BuildLog.htm"
 1>ha_innodb_plugin - 23 error(s), 1 warning(s)
 == Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==

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

Re: compiling errors on mysql 5.1.46 on win64

[Michael Dykman]

I'm not a windows guy and haven't done C in a Microsoft environment
since XP was just released, so don't take my suggestions as
authoritative but:

Are you running the batch file from within Visual Studio or are you
running the batch file at the command line?

Which version of the MS compiler are you using?

Which version of MySQL are you trying to compile?  It looks from your
prefixes that you might be building a high-availability release..
Where did the sources come from?

Is the InnoDB plugin the first thing it tries to build?  That would
seem odd...  I would expect to see the core compiled before the
plugins..

If your problems are focussed exclusively on the InnoDB plugin, then
perhaps you want to seek some advice from the InnoDB guys who provide
it.


 - michael dykman

On Wed, Apr 28, 2010 at 4:34 PM, Zardosht Kasheff  wrote:
> I am using Visual Studio 2008. I run "win\configure" followed by
> "win\build_vs9_x64.bat", and then open the generated MySQL.sln from
> Visual Studio 2008. Do Build->Build Solution, and I get the errors
>
> On Wed, Apr 28, 2010 at 4:31 PM, Michael Dykman  wrote:
>> What compiler are you using?  Is this being done via a make file or
>> are you just trying to compile from a default MS Developer Studio?
>>
>> On Wed, Apr 28, 2010 at 3:51 PM, Zardosht Kasheff  wrote:
>>> I was told I should ask this on this list.
>>>
>>> Any ideas?
>>> Thanks
>>> -Zardosht
>>>
>>>
>>> -- Forwarded message --
>>> From: Zardosht Kasheff 
>>> Date: Fri, Apr 23, 2010 at 9:01 PM
>>> Subject: compiling errors on mysql 5.1.46 on win64
>>> To: wi...@lists.mysql.com
>>>
>>>
>>> Hello all,
>>>
>>> I know this is a win32 list, but I could not find a better list to ask
>>> this question. Running Visual Studio 2008, I do the following:
>>>
>>> win\configure
>>> win\build_vs9.bat
>>>
>>> Then I open MySQL.sln, run Build Solution, and I get the following
>>> errors in the innodb plugin. Any ideas what is wrong?
>>>
>>> Also, how does MySQL build the community 5.1.46 windows release?
>>>
>>> Thanks
>>> -Zardosht
>>>
>>> 1>-- Build started: Project: ha_innodb_plugin, Configuration:
>>> Release x64 --
>>> 1>Compiling...
>>> 1>ha0ha.c
>>> 1>.\ha\ha0ha.c(419) : error C2143: syntax error : missing ';' before 'type'
>>> 1>.\ha\ha0ha.c(445) : error C2065: 'n_bufs' : undeclared identifier
>>> 1>.\ha\ha0ha.c(445) : warning C4244: '=' : conversion from 'ulint' to
>>> 'int', possible loss of data
>>> 1>.\ha\ha0ha.c(448) : error C2065: 'n_bufs' : undeclared identifier
>>> 1>.\ha\ha0ha.c(452) : error C2065: 'n_bufs' : undeclared identifier
>>> 1>buf0flu.c
>>> 1>.\buf\buf0flu.c(160) : error C2143: syntax error : missing ';' before 
>>> 'type'
>>> 1>.\buf\buf0flu.c(161) : error C2143: syntax error : missing ';' before 
>>> 'const'
>>> 1>.\buf\buf0flu.c(169) : error C2065: 'b2' : undeclared identifier
>>> 1>.\buf\buf0flu.c(169) : error C2223: left of '->oldest_modification'
>>> must point to struct/union
>>> 1>.\buf\buf0flu.c(170) : error C2065: 'b1' : undeclared identifier
>>> 1>.\buf\buf0flu.c(170) : error C2223: left of '->oldest_modification'
>>> must point to struct/union
>>> 1>.\buf\buf0flu.c(174) : error C2065: 'b2' : undeclared identifier
>>> 1>.\buf\buf0flu.c(174) : error C2223: left of '->oldest_modification'
>>> must point to struct/union
>>> 1>.\buf\buf0flu.c(175) : error C2065: 'b1' : undeclared identifier
>>> 1>.\buf\buf0flu.c(175) : error C2223: left of '->oldest_modification'
>>> must point to struct/union
>>> 1>.\buf\buf0flu.c(180) : error C2065: 'b2' : undeclared identifier
>>> 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
>>> struct/union
>>> 1>.\buf\buf0flu.c(180) : error C2065: 'b1' : undeclared identifier
>>> 1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
>>> struct/union
>>> 1>.\buf\buf0flu.c(183) : error C2065: 'b2' : undeclared identifier
>>> 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
>>> struct/union
>>> 1>.\buf\buf0flu.c(183) : error C2065: 'b1' : undeclared identifier
>>> 1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
>>> struct/union
>>> 1>.\buf\buf0flu.c(183) : fatal error C1903: unable to recover from
>>> previous error(s); stopping compilation
>>> 1>Generating Code...
>>> 1>Build log was saved at
>>> "file://c:\mysql-5.1.46\storage\innodb_plugin\ha_innodb_plugin.dir\Release\BuildLog.htm"
>>> 1>ha_innodb_plugin - 23 error(s), 1 warning(s)
>>> == Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>>>
>>>
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdyk...@gmail.com
>>
>>  May the Source be with you.
>>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

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

Fwd: compiling errors on mysql 5.1.46 on win64

[Zardosht Kasheff]

I was told I should ask this on this list.

Any ideas?
Thanks
-Zardosht


-- Forwarded message --
From: Zardosht Kasheff 
Date: Fri, Apr 23, 2010 at 9:01 PM
Subject: compiling errors on mysql 5.1.46 on win64
To: wi...@lists.mysql.com


Hello all,

I know this is a win32 list, but I could not find a better list to ask
this question. Running Visual Studio 2008, I do the following:

win\configure
win\build_vs9.bat

Then I open MySQL.sln, run Build Solution, and I get the following
errors in the innodb plugin. Any ideas what is wrong?

Also, how does MySQL build the community 5.1.46 windows release?

Thanks
-Zardosht

1>-- Build started: Project: ha_innodb_plugin, Configuration:
Release x64 --
1>Compiling...
1>ha0ha.c
1>.\ha\ha0ha.c(419) : error C2143: syntax error : missing ';' before 'type'
1>.\ha\ha0ha.c(445) : error C2065: 'n_bufs' : undeclared identifier
1>.\ha\ha0ha.c(445) : warning C4244: '=' : conversion from 'ulint' to
'int', possible loss of data
1>.\ha\ha0ha.c(448) : error C2065: 'n_bufs' : undeclared identifier
1>.\ha\ha0ha.c(452) : error C2065: 'n_bufs' : undeclared identifier
1>buf0flu.c
1>.\buf\buf0flu.c(160) : error C2143: syntax error : missing ';' before 'type'
1>.\buf\buf0flu.c(161) : error C2143: syntax error : missing ';' before 'const'
1>.\buf\buf0flu.c(169) : error C2065: 'b2' : undeclared identifier
1>.\buf\buf0flu.c(169) : error C2223: left of '->oldest_modification'
must point to struct/union
1>.\buf\buf0flu.c(170) : error C2065: 'b1' : undeclared identifier
1>.\buf\buf0flu.c(170) : error C2223: left of '->oldest_modification'
must point to struct/union
1>.\buf\buf0flu.c(174) : error C2065: 'b2' : undeclared identifier
1>.\buf\buf0flu.c(174) : error C2223: left of '->oldest_modification'
must point to struct/union
1>.\buf\buf0flu.c(175) : error C2065: 'b1' : undeclared identifier
1>.\buf\buf0flu.c(175) : error C2223: left of '->oldest_modification'
must point to struct/union
1>.\buf\buf0flu.c(180) : error C2065: 'b2' : undeclared identifier
1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
struct/union
1>.\buf\buf0flu.c(180) : error C2065: 'b1' : undeclared identifier
1>.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to
struct/union
1>.\buf\buf0flu.c(183) : error C2065: 'b2' : undeclared identifier
1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
struct/union
1>.\buf\buf0flu.c(183) : error C2065: 'b1' : undeclared identifier
1>.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to
struct/union
1>.\buf\buf0flu.c(183) : fatal error C1903: unable to recover from
previous error(s); stopping compilation
1>Generating Code...
1>Build log was saved at
"file://c:\mysql-5.1.46\storage\innodb_plugin\ha_innodb_plugin.dir\Release\BuildLog.htm"
1>ha_innodb_plugin - 23 error(s), 1 warning(s)
== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==

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

Using a join-table twice in one query

[Tom Worster]

Say tables a and b each have their own id column (primary key) and sundry
other columns. Table j has columns aid and bid to join a and b many-to-many.

Now, I want to select the joined a/b rows where the b rows are joined to (a
different set of) rows in a that meet condition x (which refers only to a).
Saying it in two sentences: Find the set Bx of rows in b joined to rows in a
that meet x. Now select a table of all joined a/b rows where the b rows are
in set Bx.

Using an IN-subquery to mimic the two-sentence formulation was very slow, as
the manual warns it can be:

SELECT ...
FROM b
INNER JOIN j ON j.bid=b.id
INNER JOIN a ON j.aid=a.id
WHERE b.id IN
  ( SELECT jx.bid 
FROM a ax
INNER JOIN j jx ON jx.aid=ax.id
WHERE x )

Joining b to a via j twice, once on the way out and again on the way home,
was very fast but only with STRAIGHT_JOIN:

SELECT STRAIGHT_JOIN ...
FROM a ax
JOIN j jx ON jx.aid=ax.id
JOIN b ON jx.bid=b.id
JOIN j ON j.bid=b.id
JOIN a ON j.aid=a.id
WHERE x

What other approaches should I consider?

(Is standard stuff in database text books?)



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

Re: Replication : request DELETE is not executed on slave

[Tom Worster]

it's worth a try -- the manual for 4.1 has the same text about ORDER BY in
section 14.7.

On 4/28/10 12:30 PM, "David Florella"  wrote:

> Hi, 
> 
> Thanks to you and everyone.
> 
> I will test the same request with the ORDER BY clause.
> 
> Regards, 
> 
> David. 
> 
> -Message d'origine-
> De : Mattia Merzi [mailto:mattia.me...@gmail.com]
> Envoyé : mercredi 28 avril 2010 17:54
> À : mysql@lists.mysql.com
> Objet : Re: Replication : request DELETE is not executed on slave
> 
> AFAIR you can use LIMIT with replication only if you use row-based
> replication (or mixed), that means that you must use mysql 5.1.
> 
> Greetings,
> 
> Mattia.
> 
> 
> 2010/4/28 Tom Worster :
>> 16.3.1.9. Replication and LIMIT
>> Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT
>> statements is not guaranteed, since the order of the rows affected is not
>> defined. Such statements can be replicated correctly only if they also
>> contain an ORDER BY clause.



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

Re: My sql Security

[Vikram A]

Sir,
I will drop the authentication part.  If stored in the code, the db password is 
subject to change when needed. In this case i can not change my part of the 
code. So I will go for the config file for the credentials.

Thank you for the solutions/suggestions.

Vikram





From: Johan De Meersman 
To: Vikram A 
Cc: MY SQL Mailing list 
Sent: Wed, 28 April, 2010 9:55:38 PM
Subject: Re: My sql Security

Rip out the DB authentication part, and store those credentials in-code, in
some config file or the registry, or some remote mechanism like LDAP.

If your users need to access multiple servers, just give them an option for
each server, but don't let them enter DB credentials themselves.

Users are not to be trusted with direct data access; they're way too devious
for their own good :-)

On Wed, Apr 28, 2010 at 11:05 AM, Vikram A  wrote:

> Sir,
> Yes; As per your mail, i understood that the authentication must be
> separated for both app and the db.
> Let me send my login Authentication screen; I request you guide me how can
> handle this.
>
> Thank you
>
> Vikram
>
> --
> *From:* Johan De Meersman 
>
> *To:* Vikram A 
> *Cc:* MY SQL Mailing list 
> *Sent:* Wed, 28 April, 2010 2:10:45 PM
>
> *Subject:* Re: My sql Security
>
> I'm afraid you can't discern between clients and applications on the MySQL
> level. Your application authentication should be separate from the MySQL
> one.
>
>
> On Wed, Apr 28, 2010 at 10:28 AM, Vikram A  wrote:
>
> > Hi all,
> > I have some security issues. I would like to have your
> > suggestions/solutions.
> >
> > I have winserver2003 with mysql 5.1.45. We have client serve application
> > that allows multi-login system with various people.
> >
> > I am getting user name, password for database login when the try to use
> > login  [which is for Application]. By using DB the user name and the
> > password, people who know the mysql are opening the DB using some GUI
> tools.
> > How this can be avoided; because it is major issue right now in my work
> > place.
> >
> > Please Can any one can help me?
> >
> > Thank you
> >
> > VIKRAM A
> >
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>


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

Recommend A Backup User / Privileges?

[Carlos Mennens]

I downloaded a MySQL backup script today since I have 3 or 4
relatively small databases. The script can be found here:

http://sourceforge.net/projects/automysqlbackup/

Now I was wondering if I can create a local database user
'backup'@'localhost' & grant him a level of permissions needed to
perform a 'mysqldump' so he can backup the databases to an directory
on the server. My Question is what level or permissions does a user
need to perform a 'mysqldump' on a database since that particular
user's password will be entered into the script noted above via plain
text. I am scared the password will be compromised & that would be bad
if it's root or someone who has GRANT ALL PRIVILEGES ON *.*.

Can you guys recommend something for me here? I don't really know
enough about MySQL 'grant' permissions to determine which would work
or if the 'backup' user would require high level privileges.

-Carlos

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

RE: Replication : request DELETE is not executed on slave

[David Florella]

Hi, 

Thanks to you and everyone. 

I will test the same request with the ORDER BY clause. 

Regards, 

David. 

-Message d'origine-
De : Mattia Merzi [mailto:mattia.me...@gmail.com] 
Envoyé : mercredi 28 avril 2010 17:54
À : mysql@lists.mysql.com
Objet : Re: Replication : request DELETE is not executed on slave

AFAIR you can use LIMIT with replication only if you use row-based
replication (or mixed), that means that you must use mysql 5.1.

Greetings,

Mattia.


2010/4/28 Tom Worster :
> 16.3.1.9. Replication and LIMIT
> Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT
> statements is not guaranteed, since the order of the rows affected is not
> defined. Such statements can be replicated correctly only if they also
> contain an ORDER BY clause.

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


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

Re: My sql Security

[Johan De Meersman]

Rip out the DB authentication part, and store those credentials in-code, in
some config file or the registry, or some remote mechanism like LDAP.

If your users need to access multiple servers, just give them an option for
each server, but don't let them enter DB credentials themselves.

Users are not to be trusted with direct data access; they're way too devious
for their own good :-)

On Wed, Apr 28, 2010 at 11:05 AM, Vikram A  wrote:

> Sir,
> Yes; As per your mail, i understood that the authentication must be
> separated for both app and the db.
> Let me send my login Authentication screen; I request you guide me how can
> handle this.
>
> Thank you
>
> Vikram
>
> --
> *From:* Johan De Meersman 
>
> *To:* Vikram A 
> *Cc:* MY SQL Mailing list 
> *Sent:* Wed, 28 April, 2010 2:10:45 PM
>
> *Subject:* Re: My sql Security
>
> I'm afraid you can't discern between clients and applications on the MySQL
> level. Your application authentication should be separate from the MySQL
> one.
>
>
> On Wed, Apr 28, 2010 at 10:28 AM, Vikram A  wrote:
>
> > Hi all,
> > I have some security issues. I would like to have your
> > suggestions/solutions.
> >
> > I have winserver2003 with mysql 5.1.45. We have client serve application
> > that allows multi-login system with various people.
> >
> > I am getting user name, password for database login when the try to use
> > login  [which is for Application]. By using DB the user name and the
> > password, people who know the mysql are opening the DB using some GUI
> tools.
> > How this can be avoided; because it is major issue right now in my work
> > place.
> >
> > Please Can any one can help me?
> >
> > Thank you
> >
> > VIKRAM A
> >
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>


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

Re: Replication : request DELETE is not executed on slave

[Mattia Merzi]

AFAIR you can use LIMIT with replication only if you use row-based
replication (or mixed), that means that you must use mysql 5.1.

Greetings,

Mattia.


2010/4/28 Tom Worster :
> 16.3.1.9. Replication and LIMIT
> Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT
> statements is not guaranteed, since the order of the rows affected is not
> defined. Such statements can be replicated correctly only if they also
> contain an ORDER BY clause.

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

Re: Replication : request DELETE is not executed on slave

[Tom Worster]

16.3.1.9. Replication and LIMIT

Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT
statements is not guaranteed, since the order of the rows affected is not
defined. Such statements can be replicated correctly only if they also
contain an ORDER BY clause.

http://dev.mysql.com/doc/refman/5.0/en/replication-features-limit.html


On 4/28/10 11:24 AM, "Jerry Schwartz"  wrote:

>> -Original Message-
>> From: David Florella [mailto:dflore...@legos.fr]
>> Sent: Wednesday, April 28, 2010 10:51 AM
>> To: mysql@lists.mysql.com
>> Cc: 'Krishna Chandra Prajapati'
>> Subject: RE: Replication : request DELETE is not executed on slave
>> 
>> Hi,
>> 
>> In the MySQL documentation, it is written that the two versions are
>> compatible to make a replication.
>> 
>> It seems that if I make a DELETE without the 'LIMIT 7500', the query is
>> replicated to the slave.
>> 
> [JS] This might be my ignorance speaking, but since record order is not
> defined for a database how would the slave know WHICH records to delete.
> 
> The DELETE... LIMIT 7500 would have to be translated into 7500 separate
> DELETEs, and there would have to be some unique way of identifying the
> individual records.
> 
> Regards,
> 
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
> 
> 860.674.8796 / FAX: 860.674.8341
> 
> www.the-infoshop.com
> 
> 
> 
>> Regards,
>> 
>> David.
>> 
>> 
>> -Message d'origine-
>> De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com]
>> Envoy� : mercredi 28 avril 2010 11:15
>> � : dflore...@legos.fr
>> Cc : mysql@lists.mysql.com
>> Objet : Re: Replication : request DELETE is not executed on slave
>> 
>> Hi dflorella,
>> 
>> The important thing about mysql replication is same mysql version for both
>> master as well as slave should be used. It should be taken as good practice.
>> 
>> You need to check that master and slave are in sync. Is there any error
>> (replication) on the slave server. Check the mode, strict or some thing
>> else.
>> 
>> Does the delete command exits in binlog.
>> 
>> Regards,
>> Krishna
>> 
>> 
>> On Wed, Apr 28, 2010 at 2:37 PM, David Florella  wrote:
>> 
>>> Hi,
>>> 
>>> 
>>> 
>>> I am using MySQL replication :
>>> 
>>> 
>>> 
>>> -  The version of the master is 4.1.12-log
>>> 
>>> -  The version of the slave is 5.0.41
>>> 
>>> 
>>> 
>>> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
>>> LIMIT 7500", the query is executed on the master but not on the slave.
>>> 
>>> 
>>> 
>>> Do you know why the request is not executed on the slave?
>>> 
>>> 
>>> 
>>> Regards,
>>> 
>>> 
>>> 
>>> David.
>>> 
>>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
> 
> 
> 
> 



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

RE: Replication : request DELETE is not executed on slave

[Jerry Schwartz]

>-Original Message-
>From: David Florella [mailto:dflore...@legos.fr]
>Sent: Wednesday, April 28, 2010 10:51 AM
>To: mysql@lists.mysql.com
>Cc: 'Krishna Chandra Prajapati'
>Subject: RE: Replication : request DELETE is not executed on slave
>
>Hi,
>
>In the MySQL documentation, it is written that the two versions are
>compatible to make a replication.
>
>It seems that if I make a DELETE without the 'LIMIT 7500', the query is
>replicated to the slave.
>
[JS] This might be my ignorance speaking, but since record order is not 
defined for a database how would the slave know WHICH records to delete.

The DELETE... LIMIT 7500 would have to be translated into 7500 separate 
DELETEs, and there would have to be some unique way of identifying the 
individual records.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>Regards,
>
>David.
>
>
>-Message d'origine-
>De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com]
>Envoyé : mercredi 28 avril 2010 11:15
>À : dflore...@legos.fr
>Cc : mysql@lists.mysql.com
>Objet : Re: Replication : request DELETE is not executed on slave
>
>Hi dflorella,
>
>The important thing about mysql replication is same mysql version for both
>master as well as slave should be used. It should be taken as good practice.
>
>You need to check that master and slave are in sync. Is there any error
>(replication) on the slave server. Check the mode, strict or some thing
>else.
>
>Does the delete command exits in binlog.
>
>Regards,
>Krishna
>
>
>On Wed, Apr 28, 2010 at 2:37 PM, David Florella  wrote:
>
>> Hi,
>>
>>
>>
>> I am using MySQL replication :
>>
>>
>>
>> -  The version of the master is 4.1.12-log
>>
>> -  The version of the slave is 5.0.41
>>
>>
>>
>> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
>> LIMIT 7500", the query is executed on the master but not on the slave.
>>
>>
>>
>> Do you know why the request is not executed on the slave?
>>
>>
>>
>> Regards,
>>
>>
>>
>> David.
>>
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





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

RE: Replication : request DELETE is not executed on slave

[David Florella]

Hi, 

In the MySQL documentation, it is written that the two versions are
compatible to make a replication. 

It seems that if I make a DELETE without the 'LIMIT 7500', the query is
replicated to the slave.

Regards, 

David.


-Message d'origine-
De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] 
Envoyé : mercredi 28 avril 2010 11:15
À : dflore...@legos.fr
Cc : mysql@lists.mysql.com
Objet : Re: Replication : request DELETE is not executed on slave

Hi dflorella,

The important thing about mysql replication is same mysql version for both
master as well as slave should be used. It should be taken as good practice.

You need to check that master and slave are in sync. Is there any error
(replication) on the slave server. Check the mode, strict or some thing
else.

Does the delete command exits in binlog.

Regards,
Krishna


On Wed, Apr 28, 2010 at 2:37 PM, David Florella  wrote:

> Hi,
>
>
>
> I am using MySQL replication :
>
>
>
> -  The version of the master is 4.1.12-log
>
> -  The version of the slave is 5.0.41
>
>
>
> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
> LIMIT 7500", the query is executed on the master but not on the slave.
>
>
>
> Do you know why the request is not executed on the slave?
>
>
>
> Regards,
>
>
>
> David.
>
>


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

RE: installation

[jamesadrian]

Anand,

Thank you for this very valuable information.  I created .profile at ~/
as you instructed.

I,m sorry I know so little but your answer makes me wonder why you wrote

/sbin:/usr/sbin:/usr/local/mysql/bin:

while the installation notes said only

/usr/local/mysql/bin

There is an extra /sbin:/usr/sbin: at the start and a  :   added to the
end.

Thank you for your help.


Jim Adrian
jamesadr...@globalfreeenterprise.com

>  Original Message 
> Subject: Re: installation
> From: Anand Kumar 
> Date: Wed, April 28, 2010 3:16 am
> To: jamesadr...@globalfreeenterprise.com
> Cc: mysql list 
> 
> 
> Hi James,
> 
> Assuming your OS is linux ,you can add the mysql bin path in the environment
> .By adding the entry in .profile of your home directory.
> 
> PATH=$PATH:/sbin:/usr/sbin:/usr/local/mysql/bin:
> export PATH
> 
> Thanks
> Anand
> 
> On Wed, Apr 28, 2010 at 11:44 AM, wrote:
> 
> > In the ReadMe.pdf that came with the mysql that I downloaded from
> > mysql.com, there is this comment:
> >
> > --
> > You might want to add aliases to your shell's resource file to make it
> > easier to access commonly used programs such as `mysql' and `mysqladmin'
> > from the command line. The syntax for `bash' is this:
> >
> > alias mysql=/usr/local/mysql/bin/mysql
> > alias mysqladmin=/usr/local/mysql/bin/mysqladmin
> >
> > Even better, add `/usr/local/mysql/bin' to your `PATH' environment
> > variable. You can do this by modifying the appropriate startup file for
> > your shell. For more information, see *Note invoking-programs.
> > ---
> >
> > My questions are these:
> >
> > 1.What is the appropriate startup file for my shell (which is bash
> > on OS X)?
> >
> > 2.Is this the best discussion group for the question above?
> >
> > --
> >
> > The installation comment said "For more information, see *Note
> > invoking-programs" but I don't find such a note in all of the downloaded
> > material.
> >
> > Thank you for your help.
> >
> > Jim Adrian
> > jamesadr...@globalfreeenterprise.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@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: remote mysqldump to csv (--tab)

[Carlos Eduardo Caldi]

Hi

You can use on shell, connect at the client mysq -h (host or IP) -p(password)

and run the query:

SELECT a, b, c INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table

more info see the link
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html


Carlos Caldi



> Date: Wed, 28 Apr 2010 14:11:14 +0200
> From: ma...@psb.vib-ugent.be
> To: mysql@lists.mysql.com
> Subject: remote mysqldump to csv (--tab)
> 
> Hi all,
> 
> posted this in the backup list, but that one seems to be pretty dead, so i'll 
> try my luck again here:
> 
> 
> We're currently looking for a way to backup a pretty big mysql table to 
> a csv file. However, we don't want to allow the user ssh or file access 
> to the server, so it'd have to happen remotely. the --tab/-T option 
> allows exporting to csv, but not remotely.
> So we're looking for a way to have mysqldump store these files remotely, 
> on the client where mysqldump is executed.
> I've been looking up and down, only to find bad news, so I'm pretty sure 
> it's just not possible. I know there are ways to do this (little script 
> to convert the sql to csv, or pipe it all through sed), but we were just 
> wondering if we're really not overlooking a nifty option in mysqldump :-)
> 
> Thanks in advance
> 
> mavoo
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=ce_ca...@hotmail.com
> 
  
_
O Internet Explorer 8 quer te ajudar a navegar seguro. Entre aqui para ler as 
dicas.
http://www.microsoft.com/brasil/windows/internet-explorer/?WT.mc_id=1500

Re: Pivot Query in

[Peter Brawley]

In MS Access I used to generate one pivot query which gets the following
report from the database which contains the following fields:


See "Pivot tables" at http://www.artfulsoftware.com/queries.php .

PB

-

Jay Blanchard wrote:

[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of 


Project Code   R&D   STP

1007304--04---04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896


  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.437 / Virus Database: 271.1.1/2840 - Release Date: 04/28/10 06:27:00


  

Re: order by numeric value

[Martijn Tonies]

> But I'd prefer not to see the extra sorting field.

You don't need to select a field in order to be able to order by it.

So

select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date > '2008-12-31'
and sales_journal.date < '2010-01-01'
group by sales_journal_entries.accountID
order by
coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
asc;

should do the trick.

Jesper


Perfectamundo!  I thought there would have been a more elegant way but
this works just fine.  Thanks.


There is a more elegant way: do not do your $ at the database, but rather
in the presentation layer.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com 



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

remote mysqldump to csv (--tab)

[Marijn Vandevoorde]

Hi all,

posted this in the backup list, but that one seems to be pretty dead, so i'll 
try my luck again here:


We're currently looking for a way to backup a pretty big mysql table to 
a csv file. However, we don't want to allow the user ssh or file access 
to the server, so it'd have to happen remotely. the --tab/-T option 
allows exporting to csv, but not remotely.
So we're looking for a way to have mysqldump store these files remotely, 
on the client where mysqldump is executed.
I've been looking up and down, only to find bad news, so I'm pretty sure 
it's just not possible. I know there are ways to do this (little script 
to convert the sql to csv, or pipe it all through sed), but we were just 
wondering if we're really not overlooking a nifty option in mysqldump :-)


Thanks in advance

mavoo


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

RE: Pivot Query in

[Jay Blanchard]

[snip]
Date, ProjectCode Building, Number of Copies

I want to get a Connsolidate Report of 

Project Code   R&D   STP

1007304--04---04

(Group by Project Code)(Sumtotal Building wise).

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by
pcode,building

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

How can I avoid this. I want to get the Building Name on top as Column
Names
and bottom I should get the Count.
[/snip]

We would need to see some of the raw data to help you but this older
article may point you in the right direction with crosstab (pivot)
queries;

http://www.evolt.org/node/26896


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

Re: My sql Security

[Vikram A]

Sir,

We dropped the Idea of getting db user name and passwd during the login; 
because we have more than 1000 users and we can not give the user name and the 
password. 

We will set the connection details in the encrypted format in some config file. 
So that the user name, and the password will not be given to user.

Early, We planned to maintain the audit information[who done the change, when 
it has done, what kind of change and so on], for this purpose only we have 
given individual user name and password.

Now we will remove the DB login part. Any other way to avoid the DB connection 
from the other GUI/connecting tools though they have given a access to db?

Thank you for the information.

Vikram
 





From: nwood 
To: Vikram A 
Cc: MY SQL Mailing list 
Sent: Wed, 28 April, 2010 3:39:23 PM
Subject: Re: My sql Security

On Wed, 2010-04-28 at 13:58 +0530, Vikram A wrote:
> Hi all,
> I have some security issues. I would like to have your suggestions/solutions.
> 
> I have winserver2003 with mysql 5.1.45. We have client serve application that 
> allows multi-login system with various people.
> 
> I am getting user name, password for database login when the try to use login 
>  

> [which is for Application]. By using DB the user name and the password, 
> people who know the mysql

>  are opening the DB using some GUI tools. How this can be avoided; because it 
> is major issue right now in my work place.
> 
> Please Can any one can help me?
> 
> Thank you
> 
> VIKRAM A
> 
> 

1.) Use MySQL connection limits to restrict the ways a client may impact
performance. In the longer term look to limit table access with stored
procedures or (when efficient) views.

2.) Restrict the database usernames/passwords by IP address and/or SSL
client certificates and restrict access to the authorised client
machines from the people causing a problem.

3.) If the clients and their credentials can't be restricted from the
problem group, use MySQL proxy or its equivalent to filter exactly which
queries may be applied so that only the actions already taken by the
application may be performed by people using it's login credentials.

4.) If the problem is being caused by people on the authorised clients
performing reasonable actions for those clients, then your problem can't
be solved techincally aside from by seperating the application
authentication credentails from the MySQL ones, or by scaling to allow
the clients usage levels.

Like Johan De Meersman I think the real problem you have is probably
that the application uses MySQL access credentials as enduser
credentials. Per-application user database users are unusual in my
industry. If you need per-user access right granularity in database
access it should still be disconnected from application login
credentials. I'd probably do:

field  | application   | mysql
===
username  | current username  | current username
password  | current password stored as hash   | unique password stored
encrypted by hash of (current password + salt)


In this way only an application working on the user's behalf to which
the user had submitted their password would be able to obtain their
unique database password but wouldn't need to store the plaintext
password in the user's session.

HTH 

Nigel


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

Re: Pivot Query in

[nwood]

On Wed, 2010-04-28 at 15:24 +0530, VR Venugopal Rao wrote:
> I am working on Java-HTML-MS Access Backend database.
> 
> Now office is removing all unlicensed softwares and they are removing
> microsoft applications also and hence the necessity to shift from MS Access
> to MySQL has arised.
> 
> In MS Access I used to generate one pivot query which gets the following
> report from the database which contains the following fields:
> 
> Date, ProjectCode Building, Number of Copies
> 
>  
> 
> I want to get a Connsolidate Report of 
> 
> Project Code   R&D   STP
> 
> 1007304--04---04
> 
> (Group by Project Code)(Sumtotal Building wise).
> 
>  
> 
> I have tried to execute the following code :
> 
> select pcode, building, sum(ncopies) from request group by pcode,building
> 
>  
> 
> It is giving the following repott
> 
> 1007304--R&D--04
> 
> 1007304-STP--05
> 
> Here there is a repetition of pcode,
> 
>  
> 
> How can I avoid this. 

Don't use unlicenced commercial software in the first place? 

> I want to get the Building Name on top as Column Names
> and bottom I should get the Count.
> 

Unlike MS Access MySQL does not directly support 'pivot queries', the
portable equivalent is a cross-tab query. In order to allow for the
addition and removal of departments you'll want to dynamically generate
the query each time in a client application or  a stored procedure

Crosstab queries in MySQL
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

Crosstab queries and their native support in MS Access
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

HTH 

Nigel


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

Pivot Query in

[VR Venugopal Rao]

I am working on Java-HTML-MS Access Backend database.

Now office is removing all unlicensed softwares and they are removing
microsoft applications also and hence the necessity to shift from MS Access
to MySQL has arised.

In MS Access I used to generate one pivot query which gets the following
report from the database which contains the following fields:

Date, ProjectCode Building, Number of Copies

 

I want to get a Connsolidate Report of 

Project Code   R&D   STP

1007304--04---04

(Group by Project Code)(Sumtotal Building wise).

 

I have tried to execute the following code :

select pcode, building, sum(ncopies) from request group by pcode,building

 

It is giving the following repott

1007304--R&D--04

1007304-STP--05

Here there is a repetition of pcode,

 

How can I avoid this. I want to get the Building Name on top as Column Names
and bottom I should get the Count.

 

-- 
With regards,
VR Venugopal Rao
91-90526-07186 

 

__

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupp...@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


__

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
__

Re: My sql Security

[nwood]

On Wed, 2010-04-28 at 13:58 +0530, Vikram A wrote:
> Hi all,
> I have some security issues. I would like to have your suggestions/solutions.
> 
> I have winserver2003 with mysql 5.1.45. We have client serve application that 
> allows multi-login system with various people.
> 
> I am getting user name, password for database login when the try to use login 
>  

> [which is for Application]. By using DB the user name and the password, 
> people who know the mysql

>  are opening the DB using some GUI tools. How this can be avoided; because it 
> is major issue right now in my work place.
> 
> Please Can any one can help me?
> 
> Thank you
> 
> VIKRAM A
> 
> 

1.) Use MySQL connection limits to restrict the ways a client may impact
performance. In the longer term look to limit table access with stored
procedures or (when efficient) views.

2.) Restrict the database usernames/passwords by IP address and/or SSL
client certificates and restrict access to the authorised client
machines from the people causing a problem.

3.) If the clients and their credentials can't be restricted from the
problem group, use MySQL proxy or its equivalent to filter exactly which
queries may be applied so that only the actions already taken by the
application may be performed by people using it's login credentials.

4.) If the problem is being caused by people on the authorised clients
performing reasonable actions for those clients, then your problem can't
be solved techincally aside from by seperating the application
authentication credentails from the MySQL ones, or by scaling to allow
the clients usage levels.

Like Johan De Meersman I think the real problem you have is probably
that the application uses MySQL access credentials as enduser
credentials. Per-application user database users are unusual in my
industry. If you need per-user access right granularity in database
access it should still be disconnected from application login
credentials. I'd probably do:
 
field  | application   | mysql
===
 username  | current username  | current username
 password  | current password stored as hash   | unique password stored
encrypted by hash of (current password + salt)


In this way only an application working on the user's behalf to which
the user had submitted their password would be able to obtain their
unique database password but wouldn't need to store the plaintext
password in the user's session.

HTH 

Nigel


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

Re: Replication : request DELETE is not executed on slave

[Krishna Chandra Prajapati]

Hi dflorella,

The important thing about mysql replication is same mysql version for both
master as well as slave should be used. It should be taken as good practice.

You need to check that master and slave are in sync. Is there any error
(replication) on the slave server. Check the mode, strict or some thing
else.

Does the delete command exits in binlog.

Regards,
Krishna


On Wed, Apr 28, 2010 at 2:37 PM, David Florella  wrote:

> Hi,
>
>
>
> I am using MySQL replication :
>
>
>
> -  The version of the master is 4.1.12-log
>
> -  The version of the slave is 5.0.41
>
>
>
> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
> LIMIT 7500", the query is executed on the master but not on the slave.
>
>
>
> Do you know why the request is not executed on the slave?
>
>
>
> Regards,
>
>
>
> David.
>
>

Replication : request DELETE is not executed on slave

[David Florella]

Hi,

 

I am using MySQL replication : 

 

-  The version of the master is 4.1.12-log 

-  The version of the slave is 5.0.41

 

When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx'
LIMIT 7500", the query is executed on the master but not on the slave. 

 

Do you know why the request is not executed on the slave?

 

Regards, 

 

David. 

Re: My sql Security

[Johan De Meersman]

I'm afraid you can't discern between clients and applications on the MySQL
level. Your application authentication should be separate from the MySQL
one.


On Wed, Apr 28, 2010 at 10:28 AM, Vikram A  wrote:

> Hi all,
> I have some security issues. I would like to have your
> suggestions/solutions.
>
> I have winserver2003 with mysql 5.1.45. We have client serve application
> that allows multi-login system with various people.
>
> I am getting user name, password for database login when the try to use
> login  [which is for Application]. By using DB the user name and the
> password, people who know the mysql are opening the DB using some GUI tools.
> How this can be avoided; because it is major issue right now in my work
> place.
>
> Please Can any one can help me?
>
> Thank you
>
> VIKRAM A
>
>
>


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

My sql Security

[Vikram A]

Hi all,
I have some security issues. I would like to have your suggestions/solutions.

I have winserver2003 with mysql 5.1.45. We have client serve application that 
allows multi-login system with various people.

I am getting user name, password for database login when the try to use login   
   [which is for Application]. By using DB the user name and the password, 
people who know the mysql are opening the DB using some GUI tools. How this can 
be avoided; because it is major issue right now in my work place.

Please Can any one can help me?

Thank you

VIKRAM A

Re: MySQL threads taking time in "statistics" state

[Dheeraj Kumar]

Hi Baron,
I am too new to mysql internal jargon to understand "Handler:info()". Can
you tell me specific variables you want to get the real issue?

*Regarding OS:*  It ubuntu disto. of linux.
dhee...@:~$ uname -a
Linux host0125 2.6.24-16-generic #1 SMP Thu Apr 10 12:47:45 UTC 2008 x86_64
GNU/Linux

*Regarding Hardware:*
Plus, it is 16GB RAM with 13.2 GB alloted to mysql and total index size of
all tables is 7-8 GB. CPU is quad-core.

-Thanks in Advance,
Dheeraj

On Mon, Apr 26, 2010 at 7:22 PM, Baron Schwartz  wrote:

> Hi,
>
> You probably aren't doing anything wrong, per se, but I suspect
> Handler::info() is slow and is being called once per partition.  You
> should probably start looking at your system overall to check where
> the time is spent.  Is it in reading from disk?  If so, can you make
> it read from memory instead, or if that's not possible, are your disks
> slower than they should be...? and so on.  What OS are you running?
>
> On Mon, Apr 26, 2010 at 5:59 AM, Dheeraj Kumar  wrote:
> > We have installed mysql-5.1.39 and having a database with following
> table.
> >
> >  CREATE TABLE `EntMsgLog` (
> >  `msgId` bigint(20) NOT NULL,
> >  `causeId` bigint(20) NOT NULL,
> >  `entityId` int(11) NOT NULL,
> >  `msgReceiver` bigint(20) NOT NULL,
> >  `msgTextId` int(11) NOT NULL,
> >  `flags` bit(8) NOT NULL,
> >  `timeStamp` bigint(20) NOT NULL,
> >  `credits` float NOT NULL,
> >  UNIQUE KEY `causeId` (`causeId`,`msgId`,`timeStamp`),
> >  KEY `entityId` (`entityId`),
> >  KEY `msgReceiver` (`msgReceiver`),
> >  KEY `timeStamp` (`timeStamp`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8
> > /*!50100 PARTITION BY RANGE (timeStamp)
> > (PARTITION p01042010 VALUES LESS THAN (127014660) ENGINE = MyISAM,
> >  PARTITION p02042010 VALUES LESS THAN (127023300) ENGINE = MyISAM,
> >  PARTITION p03042010 VALUES LESS THAN (127031940) ENGINE = MyISAM,
> > --
> > 60 such partitions..
> > 
> > PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |
> >
> > my query is following format:
> > mysql> explain select * from EntMsgLog where causeId= 659824157048176974
> and
> > msgId = 143168093266866137;
> >
> ++-+---+--+---+-+-+-+--+---+
> > | id | select_type | table | type | possible_keys | key | key_len
> |
> > ref | rows | Extra |
> >
> ++-+---+--+---+-+-+-+--+---+
> > |  1 | SIMPLE  | EntMsgLog | ref  | causeId   | causeId | 16
>  |
> > const,const |   62 |   |
> >
> ++-+---+--+---+-+-+-+--+---+
> >
> >
> > This query is taking 1-2 sec. to execute and after profiling the query, I
> > found query is taking 90-95% time in "statistics" state.  Please let me
> know
> > what I am doing wrong.
> >
> > -Dheeraj
> >
>
>
>
> --
> Baron Schwartz
> Percona Inc 
> Consulting, Training, Support & Services for MySQL
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=dksid...@gmail.com
>
>

Re: installation

[Anand Kumar]

Hi James,

Assuming your OS is linux ,you can add the mysql bin path in the environment
.By adding the entry in .profile of your home directory.

PATH=$PATH:/sbin:/usr/sbin:/usr/local/mysql/bin:
export PATH

Thanks
Anand

On Wed, Apr 28, 2010 at 11:44 AM, wrote:

> In the ReadMe.pdf that came with the mysql that I downloaded from
> mysql.com, there is this comment:
>
> --
> You might want to add aliases to your shell's resource file to make it
> easier to access commonly used programs such as `mysql' and `mysqladmin'
> from the command line. The syntax for `bash' is this:
>
> alias mysql=/usr/local/mysql/bin/mysql
> alias mysqladmin=/usr/local/mysql/bin/mysqladmin
>
> Even better, add `/usr/local/mysql/bin' to your `PATH' environment
> variable. You can do this by modifying the appropriate startup file for
> your shell. For more information, see *Note invoking-programs.
> ---
>
> My questions are these:
>
> 1.What is the appropriate startup file for my shell (which is bash
> on OS X)?
>
> 2.Is this the best discussion group for the question above?
>
> --
>
> The installation comment said "For more information, see *Note
> invoking-programs" but I don't find such a note in all of the downloaded
> material.
>
> Thank you for your help.
>
> Jim Adrian
> jamesadr...@globalfreeenterprise.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@gmail.com
>
>