Mining Farm - Tech support

[Xenland]

This is the Mining Farm tech support thread where restricted and non-restricted users can discuss, and assist in installing Mining Farm.

*Don't go too off topic into pushpool and bitcoind discussions.
 Basically be mature, don't take over the thread with off topic conversations(you can start your own)
Thanks!

Official Mining Farm #2 Thread: http://forum.bitcoin.org/index.php?topic=10617.0
Pushpoold Tech Support Thread: http://forum.bitcoin.org/index.php?topic=10321.0

[Xenland]

*Reserved Post For Now

[phorensic]

How does Mining Farm get shares information from pushpool?  Does it check shares.log or MySQL?  If MySQL, what should my server.json file look like to populate the shares table?

[Xenland]

Mining Farm recieves its shares with mysql . You can enable mysql by setting sharelog to true, then setting up the mysql query to your liking.
Here is a reference from the pushpool tech support thread,

Code:

{
   # network ports
   "listen" : [
      # binary protocol (default), port 8342
      { "port" : 8342 },

      # HTTP JSON-RPC protocol, port 8341
      { "port" : 8347, "protocol" : "http-json" },

      # HTTP JSON-RPC protocol, port 8344,
      #proxy is most likely your external ip address if your running a public pool | Public pools WON'T have the following ip addresses: (10.0.0.1, 127.0.0.1, 192.168.1.100, or 192.168.254.254,etc,etc)
      # requests to us | "proxy" should be set to your ip address that people will connect through
      { "port" : 8344, "protocol" : "http-json",
        "proxy" : "127.0.0.1" },

      # binary protocol, localhost-only port 8338
      # host is most likely your localhost address
      { "host" : "127.0.0.1", "port" : 8338, "protocol" : "binary" }
   ],

   # database settings
   "database" : {
      "engine" : "mysql",

      "host" : "localhost",

      "port" : 3306,

      #database name
      "name" : "pushpool",
      #database username
      "username" : "mysqlusername",
      #database password
      "password" : "mysqlpass",
      #enable sharelog | to insert share data or sometimes known as "work"
      "sharelog" : true,
      "stmt.pwdb":"SELECT password FROM pool_worker WHERE username = ?",
      
      "stmt.sharelog":"INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (?, ?, ?, ?, ?, ?)"

   },

   #uncoment this when you want to use memcached (Recommended for servers over 1.5gb of ram)
   # cache settings
   #"memcached" : {
   #   "servers" : [
   #      { "host" : "127.0.0.1", "port" : 11211 }
   #   ]
   #},

   "pid" : "/tmp/pushpoold.pid",

   # overrides local hostname detection
   "forcehost" : "localhost.localdomain",

   "log.requests" : "/tmp/request.log",
   "log.shares" : "/tmp/shares.log",

   # the server assumes longpolling (w/ SIGUSR1 called for each blk)
   "longpoll.disable" : false,

   # length of time to cache username/password credentials, in seconds
   "auth.cred_cache.expire" : 75,

   # RPC settings
   #Bitcoind Protocal settings
   #Host were bitcoind can be found on the network
   "rpc.url" : "http://127.0.0.1:8332/",
   #Username & password to connect to bitcoind
   "rpc.user" : "BitcoindUsername",
   "rpc.pass" : "BitcoindPassword",

   # rewrite returned 'target' to difficulty-1?
   "rpc.target.rewrite" : true
}

How does Mining Farm get shares information from pushpool?  Does it check shares.log or MySQL?  If MySQL, what should my server.json file look like to populate the shares table?

[phorensic]

Like some other people over in that thread I am having trouble getting the shares table to update.  In my server.json I have the same line

"stmt.sharelog":"INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (?, ?, ?, ?, ?, ?)"

  I have a couple workers connected to pushpool and everything is humming along.  Shares.log is a hive of activity, yet pushpool is not INSERTing into MySQL.

mysql> SELECT * FROM shares;
Empty set (0.00 sec)

[Xenland]

Intresting. What results do you get in the pushpool debug window?

Like some other people over in that thread I am having trouble getting the shares table to update.  In my server.json I have the same line

"stmt.sharelog":"INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (?, ?, ?, ?, ?, ?)"

  I have a couple workers connected to pushpool and everything is humming along.  Shares.log is a hive of activity, yet pushpool is not INSERTing into MySQL.

mysql> SELECT * FROM shares;
Empty set (0.00 sec)

[phorensic]

Code:

@server1:~/pushpool/sbin$ ./pushpoold -D=2 -E -F
[2011-06-18 21:35:28.659398] Listening on host :: port 8336
[2011-06-18 21:35:28.659476] Listening on host :: port 8337
[2011-06-18 21:35:28.659504] Listening on host 127.0.0.1 port 8338
[2011-06-18 21:35:28.661351] initialized
[2011-06-18 22:25:12.369716] PROOF-OF-WORK found
[2011-06-18 23:20:48.742067] PROOF-OF-WORK found

Edit:  Just realized I had the debug flag wrong.

Code:

@server1:~/pushpool/sbin$ ./pushpoold --debug=2 -E -F
[2011-06-19 04:50:8.288509] Debug output enabled
[2011-06-19 04:50:8.288715] Forcing local hostname to localhost.localdomain
[2011-06-19 04:50:8.324411] Listening on host :: port 8336
[2011-06-19 04:50:8.324453] Listening on host :: port 8337
[2011-06-19 04:50:8.324483] Listening on host 127.0.0.1 port 8338
[2011-06-19 04:50:8.326300] initialized
JSON protocol request:
{"method": "getwork", "params": [], "id":1}

* About to connect() to 127.0.0.1 port 8332 (#0)
*   Trying 127.0.0.1... * TCP_NODELAY set
* connected
* Connected to 127.0.0.1 (127.0.0.1) port 8332 (#0)
* Server auth using Basic with user 'user'
> POST / HTTP/1.1
Authorization: Basic Yml0Y29pbm1vbmtleTpTaG93bWV0aGVtb25leSE=
Host: 127.0.0.1:8332
Accept: */*
Accept-Encoding: deflate, gzip
Content-type: application/json
Content-Length: 45

< HTTP/1.1 200 OK
< Date: Sun, 19 Jun 2011 04:51:40 +0000
< Connection: close
< Content-Length: 592
< Content-Type: application/json
< Server: bitcoin-json-rpc/0.3.23-beta
<
* Closing connection #0
JSON protocol response:
{
   "error": null,
   "result": {
      "target": "00000000000000000000000000000000000000000000000000c4be0700000000",
      "midstate": "94305f6feccc90e1a1bbfe3720e9a45d6c564aa9eeeca037db86a25d18c6458c",
      "data": "00000001f9da09d842fea421a45bf0ff87c4e35c079f1499c73ef10e0018d59e0000000070df258c459e2db5499ec65d67e38e561da6aaf20ed5b91e96b3756c643cc7244dfd80611c07bec400000000000000800000000000000000000000000000000000000000000000000000000000000000000000000000000080020000",
      "hash1": "00000000000000000000000000000000000000000000000000000000000000000000008000000000000000000000000000000000000000000000000000010000"
   },
   "id": 1
}
JSON protocol request:
{"method": "getwork", "params": [], "id":2}

* About to connect() to 127.0.0.1 port 8332 (#0)
*   Trying 127.0.0.1... * TCP_NODELAY set
* connected
* Connected to 127.0.0.1 (127.0.0.1) port 8332 (#0)
* Server auth using Basic with user 'user'
> POST / HTTP/1.1
Authorization: Basic Yml0Y29pbm1vbmtleTpTaG93bWV0aGVtb25leSE=
Host: 127.0.0.1:8332
Accept: */*
Accept-Encoding: deflate, gzip
Content-type: application/json
Content-Length: 45

< HTTP/1.1 200 OK
< Date: Sun, 19 Jun 2011 04:52:36 +0000
< Connection: close
< Content-Length: 592
< Content-Type: application/json
< Server: bitcoin-json-rpc/0.3.23-beta
<
* Closing connection #0
JSON protocol response:
{
   "error": null,
   "result": {
      "target": "00000000000000000000000000000000000000000000000000c4be0700000000",
      "midstate": "c6f80f7a87dc954a039ccf7d0725950d816fdf838639b3789d2155e066006f6b",
      "data": "00000001f9da09d842fea421a45bf0ff87c4e35c079f1499c73ef10e0018d59e0000000064d102f328ad2903ef39d57bfeab81db98face7fe3d51f60beb7027a79ecb8f44dfd80991c07bec400000000000000800000000000000000000000000000000000000000000000000000000000000000000000000000000080020000",
      "hash1": "00000000000000000000000000000000000000000000000000000000000000000000008000000000000000000000000000000000000000000000000000010000"
   },
   "id": 2
}

[Xenland]

What results do you get when apply that query into the mysql directly with simulated data?

Try the follow query(tested) for verification

INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (" ", " ", "Y", "", " ", " ")

Like some other people over in that thread I am having trouble getting the shares table to update.  In my server.json I have the same line

"stmt.sharelog":"INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (?, ?, ?, ?, ?, ?)"

  I have a couple workers connected to pushpool and everything is humming along.  Shares.log is a hive of activity, yet pushpool is not INSERTing into MySQL.

mysql> SELECT * FROM shares;
Empty set (0.00 sec)

[phorensic]

Code:

mysql> INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (" ", " ", "Y", " ", " ", " ");
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> SELECT * FROM shares;
+----+------+----------+----------+------------+-----------------+--------+----------+
| id | time | rem_host | username | our_result | upstream_result | reason | solution |
+----+------+----------+----------+------------+-----------------+--------+----------+
|  1 |    0 |          |          | Y          |                 |        |          |
+----+------+----------+----------+------------+-----------------+--------+----------+
1 row in set (0.00 sec)

[Xenland]

Code:

mysql> INSERT INTO shares (rem_host, username, our_result, upstream_result, reason, solution) VALUES (" ", " ", "Y", " ", " ", " ");
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> SELECT * FROM shares;
+----+------+----------+----------+------------+-----------------+--------+----------+
| id | time | rem_host | username | our_result | upstream_result | reason | solution |
+----+------+----------+----------+------------+-----------------+--------+----------+
|  1 |    0 |          |          | Y          |                 |        |          |
+----+------+----------+----------+------------+-----------------+--------+----------+
1 row in set (0.00 sec)

I see that you have received 2 warnings were they related to this query, and if so what did it the warnings say?

[phorensic]

Thank you for the support Xen

Code:

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1364 | Field 'time' doesn't have a default value            |
| Warning | 1265 | Data truncated for column 'upstream_result' at row 1 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

When I set up my tables I used your example from the Pushpool Tech Support thread

CREATE TABLE IF NOT EXISTS `pool_worker` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `associatedUserId` int(255) NOT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint(30) NOT NULL AUTO_INCREMENT,
  `time` int(255) NOT NULL,
  `rem_host` varchar(255) NOT NULL,
  `username` varchar(120) NOT NULL,
  `our_result` enum('Y','N') NOT NULL,
  `upstream_result` enum('Y','N') DEFAULT NULL,
  `reason` varchar(50) DEFAULT NULL,
  `solution` varchar(257) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Do I have too many columns for pushpool to handle?  I notice there are only 6 values in server.json and 8 columns in the sql table.  Sorry, still somewhat of a MySQL noob, this stuff is always set up for me beforehand, I usually only support post-install 

[Xenland]

Yeah no problem!

Thank you for the support Xen

Code:

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1364 | Field 'time' doesn't have a default value            |
| Warning | 1265 | Data truncated for column 'upstream_result' at row 1 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

When I set up my tables I used your example from the Pushpool Tech Support thread

CREATE TABLE IF NOT EXISTS `pool_worker` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `associatedUserId` int(255) NOT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint(30) NOT NULL AUTO_INCREMENT,
  `time` int(255) NOT NULL,
  `rem_host` varchar(255) NOT NULL,
  `username` varchar(120) NOT NULL,
  `our_result` enum('Y','N') NOT NULL,
  `upstream_result` enum('Y','N') DEFAULT NULL,
  `reason` varchar(50) DEFAULT NULL,
  `solution` varchar(257) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Do I have too many columns for pushpool to handle?  I notice there are only 6 values in server.json and 8 columns in the sql table.  Sorry, still somewhat of a MySQL noob, this stuff is always set up for me beforehand, I usually only support post-install 

[phorensic]

One user "genewitch" said they compiled pushpool from Git repository and it started populating their MySQL shares table.  Just did that, no luck.

[fascistmuffin]

I had a nice post describing what might be going on, then the power went out. So now you get the simple version:

`time` int(255) NOT NULL,

Shouldn't be NOT NULL. "Field 'time' doesn't have a default value," means you didn't specify a value for `time` in the insert statement. Since there's no value, and it can't be null (the default value when a column isn't specified in the insert statement) the system gives you this error. Change that column to something like `time` int.

Really goofy create table statement tbh, specifying lengths for integers, and using bigint for key columns. Make my former DBA self cringe

[phorensic]

Changed `time` to your suggestion.  I can INSERT into shares manually with 0 warnings now.  However, pushpool still cannot INSERT.  Restarted pushpool and used a new worker.

[fascistmuffin]

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `time` int,
  `rem_host` varchar(255),
  `username` varchar(120),
  `our_result` enum('Y','N'),
  `upstream_result` enum('Y','N'),
  `reason` varchar(50),
  `solution` varchar(257),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Try that. I saw above that you said the server.json had 6 values, and the table has 8, so I'm still thinking it's a NOT NULL issue. I changed everything to be not NOT NULL (the one time a double negative is correct! Your move grammar!). I also changed the size specifications for the integers because those just bug me .

[phorensic]

I should mention, I'm running bitcoind on testnet.  Does pushpool somehow block inserting into the mysql database shares that have been generated from testnet?  Also, just noticed your original topic said don't go into pushpool or bitcoind tech support, sorry!

[Xenland]

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `time` int,
  `rem_host` varchar(255),
  `username` varchar(120),
  `our_result` enum('Y','N'),
  `upstream_result` enum('Y','N'),
  `reason` varchar(50),
  `solution` varchar(257),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Try that. I saw above that you said the server.json had 6 values, and the table has 8, so I'm still thinking it's a NOT NULL issue. I changed everything to be not NOT NULL (the one time a double negative is correct! Your move grammar!). I also changed the size specifications for the integers because those just bug me .

Thanks for this insight, I was unware that int could be created with out a limit, I shall use this in the next Mining Farm update v4.0.4

[Xenland]

I should mention, I'm running bitcoind on testnet.  Does pushpool somehow block inserting into the mysql database shares that have been generated from testnet?  Also, just noticed your original topic said don't go into pushpool or bitcoind tech support, sorry!

Its been a couple of days but I'm pretty sure testnet was working for me when I tried it, I might be wrong tho

[fascistmuffin]

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `time` int,
  `rem_host` varchar(255),
  `username` varchar(120),
  `our_result` enum('Y','N'),
  `upstream_result` enum('Y','N'),
  `reason` varchar(50),
  `solution` varchar(257),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Try that. I saw above that you said the server.json had 6 values, and the table has 8, so I'm still thinking it's a NOT NULL issue. I changed everything to be not NOT NULL (the one time a double negative is correct! Your move grammar!). I also changed the size specifications for the integers because those just bug me .

Thanks for this insight, I was unware that int could be created with out a limit, I shall use this in the next Mining Farm update v4.0.4

You might also want to rethink your Enums. You could change them to Char(1) fields, which is 1 byte of space, and you have much more options in case you need to add more values in the future. You wont need to worry about database updates if another value is needed. Also, you could change the bigint to INT UNSIGNED to save on space. Unless you intend on values exceeding 4,294,967,295 , you will be safe using int unsigned, saving you 4 bytes of room for each record.

All these changes should not cause any data loss during updates. You might need to make a conversion/update script for changing enums though. Something along the lines of a temporary table with the new structure and a SELECT....., CASE When our_result = 'Y' then 'Y' else 'N', .... FROM .......), then dropping the old table and renaming the temp one.