Re: Sync 2 live MySQL Databases

On 26/05/2005 6:40 p.m., Cecil Brand wrote: Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync.

Re: InnoDB to MyISAM

Hi, If you're loocking for consurrent inserts/deletes, use innodb (row level locking). This will be better than myisam. I'm looking for concurrent inserts / selects, reading and writing. The key is to get the reads and blocking writes (writes that need the insert ID, which can't be done DELAY

RE: update combine values

Ismet Dere wrote: > my question is that, is it possible to combine values of multiple > fields in another field of same table with an update statement such > as this; > > UPDATE UserTable SET FullName = FirstName & MiddleName & LastName; UPDATE UserTable SET FullName

update combine values

Hi am fairly new to mysql and need some help, i have search thru online documents but found not much help to this. my question is that, is it possible to combine values of multiple fields in another field of same table with an update statement such as this; UPDATE UserTable SET FullName = First

Sync 2 live MySQL Databases

Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, th

Re: InnoDB to MyISAM

> >> >>key relationships? > >> > > >> > MyISAM can't FK's. > >> > >> Yes it can, they're just not enforced. > > > > LOL - that's just like saying that MyISAM supports transactions > > if you only do transactions that are a single statement... > > > > In other words: useles. > > Somehow I use these

Re: InnoDB to MyISAM

Hi, If you're loocking for consurrent inserts/deletes, use innodb (row level locking). This will be better than myisam. The text column can be created in aother table and stay referenced via a FK. This will ceratinly be faster since insert or delete on myisam implies a LOCK table implicit mecanis

how to optimize this query

Hi, I've got a query that I believe should be giving me better performance than it is, since I've put indexes on all the appropriate columns...I'll include as much info as I can for anyone wishing to help me optimize the query/tables/indicies...thanks in advance for any assistance... the que

Re: InnoDB to MyISAM

>>key relationships? > > MyISAM can't FK's. Yes it can, they're just not enforced. LOL - that's just like saying that MyISAM supports transactions if you only do transactions that are a single statement... In other words: useles. Somehow I use these "useles" foreign keys to create relational

Re: Installer for 4.0.13

Thanks a lot. - Original Message - From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Mayuresh Kshirsagar'" <[EMAIL PROTECTED]>; "'MYSQL Mailing list'" Sent: Wednesday, May 25, 2005 4:36 PM Subject: RE: Installer for 4.0.13 > Mayuresh Kshirsagar wrote: > > > Its

Inner workings of a JOIN

What does MySQL do internally when you perform a LEFT JOIN? Let's say you have two tables: Table A has 1,000,000 rows Table B has 5,000 rows When you perform the following LEFT JOIN: Select A.*, B.* FROM A, B WHERE A.lastname = 'doe' AND A.id = B.id What does MySQL do

Query performance...two table design options

Hi: Let's say I want to store the following information. Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT I would be basing my queries on all columns _except_ the Data column. I.e. I would be u

Re: String Literals ONLY for REGEXP, LIMIT and LOAD DATA clauses in MySQL Stored Procedures?

At 19:18 -0700 5/25/05, Scott Klarenbach wrote: Am I correct in assuming that MySQL requires string literals in stored procedures for the following clauses: LIMIT requires integer constants, not strings. The filename for LOAD DATA INFILE should be a literal string, as you surmise. Don't know a

String Literals ONLY for REGEXP, LIMIT and LOAD DATA clauses in MySQL Stored Procedures?

Am I correct in assuming that MySQL requires string literals in stored procedures for the following clauses: REGEXP, LIMIT and LOAD DATA INFILE? For example, I cannot seem to pass in the {pattern} as a parameter to my SPROC, and then query for ...WHERE field REGEXP pattern I'm having the same pr

Re: LOAD DATA and skip columns in text file...

I cant tell you how much i love you right now :) This works flawless!! Thanks a million times! From: Harrison Fisk <[EMAIL PROTECTED]> To: "Jessica Svensson" <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: Wed, 25 May 2005 20:05:40 -04

Re: Sub Query to long...[solved]

[EMAIL PROTECTED] wrote: My suggestion: Don't use a subquery, use a temp table ( http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html) CREATE TEMPORARY TABLE tmpDupes (KEY (`Barang`)) SELECT `Barang` FROM Barang GROUP BY Barang HAVING count(1) >1; Select b.`BrgId`, b.`Kode`, b.`Barang`

Re: LOAD DATA and skip columns in text file...

Hi, On May 25, 2005, at 7:38 PM, Jessica Svensson wrote: Wow!! Thanks! I will upgrade right away... but i can't find any information in the url you supplied about how to specify what fields from the external file to read... It will read all of them, but you can just specify a variable for o

Re: LOAD DATA and skip columns in text file...

Wow!! Thanks! I will upgrade right away... but i can't find any information in the url you supplied about how to specify what fields from the external file to read... will be quite hard to figure out on my own i guess? Thanks again! From: Harrison Fisk <[EMAIL PROTECTED]> To: "Jessica Svenss

Re: LOAD DATA and skip columns in text file...

Hi, On May 25, 2005, at 1:28 PM, Jessica Svensson wrote: That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of

RE: Subquery question

That might explain it:-) The really said part is I remember running into the same issue some months back and completely forgot. Thanks -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 6:54 PM To: Bartis, Robert M (Bob) Cc: 'mysql' Subject: Re: S

Re: Subquery question

In the last episode (May 25), Bartis, Robert M (Bob) said: > Apologize for not including the 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 'SELECT > plantriggers.ID FROM plantriggers WHERE (((

RE: Subquery question

Apologize for not including the 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 'SELECT plantriggers.ID FROM plantriggers WHERE (((plantrigger I'm running MySQL 4.0.2-standard -Original Message

Subquery question

I have a problem where I need to use a subquery in combination with a Left Join. The SQL statement below works fine until I introduce the subquery portion. Specifically, WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanI

Re: lower version mysqlclient can not talk to the mysql server of higher version

On May 25, 2005, at 5:12 PM, Farid Hamjavar wrote: Situation: System XYZ RH AS 2.1 RH mysql client --> mysqlclient9-3.23.22-8 RH php --> php-4.1.2-2.2 & php-mysql-4.1.2-2.2 System ABC RH AS 3.0 RH mysql server --> 4.1.10-standard The mysql client on XYZ can not talk to mysql server on

lower version mysqlclient can not talk to the mysql server of higher version

Greetings We're in a peculiar situation that needs resolution. Situation: System XYZ RH AS 2.1 RH mysql client --> mysqlclient9-3.23.22-8 RH php --> php-4.1.2-2.2 & php-mysql-4.1.2-2.2 System ABC RH AS 3.0 RH mysql server --> 4.1.10-standard The mysql client on XYZ can not talk to mys

Lost connection to MySQL server during query problem

Hi, Sometimes when querying mySQL 4.1.11 on Linux machine I get the error "Lost connection to MySQL server during query". Here are the symptoms 1. When running the query from the server it returns OK 2. When running the query from another machine using the mySQL query browser it r

Re: Year Data Type

Have you seen this page of the manual? http://dev.mysql.com/doc/mysql/en/year.html Rhino - Original Message - From: "Asad Habib" <[EMAIL PROTECTED]> To: Sent: Wednesday, May 25, 2005 3:55 PM Subject: Year Data Type > For purposes of comparison, is data of type Year treated the same a

Re: Cumulative Totals

Hi again, if your looking for raising sub-totals, i found you thos form : mysql> select 'TOTAL', -> sum(if(DATE_FORMAT( `paymentDate` , '%Y-%m' )<='2005-01',amount,0)) as '2005-01', -> sum(if(DATE_FORMAT( `paymentDate` , '%Y-%m' )<='2005-02',amount,0)) as '2005-02' -> from payments

Year Data Type

For purposes of comparison, is data of type Year treated the same as integers? I think this is the case because a year is represented using either 2 or 4 digits, but I am not absolutely certain. Any help would be appreciated. Thank you. - Asad -- MySQL General Mailing List For list archives: htt

RE: Fulltext Simple Question

"Scott Purcell" <[EMAIL PROTECTED]> wrote on 05/25/2005 03:35:54 PM: > Thanks Sean fo the info. > > I see where it states the server is configured for 4 character > indexing. I would like to try and set it to 3 and do not understand > what an options file is: > The documentation states the foll

RE: Fulltext Simple Question

Thanks Sean fo the info. I see where it states the server is configured for 4 character indexing. I would like to try and set it to 3 and do not understand what an options file is: The documentation states the following: * The minimum and maximum length of words to be indexed is def

Re: Fulltext Simple Question

Brian Mansell <[EMAIL PROTECTED]> wrote on 05/25/2005 03:09:03 PM: > Scott - > > Check this excerpt out ( > http://dev.mysql.com/doc/mysql/en/fulltext-search.html ) from the MySQL > Documentation. I hope it helps! > > --bemansell > > ... > > "Every correct word in the collection and in the q

RE: LOAD DATA and skip columns in text file...

The folowing is out of the current MySQL manual. It looks like you could create an intermediate table with the fields you are interested in the front and "garbage" fields on the end. Then build a specific LOAD DATA INFILE with correct mapping for each file type [assuming you can tell this in your a

Re: Fulltext Simple Question

Scott - Check this excerpt out ( http://dev.mysql.com/doc/mysql/en/fulltext-search.html ) from the MySQL Documentation. I hope it helps! --bemansell ... "Every correct word in the collection and in the query is weighted according to its significance in the collection or query. This way, a wo

Re: Repairing/Restoring a Database

Thank you *SO* much, Shawn. Doing it manually did the trick. I'm sure you know how frustrating this can be. I really, really appreciate your help. Whew! best, Matt On 5/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > You can try doing the same steps, but break it down so that you d

Fulltext Simple Question

Hello, I am running 4.0.15 for Win95/98 and am working through the docs. I created a "text" type field with a 'fulltext' index. As I am experimenting, I have run into a couple of questions: First off, I was having trouble getting results. So I added the word "foobar" to one of the descriptions:

Re: Repairing/Restoring a Database

One more note for anyone else who runs into this problem: before running the source command, I dropped the database, recreated it, and then ran the mysqldump file into the empty database. Matt > On 5/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > You can try doing the same steps, b

Re: rpl_openssl test hangs in MySQL 4.1.12

On Wed, 25 May 2005 11:09:06 -0700 (PDT) Kathir Velu <[EMAIL PROTECTED]> wrote: > "openssl_1" and "rpl_openssl" are skipped eventhough i > have compiled with OpenSSL. > > So I have rerun these tests alone as, > > ./mysql-test-run --with-openssl openssl_1 rpl_openssl > > Now openssl_1 test pass

Re: Cumulative Totals

Hi, aren't you looking for somthing like that : mysql> select sum(amount),DATE_FORMAT( `paymentDate` , '%Y-%m' ) c FROM `payments` GROUP BY c with rollup; +-+-+ | sum(amount) | c | +-+-+ | 200 | 2005-01 | | 58 | 2005-02 | | 2

rpl_openssl test hangs in MySQL 4.1.12

Hello All, I am very new to the MySQL environment. I have built latest stable MySQL 4.1.12 in Hp-Unix 11.23 IPF platform with OpenSSL support. Following are my configuration options I have used to build, ./configure --prefix=/opt/iexpress/mysql --localstatedir=/var/opt/iexpress/m

Re: Repairing/Restoring a Database

matt g <[EMAIL PROTECTED]> wrote on 05/25/2005 12:45:22 PM: > While updating a record in a database, I inadvertantly forgot a > "where" statement; so instead of changing just one record, I changed > all 900 records in the database. > > I've been trying to figure out how to fix this. My latest ba

Re: LOAD DATA and skip columns in text file...

That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this

Repairing/Restoring a Database

While updating a record in a database, I inadvertantly forgot a "where" statement; so instead of changing just one record, I changed all 900 records in the database. I've been trying to figure out how to fix this. My latest backup (through mysqldump) was about fifteen days ago. I'm fine with res

Re: won't start on fedora core 3--kernel errors?

Hello. Disable or change policies of SELinux. Anne Ramey <[EMAIL PROTECTED]> wrote: > My mysql won't start with /etc/init.d/mysql start or service mysql start > > No errors in the mysql log, but these errors in /var/log/messages: > > May 25 11:12:21 wind kernel: audit(1117033941.355:0)

Re: how can I install mysql without administrator priviledge

Hello. MySQL could be installed under unprivileged user in location owned by that user. You should correctly specify the parameters in the configuration file or in command line. I usually launch mysqld using mysqld_safe with --defaults-file command line option. Here's an example of such file

restarting mysql server

Dear there, After install mysql 4.1.12 by using rpm, I have some problems to restart server after shutting down it. When I use "mysqld_safe" try to restart, it give me error as follws: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld

Can't Start MySql

MySQL 4.0.14 Win98 platform. what could cause the following error by typing mysqld --console. regards 050525 16:50:13 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 154545

RE: Restoring InnoDB databases from backups causing problems

Hi Thanks for the help. Even though an rpm -qV -a on the machine listed no likely candidates for corruption, a complete uninstall and reinstall of mysql and mysql-max packages seems to have have fixed whatever the problem was (running off the backed up data). David -Original Message- Fro

Re: Restoring InnoDB databases from backups causing problems

On May 25, 2005, at 10:06 AM, David Brewster wrote: Here is the log dump :- Thanks David 050525 13:24:10 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.15-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 050525 13:24:11 InnoDB: Assertion failure in thread 114

won't start on fedora core 3--kernel errors?

My mysql won't start with /etc/init.d/mysql start or service mysql start No errors in the mysql log, but these errors in /var/log/messages: May 25 11:12:21 wind kernel: audit(1117033941.355:0): avc: denied { append } for pid=2546 exe=/usr/sbin/mysqld-max path=/var/lib/mysql/wind.blast.com.e

RE: Restoring InnoDB databases from backups causing problems

Hi Here is the log dump :- Thanks David 050525 13:24:10 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.15-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 050525 13:24:11 InnoDB: Assertion failure in thread 114696 in file fsp0fsp.c line 3034 InnoDB: We in

Re: Restoring InnoDB databases from backups causing problems

On May 25, 2005, at 9:34 AM, David Brewster wrote: A client of ours has experienced some rather serious hardware failures resulting in disk errors and therefore corrupted InnoDB files. We could not get mysql to restart unless the innodb_force_recovery was set to 5. In this situation we e

Re: Alter InnoDB to MyISAM Part2

"Scott Purcell" <[EMAIL PROTECTED]> wrote on 05/25/2005 09:22:32 AM: > I can do this programatically, and will alter the table. But there > are three tables that have foreign key references to the table I > will be altering. > > They look like this. > CREATE TABLE ITEM_CAT_REL ( > id INT,

Restoring InnoDB databases from backups causing problems

Hi A client of ours has experienced some rather serious hardware failures resulting in disk errors and therefore corrupted InnoDB files. We could not get mysql to restart unless the innodb_force_recovery was set to 5. In this situation we encountered failure when attempting to read from variou

Re: InnoDB to MyISAM

> > http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html > > He's converting InnoDB to MyISAM. > > I would recommend something like this: > > CREATE TABLE newtable LIKE oldtable; > > ALTER TABLE newtable ENGINE=MyISAM; > > ALTER TABLE newtable DISABLE KEYS; > > INSERT INTO newtable S

Re: Sub Query to long...

Hendro Suryawan <[EMAIL PROTECTED]> wrote on 05/25/2005 06:23:52 PM: > Hi Mathias, > Thanks for your suggestion, but i run this query to find multiple > records with the same name in field barang (double records). And the > results i found 94 rows at 54813 ms. I try your idea and the result is

Re: Alter InnoDB to MyISAM Part2

You should be able to pull the ID of the parent table in when you do the INSERT ... SELECT to pull the data in, in which case, the IDs in the foreign key fields would still be valid. I'd convert them all to MyISAM rather than doing half and half. - Original Message - From: "Scott Purc

Re: InnoDB to MyISAM

- Original Message - From: "Rafal Kedziorski" <[EMAIL PROTECTED]> To: "Scott Purcell" <[EMAIL PROTECTED]>; Sent: Wednesday, May 25, 2005 6:06 AM Subject: Re: InnoDB to MyISAM http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html He's converting InnoDB to MyISAM. I woul

Alter InnoDB to MyISAM Part2

I can do this programatically, and will alter the table. But there are three tables that have foreign key references to the table I will be altering. They look like this. CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on

Re: InnoDB to MyISAM

hi, At 14:54 25.05.2005, Scott Purcell wrote: Hello, I posted last night but did not receive an answer. I am trying to create a fulltext index, but my table was created as an InnoDB type. There is quite a bit of data there (1000+ records) and I need to change to a MyISAM table for the indexi

Re: Cumulative Totals

- Original Message - From: "Russell Horn" <[EMAIL PROTECTED]> To: Sent: Wednesday, May 25, 2005 8:02 AM Subject: Cumulative Totals > I have a pretty simple table with a list of payments, not much more > than: > > paymentID | amount | paymentDate > 1 | 123| 2005-01-10 > 2 | 77

Re: InnoDB to MyISAM

Scott Purcell wrote: Hello, I posted last night but did not receive an answer. I am trying to create a fulltext index, but my table was created as an InnoDB type. There is quite a bit of data there (1000+ records) and I need to change to a MyISAM table for the indexing for fulltext search. H

InnoDB to MyISAM

Hello, I posted last night but did not receive an answer. I am trying to create a fulltext index, but my table was created as an InnoDB type. There is quite a bit of data there (1000+ records) and I need to change to a MyISAM table for the indexing for fulltext search. How can I convert the Inn

Re: Cumulative Totals

You may be able to use the WITH ROLLUP option of GROUP BY to get something of what you are looking for. Can't think of anything off the top of my head to get exactly what you are looking for. On May 25, 2005, at 8:02 AM, Russell Horn wrote: I have a pretty simple table with a list of payments

Re: Cumulative Totals

On Wed, 25 May 2005, Russell Horn wrote: >I have a pretty simple table with a list of payments, not much more >than: > >paymentID | amount | paymentDate >1| 123| 2005-01-10 >2| 77 | 2005-01-13 >3| 45 | 2005-02-16 >4| 13 | 2005-02-17 > > >I can get to

Cumulative Totals

I have a pretty simple table with a list of payments, not much more than: paymentID | amount | paymentDate 1 | 123| 2005-01-10 2 | 77 | 2005-01-13 3 | 45 | 2005-02-16 4 | 13 | 2005-02-17 I can get totals per month using a query like: SELECT SUM(am

Re: LOAD DATA and skip columns in text file...

In article <[EMAIL PROTECTED]>, "Jessica Svensson" <[EMAIL PROTECTED]> writes: > LOAD DATA and skip columns in text file... > What i have found out is that this is not possible in any existing > version of mysql, correct? > I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 >

Re: Installer for 4.0.13

Mayuresh Kshirsagar wrote: Hi Peter, Its the requirement for the project to use 4.0.13. I visites the page you mentioned. but the only build available there which i can see is 4.0.24. Am I missing something? Regards, Mayuresh - Original Message - From: "Peter Normann" <[EMAIL PROTECTE

RE: Installer for 4.0.13

Mayuresh Kshirsagar wrote: > Its the requirement for the project to use 4.0.13. I visites the page > you mentioned. but the only build available there which i can see is > 4.0.24. Am I missing something? Well, without having read the changelogs I can't imagine anything

Re: Installer for 4.0.13

Hi Peter, Its the requirement for the project to use 4.0.13. I visites the page you mentioned. but the only build available there which i can see is 4.0.24. Am I missing something? Regards, Mayuresh - Original Message - From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Mayuresh Kshirsagar'

Re: rpm install on RHEL4 x86-64 does not create MySQL grant tables

An earlier versions of 4.1.12 rmps had some problems, I saw this in some messages in list. But this could be caused by other reasons. SELinux for example. Are you able to create grant tables running mysql_install_db manually? "Laser, Mary" <[EMAIL PROTECTED]> wrote: >>Description: >

Re: Sub Query to long...

Hi Mathias, Thanks for your suggestion, but i run this query to find multiple records with the same name in field barang (double records). And the results i found 94 rows at 54813 ms. I try your idea and the result is the same. So i think mysql not optimized for this kind sub query. Do you hav

how can I install mysql without administrator priviledge

I downloaded the binary files and upziped them to /home/myname/mysql.(I use red hat linux) when I use command "mysqld --console" to run the server. The following message are showed on the screen: [EMAIL PROTECTED] bin]$ ./mysqld --console 050525 17:36:11 Warning: Can't create test file /var/li

RE: help needed to create index

Asha <> wrote: > Is there a physical > limitation in the InnoDb table structure as to why it can't > support FullText indexes? http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html Peter Normann -- MySQL General Mailing List