ProxySQL 1.4.4 Unable to connect with laravel

[Aban]

Hi, we are facing some issues with the latest version of proxysql. This is what we are using:

• PHP 5.4
• Laravel 5.1
• ProxySQL 1.4.4

We are getting a 'SQL STATE Connection refuse error'. I've checked the username and password and they are correct. On december 14 we downloaded the source code and compiled ProxySQL and it worked fine, we did't have any issue with it, but now that we downloaded the oficial release of proxysql 1.4.4 our PHP + Laravel application is unable to connect to proxysql.

Also i am able to connect to proxysql using Sequel Pro with no issues.

What else can i give you to help you to fix this issue?

[lazzyfu]

vim .config/database.php

add:

'options' => [ PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
PDO::ATTR_EMULATE_PREPARES => true, //default is False ]

[renecannao]

@wolfefu : thanks for jumping in.

@Aban : I never used Laravel, can you please provide your config file(s) (without password, if any)? I will plan to test Laravel

[Aban]

I tested again and i found that this only happen with the debug version of proxysql 1.4.4, the release version works fine. I attached my proxysql.cnf.

proxysql.cnf

#file proxysql.cfg

# This config file is parsed using libconfig , and its grammar is described in:
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin"
#   mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
    mysql_ifaces="0.0.0.0:6032"
#   refresh_interval=2000
#   debug=true
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033;/home/proxysql/tmp/proxysql.sock"
#   interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.6.36"
    connect_timeout_server=3000
# make sure to configure monitor nombreusuario and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_nombreusuario-mysql-monitor_password
    monitor_username="monitorusername"
    monitor_password="monitorpassword"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

# defines all the MySQL servers
mysql_servers =
(
    {
        address = "xx.xx.xx.xxx"    # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
        port = 3306                 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
        hostgroup = 0               # no default, required
        status = "ONLINE"           # default: ONLINE
        weight = 1                  # default: 1
        max_connections = 20
    }
#   {
#       address = "/var/lib/mysql/mysql.sock"
#       port = 0
#       hostgroup = 0
#   },
#   {
#       address="127.0.0.1"
#       port=21891
#       hostgroup=0
#       max_connections=200
#   },
#   { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
#   { address="127.0.0.1" , port=21892 , hostgroup=1 },
#   { address="127.0.0.1" , port=21893 , hostgroup=1 }
#   { address="127.0.0.2" , port=3306 , hostgroup=1 },
#   { address="127.0.0.3" , port=3306 , hostgroup=1 },
#   { address="127.0.0.4" , port=3306 , hostgroup=1 },
#   { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
)

# defines all the MySQL users
mysql_users:
(
    {
        username = "xxxxxxxxxxxxxxx" # no default , required
        password = "xxxxxxxxxxxxxxx" # default: ''
        default_hostgroup = 0 # default: 0
        active = 1            # default: 1
        max_connections = 1000
    }
#   {
#       username = "root"
#       password = ""
#       default_hostgroup = 0
#       max_connections=1000
#       default_schema="test"
#       active = 1
#   },
#   { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
)

#defines MySQL Query Rules
mysql_query_rules:
(
#   {
#       rule_id=1
#       active=1
#       match_pattern="^SELECT .* FOR UPDATE$"
#       destination_hostgroup=0
#       apply=1
#   },
#   {
#       rule_id=2
#       active=1
#       match_pattern="^SELECT"
#       destination_hostgroup=1
#       apply=1
#   }
)

scheduler=
(
#  {
#    id=1
#    active=0
#    interval_ms=10000
#    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
#    arg1="0"
#    arg2="0"
#    arg3="0"
#    arg4="1"
#    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
#  }
)

mysql_replication_hostgroups=
(
#        {
#                writer_hostgroup=30
#                reader_hostgroup=40
#                comment="test repl 1"
#       },
#       {
#                writer_hostgroup=50
#                reader_hostgroup=60
#                comment="test repl 2"
#        }
)

# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#
# setting = name (":" | "=") value (";" | "," | empty)
#
# value = scalar-value | array | list | group
#
# value-list = value | value-list "," value
#
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
#                | string
#
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#
# array = "[" (scalar-value-list | empty) "]"
#
# list = "(" (value-list | empty) ")"
#
# group = "{" (setting-list | empty) "}"
#
# empty =

[lazzyfu]

@renecannao
Thanks to proxysql developer, this is the best software I've ever used