Auto Install mySQL
Hi All, Does anybody have some advise on automating the install of mySQL and our DB on a PC/Laptop with no user interaction required. Does the SETUP.EXE take any paramaters to auto install without the user dialogs? Thanks, Jim
Re: Need help with triggers
Daevid, >> > > This is my first trigger I'm trying to write. >> > > I have two tables. 'stores' and 'zipcodes'. >> > > I want to automatically set the latitude and longitude of the store using >> > > it's zipcode lookup in the zipcode table. >> > > DELIMITER $$; >> > > DROP TRIGGER `store_coord`$$ >> > > create trigger `store_coord` BEFORE INSERT on `stores` >> > > for each row BEGIN >> > > UPDATE stores AS s, zipcodes AS z >> > > SET s.latitude = z.latitude, >> > > s.longitude = z.longitude >> > > WHERE s.zip = z.zip AND s.id = NEW.id; >> > > END; >> > > $$ Lose the UPDATE clause. All you need (and all that's permitted) is SET NEW ... And lose the WHERE clause. It's redundant (also not permitted). If you need a value from another table, DECLARE a var and write a SELECT INTO for it. (BTW, if your database looks up latitude & longitude from zipcodes, why bother copying them into store_coord?) PB This is my first trigger I'm trying to write. I have two tables. 'stores' and 'zipcodes'. I want to automatically set the latitude and longitude of the store using it's zipcode lookup in the zipcode table. DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET s.latitude = z.latitude, s.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ First things first ... Updating a row that doesn't exist yet (BEFORE INSERT trigger) doesn't make sense :-) Why not simply adjust it like this: new.latitude := new.longitude := Given that it's a "before insert", modifying the NEW. values, these will be stored in the table. So when I do this: insert into `stores` (id, name, zip) values (10,'test company', 14526); I get this error Error Code : 1442 Can't update table 'stores' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. I must not be doing something right, I've tried all these combinations and ALL give that same error message: DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude = z.latitude, NEW.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude := z.latitude, NEW.longitude := z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` AFTER INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET s.latitude = z.latitude, s.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ What am I doing wrong? No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with triggers
I've never written a trigger, but the error implies that you can't update the stores table when in a trigger ON the stores table. This makes sense, especially if you consider an 'UPDATE' trigger - the trigger would become recursive. So, rather than deciding which combination are recursive and which aren't, maybe there's a general rule that you can't change the table on which the trigger is placed? Hopefully someone will let you (and me) know if I don't know what I'm talking about! Quentin -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, 16 May 2006 2:41 p.m. To: 'Martijn Tonies'; mysql@lists.mysql.com Subject: RE: Need help with triggers > > This is my first trigger I'm trying to write. > > > > I have two tables. 'stores' and 'zipcodes'. > > > > I want to automatically set the latitude and longitude of > the store using > > it's zipcode lookup in the zipcode table. > > > > DELIMITER $$; > > > > DROP TRIGGER `store_coord`$$ > > > > create trigger `store_coord` BEFORE INSERT on `stores` > > for each row BEGIN > > UPDATE stores AS s, zipcodes AS z > > SET s.latitude = z.latitude, > > s.longitude = z.longitude > > WHERE s.zip = z.zip AND s.id = NEW.id; > > END; > > $$ > > First things first ... > > Updating a row that doesn't exist yet (BEFORE INSERT trigger) > doesn't make sense :-) > > Why not simply adjust it like this: > new.latitude := > new.longitude := > > Given that it's a "before insert", modifying the NEW. values, > these will be stored in the table. > > So when I do this: > > > > insert into `stores` (id, name, zip) values (10,'test > company', 14526); > > > > I get this error > > > > Error Code : 1442 > > Can't update table 'stores' in stored function/trigger because it is > > already used by statement which invoked this stored function/trigger. I must not be doing something right, I've tried all these combinations and ALL give that same error message: DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude = z.latitude, NEW.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude := z.latitude, NEW.longitude := z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` AFTER INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET s.latitude = z.latitude, s.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with triggers
> > This is my first trigger I'm trying to write. > > > > I have two tables. 'stores' and 'zipcodes'. > > > > I want to automatically set the latitude and longitude of > the store using > > it's zipcode lookup in the zipcode table. > > > > DELIMITER $$; > > > > DROP TRIGGER `store_coord`$$ > > > > create trigger `store_coord` BEFORE INSERT on `stores` > > for each row BEGIN > > UPDATE stores AS s, zipcodes AS z > > SET s.latitude = z.latitude, > > s.longitude = z.longitude > > WHERE s.zip = z.zip AND s.id = NEW.id; > > END; > > $$ > > First things first ... > > Updating a row that doesn't exist yet (BEFORE INSERT trigger) > doesn't make sense :-) > > Why not simply adjust it like this: > new.latitude := > new.longitude := > > Given that it's a "before insert", modifying the NEW. values, > these will be stored in the table. > > So when I do this: > > > > insert into `stores` (id, name, zip) values (10,'test > company', 14526); > > > > I get this error > > > > Error Code : 1442 > > Can't update table 'stores' in stored function/trigger because it is > > already used by statement which invoked this stored function/trigger. I must not be doing something right, I've tried all these combinations and ALL give that same error message: DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude = z.latitude, NEW.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` BEFORE INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET NEW.latitude := z.latitude, NEW.longitude := z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` AFTER INSERT on `stores` for each row BEGIN UPDATE stores AS s, zipcodes AS z SET s.latitude = z.latitude, s.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "optemizer" for mySQL!
Googling for "mysql profiler" brings a link: http://amr.activechannel.net/myprofiler.htm Well... that looks like something really good. I have a question for all of list subscribers: has anyone use this tool? Does it really do what it pretends to do? On 5/15/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: I'm not sure what you mean by profiler -- where are you getting your information from? It's not referenced in the article I sent. If you mean checking to see if indexes are being used during a query, check out the EXPLAIN syntax. http://dev.mysql.com/doc/refman/5.0/en/explain.html -Sheeri On 5/14/06, Jim <[EMAIL PROTECTED]> wrote: > > Thanks Sheeri, > > How do you run the profiler and what does it do? > Have recently had to add some indexes to various tables to improve > performance so does not seen to be running automatically. > > > > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Saturday, 13 May 2006 2:05 AM > To: Jim > Cc: mysql@lists.mysql.com > Subject: Re: "optemizer" for mySQL! > > http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ > > The MySQL server has it built in. > > -Sheeri > > On 5/8/06, Jim <[EMAIL PROTECTED]> wrote: > > Hi All, > > > > > > > > Didn't know there was an "optemizer" for mySQL. > > > > Where can I get it from? > > > > > > > > Thanks > > > > Jim > > > > > > > > > > > > Best regards, > > > > > > > > Jim Clark > > Project Manager > > Multilink Systems > > Ph: 03 9425 9400 > > Fax: 03 9425 9811 > > > > > > > > > > > > > -- > 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble pulling the Trigger
I'm having some trouble writing this trigger and wasn't sure what I'm doing wrong. My goal is after a row has been added to tcphdr it will pull data from several other tables based on the NEW rows event.cid and event.timestamp and insert it into acid_event. However I'm not sure how to tell the WHERE portion what the NEW cid and timestamp is. DELIMITER | CREATE TRIGGER tcphdr_trig AFTER INSERT ON tcphdr FOR EACH ROW BEGIN INSERT INTO acid_event (sid, cid, signature, sig_name, sig_class_id, sig_priority, timestamp, ip_src, ip_dst, ip_proto, layer4_sport, layer4_dport) SELECT event.sid as sid, event.cid as cid, signature, sig_priority, sig_class_id, timestamp, ip_src, ip_dst, ip_proto, tcp_sport as layer4_sport, tcp_dport as layer4_dport FROM event INNER JOIN signature ON (signature = signature.sig_id) INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid) LEFT JOIN tcphdr ON (event.sid=tcphdr.sid AND event.cid=tcphdr.cid) WHERE event.cid = NEW.cid AND event.timestamp = NEW.timestamp; END; |ERROR 1054 (42S22): Unknown column 'timestamp' in 'NEW' mysql> delimiter ; mysql> show columns from event; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | signature | int(10) unsigned | NO | MUL | | | | timestamp | datetime | NO | PRI | | | +---+--+--+-+-+---+ 4 rows in set (0.03 sec) mysql> show columns from acid_event; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | signature| int(10) unsigned | NO | MUL | | | | sig_name | varchar(255) | YES | MUL | | | | sig_class_id | int(10) unsigned | YES | MUL | | | | sig_priority | int(10) unsigned | YES | MUL | | | | timestamp| datetime | NO | PRI | | | | ip_src | int(10) unsigned | YES | MUL | | | | ip_dst | int(10) unsigned | YES | MUL | | | | ip_proto | int(11) | YES | MUL | | | | layer4_sport | int(10) unsigned | YES | MUL | | | | layer4_dport | int(10) unsigned | YES | MUL | | | +--+--+--+-+-+---+ 12 rows in set (0.03 sec) mysql> show columns from signature; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sig_id | int(10) unsigned | NO | PRI | | auto_increment | | sig_name | varchar(255) | NO | MUL | | | | sig_class_id | int(10) unsigned | NO | MUL | | | | sig_priority | int(10) unsigned | YES | | | | | sig_rev | int(10) unsigned | YES | | | | | sig_sid | int(10) unsigned | YES | | | | +--+--+--+-+-++ 6 rows in set (0.02 sec) mysql> show columns from iphdr; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | ip_src | int(10) unsigned | NO | MUL | | | | ip_dst | int(10) unsigned | NO | MUL | | | | ip_ver | tinyint(3) unsigned | YES | | | | | ip_hlen | tinyint(3) unsigned | YES | | | | | ip_tos | tinyint(3) unsigned | YES | | | | | ip_len | smallint(5) unsigned | YES | | | | | ip_id| smallint(5) unsigned | YES | | | | | ip_flags | tinyint(3) unsigned | YES | | | | | ip_off | smallint(5) unsigned | YES | | | | | ip_ttl | tinyint(3) unsigned | YES | | | | | ip_proto | tinyint(3) unsigned | NO | | | | | ip_csum | smallint(5) unsigned | YES | | | | +--+--+--+-+-+---+ 14 rows in set (0.03 sec) mysql> show columns from tcphdr; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--
Re: Need help with procedure
Barry wrote: Hello everyone! I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime To get data that isn't there from SQL, you pretty much need a join to data which _is_ there. So create a calendar table, for example calendar(d DATE), and populate it with one row per day of interest to you. (There is an example under 'Make a calendar table' at http://www.artfulsoftware.com/queries.php). Then write your query as something like ... SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM calendar AS cal LEFT JOIN clicks ON cal.d = clicks.DATE(c_clicktime) WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime; PB The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Mysql version is: 3.23.54 Barry -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting sql_log_bin
On Monday 15 May 2006 05:10 am, Mattias Andersson wrote: > Hi. > > I can't figure out how to set the session variable SQL_LOG_BIN when > using the mysql command-line client. > I want to do something like: #mysql -u root -pmypasswd --sql_log_bin = 0 > < somefile.sql. could be the fact that you have spacing between your option. Try: #mysql -u root -pmypasswd --sql_log_bin=0 < somefile.sql. instead. -- Chris White PHP Programmer/DB Monkey Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem running mysql_install_db
Check out line 1 of the fill_help_tables.sql file. (on my installation it was in /usr/share/mysql) See if there is an errant ". There shouldn't be, of course, but that's the problem according to MySQL. -Sheeri On 5/15/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I start from an up-to-date fedora core 4 (except that I've not rebooted lately, so I have newer kernels than I'm now running). This includes mysql-4.1.16-1.FC4.1.i386.rpm mysql-devel-4.1.16-1.FC4.1.i386.rpm mysql-server-4.1.16-1.FC4.1.i386.rpm mysqlclient10-3.23.58-6.i386.rpm I'm trying to follow directions in http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html Transcript: # su mysql bash-3.00$ cd /var/lib bash-3.00$ mysql_install_db Installing all prepared tables Fill help tables ERROR: 1064 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 '' at line 1 060514 21:39:56 [ERROR] Aborting 060514 21:39:56 [Note] /usr/libexec/mysqld: Shutdown complete WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED! The "HELP" command might not work properly To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h don-eve.dyndns.org password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com bash-3.00$ What's the error in syntax? I'm now simply following the directions above that say to use mysqlbug and tell you what I did. I can't tell for sure,but it looks like I'm supposed to just mail this buffer. That's another problem. I seem to be in emacs (not the right one) and not in a mail buffer ... I'll try to mail it anyway... >Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: <[ non-critical | serious | critical ] (one line)> >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-4.1.16 (Source distribution) >C compiler:i386-redhat-linux-gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8) >C++ compiler: i386-redhat-linux-g++ (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8) >Environment: System: Linux don-eve.dyndns.org 2.6.15-1.1831_FC4 #1 Tue Feb 7 13:37:42 EST 2006 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --host=i386-redhat-linux Thread model: posix gcc version 4.0.2 20051125 (Red Hat 4.0.2-8) Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing' CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 28 06:49 /lib/libc.so.6 -> libc-2.3.6.so -rwxr-xr-x 1 root root 1481808 Mar 14 05:31 /lib/libc-2.3.6.so -rw-r--r-- 1 root root 2519920 Mar 14 05:00 /usr/lib/libc.a -rw-r--r-- 1 root root 238 Mar 14 04:26 /usr/lib/libc.so lrwxrwxrwx 1 root root 10 Nov 19 07:11 /usr/lib/libc-client.a -> c-client.a lrwxrwxrwx 1 root root 20 Nov 19 07:11 /usr/lib/libc-client.so -> libc-client.so.2004g -rwxr-xr-x 1 root root 800272 Dec 1 11:58 /usr/lib/libc-client.so.0 -rwxr-xr-x 1 root root 828604 Nov 17 05:25 /usr/lib/libc-client.so.2004g Configure command: ./configure '--build=i386-redhat-linux' '--host=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--s
Re: need help in updating old binary files to database slave
I've had this same problem -- if the database loses connection at the same time the log file flushes, you need to SET MASTER_LOG_FILE and MASTER_LOG_POS again and restart. I believe this probably qualifies as a bug if you want to report it. The reason the slave isn't updating log5 is because it's at the end of the logfile -- check out the position, and then check out the length of the file. -Sheeri On 5/15/06, balaraju mandala <[EMAIL PROTECTED]> wrote: Hi Comunity, I need some help from you. In replication i want to update old binary files, but server is not updating old files. It is updatin only current using binarylog only. The scenario is, my master is updating log-5 file(say) and slave is reading data from it, and updating itself to current changes. but connection is lost between master and slave. After the connection is established the new log-6 file is update by master but at slave side its only log-5. slave is not updating log-5 though connection is ok. what i have to do so that the old binary file have to be updated in slave. regards, bala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with prcedures
You cannot get data out of a database if it's not in the database. If there's no data for a day, you cannot get 0 for that day, because the day does not exist in the database. You could make a "calendar" table, one row per day, and join it with a count to get 0 for the day. -Sheeri On 5/15/06, Barry <[EMAIL PROTECTED]> wrote: Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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: Unknown option --install
Yes -- did it help? -Sheeri On 5/15/06, Miles Thompson <[EMAIL PROTECTED]> wrote: Sheeri, So even if I am executing mysqld-nt.exe from the directory where it is installed, preface it with the full path? Never thought of that - worth a try. Thanks - Miles At 05:23 PM 5/12/2006, sheeri kritzer wrote: >I'm going to guess that the path variable is only looking at the MySQL >3.23 mysqld binary. Try using a full path to the MySQL 5.0.20 binary >and see if you still get errors. > >-Sheeri > >On 5/10/06, Miles Thompson <[EMAIL PROTECTED]> wrote: >> >>I am trying to install two MySQL servers to run as Windows XP services. >>One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 >>and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and >>testing. >> >>I'm following the manual's instructions in section 5.13.1.2. Starting >>Multiple Windows Servers as Services >>found at >>http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html >> >>The installation of mysqd1 went fine, but I consistently get an error when >>trying to install mysqld2, like so: >> >>C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 >>--defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf >> >>Which returns this error: >>060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' >> >>Huh? It's listed as one of the parameters after issuing mysqld-nt --help >>--verbose. >> >>More background: >> The previous instances of MySQL services have been removed. >> Have tried both forward "/" and back "\" slashes in the >> defaults-file path >> Console window has been closed and reopened. >> mysql.ini in the \Windows directory has been renamed to >> mysql.ini.old >> >>Does anyone have any suggestions? They will be most welcome. >> >>Regards - Miles Thompson >> >> >>-- >>No virus found in this outgoing message. >>Checked by AVG Anti-Virus. >>Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 >> >> >> >>-- >>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] > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 -- 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: Setting sql_log_bin
I don't know what version you're running, but a
mysql -?
will show you the variables you can set. It's not ALL variables you
can set that way, just a few. You will see that the variable you're
trying to set cannot be set via a command on the commandline.
However, your first command could be to SET SQL_LOG_BIN=0 in your
script or whatever you're running.
For 5.0.19, here is the snippet of mysql -? that shows the variables
you can set:
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
- -
auto-rehash TRUE
character-sets-dir(No default value)
default-character-set latin1
compress FALSE
database (No default value)
delimiter ;
vertical FALSE
force FALSE
named-commandsFALSE
local-infile FALSE
no-beep FALSE
host (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbufferedFALSE
column-names TRUE
sigint-ignore FALSE
port 0
promptmysql>
quick FALSE
raw FALSE
reconnect TRUE
socket(No default value)
table FALSE
debug-infoFALSE
user (No default value)
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet16777216
net_buffer_length 16384
select_limit 1000
max_join_size 100
secure-auth FALSE
show-warnings TRUE
-Sheeri
On 5/15/06, Mattias Andersson <[EMAIL PROTECTED]> wrote:
Hi.
I can't figure out how to set the session variable SQL_LOG_BIN when
using the mysql command-line client.
I want to do something like: #mysql -u root -pmypasswd --sql_log_bin = 0
< somefile.sql.
When trying to run this mysql tells me:
/usr/bin/mysql: unknown option '--sql_log_bin'
I figure from the documentation that this should be possible:
mysql --help
---snip---
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables
directly with
--variable-name=value.
---snip---
Perhapps the problem comes from that this is a sessions-variable...?
The problem I would like to solve is to not bin-log some special data
imports.
Any one that could help me figure this out?
Thanks alot.
Regards,
/Mattias
--
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: "optemizer" for mySQL!
I'm not sure what you mean by profiler -- where are you getting your information from? It's not referenced in the article I sent. If you mean checking to see if indexes are being used during a query, check out the EXPLAIN syntax. http://dev.mysql.com/doc/refman/5.0/en/explain.html -Sheeri On 5/14/06, Jim <[EMAIL PROTECTED]> wrote: Thanks Sheeri, How do you run the profiler and what does it do? Have recently had to add some indexes to various tables to improve performance so does not seen to be running automatically. -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Saturday, 13 May 2006 2:05 AM To: Jim Cc: mysql@lists.mysql.com Subject: Re: "optemizer" for mySQL! http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ The MySQL server has it built in. -Sheeri On 5/8/06, Jim <[EMAIL PROTECTED]> wrote: > Hi All, > > > > Didn't know there was an "optemizer" for mySQL. > > Where can I get it from? > > > > Thanks > > Jim > > > > > > Best regards, > > > > Jim Clark > Project Manager > Multilink Systems > Ph: 03 9425 9400 > Fax: 03 9425 9811 > > > > > -- 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: about log
Peng Yi-fan schrieb: > Hi, > > I would like to change the path of a database's log. Which command I should > choose? > > Thanks. > > Peng Change the config files ? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about log
Hi, I would like to change the path of a database's log. Which command I should choose? Thanks. Peng
Setting sql_log_bin
Hi. I can't figure out how to set the session variable SQL_LOG_BIN when using the mysql command-line client. I want to do something like: #mysql -u root -pmypasswd --sql_log_bin = 0 < somefile.sql. When trying to run this mysql tells me: /usr/bin/mysql: unknown option '--sql_log_bin' I figure from the documentation that this should be possible: mysql --help ---snip--- -O, --set-variable=name Change the value of a variable. Please note that this option is deprecated; you can set variables directly with --variable-name=value. ---snip--- Perhapps the problem comes from that this is a sessions-variable...? The problem I would like to solve is to not bin-log some special data imports. Any one that could help me figure this out? Thanks alot. Regards, /Mattias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown option --install
Sheeri, So even if I am executing mysqld-nt.exe from the directory where it is installed, preface it with the full path? Never thought of that - worth a try. Thanks - Miles At 05:23 PM 5/12/2006, sheeri kritzer wrote: I'm going to guess that the path variable is only looking at the MySQL 3.23 mysqld binary. Try using a full path to the MySQL 5.0.20 binary and see if you still get errors. -Sheeri On 5/10/06, Miles Thompson <[EMAIL PROTECTED]> wrote: I am trying to install two MySQL servers to run as Windows XP services. One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and testing. I'm following the manual's instructions in section 5.13.1.2. Starting Multiple Windows Servers as Services found at http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html The installation of mysqd1 went fine, but I consistently get an error when trying to install mysqld2, like so: C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 --defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf Which returns this error: 060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' Huh? It's listed as one of the parameters after issuing mysqld-nt --help --verbose. More background: The previous instances of MySQL services have been removed. Have tried both forward "/" and back "\" slashes in the defaults-file path Console window has been closed and reopened. mysql.ini in the \Windows directory has been renamed to mysql.ini.old Does anyone have any suggestions? They will be most welcome. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- 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] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with prcedures
Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help in updating old binary files to database slave
Hi Comunity, I need some help from you. In replication i want to update old binary files, but server is not updating old files. It is updatin only current using binarylog only. The scenario is, my master is updating log-5 file(say) and slave is reading data from it, and updating itself to current changes. but connection is lost between master and slave. After the connection is established the new log-6 file is update by master but at slave side its only log-5. slave is not updating log-5 though connection is ok. what i have to do so that the old binary file have to be updated in slave. regards, bala
Need help with procedure
Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Mysql version is: 3.23.54 Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with triggers
Daevid, > This is my first trigger I'm trying to write. > > I have two tables. 'stores' and 'zipcodes'. > > I want to automatically set the latitude and longitude of the store using > it's zipcode lookup in the zipcode table. > > DELIMITER $$; > > DROP TRIGGER `store_coord`$$ > > create trigger `store_coord` BEFORE INSERT on `stores` > for each row BEGIN > UPDATE stores AS s, zipcodes AS z > SET s.latitude = z.latitude, > s.longitude = z.longitude > WHERE s.zip = z.zip AND s.id = NEW.id; > END; > $$ First things first ... Updating a row that doesn't exist yet (BEFORE INSERT trigger) doesn't make sense :-) Why not simply adjust it like this: new.latitude := new.longitude := Given that it's a "before insert", modifying the NEW. values, these will be stored in the table. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com > DELIMITER ;$$ > > So when I do this: > > insert into `stores` (id, name, zip) values (10,'test company', 14526); > > I get this error > > Error Code : 1442 > Can't update table 'stores' in stored function/trigger because it is already > used by statement which invoked this stored function/trigger. > (0 ms taken) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
