MYSQL :: Step By Step Approach To Manage Dual-master Set Up

Step By Step Procedure To Install MySQL
I tried to install mysql( my OS IS WIN 2000) but when I try to connect it thro command prompt I get a message " access denied" . So I have uninstalled the whole mysql. Could u plsss give me a step by step instruction as to how I need to install and get into the mysql db and start coding?

Step By Step
So now you know how new I am. However, I am pretty good at hacking up PHP.
I installed a website - now I want to add a mod and I need to add a table to my database.
This is the table:
1. Run the Following SQL query on your SQL DB:

CREATE TABLE `probid_gbasemod_setts` (
`id` int(20) NOT NULL auto_increment,
`ftp_server` varchar(100) NOT NULL default '',
`ftp_login_name` varchar(100) NOT NULL default '',
`ftp_password` varchar(100) NOT NULL default '',
`ftp_filename` varchar(100) NOT NULL default '',
`ftp_path` varchar(255) NOT NULL default '',
`ftp_location` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

INSERT INTO `probid_gbasemod_setts` VALUES (1, 'Server Name Here', 'usernamehere', 'passwordhere', 'filenamehere.txt', 'Your path to filename', 'N');

Is there a place where I can go to learn the very basics of MySQL? This can't be to difficult. I assume that somewhere there has to be a MySQL for the very beginners website. Or maybe a MySQL for Dummies website with step by step instructions and maybe pictures.

UPDATE Step
I need to update a field (date) with a different, random date for each
record.
loop {

generate random date;
update one record with new date;
goto new record

}

Problem. Table does not have a unique id field

table (item, date)

peaches - 2003-05-13
pears - 2003-11-24
peaches - 2004-01-04

the table is a given... meaning I have to work with it the way it is.

Error In Step 5 Of Installation
I'm trying to add an application(Image Gallery) to my Web that requires a php install Program to run,check and populate a Database. It goes through a test prior to install and Says everything is A OK, and creates the Tables in the Database. After that it populates 1 table (role) just fine but when it trys to populate the second table (.SQL_ABM_TBL.) it fails(with No explaination).

The .SQL_ABM_TBL. is defined in a config.php file as the following shows:

define('SQL_SERVER', "localhost");
define('SQL_ABM_TBL', "albums");
define('SQL_IMG_TBL', "images");

I'm using MySQl 5.0.45 and php 5.x. I used the MySQL admin tools and had it check the tables and it says everything is fine. The code I'm including is from the Install.php file, where it fails is inserting data into SQL_ABM_TBL, Can someone pls have a look at this and see if they can tell me whats wrong here.....

Multiple-step OLE DB Error
I am trying to insert/update a longtext field. I use ADODB. recordset object. I am receiving the error message below. Your help will be very much appreciated.

Error Type:
Provider (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The code is as below.
sqlproductexist = "Select * From products Where product_id = " & product_id
rsdata.CursorLocation = 1
rsdata.Open sqlproductexist, datapath, 3, 3
rsdata("short_description") = short_description '//the variable contains a string value - type of the database field short_description is LONGEXT
rsdata.Update
rsdata.Close

Multiple-step Operation
Delphi Application use ADOConnection and ODBC to connect to MySQL database. Since I upgrade MySQL 4.1.13 to 5.0.15 following error occurs when try to open several (but not all!?) ADOTables and ADOQueries.
"Multiple-step operation generated errors. Check each status value."

Dual Master And Dual Slaves
We are in the process of setting up a set of high volume MySQL servers. and the configuration that we have chosen is a Dual master replication model, each with its own slave. ie. a total of 4 computers with this configuration

C<-----A<----->B----->D

We have chosen this so that if one master fails we can easily bring the slave forward to take its place.

However, we do have a question with bringing a Slave up to replace a dead master (ie. if A dies we bring C up to the dual master configuration with B)

Is there any way to bring C up as a replacement master WITHOUT stopping mysql or locking the tables?

Multiple-Step ERROR (Dynamic Cursor??)
I have been making the change from mssql to mysql in order to save myself Â£10,000 for the mssql evaluation edition.

Everything seemed to be going ok untill the last hurdle when I recieved this error:

<ErrorMessages>
<ErrorCode>-2147217887</ErrorCode>
<ErrorDescription>Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.</ErrorDescription>
</ErrorMessages>

This occurs in a dll which processes using xml (hence the strange reply) However I think the problem arrises when using dynamic cursors in statements like this:

select * from table where colum='" & var & "' and colum2='" & var2 & "'", con, adOpenDynamic, adLockOptimistic.

UPDATE Step Thru Every Record W/out Unique Id Field
How can I update every record of a given table one by one. I need to update a field (date) with a different, random date for each record.

loop {

generate random date;
update one record with new date;
goto new record

}

Problem. Table does not have a unique id field

table (item, date)

peaches - 2003-05-13
pears - 2003-11-24
peaches - 2004-01-04

the table is a given... meaning I have to work with it the way it is.

Dual Master Follow-up
I posted a much longer winded question in the replication forum a few days ago, but after much research I realized I just need one piece of information that I seem to be reading conflicting opinions regarding. Is a dual master configuration considered bad form for MySQL Network users? I'm only replicating ONE dynamic database, and there are NO auto-increment fields.

I will have a max of 40 concurrent users (20 average), so I don't think I'll have a big problem with anything becoming out of sync. Should I avoid writing on both machines or is the autoincrement issue the only reason why writing to both masters would cause a problem?

Dual-master Replication
I would like to set up a dual-master replication setup for my MySQL
database
(running on RedHat 9). Could someone please point me to appropriate
website that details this setup process and management as well (i.e.
what should I do if one of the masters goes down?).

Also, are there any solutions that allow auto-increment fields in this
kind of setup?

Dual Master Replication In MySQL 4.1
I have been charged with the task of setting up dual master replication with our company's MySQL servers. I know that their are inherent auto_increment problems with this set up, and the mechanisms to fix this problem weren't introduced until version 5.0.

Can anyone give me some tips, pointers, or advice for safely setting up this replication topology in 4.1? Are there any steps I can take to make sure there are no auto_increment issues? Has anyone had experience setting this up?

Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work.

My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?

Master/Master Replication Problems
I was wondering whether someone could help me.

I have inherited set of replicated servers from a guy I used to work with, basic set up is:

Master1/Slave2 - Server ID 1
Master2/Slave1 - Server ID 2

When I looked at the logs it looks as though Server ID 2 is well out of step. Having looked at the slave status it appears that that Slave_SQL_Running state is set to No and a particular piece of SQL is causing the problem. Having ran the SQL on the particular server it has now problem at all.

Now my understanding of this type of set up is that Server ID 1 writes directly to Server ID 2, then Server ID 1 is in turn replicated to.

But for the life in me, I cannot understand why this piece of SQL is causing the problem. Can anyone offer me any ideas on how I can best get to the bottom of this, I'm really struggling with this. There was me thinking replication just worked!

Is there an ability to get MySQL to log all queries it is running, so I can get to the bottom of all queries currently being executed?

Master / Master Asynchronous Replication
Currently we have a web based application that is mostly reads (4:1
r/w). It is using a single MySQL database server. Is there any way to
have two database servers in a master/master configuration such that
writes to either database server are replicated to eachother. Basically
even though we have a 4:1 ration of read/write the writes happen often
enought that when the database goes down the app stops working. I know
how to get this working in Oracle (insert big laugh here) but Oracle is
cost prohibitive. Any pointers?

A Better Approach
Another future problem might be multiple attachments per post. (I'd end up with even more queries?)

This is what I'm using now:

1. Query the topics for the page.

SELECT * FROM ib_topics WHERE board_id = 123 ORDER BY topic_bumptime DESC, topic_id DESC LIMIT 0, 10

2. For each topic, obtain the first and last 3 posts. Join the attachment table, if the post has an attachment. (i.o.w. this query is used multiple times, each time with a different topic_id)

( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id ASC LIMIT 1)
UNION DISTINCT
( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id DESC LIMIT 3)

Is there a better approach to this?

Approach In MySQL
What is the better approach in MySQL from a performance point of view?
I have approx 1000 stock market fond titles that I want to insert in
MySQL on daily basis and keep them in there for some years, I'll then
use the data for presentations, analysis,… the data I save is the
name, the date, the lowest, highest and last value and the volume.
The analysis will be run on all of them on daily/weekly basis.

implementation 1:
-----------------------
I create one (1) huge table with the fields date | name | lowest |
highest | last | volume

implementation 2:
-----------------------
I create one (1) huge table per year with the fields date | name |
lowest | highest | last | volume

implementation 3:
-----------------------
I create one table per name with the fields date lowest | highest |
last | volume

implementation 4:
-----------------------
I create tables with the fields date | name1 | name2 | … | name 249
i.e.:
table 1 for lowest values for titles 1 to 249
table 2 for lowest values for titles 250 to 499
table 3 for lowest values for titles 500 to 749
table 4 for lowest values for titles 750 to 1000
table 5 for highest values for titles 1 to 249
etc.

One Table Approach For All Publications
we are working on a web portal rewrite using a MVC platform (cakePHP)
so we are now having a discussion if having a one db table for all our publications (publications, news, articles, classified ads, events, magazines, etc) since all this publication holds the same data fields in 95% of the cases. For that 5% we will make an extra table publications_extras with some extra fields and this "prepend" table will be called only for that 5% of the cases.

We will cache as much queries *** possible so the db load will be very load for one day (lets say 500 select queries a day for the publications table and maybe 100-200 insert and update queries).

So Is this one-table-approach considered a bad practice or a good practice. Please tell me why and be as specific *** possible.

Oh, and one of the main reason to use this approach is also to have the M and C part of the MVC logic as unified *** possible. So in nearly all the cases for all type of the publications there will be only two for all of them (one model and one controller)

Best Approach To User Authentication?
So I'm setting up a php/mysql driven website on our company's intranet. I need to setup username's and passwords for different individuals because they all need access to different parts of the site.

Now I'm reading up on how to do user authentication. One way I am reading about, involves creating a username/password table in the database, and when the user attempts to login in some generic php/html form, the php logs into the database using a generic login/password, then checks to see if the user's login/password match up with what is in the table.

That's an interesting way to do it. But the thing that is odd to me is that you actually log into the database using a generic login/password (hidden in the php script) and you would simply have to control what the user has access to in your frontend code, based off their login.

MySQL has it's own user authentication system in place, where you create logins and passwords for the database and control what databases and tables they have access to and control what they can do with them. So why wouldn't you do it this way?

Sorry if this sounds confusing, but hopefully users experienced in this understand where I am coming from.

Approach A Heirarchy Tree
I need to organize a bunch of data into a heirarchy pattern. I could easily build this in XML but am hoping to keep all data for this project in a database. But, I'm not quite sure how to approach the heirarchy pattern within MySQL. Since this pattern is so common, I figure there must be a standard method of doing this in MySQL that Im not familiar with.My initial attempt was to add a number of fields to a table that work as keys. The names of the keys indicate which additional tables to draw information that would be sub to that 'node'. This approach seems very inflexible and awkward so I'm looking for better ideas.

Question About Search Module Approach
I am doing a search module and right now, I am using a FULLTEXT to do it. So, if I search for 'car', it'll only return me words starting with car...

I read that FULLTEXT is the good choice when it is about searching, but, if I look at sitepoint search or flickr search, it seem that they do not use FULLTEXT but more query like this

SEARCH table WHERE field LIKE '%search%' with maybe some regex!

JOIN Or OBJECT ? Which Is Good Approach
I read this on some forums that I dont remember, there was quite a debate on this, an anyone please telle me which is a good approach to get information fron different tables for example I write code like this..

suppose I have 2 tables, user, and images

$r = mysql_query("SELECT * FROM users us, images im WHERE blah blah ");

now this works fine, I have created objects of thse tables to fetch information from 2 tables, but I have seen people using LEFT JOIN or any other join to accomplish this, whats the difference ? which is fast ? which puts more load on server ? any quick explanation ?

What Are Connections And How To Manage Them
I have been using mySQL for several years now, but I currently developing a larger/broader usage website with intense mySQL contents usage.

I have been told that mySQL has a so-called connection limit of 50-70.

QUESTIONS:

1) What is a connection limit? Or... How do you count 1 connection?

2) How do you properly manage this, to e.g. handle a site that has 1000 simultaneous users logged in to a web site and interacting with the database.

Manage Sql Queries
i've been using ms sql for many development and one of the many ways to manage sql queries would be to stored them as stored procedures. since mysql does nothave this feature in its stable release, does anyone have any good ways or good practices to stored sql quaries to allow easy maintenance? i plan to stored it as class files and call the function which returns the mysql quaries.

Install And Manage MySQL
I will have to follow-up my company mySQL server. I want to understand more about mySQL,
I would like to know if mySQL server can be installed through internet/web like myphpadmin? Or it must be installed locally?

Manage MySQL Instances
I am using the version 4.1.12a
Windows XP

In one customer I found the same version as mine and the ini file has

[mysqld]

port=3306

server-id = 1

basedir="C:/MySQL/"

datadir="C:/MySQL/Data/"

and the service is mysqld-nt

the question

I want to have the same service (mysqld-nt)
But different directory of my data

datadir="C:/MyData/Data/"

How can I do it in the configuration file??

PHP Script To Simply Manage Tables
I've got three tables (users, books and news), and I would like to crete a web interface
to manage their data (create items, modify items, delete items). Is there any PHP script to create it automatically (in the same way phpMyAdmin does, but
with less functionalities)?

Windows Based GUI To Manage Tables
What is best GUI to utilize to connect to my db to make edits to tables, etc...?

I thought MYSQL Administrator could do it but I can't connect to my database on a shared server... if this is to the right tool.

SELECT 1 FROM DUAL
Is dual a built-in table in MYSQL?

MySQL With Dual OS
I have a dual OS system with XP and Linux (Ubuntu). I generally use Ubuntu, but switch back to XP for the few things it does better (eg read SD cards). I also thought it would be better to use XP when visiting clients that are Windows only users. I therefore wanted to have my database accessable from both OSs. It took me a while, but I managed it this way:

- installed MySQL in XP on the shared drive, which is formatted as FAT32.
- created the database in XP.
- installed MySQL in Linux
- created a symbolic link in Linux from /var/lib/mysql/data to /shared/mysql/data

From there, everything was accessable and I thought it was working fine until I got a dreaded 127 error and found many of my tables were corrupt. Googling around, it seems that the usual cause of this was that a table was too big. That wasn't possible because at the time, I only had 20 rows in the largest table.

It seems more likely that it was because the access counts were screwed up from switching between systems. I found an error message (not sure where so I can't paste it here) that said something to the effect that the application thought the (query number?) was earlier than the database did.

Does any of this sound familiar? Has anyone found a way to access the same database from 2 OSs?

Dual Processors
I'm running a centos server on an older dual 450 PII. It works fine for what I need it for at the present time. However It seems that mysql is using only one processor...I think.

I am running 2.6.9-34.ELsmp, so I know I've got access to both processors.

In running a process that should consume all of my processor power with mysql, I discovered that top shows that the mysql is using 99.9% processor, but the CPU(s) line in top shows only 50% processor usage. Suggesting that mysql is only using 1 of the processors.

Dual Connections
I am developing a PHP script that needs to connect to two different databases in one script. There is one catch, however. One database is on a local computer (laptop). The other database is on a server 1,000 miles away.Is this possible?
In case you are wondering why I need this capability:

This application allows a user to enter data into a database on their local computer (laptop). With the click of a button, I want to transfer all of the data from the local database to the database on the server. Therefore I need to connect to the local database, loop through each record, copy it into an array. Then I need to connect to the server database, loop through the array, and insert each row into the server database. This must all be in the same script.

MAXDB Not Allow To Create More Than 5 Instance On Database Manage
I have several problems with my MAXDB, IÂ´m New user. The problem appears when I try to create more than 5 instance on DATABASE MANAGER, it close all conections and shut down all database, i have install it on LINUX DEBIAN 1.3 with MAXDB 7.5.

In addition to this, when I send more than 500 Mb. of data to my server it closes all connetions..

Dual Sort In ORDER BY Clause?
I'm wondering if there's a way to order results by two criteria? I want to order forum threads by (1) their "stuck status" (enum, 0 or 1) and (2) by their "last post date" (int, unix timestamp).

The resulting output would have the "stuck" threads at the top regardless of their last post date, followed by the "unstuck" threads ordered by their last post date.

Can this be done with SQL?

Database Design: How Best To Manage Data Across Multiple Schemas / Databases
Our organization is looking for the best design for managing shared data. Specifically we wish to have a master table for data related to a Person. That person table would be shared by any other databases within the company that needed it. Of course, we do not want to share the whole master table, just the rows relevant to specific application that will be using it.

We also want the sharing to be as transparent as possible to both the users and the support / development staff. Using a view seems like the way to go, BUT...each application has tables with FKs to the Person table. We can't do FKs to a view. We can have the application reference the view for selects / updates to the Person table, and have the other tables define their FKs to the master Person table, but we're a little worried about security since we can't narrow their permissions to only certain columns & rows like in the view. Oh, I am not doing a good job of explaining this.

AUTO_INCREMENT - Configuration For Replication With Dual Masters
I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

MySQL 4.0.18 On Dual AMD64 Opteron Causing Thread Zombie Even When Timeout Is Set To 60 Seconds
We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10
for a very high volume site. We are evaluating the performance on our
new server AMD64 and it seems it's slow compared to Dual Xeon/MySQL
4.0.15/RedHat8 and Dual Xeon/MySQL 4.0.18/Mandrake 10.

And it seems there are zombie threads. 570 threads in 1 hour and we
didn't even use JDBC connection pooling at all. These threads are
supposed to be gone within 60 seconds, since we set that option in
mysqld. Note that we run many SELECT queries (can be up to 150
queries/seconds), but the system does not indicate any slow query:
it's 0!

Our configuration is Apache 2.0.48 + Tomcat 5.0.27 + MySQL 4.0.18 with
MySQL connector/J 3.0.14 (latest stable). The Redhat 8 runs on Apache
2 + Tomcat 4.0 + MySQL 4.0.15. The old Redhat 8 on Xeon was fine. We
have another machine running Mandrake 10 on Xeon and they were fine
under the same load.

I have set the wait_timeout to 60 seconds, and it appears to be fine
within 10 minutes, all the threads that are in "sleep" mode
disappeared after 60 seconds. After a few minutes though, it's back
like it was before.

Is this Mandrake problem? MySQL problem? I read in here than Mandrake
win hands down on AMD64 compared to FreeBSD. Code:

How Do You Manage Changes From Development Server To Production Server?
What started out as a simple test of mysql has turned into what is
quickly becoming a replacement for our entire business system and web
site

The bad news is that we are now wrestling with how to we continue
working on development the system without risking down time for users.
If we work on a development server, how can we log the changes to table
structure and then apply them to the live database on the production
server when we are done testing?

Is there some sort of tool or log that would just let us apply table
structure changes to another copy of the database?

Relay-bin On Master?
I am getting relay-bin logs on my master server. They are all empty except
for one line that reads:

þbin

I have no slave settings in my my.cnf file. Any idea where these are coming
from and how to turn them off?

Replication From 2 Master
I am running MySQL V4.0.14 with replication. I want to replicate specified databases from 2 different masters into one slave. Is this possible?

Relay-bin On Master?
I am getting relay-bin logs on my master server. They are all empty except
for one line that reads: þbin

I have no slave settings in my my.cnf file. Any idea where these are coming
from and how to turn them off?

Replication From 2 Master
I am running MySQL V4.0.14 with replication.

I want to replicate specified databases from 2 different masters into one
slave. Is this possible?

Clustering And Master Down
I want to create replication databases with multiple servers, but i need more informations about consequences when the MASTER database crash : what happens ?

And, if one slave crash : what happens ?

Is it like RAID 5, mysql just work in degraded mode and continue to work, or is it fatal ?

Can we compare the replication system by MySQL with the Grid Computing of Oracle ?
I don't think MySQL offers a clustering mode for calculating and searching rows, am i wrong ?

Two Master Servers
I am using two master servers that are synchronized with each other so that if one of them gets disconnected, the other one could take over and no data is lost.
Now my problem is doing backup on the servers. Say I am doing backup on Master 1 and after backup, I will delete the data. So, since Master 2 is synchronized with Master 1, will the deleted data be written back to Master 1?
My batch file is something like this.
echo Executing MYSQL BACKUP
mysqldump -uroot -proot --result-file=%SAVPATH%%FILENAME%".sql" database1
echo Executing MYSQL DELETE
mysql -uroot -proot datalog < "C:Program FilesMySQLMySQL Server 5.0inscript.sql"
Script.sql will call the command "DELETE from table1" for me.

Master/Slave Restoration.
I have 2 servers (A and B) setup as Master and Slave respectively.
Upon A's failure, all client's request will be directed to B.

How can I set A to become master again after its been restored from
its failure? In chapter 4.10 of mysql docs, it mentioned (vaguely)
that A will become B's slave to sync all operations that had happened
after the failure. Finally, upon full sync switch A to become Master
again and B to slave.

How can I setup A to be a slave of B? The problem I faced is that I
can't find out what the bin log and position is from B since "show
master status" is always empty. I guess B still thinks its a slave.

Master Servers Merge
I want to make query to merge table1 from master server A and table2 from master server B (ex: select * from table1, table2) because my site becomes heavy traffic and database gets bigger. It means I need to separate tables and create new database on other master server B. Do you know how to make "merge" from master server A and B?

MySQL Master/Slave
In the case of 2 3.23.X series mysql databases in a master/slave
replication setup (myiasm tables), if a FLUSH TABLES WITH READ LOCK is
put on the slave, does the master effectively lock too because it is
waiting for the binlog update(s) to be confirmed from the slave, or
does the master merrily carry on not caring in the least that the
slave tables are locked?

Master-connect-retry
I installed MySQL 4.0.14 on a new Linux machine unde the form of two
servers: one master and one slave.

I met many unsynchronized events so it was appears conflicts from this late
of the changes from master to slave. I put the master-connect-retry to 2.
Now it seems to work clearly but I cannot see the master-connect-retry
variable with "show variables" command. Is this bad? Shall I have another
unsynchronized events in the future between master and slave?

I manually repaired each conflict or I did RESET MASTER/SLAVE to escape from
the errors' labirint.

Step By Step Approach To Manage Dual-master Set Up

I am thinking of having a dual master setup where two database servers A and B are slaves to each other. Could someone please point me to some documentation that details what exactly I should do if A or B goes down?

I am thinking of having a dual master setup where two database servers
A and B are slaves to each other.

Could someone please point me to some documentation that details what
exactly I should do if A or B goes down?