Say that you need to add a unique index to an existing catalog table, so you decide to add a new column with a consecutive number in it. You may make it an auto_increment column, but that would work only for new inserts. How would you populate the column for the exisiting rows?
If you’re using mySQL you may try this little recipe:
First let’s add the column that we’ll use later as primary key.
alter table mytable add column id integer first;
Now we declare a counter and use it to populate each row.
set @i = 0;
update mytable set id=(@i:=@i+1);
Finally, we transform the new column into the primary key.
alter table mytable modify column id integer auto_increment primary key;
There you go. A new auto-increment primary key that has been correctly populated for all existing rows.
Month: August 2008
Solved mysterious Firefox 3.0.1 crashes
A few months ago I installed a Firefox 3 beta in my Linux laptop to… well you know, just to fool around. And it crashed spectacularly. I tried again with RC1. No such luck. When 3.0 came around I installed it in several machines –including my wife’s– but it still refused to run in mine.
Today I decided I had enough and ran FF under strace and generally whacked it around hoping to make it work without much luck. I finally decided to apt-get install iceweasel 3.0.1 expecting to have apt-get iron out any possible library conflict but the problem persisted. If anything Firefox crashed even harder because Debian’s Iceweasel does not include Mozilla’s Crash Reporter — it suggests that you install bug-buddy instead. I obligued dutifully and much to my surprise, bug-buddy’s crash report had an extensive and very useful stack trace that revealed libpango as the real culprit.
#0 0xffffe410 in __kernel_vsyscall ()
#1 0xb7db91ab in waitpid () from /lib/i686/cmov/libc.so.6
#2 0xb6382865 in gnome_gtk_module_info_get () from /usr/lib/libgnomeui-2.so.0
#3 0xb7113e2d in XRE_LockProfileDirectory () from /usr/lib/iceweasel/xulrunner/libxul.so
#4
#5 0xb2d94c77 in TtfUtil::GetNameInfo () from /usr/lib/libgraphite.so.3
#6 0xb2d94ebb in TtfUtil::Get31EngFamilyInfo () from /usr/lib/libgraphite.so.3
#7 0xb2d940bc in gr::Font::UniqueCacheInfo () from /usr/lib/libgraphite.so.3
#8 0xb2d9308c in gr::Font::initialiseFontFace () from /usr/lib/libgraphite.so.3
#9 0xb2d93278 in gr::Font::RenderRangeSegment () from /usr/lib/libgraphite.so.3
#10 0xb2dbeeb0 in gr::RangeSegment::RangeSegment () from /usr/lib/libgraphite.so.3
#11 0xb383899e in graphite_PangoGlyphString (text=0xbfb73e88 " ?<8A>?¸?¶", length=1, xftfont=0xb36e6000, glyphs=0xb2cad800, language=0xb7b58a94 "en") at pangographite.cpp:183
#12 0xb38393c0 in graphite_engine_script_shape (engine=0xb2c35a80, font=0xb36e6000, text=0xbfb73e88 " ?<8A>?¸?¶", length=1, analysis=0xbfb73e94, glyphs=0xb2cad800) at graphite-module.c:155
#13 0xb6c89e9a in pango_coverage_new () from /usr/lib/libpango-1.0.so.0
#14 0xb6c9c94a in pango_shape () from /usr/lib/libpango-1.0.so.0
#15 0xb7897e96 in gfxPangoFontGroup::SetGlyphs () from /usr/lib/iceweasel/xulrunner/libxul.so
*snip!*
After zapping libpango I had a working Iceweasel and a few seconds later I was running FF 3.0.1 as well. Now I will check the status of my 23 installed extensions (or at least of those I just can’t live without) and consider Firefox 3.0.1 seriously for my everyday browsing.
X forwarding through SSH in HP-UX
If you try to do X forwarding by SSHing to an HP-UX host, you may get the dreaded “Can’t get IP address for X11 DISPLAY.” error. This is more common than you might think, and the reason is that an out-of-the box installation of HP-UX has four or five /etc/nsswitch.conf *examples* for you to install, but not an actual /etc/nsswitch.conf file. I guess this is buried somewhere on the documentation — hey, it might even be a FAQ, but I guess that shipping with a reasonable default wouldn’t hurt.
Well anyway, the following minimal /etc/nsswitch.conf should do for the vast majority of scenarios I can imagine:
# echo "hosts: files dns" > /etc/nsswitch.conf
Now SSH X forwarding should work and a myriad other disasters waiting to happen will surely be averted.
update…where id in (select…) and mySQL ERROR 1093
What’s wrong with this picture?
mysql> update ignore flight set intl=1 where id in (select f.id as id from flight f left join airport a on a.id=f.origin where a.country<>'mx' for update);
ERROR 1093 (HY000): You can't specify target table 'flight' for update in FROM clause
This construct is invalid in mySQL
According to the manual,
You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery’s FROM clause and the update target.
The equivalent multi-table update does the same and works as intended:
update flight f left join airport a on a.id=f.origin set f.intl=1 where a.country<>'mx';