MYSQL :: Quick & Easy MySQL Sync?

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

MySQL Sync
i have 2 mysql tables on 2 different servers with the same table structure - i want to be able to sync up some tables within the databases.
I am getting very stuck with this and in need of some help.
I keep reading about cron jobs and this sounds like its something i need to get set up, but then i need a script to run.

Mysql Table Sync
Can a MySQL table sync with a MSSQL table?

Sync Mysql Databases Over The Web
I would like to have two mysql instances syncing with eachother over
https. So I dont want to do any tunneling or have any ports but 443
open.

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

Easy Way To Enter 20000 Records To Mysql Db
I'm looking for the easy way to enter a lot of information to Mysql Db for me the best way is like in access you just paste the buffer (from excel) to the table you selected and it does it.

Bdb-no-sync
What are the implications of setting the "--bdb-no-sync" flag when
using the BDB storage engine. There isn't alot of documentation on
this. I have searched google and docs. If there is a resource that
someone can point to, it would be helpful.

Does this mean that after every transaction, sync will not be called?

When will sync be called then?

Will setting this flag lead to "ACI" semantics, ie no durability? If
so, when will transactions become durable?

Will setting this flag increase the likelyhood of DB corruption? As
happens in Postgresql with fsync off.

I know in BDB (non MySQL storage engine), setting "DB_TXN_NOSYNC" on a
BDB transaction leads to ACI semantics. It is then up to the app to
call DB->sync when it wants durability. How does this map to the
BDB/MSQL storage engine?

Sync 2 Databases
I've recently ran into issues with my web hosting server (not in house) so I am wanting to set up a local machine with an exact replica of information from the hosting server. Is there any function or "simple" way to sync 2 databases on different machines - outside of manually updating them? I have to perform this operation once or twice an hour so manually updating the information is gonna get bothersome real quick.

Two Tables Out Of Sync
I'm new to databases and am experimenting with mysql.

I have a clients table and a sales table in a mysql database.
Client_id is the primary key in clients and a secondary key in sales.

The only client_id's that show up in the clients table are client_id's
starting at 28 (they autoincrement from there).1-27 were old ones
that I used in testing.I added a new client and clients shows the new
client_id as 188 which makes sense.I added a new sale to the sales
table for this client.

But in sales the client_id is set to '1'. Why isn't it 188?
Probably '1' was the original test client_id and had the same data.
The database seems to have remnants left over from past deleted
records from the clients table.

Does anyone know what's going on?Is there something like a 'pack' or
're-sync' command in mysql?

Hosted Sync
I have 2 mysql database, the first is mysql from hosting company and the second is mysql hosted on my computer.
What i have to do to syncronizing both database.

Sync Databases
I have a database on a production server, and a seperate one on a development server. Up until now, I have been doing a mysqldump on the DB on the production server, SFTPing the file to the development server, and then running mysql dbName < filename.sql
is there an easier way to do this? I guess I could use a shell script, perhaps including some expect for the remote server work. But is there anything either builtin to MySQL or as a simple third-party tool which, without creating a temporary file, copies the database from one server to another?

Incremental Sync Of Two Db's
I have two db's. One on a local server, one on a remote server. Both
are running MySQL 4.0. The remote is a production server that I run
mysqldump on nightly to backup the prod data. The local server db was
created with one of the production dump files, but I'd like to
automatically update my local server with the production data on a
nightly basis.

How can I sync them up? Keep in mind that it's a one way street,
meaning, I cannot make remote connections to the remote db from the
local db. I can however make connections from the remote db to my
local db.

Commands Out Of Sync Administrator
When I try to create a table using MySQL Administrator I receive the following error:
"MySQL error number 2014
Commands out of sync; you can't run this command now"

Most of the other posts refer to running multiple queries at the same time, but this a development resource so no other queries are being run, unless there is something going on in the background. I'm a basic/beginning user.

I know how to run queries and update data, but as far as advanced/custom installations I'm out of my element. This is a recent installation of a XAMPP package on a Windows XP platform, which included Apache 2.0, PHP 5.1.6, MySQL 5.0.24a. I am using MySQL Administrator 1.2.4.

Commands Out Of Sync Message
I'm having a problem wherein I unexpectedly receive a "Commands out of sync" error message. I'm using the C API for MySQL 4.0.21 under Linux. The basic flow of my program looks something like this (parentheses indicate the function that the commands are taking place in): Code:

How To Track Error - Commands Out Of Sync
We are currently running about 50 databases on MySQL 4.0.14. Occasionally
our users will get the error:

Couldn't select database. Message: Commands out of sync; You can't run this
command now Message Number: 2014

When this happens to one customer, it affects them all. We end up having to
restart the MySQL services to clean things up. Our goal is to eliminate
whatever is causing this, but cannot determine the cause.

I have checked the online help on MySQL and we are not calling the functions
normally associated with the error.

Error 2014 - Commands Out Of Sync
I am seeing this error message when running my php scripts, and have no idea what it means!

The error reporting just "says error 2014, "Commands out of sync", "you can't run this command now".
using php 4.3 and Mysql 4.0.20d.

PDA Client Or Access Sync Tool
I have a software application with an Access front end and an Access backend
to house the data. Recently I converted the backend to MySQL. The Access
frontend works fine connecting to the MySQL backend.

The issue I have now is that I also have a syncronized database on a PDA. I
was using ActiveSync and Pocket Access to create the database on the PDA and
keep it syncronized with my PC (backend) database. I'm using eVB programs
to retrieve and update the data on the PDA, then all I have to do is sync
the PDA with ActiveSync and it will update my Access Backend database
accordingly and vise-versa.

If that is not an option, on the PC side, I could keep a
replicated/synchronized Access database using a third party app (like
Access2MySQL Sync). I would still keep the MySQL backend, but also have a
replicated/syncronized Access database strictly for use with my PDA. This
would allow me to keep my ActiveSync & Pocket Access synchronization
process. My problem is I can't find a third party app that actually works
well enough to put into a production environment.

PDA Client Or Access Sync Tool?
I have a software application with an Access front end and an Access backend
to house the data. Recently I converted the backend to MySQL. The Access
frontend works fine connecting to the MySQL backend.

The issue I have now is that I also have a syncronized database on a PDA. I
was using ActiveSync and Pocket Access to create the database on the PDA and
keep it syncronized with my PC (backend) database. I'm using eVB programs
to retrieve and update the data on the PDA, then all I have to do is sync
the PDA with ActiveSync and it will update my Access Backend database
accordingly and vise-versa.

I know MySQL doesn't have a PDA client, but I was wondering if anyone knew
of a third party application that I might be able to use to create and sync
a database on the PDA with the MySQL database on the PC.

If that is not an option, on the PC side, I could keep a
replicated/synchronized Access database using a third party app (like
Access2MySQL Sync). I would still keep the MySQL backend, but also have a
replicated/syncronized Access database strictly for use with my PDA. This
would allow me to keep my ActiveSync & Pocket Access synchronization
process. My problem is I can't find a third party app that actually works
well enough to put into a production environment. Does anyone have any
recommendations or suggestions?

Quick Join
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys.

Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id

How do I get the loser name?

Quick Join
I have a quick SQL question:
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id
How do I get the loser name?

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

Quick COUNT()
The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

Sync Databases For Replication Without Stopping The Master
Teh replication breaks every couple of months and the problem is that the server can't be stopped since it's a high volume transactions application. So is it possible to set the replication without stopping the master server? If yes, then how? If not, then what shall be done?

Help With A (probably) Easy Query
I am trying to write a query to run through phpmyadmin. What I have is this:

In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

Easy Query...probably
I've got three tables:

- events
- tasks
- docs

Events are made up of one or more tasks and tasks may or may not have docs.

What I need to do is get all the events with associated tasks and docs.

Quick Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value?

No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this.

UPDATE articles
SET published = ABS(published - 1)
WHERE id = x

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am querying from a table containing more than 40,000 records. Earlier =
when the records were 10,000 it was taking 9 sec and now after 1 year =
and 40,000 records its taking 30 sec. Code is the same.=20

I am pretty sure that it has something to do with database only. No body =
can think of spending 30 secs for retrieving values from tables unless =
it's very huge in the sense of millions of records. =20

I don know whether I have to modify my database or do some sort of =
restructuring or reindexing so as to make it fast enough.

Is there some methods or optmization which can be applied to this =
database which hasn't been touched since design to enhance the spped.=20

If there are some quick ones but valued alottttttt please let me know.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

Easy Way To Delete Duplicates?
I had 2 membership db's. One had all the members (65K) db2 had all the members that have registered on the website. I am wanting to consolidate the two.

Basically i took both, and merged them together into their own db. now the membership list is about 80k. 20k or so of that are duplicates. My idea was to export the thing as .xls and remove the duplicates via Microsoft Excel. However, Excel has a limit of 65Kish rows. so i can't really go about that.

Select Row From Table, EASY
I'm drawing a blank and can't find it my Sams 24 hour book.

I've got a player name and stats in a DB so a row in the database would look like
Chris Brown 300 1200 5 40 325 2

I need to select(display PHP) Chris Brown and everything associated with him.

I had this all setup and haven't thought of it in years and now....geesh I just feel like an idiot as nothing I've tried works and I know it's connect to DB then one line of code, a select statement.

Pros/cons (easy ?)
Say you have a double opt in mailing list, of which the subcriber list is
store in the db. Im still somewhat of a newb, so bear with me... are there
any pros/cons as to keeping the 'unverified' subscribers in a different
table, aside from organization?
I did just that, where, when they subscribe, they are put in a temp table,
and them moved to the real table once verified (and the temp entry deleted).
At the time, I did this thinking organization of data... but as an
afterthought, am thinking I just did a lot of unecessary coding... ... and
am thinking just an y/n field for verified or not in the primary table would
suffice.

It's not actually a mailing list, just thought that'd be the easiest
example.... the main table holds about 1000 entries, and there about 10-20
temps/days. The only con to my setup I can think of (again, as an
afterthought) is deleting the unverified entries after a week and the extra
resources of going through the entire db searching for the week-old
unverifieds, but with the size of my db, I dont even think that is a con in
my case.

Did I waste a lil time there w/ the extra table?

Create User Easy?
Is there an easy way I can just create a MySQL User... and give him the privledges to only do things to databases that he from now on creates..?

Or is it gonna be every time I create a database I would have to give him privledge to that database? What type of route would I have to go with that...

Also when his user connects to the MySQL Server is there a way I can do a SHOW DATABASES and show only the databases that he has created? He needs to also be able to Create/DELETE/APPEND/UPDATE any database that he creates.

What's the easiset way about going around this route? Am I going to have to Grant him Permissions on every database that he creates? Does the root account have to grant the permissions?

I Think It's An Easy To Fix Sql Syntax Problem
can anybody help me :

@mysql_query("INSERT INTO leden WHERE gebruikersnaam = ".lekubb($_POST['gebruikersnaam'])." (status) VALUES (Global Admin)") or die(mysql_error());

Ik get this error :
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE gebruikersnaam = t (status) VALUES (Global Admin)' at lin

You can see the script online at http://www.webmasterhelp.be/systeem/testje.php
When you fill in something and press enter you'll see the error.
If you need more info or something you can mail me at arne_dejongh2@hotmail.com or post something here.

Easy Collation Changing.
A while ago, I messed around with collations etc, without understanding the rammifications.Ended up with some tables swedish, some general and others, but all were under Latin1. After some time a few queries started breaking. Anyway, I played and tinkered and eventually managed to fix.
The problem was that I couldn't go and Alter thousands of columns manually, so a PHP script was created, and works fine.
But for the sake of knowledge, is there a way to change the Character Set and Collation stuff of an entire database(s) quickly and easily using SQL?

Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Sql Virgin. An Easy Question For U Experts Im Sure
Im a database virgin, i would like to know more but find it quite confusing, although im sure its not!

Im setting up a cms and have installed wamp on my machine and its working. Now the cms has asked me to create a sql database so ive gone to php my admin and put a name in the creat field and created it. Its showing in the left field of phpmy admin.

Now the cms is asking for the name of mysql login and mysql passwoord!

Where the hell do i find that? ive never created one!

Easy Question Re: Type Sizes
Just wondered, what exactly does:
int(11) do
does that mean you could store UP to an 11 digit number in the field?
ie. 01234567890
with the highest possible number:
99999999999

and does the same apply to tinyint and all the other int types?

if that is the case, why would one chose tinyint(1) over int(1)?

and also I guess the same goes with text/varchar types.

Easy Way To Get Time Zone Offset
I need to get the standard offset value from GMT for any given time zone name in the mysql.time_zone_name table. Is there an way in MySQL to do this?

For example:
I have 'US/Eastern' time zone. I know that the value I'm looking for is -5. How can I get this out of MySQL. I don't care about DST offset, just standard time offset.

Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID.
Table 2 is a chat log. Colums: From, To

I'd like to find out who is NOT chatting.
So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2

So far I'm using two selects, but this doesn't seem right.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

How Do I Employ This Easy Access Query Technic
I Access it is very easy to build queries off of queries built of of even more queries.
with only a few certain limitations it works quite well and is easy in the Access Graphic interface.

I am completely self taught in Access so while I can build an entire enterprize application from scratch, I am a little sketchy on some of the technical terms. I think what I am doing in Access is called "layered" queries.

HOWEVER, whatever you call it, I am not so certain I'll be able to enjoy the same functinality with the MySQL database I have recently be asigned to admin.

First of all, the database, while in MySQL is accessed (for me) only through an interface in the UBB Forums SQL Commands control panel.

Secondly, while I can write and save standard text SQL queries, HOWEVER the saved queries are not necessarily saved with a certain name and do not appear to be able to be referenced in other queries. The saved queries are in a seperate table. As a side note, I can tell that the previous admin knew, or did even even less than I becasue the index key for my very first saved query was 1.

sigh....

Anyway, becasue the site owner has not added my email address to the contact info for the site's web host, I do not have access to the main MySQL control panel in the home folder. I only have access to the forum tables through the UBB Threads control panel.

I am considering attempting to make some tables to use in place of the layered queries, however I am uncertain if they will be in the same folder and thus if I'll actually be able to access them through the UBB Threads control panel.

However, I am wondering if anybody can share with me anything I might be missing as to how I can, or even IF I can layer queries in MySQL.

An example of what I want to do is this:

Desired result is to know the count of posts by hour of the day, filtered by Year and by month, BUT ONLY GROUPED BY HOUR OF DAY.

In Access, I could easily build a first query that filters the posts table to the year and month(s) desired, and then build my 'Group By Hour/Count of Posts' query off of it.

Short of actually creating a new table with the filtered data, can anybody share with me how I might be able to do the same thing in MySQL?

Quick & Easy MySQL Sync?

Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?