better way of doing 1800 sequential updates?
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
'Into outfile' doesn't include the column names. How can it be done?
When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel
RE: file permission
I didn't get it :( for example, I'm using: SELECT * INTO OUTFILE 'result_a.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM lista_switches; But this only saves the rows of data, with no column name; I would like to store the column name so the .csv is easier to read. If MySQL has options for storing, for example, the date as the last row, first column, even better; although I think I see what you mean, it reallly depends on the sleect statement? I f I manage to display what I want with the select, I'll be able to store it? That should work... But I though there was a -include_column_names option or something :) Which way should I go? Ariel -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 01 de marzo de 2006 10:58 Para: Ariel Sánchez Mora; mysql@lists.mysql.com Asunto: RE: file permission [snip] This function is great! Anyone know how to include the column names, table info, any extra miscellaneous info? I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. [/snip] Any select query will be handled properly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: file permission
This function is great! Anyone know how to include the column names, table info, any extra miscellaneous info? I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -Mensaje original- De: unplug [mailto:[EMAIL PROTECTED] Enviado el: martes, 28 de febrero de 2006 21:55 Para: mysql@lists.mysql.com Asunto: file permission Hi, When I use a query "SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table;", it will create a file with the following permission and owner. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv How can I change the default permission or the ownership of the file? Rgds, unplug -- 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: Number Searches
Probably the problem is in php, or, more probably, in how you store first and then look for the IP address in your query. You should try your query in the mysql console; varchars work almost with anything and I put this example where I look for an IP address with your table, and it finds it correctly. Hope this helps; if you can't find the problem, try little steps with select * from portal_forums_users where ip = '192.168.1.0'; To try and find where you have a problem. You can even try select * from portal_forums_users where ip like '%192.168.1.0%'; The % are wildcards, and that would take care of periods you inadvertenly added/erased. I really think this is not a MySQL problem. mysql> describe portal_forums_users; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | user_id | bigint(255) | | PRI | NULL| auto_increment | | ip| varchar(200) | YES | | NULL|| | signup_date | varchar(30) | YES | | NULL|| | city | varchar(200) | YES | MUL | NULL|| | state | varchar(100) | YES | | NULL|| | email_address | varchar(200) | YES | | NULL|| | username | varchar(100) | YES | | NULL|| | password | varchar(100) | YES | | NULL|| | yim | text | YES | | NULL|| | aol | text | YES | | NULL|| | web_url | text | YES | | NULL|| | post_count| varchar(255) | YES | | NULL|| | info | text | YES | | NULL|| | sig | text | YES | | NULL|| | avatar| text | YES | | NULL|| | css_id| int(11) | YES | | NULL|| | mod_f | varchar(20) | YES | | NULL|| | admin | varchar(20) | YES | | NULL|| +---+--+--+-+-++ 18 rows in set (0.00 sec) mysql> select * from portal_forums_users; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | | 2 | 10.100.1.1 | y | y| y | y | y | y| y| y| y | y +-+-+-+--+---+---+--+--+--+--+-+ 2 rows in set (0.00 sec) mysql> select * from portal_forums_users where ip = '192.168.1.0'; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | +-+-+-+--+---+---+--+--+--+--+-+ 1 row in set (0.00 sec) -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 17:35 Para: Ariel Sánchez Mora CC: mysql@lists.mysql.com Asunto: RE: Number Searches On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: > So far i've been able to store ip addresses as strings like you would > type them in DOS, for ex, '192.168.0.1'. This serves me great since my > application uses IP addresses as strings in all cases. I've done > queries with the IP column , for example, select office_name from > table_1 where ip='10.100.1.1'; and have never had any problems. > However, if you plan on sorting based on this column, strings with > periods do not behave correctly, and the answers to my previous > question on this list do not apply; it makes a good aproximation, > though. > > Hope this helps
RE: Number Searches
So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. Hope this helps, but I must admit I am not sure if this answers your question. An example in the mysql console would be great for clearing up your objetive. Regards, Ariel -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 15:53 Para: MySQL-List Asunto: Number Searches Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? Thanks, Will -- 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]
RV: Same question, better example / SOLVED, thanks!
Hi Sheeri, you are correct in both the MySQL tips and in your suggestion that I
should include my test when I think something doesn't work ;) Thanks a lot!
I realize now that when I've sent a thank you note, I have only sent it to the
responder instead of all the list; my problem was sorted out (no pun intended)
and I am deeply thankful to all of you for your time in reading the email.
Warm thanks to David, Leo and Sheeri for their tips and examples; hope I can
soon contribute back :) A small thing: I think I found a bug in Delphi 7 with
dbExpress that makes both these correct solutions behave differently; It
probably behaves this way because the +0 converts the varchars into some
numeric form and Delphi can order them correctly. I explain better in the
forwarded email below; in a nutshell, Delphi re-ordered incorrectly the column
when I used CAST() but not when I used +0.
Ariel
-Mensaje original-
De: Ariel Sánchez Mora
Enviado el: lunes, 20 de febrero de 2006 10:30
Para: 'leo huang'; 'Logan, David (SST - Adelaide)'
Asunto: RE: Same question, better example
Thanks a lot Leo, that worked wonders. David Logan from HP Australia corrected
my use of CAST and this also works, in MySQL:
select stri from prueba order by CAST(stri as unsigned) desc;
Alas, dbexpress in Delphi 7 had problems when presenting it in a grid (it
re-ordered what MySQL gave, in the old manner, although everywhere else I
tried, the results were displayed correctly). I had decided not to bother David
anymore, since I could change my query and use a > (some_value), which also
worked fine for my project. The dbexpress problem does not happen in your case,
so your solution is greatly appreciated, and now I have more options. Thank you
a lot, both, for your time! I hope that if you ever have a similar problem with
dbExpress or Delphi, you can use this information :)
Thanks again,
Ariel
-Mensaje original-
De: leo huang [mailto:[EMAIL PROTECTED]
Enviado el: lunes, 20 de febrero de 2006 2:47
Para: Ariel Sánchez Mora
CC: mysql@lists.mysql.com
Asunto: Re: Same question, better example
Ariel,
You can try this:
mysql>select stri from prueba order by stri+0 desc;
Leo Huang
2006/2/17, Ariel Sánchez Mora <[EMAIL PROTECTED] <mailto:[EMAIL
PROTECTED]> >:
mysql> describe prueba;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| inte | int(2) | YES | | NULL| |
| stri | char(2) | YES | | NULL| |
+---+-+--+-+-+---+
2 rows in set ( 0.00 sec)
mysql> select * from prueba;
+--+--+
| inte | stri |
+--+--+
|1 | 1|
|2 | 2|
|3 | 3|
|4 | 4|
|5 | 5|
|6 | 6|
|7 | 7|
|8 | 8|
|9 | 9|
| 10 | 10 |
+--+--+
10 rows in set (0.00 sec)
-->Is there a way I can make this:
mysql> select stri from prueba order by stri desc;
+--+
| stri |
+--+
| 9|
| 8|
| 7|
| 6|
| 5|
| 4|
| 3|
| 2|
| 10 |
| 1|
+--+
10 rows in set (0.00 sec)
-->come out like this:
mysql> select inte from prueba order by inte desc;
+--+
| inte |
+--+
| 10 |
|9 |
|8 |
|7 |
|6 |
|5 |
|4 |
|3 |
|2 |
|1 |
+--+
10 rows in set (0.00 sec)
I'm using MySQL 4.1.14 in windows 2000.
Thanks!
Ariel
-Mensaje original-
De: sheeri kritzer [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
Enviado el: lunes, 20 de febrero de 2006 11:47
Same question, better example
mysql> describe prueba; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | inte | int(2) | YES | | NULL| | | stri | char(2) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> select * from prueba; +--+--+ | inte | stri | +--+--+ |1 | 1| |2 | 2| |3 | 3| |4 | 4| |5 | 5| |6 | 6| |7 | 7| |8 | 8| |9 | 9| | 10 | 10 | +--+--+ 10 rows in set (0.00 sec) -->Is there a way I can make this: mysql> select stri from prueba order by stri desc; +--+ | stri | +--+ | 9| | 8| | 7| | 6| | 5| | 4| | 3| | 2| | 10 | | 1| +--+ 10 rows in set (0.00 sec) -->come out like this: mysql> select inte from prueba order by inte desc; +--+ | inte | +--+ | 10 | |9 | |8 | |7 | |6 | |5 | |4 | |3 | |2 | |1 | +--+ 10 rows in set (0.00 sec) I'm using MySQL 4.1.14 in windows 2000. Thanks! Ariel
Hi, newbie question on a select statement
I searched the other lists and couldn't find one that was more appropiate for this question; if there is, please tell me so :) I am monitoring networking equipment and so far I'm saving all my data in a MySQL database (hence an email to this list). I'm using only VARCHARs because the SNMP agent returns only strings and I didn't find enough a reason for converting the different types of answers, since most string comparations are donde correctly; I received both text and numbers from the monitoring tasks and wanted to keep it simple. However when I execute this select statement I am getting this problem: select info_oficina,valorSNMP from ultimas_respuestas_snmp where columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10; info_oficina valorSNMP CSF Desamparados error Periferica Palmares 4 CSF San Pedro4 Sucursal Guapiles4 Periferica Pentagono San Pablo 30 Periferica Tibas 3 Periferica Buenos Aires 3 Sucursal Turrialba 3 Ventanilla Florencia 3 CSF del Sur (Ciudad Neilly) 3 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, etc. The summary question is: given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL to order it as if they were integer values, so that instead of 99,98,97,96,95,94,93,92,91,90,9,89 I'd have 99,98,97,96,95,94,93,92,91,90,89,88 Apparently, CAST() does not have an effect (but feel free to prove me wrong) Thanks! Ariel
