Need help testing mysqli in PHP

[mcargile]

So Vicidial is currently written to use the mysql functions in PHP. PHP a while ago started developing a replacement for them called mysqli. Well now PHP is in the process of deprecating the mysql functions in favor of the mysqli functions. They have also stopped maintaining the mysql functions. This might be the reason Vicidial will not work with more than a few agents on MySQL 5.5. As such we need to convert all of Vicidial to the mysqli functions. Unfortunately they are not a drop in replacement for mysql. I believe I have done the necessary leg work to switch it over the agent interface. However I do not have enough people to properly test all of the functionality. I have logged in and taken some calls with it. Could a few people please test my mysqli changes on production systems? I have made it super easy to go back if there are problems.

To install the code:

1. upgrade the server to SVN revision 1928
2. download this file http://download.vicidial.com/beta-apps/agci-1928.tgz
3. Untar this in the web servers root directory. It will create an addition directory called agci.
4. Instead of having the agents log into http://SERVERIP/agc/vicidial.php have them log into http://SERVERIP/agci/vicidial.php (note the 'i' in agci).
5. If there are any problems you will still be able to have the agents log into http://SERVERIP/agc/vicidial.php which is the stock vicidial code.
6. Try and test as much functionality as possible.

This also effects the agent API so if someone could do some production level testing on that it would great as well. Just change from agc to agci in the api url path.

[mcargile]

Anyone get chance to test this out?

[DomeDan]

I checked it out and got this error:

PHP Warning: Missing argument 2 for my_mysqli_real_escape_string(), called in /srv/www/htdocs/agci/vdc_db_query.php on line 9630 and defined in /srv/www/htdocs/agci/dbconnect.php on line 15
PHP Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null given in /srv/www/htdocs/agci/dbconnect.php on line 16

just added $link to the function calls, posted a patch to it here http://www.vicidial.org/VICIDIALmantis/view.php?id=649

and there's a swap file still left in the tar file ".dbconnect.php.swp" was a bit surprised when the warning about that file popped up in vim.

those are the things I've seen so far. gonna make it default for the agents so I get sit back and wait for the problems to come to me


How is this in contrast to the upcoming conversion to MariaDB talked about in this thread http://vicidial.org/VICIDIALforum/viewt ... =2&t=26462 ?

[mcargile]

Here is an update for SVN 1932 with your fix in it:

http://download.vicidial.com/beta-apps/agci-1932.tgz

Let me know how that one works.

[KeithHBW]

What does the following statement mean in regards to "work":
This might be the reason Vicidial will not work with more than a few agents on MySQL 5.5

I use MySQL 5.5 and have ~20 agents per dialer logged in. ~100agents in a single webserver. Should I help test this out? I do not know what issues I am running into if I am running into said issues.

[Noah]

Check your table types Mysql 5.5 creates INNODB database table types by default. This could lead to your slowness with just a few reps. So if you created your db for vici using the "create table script" on a fresh mysql install you might get yourself into innodb trouble.

We actually ran into this trouble building a new system, in just that manner. Innodb was the default engine variable type or it wasn't established in my.cnf before running the create table script and simply defaulted the tables to innodb type.

Do NOT use innodb types of tables with vicidial, unless you really know how to tweak out mysql. I believe it's always been the recommendation to use myisam.

Confirm your type:

mysql>show variables like '%default_storage_engine';

If you don't have MyISAM below - you're probably going to see some slowness.
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+

Check your tables - swap db name with asterisk or whatever you named your db for vici.
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbname';

Noah
mycallcloud.com

[mcargile]

With MySQL 5.5 for some reason we have not completely narrowed down, agents will just randomly go into pause. This will happen with just a few agents on the system (5 or less). This has nothing to do with storage engine as all of the tables are MyISAM.

We are guessing the problem is probably related to PHP's implementation of the mysql connection. Vicidial uses the mysql database connection type that has been in PHP for years. However PHP started to implement mysqli a few years back. It is now the recommended mysql connection type. The standard mysql connection type has been deprecated and is no longer under development. This happened when MySQL was still in 5.0. We are thinking the issue is that enough has changed in MySQL 5.5 that it is no longer fully compatible with the mysql connection type. As such hopefully converting over to mysqli will fix the issue.

If that does not work we will be trying MariaDB.

In the mean time downgrading to MySQL 5.1 from 5.5 fixes the random agents pausing issue.


This topic is about testing the mysqli implementation I did to make sure everything is working in it. If there are no issues in it, it will probably become the connection type used in the final 2.6 Vicidial release. If you are looking at this topic because of agent pausing issues, please open a ticket in Support as that could be caused by a number of other things.

[williamconley]

If you have a live box experiencing the sudden pausing and would like another set of eyes, let me know. We've not had this problem as yet, and while a downgrade for mysql is a simple fix, I'd like to see this problem myself once to see if I can hit it. I've used mysqli exclusively for quite some time (except in Vicidial!) and have a fair amount of mysql experience. So if I had access to a machine experiencing this particular problem, I may be of assistance.

[mcargile]

Most of our clients who have the pausing issue are not particularly interested in letting us play around with it. They want it fixed quickly. Downgrading to 5.1 does that.

My concern in this topic really isn't the pausing agent issue though. The mysql connector is depreciated and scheduled to be removed from PHP. We have to switch to mysqli. I need people to try out my code and let me know if there is any problems. Thus far DomaDan helped me find one bug. Hopefully that is it.

If you want to look into the pausing issue, search the support forums. Kumba has already helped a few people downgrade to 5.1.

[williamconley]

I was just hoping to get my hands on one. After all, when the moment comes, as you say, the client will have NO interest in "troubleshooting", only in Fix it!!!

MySQL downgrade is of course simpler, but I hate downgrading when I can avoid it.

Unfortunately, I have no clients presently in "beta" mode upon whom I can test a mysqli (a lot of cheapskates, no doubt, but none presently that hungry!).

[ruben23]

Hi, im willing to test this now on production im having
Scratch install
Ubuntu Server 12.04 LTS
mysql 5.5
asterisk 1.8-vici-beta
SVN trunk
Multi Server 1DB, 3 AST, 2 (virtualweb-loadbalance-KVM)

[mcargile]

Make sure you are using svn 1932 then just extract this into your web root directory:

http://download.vicidial.com/beta-apps/agci-1932.tgz

Have the agents use the agci directory rather than agc directory.

[ruben23]

@ mcargile
My production system now is this -------------> SVN Version: 1938 <-----------------is this ok..?

[mcargile]

It is important that the SVN versions match so no this will not work. I will be updating the mysqli code sometime this week, but in the mean time you need to be running 1932.

[ruben23]

@ mcargile

How do i download that specific svn version 1932..? kindly help, i really wanted to test it.Thanks

[williamconley]

you use one of these to "acquire" the correct code:

Update: (if your present version is below the required version)

Code: Select all

 cd /usr/src/astguiclient/trunk
 svn up -r 1932

Checkout: (if you do not have any svn version or just want to install in a whole new folder, or have just deleted the previous folder outright)

Code: Select all

 cd /usr/src/astguiclient/
 svn co -r 1932 svn://svn.eflo.net:43690/agc_2-X/trunk

Downgrade: (to move aside the previous svn trunk without deleting it)

Code: Select all

 cd /usr/src/astguiclient/
 mv trunk trunkOLD
 svn co -r 1932 svn://svn.eflo.net:43690/agc_2-X/trunk

This must happen before your final installation.

[ruben23]

@ williamconley Thanks for this...

[bobbymc]

does anybody have a update on this?

[mflorell]

We committed mysqli changes to all PHP scripts a month or two ago. All appears to be working properly.

[saulortega]

Hello.

I think this is the reason of my problem: viewtopic.php?f=7&t=20223&start=40#p108505

How can I to fix it?

[saulortega]

In functions.php

Before:

Code: Select all

   require("dbconnect.php");

        $CFoutput='';
        $stmt="SHOW TABLES LIKE \"custom_$list_id\";";
        if ($DB>0) {echo "$stmt";}
        $rslt=mysql_query($stmt, $link);
                if ($mel > 0) {mysql_error_logging($NOW_TIME,$link,$mel,$stmt,'05002',$user,$server_ip,$session_name,$one_mysql_log);}
        $tablecount_to_print = mysql_num_rows($rslt);
        if ($tablecount_to_print > 0)
...

Now:

Code: Select all

   require("dbconnect_mysqli.php");

   $CFoutput='';
   $stmt="SHOW TABLES LIKE \"custom_$list_id\";";
   if ($DB>0) {echo "$stmt";}
   $rslt=mysql_to_mysqli($stmt, $link);
      if ($mel > 0) {mysql_error_logging($NOW_TIME,$link,$mel,$stmt,'05002',$user,$server_ip,$session_name,$one_mysql_log);}
   $tablecount_to_print = mysqli_num_rows($rslt);
   if ($tablecount_to_print > 0)

mysqli_num_rows($rslt) returns 0.

[mflorell]

Just tested this to confirm on our development system and a live production system and it worked properly on both.