Client deleted database, semi high priority master slave question

am 08.07.2009 18:49:45 von Scott Haneda

A client deleted their database and did not have a slave in place for
backup. No scheduled dumps either.

However, I have set up a slave to the master for one table. These
tables hold DNS data for database driven DNS server features.

The master table is empty, the slave rightly saw the deletes and
carried them out.

But I believe the replication logs on the slave, and also perhaps on
the master should still be there. I'm not clear on how large these
get, if they roll/truncate, etc.

Is there any way to restore the master up to the point the data was
deleted? I can then set the slave to resync and they should be back
where the left off.

There are a lot if records, not a lot of data since DNS data is so
small. If I could restore the master It would prove quite helpful.
Currently, all servers are down since DNS is not working, as a result
of DNS being backed by MySql via DLZ/named.

Thanks for any suggestions.
--
Scott
Iphone says hello.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Client deleted database, semi high priority master slave question

am 08.07.2009 18:54:15 von bmurphy

Hope the client learned the lesson about backups. it's a hard one to learn
sometimes.

If the binary logs on the slave were enabled when the table was populated
it should be possible to restore the table from the binary logs. If not,
and logging was turned on after the table was populated than you are out
of luck.

Keith

> A client deleted their database and did not have a slave in place for
> backup. No scheduled dumps either.
>
> However, I have set up a slave to the master for one table. These
> tables hold DNS data for database driven DNS server features.
>
> The master table is empty, the slave rightly saw the deletes and
> carried them out.
>
> But I believe the replication logs on the slave, and also perhaps on
> the master should still be there. I'm not clear on how large these
> get, if they roll/truncate, etc.
>
> Is there any way to restore the master up to the point the data was
> deleted? I can then set the slave to resync and they should be back
> where the left off.
>
> There are a lot if records, not a lot of data since DNS data is so
> small. If I could restore the master It would prove quite helpful.
> Currently, all servers are down since DNS is not working, as a result
> of DNS being backed by MySql via DLZ/named.
>
> Thanks for any suggestions.
> --
> Scott
> Iphone says hello.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=bmurphy@paragon-cs.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Client deleted database, semi high priority master slave question

am 08.07.2009 21:49:20 von Daevid Vincent

> -----Original Message-----
> From: Scott Haneda [mailto:talklists@newgeo.com]
> Sent: Wednesday, July 08, 2009 9:50 AM
> To: mysql@lists.mysql.com
> Subject: Client deleted database, semi high priority master
> slave question
>
> A client deleted their database and did not have a slave in
> place for
> backup. No scheduled dumps either.

Ouch.

I know it comes a little too late, but as a preventive measure going
forward, I offer up my daily crontab backup script:

http://www.daevid.com/content/examples/daily_backup.php

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Client deleted database, semi high priority master slave question

am 08.07.2009 22:41:07 von Scott Haneda

On Jul 8, 2009, at 12:49 PM, Daevid Vincent wrote:

> Ouch.
>
> I know it comes a little too late, but as a preventive measure going
> forward, I offer up my daily crontab backup script:
>
> http://www.daevid.com/content/examples/daily_backup.php

They move a lot of data, so scheduled dumps were not really an
option. I wrote a proposal to use two databases, one is the primary
acting as a DNS 1, and the second is the slave, acting as DNS 2. I
then wanted to add a 3rd slave server, that was a hot standby, in case
the primary went down. It would hold copies of all databases.

This would be on a raid, and on a schedule, it would also do dumps,
which could take as long as they want, since this machine was not
being hit at all.

This was not implemented for some reason. I will look at your
scripts, thanks again.
--
Scott * If you contact me off list replace talklists@ with scott@ *

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Client deleted database, semi high priority master slave question

am 08.07.2009 22:42:46 von Scott Haneda

On Jul 8, 2009, at 12:49 PM, Daevid Vincent wrote:

> I know it comes a little too late, but as a preventive measure going
> forward, I offer up my daily crontab backup script:
>
> http://www.daevid.com/content/examples/daily_backup.php

FYI, this link does not work in Safari.
--
Scott * If you contact me off list replace talklists@ with scott@ *

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Client deleted database, semi high priority master slave question

am 09.07.2009 14:11:00 von Baron Schwartz

Scott,

Based on earlier messages, there's no way to just restore the master
again. But can the data be recovered from the files? If it's InnoDB,
then perhaps. See http://code.google.com/p/innodb-tools/. If it's
MyISAM, one of our guys has made some partial work on a set of tools
to serve a similar function -- extract the data from the files.

Either way, it's going to be tedious and/or mildly costly without
guaranteed success.

Baron

On Wed, Jul 8, 2009 at 12:49 PM, Scott Haneda wrote:
> A client deleted their database and did not have a slave in place for
> backup. No scheduled dumps either.
>
> However, I have set up a slave to the master for one table. These tables
> hold DNS data for database driven DNS server features.
>
> The master table is empty, the slave rightly saw the deletes and carried
> them out.
>
> But I believe the replication logs on the slave, and also perhaps on the
> master should still be there. I'm not clear on how large these get, if th=
ey
> roll/truncate, etc.
>
> Is there any way to restore the master up to the point the data was delet=
ed?
> I can then set the slave to resync and they should be back where the left
> off.
>
> There are a lot if records, not a lot of data since DNS data is so small.=
If
> I could restore the master It would prove quite helpful. Currently, all
> servers are down since DNS is not working, as a result of DNS being backe=
d
> by MySql via DLZ/named.
>
> Thanks for any suggestions.
> --
> Scott
> Iphone says hello.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dbaron@xaprb.c=
om
>
>

--=20
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Hard? query to with group order by group head"s name

am 16.07.2009 07:20:52 von Elim Qiu

------=_NextPart_000_1642_01CA05A2.E55F0150
Content-Type: text/plain;
charset="windows-1252"
Content-Transfer-Encoding: quoted-printable

My table group_member looks like this:
+-----------+-------+---------+
| member_id | name | head_id |
+-----------+-------+---------+
| 1 | Elim | NULL |
| 2 | Ann | 1 |
| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
+-----------+-------+---------+

Record with null head_id means=20
the member is a group head.
Record with head_id k are in the=20
group with head whoes id equals k.

I like to fetch the rows in the following ordaer

| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
| 1 | Elim | NULL |
| 2 | Ann | 1 |

That is=20
(1) A head-row follewed by the group members with that head
(2)head rows are ordered alphabetically by name.

What the query looks like?

Thanks

------=_NextPart_000_1642_01CA05A2.E55F0150--

Re: Hard? query to with group order by group head"s name

am 16.07.2009 07:50:50 von Darryle steplight

what outputs/illustrates a table"s structure? - WAS: Re: Hard? query to with group order by group h

am 16.07.2009 16:02:39 von Govinda

> My table group_member looks like this:
> +-----------+-------+---------+
> | member_id | name | head_id |
> +-----------+-------+---------+
> | 1 | Elim | NULL |
> | 2 | Ann | 1 |
> | 3 | David | NULL |
> | 4 | John | 3 |
> | 5 | Jane | 3 |
> +-----------+-------+---------+

I see such nice formated text output serving to illustrate people's
tables and I think it must be due to some code which is spitting that
out, rather than people typing so painstakingly. What is that
function/MySQL/code?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: what outputs/illustrates a table"s structure? - WAS: Re: Hard? query to with group order by gro

am 16.07.2009 16:14:57 von Marcus Bointon

On 16 Jul 2009, at 15:02, Govinda wrote:

> I see such nice formated text output serving to illustrate people's
> tables and I think it must be due to some code which is spitting
> that out, rather than people typing so painstakingly. What is that
> function/MySQL/code?

It's the default output format of the mysql command line client,
nothing special.

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: what outputs/illustrates a table"s structure? - WAS: Re: Hard? query to with group order by gro

am 16.07.2009 16:26:28 von Govinda

>> I see such nice formated text output serving to illustrate people's
>> tables and I think it must be due to some code which is spitting
>> that out, rather than people typing so painstakingly. What is that
>> function/MySQL/code?
>
> It's the default output format of the mysql command line client,
> nothing special.
>
> Marcus
> --

Meaning that on a shared hosting situation, without ssh, then I cannot
do that, right?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: what outputs/illustrates a table"s structure? - WAS: Re: Hard? query to with group order by gro

am 16.07.2009 16:35:37 von Marcus Bointon

--Apple-Mail-51-1012281183
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

On 16 Jul 2009, at 15:26, Govinda wrote:

> Meaning that on a shared hosting situation, without ssh, then I
> cannot do that, right?

Not necessarily - you can run the client locally and connect to the
remote DB. It depends if your host allows remote access to mysql (they
might do on request).

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/

--Apple-Mail-51-1012281183
Content-Disposition: attachment;
filename=smime.p7s
Content-Type: application/pkcs7-signature;
name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIILHzCCBN0w
ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezEL MAkGA1UEBhMCR0Ix
GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9y ZDEaMBgGA1UECgwR
Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBT ZXJ2aWNlczAeFw0w
NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRS VVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMt VVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5 pELlzkniii8efNIx
B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9 mKALi+VuG6JG+ni8
om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq /NonWz1zHyLmSGHG
TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM 1tZUOt4KpLoDd7Nl
yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IB JzCCASMwHwYDVR0j
BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZw AEu0UEh83j2uBG59
MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQG CCsGAQUFBwMCBggr
BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYy aHR0cDovL2NybC5j
b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKG MGh0dHA6Ly9jcmwu
Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgB hvhCAQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvzbRx8
NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er5 7D9Wyd12jMOCAU9s
APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBK nxogL5dMUbtGB8SK
N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNB pEMD9O3vMyfbOeAU
TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/ FX8wggY6MIIFIqAD
AgECAhBx3xrKwDO5B7CQiO3prEe8MA0GCSqGSIb3DQEBBQUAMIGuMQswCQYD VQQGEwJVUzELMAkG
A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVU aGUgVVNFUlRSVVNU
IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2 MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4X DTA4MTEyODAwMDAw
MFoXDTA5MTEyODIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBO ZXR3b3JrIC0gUEVS
U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0 aW9ucyBvZiB1c2U6
IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYo YykyMDAzIENvbW9k
byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3 DQEJARYZbWFyY3Vz
QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC AQoCggEBAJq12Fwn
mDc8j/J+fsyJFdXkxSsbex2n5eXKm0Tjwy+IPE2F5jxpaITqju9WtxMcsjtt P+CZfAdOQQD/R1fg
jPQLXDifxudsMogyOmX2HdoEpGFITEnes21U+hKTU87DlIM+hEZViuOqS8Ym Xj8oNISFYAa90lvs
nt7jtu/eyDdvrZJ0q6QnntRUaeSsz8ngV8Lqshmo/1qmz67JL77MfJgkSfEP 6fJkqb/e9kKjJ1Tn
HipP2PmZTQUnHwcOs/0uaSgOumgpgMyX8fiQ33VV/DGMcTl9P0oLB0GopAKw qlHoXPCJXFkX6LLi
Mm9bdoKUIW0kwnRx1HWjvDrih9UeMmECAwEAAaOCAhswggIXMB8GA1UdIwQY MBaAFImCZ33EnSZw
AEu0UEh83j2uBG59MB0GA1UdDgQWBBSiqul8UOEn/VVzRp+EXywCAx973TAO BgNVHQ8BAf8EBAMC
BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZI
AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYI KwYBBQUHAgEWHWh0
dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBK oEiGRmh0dHA6Ly9j
cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGlj YXRpb25hbmRFbWFp
bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZp cnN0LUNsaWVudEF1
dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMGwGCCsGAQUFBwEBBGAwXjA2Bggr BgEFBQcwAoYqaHR0
cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MCQGCCsG AQUFBzABhhhodHRw
Oi8vb2NzcC5jb21vZG9jYS5jb20wJAYDVR0RBB0wG4EZbWFyY3VzQHN5bmNo cm9tZWRpYS5jby51
azANBgkqhkiG9w0BAQUFAAOCAQEAKfnMTtYONyd2o63aCpSJs8GpopYaEU3k sEBgOAfTFe52yx+k
6eLyah2I0N+lnBmDdLDeR7Eel6zWjKZmaNW9r1h7lRkuzLt6VqpjbBPefOk3 UWkjwJ8j0opKrFSW
UXg+fghnuNVptw3STOyWQKk+fEKtiuf+Ygpnl+QnJWsVjSeasklYqLImXYmn IM3N4y/18UQFztXD
+Z+cn7ZLnBXEfSb/GatxP9JYuhLRTV1YMwtcVhGydHvJTzug+5+wBPdlI8L1 5YecgOR4JxoNJ5T9
Odt0TMPxxVi+m2pZw6Ni3fh8yeKoLsBreKTAX3IsKGsIPJzczQvnVeRrTLaH is9r2DGCA/wwggP4
AgEBMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcT DlNhbHQgTGFrZSBD
aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsT GGh0dHA6Ly93d3cu
dXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQg QXV0aGVudGljYXRp
b24gYW5kIEVtYWlsAhBx3xrKwDO5B7CQiO3prEe8MAkGBSsOAwIaBQCgggIN MBgGCSqGSIb3DQEJ
AzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTA5MDcxNjE0MzUzN1ow IwYJKoZIhvcNAQkE
MRYEFHwHLkdP6365vYD2zbykvvUbB1VKMIHUBgkrBgEEAYI3EAQxgcYwgcMw ga4xCzAJBgNVBAYT
AlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAc BgNVBAoTFVRoZSBV
U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1 c3QuY29tMTYwNAYD
VQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQg RW1haWwCEHHfGsrA
M7kHsJCI7emsR7wwgdYGCyqGSIb3DQEJEAILMYHGoIHDMIGuMQswCQYDVQQG EwJVUzELMAkGA1UE
CBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUg VVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVT
RVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhBx3xrK wDO5B7CQiO3prEe8
MA0GCSqGSIb3DQEBAQUABIIBAAiNuhf/heIGUy4RePrIhYt/AE5J5Vjmwcuw 3/sigPmMjb4EyoFR
EQs7k324x1kd5cEQ9yxzvE6ghpkngUd6G0Q0hk/IdEpC3mVEsUlOjG2rcUzi IB5mtNN2u1tvY8N7
06DazS7r2voyhTQlfUaPcjbvoy+qL9jhFi38w1e/dVRIEhXHlpdkenAZCznQ YsGkMABcqL6HYAFU
obqWCO6/zYBK5LxZafmULdRht650hvrm2huyohUkAwkvGxb5jc0bzZEnBUG6 VBj04Zw/fnN6z+EM
x6Eqlfdou5R8SGG/aC2R9E2fXcFQjAhsg5gD9hgDlRsfFirBPyBOBr/y5xKH yPwAAAAAAAA=

--Apple-Mail-51-1012281183--

Re: Client deleted database, semi high priority master slave question

am 16.07.2009 18:16:04 von Russ F

Scott Haneda wrote:

> On Jul 8, 2009, at 12:49 PM, Daevid Vincent wrote:
>
>> I know it comes a little too late, but as a preventive measure going
>> forward, I offer up my daily crontab backup script:
>>
>> http://www.daevid.com/content/examples/daily_backup.php
>
>
> FYI, this link does not work in Safari.
Does not work with konqueror 4.2.4 release 2 also.
--
------------------------------------------------------------ ------------------
OpenSUSE 11.1 KDE 4.1.3,
Intel DX48BT2 Core 2 Dual E7200. 4 GB DDR III
GeForce 8400 GS, 320GB Disc (2)

-------
Russ

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Hard? query to with group order by group head"s name

am 21.07.2009 19:43:59 von Elim Qiu

Hi Darryle,
Your result was:
+-----------+-------+---------+
| member_id | name | head_id |
+-----------+-------+---------+
| 2 | Ann | 1 |
| 3 | David | NULL |
| 1 | Elim | NULL |
| 5 | Jane | 3 |
| 4 | John | 3 |
+-----------+-------+---------+
which not groups correctly. Seems it's a hard query.

----- Original Message -----
From: "Darryle Steplight"
To: "Elim PDT"
Cc:
Sent: Wednesday, July 15, 2009 11:50 PM
Subject: Re: Hard? query to with group order by group head's name

Hi Elim,
I didn't test it out but it sounds like you want to do this "
SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name
ASC ".

On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT wrote:
> My table group_member looks like this:
> +-----------+-------+---------+
> | member_id | name | head_id |
> +-----------+-------+---------+
> | 1 | Elim | NULL |
> | 2 | Ann | 1 |
> | 3 | David | NULL |
> | 4 | John | 3 |
> | 5 | Jane | 3 |
> +-----------+-------+---------+
>
> Record with null head_id means
> the member is a group head.
> Record with head_id k are in the
> group with head whoes id equals k.
>
> I like to fetch the rows in the following ordaer
>
> | 3 | David | NULL |
> | 4 | John | 3 |
> | 5 | Jane | 3 |
> | 1 | Elim | NULL |
> | 2 | Ann | 1 |
>
> That is
> (1) A head-row follewed by the group members with that head
> (2)head rows are ordered alphabetically by name.
>
> What the query looks like?
>
> Thanks
>
>

--
A: It reverses the normal flow of conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the biggest scourge on plain text email discussions?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Hard? query to with group order by group head"s name

am 21.07.2009 20:50:16 von Brent Baisley

Re: Hard? query to with group order by group head"s name

am 21.07.2009 22:04:40 von Basil Daoust

> On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT wrote:
>> My table group_member looks like this:
>> +-----------+-------+---------+
>> | member_id | name | head_id |
>> +-----------+-------+---------+
>> | 1 | Elim | NULL |
>> | 2 | Ann | 1 |
>> | 3 | David | NULL |
>> | 4 | John | 3 |
>> | 5 | Jane | 3 |
>> +-----------+-------+---------+
>>
>> Record with null head_id means
>> the member is a group head.
>> Record with head_id k are in the
>> group with head whoes id equals k.
>>
>> I like to fetch the rows in the following ordaer
>>
>> | 3 | David | NULL |
>> | 4 | John | 3 |
>> | 5 | Jane | 3 |
>> | 1 | Elim | NULL |
>> | 2 | Ann | 1 |
>>
>> That is
>> (1) A head-row follewed by the group members with that head
>> (2)head rows are ordered alphabetically by name.
>>
>> What the query looks like?
>>
>> Thanks

I hope this is not a school assignment.

What I came up with was to create a new order column that I populated
with the name of the HEAD.
Then I can order by the head, head_id, and the member_id

mysql> select t1.member_id, t1.name, t1.head_id from (
select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from
group_member as m1
left outer join group_member as m2 ON ( m1.head_id = m2.member_id )
order by groupName, m1.head_id, m1.member_id ) AS t1;
+-----------+-------+---------+
| member_id | name | head_id |
+-----------+-------+---------+
| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
| 1 | Elim | NULL |
| 2 | Ann | 1 |
+-----------+-------+---------+
5 rows in set (0.01 sec)

It seemed to work without the order by member_id but I'll assume that
is a fact of the small sample size.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org