R: inconsistent replication?

I got the same problem. In theory there should be some method to avoid duplicate entry during replication, but in practise all that way seems to fail, mybe I'm not using it properly ( see my threads on this topic ). The only can help you go on , but is a dirty trick is remove the file from the slav

R: what about slave_skip_errors variables

I got some backup server where the database is configured as slave. On each server there is a program that querye the mester and if it in not responding one of the slave configure itself as a master. I should consider that is possible there is a mistake in my program which cause and extra insertion

adding conditional stmt using mysql

Hi, I am facing some problems while writing a MySQL query for a complex scenario. I have a table with employments for all executives and a flag to distinguish the current and previous employments(titles in different companies or previous titles in the same company). Here are 2 examples: i. Em

Re: need help with user variables in where clause of sub query

Dan, You need to shoot your SQL tutor. Whoever taught you to write aggregate queries seriously took your money. You DO NOT need to use subqueries to do what you want to do. You do not need to write a full CREATE TABLE statement to create a temporary table (see other response). You do not need

Re: need help with user variables in where clause of sub query

I just tried to create a Function or Stored Procedure instead of making variables but it didnt even let me do this CREATE FUNCTION test (customerID, month, producerID) RETURN SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE fu.customerID=customerID AND fu.month=month AND fu.feedID IN (SELECT

Re: need help with user variables in where clause of sub query

Btwi dont want the column of a view to be a variable, i think thats what it thinks ! Im just needing to send the value of the current primary key field top a sub query ! Read my latest post if i can get around not using variables, and still manage to get the right values of a current row going

Re: need help with user variables in where clause of sub query

Um, thast exactly right each select is a list of results , i want to merge them then manipulate the data after putting them into a view, maybe a temp table is needed for this but i dont really want to do an entire create table statement aswell :\ On 29/12/2005, at 2:48 AM, [EMAIL PROTECTED] w

issue with user variables in a view

Ok i have simplified my query into sections i have discovered that mysql5 doesnt like user variables in a select statement when creating a view, i am required to setup user variables so i can send the current row value primary key to a sub query, i really wished i could just send the field to t

create function with space

I set my sql_mode = 'STRICT_TRANS_TABLES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO'. Note,IGNORE_SPACE not include sql mode. But i still execute statement as follow : CREATE FUNCTION "wangxu"."user " () RETURNS int(11) BEGIN return 1;

Re: issue with subquery

Dan, >SELECT count(*) >FROM feeds >WHERE feedID IN ( > SELECT feeds FROM month_totals > WHERE customerID=9 AND month_unique=1105 >) >expected result should be 4 , i get 1. If i manually put in > SELECT count(*) FROM feeds WHERE feedID IN (1,2,3,4) >for instance i get 4 Why the subquery? Aren'

issue with subquery

Hi, im having some issues with a sub query in mysql5 , i have a field which is storing a static comma seperate list of primary keys ie 1,2,3,4 , i am then trying to use that to find entries in another table of the same primary keys here is the sql SELECT count(*) FROM feeds WHERE feedID IN (SE

Re: MySQL Administrator logon question

Aftab Khan wrote: I have installed mySQL with admin ID and password. However when I login using the mysql Administrator, it allows logons without a password. How do I force it to get the password and authentic rather allowing only user id? Thaks Please refer to this page: http://dev.mysql.co

RE: How to share databases on dual-boot machines?

Hi James, There are a couple of options (from the Linux side of things) : 1) set the datadir either in the start options of safe_mysqld (or mysqld_safe I can never remember) or set it in the options file (my.cnf or a .my.cnf in your home directory) see http://dev.mysql.com/doc/refman/5.0/en/optio

How to share databases on dual-boot machines?

All, I'm new to MySQL (using v4.1) and have a dual boot machine with Windows and Debian Linux (Etch). My goal is to share a database named "wordpress" between Linux and Windows by storing it on a FAT32 mount (I realise this limits me to 4GB in total). From what I can see at the moment, the

Re: set sql mode

My sql_mode is "STRICT_TRANS_TABLES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO". But when i execute "select 1/0 from ht_detail",the result is "Null". No error throw out. Why? - Original Message - From: "Gleb Paharenko" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 28, 2005 9:26 PM Re:

load balance for mysql servers

Hello, We have four Mysql database servers: S1, S2, S3, S4 and we use Mysql replications, S1 is the master server S2, S3, S4 are the slave servers. Our website use S2, S3, S4 as production databases; But we have no good idea to load balance between three severs. We use php scripts to chose a

doubled words in 5.0.17 manual

>Description: Using Nelson Beebe's dw program (http://www.math.utah.edu/~beebe/software/file-tools.html) I noticed a number of spuriously doubled words in the manual. The text here comes from the .info file in the distribution; sorry, I could not easily find

ibdata1 File

Hello! When I first set up my mySQL 4.1 server, I did not have the "innodb_file_per_table" option set. I have since set this option, and re-created all my tables so that they are now in individual innoDB files. However, the original, 44GB ibdata1 file still exists and I can't find any good way o

inconsistent replication?

Hi, Some hours ago i setup the replication for my cluster and it's the third time i need to reset the replication and copy the whole database from master to slave due to errors like this: 051228 17:13:35 [ERROR] Slave: Error 'Duplicate entry '9947776' for key 1' on query. Default database: '

Re: Need Help Writing a Trigger

That was perfect. I had done some research, and tried set today=(select campstartdate from config) and various other arrangements of this, but none of it worked. Yours worked perfectly. Thanks for the help! Jesse - Original Message - From: "Peter Brawley" <[EMAIL PROTECTED]> To: "

Re: LIMIT on GROUP BY?

"Jay Paulson \(CE CEN\)" <[EMAIL PROTECTED]> wrote on 12/28/2005 02:37:36 PM: > My query below returns however many rows fit the WHERE condition, in > this case when they year, period, week is <= 2009131. In my case it > is returning 11 rows because I have 11 rows where the year,period, > week

Re: LIMIT on GROUP BY?

Jay, >I only need 4 rows returned to me and not all 11 no >matter what the <= XXX part of the where is. ... LIMIT BY 0, 4? PB - Jay Paulson (CE CEN) wrote: My query below returns however many rows fit the WHERE condition, in this case when they year, period, week is <= 2009131.

Re: Replication A->B->C - changes on B are not replicated to C

Hello. >So now my question is: is it basically possible to do this or doesn't mysql >replication mechanism support this setup? If it is possible: Any idea where >the problem could be? If it's not possible: any idea for a different setup >that would allow to this? You should localize the pr

Re: what about slave_skip_errors variables

Are you saying that "from time to time it happens that data gets inserted directly to the slave"? In other words, they are NOT inserted into the master. Have you looked into using this mysqld option, which prevents accidental slave updates? --read-only Make all tables readonly, with the

LIMIT on GROUP BY?

My query below returns however many rows fit the WHERE condition, in this case when they year, period, week is <= 2009131. In my case it is returning 11 rows because I have 11 rows where the year,period, week is 2006XXX. However, this is not what I want. I only need 4 rows returned to me and

Re: Urgent help using logon to mySQL

Aftab Khan <[EMAIL PROTECTED]> wrote on 12/28/2005 02:15:33 PM: > Can some one please tell me what I am doing wrong here I have installed > and configured users in the database. I am using ODBC driver to logon. > When I use the password, it does not work but surprisingly the logon is > allow

Urgent help using logon to mySQL

Can some one please tell me what I am doing wrong here I have installed and configured users in the database. I am using ODBC driver to logon. When I use the password, it does not work but surprisingly the logon is allowed without a password. I have tried to use MySQL Administrator to logon r

MySQL 5.0.17 on FreeBSD 4.7 - zlib error

Just throwing this out there for the archives. Installing 5.0.17 MySQL Client on a FreeBSD VPS2 Virtual server from Verio kept throwing an error looking for zlib.la in the zlib library. This system didn't have a system wide zlib installed, so the bundled library kept trying to be used instead.

Re: Need Help Writing a Trigger

Jesse, >BTW, is there a way to change this function so that it does away with the >"today" variable, and uses a field from a different database? For instance, >I have CampStartDate stored on the Config table. Can this Age function >be modified to get the "today" variable from that table instea

Re: Need Help Writing a Trigger

BTW, is there a way to change this function so that it does away with the "today" variable, and uses a field from a different database? For instance, I have CampStartDate stored on the Config table. Can this Age function be modified to get the "today" variable from that table instead? Thanks,

MySQL Administrator logon question

I have installed mySQL with admin ID and password. However when I login using the mysql Administrator, it allows logons without a password. How do I force it to get the password and authentic rather allowing only user id? Thaks

Re: Upgrading to 5.0.15

We moved directly from 4.0.20 to 5.0.16. Worked like a charm. I had a script that went through and optimized all tables with keys on text/varchar and char fields. We're also slowly ALTERing innodb tables to get them into the new compact format. On 12/28/05, Gleb Paharenko <[EMAIL PROTECTED]> wro

Re: autoincrement for year

Yes it could be but YOU DON'T NEED TRIGGERS. All you need is the auto_increment extension for multiple-column primary keys, as described by another post and demonstrated here: http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html Shawn Green Database Administrator Unimin Corporatio

Slave to multiple masters?

Are there any plans to address multiple masters to a single slave? I've been Googling this and it seems like it's a fairly desired feature. Or has this been addressed in 5.x? I know you can run multiple mysqld's on the slave, but it could (should) be done in a thread on the slave. -- --

Re: need help with user variables in where clause of sub query

You seem to be coming at SQL with a COBOL perspective. Views are something you typically create just once and they stay updated automatically. They work like tables not like queries. Assigning variables to each column of a view doesn't make any sense (in the SQL sense of "view") as each column

RE: update or insert if necessary?

You should read http://dev.mysql.com/doc/refman/4.1/en/insert.html and maybe http://dev.mysql.com/doc/refman/4.1/en/replace.html > -Original Message- > From: steve [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 28, 2005 8:57 AM > To: mysql@lists.mysql.com > Subject: update or inse

Re: update or insert if necessary?

Actually, you may be more interested in: INSERT . ON DUPLICATE KEY UPDATE - Original Message - From: "steve" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 28, 2005 6:57 AM Subject: update or insert if necessary? I have a situation where I need to update a record for a given key

Re: update or insert if necessary?

http://dev.mysql.com/doc/refman/5.0/en/replace.html - Original Message - From: "steve" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 28, 2005 6:57 AM Subject: update or insert if necessary? I have a situation where I need to update a record for a given key in a MySQL table, but

how use sql_slave_skip_counter to restore slave replication

I'm trying to use teh parameter sql_slave_skip_counter at run-time to restore slave replication. When a slave replication broke due some errors in code, my be a duplicate key, the only working way to restore the replica where to delete the existing record which conflicts whith the ones inserted by

update or insert if necessary?

I have a situation where I need to update a record for a given key in a MySQL table, but insert a record if the key doesn't exist. I could do this by doing a SELECT on the key, then doing an UPDATE if anything comes back, or and INSERT otherwise. This seems rather clunky though, and I'm wonder

is there a type definition similar to the oracle rowtype

Is there in mysql 5 a datatype similar to the oracle rowtype ? To better explain I include the following example which should be working on oracle and don't run on mysql CREATE PROCEDURE pTest() BEGIN declare c cursor for select * from table_test; declare crec c%rowtype; open c;

Re: Need Help Writing a Trigger

Jesse, >Therefore, instead of putting that long calculation in my query every time, >I'm looking for a simpler solution, a more automatic one. CREATE FUNCTION Age( dob DATE, today DATE ) RETURNS INTEGER DETERMINISTIC BEGIN RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0

Replication A->B->C - changes on B are not replicated to C

Hi all i have a question related to replication on 4.0.x. I have the following setup: A -> B -> C A is a Master and writes all changes to its binlog (meaning there is no filter set). B acts as Slave from A and as Master for C. As a Slave B has filters set, so not every change of every databas

Re: "Got error 12 from storage engine" on ORDER BY

Hello. > Strangely, this problem does not appear when mysqld is restarted and I > retry the query. When I wait a day, the error starts to appear again. It seems like a memory leak. However we can't be sure, may be due some coincidence memory is thrilled by client threads. In my opinion your

Re: set sql mode

Hello. >But now i wish sql mode only include "REAL_AS_FLOAT,PIPES_AS_CONCAT". Do you want this: mysql> set @@sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT'; Query OK, 0 rows affected (0.00 sec) mysql> select @@sql_mode; +---+ | @@sql_mode| +

Re: R: field truncate trying to using 'show slave status'

Hello. > The info returned by 'show slave status' are available in some table, >when I can retrieve data using normal select statement ? As far as I know - not. AESYS S.p.A. [Enzo Arlati] wrote: > Normal query , the one started with the select statement works well, so I'm > thinking

Re: Problem With FulltText Index and VarChar

Hello. > #1054 - Unknown column 'CommentsIDX' in 'where clause' > My table structure contains:FULLTEXT KEY `CommentsIDX` >(`Comments`) You should use column names not index names in your queries. Please, provide CREATE statement for your tables and problematic queries. With this inform

Re: autoincrement for year

Hello. This should be possible with triggers. See: http://dev.mysql.com/doc/refman/5.0/en/triggers.html "Salvatore Celsomino" <[EMAIL PROTECTED]> wrote: >Hi, >it is possible to create a field autoincrement for year. >example: >1/2005 >2/2005 >... >10500/2005 >new

Re: Upgrading to 5.0.15

Hello. Manual recommends to perform an upgrade step by step. So I suggest you to move to 4.1 at first. When you said 'tablespace' have you meant that you're using InnoDB tables? I'm not sure about them, check the change logs to find out any incompatible changes. In case of MyISAM,very often

Re: autoincrement for year

2005/12/28, Salvatore Celsomino <[EMAIL PROTECTED]>: > Hi, > it is possible to create a field autoincrement for year. > example: > 1/2005 > 2/2005 > ... > 10500/2005 > new year-- > 1/2006 > 2/2006 > > This could be of interest : http://dev.mysql.com/doc/refman/5.0/en/

Re: Need Help Writing a Trigger

It's not as simple as that. First, if you subtract the curdate() from the birthday (or vice versa), you end up with some large number that isn't the actual age at all. So, the calculation is a bit more complicated than that. Also, I'm not interested in their current age, but their age at the t

LEFT JOIN combined with JOIN

Hello List I am running a query to find accounts that not represented in an invoice: Table structure is as follows: Invoice -- invoice_line_number account_number Account_Parameters - account_id parameter_id parameter_value Parameter_Library -

R: field truncate trying to using 'show slave status'

Normal query , the one started with the select statement works well, so I'm thinking about a workaround if possible. The info returned by 'show slave status' are available in some table, when I can retrieve data using normal select statement ? regards, Enzo -Messaggio originale- Da: Gle

autoincrement for year

Hi, it is possible to create a field autoincrement for year. example: 1/2005 2/2005 ... 10500/2005 new year-- 1/2006 2/2006

u kwzzgitys

The original message was received at Wed, 28 Dec 2005 09:08:42 +0100 from gerwinski.de [143.125.227.91] - The following addresses had permanent fatal errors - - Transcript of session follows - while talking to lists.mysql.com.: >>> MAIL From:[EMAIL PROTECTED] <<< 501 [EMAIL PRO

set sql mode

It showing "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI" when i set sql mode is "ansi". But now i wish sql mode only include "REAL_AS_FLOAT,PIPES_AS_CONCAT". Can I achieve it?