Spent probably about 3 hours today porting the database schema to MySQL 5 along with finding some bugs in the process. Pretty minor ones, mainly to do with how things could be improved to improve compatibility with schemas written with postgresql in mind.<\/p>\n
While chasing up some stuff on why the serial type alias in mysql wasn’t exactly the same as postgresql serial type (which is an integer with a sequence and default value) i found this gem in the postgresql docs:<\/p>\n
Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.<\/p><\/blockquote>\n
Great huh? So upgrade postgresql and don’t go sifting through your tables (now come on, everybody<\/strong> uses a serial\/auto_increment field in a lot of tables) you loose!<\/p>\ni.e. there’s bugs in memberdb now that weren’t there when i started and i didn’t change any code to make them. hrrm…<\/p>\n
anyway, i’ll write at some time the few easy steps it took to get the schema across (it takes no time once you know what you’re doing – like a few commands and a few search and replaces).<\/p>\n
for now, you can get stuff from arch: stewart@flamingspork.com–memberdb\/memberdb–mysql–0.4<\/p>\n
the schema loads, i’ll have to change one bit of code to make it all work – otherwise everything should be fine (but let me test first – or provide fixes, not complaints :)<\/p>\n","protected":false},"excerpt":{"rendered":"
Spent probably about 3 hours today porting the database schema to MySQL 5 along with finding some bugs in the process. Pretty minor ones, mainly to do with how things could be improved to improve compatibility with schemas written with … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6,13,14],"tags":[],"class_list":["post-345","post","type-post","status-publish","format-standard","hentry","category-linux-aus","category-memberdb","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-5z","jetpack-related-posts":[{"id":3257,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/13\/which-is-bigger-mysql-or-postgresql\/","url_meta":{"origin":345,"position":0},"title":"Which is bigger: MySQL or PostgreSQL?","author":"Stewart Smith","date":"2013-03-13","format":false,"excerpt":"From my previous posts, we have some numbers (excluding NDB) for the size of MySQL, so what about PostgreSQL? Here, I used PostgreSQL git trunk and classing things in the contrib\/ directory as plugins. I put the number of lines of code in the src\/backend\/storage directory down as storage engines\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2447,"url":"https:\/\/www.flamingspork.com\/blog\/2012\/01\/23\/mysql-and-postgresql-cloud-offerings-linux-conf-au-2012-miniconf-talk-by-myself-and-selena\/","url_meta":{"origin":345,"position":1},"title":"MySQL and PostgreSQL Cloud Offerings – linux.conf.au 2012 miniconf talk by myself and Selena","author":"Stewart Smith","date":"2012-01-23","format":false,"excerpt":"Selena and I gave a talk on the various issues of running databases \"in the cloud\" at the recent linux.conf.au in Ballarat. Video is up, embedded below:","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":452,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/08\/18\/election-results-page-performance\/","url_meta":{"origin":345,"position":2},"title":"Election results page performance","author":"Stewart Smith","date":"2005-08-18","format":false,"excerpt":"Did the switch of the election-results page from postgresql to mysql today. It's about twice as fast (crappy statistics there, but it's an approximation). This is using InnoDB tables. With prepared statements we should be able to increase performance even further. I enabled the query log for a load of\u2026","rel":"","context":"In "linux-aus"","block_context":{"text":"linux-aus","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-aus\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":661,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/04\/19\/postgresql-73-sql-key-words\/","url_meta":{"origin":345,"position":3},"title":"PostgreSQL 7.3: SQL Key Words","author":"Stewart Smith","date":"2006-04-19","format":false,"excerpt":"PostgreSQL: Documentation: Manuals: PostgreSQL 7.3: SQL Key Words It's very annoying that 'user' is a reserved word in postgresql. You also get really crappy error messages (at least with the various forms of quoting I've tried to use) when you try to create a table called 'user' $ psql web\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3664,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/04\/ghosts-of-mysql-past-part-2\/","url_meta":{"origin":345,"position":4},"title":"Ghosts of MySQL Past: Part 2","author":"Stewart Smith","date":"2014-02-04","format":false,"excerpt":"This continues on from my post yesterday and also contains content from my linux.conf.au 2014 talk (view video here). Way back in May in the year 2000, a feature was added to MySQL that would keep many people employed for many years - replication. In 3.23.15 you could replicate from\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2286,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/02\/08\/drizzle-metadata-tables\/","url_meta":{"origin":345,"position":5},"title":"Drizzle metadata tables","author":"Stewart Smith","date":"2011-02-08","format":false,"excerpt":"Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I'd have a look at what we have in Drizzle. It's pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view: drizzle> select table_schema,count(table_name) -> from information_schema.tables\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=345"}],"version-history":[{"count":0,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/345\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}