Re: MySQL slave error when creating view
Yes we faced the same prbs with 5.0.24, pls upgrade to 5.0.37 community / Darshan - Original Message - From: Baron Schwartz [EMAIL PROTECTED] To: Tim Lucia [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Saturday, April 14, 2007 6:53:17 PM (GMT+0530) Asia/Calcutta Subject: Re: MySQL slave error when creating view Sounds like a bug to me. It's likely fixed in newer versions; a lot of view/function/sproc replication bugs have been fixed. But if not, you should report it. Tim Lucia wrote: Nobody has any idea(s) on this? -Original Message- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: Friday, April 06, 2007 7:11 PM To: [EMAIL PROTECTED] Subject: MySQL slave error when creating view I ran the following DDL on our master today: CREATE OR REPLACE VIEW REF_TRANSFER_PREFERENCE ( IORG_UID, TDISTRICT_NAME, IORGDISPLAY, IORG ) AS SELECT 0, TDISTRICT_NAME, IORG_UID, IORG_UID FROM REF_PREFERENCE ; The master happily created the view. The slave however dies with: 070405 10:48:23 [ERROR] Slave: Error 'Duplicate column name 'IORG_UID'' on query. Default database: 'pildb'. Query: 'CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL SECURITY DEFINER VIEW `REF_TRANSFER_PREFERENCE` AS SELECT 0, TDISTRICT_NAME, IORG_UID, IORG_UID FROM REF_PREFERENCE', Error_code: 1060 I can work around it by redefining the view DDL as: CREATE OR REPLACE VIEW REF_TRANSFER_PREFERENCE ( IORG_UID, TDISTRICT_NAME, IORGDISPLAY, IORG ) AS SELECT 0 as IORG_UID, TDISTRICT_NAME, IORG_UID as IORGDISPLAY, IORG_UID as IORG FROM REF_PREFERENCE ; And the slave is happy. Is this a bug? Anyone care to comment? CONFIGURATION: storage_engine MyISAM table_type MyISAM updatable_views_with_limit YES version 5.0.24-standard-log version_comment MySQL Community Edition - Standard (GPL) version_compile_machine x86_64 version_compile_os unknown-linux-gnu Thanks, Tim -- MySQL Replication Mailing List For list archives: http://lists.mysql.com/replication To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Replication Mailing List For list archives: http://lists.mysql.com/replication 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 overwrite existing file with SELECT .. INTO?
At 08:14 AM 4/13/2007, Amer Neely wrote: I'm using MySQL 5.0.21 and am trying to find out if it is possible to overwrite an existing file when using a 'SELECT ... INTO' command from the command line. Is there another parameter that can do this? I've looked through the online reference manual, but found no specific help there. -- Amer Neely Amer, Why can't you do: drop table if exists mynewtable;select .. into mynewtable Mike :) Thanks, but you missed the part about '.. an existing file..'. I'm hoping there is an option for the SELECT INTO to do this. I'm doing some extensive testing and it's getting tiresome having to delete a file each time I run the test. -- Amer Neely w: www.softouch.on.ca/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1111 (Weird.)
Ok the Query i am trying to run is very long with many joins it is only run a couple of times every two weeks for payroll. It worked well on 4.1alpha and we have now upgraded to 5.0.24a-log Running on 64 Bit AMD Linux Each section of the query works correctly and if i run only a two or 3 day selection no problems but when i run 4 or more days i get this error. Error code I think it has to do with the size of the query not the query itself. As if i remove a large section out and run it it will work. Remove a different section and also it will work. It has about 30 joined sections and in those sections it has many unions. Any ideas would be greatly apperciated. -- Regards Michael Cole -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database with a lot of views
In our application, each user has an won set of tables. We're migrating to a shared table set, with an extra field in each table, identifying the user a particular rows belongs to. In order to keep database changes minimal, we're thinking of using an own set of views for each user. Is there huge overhead in database containing a lot (millions) of views? Yours faithfully, Olexandr Melnyk
Problem with mysqldump and local-infile
Hi, I want to backup my databases with mysqldump, but mysqldump won't run because I use the 'local-infile=1' option in the my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock local-infile= 1 This is because I want php and other clients to use local-infile. This works, by when I start mysqldump I got this error: ~ # mysqldump mysqldump: unknown variable 'local-infile=1' So, what is wrong? As far as I know local-infile is a valid option to put in the my.cnf. Is there a way to unset the local-infile option and start then start mysqldump or something? Or any other solution? Grtz, Mark van Herpen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqldump and local-infile
It looks to me that local-infile is a command-line parameter to mysql client mysql --local-infile -u user dbname I've not been able to find this option elsewhere. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Mon, April 16, 2007 12:14, Mark van Herpen wrote: Hi, I want to backup my databases with mysqldump, but mysqldump won't run because I use the 'local-infile=1' option in the my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock local-infile= 1 This is because I want php and other clients to use local-infile. This works, by when I start mysqldump I got this error: ~ # mysqldump mysqldump: unknown variable 'local-infile=1' So, what is wrong? As far as I know local-infile is a valid option to put in the my.cnf. Is there a way to unset the local-infile option and start then start mysqldump or something? Or any other solution? Grtz, Mark van Herpen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplication in sles9 for my.cnf
Hi, I have noticed in the man page for mysql from MySQL-client-community-5.0.37-0.sles9.i586.rpm there is a repetition in the my.cnf files read by mysql. I am wondering if this is at all significant. This is what man mysql shows for the MySQL-client-community-5.0.37-0.sles9.i586.rpm install, mysql Ver 14.12 Distrib 5.0.37, for pc-linux-gnu (i686) using readline 5.0 Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf /etc/my.cnf So the order is /etc/my.cnf ~/.my.cnf /etc/my.cnf On Debian Sarge with 4.1.11, the order is more sensible, mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386) Default options are read from the following files in the given order: /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf The order on Sarge, 4.1.11 is /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf Cheers, -Janek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqldump and local-infile
Mark van Herpen [EMAIL PROTECTED] writes: Hi, I want to backup my databases with mysqldump, but mysqldump won't run because I use the 'local-infile=1' option in the my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock local-infile= 1 This is because I want php and other clients to use local-infile. This works, by when I start mysqldump I got this error: ~ # mysqldump mysqldump: unknown variable 'local-infile=1' So, what is wrong? As far as I know local-infile is a valid option to put in the my.cnf. Is there a way to unset the local-infile option and start then start mysqldump or something? Or any other solution? If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the loose- prefix: [client] loose-local-infile=1 http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html Grtz, Mark van Herpen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
[snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet - SOLVED
[snip] [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. [/snip] select store.storeid, store.stname from store where store.storeid not in ( select transaction.storeid from transaction where substring(transaction.created, 1, 10) date_sub(current_date(), interval 1 day) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL IPv6 Support?
Hello, I have searched every corner of the internet for info on MySQL's IPv6 support, but have not found much information. I have a Linux box with an IPv6 address. It can be pinged from other machines (Linux and Windows) by its IPv6 address. However, I cannot get this command to work: mysql -h ::1 This gives me an “Unknown Host” error. Has anyone ever gotten an example like that to work? This is the IPv6 equivalent of mysql -f 127.0.0.1 (which works on that machine). Obviously, my goal is to do something a bit more complicated, but I can't even get that simple use of IPv6 to work. Any help appreciated. -- View this message in context: http://www.nabble.com/MySQL-IPv6-Support--tf3584588.html#a10016526 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting a field to NULL (default value)
Hi: I have a table with a column of type char(120). default is NULL How do I a set a 'cell' back to NULL? Example: alter customers set all_colslist=NULL where ID=57; returns a syntax error. Mysql version 4.0.20 thanks -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting a field to NULL (default value)
On Monday 16 April 2007 07:56, Tim Johnson wrote: Hi: I have a table with a column of type char(120). default is NULL How do I a set a 'cell' back to NULL? Example: alter customers set all_colslist=NULL where ID=57; returns a syntax error. Mysql version 4.0.20 thanks This is embarrassing I used alter instead of update. I should not start work before having all of my coffee. :-) Correct syntax is update customers set all_colslist=NULL where ID=57; | sorry! tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connector J 5.1.0 is Production ?
In my Connector/J Costumer Page the 5.1.0 is a Production release... and the dev.mysql.com is ALPHA... really ? JDBC 4.0 is ready to use in production enviroments ? -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://www.go-java.com/blog*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Question
I have a couple of very simple tables to handle a client signin site: The client table has the following fields: client_id int(11) primary key auto_increment first_name char(90) last_name char(90) The signin table has the following fields record_id int primary key auto_increment client_id int date datetime Essentially, the client enters his id and it creates a record in the signin table. I need a query that can identify all the clients who signed in for the first time during a specific month. I have fought this one for a couple of days now and just can't seem to get it. -- Aaron Clausen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Aaron, Aaron Clausen wrote: I have a couple of very simple tables to handle a client signin site: The client table has the following fields: client_id int(11) primary key auto_increment first_name char(90) last_name char(90) The signin table has the following fields record_id int primary key auto_increment client_id int date datetime Essentially, the client enters his id and it creates a record in the signin table. I need a query that can identify all the clients who signed in for the first time during a specific month. I have fought this one for a couple of days now and just can't seem to get it. I think you can break the problem into a couple of steps: 1) find the first login for each client. 2) eliminate all but the ones in the month. You can't do step 2 first because that would destroy your knowledge of whether a signin record is a client's first. select client_id, min(date) from signin group by client_id; Now you know the first time each client signed in. From here you can go several ways. One is to just add a HAVING clause. select client_id, min(date) from signin group by client_id having min(date) between ? and ?; I hope that helps, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max columns in a tabel in MyISAM storage engine
Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: state=sending data for too long
can u please tell us on which all columns in this table have indexes
On 4/16/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hello,
For the following query:
select unix_timestamp(beginTime) as bTime,myTable.* from myTable Where
nodeID=2 AND flavor In('sim','ofl') AND (deactive= 0 OR
deactive=1176682388) AND unix_timestamp(entryTime)=1176682388 AND
beginTime=from_unixtime(1546300816) AND elementID In
(491507,491508,491509,491510,491511,491512,491513,491514,491515,491516,491517,491518,491519,491520)
Order by beginTime desc limit 14
EXPLAIN shows
++-+---+--+---+-+-+---++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+---+--+---+-+-+---++-+
| 1 | SIMPLE | myTable | ref | PRIMARY | PRIMARY | 4 |
const | 403782 | Using where |
++-+---+--+---+-+-+---++-+
Show processlist reveals that the query state sits in sending data for a
very very long time.
Perhaps I need a tunning parameter adjusted. Does anyone know which one,
or have any other ideas?
Thanks,
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
more than 10000 fields in the OR clause
Guys, I know this is a stupid thing but I wanted to know if we have an index on a column X and if i have a query having 1 OR conditions on the field e.g. where X=10 OR x=12 OR x=13 OR x=15.. so on then will it give any benefits in terms of speed? thx - A
Re: more than 10000 fields in the OR clause
Hi Arun, Instead of doing x=10 or x=10, use the IN condition where X in (10,20,30..etc), it would work better. But i guess there is a limit on the number of values specified in IN clause, I am not sure, need to check on that. regards anandkl On 4/17/07, Arun Kumar PG [EMAIL PROTECTED] wrote: Guys, I know this is a stupid thing but I wanted to know if we have an index on a column X and if i have a query having 1 OR conditions on the field e.g. where X=10 OR x=12 OR x=13 OR x=15.. so on then will it give any benefits in terms of speed? thx - A
