PHP telnet parsing data output for mysql table

user_6837605 0
Viewed 62

Hi guys i am developing a small project and learning php coding trough it.. but i am stuck on a new issue below.. i am using PHPtelnet to send cmds and get data replied back from the device..

so i have sent a cmd to check the total number of devices conected on a specific port from the device i am testing with the following example code below. The code sends the cmd, and parses the reply in order to update a mysql table with the specific items described on the code.

The issu is on specific hardware with up to 10 devices i can parse every detail of each device and update to the mysql properly, but on ports with more then 10 devices such as the example posted below.. over 30 devices .. it spits out some error not sure if its buffer size or what.. but using the same cmd on telenet conected to the device i get the correct output..

below the code

   /*Displays all the devices on the specific PON*/
   /*PON1*/
   $show_pon[0] = "show ont info 1 all";
   $telnet->DoCommand($show_pon, $data_response);
  // Data is a raw string
  $sections = preg_split('/-{50,}\s*\r?\n/s', $data_response);
  $rows = preg_split('/\r?\n/', trim($sections[2]));
  $query_chunks = [];
  $query_data = [];

  foreach( $rows as $row )
  {
  $query_chunks[] = "(?,?,?,?,?,?,?,?,?)";
  $query_data = array_merge($query_data, preg_split("/\s+/", trim($row)));

  }

  $query = "INSERT INTO ont_onu (frame_slot, pon, ont_id, mac_address, control_flag, run_state, config_state, match_state, description) VALUES "  . implode( ', ', $query_chunks ) ;

  $pdo_dbh = new PDO('mysql:host=localhost;dbname=MYDBNAME', 'MYDBUSER', 'MYDBPASSWD');
  $sth = $pdo_dbh->prepare( $query );
  $sth->execute( $query_data );
   
  die(print_r($query_data, true));

And below i have the following output print out on the screen

Array 
 ( 
 [0] => 0/0 [1] => 1 [2] => 1 [3] => 80:14:A8:A6:B8:E2 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-TA. 
 [9] => 0/0 [10] => 1 [11] => 2 [12] => 80:14:A8:AA:45:80 [13] => active [14] => online [15] => success [16] => match [17] => ONU-EXT-NA. 
 [18] => 0/0 [19] => 1 [20] => 3 [21] => 80:14:A8:98:12:F0 [22] => active [23] => online [24] => success [25] => match [26] => EURIZAM-MO. 
 [27] => 0/0 [28] => 1 [29] => 4 [30] => E0:67:B3:A6:56:73 [31] => active [32] => online [33] => success [34] => match [35] => DOUGLAS-CA. 
 [36] => 0/0 [37] => 1 [38] => 5 [39] => C4:70:0B:26:B1:58 [40] => active [41] => online [42] => success [43] => match [44] => AFONSO-LEM. 
 [45] => 0/0 [46] => 1 [47] => 6 [48] => 80:14:A8:32:0B:60 [49] => active [50] => online [51] => failed [52] => match [53] => ALINE-FERR. 
 [54] => 0/0 [55] => 1 [56] => 7 [57] => 80:14:A8:A6:BB:13 [58] => active [59] => online [60] => success [61] => match [62] => ONU-EXT-R.. 
 [63] => 0/0 [64] => 1 [65] => 8 [66] => E0:E8:E6:4E:74:C0 [67] => active [68] => online [69] => success [70] => match [71] => MARCIA-CAR. 
 [72] => 0/0 [73] => 1 [74] => 9 [75] => 80:14:A8:A6:54:1F [76] => active [77] => online [78] => success [79] => match [80] => ONU-EXT-JE. 
 [81] => 0/0 [82] => 1 [83] => 10 [84] => E0:67:B3:7E:03:CA [85] => active [86] => online [87] => success [88] => match [89] => MARIA-AGUI 
 [90] => --More [91] => ( [92] => Press [93] => 'Q' [94] => to [95] => quit [96] => )-- [97] =>  [98] => . 
  [99] => 0/0 [100] => 1 [101] => 11 [102] => C4:70:0B:26:B2:E0 [103] => active [104] => online [105] => success [106] => match [107] => CRISTINO-A. 
 [108] => 0/0 [109] => 1 [110] => 12 [111] => 44:55:B1:07:7E:11 [112] => active [113] => online [114] => success [115] => match [116] => IRACEMA-SI. 
  [117] => 0/0 [118] => 1 [119] => 13 [120] => 80:14:A8:AA:6D:F0 [121] => active [122] => online [123] => success [124] => match [125] => ONU-EXT 
  [126] => 0/0 [127] => 1 [128] => 14 [129] => E0:E8:E6:4E:55:10 [130] => active [131] => online [132] => success [133] => match [134] => LENILDE-OL. 
  [135] => 0/0 [136] => 1 [137] => 15 [138] => E0:67:B3:B2:FC:8F [139] => active [140] => online [141] => success [142] => match [143] => VINICIUS-N. 
  [144] => 0/0 [145] => 1 [146] => 16 [147] => 80:14:A8:A6:BB:17 [148] => active [149] => online [150] => success [151] => match [152] => ONU-EXT-RJ. 
  [153] => 0/0 [154] => 1 [155] => 17 [156] => 80:14:A8:A6:C0:B8 [157] => active [158] => online [159] => success [160] => match [161] => ONU-EXT-MA. 
  [162] => 0/0 [163] => 1 [164] => 18 [165] => 80:14:A8:A6:BB:05 [166] => active [167] => online [168] => success [169] => match [170] => ONU-EXT-DI. 
  [171] => 0/0 [172] => 1 [173] => 19 [174] => 80:14:A8:A6:BB:18 [175] => active [176] => online [177] => success [178] => match [179] => ONU-FRANCI. 
  [180] => 0/0 [181] => 1 [182] => 20 [183] => E0:67:B3:88:5C:4A [184] => active [185] => online [186] => success [187] => match [188] => EDILEUZA-A. 
  [189] => 0/0 [190] => 1 [191] => 21 [192] => E0:67:B3:7E:0A:B1 [193] => active [194] => online [195] => success [196] => match [197] => ANDRESA-RO. 
  [198] => 0/0 [199] => 1 [200] => 22 [201] => C4:70:0B:25:F1:60 [202] => active [203] => online [204] => success [205] => match [206] => MARCUS-SOU. 
 [207] => --More [208] => ( [209] => Press [210] => 'Q' [211] => to [212] => quit [213] => )--

)

but the correct output on the telnet cmd is

   OLT(config-interface-epon-0/0)# show ont info 1 all
 -----------------------------------------------------------------------------
   F/S  P  ONT MAC               Control   Run      Config   Match     Desc
           ID                    flag      state    state    state
  ----------------------------------------------------------------------------
   0/0  1  1   80:14:A8:A6:B8:E2 active    online   success  match     ONU-EXT-TA.
   0/0  1  2   80:14:A8:AA:45:80 active    online   success  match     ONU-EXT-NA.
   0/0  1  3   80:14:A8:98:12:F0 active    online   success  match     EURIZAM-MO.
   0/0  1  4   E0:67:B3:A6:56:73 active    online   success  match     DOUGLAS-CA.
   0/0  1  5   C4:70:0B:26:B1:58 active    online   success  match     AFONSO-LEM.
   0/0  1  6   80:14:A8:32:0B:60 active    online   failed   match     ALINE-FERR.
   0/0  1  7   80:14:A8:A6:BB:13 active    online   success  match     ONU-EXT-R..
   0/0  1  8   E0:E8:E6:4E:74:C0 active    online   success  match     MARCIA-CAR.
   0/0  1  9   80:14:A8:A6:54:1F active    online   success  match     ONU-EXT-JE.
   0/0  1  10  E0:67:B3:7E:03:CA active    online   success  match     MARIA-AGUI.
   0/0  1  11  C4:70:0B:26:B2:E0 active    online   success  match     CRISTINO-A.
   0/0  1  12  44:55:B1:07:7E:11 active    online   success  match     IRACEMA-SI.
   0/0  1  13  80:14:A8:AA:6D:F0 active    online   success  match     ONU-EXT
   0/0  1  14  E0:E8:E6:4E:55:10 active    online   success  match     LENILDE-OL.
   0/0  1  15  E0:67:B3:B2:FC:8F active    online   success  match     VINICIUS-N.
   0/0  1  16  80:14:A8:A6:BB:17 active    online   success  match     ONU-EXT-RJ.
   0/0  1  17  80:14:A8:A6:C0:B8 active    online   success  match     ONU-EXT-MA.
   0/0  1  18  80:14:A8:A6:BB:05 active    online   success  match     ONU-EXT-DI.
   0/0  1  19  80:14:A8:A6:BB:18 active    online   success  match     ONU-FRANCI.
   0/0  1  20  E0:67:B3:88:5C:4A active    online   success  match     EDILEUZA-A.
   0/0  1  21  E0:67:B3:7E:0A:B1 active    online   success  match     ANDRESA-RO.
   0/0  1  22  C4:70:0B:25:F1:60 active    online   success  match     MARCUS-SOU.
   0/0  1  23  C4:70:0B:26:C2:78 active    online   success  match     RICARDO-PA.
   0/0  1  24  E0:67:B3:A6:56:65 active    online   success  match     PHATRICIA-.
   0/0  1  25  00:6D:61:59:ED:B0 active    online   success  match     CARINA-BRI.
   0/0  1  26  80:14:A8:A6:BB:11 active    online   success  match     ONU-JONATA.
   0/0  1  27  C4:70:0B:26:07:C8 active    online   success  match     CHRISTIANO.
   0/0  1  28  80:14:A8:7F:8D:48 active    online   success  match     CARLA-SOUZ.
   0/0  1  29  E0:67:B3:88:30:13 active    online   success  match     VALERIA-FA.
  -----------------------------------------------------------------------------
   Total: 29, online 29

As we can see the output is not complete as it seems to crash before finishing the output.. or maybe there is a output limit read?

mysql php
4 Answers

The following output prints out on the php log before processing the rest of the cmd

 --More ( Press 'Q' to quit )--  .

and aparently it crashes because its not forwarding the correct data to the mysql pdo statement as its expected..

could this be a buffer size issue on the cmd reply that php is not handling all the full cmd reply?

user_68376050

Can you please output the exact error you are getting.

Standard MYSQL/PHP can easily do a 100k+ inserts at once without much issue.

$query_data = array_merge($query_data, preg_split("/\s+/", trim($row))); Has a lot going on and does not logically enforce the requirements for $query_chunks.

$show_pon[0] = "show ont info 1 all";
   $telnet->DoCommand($show_pon, $data_response);
  // Data is a raw string
  $sections = preg_split('/-{50,}\s*\r?\n/s', $data_response);
  $rows = preg_split('/\r?\n/', trim($sections[2]));
  $query_chunks = [];
  $query_data = [];

  foreach( $rows as $row )
  {
    $data =preg_split("/\s+/", trim($row));
    if (count($data) == 9){
        $query_data[] = $data;
        $query_chunks[] = "(?,?,?,?,?,?,?,?,?)";
    }
  }

  $query = "INSERT INTO ont_onu (frame_slot, pon, ont_id, mac_address, control_flag, run_state, config_state, match_state, description) VALUES "  . implode( ', ', $query_chunks ) ;

  $pdo_dbh = new PDO('mysql:host=localhost;dbname=MYDBNAME', 'MYDBUSER', 'MYDBPASSWD');
  $sth = $pdo_dbh->prepare( $query );
  $sth->execute( $query_data );
   
  die(print_r($query_data, true));
user_47194450

Full attached log below, something is interrupting the read out.. from array 20 forward.

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /data.php on line 227

 Array 
 ( 
 [0] => Array ( [0] => 0/0 [1] => 1 [2] => 1 [3] => 80:14:A8:A6:B8:E2 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-TA. ) 
 [1] => Array ( [0] => 0/0 [1] => 1 [2] => 2 [3] => 80:14:A8:AA:45:80 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-NA. ) 
 [2] => Array ( [0] => 0/0 [1] => 1 [2] => 3 [3] => 80:14:A8:98:12:F0 [4] => active [5] => online [6] => success [7] => match [8] => EURIZAM-MO. ) 
 [3] => Array ( [0] => 0/0 [1] => 1 [2] => 4 [3] => E0:67:B3:A6:56:73 [4] => active [5] => online [6] => success [7] => match [8] => DOUGLAS-CA. ) 
 [4] => Array ( [0] => 0/0 [1] => 1 [2] => 5 [3] => C4:70:0B:26:B1:58 [4] => active [5] => online [6] => success [7] => match [8] => AFONSO-LEM. ) 
 [5] => Array ( [0] => 0/0 [1] => 1 [2] => 6 [3] => 80:14:A8:32:0B:60 [4] => active [5] => online [6] => failed [7] => match [8] => ALINE-FERR. ) 
 [6] => Array ( [0] => 0/0 [1] => 1 [2] => 7 [3] => 80:14:A8:A6:BB:13 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-R.. ) 
 [7] => Array ( [0] => 0/0 [1] => 1 [2] => 8 [3] => E0:E8:E6:4E:74:C0 [4] => active [5] => online [6] => success [7] => match [8] => MARCIA-CAR. ) 
 [8] => Array ( [0] => 0/0 [1] => 1 [2] => 9 [3] => 80:14:A8:A6:54:1F [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-JE. ) 
 [9] => Array ( [0] => 0/0 [1] => 1 [2] => 11 [3] => C4:70:0B:26:B2:E0 [4] => active [5] => online [6] => success [7] => match [8] => CRISTINO-A. ) 
 [10] => Array ( [0] => 0/0 [1] => 1 [2] => 12 [3] => 44:55:B1:07:7E:11 [4] => active [5] => online [6] => success [7] => match [8] => IRACEMA-SI. ) 
 [11] => Array ( [0] => 0/0 [1] => 1 [2] => 13 [3] => 80:14:A8:AA:6D:F0 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT ) 
 [12] => Array ( [0] => 0/0 [1] => 1 [2] => 14 [3] => E0:E8:E6:4E:55:10 [4] => active [5] => online [6] => success [7] => match [8] => LENILDE-OL. ) 
 [13] => Array ( [0] => 0/0 [1] => 1 [2] => 15 [3] => E0:67:B3:B2:FC:8F [4] => active [5] => online [6] => success [7] => match [8] => VINICIUS-N. ) 
 [14] => Array ( [0] => 0/0 [1] => 1 [2] => 16 [3] => 80:14:A8:A6:BB:17 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-RJ. ) 
 [15] => Array ( [0] => 0/0 [1] => 1 [2] => 17 [3] => 80:14:A8:A6:C0:B8 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-MA. ) 
 [16] => Array ( [0] => 0/0 [1] => 1 [2] => 18 [3] => 80:14:A8:A6:BB:05 [4] => active [5] => online [6] => success [7] => match [8] => ONU-EXT-DI. ) 
 [17] => Array ( [0] => 0/0 [1] => 1 [2] => 19 [3] => 80:14:A8:A6:BB:18 [4] => active [5] => online [6] => success [7] => match [8] => ONU-FRANCI. ) 
 [18] => Array ( [0] => 0/0 [1] => 1 [2] => 20 [3] => E0:67:B3:88:5C:4A [4] => active [5] => online [6] => success [7] => match [8] => EDILEUZA-A. ) 
 [19] => Array ( [0] => 0/0 [1] => 1 [2] => 21 [3] => E0:67:B3:7E:0A:B1 [4] => active [5] => online [6] => success [7] => match [8] => ANDRESA-RO. ) 
 [20] => Array ( [0] => 0/0 [1] => 1 [2] => 22 [3] => C4:70:0B:25:F1:60 [4] => active [5] => online [6] => success [7] => match [8] => MARCUS-SOU. )

)

and below is the full cmd telnet reply, i have connected remotely via telnet to the same device, and i have executed the same command, and it prints out all the devices connected to the specific PON port.. whilst the same command via PHP telnet is nout outputing the full cmd reply has seen on previous log..

 OLT(config-interface-epon-0/0)# show ont info 1 all
     -----------------------------------------------------------------------------
    F/S  P  ONT MAC               Control   Run      Config   Match     Desc
            ID                    flag      state    state    state
   ----------------------------------------------------------------------------
    0/0  1  1   80:14:A8:A6:B8:E2 active    online   success  match     ONU-EXT-TA.
    0/0  1  2   80:14:A8:AA:45:80 active    online   success  match     ONU-EXT-NA.
    0/0  1  3   80:14:A8:98:12:F0 active    online   success  match     EURIZAM-MO.
    0/0  1  4   E0:67:B3:A6:56:73 active    online   success  match     DOUGLAS-CA.
    0/0  1  5   C4:70:0B:26:B1:58 active    online   success  match     AFONSO-LEM.
    0/0  1  6   80:14:A8:32:0B:60 active    online   failed   match     ALINE-FERR.
    0/0  1  7   80:14:A8:A6:BB:13 active    online   success  match     ONU-EXT-R..
    0/0  1  8   E0:E8:E6:4E:74:C0 active    online   success  match     MARCIA-CAR.
    0/0  1  9   80:14:A8:A6:54:1F active    online   success  match     ONU-EXT-JE. 
    0/0  1  10  E0:67:B3:7E:03:CA active    online   success  match     MARIA-AGUI.
    0/0  1  11  C4:70:0B:26:B2:E0 active    online   success  match     CRISTINO-A.
    0/0  1  12  44:55:B1:07:7E:11 active    online   success  match     IRACEMA-SI.
    0/0  1  13  80:14:A8:AA:6D:F0 active    online   success  match     ONU-EXT
    0/0  1  14  E0:E8:E6:4E:55:10 active    online   success  match     LENILDE-OL.
    0/0  1  15  E0:67:B3:B2:FC:8F active    online   success  match     VINICIUS-N.
    0/0  1  16  80:14:A8:A6:BB:17 active    online   success  match     ONU-EXT-RJ.
    0/0  1  17  80:14:A8:A6:C0:B8 active    online   success  match     ONU-EXT-MA.
    0/0  1  18  80:14:A8:A6:BB:05 active    online   success  match     ONU-EXT-DI.
    0/0  1  19  80:14:A8:A6:BB:18 active    online   success  match     ONU-FRANCI.
    0/0  1  20  E0:67:B3:88:5C:4A active    online   success  match     EDILEUZA-A.
    0/0  1  21  E0:67:B3:7E:0A:B1 active    online   success  match     ANDRESA-RO.
    0/0  1  22  C4:70:0B:25:F1:60 active    online   success  match     MARCUS-SOU.
    0/0  1  23  C4:70:0B:26:C2:78 active    online   success  match     RICARDO-PA. 
    0/0  1  24  E0:67:B3:A6:56:65 active    online   success  match     PHATRICIA-.
    0/0  1  25  00:6D:61:59:ED:B0 active    online   success  match     CARINA-BRI.
    0/0  1  26  80:14:A8:A6:BB:11 active    online   success  match     ONU-JONATA.
    0/0  1  27  C4:70:0B:26:07:C8 active    online   success  match     CHRISTIANO.
    0/0  1  28  80:14:A8:7F:8D:48 active    online   success  match     CARLA-SOUZ.
    0/0  1  29  E0:67:B3:88:30:13 active    online   success  match     VALERIA-FA.
   -----------------------------------------------------------------------------
    Total: 29, online 29
user_68376050

And this is the full output of the $data_response sent out from the device

 show ont info 1 all 
 ----------------------------------------------------------------------------- 
 F/S P ONT MAC Control Run Config Match Desc ID flag state state state 
 ---------------------------------------------------------------------------- 
 0/0 1 1 80:14:A8:A6:B8:E2 active online success match ONU-EXT-TA. 
 0/0 1 2 80:14:A8:AA:45:80 active online success match ONU-EXT-NA. 
 0/0 1 3 80:14:A8:98:12:F0 active online success match EURIZAM-MO. 
 0/0 1 4 E0:67:B3:A6:56:73 active online success match DOUGLAS-CA. 
 0/0 1 5 C4:70:0B:26:B1:58 active online success match AFONSO-LEM.  
 0/0 1 6 80:14:A8:32:0B:60 active online failed match ALINE-FERR. 
 0/0 1 7 80:14:A8:A6:BB:13 active online success match ONU-EXT-R.. 
 0/0 1 8 E0:E8:E6:4E:74:C0 active online success match MARCIA-CAR. 
 0/0 1 9 80:14:A8:A6:54:1F active online success match ONU-EXT-JE. 
 0/0 1 10 E0:67:B3:7E:03:CA active online success match MARIA-AGUI 
 --More ( Press 'Q' to quit )--  . 
 0/0 1 11 C4:70:0B:26:B2:E0 active online success match CRISTINO-A. 
 0/0 1 12 44:55:B1:07:7E:11 active online success match IRACEMA-SI. 
 0/0 1 13 80:14:A8:AA:6D:F0 active online success match ONU-EXT 
 0/0 1 14 E0:E8:E6:4E:55:10 active online success match LENILDE-OL. 
 0/0 1 15 E0:67:B3:B2:FC:8F active online success match VINICIUS-N. 
 0/0 1 16 80:14:A8:A6:BB:17 active online success match ONU-EXT-RJ. 
 0/0 1 17 80:14:A8:A6:C0:B8 active online success match ONU-EXT-MA. 
 0/0 1 18 80:14:A8:A6:BB:05 active online success match ONU-EXT-DI. 
 0/0 1 19 80:14:A8:A6:BB:18 active online success match ONU-FRANCI. 
 0/0 1 20 E0:67:B3:88:5C:4A active online success match EDILEUZA-A. 
 0/0 1 21 E0:67:B3:7E:0A:B1 active online success match ANDRESA-RO. 
 0/0 1 22 C4:70:0B:25:F1:60 active online success match MARCUS-SOU. 
  --More ( Press 'Q' to quit )--

This is whats happening on the output log.. and this is why it seems to be crashing and not reading out the full output telnet cmd response on the PHP.. it seems that the cmd reply.. its sitting on a input ENTER button or Q to quit .. and because the php does nothing.. it simply crashes out and does not display the full output cmd reply.

user_68376050
Related