guyblade.com #/


PSN
Gamercard


2010 Jun 18 elly-miang


Guy Blade Guy Blade---04:19:00


It may have "My" in the title, but I disclaim all responsibility
So, a while back Blogger announced that they were ending support for users who used ftp/sftp to post. I had been one of those users since before Blogger was bought by Google, so I was a bit annoyed. Nevertheless, I basically ignored the whole issue and the cutoff was over a month ago. Since then, guyblade.com hasn't updated due to the fact that it is driven by blogger. I've still been crossposting there, but the updates are just not pushed out. After freasha was replaced last week, I finally decided to do something about the issue of keeping guyblade.com (at least) up to date. Originally, I had considered creating an entire set of posting infrastructure with a metaweblog framework and the like, but I decided to go with a simpler, if less robust, solution.

Basically, what I intended to do was to import all of the old posts from guyblade.com into a database, then use a periodic script to sync new posts into the database by reading the blogspot atom feed. This mostly allows continuity of everything that was working before (comments, etc) without requiring a whole lot of work. I've mostly coded up the entire thing at this point, but I've run into two major problems, both due to MySQL.

Firstly, I should note that SQL requires certain things of literals used in queries. The main thing that it requires (as far as this problem is concerned) is that numeric literals must not be quoted. That is, when saying "SELECT blah FROM foo WHERE value = 3", the 3 must not be quoted. MS-SQL is very pedantic about this, but MySQL has always been rather lax and allowed you to quote numbers as strings without issue. Or at least, that is what I thought.

It turns out that for some very large numbers, MySQL will fail when doing comparisons between numbers and numeric strings. In fact, I've narrowed it down a fair amount:


mysql> select 15271058505994179526 = "15271058505994179526" ;
+-----------------------------------------------+
| 15271058505994179526 = "15271058505994179526" |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+

mysql> select 16951940091101346629 = "16951940091101346629";
+-----------------------------------------------+
| 16951940091101346629 = "16951940091101346629" |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+



The worst part about this is that I discovered this while using the perl DBI interface which is supposed to handle the type massaging on its own when I do parameterization. Eventually, I replaced the "= ?" in my query with an " = CONVERT(?, DECIMAL(60))" which fixed the problem (I used DECIMAL(60) as the column type because these values exceed the limits of BIGINT and I didn't want to screw around with finding the right size of DECIMAL to use). Incidentally, mysql will happily accept a quoted numeric value in an insert even with these large numbers. The problem that I had was violating primary key constraints when this comparison failed and I tried to insert the same userid again.

The second problem I have not yet managed to solve. Because I use php for certain parts of guyblade.com already, I decided to implement the updated guyblade.com code in php as well. Also, I decided that I'd finally move away from the mysql interface and use the (shiny, new) mysqli interface. It is somewhat more clunky than the equivalent DBI interface, but it at least allows for parameterized queries (though it doesn't allow you to merge bind and execute into a single call). Nevertheless, it is mostly straightforward. Where I ran into problems was when I was trying to pull data out of my database. I decided to use LARGETEXT fields for the "body" field of my blog postings. This seemed reasonable as the overhead is rather small and I can conceive of situations where I might want to dump more than 16M of text in a post.

It turns out, however, that the mysqli interface cannot actually read values from large text fields. The old mysql interface can do this properly, so I will probably get to rewrite all of the code using the old interface. Luckily only a few dozen lines actually dealt with the sql directly...

Published by XPostGTK+


Permalink to this post     Com/0

Archive
Copyright 2002-2024 Blade Libergnosis//Powered By Blogger