MYSQL :: Scheduling Replication To Avoid Bottle-neck Updates

Scheduling Problem
I am trying to figure out how to schedule something to run every half hour.

I am building a real time web game whereby things happen every half hour, therefore i need to find a way of scheduling some kind of process. I am using php, so I could create a file i want to execute every half hour, or have the job inside mySQL. either way i have yet to find a way of doing this.

I read about cron and windows scheduling. I am running windows, so cron is out of the picture, however i read about cronw which runs on windows but requires perl, so i'm not that keen on using it (trying to keep the various technologies to a minimum). Windows scheduling does not permit a small enough time interval.

I also read about mySQL's event scheduler, which is part of mySQL 5.1, however I am running 5.0 and don't really want to upgrade to a beta.

This leaves me with only one other thought; that is to create a vb app that kicks off my php file every half hour or does the actual sql query every half hour.

I keep thinking there must be a better way though. This must be a very common problem.

Scheduling MydbPal
I have discovered this great program and I wish to use it for data
import from dbf to MySql.

I wonder if there is possibilty to schedule automatic data import ?

Scheduling Executing
I'm looking for a GUI that is able to schedule executing batch files.
I have a .sql file that inserts records in the database. This should be done every day.
For instance Navicat is able of executing this sql-file, but it can't be scheduled to execute this (I think).

Calendar Scheduling Script W/ Php And MySQL
I would like to create an online scheduling page for people to schedule meetings. Ideally this would display a calendar with the dates and times available, (which would be populated from a DB).

I don't have a problem with the scheduling after the calendar is created, the problem I cna't figure out is how to create a DB.

Right now I have a DB with a tables like such:

table months(yearID, monthID, monthName, firstDayOfMonth, totalDaysInMonth)

where firstDayOfMonth is the day of the week that the first day falls on.

for example, the query "SELECT * FROM months WHERE yearID=2002 and monthID=7" would yield:

2002, 7, July, 1, 31

Since 0=Sunday, 1=Monday and so on this tells me that July 2002 starts on a Monday and has 31 days, so from that I can construct a calendar.

Now, I know there is an easier way to construct a calendar, and I've looked for a perpetual calendar, but can't find any info on how to get the first day of the month based on any algorithm.

Scheduling Backups Does Not Work In MySql Administrator
Anyone know why the backup scheduling feature in MySql Administrator does not actually work. I've created a job and everything seems to be in place. The backup works fine when I select "Execute Backup Now" but not on the scheduled time. Is there something I'm missing?

Avoid Adding More Than Once
im trying to do what i think is a fairly easy query but im having some problems.

basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.

Avoid Self-joins
I have a table that has values of variables for certain entities. The
columns of interest are targetID, variableID, and valueID. A row (1, 5,
9) means that target number 1 has a value of 9 for variable 5. Being
denormalized, target number one will have many possible rows in this
table, one for each variable for which it has a value.

My problem occurs when I want to find out what targets match a certain
set of variable values. For instance, I want to find out what targets
have a value of 9 for variable 5 and a value of 25 for variable 10. I'm
thinking that this can be a simple self-join:

SELECT mya.targetID from mytable as mya
LEFT JOIN mytable as myb
ON mya.targetID=myb.targetID
WHERE (mya.variableID=5 AND mya.valueID=9)
AND (myb.variableID=10 AND myb.valueID=25)

Does this make sense so far? The problem is that this doesn't scale.
When I have more than 31 variables and I need to evaluate them all,
MySQL breaks: I can't do more than 31 joins.

My design calls for perhaps 80-100 variables, so even 64-bit
architecture with a limit of 64 joins won't get me there. This is NOT
an architecture or platform issue - I need a design and a data
structure that will scale to lots of variables.

I need another data structure that won't get me stuck on too many
joins.

How To Avoid Race Condition?
How do I lock a table for one of my insert (followed by a read) queries on a table such that other simultaneous insert/read queries on that table are put off until the first one is complete? I am trying to avoid a potential race condition.

Count(*) To Avoid Duplicates
This is for (2) seperate sites that share the same login table (same username/passwords will access both sites).

Users may register at either site, if they choose, then they should login at the other site to "complete" the last phase of registration at the other site to insert remaining needed values.

Problem is with duplicates. When some users won't login, as they should, "complete" the process at the other site. They may bypass and register anew like anyone else at the site for the first time. (Even though there is a pre-registration page at both sites -- asking if users have already registered at the other site prior to arriving at the second -- directing a login to complete the process.)

Reason this is a concern is that one site has a (6) page registration process, inserting to (8) tables.

My idea (aside from aleady checking for unique inserts on usernames/passwords/emails) is to check on the complete phone number.
To be used on a conditional show region on the second page of registration -- if the latest registrant entered the same phone number asmay aleady exist in the table, (previously enterered while registering on the other site and forgot about it) -- this 2nd registration page will not show due to the: count(*)

Starting with 'Area' code, is this practical/how to combine all three?
Anyone have better suggestions or experience on handling two site duiplicates?

Would there be any benefit in having ALL USERS (both new and prior from the "other" site) fill out the the 1st registration form (on the (6) page registration site) where they are then directed to login to complete the process?

'AREA' 'PRE' 'PHONE'

SELECT *
FROM `Members`
WHERE `Area`
IN (
SELECT `Area`
FROM 'Members`
GROUP BY `Area`
HAVING count(*)>1
)

note: some registrants have same company names fir satellite/regional offices

Avoid Zero In Interger Datatype
Is there any way to avoid zeros which automatically entered in database in those column those have integer data type.

or i want to enter - instead of those zero

How To Avoid Previous Results
I want to omit all results(id) from the first query in 2nd query , without using sub query...

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

How To Avoid NOT EXISTS In MySQL 4.0.26
I tried to use NOT EXISTS but recently found out that it is supported since 4.1. Now I'm trying to avoid it but I can't figure out how to do this. The Problem is that I have a table with (among outhers) a column SessionId and action.

Action may be opened and closed. Now I want to get all those sessions which are in state open, i.e. which have no line with action = closed. My first attempt was:

select sessionId
from audit AS a
where action = 'opened' AND
NOT EXISTS (SELECT * FROM audit AS b WHERE b.sessionId = a.sessionId AND b.action = 'closed')

Could anybody give me a hint how to get an equivalient query without a subquery?

How To Avoid Repeat Typing
I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma. I use windows 98.

Avoid Repeat Typing
I am a newbie in MySQL. I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma.

Command Used In Avoid Retyping
I don't want to retype a command once I wrote it. Does anyone knowa about an option that would just that like in a dos shell where you can use arrows to get back any command you have entered.

Avoid Couples In The Resultset
I use a query like this:
select t1.id_topics, i1.id_indices , t2.id_topics from indices i1, indices i2, topics t1, topics t2
where (i1.ind ='test' and i1.id_urls=t1.id_urls and i2.id_urls=t2.id_urls and i1.ind=i2.ind and t1.id_topics<>t2.id_topics)

it gives results like those:
"id_topic1","id_indices","id_topic2"
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the "inverted" mates of couples in the resultset. That means in the example
"36,682,37" should be eliminated since "37,682,36" is already part of the resultset. Is it possoble to express this request in the query?

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

Trying To Avoid Using Query Inside While Loop
I've done a lot of reading on here and I learned from some of Rudy's posts that it's a bad idea to do a query inside a while loop. I don't know why this is but he seems like an expert so I'll listen

What I am trying to do is take the top ten points from a player and display them. First I will post the tables then the code.

CREATE TABLE `tournament` (
`gameid` int(11) unsigned NOT NULL auto_increment,
`gametype` tinyint(2) unsigned NOT NULL default Ɔ',
`gamedate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
`leagueid` int(11) unsigned NOT NULL default Ɔ',
`seasonid` int(11) unsigned NOT NULL default Ɔ',
`roomid` int(11) NOT NULL default Ɔ',
`gamename` varchar(50) NOT NULL default '',
`cost` mediumint(4) NOT NULL default Ɔ',
`seats` mediumint(4) NOT NULL default Ɔ',
`notes` tinytext NOT NULL,
PRIMARY KEY (`gameid`)
) ;

CREATE TABLE `tournament_results` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`gameid` int(11) unsigned NOT NULL default Ɔ',
`memberid` int(11) NOT NULL default Ɔ',
`place` smallint(4) NOT NULL default Ɔ',
`earnings` smallint(5) default Ɔ',
`bounties` float unsigned default Ɔ',
`points` float NOT NULL,
PRIMARY KEY (`id`)
);
Now the follow code would be what I would use if I just wanted to add all the points and not limit it

PHP

mysql_query("SELECT
members.firstname,
members.lastname,
SUM(tournament_results.points) AS tpoints,
COUNT(*)as numgames,
AVG(tournament_results.points) AS apoints,
members.memberid AS membersid
FROM
members,
tournament_results,
leaguemembers, tournament
WHERE members.memberid = tournament_results.memberid
AND leaguemembers.memberid = members.memberid
AND leaguemembers.leagueid = '$lid'
AND tournament_results.gameid = tournament.gameid
AND tournament.seasonid = '$sid'
GROUP BY members.memberid
ORDER BY tpoints DESC");

That will total all of their games but I want to limit it their top 10 scores how can I do this without introducing a query inside of the while loop?

How To Avoid Having Thousands Of Records In A Many-to-many Relation
I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Cleaning Up MySQL Connections To Avoid 1040
Well, I'm trying to run
PHP

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
    $process_id=$row["Id"];
    if ($row["Time"] > 200 ) {
        $sql="KILL $process_id";
        mysql_query($sql);
    }
}

To clean up my connections, as I'm getting a 1040 error "too many connections". Of course, I can't run this until I can actually connect, unless there is a way around somehow. I don't have any admin rights, I just have a web-based "php my-admin" module to run the db.

Every page people access opens a mysql connection, and then it is closed with
PHP

mysql_close($connection);

. Would putting in
PHP

<?php mysql_close($connection); $NASI_connection = null;?>

Mysql Running All Queries Double ? How To Avoid ?
Sometimes, when my database server has been slow and building up hundreds of queries, I have the impression that mysql is running 'double' of triple . It's hard to explain, but when I look at mtop (a tool to see what queries are active and what time they take to finish) , I see a lot of queries that seem to hang and that are present more then once. Some of them have unique data in them that can not happen just by refreshing a form. When the peak moment is over, and I check my site, then I sometimes see for example the exact same forum post 3 or 4 times in the same thread. Needless to say, this causes a lot of extra load on my database.

I'm not sure what really happens, but it seems like mysql server is just piling up queries wich it can not process fast enough, and those queries seem to come in several times again. I use seperate webservers, but even if I reboot them all, the extra queries still come in as soon as the webserver is back up and php scripts start to work again. The only way to stop it all, is to restart mysql, but that is something that I can not just do very easily. I rely on several heap tables for my site and they need to be refilled with data from normal tables. So restarting mysql is something I only like to do at night, with very few visitors online.

I hope I'm explaining my problem well enough. And I hope there is a way for me to check what really is going on, and if there is a way to stop mysql from 'running double' ?

What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here?

I've got a table in mysql with all 5 fields defined
as tinytext

Problem is when I export this to to a text file for notepad
each field is padded out by several blank spaces,
and i think my eamil program doesnt like this type of structue :
field1 , field2 , field3 , field4

Updates
How do I look for/download and apply updates to the Windows 2000 server.

Updates
if there's a query that could help me 2 update my table fields as follows.
It needs to 2 tables.

Withing table2 i needs to update the same field twice 2 different records e.g. set name='1', name='2' where id='1' and id='2'.
I've tried it like that but all it does is to update both records with 2.
What i wanted 2 to was to update "name=1 where id=1" then "name=2 where id=2".
Can anyone help me on this?

SQL Updates
This is so weird, I'm writing a little poll thing for my website and I've run into a problem..
$update = 'UPDATE mith_poll SET ans7N = 3 WHERE id = 1';
mysql_query($update);
When I vote on an answer it will go up +1 but when I pick a different answer after the last field has been updated the same field gets updated again, I then have to vote again to make the second choice go up + 1..
Am I making sense?
I'll try a bit harder for those who don't understand..
I select answer A
Answer A gets updated
I select answer B
Answer A gets updated
I select answer B,C,D,E and which ever I picked gets updated
I select answer F and the last answer gets updated

Updates Not Consistent
I got a simple application in C# where I have a text box and the userinsert the serial number then I will check if its status in thedatabase is 'y' then I am able to select it and set a new status as's'. The problem here if I do it 2 for different serial number oneafter another for ex. first I select 00123 and then 00124. First the00123 is set to 's' but then the moment I select 00124 the status of00123 gets back to 'y'. I am using the same set of code for both 00123and 00124. There is no place where I run a query which sets the statusto 'y' and I dont understand why the status of 00123 can become to 'y'when it shows in the first place it has succeffully updated to 's'.Below is my code. I have commented the places what I do exactly.

MySQL Updates
We have a new application that will be using MySQL i haven't used it before so have some time put aside to get the hang.

I downloaded mysql-essential-5.0.45-win32.msi and installed it i have the app up and running ok but i'd like to know how i find out what MySQL patches are available and if they need applying. Is there a live update style facility?

Updates And UDF Statements
I have a particular UDF function called pcd which returns the first 1
or 2 letters of a postcode (postcode area), it works fine in selects
but when i use it in a update the column being updated ends up with
the first column in the table, 10 spaces, then the 2nd column in the
table, instead of whatever the UDF function returns.

Has anybody else experienced similar problems, im losing my faith in
mysql a bit, im trying to use it for serious business applications but
might have to find an alternative. I prefer linux servers to windows,
any advice, like Max DB?

Automatic Updates
I'm not sure if this is possible or not, but is there a way to automatically insert data and delete data in a database at specific times during the day?

Is there a script or something out there that would allow me to do this?

Substrings In Updates
I have a column called full_name that contains name like 'amar ram patel'.
I want to update the same table by breaking the name in 3 different values using space as delimiter and add it to the respective columns viz. name, middle_name and surname.
I guess substring_index function can be used for the same.

Trigger Updates
If a certain table is updated I want all other tables which contain some of its attributes as a foreign key to be updated as it is updated.
However the tables that I want to trigger updates from is ISAM
and the other tables are INNODB.

Table Updates
There is a field in a MyISAM table that gets updated each time a PHP page is reloaded, providing an indicator of continued presence on a web site. There is also an app on the server that watches this field for 'logged in' users and logs them out if the timestamp doesn't update for several minutes. Mostly it works, but sometimes it seems that the value in the database table isn't being updated. I.e. the web page refreshes, but the server-side app continues to get the same timestamp.

I'm pretty new to this environment, so I didn't attempt to layer session logic (transactions) over the top of the basic 'update table' and 'select' commands.
Is there something I can put in the php file, or a configuration option on the database or server that would insure the data is committed to disk? (and, since the docs say that happens when the php script terminates..., is there something else I might not be seeing in this situation?)

Multiple Updates.
UPDATE site_settings SET
value = '$title' WHERE setting_name = 'site_name'
value = '$keywords' WHERE setting_name = 'keywords',
value = '$description' WHERE setting_name = 'description'
How can I perform this in ONE Query?

I Have Way Too Many Inserts/updates In My Forum
when someone posts a thread theres 2 count queries
2 fetch queries
3 insert queries
and 2 update queries

so my question is, is there a way to do a sort of join query for inserts and updates?

also, has anyone got some mysql musts?
i dont see a mysql tips thread
i think there should be one

Optimizing Multiple UPDATEs
Basically what I want to do comes down to this:

UPDATE players
SET ranking = 1
WHERE id = 45;

UPDATE players
SET ranking = 2
WHERE id = 30;

UPDATE players
SET ranking = 3
WHERE id = 8;

UPDATE players
SET ranking = 4
WHERE id = 97;

UPDATE players
SET ranking = 5
WHERE id = 12;
I do have a php array containing each player ID with its (new) ranking. However looping through all the players to update their rankings (as shown above) does not seem like a scalable solution to me.

Finding Out Table Updates Since...
I want to synchronize 2 sets of tables and need to be able to tell wich updates the offline set has had since the last time it sychronized the online table. Then i want to run these queries against the online table so their both in sync again.

Is there a MySQL way to do this or will i have to store the SQL queries ran agains the offline table manualy?

Updates Not Visible To Client
I have a Java application (app A) which "polls" a MySQL database table
for records with a certain column set to 0. It runs continuously,
utilising the same commection repeatedly.

The other day, I had a support call raised, whereby there were clearly
suitable records in the database, but my application was not selecting
them.

I wrote a standalone test app (app B) to read the same table and list
the records with the column=0. Sure enough, this app listed records as
requested.

So, app A retrieved zero records whilst app B retrieved many. Both
apps *are* using the same select statement.

I think it might be something to do with the rules governing when
updates become visible to clients.

The application that writes records to the database is not mine so I
don't know all the details. It's a php webapp that seems pretty simple
and just does plain inserts into the table with no attempt to do
anything non-default wrt locking afaik.

Updates To Multiple Tables
can i insert multiple records into multiple tables using MySQL:

statement.executeUpdate("INSERT INTO members, members2, members3, members4, members5 SET
La_Na='"+lana+"', Fi_Na='"+fina+"', NRIC='"+nric+"', CCode='"+ccode+"'");

is this possible?

Safe-updates From All Clients
This is version 5.0.24a on a Windows based machine.

If safe-updates is specified in the [mysql] section, if using the MySQL command line client, safe-updates does work! However, if using another client, or a web page for example to access the database, safe-updates does not work.

Is it possible to have safe-updates work in all clients, such as through connector/ODBC?

safe-updates in the [mysqld] section does not work (the server will not even start with it there).

So the question is: Is it possible to specify safe-updates somewhere so that it is global to every connection that accesses the database no matter what the source is for that connection?

User Updates On MySQL With PHP
I am trying to do is to have a login form in html or php that when a user enters a user name and password that they are taken to a page where their editable information in a mysql database is displayed for them to change.

Mass Updates To A Table
I've got a web-based form that will allow people to update a database
that is displayed as a table. Let's say that there are several columns
across titled Red, Blue, Green... and several rows titled 01, 02, 03...
In each cell, they can fill in some simple text data.

The only way that I can think of to update the database with this
information is using two imbedded loops in Perl:

pseudocode:
for($color=1;$color<=10;++$color) {
for($number=1;$number<=10;++$number) {
update table xyz set value=$value[$color][$number]
where color=$color and number=$number;

This would mean that it would have to execute 100 update statements.
Perhaps this is no big deal and I shouldn't worry about it, but since
I'm relatively new to sql, I'm guessing that there's probably a more
streamlined method to do this.

BTW - I've seen a lot of people asking questions that were vaguely
similar to mine in this group, and they've been told to go to the php
group, even though I didn't see any php-specific code in their
questions. I don't use php and don't have it installed on my server.

Multi-Table UPDATEs
on my old server I developed an application in MySQL 4+. Now I am trying to run it on a different server that only has MySQL 3.

Previously, I used these queries:

CREATE TEMPORARY TABLE x
SELECT *, 0 AS flag
FROM user
WHERE active = 1
ORDER BY id
LIMIT 0,25

UPDATE x, othertable
SET x.flag=1
WHERE x.id = othertable.userid AND othertable.type = 8

Is there any way to do this same sort of thing - update data in one table based on data in another table in MySQL 3?

Detect And Track Updates
I'm trying a develop a connector (or synchronizer) between MySQL and OpenLDAP(very similar to Metadirectory connector). The connector has to detect any updates made to the mysql database and should replicate that in OpenLDAP. The first part of it, is how to detect changes in MySQL? I thought of the option of using a Timestamp variable as an additional column in my DB table. But it won't work for 'delete' detection. Please let me know how to do that or point me to any source regarding that. Please reply soon as this is a part of my project.

Newbie: Formating Data In Updates
My POS products table is formatted totally different from that of the Online products table. No problem, I've got FileMaker Pro and it is really good at changing and formating data how I want it. I can output the product records for my website one way, the output the records for Froogle in another format.

The problem is that it takes and extremely long time with 70k+ records. My current scenario is outgrowing itself and I need to come up with a better solution. I need to find a quicker solution, possibly one I can automate down the road.

Any advice for resources? Or examples?

Unwanted Updates Follow A Select
I update a field after a very banal query (call it the WANTED update).

After that, this crazy thing happens: if I load a page of statistics to see the newly updated values after the mentioned update query has run, it results updates by 3 numbers rather than increased just by 1.
I am absolutely, psoitively sure the WANTED update increased only by 1: I printed the result all in the page, no weird addition ensued.

If I reload again the statistics (that is, I deliver one more select), it doesn't happen.

Morale, an update, followed by a select, updates (this is not wanted!) the selected fields of more than 2 or 3 digits. If another select follows on the same table, no unwanted updates occurs.

Best Way To Issue Hundreds Of Inserts/updates?
What is the best way to execute several (hundreds of) inserts and updates?
Rather than issuing tons of individual inserts and updates, can I send the
strings to a text file and then have mysql do them all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??

Updates Invisible - Windows Only (Linux OK)
I have one Java app writing records to a database (App A) and another,
a Java web application running under Tomcat, displaying the records.
Call this App B.

When I first log into App B and get a connection, all records in the
database at that point get displayed OK.

But after that, any new records inserted by App A do not get displayed
when I refresh (running the query again).

If I log out of App B and log in again, getting a new conection, I see
all the new records.

This behaviour only occurs if I'm running on Windows. If I run the
database on my Linux box with the same application code, new records
inserted by App B are immediately visible to App B with no need to
close the connection and open a new one.