Twitter API Question posted in
The official Twitter API documentation can be found here.

BIGINT created as INT in mysql – Twitter API

I have a mysql database table in wordpress where I declare a BIGINT field without any problem.

But when I create the same table in mysql installed on my own computer the large numbers are stored as 2147483647, which in the INT maximum size.

Any ideas why that happens?

Here’s the table,

CREATE TABLE inPxUtBI_follow_data_tokens (
  id int(20) unsigned NOT NULL,
  screen_name varchar(15) NOT NULL,
  token tinytext,
  secret tinytext,
  time_data_cursor datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  friends_cursor bigint(20) NOT NULL DEFAULT -1,
  followers_cursor bigint(20) NOT NULL DEFAULT -1,  
  datetime_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  time_data_cursor_index smallint(10) unsigned NOT NULL,
  PRIMARY KEY  (screen_name)
) ;

EDIT: Version info

mysql> s
--------------
C:Program FilesMySQLMySQL Server 5.7binmysql.exe  Ver 14.14 Distrib 5.7.22,
 for Win64 (x86_64)

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.22-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3306
Uptime:                 30 min 3 sec

Threads: 1  Questions: 7  Slow queries: 0  Opens: 109  Flush tables: 1  Open tab
les: 102  Queries per second avg: 0.003
--------------

EDIT: Data info

Here is the update sql,

update inPxUtBI_follow_data_tokens set followers_cursor = %d WHERE screen_name = '%s' ["1599757792260458963","xxx"]
update inPxUtBI_follow_data_tokens set friends_cursor = %d WHERE screen_name = '%s' ["1600189794255483463","xxx"]

and here is the updated row,

id,screen_name,token,secret,time_data_cursor,friends_cursor,followers_cursor,datetime_created,time_data_cursor_index
111,xxx,yyy,zzz,"2018-05-13 15:37:06",2147483647,2147483647,"2018-05-13 11:59:57",9

EDIT: php code

public static function setUserCursor($table, $field, $screen_name, $next_cursor) {
        flog(DEBUG, 'setUserCursor', $next_cursor);
        $update_count = 0;
        if ($next_cursor > 0) {
            global $wpdb;
            $sql = "update $table set $field = %d WHERE screen_name = '%s'";
            $sqldata = array($next_cursor, $screen_name);
            flog(DEBUG, 'setUserCursor', $sql . ' ' . json_encode($sqldata));

            $update_count = $wpdb->query($wpdb->prepare($sql, $sqldata));
        }
        return $update_count;
    }

EDIT: the suggestion by @Progman throws up something curious

On my local machine I get,

update inPxUtBI_follow_data_tokens set friends_cursor = %d WHERE screen_name = '%s' ["1557868487712412145","xxx"]
update inPxUtBI_follow_data_tokens set friends_cursor = 2147483647 WHERE screen_name = 'xxx'

yet on the remote server I get,

update inPxUtBI_follow_data_tokens set friends_cursor = %d WHERE screen_name = '%s' [1600189862942848368,"xxx"]
update inPxUtBI_follow_data_tokens set friends_cursor = 1600189862942848368 WHERE screen_name = 'xxx'

Note the quotes around the value, “1557868487712412145”, on the first line.

I have pinned things down to this function where json data is retrieved.

function getFollowersIDs($user, $count, $cursor) {
    $url = $this->api . '1.1/followers/ids.json';
    $getfield = '?screen_name=' . $user . '&skip_status=1&count=' . $count . '&cursor=' . $cursor;
    $requestMethod = 'GET';
    $twitter = new TwitterAPIExchange($this->settings);
    $data = $twitter->setGetfield($getfield)->buildOauth($url, $requestMethod)->performRequest();
    $rtn = json_decode($data, true, 512, JSON_BIGINT_AS_STRING);
    flog(VERBOSE, 'getFollowersIDs', $data);
    flog(DEBUG, 'getFollowersIDs', 'CURSOR: ' . json_encode(array($rtn['next_cursor'])));
    flog(DEBUG, 'getFollowersIDs', is_string($rtn['next_cursor']) ? $rtn['next_cursor'] . ' IS string' : $rtn['next_cursor'] . ' IS NOT string');

    return $rtn;
}

The logs for these are respectively, for local and remote,

[getFollowersIDs] {"ids":[1492183206,913536285461147649,825717050538618880,961964711720910848,591132453,248777189,232207153,400934967,77967828,443634207],"next_cursor":1600147168522111920,"next_cursor_str":"1600147168522111920","previous_cursor":0,"previous_cursor_str":"0"}
[getFollowersIDs] CURSOR: ["1600147168522111920"]
[getFollowersIDs] 1600147168522111920 IS string

and

[getFollowersIDs] {"ids":[59150726,901375444934635520,385097832,331067377,194220828,540223123,2746743156,2271848935,819196471845253121,963324881906511877],"next_cursor":1597756074201108094,"next_cursor_str":"1597756074201108094","previous_cursor":-1597922052519508811,"previous_cursor_str":"-1597922052519508811"}
[getFollowersIDs] CURSOR: [1597756074201108094]
[getFollowersIDs] 1597756074201108094 IS NOT string

So, why does json_decode return a string on one machine and a bigint on another?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After much investigation I discovered that my local php was 32 bit.

    Installing 64 bit php resolved the issue.

    So, with this data,

    [{"ids":[59150726,901375444934635520,385097832,331067377,194220828,540223123,2746743156,2271848935,819196471845253121,963324881906511877],"next_cursor":1597756074201108094,"next_cursor_str":"1597756074201108094","previous_cursor":-1597922052519508811,"previous_cursor_str":"-1597922052519508811"}]

    this code,

    echo 'CURSOR: ' . json_encode(array($rtn[0]['next_cursor']))."n";

    produces this result (no quotes around value),

    CURSOR: [1597756074201108094].

    A rather obscure and misleading difference between different versions I think!

    Thanks to @Progman for leading me to the resolution.


  2. 0

    Seems like your local machine uses 4-byte integer (32 or 64bit PHP) and the remote one uses 8 byte integer (64bit PHP). The size of integer defines how big is BIGINT:

    $a = json_decode('{"n":1600147168522111920}', true, 512, JSON_BIGINT_AS_STRING);
var_dump(PHP_INT_SIZE, PHP_INT_MAX, $a);

// local machine output
int(4)
int(2147483647)
array(1) {
  ["n"]=>
  string(19) "1600147168522111920"
}

// remote machine output
int(8)
int(9223372036854775807)
array(1) {
  ["n"]=>
  int(1600147168522111920)
}

    As you can see, the value 1600147168522111920 cannot fit inside 4-byte integer and thus converted to string.

    Now, I don’t know the underlying implementation of wpdb::prepare, but apparently it will try to convert %d to the platform specific integer, truncating 1600147168522111920 to 2147483647:

    echo sprintf("%d", "1600147168522111920");
// 2147483647

    The solution is to change %d to %s. But before you do that, make sure that the value in question looks like a valid, big integer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search