InnoDB how to.
Dear Sir, I install MySQL 5.0.18 on RH EL4, but do not know how to use InnoDB for ROLLBACK. ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-innodb make make install **/etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 with setting above, I could not use InnoDB. How to know InnoDB is installed, and start it ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: better way of doing 1800 sequential updates?
Because you have no indexes on that table... MySQL will open and search within the entire table. If you would have had an index MySQL would know how to go directly to the row you want to update. You are always looking for rows with: WHERE id =? So there you have... you must add an index on the column named id ! Run in MySQL client: ALTER TABLE ultimas_respuestas_snmp ADD INDEX someNameForTheInde4x(id); It will take a while... but it will get you faster. -- Gabriel PREDA Senior Web Developer
Help Needed
Hi, How can I get the current value of AUTO_INCREMENT field, as the AUTO_INCREMENT field is a foreign key in some other table.please do mail it to my mailed, its very urgent! Thanks, bhanu The pessimist may be right in the long run, but the optimist has a better time during the trip. G Bhanu Prakash (Senior Member Technical Staff) Airtight Networks Pvt. Ltd., Airtight House, SN 149/1A, off ITI Road,Aundh, Pune 411007. Mobile:9850764135 THE INFORMATION IN THIS EMAIL AND ANY ATTACHMENTS IS CONFIDENTIAL AND INTENDED SOLELY FOR THE USE OF THE PERSON NAMED ABOVE. IF YOU ARE NOT THE INTENDED RECIPIENT, OR HAVE OTHERWISE RECEIVED THIS EMAIL IN ERROR, DO NOT READ, DISTRIBUTE, COPY OR OTHERWISE USE IT. IF YOU HAVE RECEIVED THIS COMMUNICATION IN ERROR, PLEASE IMMEDIATELY NOTIFY THE SENDER BY TELEPHONE OR EMAIL, AND DESTROY THIS MESSAGE AND ANY ATTACHMENTS. THANK YOU.
On Duplicate Key....
I want an On Duplicate Key do nothing feature which obviously doesn't exist. However, will this be any faster than actually updating the row. INSERT INTO table (a,b) VALUES (1,2) ON DUPLICATE KEY UPDATE b=b; BTW each row is made up of only 2 columns and those to columns make up the key so if there is a duplicate key the record I am trying to insert is already there exactly as I am attempting to insert -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Hello! I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've got following problem: I have a table with the followign structure +-+--+--+-+---+- ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+---+- ---+ | STEP_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | | | STEP_TYPE_ID| smallint(5) unsigned | NO | MUL | 0 | | (some other field) ... +-+--+--+-+---+- ---+ When I execute following statement SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081598; I get this result +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870211 | 17081598 |1 | | 47870212 | 17081598 |4 | | 47870214 | 17081599 |1 | !! +--++--+ As you can see there is a record with INVOICE_ID=17081599. Please note that if I ask for mysql SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081599; I also receive that record: +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870214 | 17081599 |1 | | 47870215 | 17081599 |4 | | 47870216 | 17081599 |3 | +--++--+ Interesting is also that no record with STEP_ID=47870213 is visible. I wrote visible and not present, because if I try to insert a new Record with this PK I get this error mysql INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES (47870213, 17081598, 3); ERROR 1062 (23000): Duplicate entry '47870213' for key 1 However: mysql SELECT * FROM step where STEP_ID=47870213; Empty set (0.00 sec) The problem for me is that I also collect a statistic from this come out wrong, because when I ask for mysql SELECT COUNT(*) FROM step WHERE INVOICE_ID IN (17081598,17081599) AND STEP_TYPE_ID=1; I get, instead of 2: +--+ | COUNT(*) | +--+ |3 | +--+ Can you please tell me what the problem could be and what can I do to solve it? Thanks a lot! Regards, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help Needed
If you do an INSERT and you generate an new number in an AUTO_INCREMENT field the new value generated can be retrieved using: *SELECT LAST_INSERT_ID();* You are not required to retrieve into the application and then use it back in another SQL statement... you can use user variables: *SELECT @lastGenerated:=LAST_INSERT_ID();* And then use: the variable in another query: *INSERT INTO someTbl (id, someCol, somCol2) VALUES (NULL, @lastGenerated, 'OutsideValue'); * Be warned that LAST_INSERT_ID() is functional only if you did not specified the AUTO_INCREMENT field's value... only if you did not specified the AUTO_INCREMENT field at all... or in the insert statement you used NULL or 0 as a value... those values are considered magic by MySQL in case of an AUTO_INCREMENT field. Now part 2... if you didn't created an AUTO_INCREMENT field... and only want to use the last value from that table: Either you use a MAX() request: *SELECT @lastGenerated:=MAX(columnName) FROM tableName* Or: *SHOW TABLE STATUS LIKE 'tableName'* and somewhere in the result is the AUTO_INCREMENT value. Good luck ! -- Gabriel PREDA Senior Web Developer
Re: On Duplicate Key....
Try: INSERT *IGNORE* INTO table (a,b) VALUES (1,2) -- Gabriel PREDA Senior Web Developer
Re: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Patrick, I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've got following problem: I have a table with the followign structure +-+--+--+-+---+- ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+---+- ---+ | STEP_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | | | STEP_TYPE_ID| smallint(5) unsigned | NO | MUL | 0 | | (some other field) ... +-+--+--+-+---+- ---+ When I execute following statement SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081598; I get this result +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870211 | 17081598 |1 | | 47870212 | 17081598 |4 | | 47870214 | 17081599 |1 | !! +--++--+ As you can see there is a record with INVOICE_ID=17081599. Please note that if I ask for mysql SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081599; I also receive that record: +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870214 | 17081599 |1 | | 47870215 | 17081599 |4 | | 47870216 | 17081599 |3 | +--++--+ Interesting is also that no record with STEP_ID=47870213 is visible. I wrote visible and not present, because if I try to insert a new Record with this PK I get this error mysql INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES (47870213, 17081598, 3); ERROR 1062 (23000): Duplicate entry '47870213' for key 1 However: mysql SELECT * FROM step where STEP_ID=47870213; Empty set (0.00 sec) The problem for me is that I also collect a statistic from this come out wrong, because when I ask for mysql SELECT COUNT(*) FROM step WHERE INVOICE_ID IN (17081598,17081599) AND STEP_TYPE_ID=1; I get, instead of 2: +--+ | COUNT(*) | +--+ |3 | +--+ Can you please tell me what the problem could be and what can I do to solve it? Corrupt index? Do you have indices on INVOICE_ID? If so, try dropping it and recreating it? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Thanks Martijn for you prompt answer! Do you have indices on INVOICE_ID? Yes (KEY `IDX_step1` (`INVOICE_ID`)) If so, try dropping it and recreating it? OK, I will do it (I will have to wait until this week-end to do it (= the table is a bit big (47 mio records) and I cannot stop the service during the week) and tell you the results. Shall also perform a REPAIR TABLE? Regards, Patrick -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, 04 April 2006 10:34 To: Patrick Herber; mysql@lists.mysql.com Subject: Re: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject) Patrick, I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've got following problem: I have a table with the followign structure +-+--+--+-+--- +- +-+--+--+-+--- + ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- +- +-+--+--+-+--- + ---+ | STEP_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | | | STEP_TYPE_ID| smallint(5) unsigned | NO | MUL | 0 | | (some other field) ... +-+--+--+-+--- +- +-+--+--+-+--- + ---+ When I execute following statement SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081598; I get this result +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870211 | 17081598 |1 | | 47870212 | 17081598 |4 | | 47870214 | 17081599 |1 | !! +--++--+ As you can see there is a record with INVOICE_ID=17081599. Please note that if I ask for mysql SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081599; I also receive that record: +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870214 | 17081599 |1 | | 47870215 | 17081599 |4 | | 47870216 | 17081599 |3 | +--++--+ Interesting is also that no record with STEP_ID=47870213 is visible. I wrote visible and not present, because if I try to insert a new Record with this PK I get this error mysql INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES (47870213, 17081598, 3); ERROR 1062 (23000): Duplicate entry '47870213' for key 1 However: mysql SELECT * FROM step where STEP_ID=47870213; Empty set (0.00 sec) The problem for me is that I also collect a statistic from this come out wrong, because when I ask for mysql SELECT COUNT(*) FROM step WHERE INVOICE_ID IN mysql (17081598,17081599) AND STEP_TYPE_ID=1; I get, instead of 2: +--+ | COUNT(*) | +--+ |3 | +--+ Can you please tell me what the problem could be and what can I do to solve it? Corrupt index? Do you have indices on INVOICE_ID? If so, try dropping it and recreating it? 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 -- 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: better way of doing 1800 sequential updates?
Try creating INDEX on id column I think that will do the trick ;) --Praj On Mon, 3 Apr 2006 19:07:34 -0600 Ariel Sánchez Mora [EMAIL PROTECTED] wrote: This table holds latest data from an app: mysql select * from ultimas_respuestas_snmp limit 10; +++---+-+---++--+ | id | info_oficina | columna_donde_guardar | info_interfaz | valorSNMP | nombre_dns | hora | +++---+-+---++--+ | 0 | Sucursal Canas | USO_CPU_1min || error | canas | 18:49:53 | | 1 | Sucursal Canas | RAM_LIBRE || error | canas | 18:49:54 | | 2 | Sucursal Canas | ESTADO_ADMIN_1| TDM 195-2883 ICE| 1 | canas | 18:49:55 | | 3 | Sucursal Canas | ESTADO_ADMIN_2| RDSI 669-9010 ICE | error | canas | 18:49:56 | | 4 | Sucursal Canas | ESTADO_ADMIN_3| RDSI_doble 669-9010 ICE | error | canas | 18:49:57 | | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1| TDM 195-2883 ICE| error | canas | 18:49:58 | | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2| RDSI 669-9010 ICE | error | canas | 18:49:59 | | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3| RDSI_doble 669-9010 ICE | 5 | canas | 18:50:00 | | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE| error | canas | 18:50:01 | | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error | canas | 18:50:02 | +++---+-+---++--+ 10 rows in set (0.00 sec) without the limit 10 1780 rows in set (0.03 sec) the create table: ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( `id` int(4) NOT NULL default '0', `info_oficina` varchar(35) default NULL, `columna_donde_guardar` varchar(30) default NULL, `info_interfaz` varchar(30) default NULL, `valorSNMP` varchar(12) default NULL, `nombre_dns` varchar(20) default NULL, `hora` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I cannot use indexes because my dbexpress driver doesn't support it (long story short, I'll change it in the next version). Now, I have to make a lot of sequential updates, like UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:21 WHERE id =0; UPDATE ultimas_respuestas_SNMP SET valorSNMP =10, hora =18:47:22 WHERE id =1; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =2; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =3; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =4; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =5; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =6; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =7; UPDATE ultimas_respuestas_SNMP SET valorSNMP =7000, hora =18:47:24 WHERE id =8; . . . UPDATE ultimas_respuestas_SNMP SET valorSNMP =0, hora =18:48:38 WHERE id =1778; UPDATE ultimas_respuestas_SNMP SET valorSNMP =, hora =18:48:38 WHERE id =1779; This makes my server CPU load top 100% for about 1:20 s. First question: is update the best command for this? I've seen replace that might work too; has anyone played around with something like this before? Second: is there a better way of formulating the update command, for this sequence?? Perhaps one that takes advantage of the sequential inserts? TIA, all comments welcome. I am a newbie by the way, trying to optimize my first MySQL related program. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error while runnign ./scripts/mysql_install_db --user=mysql
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [Error while runnign ./scripts/mysql_install_db --user=mysql] Description: When running the command ./scripts/mysql_install_db --user=mysql I get the following error 060403 13:25:39 [ERROR] ./bin/my sqld: Can't find file: './mysql/help_topic.frm' (errno: 13) 060403 13:25:39 [ERROR] ./bin/mysqld: Can't find file: './mysql/help_topic.frm' (errno: 13) I get 4176 lines of errors How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Super-User Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) 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.18-standard (MySQL Community Edition - Standard (GPL)) C compiler: C++ compiler: Environment: machine, os, target, libraries (multiple lines) System: SunOS Mars 5.9 Generic_118558-06 sun4u sparc SUNW,Ultra-60 Architecture: sun4 Some paths: /usr/bin/perl /usr/local/bin/gcc GCC: ld.so.1: /usr/local/bin/gcc: fatal: libiconv.so.2: open failed: No such file or directory Compilation info: CC='cc-5.0' CFLAGS='-xO3 -Xa -xstrconst -mt -D_FORTEC_ -xarch=v9' CXX='CC' CXXFLAGS='-xO3 -noex -mt -D_FORTEC_ -xarch=v9' LDFLAGS='-xarch=v9' ASFLAGS='-xarch=v9' LIBC: -rw-r--r-- 1 root bin 1867896 Dec 23 2004 /lib/libc.a lrwxrwxrwx 1 root root 11 May 25 2005 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 867400 Dec 23 2004 /lib/libc.so.1 -rw-r--r-- 1 root bin 1867896 Dec 23 2004 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 May 25 2005 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 867400 Dec 23 2004 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bi n' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--ena ble-thread-safe-client' '--enable-local-infile' '--with-named-z-libs=no' '--disable-shared' '--with-zlib-dir=bundled' '--with-readli ne' '--with-embedded-server' '--with-archive-storage-engine' '--with-innodb' 'CC=cc-5.0' 'CFLAGS=-xO3 -Xa -xstrconst -mt -D_FORTEC_ -xarch=v9' 'CXXFLAGS=-xO3 -noex -mt -D_FORTEC_ -xarch=v9' 'CXX=CC' 'LDFLAGS=-xarch=v9' Jacques Levac Conseiller en technologie (514) 393-8822 x358
Re: Select Sum with union, tricky question perhaps not for you
H L [EMAIL PROTECTED] wrote on 04/03/2006 01:53:37 PM: The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the flat file design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi thanks for the quick response! Well i think you are right about this. I will probably have to redesign the database. What do you think of using DATE instead of year,dayofyear or is there problems i could get into then. It would be great to be able to use select from price where date=somedate AND date = tosomedate /Henrik You could use a single date column for (year, Julian date) but if you are constantly querying on the Julian date (based on your application or other needs) then having that column would be a good thing. Without a column for Julian date, you could not index that value either by itself or as part of another index. Which way works better for you really depends on *your* application's needs and I cannot guess all of them. However, you really should normalize that data first. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: better way of doing 1800 sequential updates?
Ariel Sánchez Mora [EMAIL PROTECTED] wrote on 04/03/2006 09:07:34 PM: This table holds latest data from an app: mysql select * from ultimas_respuestas_snmp limit 10; +++--- +-+---++--+ | id | info_oficina | columna_donde_guardar | info_interfaz | valorSNMP | nombre_dns | hora | +++--- +-+---++--+ | 0 | Sucursal Canas | USO_CPU_1min | | error | canas | 18:49:53 | | 1 | Sucursal Canas | RAM_LIBRE | | error | canas | 18:49:54 | | 2 | Sucursal Canas | ESTADO_ADMIN_1| TDM 195-2883 ICE | 1 | canas | 18:49:55 | | 3 | Sucursal Canas | ESTADO_ADMIN_2| RDSI 669-9010 ICE | error | canas | 18:49:56 | | 4 | Sucursal Canas | ESTADO_ADMIN_3| RDSI_doble 669-9010 ICE | error | canas | 18:49:57 | | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1| TDM 195-2883 ICE | error | canas | 18:49:58 | | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2| RDSI 669-9010 ICE | error | canas | 18:49:59 | | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3| RDSI_doble 669-9010 ICE | 5 | canas | 18:50:00 | | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE | error | canas | 18:50:01 | | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error | canas | 18:50:02 | +++--- +-+---++--+ 10 rows in set (0.00 sec) without the limit 10 1780 rows in set (0.03 sec) the create table: ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( `id` int(4) NOT NULL default '0', `info_oficina` varchar(35) default NULL, `columna_donde_guardar` varchar(30) default NULL, `info_interfaz` varchar(30) default NULL, `valorSNMP` varchar(12) default NULL, `nombre_dns` varchar(20) default NULL, `hora` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I cannot use indexes because my dbexpress driver doesn't support it (long story short, I'll change it in the next version). Now, I have to make a lot of sequential updates, like UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:21 WHERE id =0; UPDATE ultimas_respuestas_SNMP SET valorSNMP =10, hora =18:47:22 WHERE id =1; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =2; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:22 WHERE id =3; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =4; UPDATE ultimas_respuestas_SNMP SET valorSNMP =1, hora =18:47:23 WHERE id =5; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =6; UPDATE ultimas_respuestas_SNMP SET valorSNMP =5, hora =18:47:24 WHERE id =7; UPDATE ultimas_respuestas_SNMP SET valorSNMP =7000, hora =18:47: 24 WHERE id =8; . . . UPDATE ultimas_respuestas_SNMP SET valorSNMP =0, hora =18:48:38 WHERE id =1778; UPDATE ultimas_respuestas_SNMP SET valorSNMP =, hora =18:48:38 WHERE id =1779; This makes my server CPU load top 100% for about 1:20 s. First question: is update the best command for this? I've seen replace that might work too; has anyone played around with something like this before? Second: is there a better way of formulating the update command, for this sequence?? Perhaps one that takes advantage of the sequential inserts? TIA, all comments welcome. I am a newbie by the way, trying to optimize my first MySQL related program. Ariel Yes, there is a way to make this go MUCH faster. Assuming you followed the advice of the previous responses and added an INDEX to your ID column on ultimas_respuestas_SNMP. Indexes are used internally to MySQL, the fact that you are using dbexpress has no bearing on good database design. If you want, or in this case *NEED* an index, add it. MySQL deals with those, not your connection library. start script CREATE TEMPORARY TABLE bulkUpdate ( id int not null, newHora varchar(10), newSNMP varchar(12), PRIMARY KEY (id) ) INSERT bulkUpdate (id, newHora, newSNMP) VALUES (0,'18:47:21','1'),(1,'18:47:22','10'), ... the rest of the 1800 rows of changes you want to make ...; UPDATE ultimas_respuestas_SNMP ur INNER JOIN bulkUpdate bu ON bu.id = ur.id SET ur.hora = bu.newHora, ur.valorSNMP = bu.newSNMP; DROP TEMPORARY TABLE bulkUpdate; end script This works faster for several reasons: a) There is an index on the column you are using most often for your lookups (see previous posts) b) You are asking the parser to
Re: Need for distinct sum
Yasir Assam [EMAIL PROTECTED] wrote on 04/03/2006 11:09:01 PM:
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following might
work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do it.
All my queries are generated dynamically, and I want to avoid generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change the
DB schema if needs be.
Thanks,
Yasir
Based on database theory, your schema is correct (so long as each spell
only consumes up to 1 unit of essence). As you have discovered, you are
trying to take a second-level summary within a single statement. While the
COUNT() aggregate function has a DISTINCT modifier none of the others do.
That is why you posted.
One technique you could try is to create an intermediate pivot table. That
way you can know how much of each essence has been used in each spell.
CREATE TEMPORARY TABLE tmpSpellSummary (PRIMARY KEY (wizard_id,spell_id))
SELECT
wizard_id,
spell_id,
SUM(if(type='AIR',1,0)) air,
SUM(if(type='EARTH',1,0)) earth,
SUM(if(type='FIRE',1,0)) fire,
SUM(if(type='WATER',1,0)) water
FROM spellcast
GROUP BY wizard_id, spell_id
Now you can join this pivot table to your other query and you won't have
the duplication. You can also modify this by adding (after the GROUP BY
clause):
HAVING air0 and water0
to pick out just those spell_id's that used both air and water.
Another technique you could try (still assuming that using an essence only
consumes 1 unit of it) is to add an essence bitmap value to your spell
table
Re: Limited trafic in 5.0?
Thanks for the answers! When i do a SHOW PROCESSLIST; i get this: # mysql show processlist; ++--++--+-+--+---+-- + | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+--+---+-- + | 1 | root | localhost:1116 | NULL | Query |0 | NULL | show processlist | | 2 | root | localhost:1117 | NULL | Sleep | 162 | | NULL | | 5 | root | localhost:1132 | aa | Sleep | 89 | | NULL | | 6 | root | localhost:1133 | aa | Sleep | 89 | | NULL | | 7 | root | localhost:1134 | aa | Sleep |0 | | NULL | | 8 | root | localhost:1135 | aa | Sleep |0 | | NULL | ++--++--+-+--+---+-- + 6 rows in set (0.00 sec) mysql show processlist; ++--++--+-+--+---+-- + | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+--+---+-- + | 1 | root | localhost:1116 | NULL | Query |0 | NULL | show processlist | | 2 | root | localhost:1117 | NULL | Sleep | 21 | | NULL | | 5 | root | localhost:1132 | aa | Sleep | 124 | | NULL | | 6 | root | localhost:1133 | aa | Sleep | 124 | | NULL | | 7 | root | localhost:1134 | aa | Sleep |1 | | NULL | | 8 | root | localhost:1135 | aa | Sleep |0 | | NULL | | 9 | root | localhost:1145 | NULL | Sleep |1 | | NULL | ++--++--+-+--+---+-- + 7 rows in set (0.00 sec) # Does it shows anything abnormal? How do i change that set global max_connections=900;? Just execute it from the MySql comand line? From Mysql Administrator 1.1, i'm seeing the user root, the one i'm using, as having 0 max connections, 0 max updates and 0 queries. Suposedly it will allow for infinite, right? Or will global variables overcome those settings of the user? Thanks for the help! Citando sheeri kritzer [EMAIL PROTECTED]: You might want to change those. I'm guessing your wait_timeout is set to the default of a day. Is the application letting go of connections? If you do a SHOW PROCESSLIST on the server, is there really only one connection, or are there many? -Sheeri On 4/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: mysql SHOW VARIABLES LIKE max%connect%; +--+---+ | Variable_name| Value | +--+---+ | max_connect_errors | 10| | max_connections | 100 | | max_user_connections | 0 | +--+---+ 3 rows in set (0.06 sec) mysql Citando sheeri kritzer [EMAIL PROTECTED]: It's possible that you have max_connections set to something very low, and/or the application is not letting go of MySQL connections. What does the following show you? mysql SHOW VARIABLES LIKE max%connect%; -Sheeri On 4/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have a program that uses MySql. When using a MySql 5.0 server, the MySql Administrator shows in the health section that the server never passes a perfectly stable limit of 9.1~9.2 KB/s of trafic and never shows more that 1 query (per second). Looks like some kind of configuration limitation!? This makes my application absurdly slow. My mysql 5.0 server was installed on windows with all default settings. Any hints at what could be wrong and how to change it? __ O email preferido dos portugueses agora com 2 000 MB de espaço e acesso gratuito à Internet http://www.portugalmail.pt/2000mb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Email gratuito com 2 000 MB Espaço para guardar 1 milhão de mensagens http://www.portugalmail.pt/2000mb __ Sabe quanto gasta com a sua ligação à Internet? Verifique aqui: http://acesso.portugalmail.pt/contas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limited trafic in 5.0?
I don't see anything abnormal in your show processlist, yes you can change the max_connections global setting from the mysql CLI, but for it to survive restarts/reboots it has to be defined in the my.cnf file ( I guess its my.ini for windows) . Kishore Jalleda http://kjalleda.googlepages.com/ On 4/4/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thanks for the answers! When i do a SHOW PROCESSLIST; i get this: # mysql show processlist; ++--++--+-+--+---+-- + | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+--+---+-- + | 1 | root | localhost:1116 | NULL | Query |0 | NULL | show processlist | | 2 | root | localhost:1117 | NULL | Sleep | 162 | | NULL | | 5 | root | localhost:1132 | aa | Sleep | 89 | | NULL | | 6 | root | localhost:1133 | aa | Sleep | 89 | | NULL | | 7 | root | localhost:1134 | aa | Sleep |0 | | NULL | | 8 | root | localhost:1135 | aa | Sleep |0 | | NULL | ++--++--+-+--+---+-- + 6 rows in set (0.00 sec) mysql show processlist; ++--++--+-+--+---+-- + | Id | User | Host | db | Command | Time | State | Info | ++--++--+-+--+---+-- + | 1 | root | localhost:1116 | NULL | Query |0 | NULL | show processlist | | 2 | root | localhost:1117 | NULL | Sleep | 21 | | NULL | | 5 | root | localhost:1132 | aa | Sleep | 124 | | NULL | | 6 | root | localhost:1133 | aa | Sleep | 124 | | NULL | | 7 | root | localhost:1134 | aa | Sleep |1 | | NULL | | 8 | root | localhost:1135 | aa | Sleep |0 | | NULL | | 9 | root | localhost:1145 | NULL | Sleep |1 | | NULL | ++--++--+-+--+---+-- + 7 rows in set (0.00 sec) # Does it shows anything abnormal? How do i change that set global max_connections=900;? Just execute it from the MySql comand line? From Mysql Administrator 1.1, i'm seeing the user root, the one i'm using, as having 0 max connections, 0 max updates and 0 queries. Suposedly it will allow for infinite, right? Or will global variables overcome those settings of the user? Thanks for the help! Citando sheeri kritzer [EMAIL PROTECTED]: You might want to change those. I'm guessing your wait_timeout is set to the default of a day. Is the application letting go of connections? If you do a SHOW PROCESSLIST on the server, is there really only one connection, or are there many? -Sheeri On 4/3/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: mysql SHOW VARIABLES LIKE max%connect%; +--+---+ | Variable_name| Value | +--+---+ | max_connect_errors | 10| | max_connections | 100 | | max_user_connections | 0 | +--+---+ 3 rows in set (0.06 sec) mysql Citando sheeri kritzer [EMAIL PROTECTED]: It's possible that you have max_connections set to something very low, and/or the application is not letting go of MySQL connections. What does the following show you? mysql SHOW VARIABLES LIKE max%connect%; -Sheeri On 4/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have a program that uses MySql. When using a MySql 5.0 server, the MySql Administrator shows in the health section that the server never passes a perfectly stable limit of 9.1~9.2 KB/s of trafic and never shows more that 1 query (per second). Looks like some kind of configuration limitation!? This makes my application absurdly slow. My mysql 5.0 server was installed on windows with all default settings. Any hints at what could be wrong and how to change it? __ O email preferido dos portugueses agora com 2 000 MB de espaço e acesso gratuito à Internet http://www.portugalmail.pt/2000mb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Email gratuito com 2 000 MB Espaço para guardar 1 milhão de mensagens http://www.portugalmail.pt/2000mb __ Sabe quanto gasta com a sua ligação à Internet? Verifique aqui: http://acesso.portugalmail.pt/contas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
new password will not be effective for connection immediately
Hi,
I want to change the password of the User( which is root) in the table 'user'
from the database 'mysql'.
I use the String sql = update user set Password=password('somePassWord') where
User='root';
I see from MySql Query Browser the password really changed !
Before I stop the mysql from Service in Control Panel of my machine Window Xp,
I can still make connection to the mysql database with the old password of the
user 'root'.
Only when I Stop and Start again the mysql from Service in Control Panel ,
the connection to the mysql database with the old password of the user 'root'
will fail,
and with the new password of the user 'root' will succeed !
My question is I hope the new password will be effective for connection
immediately
without ' Stop and Start ' the mysql Service.
Thanks for your help !
NanFei
RE: new password will not be effective for connection immediately
[snip] My question is I hope the new password will be effective for connection immediately without ' Stop and Start ' the mysql Service. [/snip] Instead of stopping and starting the service you should do the following query; FLUSH PRIVILEGES; That will cause new passwords to take effect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new password will not be effective for connection immediately
In the last episode (Apr 04), NanFei Wang said:
I want to change the password of the User( which is root) in the
table 'user' from the database 'mysql'.
I use the String sql = update user set
Password=password('somePassWord') where User='root'; I see from
MySql Query Browser the password really changed !
If you edit the mysql tables directly, you must use the FLUSH
PRIVILEGES command to tell mysql that they have changed. A cleaner
alternative would be to use the SET PASSWORD command.
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new password will not be effective for connection immediately
Only if you run this SQL statement: *FLUSH PRIVILEGES* That will make the new password effective. -- Gabriel PREDA Senior Web Developer
Re: new password will not be effective for connection immediately
You need to issue FLUSH PRIVILEGES or you must RELOAD Mysql for the
changes to come into effect after you have changed your settings
Kishore Jalleda
http://kjalleda.googlepages.com/
On 4/4/06, NanFei Wang [EMAIL PROTECTED] wrote:
Hi,
I want to change the password of the User( which is root) in the table
'user' from the database 'mysql'.
I use the String sql = update user set Password=password('somePassWord')
where User='root';
I see from MySql Query Browser the password really changed !
Before I stop the mysql from Service in Control Panel of my machine Window
Xp,
I can still make connection to the mysql database with the old password of
the user 'root'.
Only when I Stop and Start again the mysql from Service in Control Panel ,
the connection to the mysql database with the old password of the user
'root' will fail,
and with the new password of the user 'root' will succeed !
My question is I hope the new password will be effective for connection
immediately
without ' Stop and Start ' the mysql Service.
Thanks for your help !
NanFei
link MS Access to MySQL?
Hello everyone, Please correct me if my understanding is wrong. Does the implementation of linked table mentioned on 18.1.11.3. How to Import or Link MySQL Database Tables to Access? (http://dev.mysql.com/doc/refman/4.1/en/import-of-data.html) means that data is actually stored in MySQL and tables in Access are links pointing to tables in MySQL? What I want is do linked table the other way, which is store data in Access and make tables in MySQL links that point to tables in Access? Is it possible and how? Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Thread Memory Usage
Hello all, I am looking for a way to view the max values that have been used by all threads or a signal thread for read_buffer, read_rnd_buffer, sort_buffer, and net_buffer. I want to know these values so that I can turn them appropriately. Regards, --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax: 260.459.6271
Re: stunningly slow query
I have just noticed this from section 13.4.5. LOCK TABLES and UNLOCK TABLES Syntax of the 5.0.18 ref manual, and wondered if it will help improve the speed of your query: snip Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage: If you are going to run many operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them. The downside is that no thread can update a READ-locked table (including the one holding the lock) and no thread can access a WRITE-locked table other than the one holding the lock. The reason some MyISAM operations are faster under LOCK TABLES is that MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called. Normally, the key cache is flushed after each SQL statement. /snip Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : error 28 from table handler
Thanks Yes the hoster fixed it by cleaning the temp files Jacques Brignon -Message d'origine- De : Sander Smeenk [mailto:[EMAIL PROTECTED] Envoyé : lundi 3 avril 2006 10:32 À : mysql@lists.mysql.com Objet : Re: error 28 from table handler Quoting Jacques Brignon ([EMAIL PROTECTED]): 1030 : Got error 28 from table handler % perror 28 OS error code 28: No space left on device Any idea of what might cause that, and what should be done to correct this situation? Aparently the disk that stores your MySQL datadirectory is full. Either you or your hoster should clean it up ;) With regards, Sander. -- | Junk is something you've kept for years and throw away three weeks | before you need it. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- 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: link MS Access to MySQL?
Bing Du [EMAIL PROTECTED] wrote on 04/04/2006 12:20:23 PM: Hello everyone, Please correct me if my understanding is wrong. Does the implementation of linked table mentioned on 18.1.11.3. How to Import or Link MySQL Database Tables to Access? (http://dev.mysql.com/doc/refman/4.1/en/import-of-data.html) means that data is actually stored in MySQL and tables in Access are links pointing to tables in MySQL? What I want is do linked table the other way, which is store data in Access and make tables in MySQL links that point to tables in Access? Is it possible and how? Thanks in advance, Bing The only way to link tables in MySQL (to use the term from Access) is to use the Federated database engine. A Federated table physically exists on one server but virtually exists on another. This is exactly like creating a linked table. However, AFAIK, the Federated database engine only supports direct MySQL to MySQL sharing. It is not ready to do MySQL to any other server yet. http://dev.mysql.com/doc/refman/5.0/en/federated-description.html http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Days in Month
Hello, is there a MySQL function to determine the total days of any given month, IE if current month is April, is there a function or query to find the total days of the month? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Days in Month
Mike,
Maybe :
LAST_DAY(date)
Takes a date or datetime value and returns the corresponding value for
the last day of the month. Returns NULL if the argument is invalid.
mysql SELECT LAST_DAY('2003-02-05');
- '2003-02-28'
mysql SELECT LAST_DAY('2004-02-05');
- '2004-02-29'
mysql SELECT LAST_DAY('2004-01-01 01:01:01');
- '2004-01-31'
mysql SELECT LAST_DAY('2003-03-32');
- NULL
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 04, 2006 2:48 PM
To: MySQL List
Subject: Days in Month
Hello,
is there a MySQL function to determine the total days of any given
month,
IE if
current month is April, is there a function or query to find the total
days of
the month?
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
===
--
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: Days in Month
Mike Blezien [EMAIL PROTECTED] wrote on 04/04/2006 02:47:50 PM: Hello, is there a MySQL function to determine the total days of any given month, IE if current month is April, is there a function or query to find the total days of the month? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === Well, the first place I would have looked is in the manual... http://dev.mysql.com/doc/refman/4.1/en/index.html Having skimmed through it at least once (something I recommend that everyone does), I know there is a section describing all of the functions that work with date and time values... http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html where I found a function to return exactly what you were looking for... http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id2691432 Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: InnoDB how to.
Try going to: http://www.mysql.com/innodb rollback You can search the manual by typing http://www.mysql.com/search term into the address bar of your browser. The first hit looked like a winner to me. -Sheeri On 4/4/06, Truong Tan Son [EMAIL PROTECTED] wrote: Dear Sir, I install MySQL 5.0.18 on RH EL4, but do not know how to use InnoDB for ROLLBACK. ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-innodb make make install **/etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 with setting above, I could not use InnoDB. How to know InnoDB is installed, and start it ? Thanks you and best regards, -- -- 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]
very long query for such a simple result
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this,
You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Low')),')')),'');
Re: very long query for such a simple result
Ed Reed [EMAIL PROTECTED] wrote on 04/04/2006 04:34:29 PM:
Can someone help me simplify this query please? It's meant to return
a single string result that looks something like this,
You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',
Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.
Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')
And (Priority='Low')),')')),'');
You are new at this, aren't you? First thing you need to do is to let us
see exactly what we need to deal with. Please respond with the results of
the following two commands
SHOW CREATE TABLE Employees\G
SHOW CREATE TABLE ProblemReports\G
We (the list members) will be able to help straighten you out from there
(there are lots of things we need to talk about but I think that we should
get you working first, ok?)
Always CC the list (or hit the REPLY TO ALL button or whatever you have in
your email client). That way everyone on the list stays informed of the
progress of this issue.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: // confusing syntax error when importing //
How did you try to do it on the remote system?
I copied and pasted your query to a server running MySQL
4.1.12-standard-log, and another running MySQL 5.0.19-standard-log,
and they both created the table just fine.
Have you tried copying and pasting the table creation itself to see if
that works?
Or using the commandline to do it on the remote machine --
mysql -u root -p -h host db file.sql
-Sheeri
On 4/3/06, julian oliver [EMAIL PROTECTED] wrote:
--apologies if you receive this email from another account--
hi,
i exported a *.sql using phpMyAdmin on a system running:
mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using
readline 5.1
i'm trying to import it from this localhost to a remote host
running:
mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i486) using
readline 5.1
the 13MB file fails during import with this error:
//-
Error
SQL query:
-- phpMyAdmin SQL Dump
-- version 2.8.0.2-Debian-3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 03, 2006 at 07:09 PM
-- Server version: 5.0.19
-- PHP Version: 4.4.2-1+b1
--
-- Database: `nuke`
--
--
--
-- Table structure for table `cur`
--
CREATE TABLE `cur` (
`cur_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`cur_namespace` tinyint( 2 ) unsigned NOT NULL default '0',
`cur_title` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`cur_text` mediumtext NOT NULL ,
`cur_comment` tinyblob NOT NULL ,
`cur_user` int( 5 ) unsigned NOT NULL default '0',
`cur_user_text` varchar( 255 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin
NOT NULL default '',
`cur_restrictions` tinyblob NOT NULL ,
`cur_counter` bigint( 20 ) unsigned NOT NULL default '0',
`cur_is_redirect` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_is_new` tinyint( 1 ) unsigned NOT NULL default '0',
`cur_random` double unsigned NOT NULL default '0',
`cur_touched` varchar( 14 ) CHARACTER SET latin1 COLLATE latin1_bin NOT
NULL default '',
`inverse_timestamp` varchar( 14 ) CHARACTER SET latin1 COLLATE
latin1_bin NOT NULL default '',
UNIQUE KEY `cur_id` ( `cur_id` ) ,
KEY `cur_namespace` ( `cur_namespace` ) ,
KEY `cur_title` ( `cur_title` ( 20 ) ) ,
KEY `cur_timestamp` ( `cur_timestamp` ) ,
KEY `cur_random` ( `cur_random` ) ,
KEY `name_title_timestamp` ( `cur_namespace` , `cur_title` ,
`inverse_timestamp` ) ,
KEY `user_timestamp` ( `cur_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `cur_user_text` , `inverse_timestamp` ) ,
KEY `namespace_redirect_timestamp` ( `cur_namespace` , `cur_is_redirect`
, `cur_timestamp` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 PACK_KEYS =1 AUTO_INCREMENT
=1028;
MySQL said: Documentation
#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 'collate
+latin1_bin NOT NULL default '',
`cur_text` mediumtext
//-
as i know very little about mysql generally, i'm having a hard time
deriving what the right syntax might be here.
out of interest the same *.sql imports on the local system just fine
using mysql on the commandline ('mysql -u user -p database
file.sql'),
but on the remote machine fails with the same error as seen above.
if someone could point me in the right direction i'd be very grateful.
cheers,
julian
--
julian oliver
[EMAIL PROTECTED]
--
http://www.fastmail.fm - Does exactly what it says on the tin
--
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]
Restrict MySQL server 4/5 to single IP
Hi, I have a machine with multiple IP addresses on my network interface and I have setup multiple MySQL servers on the machine, version 4.0 and 5.0. Currently, they're all listening on all IP addresses on different ports (3306 and 3307) but I'd like to make use of the second IP to make it easier to connect to each MySQL instance with different DNS names (mysql4.mydomain and mysql5.mydomain) on the default port. Only I couldn't find any hint on how to tell the MySQL server to listen only on a single IP address. I can change the port, the UNIX socket and disable IP networking entirely, but no idea how to specify a custom IP. Any hints? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
my.cnf: bind-address = ip Will probably do the trick. -- Eric Braswell Web Manager MySQL AB Cupertino, USA Yves Goergen wrote: Hi, I have a machine with multiple IP addresses on my network interface and I have setup multiple MySQL servers on the machine, version 4.0 and 5.0. Currently, they're all listening on all IP addresses on different ports (3306 and 3307) but I'd like to make use of the second IP to make it easier to connect to each MySQL instance with different DNS names (mysql4.mydomain and mysql5.mydomain) on the default port. Only I couldn't find any hint on how to tell the MySQL server to listen only on a single IP address. I can change the port, the UNIX socket and disable IP networking entirely, but no idea how to specify a custom IP. Any hints? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
Sorry, here's the results. BTW the query works it just seems overly complex and I'd like to streamline it. CREATE TABLE `employees` ( `EmployeeID` int(11) NOT NULL auto_increment, `FirstName` varchar(50) default NULL, `LastName` varchar(50) default NULL, `DateTerminated` datetime default NULL, `UserName` varchar(15) default NULL, PRIMARY KEY (`EmployeeID`) UNIQUE KEY `EmployeeID` (`EmployeeID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `problemreports` ( `PRNo` double NOT NULL auto_increment, `Status` varchar(10) default NULL, `Responsible` varchar(20) default NULL, `Priority` varchar(10) default NULL, PRIMARY KEY (`PRNo`), UNIQUE KEY `PRNo` (`PRNo`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 [EMAIL PROTECTED] 4/4/06 1:56:10 PM You are new at this, aren't you? First thing you need to do is to let us see exactly what we need to deal with. Please respond with the results of the following two commands SHOW CREATE TABLE Employees\G SHOW CREATE TABLE ProblemReports\G We (the list members) will be able to help straighten you out from there (there are lots of things we need to talk about but I think that we should get you working first, ok?) Always CC the list (or hit the REPLY TO ALL button or whatever you have in your email client). That way everyone on the list stays informed of the progress of this issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: very long query for such a simple result
Ed,
Can someone help me simplify this query please? It's meant
to return a single string result that looks something like this,
"You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)"
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a
first priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's
problem priorities to a temp table, then doing a simple pivot on
priority to generate your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single string result that looks something like this,
"You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)"
The relavent columns from the two tables are
Table: ProblemReports
Fields: PRNo, Status, Priority, Responsible
Table: Employees
Fields: Firstname, Lastname, DateTerminated, UserName
Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0,
If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ',
(Select Priority From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),
Concat('You have ', @a, ' open Problem Reports: Priorities(',
(Select ConCat('High=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='High')),', ',
(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Med')),', ',
(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Low')),')')),'');
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: link MS Access to MySQL?
The only way to link tables in MySQL (to use the term from Access) is to use the Federated database engine. A Federated table physically exists on one server but virtually exists on another. This is exactly like creating a linked table. However, AFAIK, the Federated database engine only supports direct MySQL to MySQL sharing. It is not ready to do MySQL to any other server yet. http://dev.mysql.com/doc/refman/5.0/en/federated-description.html http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html Interesting, thanks much for the information, Shawn. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call.
Thanks for the help.
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM
Ed,
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the
problemreports table not reference that key? If not, I would think a first
priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's problem
priorities to a temp table, then doing a simple pivot on priority to generate
your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single
string result that looks something like this, You have 12 open Problem
Reports: Priorities(High=5, Med=6, Low=1) The relavent columns from the two
tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible
Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's
the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees
WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found
in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
Database: 268.3.5/300 - Release Date: 4/3/2006
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. Thank you, that's what I was looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB how to.
[S] Dhandapani wrote: mysql show global variables like '%innodb%'; +-++ | Variable_name | Value | +-++ | have_innodb | YES OK, all thing is let default setting. mysql *|SET AUTOCOMMIT=0;|* Query OK, 0 rows affected (0.00 sec) mysql *|INSERT INTO CUSTOMER VALUES (15, 'John');|* Query OK, 1 row affected (0.00 sec) ERROR 1030 (HY000): Got error -1 from storage engine What is error ? I follow Document: http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html The error is same. Please teach me. Best regards, -- mysql *|ROLLBACK;|* Query OK, 0 rows affected (0.00 sec) mysql *|SELECT * FROM CUSTOMER;|* +--++ | A| B | +--++ || | +--++ the inset stattement has been rollbacked. Reference http://dev.mysql.com/doc/refman/5.0/en/innodb-transactions-with-different-apis.html Regards, Dhandapani Dba Sify Limited. Truong Tan Son wrote: Dear Sir, I install MySQL 5.0.18 on RH EL4, but do not know how to use InnoDB for ROLLBACK. ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-innodb make make install **/etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 with setting above, I could not use InnoDB. How to know InnoDB is installed, and start it ? Thanks you and best regards, -- ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password expire?
Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
Ed,e:
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that I have to make work together and the problemreport table does not reference the employeeid in the employees table. It was all create about ten years ago and the data has just always been migrated to the db du jour. I'm currently using MySQL 4.1x but most importantly I must be able to run the entire query in a single call.
Thanks for the help.
OK, that's doable in a subquery, and you can get the total from SUM(1),
so something like ...
SELECT
IF( SUM(1) = 0,
'',
CONCAT( 'You have ',
SUM(1),
' Problem Report',
IF(SUM(1) = 1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM (
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
) AS priorities;
PB
-
Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM
Ed,
The big slowdown in your query is likely the join on
ProblemReports.Responsible = CONCAT(Employees.FirstName, ' ',Employees.LastName))
Eek. Does the employees table not have a primary key, and does the problemreports table not reference that key? If not, I would think a first priority would be to fix that.
Meanwhile, you can simplify your monster query by writing the user's problem priorities to a temp table, then doing a simple pivot on priority to generate your sentence. Something like ...
DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority
FROM ProblemReports, Employees
WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL
AND Employees.UserName='User1'
SELECT COUNT(*) INTO @n FROM ProbSum;
SELECT
IF( @n = 0,
'',
CONCAT( 'You have',
@n,
'Problem Report',
IF(@n=1,'','s'),
': Priorities(High=',
SUM(IF(Priority='High',1,0)),
',Med=',
SUM(IF(Priority='Med',1,0)),
',Low=',
SUM(IF(Priority='Low' ,1,0)),
')'
)
)
FROM probsum;
DROP TEMPORARY TABLE probsum;
All this would be easier in a stored procedure, if you have MySql 5.
PB
-
Ed Reed wrote:
Can someone help me simplify this query please? It's meant to return a single string result that looks something like this, "You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)" The relavent columns from the two tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))), Concat('You have
', @a, ' open Problem Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='High')),', ',(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Med')),', ',(Select ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Low')),')')),''); No virus found in this incoming message.Checked by AVG Free E
dition.Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006
--
MySQL General Mailing List
For list archives:
