Re: how use sql_slave_skip_counter to restore slave replication
other than what Gleb says, you can avoid the error using 'insert ignore into' or better yet 'replace into' for every insert statement AESYS S.p.A. [Enzo Arlati] wrote: 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 the replication process. So if I have a duplicate key 30020 ,I have to remove the record with the id 30020 and the replication can reinsert it's copy of record with id = 30020. This should be difficult to automate so I try another way using the global variable sql_slave_skip_counter. I try to skip 5 records using a statemente like this: set global sql_slave_skip_counter = 5; and then restart the slavre start slave; but the problem is the same and the command 'show slave status' report a skip_counter filed equals 0. Where I am wrong , some can help me ? Regards, Enzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking for all columns null
Is there a simple way of checking whether all the columns in a SELECT are NULL? I know I can do the following: SELECT c1, c2, c3 FROM t WHERE COALESCE(c1, c2, c3) IS NOT NULL but this relies on specifying every column I've selected (which in my actual code is a lot of columns that may change during development). I was hoping there was a convenience syntax for checking whether all the selected columns are NULL, without having to specify each column, e.g. in pseudocode SELECT c1, c2, c3 FROM t WHERE COALESCE(All_Selected_Columns) IS NOT NULL Is there such a thing? Thanks, Yasir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about sql mode = ansi
At 11:50 +0800 12/30/05, wangxu wrote: AS 1.7.3. Running MySQL in ANSI Mode explain: -- Running the server in ANSI mode is the same as starting it with these options (specify the --sql_mode value on a single line): --transaction-isolation=SERIALIZABLE --sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACEIf transaction isolation auto setting as SERIALIZABLE when i setting sql mode equal to "ansi"?On this condition,if the transation-isolation make no effect?What will happened if i set sql mode equal to "ansi" and transation-isolation equal to "READ COMMITTED"? As the referenced manual page indicates, "running the server in ANSI mode" means starting the server with the --ansi option. That's not the same as setting the SQL mode to "ANSI" (--sql-mode=ANSI). Setting the SQL mode does not have any effect on the isolation level. Setting the SQL mode to ANSI is not the same as using --ansi. You can set the isolation level to READ COMMITTED if you like. Using --ansi sets the isolation level as one of its effects. But clients can change the isolation level when they connect if they like. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: "Got error 12 from storage engine" on ORDER BY
[in reply to [EMAIL PROTECTED], 28-12-2005] Hi, >> MySQL 4.1.10a on FreeBSD 5.4-RELEASE-p6 >> ERROR 1030 (HY000): Got error 12 from storage engine >> 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 > first step should be upgrade to 4.1.16. Use MySQL from ports collection, > and if the problem still exists, check if it disappears with official > binaries. Then decrease sizes of different buffers. > > Check if error repeats after FLUSH TABLES. thanks for the tips! Flushing the tables did not clear up the problem, so I have upgraded MySQL and will see how it goes. At http://dev.mysql.com/doc/refman/4.1/en/freebsd.html, I have also found the suggestion to set some kernel tunables in order to increase the maximum memory size of a process. FreeBSD apparently sets this to 512MB. It may be that I am just hitting this limit after MySQL has been running for longer. Thanks again! Kind regards, Walter Hop Transip BV -- Transip BV | http://www.transip.nl/ Hoogwaardige Innovatie | Aangename Zekerheid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about sql mode = ansi
AS 1.7.3. Running MySQL in ANSI Mode explain: -- Running the server in ANSI mode is the same as starting it with these options (specify the --sql_mode value on a single line): --transaction-isolation=SERIALIZABLE --sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACEIf transaction isolation auto setting as SERIALIZABLE when i setting sql mode equal to "ansi"?On this condition,if the transation-isolation make no effect?What will happened if i set sql mode equal to "ansi" and transation-isolation equal to "READ COMMITTED"?
How to insert CURDATE() as default
I forgot to mention - I'm running MySQL 4.0.14 on Linux. And I'm not very knowledgeable on databases. I just use MySQL with Knoda to get the job done. == I've got a MySQL table that I'd like to have the current date, CURDATE(), as the default in a column. I'm using knoda to worj with this table. How do I use knoda to get this done? I can enter CURDATE() in the default using the GridColumns button, but all that does is insert the phrase "CURDATE()". I've got the column with "Date" for the ColummnType. Thanks. -- Marc <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Re: create function with space
Yes,I already read this section.
But, maybe there are haven't relation to my question.
Are the effect of setting IGNORE_SPACE in sql mode allowing add space between
function name and "("?
If my attitude is right,then if i haven't setting IGNORE_SPACE in sql mode,I
shouldn't add space between function name and "(".But fact not like this.
Please note example in my reference.
> - Original Message -
> From: "Gleb Paharenko" <[EMAIL PROTECTED]>
> To:
> Sent: Friday, December 30, 2005 2:04 AM
> Subject: Re: create function with space
>
>
> > Hello.
> >
> > >From the manual:
> >
> > ANSI
> >
> > Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
> > Before MySQL 5.0.3, ANSI also includes ONLY_FULL_GROUP_BY. See Section
> > 1.8.3, ���Running MySQL in ANSI Mode��?
> >
> > wangxu wrote:
> > >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;
> > >END
> > >-
> > >There is a space in function name and there is a space between function
> > >name and "(".In mysql word,this funciton can't be created.But i created
> > >it well.
> >
> >
> > --
> > For technical support contracts, goto https://order.mysql.com/?ref=ensita
> > This email is sponsored by Ensita.NET http://www.ensita.net/
> >__ ___ ___ __
> > / |/ /_ __/ __/ __ \/ /Gleb Paharenko
> > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> ><___/ www.mysql.com
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
Re: Problem With FulltText Index and VarChar
On 12/28/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
> 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 information it'll be easier to help you.
>
Thanks this is the a snippet of the table structure:
CREATE TABLE `properties` (
`id` int(11) unsigned NOT NULL auto_increment,
`UserID` int(11) unsigned NOT NULL default '0',
`Type` enum('Commercial','Residential') NOT NULL default 'Residential',
`Subtype` varchar(64) NOT NULL default '0',
`Zip` varchar(10) NOT NULL default '',
`Heading` varchar(84) NOT NULL default '',
`Address1` varchar(32) NOT NULL default '',
`Address2` varchar(32) default NULL,
.
KEY `TypeSubType` (`Type`,`Subtype`),
KEY `CityHood` (`City`,`Neighborhood`),
FULLTEXT KEY `CommentsIDX`
(`Comments`,`Subtype`,`Heading`,`Zip`,`Address1`,`Neighborhood`,`City`,`Country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=101131 ;
I guess my question is, how do I make sure the full text search is
being done against the CommentsIDX and not just against the individual
fields...
SELECT * FROM properties WHERE MATCH
(Comments,Subtype,Heading,Zip,Address1,Neighborhood,City,Country)
AGAINST (
"search words" WITH QUERY EXPANSION)
Thanks,
Michael
>
>
> Michael Stearne wrote:
> > I am trying to do a fulltext search with a multi-field index using MySQL 4.=
> > 1.15.
> >
> > When I create a full text index of my Comments field which is of type
> > TEXT. I can do a fulltext search fine.
> >
> > But when I add another field (like a varchar or even Text) to that
> > index or change the name of the index I get and error like:
> >
> > SELECT * FROM properties WHERE MATCH (CommentsIDX)
> > AGAINST (
> > "item1 OR item2 AND (item3 AND item4 NOT (item5 OR item6))"
> > WITH QUERY EXPANSION
> > )
> >
> > MySQL said: Documentation
> > #1054 - Unknown column 'CommentsIDX' in 'where clause'
> >
> > My table structure contains:FULLTEXT KEY `CommentsIDX` (`Comments`)
> >
> > Any ideas?
> >
> > Thanks
> >
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__ ___ ___ __
> / |/ /_ __/ __/ __ \/ /Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
><___/ www.mysql.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (ÈðÐÇÌáʾ-´ËÓʼþ¿ÉÄÜÊÇÀ¬»øÓʼþ)RE: set sql mode
thanks - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; Sent: Thursday, December 29, 2005 10:38 PM Subject: (ÈðÐÇÌáʾ-´ËÓʼþ¿ÉÄÜÊÇÀ¬»øÓʼþ)RE: set sql mode An error is only thown during an INSERT or UPDATE statement, otherwise you just get a warning. - ERROR_FOR_DIVISION_BY_ZERO Produce an error in strict mode (otherwise a warning) when we encounter a division by zero (or MOD(X,0)) during an INSERT or UPDATE. If this mode is not given, MySQL instead returns NULL for divisions by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL. Ed -Original Message- From: wangxu [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 28, 2005 7:23 PM To: Gleb Paharenko; mysql@lists.mysql.com Subject: 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: 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| > +---+ > | REAL_AS_FLOAT,PIPES_AS_CONCAT | > +---+ > > > > >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?wangxu wrote: > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create function with space
Hello.
>From the manual:
ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
Before MySQL 5.0.3, ANSI also includes ONLY_FULL_GROUP_BY. See Section
1.8.3, âRunning MySQL in ANSI Modeâ.
wangxu wrote:
>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;
>END
>-
>There is a space in function name and there is a space between function
>name and "(".In mysql word,this funciton can't be created.But i created
>it well.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load balance for mysql servers
Hello. >Is there any good idea to load balance for our mysql servers? I'm not sure, but have a look at "High Performance MySQL" by Jeremy D. Zawodny. This is a snip from it's publically available chapter. ### Real-time data transmission MySQL's replication isn't the ideal vehicle for transmitting real-time or nearly real- time data such as a stock quote feed or an online auction site. In those applications, it's important that the user sees up-to-date data no matter which database server they use. The only way to combat MySQL's lack of any latency guarantee is to implement your own monitoring system. It needs to use some sort of heartbeat to verify that each server has a reasonably up-to-date copy of the data. In the event that a server falls too far behind, the monitoring system needs to proactively remove it from the list of active servers until it can catch up. Of course, you can also build your application in such a way that it updates all the slaves with the newest data. However, that can add a lot of complexity and may not be worth the effort. You'd end up writing a lot of code to handle the exceptional conditions, such as when a single server falls behind or is intermittently inaccessible. Testing and debugging all those situations can be very time-consuming and difficult. As Derek went over this, he thought, "Wouldn't it be cool if MySQL could provide a query response that signified, `Go ask another server, I'm really busy right now'?" This would allow clients to automatically find willing servers in a multihost DNS rotation. For example, the client wants to connect to db.example.com (which is db1, db2, and db3). It connects (randomly) to db2, and the server answers the query with "I'm busy; go ask someone else," whereupon the client knows enough to try db1 or db3. Because the client library would be connecting to the same virtual server, it could transparently disconnect from the busy server and connect to some other (hopefully less busy) server. As a result, all you would need is some automated way for a slave server to know how far behind they are and to shut themselves off from queries when they get too far behind, and you'd have some protection. Of course, this could also be subject to a cascading failure. If all the slaves are very busy, the last thing you'd want is for them to start removing themselves from the pool of available servers. Continue on to Chapter 8 for a deeper discussion of these issues. "lee_mezimedia" <[EMAIL PROTECTED]> wrote: >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 server by random, but sometimes we found > >S2 was very very busy but S3 was a little vacancy. > >Is there any good idea to load balance for our mysql servers? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inconsistent replication?
Hello.
For auto_increment columns MySQL sets in the binary logs INSERT_ID
variable, research binary and relay logs and check if it is set to
the same value. Is it possible that you forgot to change the
master_log_pos and master_log_file after copying the data to the slave?
PaginaDeSpud wrote:
> 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: 'genteya'. Query: 'INSERT INTO `comentarios`
> (id, idcom, nickcom, comentario, reply, ip, fecha) VALUES ('84600',
> '264452', 'cipr22valencia', 'graias por los puntitos cielo pero si hay algo
> que me gustaria mas que salir en primera paguina es conocerte mas jijiji
> aver si te veo por mi *** o me das el tuyo un kiss y toma+++ puntitos ',
> 'S', '81.202.240.73', '1135811423')', Error_code: 1062
> 051228 17:13:35 [ERROR] Error running query, slave SQL thread aborted. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We stopped
> at log 'mysql-bin.04' position 184226200
>
> I don't know why, but primary keys are inserted in slave with different
> (autoincrement) numbers than master, and replication shut down in slave.
>
> CREATE TABLE `comentarios` (
> `index` int(25) NOT NULL auto_increment,
> `id` int(9) NOT NULL default '0',
> `idcom` int(9) NOT NULL default '0',
> `nickcom` varchar(15) NOT NULL default '',
> `comentario` text NOT NULL,
> `reply` char(1) NOT NULL default '',
> `ip` varchar(15) NOT NULL default '',
> `fecha` bigint(20) NOT NULL default '0',
> PRIMARY KEY (`index`),
> KEY `idcom` (`idcom`),
> KEY `id` (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> Any idea?
>
> Thanks.
>
> Ivan Lopez.
> Logosur.
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1 File
Hello. >there, in fact, anything important in that ibdata1 file, or can I just >shut down mySQL, move that file somewhere else, and then re-start mySQL? Yes, this file contains important information. >Is this possible, or am I stuck having my database server off-line for >hours while the backup/restore procedure happens? You can setup a second server (or just run another instance of MySQL on the same machine), copy your databases there using mysqldump or replication, and switch to second copy. Its data could be a bit behind the main server, but you can quickly put it up to date. The general way, which I see is, to perform the dump with --master-data, get log file name and position in it from the dump file. Import the dump to the second instance. FLUSH TABLES WITH READ LOCK on the main server, get new updates which have appeared on the master, with mysqlbinlog utility (use --start-position=N option). Import them to the second copy and switch your applications to it. With replication this should be quite the same (FLUSH TABLES WITH READ LOCK on the master, get information from SHOW MASTER STATUS, use SELECT MASTER_POS_WAIT to put slave up to date). I agree, that this approach is not ideal, however it makes the time when you master is off-line to several minutes. "Gustafson, Tim" <[EMAIL PROTECTED]> wrote: >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 of shrinking it down to a manageable size. I >know that the file is mostly filled with junk now, but I'm also fairly >sure that there is -some- important data store in there...so the >question is this: > >How can I reduce the size of the ibdata1 file down to a manageable size, >without breaking my mySQL server, and without backing up every database >on my system (a 50GB proposition), and then deleting all databases and >the ibdata1 file, and then re-creating everything from the backup? Is >there, in fact, anything important in that ibdata1 file, or can I just >shut down mySQL, move that file somewhere else, and then re-start mySQL? > >Is this possible, or am I stuck having my database server off-line for >hours while the backup/restore procedure happens? > >Thanks for any help you can give! > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how use sql_slave_skip_counter to restore slave replication
Hello. > but the problem is the same and the command 'show slave status' report >a skip_counter filed equals 0. > Where I am wrong , some can help me ? This variable is the number of events from the master that a slave server should skip. sql_slave_skip_counter decrements each time the slave skips one event. Your slave has already skipped 5 events, so the value of events that it should skip is 0. AESYS S.p.A. [Enzo Arlati] wrote: > 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 the replication > process. > So if I have a duplicate key 30020 ,I have to remove the record with the id > 30020 and the replication can reinsert it's copy of record with id = 30020. > This should be difficult to automate so I try another way using the global > variable sql_slave_skip_counter. > > I try to skip 5 records using a statemente like this: > set global sql_slave_skip_counter = 5; > and then restart the slavre > start slave; > > but the problem is the same and the command 'show slave status' report a > skip_counter filed equals 0. > > Where I am wrong , some can help me ? > Regards, Enzo > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to insert CURDATE() as default
I've got a MySQL table that I'd like to have the current date, CURDATE(), as the default in a column. I'm using knoda to worj with this table. How do I use knoda to get this done? I can enter CURDATE() in the default using the GridColumns button, but all that does is insert the phrase "CURDATE()". I've got the column with "Date" for the ColummnType. Thanks. -- Marc <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: How to share databases on dual-boot machines?
Hi James, Check the whole /mnt filesystem hasn't been mounted read-only. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown Sent: Friday, 30 December 2005 2:30 AM To: mysql@lists.mysql.com Subject: Re: How to share databases on dual-boot machines? Thanks for the advice David. I opted for your method 2 and symlinked to my database files which now reside on a FAT32 partition. Unfortunately, I've hit a slight issue which I'd like to run past you, but I do think this could turn out to be a FAT32 problem rather than a MySQL one. Basically, the following error appears when I try to run an UPDATE on the relocated database: > WordPress database error: [Table 'wp_options' is read only] I've tried 'chmod 777 *' on all the "wordpress" database files, but still couldn't get universal write permissions. 'chown mysql *' also failed with an error. An ls -al on the database files now shows this: > # 'jamebrow' is my username > # mysql is running as user 'mysql' > -rwxr-xr-x 1 jamebrow 9063 2006-01-06 13:03 wp_options.frm* > -rwxr-xr-x 1 jamebrow 24 2006-01-07 02:22 wp_options.MYD* > -rwxr-xr-x 1 jamebrow 8192 2005-12-28 15:28 wp_options.MYI* The symlink looks like this: > lrwxrwxrwx 1 mysql 25 2005-12-29 13:40 wordpress -> /mnt/docs/mysql/wordpress/ Does anyone have any ideas? Many thanks, James. Logan, David (SST - Adelaide) wrote: > 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/option-files.html > > 2) move it to /mnt/wordpress and use a symbolic link from /var/lib/mysql > eg. mv /var/lib/mysql/wordpress /mnt ; ln -s /mnt/wordpress > /var/lib/mysql/wordpress see > http://dev.mysql.com/doc/refman/4.1/en/symbolic-links.html > > Regards > > > --- > ** _/ ** David Logan > *** _/ *** ITO Delivery Specialist - Database > *_/* Hewlett-Packard Australia Ltd > _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] > _/ _/ _/ _/ Desk: +618 8408 4273 > _/ _/ _/_/_/ Mobile: 0417 268 665 > *_/ ** > ** _/ Postal: 148 Frome Street, > _/ ** Adelaide SA 5001 > Australia > invent > --- > > -Original Message- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown > Sent: Thursday, 29 December 2005 12:54 PM > To: mysql@lists.mysql.com > Subject: 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 "wordpress" tables live under > /var/lib/mysql/wordpress. > > Numerous searches have revealed how to backup data, but I haven't seen > any clues on how to permanently reloate a database to a different > filepath (I'd like to move it to /mnt/wordpress). Could anyone enlighten > > me please? > > Thanks in advance, > > James. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re inconsistent replication?
Trying to help you with your replication problems is pretty much impossible without more information, such as: mysql versions and a more detailed explanation of your replication setup. I manage setups ranging from 1-14 replication slaves transmitting up to 2 gigabytes of data per day, and none of them have ever had problems that didn't come from user error. :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load balance for mysql servers
Less optimal solution: Create a DNS round-robin alias, say mydatabase.myinternaldomain.com, that point to S2-4. "Optimal" solution: Set up a real load balancer, hardware based or software based (http://www.linuxvirtualserver.org/), and have it configured to distribute your connections. I have quoted optimal as your success depends on how you configure your distribution, but it should still work better than the previous solution. Atle - Flying Crocodile Inc, Unix Systems Administrator On Thu, 29 Dec 2005, lee_mezimedia wrote: > 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 server by random, but sometimes we found > > S2 was very very busy but S3 was a little vacancy. > > Is there any good idea to load balance for our mysql servers? > > > > Thanks a lot! > > > > Lee > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: issue with subquery
Hi Dan, all! Dan Rossi wrote: 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 Call me pedantic, but I get suspicious: Is that a list of the four values "1", "2", "3", "4", or is it rather a single character string "1,2,3,4" ? Your text "static comma seperate list" makes me believe the latter. , 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 (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 Here, SQL syntax clearly specifies that this is a list of 4 integer values. querying SELECT feeds FROM month_totals WHERE customerID=9 AND month_unique=1105 gives me 1,2,3,4 , what seems to be the problem ? Same question as above - a list of 4 values, or a single one? Show us your "create table" for the involved tables, and also sample data of "SELECT feedID FROM feeds". Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: field truncate trying to using 'show slave status'
Mr Ikar of Core Team Lam give me a different answer ( see belowe ): This is MySQL server bug - it specifies wrong length for fields from SHOW statements. We avoided this bug in MyDAC 4 code. Can someone confirm me such bug with mysql, and when it sould be solved ? Mr Paharenko, I route your answer to Mr Ikar, hoping to have more detail on this topic. -Messaggio originale- Da: Gleb Paharenko [mailto:[EMAIL PROTECTED] Inviato: martedì 27 dicembre 2005 17.13 A: mysql@lists.mysql.com Oggetto: Re: field truncate trying to using 'show slave status' Hello. > Is this a limit of mysql ( I got the 5.0.15 wins revision ) or a MYDAC >(rev 3.50) limit or I simply missed something else ? I'm not a MYDAC (Delphi, C++ Builder) expert, but in my opinion, the problem is not in MySQL, because Query Browser and SQLyog use native MySQL C API and have no problems. MYDAC can work with MySQL in two ways - using MySQL client or native MySQL network protocol. Which of these two options are you using? AESYS S.p.A. [Enzo Arlati] wrote: > I'm using mysql 5.0.15 on windows 2000. > I connect to database using MYDAC component, which seems to work well > enough. > I got a problem when I tried to load the resultset returned by a query like > 'show slave status'. > In this case some of the fields are truncated. > As example the first field named 'Slave_IO_State' should have a value like > 'Waiting for master to send event', while the field i got have only 'Waiting > for ma'. > > If I use MysqlBrowser or yog demo , I get the whole string. > > Is this a limit of mysql ( I got the 5.0.15 wins revision ) or a MYDAC (rev > 3.50) limit or I simply missed something else ? > > regards, Enzo > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to share databases on dual-boot machines?
Hi James, all! James Brown wrote: Thanks for the advice David. I opted for your method 2 and symlinked to my database files which now reside on a FAT32 partition. Unfortunately, I've hit a slight issue which I'd like to run past you, but I do think this could turn out to be a FAT32 problem rather than a MySQL one. Right. Search the Linux docs / newsgroups / FAQs / HowTos for info on "mount" options specific to FAT file systems, there you should find more hints. Basically, the following error appears when I try to run an UPDATE on the relocated database: WordPress database error: [Table 'wp_options' is read only] I've tried 'chmod 777 *' on all the "wordpress" database files, but still couldn't get universal write permissions. 'chown mysql *' also failed with an error. FAT file systems store neither access rights nor owners. What is shown to you by "ls -l" is just fake info (not coming from the disk but added by the Linux kernel), but this fake info is also used for checking access. Basically, "mount" has options that allow you to set the user, group, and access rights for all files and directories on that mounted file system. [[...]] Does anyone have any ideas? For me (SuSE 9.3), "man mount" contains this info: === start quote === uid=value and gid=value Set the owner and group of all files. (Default: the uid and gid of the current process.) umask=value Set the umask (the bitmask of the permissions that are not present). The default is the umask of the current process. The value is given in octal. dmask=value Set the umask applied to directories only. The default is the umask of the current process. The value is given in octal. fmask=value Set the umask applied to regular files only. The default is the umask of the current process. The value is given in octal. === end quote === Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to share databases on dual-boot machines?
Thanks for the advice David. I opted for your method 2 and symlinked to my database files which now reside on a FAT32 partition. Unfortunately, I've hit a slight issue which I'd like to run past you, but I do think this could turn out to be a FAT32 problem rather than a MySQL one. Basically, the following error appears when I try to run an UPDATE on the relocated database: WordPress database error: [Table 'wp_options' is read only] I've tried 'chmod 777 *' on all the "wordpress" database files, but still couldn't get universal write permissions. 'chown mysql *' also failed with an error. An ls -al on the database files now shows this: > # 'jamebrow' is my username > # mysql is running as user 'mysql' -rwxr-xr-x 1 jamebrow 9063 2006-01-06 13:03 wp_options.frm* -rwxr-xr-x 1 jamebrow 24 2006-01-07 02:22 wp_options.MYD* -rwxr-xr-x 1 jamebrow 8192 2005-12-28 15:28 wp_options.MYI* The symlink looks like this: lrwxrwxrwx 1 mysql 25 2005-12-29 13:40 wordpress -> /mnt/docs/mysql/wordpress/ Does anyone have any ideas? Many thanks, James. Logan, David (SST - Adelaide) wrote: 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/option-files.html 2) move it to /mnt/wordpress and use a symbolic link from /var/lib/mysql eg. mv /var/lib/mysql/wordpress /mnt ; ln -s /mnt/wordpress /var/lib/mysql/wordpress see http://dev.mysql.com/doc/refman/4.1/en/symbolic-links.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of James Brown Sent: Thursday, 29 December 2005 12:54 PM To: mysql@lists.mysql.com Subject: 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 "wordpress" tables live under /var/lib/mysql/wordpress. Numerous searches have revealed how to backup data, but I haven't seen any clues on how to permanently reloate a database to a different filepath (I'd like to move it to /mnt/wordpress). Could anyone enlighten me please? Thanks in advance, James. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Dan Rossi <[EMAIL PROTECTED]> wrote on 12/29/2005 07:19:13 AM: > Thanks for your kind words of opinion, if you feel you have a better > way please do go ahead , i am going to show you the sql i ended up > using which was a union to append the current summary at the end, i > then had to use php afterwards to add up the totals as i was getting > unexpected results when grouping by month as it tended to play with the > calculations. Dan, I said I would help and I am offering to do so. If you would rather have someone else take over, please just say so and I will back off. This forum is the best place I have ever found for getting all kinds of crazy help and if you prefer someone else, I understand. I need some information about your table designs. Would you please post the SHOW CREATE TABLE statement results for the following tables: feed_usage, customers, feeds, producers_join, and month_totals? For example: SHOW CREATE TABLE feed_usage\G (the \G makes the output vertical, much less wrapping) I think I understand your various table relationships as you seem to set them up well in your example clause. FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) AND fu.month!=DATE_FORMAT(NOW(),'%m%y') The funky numbers from your initial attempts will more than likely have been caused by several tables being joined having multiple rows of matching data. Not all data-based reports can exist as single statements. It's a limitation of the SQL language that when you want to do calculations based on the results of aggregation (SUM(), COUNT(), AVG(), etc.) , you need a second or more rounds of processing especially if you are aggregating several sets of data (usage data, bandwidth data, billing data, etc.). Temporary tables are the preferred place to store any intermediate results as they are specific to the connection that creates them (In fact the query engine generates at least one temporary table for every subquery you do). I don't want you to think I am completely against subqueries, I am not. It's just that you were not exactly using them to their full potential. If we keep our wits about us, we can get through this. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: set sql mode
An error is only thown during an INSERT or UPDATE statement, otherwise you just get a warning. - ERROR_FOR_DIVISION_BY_ZERO Produce an error in strict mode (otherwise a warning) when we encounter a division by zero (or MOD(X,0)) during an INSERT or UPDATE. If this mode is not given, MySQL instead returns NULL for divisions by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL. Ed -Original Message- From: wangxu [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 28, 2005 7:23 PM To: Gleb Paharenko; mysql@lists.mysql.com Subject: 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: 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| > +---+ > | REAL_AS_FLOAT,PIPES_AS_CONCAT | > +---+ > > > > >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?wangxu wrote: > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT on GROUP BY?
On 28/12/2005, [EMAIL PROTECTED] wrote: > I don't think there is any way with plain-old SQL (extended or > otherwise) to do it in a single statement (unless you are doing it > iteratively - that is: in a stored procedure and row-by-row). USE test; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( i TINYINT UNSIGNED NOT NULL, c CHAR(1) NOT NULL, PRIMARY KEY (i, c) ); INSERT INTO foo VALUES (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'x'), (2, 'y'), (2, 'z'); SELECT DISTINCT f1.i, (SELECT GROUP_CONCAT(f2.c) FROM foo f2 WHERE f2.i = f1.i AND 2 > (SELECT COUNT(*) FROM foo f3 WHERE f3.i = f2.i AND f3.c < f2.c ) ) cc FROM foo f1; Probably not useful in production code for performance reasons, and one should be careful with duplicate rows (not allowed in this example). -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Thanks for your kind words of opinion, if you feel you have a better way please do go ahead , i am going to show you the sql i ended up using which was a union to append the current summary at the end, i then had to use php afterwards to add up the totals as i was getting unexpected results when grouping by month as it tended to play with the calculations. Most of the variables setup are for ease of reading because its such a huge query, i could have easily put the queries into the appropriate places, but i still for instance am required to send say the @customerID variable to the sub queries to return a sum of results, mind you it is not at all possible to do joins for any of this, i was needing to get certain values and caulcations i could not obtain from a sum, group, join of each row. If you think i am an idiot go ahead say so as you already are, im self taught and still learning 6 years later .. Im not perfect and there is always room for improvment hence why Ive posted to the list for help/recommendations or else I usually never post. (SELECT @customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.stats_date,'%M') AS month_long, @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage, @month_totals:=(SELECT month_total FROM month_totals WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) AND fu.month!=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC) UNION (SELECT @customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.stats_date,'%M') AS month_long, @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage, @month_totals:=(SELECT (c.monthly_price + (c.exceed_price * IF ([EMAIL PROTECTED] < 0,@total_bandwidth-c.bandwidth_limit,0))) FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] GROUP BY fu.month, fu.customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) AND fu.month=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC) On 29/12/2005, at 5:19 PM, [EMAIL PROTECTED] wrote: 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 a FUNCTION or a STORED PROCEDURE or a VIEW. Please, get back to the basics. Re-read the appropriate parts of the manual and stop trying to make this harder than it should be. At most, this will take anywhere from 2 to 5 statements. You seem to have become so impressed with subqueries that you are trying to make a square peg fit into a round hole. IMHO using subqueries is not the optimal, effective, preferred, recommended, or suggested way to write this query. I am more than willing to help you to refactor your query and I am sure there will be others on this list if you don't want to deal with me any more. But please take my advice and take the simpler, more direct approach. Respectfully, Shawn Green Database Admini
Re: STOPPING server from pid file
Hi, Did you check your error log file? Is there allready a pid file in the directory? You have to delete this one first. Hope this helps :-) Danny ali asghar torabi parizy wrote: hi to all i have mysql5.1 installed on fedoracore3 it was worked sucsessfully since yesterday. but when i run mysql_safe script today, it prompt following error: STOPPING server from pid file /usr/local/mysql/var/localhost.localdomain.pid please help me. i tierd.i think that i have to leave fc3 and work with better platform that be compatible with mysql. what is your suggestions? - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
STOPPING server from pid file
hi to all i have mysql5.1 installed on fedoracore3 it was worked sucsessfully since yesterday. but when i run mysql_safe script today, it prompt following error: STOPPING server from pid file /usr/local/mysql/var/localhost.localdomain.pid please help me. i tierd.i think that i have to leave fc3 and work with better platform that be compatible with mysql. what is your suggestions? - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
R: inconsistent replication?
Sorry, I didn't understand your replay. Is not clear to me if you are talking about a mysql bug and there is some solution available. Please, Can you expand more on your answer. Regards, Enzo -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato: giovedì 29 dicembre 2005 9.01 A: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Oggetto: Re: inconsistent replication? Hi, I have had same issue for very long time - but managed. I see this as a "bug / issue" to be handled by MySQL - because the logfile/replicationfile should not add more data then the orig SQL from program - in this case is added the auto-increment, which is dealt with internal by MySQL. Best regards Peter Rasmussen Copenhagen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inconsistent replication?
Hi, I have had same issue for very long time - but managed. I see this as a "bug / issue" to be handled by MySQL - because the logfile/replicationfile should not add more data then the orig SQL from program - in this case is added the auto-increment, which is dealt with internal by MySQL. Best regards Peter Rasmussen Copenhagen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
