How to do a multi-table update with a limit in MySQL
According to the MySQL documentation, you can’t do a multi-table UPDATE with a LIMIT.
What’s a multi-table update with a limit? Well, something like this:
UPDATE
foo
, bar
SET
foo.baz=bar.baz
WHERE
foo.baz IS NULL
AND foo.id=bar.id
LIMIT
1000
;
(Which doesn’t work. Of course, you can do single table UPDATEs with a LIMIT just fine)
Why would you even want to do this?
Well, anytime you have monster sized tables, and you don’t want to lock everybody else while you either read (from bar) or write (to foo). If you can put a limit on the update, you can call it repeatedly, in small chunks, and not choke everything for all other users.
For example, if bar happens to have, say, ohhh, 30 million rows in it, foo happens to have ooh, 2 million rows and they’re both used by everything, all the time.
So, here’s a sneaky way to get around this limitation. I did promise one, right there in the title, after all.
UPDATE
foo
, (SELECT
bar.id
, bar.baz
FROM
foo
, bar
WHERE
foo.id=bar.id
AND foo.baz IS NULL
LIMIT
1000
) tmp
SET
foo.baz=tmp.baz
WHERE
foo.id=tmp.id
;
Some important notes:
- The update conditions (foo.baz IS NULL) go inside the subquery, along with the LIMIT.
- We have to match ids twice – once for the subquery, and once against the created temporary table. That’s why we make sure we SELECT both the id and baz from bar in the subquery.
- There’s no conditionals (other than id match) on the outside WHERE condition, since we’ve done them all in the subquery.
- MySQL also has a limitation of not allowing you to UPDATE while SELECTing from the same table in a subquery. Notice that this sneakily avoids it by only SELECTing from the other table.
So, how about that? You can now do limited multi-table updates.
Oh, except for one. Minor. Problem.
This doesn’t work with temp tables (eg if foo was created with a CREATE TEMPORARY TABLE statement).
Bugger.
However, here’s a sneaky way around that limitation too.
First of all, give your temp table another column, in the example below “ref_no INT”.
Make sure you have an index on the id, otherwise it’ll be dog slow.
Then do this:
# do this in chunks of 1k
SET @counter = 1;REPEAT
UPDATE
tmp_foo
SET
ref_no=@counter
WHERE
ref_no=0
AND baz IS NULL
LIMIT
1000
;
COMMIT;UPDATE
tmp_foo
, bar
SET
tmp_foo.baz=bar.baz
WHERE
tmp_foo.ref_no=@counter
AND tmp_foo.id=bar.id
;
COMMIT;SET @counter = @counter + 1;
UNTIL (SELECT COUNT(id) FROM tmp_foo WHERE ref_no=0 AND baz IS NULL) = 0
END REPEAT;
Some important notes:
- We’re basically flagging a thousand rows at a time, then matching only against those rows – pretty simple concept really.
- The commits are in there because MySQL can be a bit weird about not propagating changes to the database if you don’t commit inside your stored proc. This ensures that updates are passed out, which also means I can run multiple copies of this stored proc concurrently with moderate safety (if I replace @counter with a suitably large RAND() value) – well, as much as you can normally expect with MySQL anyway.
- If you want to reuse the temp table (say, to update something else from – a reverse update to that shown above) you’ll need to reset all the ref_no’s to 0.
- Whatever conditions are in the initial WHERE need to be mirrored in the final SELECT COUNT.
- Obviously just drop the table when you’re finished.
As a bonus, I’ve found this is actually quicker than doing one single large scale update. Why? Less memory is used.
So look at that. TWO ways to get multi-table updates with a limit. Nifty.
related
How to automatically position PuTTY terminal
PuTTY is arguably the best Windows terminal app. I’ve used it for years.
One thing that has always felt missing is the ability to automatically position it on the screen on startup. For example, I always have a couple of windows where I watch certain processes. They’re always in the same position on my screen, and always running the same scripts inside them. How do I do that?
So, you can imagine my glee when I found a patch out there that will do exactly that. Props go to Brad Goodman who wrote it. Follow that link to get both an exe (his patch applied to a 2005 build) and the source so you can patch it yourself. I’ve also put a copy of the exe here, in case his site disappears.
How to use it? Very simple.
- Save your session settings from inside PuTTY, as normal
- Start up putty-bkg.exe (instead of the usual putty.exe)
- Now when you load your sessions you’ll also see the new “Position & Icon” options, under “Window”:
Enter the desired top, left (& icon, if you feel inspired)
- Alter them to your heart’s content (don’t forget to also adjust the rows & columns under “Window” to get it the right size too), save your session and voila.
Even more useful is if you combine this with setting a remote command to be run at initial connection.
- Click on “SSH” under “Connection” (you can’t do this if the session is already active)
- Enter the command or script you want run in the “Remote Command” textbox:
Enter the startup command
- Click the “Save” button (under “Session”) as usual.
Now, to run all of the above, just setup a shortcut to:
“C:\Program Files\putty\putty-bkg.exe” -load [session name]
(where [session name] is whatever you saved your session as). Obviously adjust the path to wherever you put putty.bkg.exe.
Next time you click the shortcut, not only will PuTTY be positioned correctly on the screen, but it’ll also automatically be running whatever script you desire. Just like magic! (but with less jiggery pokery)
related
25.Jan.2012Find non-commented Python lines in Komodo
I’ve been doing a lot of large scale refactoring recently.
This entails a lot of “find all instances of this and replace it with that” – in non-trivial ways (of course – any monkey can do a search & replace).
Obviously I also want to only bother with non-commented lines of code.
I use Komodo for my Python coding, and while it’s a great IDE in a lot of ways, it would appear I’m the first coder that’s ever wanted to search only active lines of code (/sarcasm). Komodo does have a great regex search feature though, so I put that to use.
After much head scratching (every regex engine has its own delightful little quirks) I found this incantation:
^\s*[^#\n]*find.*$
Which will find all single-line non-commented instances of ‘find’.
Now, bugger typing that mess in every time I want to find something, so here’s a better way.
Go View | Toolbox (so the toolbox appears on the right hand side). Then right-click & “Add New Macro”. Give it a sensible name and enter this into the main text area:
if (komodo.view) {komodo.view.setFocus();}
var search = ko.interpolate.interpolateStrings(‘%ask:SearchFor:’);
Find_FindAllInMacro(window, 0, ‘^\\s*[^#\\n]*’ + search + ‘.*$’, 2, 0, false, false);
It has to be Javascript – Komodo doesn’t offer the %ask functionality in Python macro scripting (nice one, guys).
Next give it a decent key-binding on the second tab. Click in the “New Key Sequence” box and hit a vulcan key combo that works for you – I’ve used Ctrl-Alt-F – followed by clicking Add.
Hit OK & you’re ready to roll. Anytime you want to find non-commented lines of code, hit your key combo, type your search string and voila!
related
17.Sep.2011Insert/Edit link button not working on WordPress (Firefox, Adblock Plus)
Just figured out a bug/conflict that’d been niggling at me for a while.
A few months back, the Insert/Edit link button (on the visual editor page of WordPress) stopped working under Firefox.
I’d just done a whole bunch of site upgrades, so I figured it was just a temporary wordpress bug.
However, several updates later, and it’s still there – and on a couple of different sites, so I thought I’d investigate a bit further.
After much digging around, I figured out what it was.
Adblock Plus blocks the javascript because it creates a popup window called “tinyMCEPopup” – and of course, the anything called “popup” is evil.
To fix this, ensure that Adblock Plus is set to show in the status bar. (Tools | Adblock Plus Preferences – then when the window opens, under Options, check that “show in status bar” is ticked)
Then you’ll get a little red ABP stop sign in your status bar, you can then right click on that, and select “Disable on [yourdomain].com.” When you do that, it’ll go grey, showing that your domain has been whitelisted, & Adblock Plus will stop screwing with it.
Once the ABP icon is grey, you’ll need to refresh the page, but after that, everything will be hunky dory.
related
17.Sep.2011New Twitter Minimaliser
Twitter recently forced everybody over onto what they’ve dubbed “New Twitter.”
It’s got more functionality than the old version – which translates to “a lot more visual clutter.”
I’d been avoiding it for the most part, simply because I like clean, simple, straightforward. When I’m using Twitter on the web, I want to read tweets and send tweets. Nothing else.
Now I have no choice (if I’m using web-based Twitter), I thought I’d do something about it.
Thus, I present to you! The New Twitter Minimaliser.
This is a GreaseMonkey script, which means it works if you have the GreaseMonkey Add-on (follow that to get it) for Firefox, or if you run Chrome (where a lot of GreaseMonkey scripts run natively).
The New Twitter Minimaliser does the following:
Removes:
- Recommended Users
- Trends
- User Recommendations
- The “Witty Definition”
- Ability to do new style RTs (one click & all done)
Adds:
- Old Style RT button (where you quote the user & add your comment)
It also shrinks the dashboard on the side, and makes the main text area much larger. Ie, focusing the screen real estate on where it’s most useful.
It doesn’t screw with any of the code on the page (just the css) so it can’t add any new bugs. It’s also carefully optimised so it works very well on 1024×768 screens.
Oddly, now I’ve been running this script for a while, I actually prefer New Twitter to the old version. It’s much cleaner & snappier. Functionality wise it’s a bit of a wash – some things are easier, some things are harder.
Now, if I could just figure out how to get New Twitter to show me incoming DMs only (like old Twitter did, rather than one mushed up list), I’d be a super happy camper.
related
19.Feb.2011Gmail: Storage quota exceeded. Why this happens
I helped a friend out yesterday with her Gmail account. It was at 23% (or something) but email to her was bouncing with a “storage quota exceeded” message.
I googled the hell out of this but couldn’t find the reason, so I thought I’d put it up here for the world.
There are three possible reasons this happens (“storage quota exceeded” bounces when you don’t appear to have exceeded your quota):
- You’re forwarding email through/to another Gmail account that has exceeded its quota
- One of your filters is redirecting your mail through a full Gmail account
- You’re using Google Apps to manage all email for your domain, and your Google Apps domain email account is full.
Taking these in turn.
Number 1, check in your Gmail account under “Mail settings” | “Forwarding and POP/IMAP” if you have any forwarding going on. If you do (and it’s to another Gmail account), check that account hasn’t used up its quota.
Number 2, check in your Gmail account under “Mail settings” | “Filters” if any of your filters send mail via another Gmail account. If so, check if it’s used up its quota.
Number 3 (this is what happened to my friend), go to Google apps (that link will currently take you mostly there, but if you’re using the free version, click on the “Google apps (Free)” link at the bottom left, under “Solutions”. Alternatively, just google the damn site (ie, Google Google Apps. Yes, this is somewhat ridiculous). Google tends to shuffle things around pretty regularly.
ANYWAY.
- Sign in to Google Apps – remember, this will be your Google Apps login/password, not your Gmail login/password.
- Scroll down to “Email”
- Click on the link underneath the Icon, not the Email icon itself (this goes somewhere else)
This will take you to the inbox for your domain. All email to any accounts on your domain gets copied here (I guess so system administrators can see what everybody’s been up to). What this means is – even if you’re keeping your normal Gmail account nice & low, this domain-wide account will be getting filled up.
THIS is where the “storage quota exceeded” bounce messages are coming from.
Confusingly, there’s nothing in the email headers that will tell you this. You just have to magically know (Thanks Google!) No matter, I’m telling you, so now you know anyway.
From this domain-wide Gmail you can delete all the Spam. Do searches for things like “has:attachment”, then click “select all”, and “select all conversations that match this search”, then click Delete. Or, if you’re feeling wild & crazy, just select everything & delete it. Up to you.
Finally, remember to click on “Bin” (it’s what Trash is called in the Google Apps version of Gmail) and “Delete forever”. This’ll free up all the space, and stop your incoming emails bouncing.
You’re quite safe deleting anything you like from this account. It’s completely separate from your normal Gmail account.
Voila. You’re welcome. Be excellent to each other!
related
16.Jul.2010Firefox Redirecting Seemingly Randomly
This is another of the “this is so obscure I couldn’t find anything on Google about it, so I’m writing the canonical post” issues.
I just had an bug that drove me batty. I was trying to access a webserver on localhost, my laptop. Firefox was redirecting to my production (ie not the laptop) server, but other browsers weren’t. Browsing to ‘localhost’ or 127.0.0.1 worked perfectly, it was just using the laptop name that caused the problem.
Straight away this rules out: the local webserver (eg .htaccess rules), dns or hosts files screw ups. I also switched OFF the local server & same issue occurred.
Of course, it took quite some time to nut my way through the above diagnostics.
Turns out it was the Firefox awesome bar being not so awesome.
Months ago, I’d redirected the laptop name (not ‘localhost’ but the actual name) to the production server via the local hosts file, to assist with a debugging issue. Of course, once I finished debugging I changed the hosts file back again.
I only recently installed a webserver on the laptop, & it seems that when I’d browsed to the laptop sufficient number of times, Firefox obviously juggled its internal preferences & then started using an older, cached version of the name->ip resolution. This added to the confusion, of course. It worked perfectly, then with no relevant changes at all suddenly started redirecting.
The solution? View history, & delete all references to the local server. Voila, incorrect ip resolution gets removed & redirection stops.
Crazy. Hopefully this saves you some time.
related
08.Nov.2009WordPress 2.8.5 dashboard not working in Firefox
I’ve been playing with an install of WordPress 2.8.5.. & along with a plethora of other bugs & issues, there was one in particular that stood out.
I couldn’t use the dashboard under Firefox.
It took a little searching, but I found the issue. Here’s how to solve it:
In your [blog root]/wp-admin/load-script.php file, around line 618, there is a line thus (this is all on one line, it’s just wrapping when displayed here):
echo “<script type=’text/javascript’ src='” . esc_attr($src) . “‘></script>\n”;
change it to:
echo “<script type=’text/javascript’ src='” . $src . “‘></script>\n”;
and around line 687 there is a line:
echo “<link rel=’stylesheet’ href='” . esc_attr($href) . “‘ type=’text/css’ media=’all’ />\n”;
change it to:
echo “<link rel=’stylesheet’ href='” . $href . “‘ type=’text/css’ media=’all’ />\n”;
So what’s going on here?
The lines before those two create urls for loading scripts & stylesheets, respectively.
Those urls are then getting run through esc_attr, which turns characters like & into strings like &
Often this is useful, but not here. What it means is that the html that is getting expressed in the page (you can see this if you pull up the dashboard & do a view source) looks like this:
<script type=’text/javascript’ src=’http://[site]/wp-admin/load-scripts.php?c=1&load=jquery,utils,quicktags&ver=b64ae9a301a545332f1fcd4c6c5351b4′></script>
and
<link rel=’stylesheet’ href=’http://[site]/wp-admin/load-styles.php?c=1&dir=ltr&load=dashboard,plugin-install,global,wp-admin &ver=6403d4cb3e6353f406fd43f1b0373ec2′ type=’text/css’ media=’all’ />
Which basically meant that the files weren’t getting loaded at all, the dashboard was looking like complete crap, and, well, not working at all.
The slight changes above simply remove that encoding, resulting in the correct urls:
<script type=’text/javascript’ src=’http://[site]/wp-admin/load-scripts.php?c=1&load=jquery,utils,quicktags&ver=b64ae9a301a545332f1fcd4c6c5351b4′></script>
and
<link rel=’stylesheet’ href=’http://[site]/wp-admin/load-styles.php?c=1&dir=ltr&load=dashboard,plugin-install,global,wp-admin &ver=6403d4cb3e6353f406fd43f1b0373ec2′ type=’text/css’ media=’all’ />
related
08.Nov.2009Firefox 3.5.5 screwy characters appearing
There’s something that’s bugged me ever since I upgraded to Firefox 3. Certain pages that used to work perfectly in Firefox 2 suddenly didn’t.
Instead there would be a mess on the page – lots of square boxes the size of characters with text inside them. Like this 
or maybe this 
Typically this would be some kind of character encoding issue ( the server/browser specifying/requesting UTF-8 instead of ISO-8859-1 etc), or having Auto-Detect universal set off in Firefox – and most sites around the net propose this as a solution (oh, & also recommend partial reinstalls of your O/S).
Uhh, no.
It’s actually a compression issue.
If you’re having this problem, the resolution is this:
Enter into the address bar
about:config
in the Filter textbox below, type
network.http.accept-encoding
You can also just start typing “accept-encoding” until it appears on the screen.
Double click the network.http.accept-encoding entry.
Now, on my browser, it was set to
gzip,deflate;q=0.9,compress;q=0.7
but should have been
gzip,deflate
So, type that into the box & hit OK, then restart your browser (just make sure you close all your windows too)
Voila, you can now surf the web without having to constantly switch back to IE.
related
23.Oct.2009Twitter OAuth Invalid Signature on friendships/create
This is a public service announcement.
I’ve been doing a bunch of work with Twitter recently & came across this problem.
When trying to do a friendships/create, I get back “OAuth Invalid Signature.”
I’m using Tweetsharp v0.15 preview (an excellent product, btw), but I don’t think this is a Tweetsharp issue, it’s a Twitter issue. People are really scratching their heads about it.
The Tweetsharp guys proposed a solution here, but that didn’t help me. In fact, the more I googled, the more erroneous solutions I found.
Here’s my setup. TwitCleaner (the app) has a consumer keys & secret. It would then get an access token/secret for the user, & use that token/secret to make the user follow @TheTwitCleaner. This is done so we can DM the user when their report is done. We encourage people to unfollow again (if they want to) once they get their report DM.
Anyway, pretty simple. We have valid OAuth token/secret from the user, so that’s not a problem.
We’re just trying to make the user follow @TheTwitCleaner, should be simple, right? No.
I wasted several hours on this. Among the solutions proposed (& wrong) were:
- You can’t use a consumer key/secret to follow the user those keys are associated with (ie, TwitCleaner the app has key/secret, but it’s associated with @TheTwitCleaner the Twitter account)
- The OAuth information is incorrect
- The request had to be made over https, not http (not something I have control over with TweetSharp, as far as I can tell)
- That because I was passing in Client information when making the request, that was gumming things up.
Well guess what? It was none of those.
Know what fixed it?
Passing in the username to follow in lower case.
I kid you not.
Now, @TheTwitCleaner is in Twitter with that combination of upper/lower case, so I was passing it exactly as stored. But no, apparently befriend (Twitter API friendships/create) needs lower case in order to work reliably.
So now you know. Hope that saves you some pain.