C++ locking MySQL from PHP.

Dev Articles Community Forums Sponsor:

March 1st, 2005, 08:47 AM

MartyConlonJr

Registered User

Join Date: Jul 2004

Posts: 14

Time spent in forums: 3 h 10 m 52 sec
Reputation Power: 0

C++ locking MySQL from PHP.

Hi all,

I haven't done too much work with MySQL before, and am not entirely
sure whether this problem is C++, MySQL or PHP related.

I am creating a simulation basketball game. The Game runs on a server
using Microsoft Visual C++ 6.0 and a copy of PHPDEV which has its own
MySQL server on it. I had been creating the C++ side with data in MySQL
which the game uses.

The game is written entirely in C++. It recieves the game_ID as an
argument and then connects to mySQL, getting all of the rest of the data
from different tables. It then simulates a game, writing information after
every 'play' is made (so is constantly writing to MySQL). It writes to a
'playbyplay' table and a 'game_stats' table. It writes this after every play,
so that the PHP side can display the 'live' stats to the user.

Now I wrote the C++ side, and my mate (a web designer) wrote the PHP
side. His tested fine by itself, while mine tested ok by itself. But when
put together, his PHP couldn't do select statements because it couldn't
access the database while the game was running, and even after it had
completed. He tells me my code is causing the problem.

If it had just had problems accessing WHILE the game was running, I'd
assume it was a problem with MySQL just locking while it's writing, but
after the game has completed and the C++ script has ended, the PHP
can't access the written to information, but if I reboot the computer,
PHP can then access the MySQL code fine.

So I assume there is some problem with my code not letting go of its
connections properly, holding on to locks and chewing up memory that
is keeping PHP out.

I used www.devarticles.com/c/a/Cplusplus/Building-a-Store-Application-With-MySQL-and-C/
to help me write the code and think I have used mysql_close(pConnection); and
mysql_free_result(pResult); in every function I called to close each connection
and so on, so I need to know:

Is there any sql close connection stuff I have forgotten, or is there another
explanation?

March 1st, 2005, 09:14 AM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 8

Quote:

He tells me my code is causing the problem.

^ Typical programmer line

Your game sounds intersting, you've described it well.
I like your thinking, the tactics you've tried in debugging are very logical.

After all you've described, I might think its MySQL causing the problem
MySQL has an auto-commit, which i think is turned on by default.
One thing could be with the table permissions.
Does the PHP log in to mysql with the same user id as the C++ script?
I suggest keeping them different, giving PHP SELECT privileges and C++ Insert (maybe update?) privileges...

Other then that, um, maybe slow down the C++'s execution?

Which box do you reboot? The MySQL one?

March 1st, 2005, 09:55 AM

MartyConlonJr

Registered User

Join Date: Jul 2004

Posts: 14

Time spent in forums: 3 h 10 m 52 sec
Reputation Power: 0

Thanks for the quick reply,

I am using the same user id for PHP and C++. I had not thought of that, I'll give it a try with different users.

I was really hoping it wasn't mysql, because if theres no way to allow reading while writing to a table (if it locks the whole table when writing) then I'm screwed because it writes constantly. As its a sim basketball game, it is scheduled to run in real time, that is, for every play (randomly between 1-24 seconds) it will do a Sleep(time*100) so that it runs in this real-time speed (where time is the time in seconds taken off the shotclock and 'Sleep()' runs in hundredths of a sec). Obviously while I'm testing it I don't want to sit there for 48 minutes per game, so it is set to Sleep(time*1), but I have tried running it at *100 to check if it was the speed of the updates. PHP still cannot access at this speed.

The box I am rebooting is one and the same, hosting both mysql and the php code, i figure it is the reboot that is clearing out any dodgy mysql connections.

I should add that the C++ code crashes after like 5 or 6 runs if I comment out the sleeps altogether, another reason i thought it was leaving open connections so by the 6th run or so it was unable to connect to MySQL and died. Earlier I had additional code that updated all 24 players minutes played at the end of each play and it was dying after fewer runs (these have been commented out) It really looks like something dodgy between mysql and c++ but this is the first time i have worked with either and I don't know too much about how they work. I would post the SQL statements segment of code but am at work and can't cut and paste my code on the laptop as it has no connection and im writing this from the LAN PC. I might post it when I get home in 6 hours.....

March 1st, 2005, 03:47 PM

MartyConlonJr

Registered User

Join Date: Jul 2004

Posts: 14

Time spent in forums: 3 h 10 m 52 sec
Reputation Power: 0

Here is one of the SQL statement functions. It is called often in the program - oh, and gameClock is an int variable, while homeTeam and awayTeam are pointers of a structure type. They are all global vars right now. haven't fixed that up yet.........

///////////////////////////////////////
// //
// Update Play By Play Method //
// //
///////////////////////////////////////
int updatePlayByPlay(int gameID, int quarterNumber, string description, int scoreFlag, int teamID)
{
MYSQL *pConnection; // pointer the MYSQL structure
static int playNum=1;
ostringstream tempStr;
string tempTime;

if (gameClock>=600)
{
if (gameClock/600<10)
{
tempStr << "0";
}
tempStr << gameClock/600 << ":";
if ((gameClock%600)/10<10)
{
tempStr << "0";
}
tempStr << (gameClock%600)/10;
tempTime=tempStr.str();
}
else
{
if ((gameClock%600)/10<10)
{
tempStr << "0";
}
tempStr << (gameClock%600)/10 << "." << gameClock%10;
tempTime=tempStr.str();
}

cout << tempTime << " " << description << endl;

//Instantiate Query variable
char myQuery[400];

// Initialise MySql Connection
pConnection = mysql_init(NULL);
if(!pConnection)
return 0;

// Connect to GMBA Database
if(mysql_real_connect(pConnection,"localhost","root","","GMBA",0,NULL,0) == NULL)
return 0;
sprintf(myQuery, "INSERT INTO playbyplay(Game_ID, Play_ID, Time, Quarter, HomeScore, AwayScore, Description, Score_Flag, Team_ID) VALUES(%d,%d,'%s',%d,%d,%d,'%s',%d,%d)", gameID, playNum, &tempTime[0], quarterNumber, homeTeam->pts, awayTeam->pts, &description[0], scoreFlag, teamID);
if(mysql_query(pConnection,myQuery) != 0) //execute the query
{
printf("Error Executing the query: %s\n\n",mysql_error(pConnection));
}
playNum++;

mysql_close(pConnection);

return 0;
}

March 3rd, 2005, 08:37 AM

MartyConlonJr

Registered User

Join Date: Jul 2004

Posts: 14

Time spent in forums: 3 h 10 m 52 sec
Reputation Power: 0

I used different user IDs for MySQL:

'phpaccess' with SELECT privileges
'root' for c++ with ALL privileges

I even fixed up some php stuff because it was looking at the 10 most
recent 'plays' and taking the latest and getting the score by quarter
and all this other arithmetic/sorting type stuff and i thought doing
sort selects might have been the prob with the intensity on mysql.

I added an extra table with the information as it needs to be
displayed so that PHP didn't need any technical stuff, just straight
queries. Still gets locked out.

So its not permissions, its not PHP, and its not the C++ queries
hammering mysql too rapidly (prob occurs even with avg of 12 seconds
between queries using sleeps) I'm left with either:

1. Problem with my update/insert code in C++ (not releasing connections properly)

2. Actual problem with MySQL.

March 3rd, 2005, 08:44 AM

MartyConlonJr

Registered User

Join Date: Jul 2004

Posts: 14

Time spent in forums: 3 h 10 m 52 sec
Reputation Power: 0

Here are my SQL coding parts. There are quite a few so i have to post them over multiple posts

Code:

 
///////////////////////////////////////
//								 //
// Clear Team Game Stats Method	 //
//								 //
///////////////////////////////////////
int clearTeamGameStats(int gameID)
{
MYSQL *pConnection;			 // pointer the MYSQL structure 
pConnection = mysql_init(NULL); // Initialise MySql Connection
if(!pConnection) 
return 0; 
// Connect to GMBA Database
if(mysql_real_connect(pConnection,"localhost","root","u2uksw9m","GMBA",0,NULL,0) == NULL)
return 0; 
 
//Instantiate Query variable
char myQuery[400];
sprintf(myQuery, "DELETE FROM team_game_stats WHERE Game_ID=%d", gameID);
if(mysql_query(pConnection,myQuery) != 0) //execute the query 
{ 
printf("Error Executing the query: %s\n\n",mysql_error(pConnection)); 
} 
// Init Game_Stats Database with players
sprintf(myQuery, "INSERT INTO team_game_stats(Game_ID, Team_ID) VALUES(%d, %d)", gameID, homeTeam->teamID);
if(mysql_query(pConnection,myQuery) != 0) //execute the query 
{ 
printf("Error Executing the query: %s\n\n",mysql_error(pConnection)); 
} 
sprintf(myQuery, "INSERT INTO team_game_stats(Game_ID, Team_ID) VALUES(%d, %d)", gameID, awayTeam->teamID);
if(mysql_query(pConnection,myQuery) != 0) //execute the query 
{ 
printf("Error Executing the query: %s\n\n",mysql_error(pConnection)); 
} 
mysql_close(pConnection);
return 0;
}
}
///////////////////////////////////////
//								 //
// Get DataBase Info Method		 //
//								 //
///////////////////////////////////////
int getDBInfo(int gameID)
{
MYSQL *pConnection;	 // pointer the MYSQL structure 
MYSQL_RES *pResult;	 // pointer to the result set structure 
MYSQL_ROW Row;			// row information
// Initialise MySql Connection
pConnection = mysql_init(NULL);
if(!pConnection) 
return 0; 
// Connect to GMBA Database
if(mysql_real_connect(pConnection,"localhost","root","u2uksw9m","GMBA",0,NULL,0) == NULL)
return 0; 
//Instantiate Query variable
char myQuery[200];
// Query Game table
sprintf(myQuery, "SELECT * FROM game WHERE Game_ID=%d\n",gameID);
mysql_query(pConnection,myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
homeTeam->teamID=atoi(Row[1]);
awayTeam->teamID=atoi(Row[2]);
myGame->attendance=atoi(Row[3]);
myGame->gameRefs[0]=new refRecord;
myGame->gameRefs[1]=new refRecord;
myGame->gameRefs[2]=new refRecord;
myGame->gameRefs[0]->refID=atoi(Row[4]);
myGame->gameRefs[1]->refID=atoi(Row[5]);
myGame->gameRefs[2]->refID=atoi(Row[6]);
myGame->gameDate=Row[7];
strcpy(myGame->gameTime,Row[8]);
}
mysql_free_result(pResult);
// Query Game_Settings table for Home Team
sprintf(myQuery, "SELECT * FROM game_settings WHERE Game_ID=%d AND Team_ID=%d\n",gameID,homeTeam->teamID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
for (int x = 0; x < 12; x++)
{
homeTeam->teamPlayer[x]=new playerRecord;
homeTeam->teamPlayer[x]->playerID=atoi(Row[x+2]);
homeTeam->teamPlayer[x]->playerMins=atoi(Row[x+14]);
}
homeTeam->teamOffense=Row[26];
}
mysql_free_result(pResult); 
// Query Game_Settings table for Away Team
sprintf(myQuery, "SELECT * FROM game_settings WHERE Game_ID=%d AND Team_ID=%d\n",gameID,awayTeam->teamID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
for (int x = 0; x < 12; x++)
{
awayTeam->teamPlayer[x]=new playerRecord;
awayTeam->teamPlayer[x]->playerID=atoi(Row[x+2]);
awayTeam->teamPlayer[x]->playerMins=atoi(Row[x+14]);
awayTeam->teamOffense=Row[26];
}
}
mysql_free_result(pResult); 
// Query Team table for Home Team
sprintf(myQuery, "SELECT * FROM team WHERE Team_ID=%d\n",homeTeam->teamID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
homeTeam->teamCoach=new coachRecord;
homeTeam->teamCoach->coachID=atoi(Row[1]);
strcpy(homeTeam->teamHome,Row[2]);
strcpy(homeTeam->teamName,Row[3]);
strcpy(homeTeam->stadName,Row[4]);
strcpy(homeTeam->stadLoc,Row[5]);
strcpy(homeTeam->stadState,Row[6]);
homeTeam->stadSize=atoi(Row[7]);
strcpy(homeTeam->teamShort,Row[8]);
}
mysql_free_result(pResult);
// Query Team table for Away Team
sprintf(myQuery, "SELECT * FROM team WHERE Team_ID=%d\n",awayTeam->teamID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
awayTeam->teamCoach=new coachRecord;
awayTeam->teamCoach->coachID=atoi(Row[1]);
strcpy(awayTeam->teamHome,Row[2]);
strcpy(awayTeam->teamName,Row[3]);
strcpy(awayTeam->stadName,Row[4]);
strcpy(awayTeam->stadLoc,Row[5]);
strcpy(awayTeam->stadState,Row[6]);
awayTeam->stadSize=atoi(Row[7]);
strcpy(awayTeam->teamShort,Row[8]);
}
mysql_free_result(pResult);
// Query Player table for Home Team Player information
for (int x = 0; x < 12; x++)
{
sprintf(myQuery, "SELECT * FROM player WHERE Player_ID=%d\n",homeTeam->teamPlayer[x]->playerID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
homeTeam->teamPlayer[x]->firstName=Row[1];
homeTeam->teamPlayer[x]->lastName=Row[2];
homeTeam->teamPlayer[x]->school=Row[3];
homeTeam->teamPlayer[x]->weight=atoi(Row[4]);
homeTeam->teamPlayer[x]->height=atoi(Row[5]);
homeTeam->teamPlayer[x]->cEffective=atoi(Row[6]);
homeTeam->teamPlayer[x]->pfEffective=atoi(Row[7]);
homeTeam->teamPlayer[x]->sfEffective=atoi(Row[8]);
homeTeam->teamPlayer[x]->sgEffective=atoi(Row[9]);
homeTeam->teamPlayer[x]->pgEffective=atoi(Row[10]);
homeTeam->teamPlayer[x]->postRange=atoi(Row[11]);
homeTeam->teamPlayer[x]->midRange=atoi(Row[12]);
homeTeam->teamPlayer[x]->longRange=atoi(Row[13]);
homeTeam->teamPlayer[x]->freeThrow=atoi(Row[14]);
homeTeam->teamPlayer[x]->ballHandle=atoi(Row[15]);
homeTeam->teamPlayer[x]->passing=atoi(Row[16]);
homeTeam->teamPlayer[x]->blocking=atoi(Row[17]);
homeTeam->teamPlayer[x]->stealing=atoi(Row[18]);
homeTeam->teamPlayer[x]->offRebound=atoi(Row[19]);
homeTeam->teamPlayer[x]->defRebound=atoi(Row[20]);
homeTeam->teamPlayer[x]->offAware=atoi(Row[21]);
homeTeam->teamPlayer[x]->defAware=atoi(Row[22]);
homeTeam->teamPlayer[x]->speed=atoi(Row[23]);
homeTeam->teamPlayer[x]->quickness=atoi(Row[24]);
homeTeam->teamPlayer[x]->jumping=atoi(Row[25]);
homeTeam->teamPlayer[x]->clutch=atoi(Row[26]);
homeTeam->teamPlayer[x]->fatigue=atoi(Row[27]);
homeTeam->teamPlayer[x]->strength=atoi(Row[28]);
homeTeam->teamPlayer[x]->injury=atoi(Row[29]);
homeTeam->teamPlayer[x]->aggression=atoi(Row[30]);
homeTeam->teamPlayer[x]->resolve=atoi(Row[31]);
}
mysql_free_result(pResult); 
}
// Query Player table for Away Team Player information
for (x = 0; x < 12; x++)
{
sprintf(myQuery, "SELECT * FROM player WHERE Player_ID=%d\n",awayTeam->teamPlayer[x]->playerID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
awayTeam->teamPlayer[x]->firstName=Row[1];
awayTeam->teamPlayer[x]->lastName=Row[2];
awayTeam->teamPlayer[x]->school=Row[3];
awayTeam->teamPlayer[x]->weight=atoi(Row[4]);
awayTeam->teamPlayer[x]->height=atoi(Row[5]);
awayTeam->teamPlayer[x]->cEffective=atoi(Row[6]);
awayTeam->teamPlayer[x]->pfEffective=atoi(Row[7]);
awayTeam->teamPlayer[x]->sfEffective=atoi(Row[8]);
awayTeam->teamPlayer[x]->sgEffective=atoi(Row[9]);
awayTeam->teamPlayer[x]->pgEffective=atoi(Row[10]);
awayTeam->teamPlayer[x]->postRange=atoi(Row[11]);
awayTeam->teamPlayer[x]->midRange=atoi(Row[12]);
awayTeam->teamPlayer[x]->longRange=atoi(Row[13]);
awayTeam->teamPlayer[x]->freeThrow=atoi(Row[14]);
awayTeam->teamPlayer[x]->ballHandle=atoi(Row[15]);
awayTeam->teamPlayer[x]->passing=atoi(Row[16]);
awayTeam->teamPlayer[x]->blocking=atoi(Row[17]);
awayTeam->teamPlayer[x]->stealing=atoi(Row[18]);
awayTeam->teamPlayer[x]->offRebound=atoi(Row[19]);
awayTeam->teamPlayer[x]->defRebound=atoi(Row[20]);
awayTeam->teamPlayer[x]->offAware=atoi(Row[21]);
awayTeam->teamPlayer[x]->defAware=atoi(Row[22]);
awayTeam->teamPlayer[x]->speed=atoi(Row[23]);
awayTeam->teamPlayer[x]->quickness=atoi(Row[24]);
awayTeam->teamPlayer[x]->jumping=atoi(Row[25]);
awayTeam->teamPlayer[x]->clutch=atoi(Row[26]);
awayTeam->teamPlayer[x]->fatigue=atoi(Row[27]);
awayTeam->teamPlayer[x]->strength=atoi(Row[28]);
awayTeam->teamPlayer[x]->injury=atoi(Row[29]);
awayTeam->teamPlayer[x]->aggression=atoi(Row[30]);
awayTeam->teamPlayer[x]->resolve=atoi(Row[31]);
}
mysql_free_result(pResult); 
}
//Query Referee table for Referee information
for (x = 0; x < 3; x++)
{
sprintf(myQuery, "SELECT * FROM referee WHERE Referee_ID=%d\n",myGame->gameRefs[x]->refID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
myGame->gameRefs[x]->firstName=Row[1];
myGame->gameRefs[x]->lastName=Row[2];
myGame->gameRefs[x]->refNumber=atoi(Row[3]);
}
mysql_free_result(pResult); 
}
 
//Query Coach table for Coach information
sprintf(myQuery, "SELECT * FROM coach WHERE Coach_ID=%d\n",homeTeam->teamCoach->coachID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
homeTeam->teamCoach->firstName=Row[1];
homeTeam->teamCoach->lastName=Row[2];
}
mysql_free_result(pResult); 
sprintf(myQuery, "SELECT * FROM coach WHERE Coach_ID=%d\n",awayTeam->teamCoach->coachID);
mysql_query(pConnection, myQuery);
pResult = mysql_use_result(pConnection); 
while ((Row = mysql_fetch_row(pResult)))
{
awayTeam->teamCoach->firstName=Row[1];
awayTeam->teamCoach->lastName=Row[2];
}
mysql_free_result(pResult); 
 
mysql_close(pConnection);
return 0;
}

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: