Closed Bug 1036092 Opened 10 years ago Closed 10 years ago

convert fxa_oauth to use utf8

Categories

(Cloud Services :: General, defect)

x86
macOS
defect
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: jrgm, Assigned: ckolos)

Details

Currently, the fxa_oauth database in stage, and its tables {clients,tokens,codes} are `DEFAULT CHARACTER SET latin1`. While most of the non-BINARY, VARCHAR fields are 7-bit strings in practice (url, email, identifiers like 'profile') it would be useful to allow the clients.name field to be multilingual capable [and in general to just use utf8 from the beginning of production use]. I've dumped out the tables in stage, and `iconv` says there are no non 7-bit strings in the varchar data. So we could perhaps modify the tables in place. Alternatively, there are articles on the web that suggest dumping the data, editing/extending the charset/collation params to use utf8, and re-importing that data. I prefer slightly the latter, but won't hold to it strongly.
is there any time that's better for this work to go on? I'm assuming that fxa train-16 will be unaffected by this as well, correct?
I think you can pick a time that works for you. No particular rush, but sometime in the next 7 days would be good to just get it over with ahead of whenever real production use starts.
I very strongly agree with :jrgm on this, that we want this done before we have production-level traffic.
Since the data that's in the DB *should* be in the client configs, I would think that we can just delete the existing DB. I will created them with the proper charsets and the app will repopulate them on startup. :jrgm - Sound good?
Flags: needinfo?(jrgm)
I liked :jrgm's first suggestion better and tested in stage. All looks as it should, so proceeding in prod.
Flags: needinfo?(jrgm)
Completed in stage and prod. Database Create Database fxa_oauth CREATE DATABASE `fxa_oauth` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ Table Create Table clients CREATE TABLE `clients` (\n `id` binary(8) NOT NULL,\n `secret` binary(32) NOT NULL,\n `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `imageUri` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `redirectUri` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `whitelisted` tinyint(1) DEFAULT '0',\n `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Table Create Table codes CREATE TABLE `codes` (\n `code` binary(32) NOT NULL,\n `clientId` binary(8) NOT NULL,\n `userId` binary(16) NOT NULL,\n `email` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `scope` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (`code`),\n KEY `codes_client_id` (`clientId`),\n KEY `codes_user_id` (`userId`),\n CONSTRAINT `codes_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `clients` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Table Create Table tokens CREATE TABLE `tokens` (\n `token` binary(32) NOT NULL,\n `clientId` binary(8) NOT NULL,\n `userId` binary(16) NOT NULL,\n `email` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `type` varchar(16) COLLATE utf8_unicode_ci NOT NULL,\n `scope` varchar(256) COLLATE utf8_unicode_ci NOT NULL,\n `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (`token`),\n KEY `tokens_client_id` (`clientId`),\n KEY `tokens_user_id` (`userId`),\n CONSTRAINT `tokens_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `clients` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Status: NEW → RESOLVED
Closed: 10 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.