MYSQL :: Remote Database Issue

Database Design Issue
i am working on a site redesign that include 25 articles in distinct html files. The plan is to incorporate into the new design an "article of the month" feature where the monthly showcased article is taken from the existing articles. Is it possible to create a database that points to the html files instead of entering the content of each article into the database? Or is there a better way of achieving my goal? Your help is appreciated!

Wierd Database Issue (mysql)
Something has happened to my Mysql database recently which has broken some of my search functions and the such. I've been reading up on it, but I don't really understand what happened or why it changed, (I'm thinking maybe an automatic update) but anyway, I'll explain.

A new row has recently appeared in all of my tables. This row is called "Collation" and is currently set to "latin1_swedish_ci". This was never there before but it is now and all of the querys I run from inside phpMyadmin have this format now:

SELECT * FROM `some_table` WHERE `row` LIKE CONVERT( _utf8 'word match' USING latin1 ) COLLATE latin1_swedish_ci LIMIT 0 , 30

Now since this happened I have looked into what collation is and how it has to do with character encoding or something like that, but I don't understand why it just appeared and what it should be set to for an english only database. All of my search functions are failing now and I assume it's because of this collation thing.

Database Structure/Performance Issue
I'll try to be as concise as I can. But I'd like to say first, I'm a longtime lurker on this forum and it's been a big help in solving most of my issues. Thanks to everyone. Right of the bat, if anyone thinks this is the wrong forum to post this in, please let me know right away and if possible direct to a place that may be of more help.

I'm developing a small concert tour archive. Its a bit more complex than I'm going to explain, but I'll reduce all parts not necessary for this issue.

Right now I've got 2 tables. 1st is tblShow. Contains showID, showDate, venue, and openingAct. All pretty much self explanitory fields.

2nd is tblSetlist. Contains songID, orderNum, encore, showID. orderNum will tell me what order that song was played in that night; encore is a boolean value, true if it was an encore song, false if it was part of the regular set; showID will tell me what record in tblShow to link to.

For the purposes of what I've got right now, to get details of a show, this works perfectly. I get all my basic info displayed, and on the left side of the page, I run query to get all records from tblSelist that have the current showID in them and arrange them descending by orderNum. Right on! :thumbsup:

Now what I want to do is eventually, is pull stats on how many times a particular song was played, and where, then link each occurance to that particular show. The database seems inefficiant already in that many song names will be repeated throughout tblSetlist. Now the only way I get this to work now, would be to run a query getting all records that have a particular song title in it, then running a query on each showID to get the date and venue it was played in. But say a song was played 500 times, thats 501 queries on a single page :eek:

I was thinking, add 2 more fields in tbSetlist. Throw in venue and showDate as well. But then that's even more redundant data. :confused: To get to the point, what's a better way to go about doing this? In terms of structure and performance, is the second option any better?

Replication And Mysqdump Issue With Database Name
I have a database called abc-100 which seems to cause problems with
replication and importing sql files created with mysqldump.

First, if on the master we create a temporary table (because we don't
have subselects yet) it causes the slave to die with the following.

Relay log says:

DROP /*!40005 TEMPORARY */ TABLE abc-100.temp_summary

And the error log says:

ERROR: 1064 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 '-100.temp_summary

Second, on doing a mysql < dump.sql for a database restore, I have to
manually edit the dump file first and use `` around the abc-100.

So, apparently the - or the -100 is an invalid database name? If so,
why would mysql allow me to create it in the first place? Is there
any way to make this work without changing the database name?

Changing the name is not a trivial matter because I am dealing with
many databases with the same issue, 100, 101, 102 etc. I am more
concerned with replication than the dump.sql as I can always edit the
database names to be contained in ``.

Replication And Mysqdump Issue With Database Name
I have a database called abc-100 which seems to cause problems with
replication and importing sql files created with mysqldump.

First, if on the master we create a temporary table (because we don't
have subselects yet) it causes the slave to die with the following.

Relay log says:

DROP /*!40005 TEMPORARY */ TABLE abc-100.temp_summary

And the error log says:

ERROR: 1064 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 '-100.temp_summary

Second, on doing a mysql < dump.sql for a database restore, I have to
manually edit the dump file first and use `` around the abc-100.

So, apparently the - or the -100 is an invalid database name? If so,
why would mysql allow me to create it in the first place? Is there
any way to make this work without changing the database name?
Changing the name is not a trivial matter because I am dealing with
many databases with the same issue, 100, 101, 102 etc. I am more
concerned with replication than the dump.sql as I can always edit the
database names to be contained in ``.

Database Replication Binlog Issue
my client does not do a "USE dbname" and will not binlog a query like

"update in dbname.foobar set foo = 1".

I know this can be changed to work, but is there any way around this? I have the given example used in many many places.

Connecting To Remote Database
I'm using MAMP on a MacBook Pro. The only database connection I've been able to write that doesn't return an error message is on the database "test" that's installed by default:

$link = mysql_connect ("localhost" , "USERNAME", "") or die(mysql_error());
mysql_select_db ("test", $link) or die(mysql_error());
...where USERNAME is a username I created and accidentally applied to all my databases. Anyway, I assumed I could use the database "test," since I can at least connect to it.

But my queries all display error messages, even after I copy the tables they reference into the database test. Finally, I wrote a very simple query:

$Geog = mysql_fetch_assoc(mysql_query("SELECT * FROM gw_geog_gw"));
But I still get the following error message:

Quote:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Users/MyName/Sites/Geobop/Files/World.php on line 7

Does that mean I'm not even connected to the database "test"?

Connecting To A Remote Database....please Help
I'm trying to connect to a remote database, but am not sure how to do that (ie what command I should use in the Terminal). I have a php file that gives me all of the details:
<?
Database Details ----#

$host = "**.**.**.***";
$dbname = "**************";
$dbuser = "**************";
$dbpass = "**************";

Paths Used in Codeing ---#

$root_path = "/home/software/public_html/**************/";
$http_path = "http://www.webaddress.com/**************/";

?>

Triggers To A Remote Database
We make use of a PACS (Picture Archiving and Communication System) and
system to store DICOM (Digital Imaging and Communication in Medicine)
images (like CT scans, MR images etc.) in our Oncology Center. This PACS
system uses mysql as it's native database, in which it stores certain
data as date and time of the examination, it uses InnoDB tables.

We are building another application around it, which will use data from
the PACS tables and will hold additional data in a seperate database in
it's own tables.

Every study has it's own unique identifier which is stored in the PACS
database. Additional data is stored in the additional database, under
the same unique identifier to be able to keep track of the data and link
it to each other.

Now I would like to know if it is possible to add a trigger to the PACS
database which will delete the related information in the additional
database if a record in the PACS database is dropped. This trigger will
have to delete the records in the additional tables before dropping the
data in it's own database, so we can maintain a more compact database
and don't store data which we cannot link to the PACS system anymore.

Connecting To Remote Database
I'm passing in the ip address of the server, my userid and password, but I get back my userid appended with my local machine name (or maybe it's the local domain, not sure) and it fails to authenticate? Is there any way to override this appending of domain?

Remote Table In A Database
is it possible to create remote virtual tables in mysql? By that I mean
if it is possible to define a table, which actually resides on a remote
mysql server, but which can be accessed and addressed as if the table
would be on the local database.

My intention is to move one table to a remote host, but to be able to
address the table as if it was still on the same machine.

So for example:

SELECT m.header, m.body, m.from, m.to FROM users u, messages m WHERE
u.login=m.login;

would still work, even though the table "users" resides in the database
"moon" on a different host, than the table "message", which resides on
the localhost in the database "alltables".

The problem I'm trying to solve is: I have to webapplications, which
both have a table, which stores login information. Those tables have to
be combined into one and have to be put on one of the hosts only.

One of the application will access the remote user table for logins.
Since many of the SELECTS of this application contain joins like the
example above I'm looking for a way in which I do not have to modify
the selects but can move the login table to a different machine.

Remote Database Updating
I am working on an internet enabled sensor device project. I have a small microcontroller device that is connected to a sensor device. I want this device to dial the ISP and post its readings each day. I would like to know if anyone might have any suggestions on how this could be done.

Once the unit logs onto the server how might I code it to access the data area and post its reading. Also can this even be done. I mean does the microcontroller have to have a special database driver to access the remote database?

Remote Connection To Database
i have a web-hosted website and i need it to connect to my mysql database is there anyway i could set this up.

Selecting From A Remote Database
I would like to create a stored procedure that could select from a remote database, can i do that with MySQL?

Administrate A Remote Database
How is it possible to administrate a database placed on a remote server, without having access to it's desktop ?When i use the MySql adminstrator program it disables some of the setting, and says it has to bee a localhost connection.I would like to install the dataabase on a server, and afterward just use clients to administrate through.hope someone can explane the setup.

Remote MySQL Database
I have a MySQL Server in a Windows Box. How can I connect to the server
from another windows box using ODBC?

Accessing Remote MySQL Database
Is it possible to access remote MySQL databases?

I manage several sites and I will soon be building a tool that can track all of them, and it would be really convenient if I could somehow access all of the databases from one remote site.

Connect To Remote Database(MySQL)
is there some sort of setting I need to turn on where I can access the database from another server. So instead of using "localhost" I can use the actual server IP address.

And just for future reference, is this how you set up a application server connecting to a database server?

Database Synchronisation On Two Remote Sites
I am considering the possibility of trying to keep a user table synchronised on two different sites. This is not something i've done before so i'm looking for any tips that might help me on my way.

Basically I was thinking of:

- scheduling php script to connect to both databases via cron to update the table at regular intervals
- generating an xml feed and doing the same thing

Both methods would potentially involve storing a pointer to only read and write the new rows, although perhaps I could use DATETIME to also copy accounts that have been updated since last run.

Probably biggest concern is:
- security... operating in a way that the data is private. The most sensitive data is hashed passwords and postal addresses, I don't think it would be appropriate to not somehow protect this info during transfers. I'm not sure whether SSL is an option (should be) but possibly I was thinking of creating some kind of token system.

- bandwidth. both sites should be fine resource wise but due to my lack of experience in these matters I don't really know what the performance hit will be, and I would like to run the script very often

As far as I know alot of big sites (for example hotel booking engines) use xml in the background to share info but I really have no idea how it all works.

Query Database From Remote Location.
I have a database sitting on my db server, which is part of my office network (which has a domain controller). Access to the outside world is via a router, which is well locked down.

Currently, remote users access the database front end via Terminal Services but this is only a method of accessing an application stored and running on the Database Server.
What I want to do is allow access to the database from a server, which is located in another building (another part of the country). This is essentially an inter/intranet server.

What is the best way for me to get this external server to be able to query my database server?

Downloading A Database From Remote Server Using Ssh?
I need to copy a database from a remote server where I have ssh access to my local computer. Can anyone tell me how to do this?

I was thinking something like this:

- login to ssh
$mysql_admin database_name | local_pc (very simplified)

Connect To Database From A Remote Source
I own a website and plan on running multiple message boards on it...the problem is that I would like a database per board...however my host only gives 1 database....unless I want to pay an extaordinary amount of money for more.

So I have used my PC as a webserver before without any problems and in fact ran a pretty big board off it with php, mysql, phpmyadmin installed.

So my thought was to use a couple of databases off my PC to help run the new boards.

I have created the database...but I'm having trouble with the board connecting.

I'm sure that it's some kind of permission that needs to be inserted for the remote host to connect to my databases.

Configuring A Database On A Remote Server
i have an aplication on .net for web, with mysql db, in my local server i have no problems, but recently i upload the aplication into a bougt host; when i open the page on the internet explorer or firefox, the page loads well, except for one simple grid. in another part of the aplication i try to insert a record, the app gave the me no error message, but when i check the table, there's nothing new.

i don't if i configuring something wrone, on the web.config. and the worse of all if that i dont get any error?

US State, County, City, Zip Code Database Issue
I have a database of all the US state,city,zip code, area code. I need to program a script where it will list all the cities, counties or zips for each state. The problem I have is the databse I have is listed by zipcode.

ex. 91201 Glendale CA
91202 Burbank, CA
91203 Pasaden, CA

How can I put all the zips and cities to each State.
Thank You

P.S I will be glad to share it with anyone who needs it.

VB6 Controls Not Populating From Remote MySQL Database
I have created a VB6 app that connects to a web hosted MySQL database. I have created the connection string in my app and used the Port number required by the Web Host. All my data comes back to my VB forms when I run my app from my Win XP development machine, but here is my problem...

I have installed the app using InnoSetup wizard on a Win2K pc at SP4, and it connects successfully to the databases, but my textboxes, labels, comboboxes and datagrids show no data. For example, one of my forms has textboxes and a datagrid, and a statusbar. The textboxes and datagrid are bound to ADO controls which pass SQL statements to MySQL to create the recordset. The statusbar shows the RecordCount from the resulting recordset. The statusbar shows the correct recordset count, but the textboxes and datagrid are empty. This doesn't happen on my development machine.

In my installer, I have installed all the VB Runtime files to the target machines WindowsSystem32 directory, and have installed the OCX files the app uses to the application's directory.

I am at a complete loss to know why I can connect to the database, successfully open a recordset but not populate my forms' controls with the resulting data.

Method - Remote Databases To Central Database
I am looking for the best approach to move data from four remote MySQL database applications back to a central MySQL database in near real time. Each remote database has a low number of transactions up to 15 per second of large data sets (12,000 items). The central database will be replicated across two servers for redundancy.

Database Dumps From Remote Server Via Proxy
How to copy a MYSQL database placed on a remote server using 'mysqldump' where there is a proxy server involved? This proxy server requires user name and password. I have Win XP.

Connecting To Remote Database Via MySQL Administrator
I have used Sql Server and other databases extensively. I have the following questions.

1. I need to connect / manage a SQL database located on a remote web server runnin Linux. My workstation is running Windows XP. I understand that this is what MySQL Administrator is for. Is this correct?

2. Can I run both MySQL and SQL Server on my Windows XP machine?

3. What one book would be the most helpful to help me get a basic table setup?

MySQL Slow To Connect To Remote Database
I was running a PHP/MySQL web site with the database on the local web server. I needed to move the database off the webserver (located in the firewall DMZ) and onto its own machine (behind the firewall on our private network)

Now when I run my web site it goes really slow. I ran mysql from the command line on the web server and connected through to the database machine behind the firewall using
webserver:/# mysql -h 192.168.1.7 -u www-data -p

After I enter the password, it takes around 5-7s before I see the mysql> prompt.

Is mysql trying to do reverse dns on the IP address and timing out? I added the machine details to /etc/hosts so it would be recognized but no improvement. Or do I have something wrong with my mysql configuration?

Remote Connection To Mysql Database Using JDBC?
I am trying to set up OpenOffice Base to connect to a remote os x server with mysql on it.

It has the default mySQL JDBC driver class set to com.mysql.jdbc.Driver

It also asks for server URL, should this be the ip address?
Port number? Default is set to 3306.

When I set the class it says that the JDBC driver could not be loaded.

Copy Records From Tables To Remote Database
there ara two mysql server server1 and server2. server1 ip adress is 192.168.1.2 and
server2 ip adress is 192.168.1.3. both servers have same database. i want to copy records server1 to server2 have can i do this?

Connect Remote JSP Files With Local MySQL Database?
I use dreamweaver 8.0.
I have my jsp files on the remote server Z:.
I have my local computer 'A' connected to this server.
I have my MySQL database on my 'A' computer.
I want to know how to connect the remote JSP files with the database?
Currently I am using jdbc.odbc drivers on 'A' computer.
But my server computer has Linux and uses org.gjt.mm.mysql drivers.
Which drivers and Where should be the drivers?

Connect Remote JSP Files With Local MySQL Database?
I use dreamweaver 8.0.
I have my jsp files on the remote server.
I have my local computer connected to this server.
I have my MySQL database on my computer.
I want to know how to connect the remote JSP files with the database?
Where should be the ODBC-drivers?

Forcing Remote Reload Of PHP Page After Database Change
I'm developing a mysql database and have users access the data via homemade PHP pages that query data. When the user(s) open the PHP page or query on it, it fetches the data from the database and then refreshes the page showing the new data.

If something in the database changes, like if a user adjusts some value, is it possible for mySql to send out some sort of "autorefresh" signal/trigger to all open connections to the database, which could be used to automatically refresh the user(s) PHP page, thus giving them the most up to date information without them having to manually click refresh?

How Do I Enable Remote Access To MySQL Database Server
I want to connect to my mysql server which is seperate on an external server but i dont know how to enable remote access to MySQL database. The mysql server is on xp and running mysql 5.

Backup/Restore Mysql Database From Remote Server To Local Pc
Im trying to download mysql database "dbproducts1" which is residing in my webhost's server (Linux). From telnet i backed up using "mysqldump -u -p dbproducts1 > dbproducts1.sql"

Then i downloaded this file via ftp to my local pc(WinXP). Now on my local pc to restore database i went to command prompt and i did following:

mysql -u -p dbproducts1 < dbproducts1.sql

I got following error:

ERROR 1064 (42000) at line 380: 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 '-workshop (
title varchar(250) NOT NULL default '',
decription longtext NOT ' at line 1

I opened the phpmyadmin and found that dbproducts1 database was restored but with only few tables i.e. not all tables in the db was restored.

Can somebody please help me to backup/restore this database. I checked the mysql server version and found following:

server version: 5.0.24a-community in my local winxp pc
server version: 3.23.36 in my webhost's server (Linux)

MySQL Remote Backups - No Access To FTP Or Remote Command Line
I have a mySQL database located on a remote host's server. I would
like to schedule a task on my local Windows computer to retrieve a
backup/dump of this remote database.

I have contacted my host, and they indicated that the server is only
open to the Internet via mySQL. Therefore, even if I did have access
to that server's command line (which I don't) to create dumps via
mysqldump, I would not be able to retrieve via FTP.

Does anybody have any ideas as to how I can generate backup files from
my local computer through a local command-line interface? I have done
it through a GUI (DBTools), but I would like to have it done via DOS
batch, so that I can schedule it. Does anyone know of any mysql backup
GUIs with command line switches? Or can somebody recommend an
alternate means of getting a local backup?

How Can I "see" A Table In A Database On A Remote MySQL Server After Creating It
I used the following SQL to create a new table in a database on
a remote MySQL server by copying one already there. I know the
table exists SOMEWHERE in cyberspace. I can read its data, write
to it, delete from it. But I cannot see it. The only way I know it
exists is by running this SQL from Access 97 pass through query:

SELECT ALL new_tbl.name FROM new_tbl

The database resides on a MySQL server that was created with
a single table (named test) in it for testing purposes. I ran the
following SQL to create another copy of the table in the same
database named: new_tbl

CREATE TABLE new_tbl SELECT * FROM test;

I cannot see the new table in the Access 97 database window
under the Tables Tab. Anybody know how to overcome this?

Its a severe drawback to programming efforts not to KNOW what
tables are in your database.

SQL Issue
Had a question I can't seem to solve.

Here is the line I am having trouble with.
$query2 = mysql_query("UPDATE Users SET Totref += Ƈ' WHERE id = '$referral'") or die(mysql_error());

The exact part is the: SET Totref += Ƈ'

I think the problem is that I can not use += in that spot. How could I increase that value by 1 in a way that works?

Max(id) Issue
I have a table that is set up as follows

id auto increment, reference varchar(20), value varchar(20)
1,passed,3
2,failed,888
3,total,10
4,failed,99
5,passed,900
6,total,3

I am looking to get the last value inserted into the table for each reference value. the result should be:

2,failed,88
4,passed,99
3,total,10

WAMP Issue Please Help!
I installed wamp onto my laptop (it's new) and everything went fine except that when I try and access the phpmyadmin from the control panel I get the error message 'php tried to connect to the mysql server, and the server rejected the connection. You should check the host, username password in
config.inc.php

then it tells me

#1045 - access denied for user 'gokeeffe' '@' 'localhost' (using password YES)

This is not the password that I set and I checked the config file and everthing seems to be inorder. Does anyone know what is wrong.

Why is mysql + phpadmin so difficult to install?

Issue With JOIN And AVG
I'm having an issue with trying to get the average of a few rows in this query.

Here's my query:

SQL
SELECT g.name, g.thumbnail, g.plays, g.id, AVG(r.amount) AS game_ratingFROM `games` g LEFT JOIN `ratings` r ON r.game_id = g.id GROUP BY r.game_id ORDER BY RAND() LIMIT 0,1

There's the `games` table that I'm initially pulling from and then there's a separate `ratings` table that holds all the ratings for games. Ultimately I'm trying to pull the averages for the game.

The problem is that when I output game_rating it outputs 鰿.9999999' regardless of what the actual average is (should be between 0-10).

Order By Issue
I am running a query that has to search through 4 different tables by taking in a list of contact ids. This list of IDs is then used to search the orders table (matching either invoicee_id or recipient_id), if there is a match I want to take the order ID from that table, then search in the order_product table. From the order_product table if there are entries for the order_id we grabbed I want to get the subject_id of the order products. This I then use to search in the subject table to grab the name of the subject.

So essentially I want to grab a list of all the subjects that a certain user has ordered products for. But what I want to have is an ordered array at the end of it, where the subjects associated to a user ID are ordered by how many products of that subject type have been ordered. So if the user has ordered 5 Art products, 3 english and 10 Maths, I want the row in the result to be Maths, Art, English.

I have written the query which succesfully grabs the subjects ordered by a list of IDs and arranges them as so:

subjects contact_id
Art, English, Maths 9
French 11
but it will have art in the list 5 times, english 3 and maths 10.. Obviously if I use a distinct it will only display them once each, but I want them ordered by frequency if possible.

Here is my query at the moment:

SELECT
GROUP_CONCAT (s.subject_name) as subjects, c.contact_id as contact_id
FROM
orders as o
INNER JOIN
order_product as op
ON
(op.order_id = o.order_id)
INNER JOIN
product as p
ON
(p.product_id = op.product_id)
INNER JOIN
subject as s
ON
(s.subject_id = p.subject_id)
INNER JOIN
contact as c
ON
(c.contact_id = o.invoicee_id OR c.contact_id = o.recipient_id)
WHERE
find_in_set(o.invoicee_id, Ə,11')
OR
find_in_set(o.recipient_id, Ə,11')

GROUP BY
c.contact_id
note the 9,11 are just there for testing purposes, eventually I will be passing in an entire list of IDs.

AUTO_INCREMENT Issue
I am still a rather new MySQL user. The auto_increment property was learned from a tutorial and it has stuck with me ever since for generating distinctive IDs. However, I have only recently discovered that when I delete a row from the existing table, the new auto_increment will not go from the last ID left but rather keeps going from where the previous record was. For example, I delete row 5 with ID 5, the next one I create would be row 5 with ID 6 instead of ID 5 as intended. How should I go about solving this problem? Thx.

Issue With LIMIT
PHP

SELECT item.itemId, item.itemName, 2 * SUM(bans.bansForAgainst) -
COUNT(bans.bansForAgainst)
AS count, bans.bansForAgainst, bans.bansItemId
FROM item, bans
WHERE item.itemId = bans.bansItemId
GROUP BY bans.bansItemId
ORDER BY item.itemId DESC
LIMIT 0,5

But it's not limiting the output...at all.

Autonumber Issue
Once the database is incremented to 127 entries the database no longer increments. As a result I get a 127 error.

Replication Issue?
My slave has been working fine up until now. Here is the error message.

030730 11:32:28 Slave: connected to master 'repl@xxxx.xxx.org:3306',
replication started in log 'SQLServer1-bin.034' at position 77571524
ERROR: 1062 Duplicate entry '120816' for key 1
030730 11:32:28 Slave: error running query 'INSERT INTO Assignments (
..........

Key 1 is CaseID

Database changed
mysql> select CaseID from Assignments Order by CaseID DESC Limit 3;
+--------+
| CaseID |
+--------+
| 120815 |
| 120814 |
| 120813 |
+--------+
3 rows in set (0.02 sec)

mysql> DELETE FROM Assignments WHERE CaseID = 120816;
Query OK, 0 rows affected (0.04 sec)

If I try to restart the slave I get the error all over again.