Changing User Names in Confluence

At the office we use Atlassian Confluence as our internal Wiki system. I do like it, but it has some idiosyncrasies. Similar to JIRA there is no internal way to change user names. Not too long ago, I wrote a post giving the SQL needed to update JIRA. Here is the SQL needed to update Confuence.

This is a bit cleaner than the JIRA SQL because you really only need to update the first two lines.

SET @oldusername = "OLD_USER_NAME";
SET @newusername = "NEW_USER_NAME";
SET @tildedoldusername = CONCAT('~', @oldusername);
SET @tildednewusername = CONCAT('~', @newusername);
SET @locoldusername = CONCAT('LOC_', @oldusername);
SET @locnewusername = CONCAT('LOC_', @newusername);
-- Attachments
update ATTACHMENTS set creator = @newusername where creator = @oldusername;
update ATTACHMENTS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- Bandana
update BANDANA set bandanacontext = @newusername where bandanacontext = @oldusername;
-- Content
update CONTENT set creator = @newusername where creator = @oldusername;
update CONTENT set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT set username = @newusername where username = @oldusername;
update CONTENT set draftspacekey = @tildednewusername where draftspacekey = @tildeoldusername;
-- content_label
update CONTENT_LABEL set owner = @newusername where owner = @oldusername;
-- update CONTENT_LABEL set spacekey = @tildednewusername where owner = @tildedoldusername;
-- content_perl
update CONTENT_PERM set creator = @newusername where creator = @oldusername;
update CONTENT_PERM set lastmodifier = @newusername where lastmodifier = @oldusername;
update CONTENT_PERM set username = @newusername where username = @oldusername;
-- contentlock
update CONTENTLOCK set creator = @newusername where creator = @oldusername;
update CONTENTLOCK set lastmodifier = @newusername where lastmodifier = @oldusername;
-- decorator
update DECORATOR set SPACEKEY = @tildednewusername where SPACEKEY = @tildedoldusername;
-- extrnlnks
update EXTRNLNKS set creator = @newusername where creator = @oldusername;
update EXTRNLNKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- label
update LABEL set owner = @newusername where owner = @oldusername;
-- links
update LINKS set creator = @newusername where creator = @oldusername;
update LINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
update LINKS set destspacekey = @tildednewusername where destspacekey = @tildedoldusername;
update LINKS set destpagetitle = @tildednewusername where destpagetitle = @tildedoldusername;
-- notifications
update NOTIFICATIONS set creator = @newusername where creator = @oldusername;
update NOTIFICATIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update NOTIFICATIONS set username = @newusername where username = @oldusername;
-- os_propertyEntry
update OS_PROPERTYENTRY set entity_name = @locnewusername where entity_name = @locoldusername;
update OS_PROPERTYENTRY set string_val = @tildednewusername where entity_name = @tildedoldusername;
-- pagetemplates
update PAGETEMPLATES set creator = @newusername where creator = @oldusername;
update PAGETEMPLATES set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacegrouppermissions
update SPACEGROUPPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spacegroups
update SPACEGROUPS set creator = @newusername where creator = @oldusername;
update SPACEGROUPS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- spacepermissions
update SPACEPERMISSIONS set creator = @newusername where creator = @oldusername;
update SPACEPERMISSIONS set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACEPERMISSIONS set permusername = @newusername where permusername = @oldusername;
-- spaces
update SPACES set creator = @newusername where creator = @oldusername;
update SPACES set lastmodifier = @newusername where lastmodifier = @oldusername;
update SPACES set spacekey = @tildednewusername where lastmodifier = @tildedoldusername;
-- trackbacklinks
update TRACKBACKLINKS set creator = @newusername where creator = @oldusername;
update TRACKBACKLINKS set lastmodifier = @newusername where lastmodifier = @oldusername;
-- os_user and users
update os_user set username = @newusername where username = @oldusername;
update users set name = @newusername where name = @oldusername;

I used this code on about 50 users recently and did not have any problems.

Here are the steps I did:

  • I took the template above and made copies for each user that needed to be changed. Each was edited as needed.
  • I shutdown Confluence
  • I used the mysql command line utility to connect to my confluence database
  • I loaded each SQL file by hand, which will update the database
  • I relocated the cache which is in the data directory and called index.
  • I restarted confluence and rebuilt the cache

Everything went pretty well. After I did the user migration, I then integrated LDAP to our AD and every user now has one less password to remember.

Hope this helps someone.

Posted in Geek Out | Comments Off

Samsung Captivate

So, I lied in my last post. My newest toy is a Samsung Captivate smart phone. It is powered by Android.

I do like it, but all it does take some getting used to the touch screen.

My biggest annoyance is the bloatware, shovelware, crapware, whatever you want to call it. AT&T installs a ton of stuff. Time to get rid of it!

First you need to gain super-user privileges. Here is how to do that:

  1. Download and install the Android SDK (download and install guide)
  2. Install the Samsung Drivers (download location)
  3. Set your phone to connect in Mass Storage mode (Settings->Applications->USB Settings->Mass storage)
  4. Connect the phone via USB. Mount the drive (select Mount from the USB drop down notification).
  5. Download the Root Update Zip and rename it update.zip
  6. Copy the update.zip to your internal SD card on the phone
  7. Disconnect your phone’s USB
  8. Turn on USB Debugging Mode (Settings->Applications->Development->USB Debugging)
  9. Connect the phone via USB again
  10. Use a command prompt to cd to the SDK’s tools directory. Mine is c:\android\tools.
  11. Run the recovery mode reboot command: adb reboot recovery
  12. The phone will reboot into recovery mode. Select Reinstall Packages on the phone’s menu using the Volume Up/Down buttons and select using the Power button. The phone will install the update and reboot.
  13. You should now see a Superuser Permission application in the Applications panels.

Now it is time to remove some crap!

  1. Make sure your phone is connected via USB and the USB Debugging mode is still turned on.
  2. From a command prompt, cd to the SDK’s tools directory.
  3. Run the SDK shell: adb shell
  4. Become root: su
  5. To remove apps, we will just rename them. For example this removes the AT&T Nav app: mv /system/app/ATTNav-Samsung-Vega-1488.apk /system/app/ATTNav-Samsung-Vega-1488.tmp
  6. Here is a list of applications and their actual application file names:
    • AT&T Nav — /system/app/ATTNav-Samsung-Vega-1488.apk
    • AT&T Radio — /system/app/ATT_Radio_2.1.07.apk
    • MobiTV — /system/app/MobiTV_800x480_320_20100610_1.0.0.15-118829.apk
    • MobileBanking — mv /system/app/MobileBanking.apk
    • AT&T Maps — /system/app/attmaps20-1094.apk
    • Where — /system/app/WHERE_Samsung_GT-I900_Vegas_EN_1.9.3_GM_06112010.apk
    • AT&T Hotspots — /system/app/WISPr_v41.apk /system/app/WISPr_v41.tmp
    • AT&T FamilyMap — mv /system/app/androidlauncher2.1-61x612010_06_11t15_49.apk
    • AT&T Music mv /system/app/ATT-P11_PROD.apk
    • Instant Messaging — /system/app/IM-Signed.apk
    • AllShare — /system/app/Dlna.apk
  7. Disconnect your USB, turn off USB Debugging and see how your apps are missing!

Here are the list of sources I used to get this going:

Posted in Geek Out | Comments Off

I think this will be my next toy…

I am thinking about getting a Henry Repeating Arms H001T lever action rifle.

Here is the beauty.

Bud’s wants $320 (plus fees) for it. Not a bad price.

Yeah, it is a 22LR. Yeah, it is just a plinker. But my 30-06 cost a buck a shell to fire and that is a bit steep for lots of practice.

I still need to get to the range and get some time in with existing toys, so I will probably wait until fall.

Posted in Random Rants | Comments Off

Renaming JIRA Users

So you use JIRA do you? Nice, so do I.

Ever have someone get married? divorced? a typo in their user name? Every try and change it? Yeah, not so easy.

Atlassian has not set a release where there will be a fix, so we humble JIRA administrators have to make due with what we can. At this current job we are using version 4.0 (with plans to upgrade this summer). We also used email addresses as the user name rather than the AD based user name. If you want to use AD integration, it is best to match user names. So we had a pressing need to rename everyone. What is the right way? Atlassian tells us to export, search and replace, then import. Yep. Kludgey. Plus my import was not working for some reason. What else can we do? Database Updates! Yeah!

I found my first SQL example in the 7 year old rename user bug. It was published by Jeff Turner. It mostly works on the 4.0 scheme. Some updates are needed.

Here is the updated SQL for a 4.0 schema:

update jiraissue set reporter='newuser' where reporter='olduser@example.com';
update jiraissue set assignee='newuser' where assignee='olduser@example.com';
update jiraaction set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update changegroup set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update changeitem set OLDVALUE='newuser' where OLDVALUE='olduser@example.com' and FIELD='assignee';
update changeitem set NEWVALUE='newuser' where NEWVALUE='olduser@example.com' and FIELD='assignee';
update searchrequest set authorname='newuser' where authorname='olduser@example.com';
update searchrequest set username='newuser' where username='olduser@example.com';
update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user";
update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user";
update searchrequest set authorname='newuser' where authorname='olduser@example.com';
update membershipbase set USER_NAME='newuser' where USER_NAME='olduser@example.com';
update OS_CURRENTSTEP set owner='newuser' where owner='olduser@example.com';
update OS_CURRENTSTEP set caller='newuser' where caller='olduser@example.com';
update OS_HISTORYSTEP set owner='newuser' where owner='olduser@example.com';
update OS_HISTORYSTEP set caller='newuser' where caller='olduser@example.com';
update fileattachment set author='newuser' where author='olduser@example.com';
update filtersubscription set username='newuser' where username='olduser@example.com';
update project set lead='newuser' where lead='olduser@example.com';
update userbase set username='newuser' where username='olduser@example.com';
update customfieldvalue set stringvalue='newuser' where stringvalue='olduser@example.com';
update columnlayout set username='newuser' where username='olduser@example.com';
update portalpage set username='newuser' where username='olduser@example.com';
update userhistoryitem set USERNAME='newuser' where USERNAME='olduser@example.com';
update worklog set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update worklog set UPDATEAUTHOR='newuser' where UPDATEAUTHOR='olduser@example.com';
update notification set notif_parameter='newuser' where notif_parameter='olduser@example.com';
update component set lead='newuser' where lead='olduser@example.com';
update columnlayout set username='newuser' where username='olduser@example.com';

Once you update your DB with all of those update statements you need to refresh your JIRA indexes. This is done via the JIRA Administrator->Indexes page.

I have done this for a couple of users today and everything looks OK so far. I will be doing it for bunches of users in the next couple of days and will update this post if needed.

Posted in Geek Out | Comments Off

Wednesday Funny

An old Italian lived alone in New Jersey . He wanted to plant his annual tomato garden, but it was very difficult work, as the ground was hard. His only son, Vincent, who used to help him, was in prison. The old man wrote a letter to his son and described his predicament:

Dear Vincent,
I am feeling pretty sad, because it looks like I won’t be able to plant my tomato garden this year. I’m just getting too old to be digging up a garden plot. I know if you were here my troubles would be over. I know you would be happy to dig the plot for me, like in the old days.
Love, Papa

A few days later he received a letter from his son.

Dear Pop,
Don’t dig up that garden. That’s where the bodies are buried..
Love,
Vinnie

At 4 a.m. the next morning, FBI agents and local police arrived and dug up the entire area without finding any bodies. They apologized to the old man and left.
That same day the old man received another letter from his son.

Dear Pop,
Go ahead and plant the tomatoes now. That’s the best I could do under the circumstances.
Love you,
Vinnie

Posted in Random Rants | Comments Off

Ubuntu eSATA Configuration

We use a couple of external hard drive enclosures for offline backups at work. The best connectivity is via eSATA (USB being a bit flakier and much slower).

It works great on my Ubuntu 10.04 install on my Dell laptop, but that is my personal box. I was able to provision a Dell desktop and a PCI SATA card with two eSATA ports. It is a Rosewill RC-209-EX with the SIL3114 chipset.

After plugging it all in, my drives were just not recognized. No matter the boot order or what I did- no go. So, I buckled and read the directions. :-) As it turns out, this card can only do four total SATA ports and the four internal are on by default and the two external are off. I reset the jumpers to turn on both external and two internal.

Now it detects the drives. Well, actually it detects one drive. There are two in the enclosure.

So far I have had no luck getting the other drive in the enclosure detected. This is quite frustrating.

If anyone happens to read this and know what I can do to solve this, please leave a comment. If I fix it, I will add and update to this post.

UPDATE: I have emailed Rosewill (no response yet). I also set the jumpers on the WD drives to force them to be 150 MB/s. This had no effect either. I am still without a solution and am pretty frustrated.

UPDATE 2: I heard back from Rosewill. No assistance at all, I responded- but no word back yet. I have now also tried a SIL-3512 eSATA card (the only eSATA card Fry’s had) and it does not work either. This is very frustrating.

UPDATE 3: I returned the SIL-3512 to Fry’s (no problems there) and hit up NewEgg for a SNT SNT-PCIX Card Silicon Image 3132 SATA II PCI Express 2 x eSATA Port Multiplier RAID Controller Card. It is PCI-E but I have a slot (it appears anyway) and it works with a drive array with multiple drives. About the same price as the other two as well. Why is this so hard? I should get it next week. I also ordered an additional eSATA cable so I have two. This looks really cool as well if you just need eSATA ports and you have spare SATA ones eSATA bracket.

UPDATE 4: The card I ordered from NewEgg works. Well mostly. Sometimes I do not get both drives showing up. I re-seat the cables, and power the enclosure back on and all is well. This was pretty darn annoying if I say so myself.

Posted in Geek Out | Tagged | Comments Off

MySQL Flush Privileges Error

I spent a little time recently creating a new blog to act as a business landing page.

To make it simple, I am going to use WordPress. Actually installing WordPress is very simple; just unzip it in the right place and follow the wizard to create a wp-config.php file.

The trick I had was creating the database and user. I ran these commands:
mysql> CREATE DATABASE newdatabase;
mysql> GRANT ALL PRIVILEGES ON newusername.* TO “newdatabase”@”localhost”
-> IDENTIFIED BY “somepassword”;
mysql> FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command had an error:
ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist

A bit of google-fu revealed some SQL to run. My supposition is that during a recent upgrade of mysql server, this table became a requirement but was not created as part of the upgrade.

Here is the SQL to create an empty table in the mysql database.
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
COMMENT='MySQL Foreign Servers table';

Once I created that table, the FLUSH PRIVILEGES command worked without error and WordPress did all the DB work needed without complaint.

Posted in Geek Out | Tagged , | Comments Off

Free WiFi at Select Airports

I just found out that there will be free wifi at about 40 airports this holiday season. (via LifeHacker).

It is offered by Google. Check out the details here: http://www.freeholidaywifi.com/

I am traveling for Thanksgiving but I probably will not carry a laptop. Others may find the list useful though.

Posted in Geek Out | Comments Off

An Apology

I recently received an email about a rant I made several years ago after the shootings at Virginia Tech. You can read it here.

The individual who wrote the email was connected to the wedding party that I made a passing reference too. I should probably have been clearer with my words because I referred to them as a “drunk wedding party”. I will readily admit I was not there and I do not know how drunk they were. I am very clear on the fact that the Scales brothers were the ones with guns and were the ones who did the shooting. The wedding party will forever have to deal with the tragedy of that night and I apologize for my characterization of the party as a whole.

Posted in Random Rants | Comments Off

My Galileoscope Arrived

Awhile back I posted about a program to bring telescopes to schools. Specifically the Galileoscope. Well this was on my stoop today:

Galileoscope Box

I unpacked it on my dining table and this is what I ended up with:

Galileoscope Unpacked

I tried to follow the paper instructions, which are decent. But I highly recommend the PDF on the Galileoscope site. You can download it here: PDF Download Page

When all was done, this is what I ended up:

Galileoscope Assembled

It is still light out, so I cannot check out any stars yet- but it is pretty clear in Austin today so I am hopefully I can check out some stars or the moon.

Posted in Random Rants | Comments Off