Биллинговая система Nodeny

]# perl nokernel.pl -v
Start. Flag -h for help

loading /usr/local/nodeny/sat.cfg

Connecting to DBI:mysql:database=bill;host=localhost;port=3306;mysql_connect_timeout=5 : 0.020868 sec

SELECT * FROM config ORDER BY time DESC LIMIT 1
Строк: 0. Время выполнения sql: 0.0001 сек

SELECT * FROM config ORDER BY time DESC LIMIT 1
Строк: 0. Время выполнения sql: 0.00007600 сек

SELECT * FROM config ORDER BY time DESC LIMIT 1
Строк: 0. Время выполнения sql: 0.00007600 сек
....
...
Error getting config from DB at nokernel.pl line 39.

INSERT INTO config SET `data`="package cfg; $img_dir = ''; 1;", time=1;

INSERT INTO config SET `data`="package cfg; $img_dir = ''; 1;", time=1;

INSERT INTO admin SET login='admin', name='', post='', privil=',1,3,2,17,20,11,12,13,27,30,31,32,50,51,52,55,100,61,90,69,70,71,72,73,74,75,76,77,78,79,80,81,94,95,', usr_grps='', passwd=AES_ENCRYPT('33', 'BIGint');

ALTER TABLE `users` CHANGE `passwd` `passwd` VARCHAR( 64 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL;

AES_DECRYPT -> AES_ENCRYPT

vim bill.sql
:set fileformat=unix
:w!
:q

dos2unix bill.sql

.main_bg {
    background-color: #ededed;
}
html,body {
    height: 100%;
    min-height: 100%;
    margin: 0;
    padding: 0;
    background-color: #ededed;
    color: #000000;
    font-size: 9pt;
    font-family: Tahoma, sans-serif;
}
.low_resolution {
    font-size: 8pt;
}

table {
    border: none;
    border-collapse: collapse;
}
iframe {
    border: none;
}
a {
    color: #5a80b0;
    text-decoration: none;
}
a:hover {
    color: #800000;
}
a img {
    border: none;
}
input[type=text],
input[type=password],
textarea,
select {
    color: #660000;
    background-color: #fffff0;
    border: 1px solid #b0c0d0;
}
input:disabled {
    color: #707070;
    background-color: #f0f0f0;
    border: 1px solid #e0e0e0;
}
input[type=checkbox],
input[type=radio] {
    background: transparent;
    border: none !important;
}
form {
    margin: 0;
}
acronym {
    cursor: help;
}
ul {
    margin: 0;
}
li {
    margin-bottom: 3pt;
}
hr {
    background-color: #c0d0e0;
    border: 0;
    height: 1px;
    display: block;
}
hr.space {
    background-color: transparent;
    height: 12pt;
    margin: 0;
    padding: 0;
}
.big {
    font-size: 150%;
}
.normal {
    font-size: 130%;
}
.small {
    font-size: 80%;
}
.bold {
    font-weight: bold;
}
.story {
    text-align: justify;
    text-indent: 120%;
}
.h_center {
    text-align: center;
}
.h_left {
    text-align: left;
}
.h_right {
    text-align: right;
}
.v_top {
    vertical-align: top;
}
.align_center {
    margin: 0 auto;
    display: table;
}
.cursor_pointer {
    cursor: pointer;
}
.hidden {
    display: none;
}
.nowrap {
    white-space: nowrap;
}
.break-word {
    word-wrap: break-word;
}

.data1 {
    color: green;
}
.data2 {
    color: #3e87bf
}
.error {
    color: red;
}
.warn {
    color: #0080ff;
}
.disabled {
    color: #606060;
}

a.disabled {
    color: #606060;
}
a.active {
    font-weight: bold;
}

/* --- а‘аОаКб аНаА аОбаНаОаВаЕ аКаАб€б‚аИаНаОаК --- */

.box-mini {
    margin: 0 auto;
    display: table;
    min-width: 100px;
}
.box {
    background: #ffffff;
}
.box_tr, .box_tl, .box_t {
    min-height: 17px;
}
.box_tr {
    background: url(box_tr.gif) no-repeat top right;
}
.box_tl {
    background: url(box_tl.gif) no-repeat top left;
    margin: 0;
}
.box_t {
    background: url(box_t.gif) repeat-x;
    margin: 0 17px;
}
.box_r {
    background: url(box_r.gif) repeat-y right top;
    margin: 0;
}
.box_l {
    background: url(box_l.gif) repeat-y left top;
    padding: 2px 24px;
    margin: 0;
}

.box_br, .box_bl, .box_b {
    height: 17px;
}
.box_br {
    background: url(box_br.gif) no-repeat bottom right;
}
.box_bl {
    background: url(box_bl.gif) no-repeat bottom left;
    margin: 0 17px 0 0;
}
.box_b {
    background: url(box_b.gif) repeat-x bottom left;
    margin: 0 0 0 17px;
}
.box_title {
    text-align: right;
    height: 34px;
    padding: 0 15px 0 0;
    margin: 0 10px 0 0;
    background: url(box_title_r.png) no-repeat top right;
}
.box_title span {
    text-align: center;
    display: inline-block;
    height: 34px;
    min-width: 140px;
    padding: 13px 4px 0 20px;
    font-size: 12px;
    color: #000000;
    background: url(box_title_l.png) no-repeat top left;
}

.low_resolution .box {
    border: 1px solid #e0e0e0;
    border-radius: 7px;
    margin: 1px;
}
.low_resolution .box_l {
    padding: 2px 8px;
}

.low_resolution .box_t,
.low_resolution .box_tr,
.low_resolution .box_tl,
.low_resolution .box_r,
.low_resolution .box_l,
.low_resolution .box_b,
.low_resolution .box_bl,
.low_resolution .box_br,
.low_resolution .box_title,
.low_resolution .box_title span {
    background: none;
}

.boxpddng {
    padding: 0 20px;
}


.txtpadding {
    padding: 8px;
}

.msg_tbl td {
    padding: 0 15px;
}

.tablebg {
    background-color: #ccd8e0;
}

table.border td {
    border: 1px solid #e0e0e0;
}

.td_tall td {
    padding-top: 8px;
    padding-bottom: 8px;
}
.td_medium td {
    padding-top: 3px;
    padding-bottom: 2px;
}
.td_wide td {
    padding-right: 14px;
    padding-left: 14px;
}
.td_narrow td {
    padding-right: 4px;
    padding-left: 4px;
}
.td_ok td {
    padding: 2px 8px;
}

.low_resolution .td_wide td {
    padding-right: 7px;
    padding-left: 7px;
}

.thead > thead {
    background-color: #ffffff;
    font-weight: bold;
}

table.pretty {
    margin-top: 2pt;
    margin-bottom: 2pt;
}
table.pretty > thead {
    background-color: #ffffff;
    color: #000000;
    font-weight: bold;
    border-top: 1px solid #e0e0e0;
    border-bottom: 1px solid #c0c0c0;
}

table.fade_border  > tbody > tr > td {
    border-right: 1px solid rgba(200,200,200, 0.2);
}

.pretty_border > tbody > tr > td {
    border: solid 1px #d0d0d0 !important;
}
.pretty_now_odd {
    background-color: #e0e0e0 !important;
}
.pretty_now_even {
    background-color: #eeeeee !important;
}

.row1, .row2, .rowoff, .rowoff2 {
    background: url(pixel.gif) repeat-x top left;
    border-bottom: 1px solid #d0d0d0;
}
.row1 {
    background-color: #e0e0e0;
}
.row2 {
    background-color: #eeeeee;
}
.row3 {
    border-bottom: 1px solid #e5e5e5;
}
.row4 {
    background-color: #eaeaea;
    background-image: url(fon.gif);
}
.row5 {
    background-color: #f8f8f8;
}
.rowoff {
    background-color: #ffd7d7;
}
.rowoff2{
    background-color: #ffd0d0;
}
.rowover{
    background-color: #ffd4d4;
}
.row_usr_off {
    background-color: #ffd0d0;
}

.head {
    color: #000000;
    text-shadow: #f0f0f0 0px 1px 0px;
    font-weight: bold;
}

.head2 {
    text-align: center;
    color: #ffffff;
    background-color: #707070;
}
.head2 td {
    border-width: 1px;
    border-style: solid;
    border-color: #ffffff #808080 #ffffff #808080 !important;
}
.head2 a {
    display: block;
    color: #ffffff;
}
.head2 a:hover {
    color: #000000;
}

.small_msg {
    padding: 6pt;
    margin: 6pt;
    min-height: 12pt;
    border: 1px solid #e0e0e0;
    border-radius: 5px;
    background: #f0f0f0;
    background-image: linear-gradient(top, #f0f0f0 0%, #f5f5f5 100%);
    background-image: -o-linear-gradient(top, #f0f0f0 0%, #f5f5f5 100%);
    background-image: -moz-linear-gradient(top, #f0f0f0 0%, #f5f5f5 100%);
    background-image: -webkit-linear-gradient(top, #f0f0f0 0%, #f5f5f5 100%);
    background-image: -ms-linear-gradient(top, #f0f0f0 0%, #f5f5f5 100%);
}

.input_modified input,
.input_modified select,
.input_modified textarea,
input.input_modified,
textarea.input_modified {
    color: #0000ff;
}

.input_short  input[type='text'] {
    width: 70pt;
}

.modified {
    border: 1px solid red;
}

.width100 {
    width: 100%;
}

a.nav, .nav a, a.nav2, .nav2 a, .nav_active, .nav_button, .nav_disabled  {
    position: relative;
    white-space: pre;
    font-size: 9pt;
    font-family: Tahoma, sans-serif;
    text-decoration: none;
    text-shadow: 0 1px 1px #ffffff;
    background-color: #f5f8fd;
    border: solid 1px #90a0b0;
    -webkit-border-radius: 5px;
    -moz-border-radius: 5px;
    -o-border-radius: 5px;
    border-radius: 5px;
    margin-right: 1px;
}

a.nav, .nav a, a.nav2, .nav2 a, .nav_button {
    color: #304050;
    background-image: linear-gradient(bottom, #d0d5e0 0%, #ffffff 100%);
    background-image: -o-linear-gradient(bottom, #d0d5e0 0%, #ffffff 100%);
    background-image: -moz-linear-gradient(bottom, #d0d5e0 0%, #ffffff 100%);
    background-image: -webkit-linear-gradient(bottom, #d0d5e0 0%, #ffffff 100%);
    background-image: -ms-linear-gradient(bottom, #d0d5e0 0%, #ffffff) 100%);
    background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, rgb(208,213,224)), color-stop(1, rgb(255,255,255)));
    -webkit-box-shadow: inset 0px 1px 0px #e0e5ff, 0px 1px 0px #ffffff;
    -moz-box-shadow: inset 0px 1px 0px #e0e5ff, 0px 1px 0px #ffffff;
    box-shadow: inset 0px 1px 0px #e0e5ff, 0px 1px 0px #ffffff;
}

a.nav, .nav a, .nav_active, .nav_disabled {
    display: inline-block;
    padding: 5px 7px;
}

.nav_disabled {
    color: #c5c5c5;
    background-color: #ffffff;
}

.nav_button  {
    display: inline-block;
    padding: 5px 9px;
    outline: none;
    cursor: pointer;
}

a.nav2, .nav2 a {
    display: block;
    padding: 5px;
    margin-bottom: 1px;
}

a.nav:hover, .nav a:hover, .nav_button:hover, .nav_disabled:hover {
    top: 1px;
    color: #000000;
    background-image: linear-gradient(bottom, #d0d5e0 40%, #ffffff 100%);
    background-image: -o-linear-gradient(bottom, #d0d5e0 40%, #ffffff 100%);
    background-image: -moz-linear-gradient(bottom, #d0d5e0 40%, #ffffff 100%);
    background-image: -webkit-linear-gradient(bottom, #d0d5e0 40%, #ffffff 100%);
    background-image: -ms-linear-gradient(bottom, #d0d5e0 40%, #ffffff 100%);
    background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0.4, rgb(208,213,224)), color-stop(1, rgb(255,255,255)));
}

.nav_active {
    color: #700000 !important;
    font-weight: bold;
    background-image: -moz-linear-gradient(bottom, #ffffff 10%, #d0d5e0 100%);
    background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0.1, rgb(255,255,255)), color-stop(1, rgb(208,213,224)));
}

a.nav.downed {
    background-image: none;
}

a.nav.error {
    color: #900000;
}

a.nav_wide {
    padding: 2pt 8pt 3pt 8pt !important;
}

.navmenu a {
    white-space: nowrap;
    color: #5a80b0;
    font-style: normal;
    text-align: left;
    padding: 4pt 3pt 4pt 3pt;
    border: 1px solid transparent;
    display: block;
    line-height: 100%;
    text-decoration: none;
}

.navmenu a:hover {
    color: #800000;
}
.navmenu_active {
    color: #000000 !important;
}
.navmenu a.error {
    color: #b00000;
}

.modal_menu_active {
    color: #a00000 !important;
    font-size: 150% !important;
}



.datepicker { border-collapse: collapse; border: 2px solid #999; position: absolute; z-index: 200}
.datepicker tr.controls th { height: 22px; font-size: 11px; }
.datepicker select { font-size: 11px; }
.datepicker tr.days th { height: 18px; }
.datepicker tfoot td { height: 18px; text-align: center; text-transform: capitalize; }
.datepicker th, .datepicker tfoot td { background: #eee; font: 10px/18px Verdana, Arial, Helvetica, sans-serif; }
.datepicker th span, .datepicker tfoot td span { font-weight: bold; }

.datepicker tbody td { width: 24px; height: 24px; border: 1px solid #ccc; font: 11px/22px Arial, Helvetica, sans-serif; text-align: center; background: #fff; }
.datepicker tbody td.date { cursor: pointer; }
.datepicker tbody td.date.over { background-color: #99ffff; }
.datepicker tbody td.date.chosen { font-weight: bold; background-color: #ccffcc; }

.datepicker .close ,
.datepicker .today {
    color: #5a80b0;
}

input[type='text'].dateinput {
    width: 50pt;
}

.data_input_tbl {
}
.data_input_tbl td{
    padding: 2pt 6pt;
}
.data_input_tbl input[type='text'],
.data_input_tbl select {
    width: 140pt;
}
.data_input_tbl textarea {
    width: 100%;
}






/* --- base --- */

#main_table {
    width: 100%;
    height: 100%;
    min-height: 100%;
    background-color: #ededed;
}
#main_block {
    padding-top: 10px;
}
#left_block {
    padding-top: 10px;
    vertical-align: top;
    width: 30%;
}
#right_block {
    padding-top: 10px;
    vertical-align: top;
    width: 30%;
}

#debug {
    display: none;
    text-align: left;
    padding: 20px 20px 3px 20px;
    background-color: #909090;
}

.debug_href {
    display: inline-block;
    position: absolute;
    top: 2px;
    left: 2px;
    z-index: 20;
    border: 1px solid #404040;
    border-radius: 3px;
    background-color: #e4e9f2;
    padding: 6px 8px;
    text-shadow: #e5e5e5 0px 1px 0px;
}

#window_reload_btn {
    position: absolute;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
    cursor: pointer;
    background: url(riss.png) center no-repeat;
}

.login_box {
    margin: 34px 0 14px 0;
    color: #304050;
    font-size: 125%;
}
.login_box td {
    padding: 0 10px;
}
.login_tbl td {
    padding: 2px !important;
}
.login_tbl input[type=text],
.login_tbl input[type=password] {
    width: 140px;
    border: 1px solid #d0d0d0;
    background-color: #e0e0e0;
    padding: 2px;
}

/* --- аМаОаДаАаЛбŒаНаОаЕ аОаКаНаО --- */

#modal_window {
  padding: 4px;
}

.modal_mask {
  position: absolute;
  z-index: 100;
  top: 0;
  left: 0;
  background-color: #000000;
}
   
.modal_box {
  position: absolute;
  z-index: 101;
  padding: 14px;
  background-color: #ffffff;
  border: 1px solid #a0a0a0;
  border-radius: 7px;
}

.modal_close {
  position: absolute;
  display: block;
  left: -12px;
  top: -12px;
  height: 24px;
  width: 24px;
  border: 1px solid #a0a0a0;
  border-radius: 12px;
  background-color: #ffffff;
}

.modal_close:hover {
  background: #ffffff;
}

/* --- бˆаАаПаКаА --- */

.top_msg {
    text-align: left;
    color: #304050;
    background-color: #e4e9f2;
    border-style: solid;
    border-width: 1px;
    border-color: #ffffff transparent #d0d5dd transparent;
    margin: 0px 3pt;
    padding: 7px;
}
.top_msg  a {
    display: inline-block;
    padding: 4pt 7pt;
    margin: 0;
    text-decoration: none;
    color: #000000;
    cursor: pointer;
}
.top_msg a:hover {
    color: #708090;
}
.top_msg a.downed {
    border-style: solid;
    border-width: 1px;
    border-color: #d0dde8 #f0f0f0 #f0f0f0 #d0dde8;
    background-color: #dee3ec;
}

.adm_top_made_msg  div{
    color: #ffffff;
    margin: 4pt 0 -4pt 0;
    padding: 8pt 0 6pt 0;
    text-align: center;
    background-color: #606060;
}
.adm_top_made_msg  .top_msg_error {
    color: #900000;
    background-color: #ffffff;
    background-image: -moz-linear-gradient(top, #ffffff 0%, #fff0f0 100%);
    background-image: -webkit-linear-gradient(top, #ffffff 0%, #fff0f0 100%);
    border: none;
}

.usr_top_made_msg  div{
    color: #000000;
    background-color: #ffffff;
    border: 3px double #d0d5dd;
    margin: 4pt 20pt;
    min-height: 4px;
    padding: 8pt;
    text-align: center;
}
.usr_top_made_msg .top_msg_error {
    color: #900000;
    border: 3px double #b00000;
}

/* --- tune.pl --- */

.mTune_left_block {
    white-space: nowrap;
}
.tune_tbl {
}
.tune_tbl td{
    padding: 7pt;
}
.tune_tbl textarea {
    width: 400pt;
    margin: 5pt 1pt 3pt 1pt;
}
.tune_tbl_narrow input {
    width: 120pt;
}
.tune_tbl_wide input {
    width: 230pt;
}
.tune_tbl_narrow_td td{
    width: 124pt;
}

/* --- user.pl --- */

.mUser_data_box td{
    padding: 2pt 6pt;
}

.mUser_data_box input[type='text'],
.mUser_data_box select {
    width: 140pt;
}

.mUser_data_box textarea {
    width: 100%;
}

.low_resolution .mUser_data_box input[type='text'],
.low_resolution .mUser_data_box select {
    width: 100pt;
}

#mUser_ip_list input[type='text']
{
    width: 80pt;
}
.mUser_service_list {
    overflow: auto;
    min-width: 180px;
    max-height: 220px;
    margin: 5px;
}

.mUser_service_list a {
    display: block;
    margin: 3px;
}

/* --- users.pl --- */

.mUsers_search_info {
    padding: 2pt;
    background-color: #ffffff;
}

.mUsers_search_info input[type='text'],
.mUsers_search_info select,
.mUsers_search_info textarea {
    width: 100pt;
}

/* ---  --- */

.usr_info_block table{
    width: 100%;
}

.usr_info_block table td{
    padding: 2pt 5pt;
}

.usr_info_block table a{
    padding: 3pt 6pt;
    font-size: 120%;
}

.usr_top_block {
    margin: 0;
    padding: 4px;
    background-color: #c0d0e0;
}
.usr_top_block div {
    padding: 10pt;
    border: 1px solid #ffffff;
    background-color: #e4e9f2;
    text-align: center;
}

.usr_top_block span {
    font-size: 150%;
    color: #000000;
    text-shadow: #ffffff 0px 1px 0px;
}

.mTraf_log_graph {
    width: 200px;
    background-color: #ededed;
    margin-right: -15px;
}
.mTraf_log_graph div {
    height: 14px;
    background-color: #a0a0a0;
}

.sMain_request_info input[type='text'],
.sMain_request_info select {
    width: 200pt;
}

159	stat.pl
1072	calls.pm::Require_web_mod
1072	calls.pm
54	start_admin.pl
	Команда ajAdmin выполняется в ajax-контексте, но http-запрос не ajax - выводим титульную страницу

mysql> source nodeny_plus/from_50.32_to_n_plus.txt

ERROR 1051 (42S02): Unknown table 'login_last'
ERROR 1051 (42S02): Unknown table 'newuser_opt'

ERROR 1051 (42S02): Unknown table 'traf_info'

# mysql -p nodeny < from_50.32_to_n_plus.txt > sql.log
Enter password: 
ERROR 1051 (42S02) at line 35: Unknown table 'login_last'

 cat /tmp/nodeny_1356095209_3585474.log
21.12.2012 17:06:49 [(eval 9)::BEGIN(2) calls.pm(38) Db.pm::line(135) Db.pm::sql(104) Db.pm::connect(86)]
Connecting to DBI:mysql:database=nodeny;host=localhost;port=3306;mysql_connect_timeout=5 : 0.111438 sec
21.12.2012 17:06:49 [(eval 9)::BEGIN(2) calls.pm(38) Db.pm::line(135) Db.pm::sql(125) Db.pm::sql(298)]
Table 'nodeny.config' doesn't exist 

{
  'sql' => 'SELECT *, UNIX_TIMESTAMP() AS t FROM config ORDER BY time DESC LIMIT 1',
  'param' => []
};
 
 SELECT *, UNIX_TIMESTAMP() AS t FROM config ORDER BY time DESC LIMIT 1
Строк: -1. Время выполнения sql: 0.0002 сек
21.12.2012 17:06:49 [stat.pl(102) (eval 9)(2) (eval 9)(2) (eval 9)::BEGIN(2) stat.pl::__ANON__(25) stat.pl(25) stat.pl::Hard_exit(84) stat.pl(85)]
No config in DB at /usr/local/nodeny/web/calls.pm line 40.
Compilation failed in require at (eval 9) line 2.

21.12.2012 17:06:49 [stat.pl(102) (eval 9)(2) (eval 9)(2) (eval 9)::BEGIN(2) stat.pl::__ANON__(25) stat.pl(25) stat.pl::Hard_exit(84) stat.pl(87)]
Elapsed time: 0.291327 sec
root@nodenyplus:/usr/local/etc/apache22/ssl # mysql -unodeny -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use nodeny;
Database changed
mysql> INSERT INTO config SET `data`="package cfg; $img_dir = ''; 1;", time=1;
ERROR 1146 (42S02): Table 'nodeny.config' doesn't exist
mysql> 

# perl install.pl -a=admin -p=pass -v
Start. Flag -h for help

loading /usr/local/nodeny/sat.cfg

Создание суперадминской учетной записи

Connecting to DBI:mysql:database=nodeny;host=localhost;port=3306;mysql_connect_timeout=5 : 0.009794 sec

Unknown column 'login' in 'field list' 

{
  'sql' => 'INSERT INTO admin SET privil=\',1,2,3,\', login=?, passwd=AES_ENCRYPT(?,?)',
  'param' => [
    'admin',
    'pass',
    'secret'
  ]
};
 
 INSERT INTO admin SET privil=',1,2,3,', login='admin', passwd=AES_ENCRYPT('pass','secret')
Строк: -1. Время выполнения sql: 0.0002 сек

[!] Sql error

ERROR 1146 (42S02) at line 3: Table 'nodeny.dopvalues' doesn't exist

svn co svn://forum.nodeny.com.ua/svn/nodeny_plus nodeny
Checked out revision 82.

#perl
my $sth=&sql("SELECT COUNT(*) AS n FROM nets");
($sth->execute) && (my $p=$sth->fetchrow_hashref) && ($p->{n}==0) && &sql_do("INSERT INTO nets SET preset=0,priority=0,net='',port=0,class=1,comment='интернет'");

#!/usr/bin/perl
#привязка тарифов

use DBI;

my $dsn = 'DBI:mysql:bill:localhost';
my $db_user_name = 'root';
my $db_password = 'hardpass';
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

$x=0;
while($x<скока абонов + 1) {
$dbh->do("
insert into bill.users_services (uid,service_id)  select id,paket from test.users where id=$x;
");
$x++;
}
$dbh->disconnect();

select cid,cod,alive,serial, count(cod)  as dubcount
from cards
group by cod
having dubcount > 1

ALTER TABLE `data0` ADD `_adr_telefon` VARCHAR(255) NOT NULL;
ALTER TABLE `data0` ADD INDEX (`_adr_telefon`);
INSERT INTO datasetup SET id=4, title='[10]Телефон', name='_adr_telefon', type='4', param='', template='1', flags='abcq', comment='';
....
INSERT INTO data0 (uid,`_adr_telefon`)
    (SELECT parent_id,field_value FROM dopdata WHERE field_alias='_adr_telefon') 
    ON DUPLICATE KEY UPDATE `_adr_telefon`=field_value;

# Конвертер: номер пакета => id услуги
    my %tarif = (
        10 => 3,
        15 => 4,
    );

#!/usr/bin/perl


use DBI;

my $dsn = 'DBI:mysql:bill:localhost';
my $db_user_name = 'root';
my $db_password = 'hardpass';
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

$x=0;
while($x<Номер id последнего абонента + 1) {
$dbh->do("
insert into bill.users_services (uid,service_id)  select id,paket from test.users where id=$x;
");
$x++;
}
$dbh->disconnect();

perl install.pl -u

23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(36) Db.pm::line(144) Db.pm::sql(113) Db.pm::connect(95)]
Connecting to DBI:mysql:database=nodeny;host=localhost;port=3306;mysql_connect_timeout=5 : 0.084339 sec
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(36) Db.pm::line(144) Db.pm::sql(134) Db.pm::sql(321)]
SELECT *, UNIX_TIMESTAMP() AS t FROM config ORDER BY time DESC LIMIT 1
Строк: 1. Время выполнения sql: 0.0009 сек
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(185)]
GET data: _unikey=vE1ilKH8zknfKjYgyYzlHA
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(220)]
  _unikey  = 'vE1ilKH8zknfKjYgyYzlHA'
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(234)]
Текущие cookies:
  noses  = 'vrpySrtwCT1wyCWF5BS1Kg'
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(254) Db.pm::line(144) Db.pm::sql(134) Db.pm::sql(321)]
SELECT * FROM websessions s LEFT JOIN admin a ON (s.uid=a.id AND s.role='admin') WHERE BINARY ses='vrpySrtwCT1wyCWF5BS1Kg' AND uid>0 LIMIT 1
Строк: 1. Время выполнения sql: 0.0024 сек
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(272)]
Сессия `vrpySrtwCT1wyCWF5BS1Kg` существует, uid: 2, role: admin
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(294) Db.pm::do(158) Db.pm::sql(134) Db.pm::sql(321)]
UPDATE websessions SET expire=UNIX_TIMESTAMP()+'7200' WHERE BINARY ses='vrpySrtwCT1wyCWF5BS1Kg' LIMIT 1
Строк: 1. Время выполнения sql: 0.0014 сек
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(301) Db.pm::line(144) Db.pm::sql(134) Db.pm::sql(321)]
SELECT * FROM webses_data WHERE BINARY unikey='vE1ilKH8zknfKjYgyYzlHA' LIMIT 1
Строк: 1. Время выполнения sql: 0.0020 сек
23.10.2013 10:41:16 [(eval 10)::BEGIN(2) calls.pm(318)]
Данные по unikey `vE1ilKH8zknfKjYgyYzlHA`: HASH(0x2888c4d8)
23.10.2013 10:41:16 [stat.pl(108) (eval 10)(2) (eval 10)(2) (eval 10)::BEGIN(2) stat.pl::__ANON__(29) stat.pl(29) stat.pl::Hard_exit(88) stat.pl(89)]
Can't use string ("HASH(0x2888c4d8)") as a HASH ref while "strict refs" in use at /usr/local/nodeny/web/calls.pm line 320.
Compilation failed in require at (eval 10) line 2.

23.10.2013 10:41:16 [stat.pl(108) (eval 10)(2) (eval 10)(2) (eval 10)::BEGIN(2) stat.pl::__ANON__(29) stat.pl(29) stat.pl::Hard_exit(88) stat.pl(91)]
Elapsed time: 0.448683 sec

perl -e'use Data::Dumper; my $v = { "\x{FFFF_FFFF}" }; utf8::decode($v); print Dumper($v)'

INSERT INTO mac_uid (uid,mac)     
   (SELECT dd.parent_id, REPLACE(dd.field_value,':','') FROM dopdata dd inner join rev_users r on r.rev=dd.revision and dd.template_num=r.template_num WHERE dd.field_alias='_mac')

#!/usr/bin/perl

use DBI;

my $dsn = 'DBI:mysql:nodeny:localhost';
my $db_user_name = 'root';
my $db_password = 'рутовій пас';
my ($uid, $mac, $mac_ok, $x);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

$x=0;
$dbh->do("TRUNCATE TABLE nodeny.mac_uid");

while($x<количество абонов) {

my $sth = $dbh->prepare(qq{
	SELECT parent_id, field_value from test.dopvalues
        WHERE  parent_id=$x AND dopfield_id=4 AND revision=(
                SELECT MAX(revision)
                FROM test.dopvalues
                WHERE parent_id=$x AND dopfield_id=4);
});
$sth->execute();
($uid, $mac) = $sth->fetchrow_array();
$sth->finish();
$mac_ok = join '', split /:/, lc $mac;

$dbh->do("INSERT INTO nodeny.mac_uid (uid, mac) values ('$uid', '$mac_ok')");
print "$x, $mac, $mac_ok\n";
$x++;
}
$dbh->disconnect();

SELECT dd.parent_id, REPLACE(dd.field_value,':','') FROM dopdata dd inner join rev_users r on r.rev=dd.revision and dd.template_num=r.template_num WHERE dd.field_alias='_mac'

SELECT dd.parent_id, REPLACE(dd.field_value,':','') FROM dopvalues dd inner join dopfields df inner join rev_users r on r.rev=dd.revision and df.template_num=r.template_num and df.id=dd.dopfield_id WHERE df.field_alias='_mac'

SELECT * FROM dopvalues LIMIT 10

SELECT * FROM dopdata LIMIT 10

SELECT dd.parent_id, REPLACE(dd.field_value,':','') FROM dopvalues dd inner join dopfields df inner join rev_users r on r.rev=dd.revision and df.template_num=r.template_num and df.id=dd.dopfield_id WHERE df.field_alias='_mac';

select * from rev_users limit 10;

select * from dopfields;

INSERT INTO mac_uid (uid,mac)  SELECT dd.parent_id, REPLACE(dd.field_value,':','') FROM dopvalues dd inner join dopfields df inner join rev_users r on r.rev=dd.revision and df.template_num=r.template_num and df.id=dd.dopfield_id WHERE df.field_alias='_mac';

perl nokernel.pl -v -m=services

# ps ax | grep nodeny | grep pl | grep -v grep
 892 v0- S       1:04,59 /usr/bin/perl /usr/local/nodeny/nokernel.pl -d
 893 v0- S      78:51,21 /usr/bin/perl /usr/local/nodeny/noserver.pl -d

[root@nod+ /usr/home/lexx]# ps ax | grep nodeny | grep pl | grep -v grep
  892 v0- S       22:22,90 /usr/bin/perl /usr/local/nodeny/nokernel.pl -d
  893 v0- S      493:25,92 /usr/bin/perl /usr/local/nodeny/noserver.pl -d
35829  1- S      172:17,50 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=collectors -d
[root@nod+ /usr/home/lexx]#

run     => 0,

interface => 'igb0',

# /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=authtraf
Start. Flag -h for help
loading /usr/local/nodeny/sat.cfg
loading kernel::authtraf.pm
Start
retrieval of routing table: Cannot allocate memory

my $mac = Net::ARP::arp_lookup( $interface, $p{ipa} );

my $mac = '';

ipfw add 100 allow tcp from any to any

659 v0- S    0:00.98 /usr/bin/perl /usr/local/nodeny/noserver.pl -d
 661 v0- S    0:00.99 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=collectors -d

#!/bin/sh -
f='/sbin/ipfw'

ifOut='igb0'
#ifOut='re0 ng0 ng1'

use_ipcad_divert=YES


ifVia=''
ifRecv=''
tmp_or=''
for i in $ifOut
  do
    ifVia="${ifVia}${tmp_or}via $i"
    ifRecv="${ifRecv}${tmp_or}recv $i"
    tmp_or=' or '
 done

${f} -f flush

# dns, www.liqpay.com, liqpay.com
${f} table 100 add 8.8.8.8
${f} table 100 add 50.16.196.80
${f} table 100 add 107.21.122.223

# mysql slave server cs.csmoney.net
${f} table 101 add 50.17.182.68
${f} table 101 add 10.0.0.6
${f} add 50 allow tcp from any to me 22
${f} add 51 allow tcp from me 22 to any

${f} add 110 allow ip from any to any via lo0
${f} add 120 skipto 1000 ip from me to any
${f} add 130 deny icmp from any to any in icmptype 5,9,13,14,15,16,17
${f} add 160 skipto 2000 ip from any to me

${f} add 200 skipto 500 ip from any to any { $ifVia }

${f} add 300 skipto 4500 ip from any to any in

${f} add 400 skipto 450 ip from any to any { $ifRecv }
if [ $use_ipcad_divert ]; then
  ${f} add 420 divert 1 ip from any to any
  ${f} add 450 divert 2 ip from any to any
fi
${f} add 490 allow ip from any to any

${f} add 500 skipto 32500 ip from any to any in
if [ $use_ipcad_divert ]; then
  ${f} add 510 divert 1 ip from any to any
fi
${f} add 540 allow ip from any to any


${f} add 1000 allow udp from any 53,7723 to any
${f} add 1010 allow tcp from any to any setup keep-state
${f} add 1020 allow udp from any to any keep-state
${f} add 1100 allow ip from any to any

${f} add 2000 check-state
${f} add 2010 allow icmp from any to any
${f} add 2020 allow tcp from any to any 22,80,443,5006
${f} add 2030 allow tcp from "table(101)" to any
${f} add 2050 deny ip from any to any { $ifVia }
${f} add 2060 allow udp from any to any 53,7723

${f} add 2100 deny ip from any to any
${f} add 4500 allow ip from any to "table(100)"
${f} add 32490 deny ip from any to any

${f} add 32500 allow ip from "table(100)" to any

ipfw add 1 allow ip from any to any

 {
                 type => 'ipcad',
                 addr => '10.0.0.6',
                 rsh => '/usr/bin/rsh',
             },

# ps ax | grep nodeny | grep pl | grep -v grep
 917 v0- S      0:57,50 /usr/bin/perl /usr/local/nodeny/nokernel.pl -d
 918 v0- S      9:42,79 /usr/bin/perl /usr/local/nodeny/noserver.pl -d
 919 v0- S      2:17,07 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=collectors -d
 920 v0- S      0:16,17 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=authtraf -d

# ps ax | grep nodeny | grep pl | grep -v grep
1197 v0- S       9:08.26 /usr/bin/perl /usr/local/nodeny/noserver.pl -d

]# perl nokernel.pl -L
Start. Flag -h for help
loading /usr/local/nodeny/sat.cfg

MODULE                   AUTORUN?
authtraf                    -
system_clean               YES
collectors                  -
services                   YES
notifications               -
tmppays                    YES
balance                    YES
auth                       YES
system_check               YES
websession                 YES
authserver                  -
cap                         -
make_config                 -

# perl nokernel.pl -L
Start. Flag -h for help
loading /usr/local/nodeny/sat.cfg

MODULE                   AUTORUN?
authtraf                    -
auth                       YES
tmppays                    YES
services                   YES
system_clean               YES
cap                         -
system_check               YES
notifications               -
balance                    YES
authserver                  -
collectors                  -
make_config                 -
websession                 YES

my $mac = '';

Unknown column 'adm_owner' in 'field list' 

{
  'param' => [],
  'sql' => 'SELECT a.*, c.card_balance FROM admin a LEFT JOIN (SELECT adm_owner, SUM(money) AS card_balance FROM cards WHERE alive IN(\'good\', \'activated\') GROUP BY adm_owner) c ON a.id=c.adm_owner ORDER BY a.login'
};
 
 SELECT a.*, c.card_balance FROM admin a LEFT JOIN (SELECT adm_owner, SUM(money) AS card_balance FROM cards WHERE alive IN('good', 'activated') GROUP BY adm_owner) c ON a.id=c.adm_owner ORDER BY a.login

mysql> show create table cards;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cards | CREATE TABLE `cards` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cod` tinytext CHARACTER SET cp1251 NOT NULL,
  `money` float(6,2) NOT NULL DEFAULT '0.00',
  `tm_create` int(11) unsigned NOT NULL DEFAULT '0',
  `tm_end` int(11) unsigned NOT NULL DEFAULT '0',
  `tm_activate` int(11) unsigned NOT NULL DEFAULT '0',
  `tm_move` int(11) unsigned DEFAULT '0',
  `adm_create` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `alive` enum('good','bad','stock','activated') NOT NULL DEFAULT 'good',
  `uid_activate` int(10) unsigned NOT NULL DEFAULT '0',
  `r` mediumint(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`),
  KEY `r` (`r`)
) ENGINE=InnoDB AUTO_INCREMENT=136000 DEFAULT CHARSET=utf8 COMMENT='rev_0' |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ALTER TABLE cards CHANGE cod cod TEXT NOT NULL;
ALTER TABLE cards CHANGE r adm_owner MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE cards ADD adm_move MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER adm_owner;
ALTER TABLE cards ADD INDEX (adm_move);
ALTER TABLE cards ADD UNIQUE (cod(36));

mysql> ALTER TABLE cards CHANGE cod cod TEXT NOT NULL;
Query OK, 135951 rows affected (1.23 sec)
Records: 135951  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE cards CHANGE r adm_owner MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0';
ERROR 1264 (22003): Out of range value for column 'adm_owner' at row 11937

mysql> ALTER TABLE cards ADD adm_move MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER adm_owner;
ERROR 1054 (42S22): Unknown column 'adm_owner' in 'cards'

mysql> ALTER TABLE cards ADD INDEX (adm_move);
ERROR 1072 (42000): Key column 'adm_move' doesn't exist in table

mysql> ALTER TABLE cards ADD UNIQUE (cod(36));
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

UPDATE cards SET r=ABS(r);

ALTER TABLE cards CHANGE r adm_owner MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0';

ALTER TABLE cards CHANGE r adm_owner INT(11) NOT NULL DEFAULT '0';

ALTER TABLE cards ADD adm_move MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER adm_owner;
ALTER TABLE cards ADD INDEX (adm_move);

SELECT parent_id,field_value FROM dopdata WHERE field_alias='_mac';

INSERT INTO mac_uid (uid,mac)         (SELECT parent_id, REPLACE(field_value,':','') FROM dopdata WHERE field_alias='_mac')      ON DUPLICATE KEY UPDATE uid=parent_id;

ERROR 1364 (HY000): Field 'ip' doesn't have a default value

mysql> select * from ip_pool limit 10;
+----+------------+--------+--------+---------+------+------+
| id | ip         | type   | realip | release | uid  | tags |
+----+------------+--------+--------+---------+------+------+

mysql> show columns from mac_uid;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| mac         | varchar(12)         | YES  | UNI | NULL    |                |
| ip          | int(10) unsigned    | NO   | MUL | NULL    |                |
| uid         | bigint(20) unsigned | NO   | MUL | NULL    |                |
| time        | int(10) unsigned    | NO   | MUL | NULL    |                |
| device_mac  | varchar(12)         | NO   |     | NULL    |                |
| device_port | smallint(6)         | NO   |     | NULL    |                |
| oneconnect  | tinyint(4)          | NO   |     | 0       |                |
| comment     | varchar(256)        | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

INSERT INTO ip_pool SELECT NULL, INET_ATON(ip), 'static', 0, 0, id FROM users;

INSERT INTO mac_uid (uid, mac, ip, time, device_mac, device_port, oneconnect, comment)
    (SELECT parent_id, REPLACE(REPLACE(field_value, ':', ''), '-', ''), 0, 0, '', 0, 0, ''
     FROM dopdata WHERE field_alias='_mac' AND field_value <>'') 
    ON DUPLICATE KEY UPDATE uid=parent_id;

SELECT * FROM mac_uid ORDER BY mac
Строк: 10. Время выполнения sql: 0.0247 сек

Can't use string ("") as a HASH ref while "strict refs" in use at /usr/local/nodeny/kernel/make_config.pm line 131.

if( $config->{db_table}{mac_uid} )
    {
        my $db = Db->sql("SELECT * FROM mac_uid ORDER BY mac");
        $db->ok or return;
        my $indexes = {};
        while( my %p = $db->line )
        {
            my $uid = $p{uid};
            exists $users{$uid} or next;
            my $i = $indexes->{$uid}++ || '0';
            $users{$uid}->{connection}{$i} = \%p;
           [b] $users{$uid}->{mac}{$i++} = $p{mac};[/b]
        }
    }

perl -e'use strict; my $a={}; $a->{a}{a} = 5'

perl -v | head -2

# perl -e'use strict; my $a={}; $a->{a}{a} = 5'
# perl -v | head -2

This is perl 5, version 26, subversion 2 (v5.26.2) built for amd64-freebsd-thread-multi

show create table users;

| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `passwd` tinytext CHARACTER SET cp1251 NOT NULL,
  `grp` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `contract` tinytext NOT NULL,
  `contract_date` int(10) unsigned NOT NULL DEFAULT '0',
  `state` enum('off','on') NOT NULL DEFAULT 'on',
  `balance` float(10,2) NOT NULL DEFAULT '0.00',
  `limit_balance` float(6,2) NOT NULL DEFAULT '0.00',
  `block_if_limit` tinyint(4) NOT NULL DEFAULT '0',
  `mac` tinytext CHARACTER SET cp1250 NOT NULL,
  `modify_time` int(11) NOT NULL DEFAULT '0',
  `fio` tinytext NOT NULL,
  `discount` tinyint(4) NOT NULL DEFAULT '0',
  `real_ip` tinytext CHARACTER SET cp1251 NOT NULL,
  `cstate` int(11) NOT NULL DEFAULT '0',
  `cstate_time` int(10) unsigned NOT NULL DEFAULT '0',
  `comment` varchar(8192) NOT NULL DEFAULT '',
  `lstate` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `state` (`state`)
) ENGINE=InnoDB AUTO_INCREMENT=3448 DEFAULT CHARSET=utf8 COMMENT='rev_0' |

ALTER TABLE users DROP mac;

mysql> UPDATE cards SET r=ABS(r);
Query OK, 7 rows affected (0.45 sec)
Rows matched: 135951  Changed: 7  Warnings: 0

mysql> ALTER TABLE cards CHANGE r adm_owner MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0';
Query OK, 135951 rows affected (0.62 sec)
Records: 135951  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE cards ADD adm_move MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER adm_owner;
Query OK, 135951 rows affected (0.63 sec)
Records: 135951  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE cards ADD INDEX (adm_move);
Query OK, 135951 rows affected (0.83 sec)
Records: 135951  Duplicates: 0  Warnings: 0

ALTER TABLE `admin` MODIFY COLUMN passwd varbinary(64) NOT NULL;
 ALTER TABLE `users` MODIFY COLUMN passwd VARCHAR(64) NOT NULL;

 ALTER TABLE `users` MODIFY COLUMN passwd VARCHAR(64) NOT NULL;

SELECT AES_DECRYPT(passwd,'hardpass3') FROM users;

SELECT AES_DECRYPT(passwd,'hardpass3') FROM admin;

use nodeny;
source bill.sql; (бекап со старого биллинга)
source from_50.32_to_n_plus.txt;
cd /usr/local/nodeny
perl install.pl -x
perl install.pl -w=www
perl install.pl -m
perl install.pl -u

use nodeny;
source bill.sql; (от Н+ , который идет в комплекте)

	0.400556	
304 Db.pm::sql
	

Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. 

{
  'param' => [
    8688
  ],
  'sql' => 'INSERT INTO users_trf SET uid=?'
};
 
 INSERT INTO users_trf SET uid='8688'

+	0.400759	
181 Db.pm::rollback
	rollback

Field 'ip' doesn't have a default value 

{
  'sql' => 'INSERT INTO users SET modify_time=UNIX_TIMESTAMP(), comment=?, lstate=?, contract_date=?, limit_balance=?, block_if_limit=?, grp=?, state=?, cstate=?, contract=?, cstate_time=?, fio=?, passwd=AES_ENCRYPT(?,?), id=?',
  'param' => [
    '',
    0,
    0,
    '0',
    1,
    20,
    'on',
    1,
    '',
    0,
    '',
    '87464438h1',
    '3654xx',
    8683
  ]
};
 
 INSERT INTO users SET modify_time=UNIX_TIMESTAMP(), comment='', lstate='0', contract_date='0', limit_balance='0', block_if_limit='1', grp='20', state='on', cstate='1', contract='', cstate_time='0', fio='', passwd=AES_ENCRYPT('87464438h1','3654xx'),
 id='8683'

Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. 

{
  'sql' => 'INSERT INTO users_trf SET uid=?',
  'param' => [
    8696
  ]
};
 
 INSERT INTO users_trf SET uid='8696'

perl install.pl -u

use nodeny;
source bill.sql; (бекап со старого биллинга)
source from_50.32_to_n_plus.txt;
cd /usr/local/nodeny
perl install.pl -x
perl install.pl -w=www
perl install.pl -m