I installed the Oauth and Twitter modules on my Drupal 7.22 site on an Ubuntu 12.04 server, w/ Postgres 9.1/PHP5.3. I added an application in twitter, and added the keys in the module settings tab. When I added the account/authorize app, in the tweets tab, and checked tweets on the checkbox, I see a view link appear after I save the configuration. When I click on that link it takes me to this page:
http://nimhq.net/?q=tweets/nimhq
I get this error:
PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type numeric: "nimhq" LINE 3: WHERE twitter_uid = 'nimhq' ^: SELECT * FROM {twitter_account} WHERE twitter_uid = :id OR screen_name = :id; Array ( [:id] => nimhq ) in twitter_account_load() (line 82 of /home/john/public/sites/all/modules/twitter/twitter.inc).
I've found this report here which seems similar:
http://drupal.org/node/1889336
And these reports in Drupal Core:
http://drupal.org/node/1932612
http://drupal.org/node/1003788
Not really sure if something patched there will fix this, or what really is the problem. I did try reinstalling the modules a few times, but consistently get the same error above.
Comment | File | Size | Author |
---|---|---|---|
#17 | twitter-n1985708-17.patch | 1.71 KB | DamienMcKenna |
#11 | twitter-n1985708-11.patch | 2.7 KB | DamienMcKenna |
Comments
Comment #1
christoph CreditAttribution: christoph commentedThe issue arises from Postgres not automatically casting between data types (Mysql does - I think not per standards, but not sure). The fix is relatively easy - cast the value in PHP before passing it into the query. So line 78 of twitter.inc could read:-
Note that the value :tid is cast to integer by intaval($id). This fixed the issue for me.
Comment #2
floydwilde CreditAttribution: floydwilde commentedThanks for that christoph. There was a code change in 5.8 that effected that same block of code:
I would still get the same error w/ that code, and I wasn't sure how to apply christoph's patch to that, so I reverted my "twitter.inc" in the 5.8 module to the one from 5.7, and applied this:
And now things are working as expected for me on Postgres.
FYI: the patch in 5.8 was related to this bug on SQL Server: https://drupal.org/node/1905324
Comment #3
erik.erskine CreditAttribution: erik.erskine commentedAs @christoph mentions in #1, PostgreSQL (and possibly other databases) don't like comparing the numeric
twitter_uid
column to a string.Given that
$id
parameter intwitter_account_load
can be either a twitter uid or a screen name, we probably need to do one of two queries depending on it's type.If
$id
is numeric, it could be either a twitter uid or a screen name:But if
$id
is a string, it is only ever a screen name:Patch is attached (works on both postgresql and mysql)
Comment #4
thatoneguy CreditAttribution: thatoneguy commentedThe patch in #3 would be the way to go, and it works.
intval(string)
evaluates to 0, which is probably not what was intended.Comment #5
thatoneguy CreditAttribution: thatoneguy commentedIs there a case where a Twitter username could conflict with another user's uid?
Comment #6
erik.erskine CreditAttribution: erik.erskine commentedTheoretically yes, but I think that is outside the scope of this issue.
If you had one account with a screen name of
5
and another with a uid of <5> then the query concerned would match both. That's true regardless of this change though. All this patch does is assume that ifid
is non-numeric, it cannot be a uid, thereby avoiding the error that occurs when trying to cast it to a number.Comment #7
DamienMcKennaTriggering testbot.
Comment #8
DamienMcKennaTriggering testbot.
Comment #10
DamienMcKennaComment #11
DamienMcKennaGiven that we control the internal API, I think a much better approach would be to specifically tell twitter_account_load() whether the $id is a uid or screen name.
Could someone please give this a quick test?
Comment #12
DamienMcKennaOf course the 7.x-6.x branch solved this by switching to using the screen name for everything. Doh.
Comment #13
DamienMcKennaOTOH the 6.x-5.x branch uses the uid for everything.
Comment #14
erik.erskine CreditAttribution: erik.erskine commented#11 works for me. Using postgres 9.3 and 7.x-5.x branch.
Comment #16
DamienMcKenna@ingaro: Thanks for the review. This has been committed to the 7.x-5.x branch.
Comment #17
DamienMcKennaThis is a backport of the changes to the 6.x-5.x branch.
Comment #18
DamienMcKennaI've committed the changes to the 6.x-5.x branch too.