Anybody used bit.io as postgresql database backend?

stevencmon

Link to test case:
https://www.closest.com/bit.io/

Debugger code (debug.datatables.net):
https://debug.datatables.net/akenan

Error messages shown:
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 7 LOG: Error occurred when processing query: Unable to send message while query "" still in progress. QUERY: Extended Protocol Message LOG: Error occurred when processing query: Unable to send message while query "" still in progress. QUERY: Extended Protocol Message ERROR: unable to read data DETAIL: child connection forced to terminate due to client_idle_limit:300 is reached SSL SYSCALL error: EOF detected in /var/www/www.closest.com/public_html/bit.io/php/lib/Database/Query.php:204 Stack trace: #0 /var/www/www.closest.com/public_html/bit.io/php/lib/Database/Query.php(204): PDO->rollBack() #1 [internal function]: DataTables\Database\Query::rollback() #2 /var/www/www.closest.com/public_html/bit.io/php/lib/Database.php(314): call_user_func() #3 /var/www/www.closest.com/public_html/bit.io/php/lib/Editor.php(708): DataTables\Database->rollback() #4 /var/www/www.closest.com/public_html/bit.io/php/table.global_editing.php(46): DataTables\Editor->process in /var/www/www.closest.com/public_html/bit.io/php/lib/Database/Query.php on line 204

Description of problem:
Trying to use datatables editor with https://bit.io as a postgres database.

Query doesn't complete. times out after 300 seconds

standalone php pdo is able to query data. psql works ok too.

Thanks

allan

It sounds to me like the query hasn't completed by the end of the 5 minute timeout. How large is your data set? That it says query "" is rather odd!

I haven't actually heard of bit.io before - does their management console let you see the queries that have been run against your databases?

And just to confirm, as you saying that the Editor PHP works with your own Postgres server, or that a PDO query against bit.io works?

Can you show me your config.php (with the user / password / database all changed to 123 or whatever as well please?).

Allan

stevencmon

yes, well I was looking for "data cleansing" tools and ran across bit.io.

I'm not sure who they think that their customers really are. They promote that they're postgres based. However, they don't support the serial, decimal or numeric data types. So, off to the races they don't support your "generated" create table with a 'serial' id column. they also subset the functions available.

It appears that in reality they want their customers to be able to upload spreadsheets and for their customers to "share" without emailing spreadsheets.

I've successfully uploaded a 33MB CSV file with ~220K rows with no problem. Well, except that it doesn't do any data type determination and it appears that you can't create any indexes so joining tables will have to be horrid with the full table scans...
So, this isn't going to be useful for my needs but you might be interested. If I had found all of the "potholes" I wouldn't have submitted this so you can close it.

My bit.io table has exactly 1 row...

I didn't set up a postgresql server but I might try that out but I'm happy with MySQL/MariaDB for now. I'll include my simple PHP/PDO code that works (along with it's response.)

My "config.php" file:

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

/*
 * DB connection script for Editor
 * Created by http://editor.datatables.net/generator
 */

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');

/*
 * Edit the following with your database connection options
 */
$sql_details = array(
        "type" => "Postgres",
        "user" => "username",
        "pass" => "password",
        "host" => "db.bit.io",
        "port" => "5432",
        "db"   => "bitdotio",
        "dsn"  => ""
);

My PHP/PDO code:

<?php

$host='db.bit.io';
$db = 'bitdotio';
$username = 'username';
$password = 'password';

$dsn = "pgsql:host=$host;port=5432;dbname=$db;user=$username;password=$password"
;

try {
  // create a PostgreSQL database connection
  $pdo = new PDO( $dsn );

  // display a message if connected to the PostgreSQL successfully
  if ( $pdo ) {
    print "Connected to the <strong>$db</strong> database successfully!";
  }

  $stmt = $pdo->query( 'SELECT *  FROM "scm/data_cleanse"."global_editing"');
  while ( $row = $stmt->fetch() ) {
    print_r( $row );
  }
} catch ( PDOException $e ) {
  // report error message
  print $e->getMessage();
}

My PHP/PDO response:

Connected to the <strong>bitdotio</strong> database successfully!Array
(
    [id] => 1
    [0] => 1
    [name] => remove NULL string
    [1] => remove NULL string
    [seq] => 1
    [2] => 1
    [from_string] => NULL
    [3] => NULL
    [to_string] => 
    [4] => 
)

You've got a great tool that I've used for everything from equipment inventory management to data cleanup (comparing names that should be the same in different databases and tables to managing out timeline for what my spouse and I have done during the year (e.g. marking specific things for the holiday newsletter, mark when we're expecting visitor, just a general "what we did this year".)

My only complaint is that I keep reviewing for the "new" features and keep mumbling to myself "why wasn't that available when I did project "x"

allan

Thank you for your kind words

It sounds to me like bit.io don't support enough of Postgres to work with Editor. What strikes me as really odd is that it would timeout rather than just throw an error if that is the case though. Although perhaps it is just really slow... Not sure!

I've not had a chance to try bit.io yet, but will do so later on. In the meantime, if you are interested, we have our own CloudTables product which is sort of similar - DataTables + Editor configured by the web interface, running on a Node and Postgres backend. 50k row limit at the moment, although I'm working on removing that.

Regards,
Allan