Re: Unique ID's across multiple databases

On 12 Sep 2010, at 19:47, Kiss Dániel wrote: > - SID adds only 2 bytes in this case to the size of the primary key item. > It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers. But > anyhow, it is still way smaller than the 16 byte of a UUID field, even if > using BIGINT's

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

). Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature

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

ult output format of the mysql command line client, nothing special. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For lis

Re: beginners

On 15 Jul 2009, at 13:56, shridhar kyrlageri wrote: i am very new to mysql . i want to learn this but i don know where to start what to do. so please enlighten me . http://dev.mysql.com/doc/ Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK

Re: Removing Duplicate Records

AS dups WHERE table1.id=dups.dupid; Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature

Re: Copy 70GB ibdata, etc. and server won't start now

You should take a look at Percona's xtrabackup utility to do this. It takes a clean snapshot of an innodb database that can be restored on a target machine in a few minutes, though it does crash recovery at backup time which can take a while. Marcus -- MySQL General Mailing List For list a

Re: MySQL Windows version

or Windows. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature

Re: Possible Faster Query

1 HOUR) AND (sms_type = 1 OR sms_type =2) GROUP BY mydate ORDER BY insertdate This assumes that insertdate can't be in the future... Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.

Re: Replication switch Master to slave and back

etween two masters in a failover scenario since they're both writable. That setup works very well for me, but I also use google's mmm on top of it so that the switch is transparent to client apps. Works beautifully. https://launchpad.net/mysql-mmm Marcus -- Marcus Bointon

Re: how to get the timestamp from remote mysql

x27;s a much simpler way of working that storing local times. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list arc

Re: Best approach for DB-based event logging?

ed to initially create about 64k folders (keying off a user-id related hash) in order to keep files- per-dir down to a sensible amount? Its probably about now that I start wanting a reiserFS partition... Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.ne

Best approach for DB-based event logging?

on as that's mainly for improving select performance. Keeping a file on disk for each user might work, but I suspect that would become impractical as I have millions of users, and files on disk is just a kind of inefficient home-brew database. So, any other ideas? Marcus -- Marcus B

Re: Transaction/locking confusion

hat I'm already doing, and thus suffer the same problem. Is it just that locks don't apply outside the transaction? If transactions can't solve synchronisation problems between processes, what are they for??! Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://

Re: Transaction/locking confusion

component queries occurred before the first query of the second one, even if they are actually issued in an overlapping order? Consequently, the first query of the second transaction in my example should not succeed in finding anything. Or are transactions not atomic this way?? Marcus -- Marc

Transaction/locking confusion

the later query from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.sma

Re: Diagnosing i/o thrashing

e around 175 tables each. To all of you that have offered help, thank you very much. I've now got Peter Zaitsev on the case, so hopefully he'll be able to pinpoint my problem areas. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK

Diagnosing i/o thrashing

ed. The diagnostic suggestions on phpMyAdmin's status page have been useful to date, but I'm just not sure what measures will be the most effective. How might I best track down the root of the problem? Alternatively, anyone up for a few hours of consultancy? Marcus -- Marcus B

Re: mysqldump --single-transaction causes database to become unusable

ysqld on one machine, perhaps on different ports or interfaces. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To

Re: Pointers about replication

4 http://phpthinktank.com/plugin/tag/mysql Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqldump --single-transaction causes database to become unusable

ed, but it's apparently 'the way'). The other is one of the reasons for using a slave - stop the slave, do the dump, restart the slave and it will catch up anything it missed. It helps if you can dedicate a slave for this. I reserve the right to be completely wrong though

Re: Best Practice: Timezones and web development

you can just ask - I've made the time zone a user preference, and most systems I've seen do the same. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list arc

Transaction/locking confusion

y from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAI

Transaction/locking confusion

y from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAI

UPDATE LOW_PRIORITY

(around 1.2M records at present) - any tips for that? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Removing DBs from replication

I want to remove, and setting replicate-ignore-db, but it doesn't 'unreplicate' it - after a restart, do and ignore columns are still empty in show master status. What else do I need to do? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/

Deadlock avoidance

the update? Is there a better way of doing this? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Broken Mac pref panel

On 4 Jun 2006, at 23:25, Marcus Bointon wrote: I'm having trouble with the prefpane in OS X It seems I'm not alone - there are several reports of this in the MySQL bug tracker. Looks like a bug in the prefpane: http://bugs.mysql.com/bug.php?id=19577 Marcus -- Marcus Bointon Sy

Re: my.cnf not being found?

On 5 Jun 2006, at 03:12, Marcus Bointon wrote: Any other ideas? D'oh! I just fixed the my.cnf problems. Because of the slightly peculiar route that the my.cnf file got onto my MacBook, it had somehow had its line breaks translated to Mac format, and it seems MySQL doesn't li

Re: my.cnf not being found?

On 5 Jun 2006, at 01:52, Petr Chardin wrote: "mysqld --print-defaults". That produces: > mysqld would have been started with the following arguments: i.e., it's got no options at all. It is a completely default install. Any other ideas? Marcus -- Marcus Bointon Sy

my.cnf not being found?

On the same OS X machine that's having prefpanel trouble with 5.0.22, I find that mysql is not loading the values set in /etc/my.cnf. The contents of the file is from another Mac that works just fine with it. The file is world-readable. Should it be somewhere else? Marcus -- Marcus Bo

Broken Mac pref panel

rtupitem seems to work fine too. Reinstalling the prefpane didn't help. Anyone else seen this? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.c

Re: Matching problem

lds for both numbers, or a m:n table if there are more possible entries? Definitely. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com

Re: Case confusion

On 9 May 2006, at 13:12, Marcus Bointon wrote: I'm giving it a go, but it's been running for about 4 hours at 60% CPU so far! Would it be quicker to tell it to use a case insensitive collation to locate the duplicates? I managed to come up with a variation on my original at

Re: Case confusion

to choose which records to change/keep/delete. May not be the best way, but it would work. I'm giving it a go, but it's been running for about 4 hours at 60% CPU so far! Would it be quicker to tell it to use a case insensitive collation to locate the duplicates? Marcus -- M

Case confusion

e records with all cases, that is all of '[EMAIL PROTECTED]', '[EMAIL PROTECTED]' and '[EMAIL PROTECTED]'. I'm confusing myself with the case sensitivity and self-references! I think there are about 45 duplicates out of about 200,000. How can I find t

Sorting with NULL

sing FIELD() in the order by, but the docs say it doesn't like nulls. Is there some other sorting mechanism I could use? Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For

Slow sorting

ee that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? A DBA friend recommended using clustered indexes - does MySQL have such things? Marcus -- Marcus Bointon Synchromedia Limited: Putt

Re: 5.0.15->5.0.16 upgrade dependency failure

three categories, you will need to open a second connection in your code and send a second query to the second server. There is no other way around it. Errr, were you answering some other question? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] |

5.0.15->5.0.16 upgrade dependency failure

dependency? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: log-warnings

my work to a bit of simple detective work rather than having to trawl through endless log files. Thanks for the explanation, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For lis

log-warnings

source. Help! If there was at least a user note on warnings I could be saved - can anyone tell me where I might find the warning messages, along with the query that caused them? Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://w

Re: Padding date results

ks for the suggestions. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Padding date results

DB. I'm thinking along the lines of: GROUP BY FIELD(DATE_FORMAT(event.timestamp, '%Y-%m-%d'), '2005-10-01','2005-10-02','2005-10-03','2005-10-04','2005-10-05','2005-1 0-06') That syntax is lifted from ORDER BY, is s

Padding date results

ion to pad these gaps in date ranges with zero values, but I suspect I could be getting MySQL to do this. How? I could have a table containing all possible dates and do a left join with that, but that just seems like a crap idea! Perhaps create a set of fixed values for GROUP BY? Mar

Re: Heikki: What will become of InnoDb once MySQL license runs out

On 5 Nov 2005, at 03:47, Ezra Taylor wrote: They will crush anyone that gets in there way. Well, if recent events are any indication, Oracle's approach to 'crushing' the opposition is to give them very large amounts of money. If that's being crushed, I'm up f

Re: Tracing a constraint problem

report for having it reported with the error. MySQL seems generally reluctant to tell you about errors - they only just added the ability to show/log warnings during batch operations in the latest beta of 5! Thanks for the tip. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in t

Tracing a constraint problem

I have a single-table update query that's giving me a "Cannot add or update a child row: a foreign key constraint fails" error. Fair enough, I do have several constraints defined - but how can I find out which constraint is failing? Marcus -- Marcus Bointon Synchromedia L

Re: MySQL in CentOS? try before?

(e.g. RHN) for your server (e.g. for development rather than production servers), Centos is a cheaper route to get almost exactly the same thing. Centos provide what amounts to their own version of RHN, so you're not left entirely to your own devices. Marcus -- Marcus Bointon Synch

Re: Problems after upgrading form 4.1.10a to 4.1.13 on RHEL4 - solved

, so it couldn't start up as the mysql user. I manually recreated the user and group using the original IDs (both 27 in this case), and I was then able to launch the server successfully. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] |

Re: Problems after upgrading form 4.1.10a to 4.1.13 on RHEL4

lated to mysql startup is appearing. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problems after upgrading form 4.1.10a to 4.1.13 on RHEL4

ng is logged in mysql.err, and I can't find any other error messages. Any ideas? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To u

Re: Foreign key constraint problem

cause neither is a value). Am I just fantasizing? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[

Foreign key constraint problem

n one tree stored in the table. How should I set up this relation so it works how I want? I'd really prefer not to maintain it manually... Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailin

Strangely large InnoDB file

x27;m guessing that some time in the past deleted data has not been purged from the innodb file. How can I do this manually? Can I force InnoDB to rebuild the file? 'SHOW INNODB STATUS' doesn't show any open transactions. Marcus -- Marcus Bointon Synchromedia Limited: Puttin

Re: Recursive queries

On 13 May 2005, at 10:02, [EMAIL PROTECTED] wrote: if you use php, you can look at http://www.sitepoint.com/article/hierarchical-data-database That's just what i needed, great article. Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] |

Recursive queries

rds 2 and 1 (probably in that order), starting from 5 would just give me 1 etc. It needs to support arbitrary depth, hence the need for recursion. Can I do this in one go, or do I have to query iteratively until I encounter a zero reference? Marcus -- Marcus Bointon Synchromedia Limited: Puttin

Re: log-warnings

l.com/bug.php?id=8684 Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: log-warnings

ore than file transfer and import time, not several days to manually track down errors every time. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

log-warnings

having to log everything and trawl through it - what have I missed?! Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]