primary key
Im trying to set up a primary key of server(text), date (date), hour (small int) but when i try to include the server field in the key it replies with ALTER TABLE `exim` DROP PRIMARY KEY , ADD PRIMARY KEY ( `date` , `hour` , `server` ) #1170 - BLOB/TEXT column 'server' used in key specification without a key length i have googled, but not much is relevant to my example i dont think.. CREATE TABLE `exim` ( `date` date NOT NULL default '-00-00', `server` longtext NOT NULL, `hour` tinyint(4) NOT NULL default '0', `count` smallint(6) NOT NULL default '0', PRIMARY KEY (`date`,`hour`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats'; thanks Ronan -- Ronan McGlue Analyst / Programmer CMC Systems Group Queens University Belfast -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key
Ronan [EMAIL PROTECTED] wrote on 16/02/2006 11:56:18: Im trying to set up a primary key of server(text), date (date), hour (small int) but when i try to include the server field in the key it replies with ALTER TABLE `exim` DROP PRIMARY KEY , ADD PRIMARY KEY ( `date` , `hour` , `server` ) #1170 - BLOB/TEXT column 'server' used in key specification without a key length i have googled, but not much is relevant to my example i dont think.. CREATE TABLE `exim` ( `date` date NOT NULL default '-00-00', `server` longtext NOT NULL, `hour` tinyint(4) NOT NULL default '0', `count` smallint(6) NOT NULL default '0', PRIMARY KEY (`date`,`hour`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats'; A LONGTEXT field may be up to 4 Gigabytes long. MySQL cannot (and, I would think, should not) include thenwhole 4Gb in the index. It therefore needs you to indicate how many characters of the server field it should actually use in the index. To get it to use only the first 64 characters in the key, you should put in server(64) (the single quotes you are using are necesary only if you wish to give a colum the same name as a reserved wioord - a vary bad practice). However, if you are using it as a PRIMARY KEY or UNIQUE KEY, *you* must guarantee that those firat 64 characters are unique. within any given date and hour i.e., I would guess, that your servers are unique within the first 64 (or however many you choose) characters. May I suggest that a more conventional way to do what I think you are doing woiuld be to have two tables. Allocate each server a number, and put the number in the exim table. Then have another table to convert the server name to a number. It is then trivially easy to use that table to convert from server number to name or vice versa. And the server name no l;onger has to be unique in the first N characters: as long as the names differ, the table will work. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key
Hi there, Im trying to set up a primary key of server(text), date (date), hour (small int) but when i try to include the server field in the key it replies with ALTER TABLE `exim` DROP PRIMARY KEY , ADD PRIMARY KEY ( `date` , `hour` , `server` ) #1170 - BLOB/TEXT column 'server' used in key specification without a key length i have googled, but not much is relevant to my example i dont think.. Well, you could start with the documentation. server apparently, is a blob-type column. Lo and behold, the table definition: CREATE TABLE `exim` ( `date` date NOT NULL default '-00-00', `server` longtext NOT NULL, Yep. A BLOB column. Why, I wonder. Either way - check the error message, read it. From what I can read from it - and the MySQL documentation - you cannot just create a constraint/index on a BLOB column. `hour` tinyint(4) NOT NULL default '0', `count` smallint(6) NOT NULL default '0', PRIMARY KEY (`date`,`hour`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats'; 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]
APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
hi, since 3 days, i'm trying to connect to MYSQL 5.0.18 from php scripts using Apache et Macromedia 2004 Dreamweaver. Could someone help me doing this? Best regard. - Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et l'international.Téléchargez la version beta.
Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
persant mpote [EMAIL PROTECTED] wrote on 02/16/2006 10:29:37 AM: hi, since 3 days, i'm trying to connect to MYSQL 5.0.18 from php scripts using Apache et Macromedia 2004 Dreamweaver. Could someone help me doing this? Best regard. - Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et l'international. Téléchargez la version beta. What exactly have been the commands you have tried and what exactly were the error messages you received? We can't help you until you provide us with more details. Have you tried the advice in the manual? http://dev.mysql.com/doc/refman/5.0/fr/can-not-connect-to-server.html http://dev.mysql.com/doc/refman/5.0/fr/starting-server.html What successes or failures have you had? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
(mysqldump) Serial output. . .?
All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Thanks Dathan, But I'm more concerned with ALTER than anything. As I said, I basically want to be able to output field, type, etc. by row so that *if* the receiving DB needs to add a field it can do so without having to nuke the table with all data in it just to add all fields again with a CREATE statement. The final purpose is for the sake of data syncronization, not replication. Regards, Michael -- Original Message -- From: Dathan V. Pattishall [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 12:06:40 -0800 Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/16/2006 03:22:51 PM: Thanks Dathan, But I'm more concerned with ALTER than anything. As I said, I basically want to be able to output field, type, etc. by row so that *if* the receiving DB needs to add a field it can do so without having to nuke the table with all data in it just to add all fields again with a CREATE statement. The final purpose is for the sake of data syncronization, not replication. Regards, Michael -- Original Message -- From: Dathan V. Pattishall [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 12:06:40 -0800 Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Hi Michael, Have you considered a Master-Master (or more if required) replication setup for achieving what you mentioned below? That would certainly provide the I'll show you mine if you show me yours scenario. I can recommend the High Performance MySQL book by Jeremy Zawodny as it has an example of the setup required. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 7:14 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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: (mysqldump) Serial output. . .?
Rather than changing the function of the mysqldump program, why not massage its output to your specifications. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Thursday, February 16, 2006 14:44 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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]
User is rejected because of IP, but hostname is allowed
I am trying to connect to my MySQL 4.x server from a new machine. I am getting this error: Got error: 1045: Access denied for user 'user'@'192.168.2.56' (using password: YES) when trying to connect BUT I have a user setup in mysql with allowed access from '%.willcomminc.com'. The machine I'm trying to connect from is named dbdev.willcomminc.com. I did a reverse lookup from the database server and the IP 192.168.2.56 does indeed lookup to dbdev.willcomminc.com. Why is MySQL ignoring the hostname and using the IP? I did google for this first but didn't come up with anything. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User is rejected because of IP, but hostname is allowed
did u start mysql with --skip-name-resolve ??? Kishore Jalleda On 2/16/06, Ryan Stille [EMAIL PROTECTED] wrote: I am trying to connect to my MySQL 4.x server from a new machine. I am getting this error: Got error: 1045: Access denied for user 'user'@'192.168.2.56' (using password: YES) when trying to connect BUT I have a user setup in mysql with allowed access from '%.willcomminc.com'. The machine I'm trying to connect from is named dbdev.willcomminc.com. I did a reverse lookup from the database server and the IP 192.168.2.56 does indeed lookup to dbdev.willcomminc.com. Why is MySQL ignoring the hostname and using the IP? I did google for this first but didn't come up with anything. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User is rejected because of IP, but hostname is allowed
Nope. _ From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, February 16, 2006 4:32 PM To: Ryan Stille Cc: mysql@lists.mysql.com Subject: Re: User is rejected because of IP, but hostname is allowed did u start mysql with --skip-name-resolve ??? Kishore Jalleda
Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
First you need to make sure that you have a running mysql server for the client mysql program to connect to. If the server is not runing, you will obviously not be able to connect to it. I find the following utility very helpfull - been messsing around with it today. http://www.student.nada.kth.se/~f91-men/qps/ It give a graphical UI, into the running processes. I noticed using qps, that sometimes the mysqld_safe script loads into memory, but does not actually load a mysqld process. Also, version 4.0.21 only spawns one child process, where 5.0.18 starts several processes in memory. I had the RPM 5.0.18 version running, and two other seperate binary distro's of 5.0.18, all on different sockets and ports. I've given up with using mysqld_safe to run the mysqld daemon. It is to unpredictable. qps also shows all the command line arguments passed to mysqld, which shows at a glance which ports and sockets the daemons are listening on. It is included with SuSE 9.2 pro, as an extra package. I think it lives on the DVD's. You can download qps from the link above. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 16 Feb 2006, persant mpote wrote: To: mysql@lists.mysql.com From: persant mpote [EMAIL PROTECTED] Subject: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2 hi, since 3 days, i'm trying to connect to MYSQL 5.0.18 from php scripts using Apache et Macromedia 2004 Dreamweaver. Could someone help me doing this? Best regard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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
RE: Hi, newbie question on a select statement
Hi Ariel, It works fine for me localhost.testshow create table mytest\G *** 1. row *** Table: mytest Create Table: CREATE TABLE `mytest` ( `id` int(11) NOT NULL auto_increment, `test_col` varchar(10) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.test 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by test_col; ++--+ | id | test_col | ++--+ | 1 | 1| | 10 | 10 | | 12 | 11 | | 2 | 2| | 11 | 20 | | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| ++--+ 12 rows in set (0.01 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by CAST(test_col as unsigned); ++--+ | id | test_col | ++--+ | 1 | 1| | 2 | 2| | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| | 10 | 10 | | 12 | 11 | | 11 | 20 | ++--+ 12 rows in set (0.00 sec) Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 11:29 AM To: mysql@lists.mysql.com Subject: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Byte Swapping
David Godsey wrote: This might be an unusual question because mysql always stores in little endian for all platforms (if I'm not mistaken). However, I am receiving data from several different platforms (custom boards with various processors) in raw form. I will have stored as a configuration option what endianess it is. So what I need to do is write a procdure, or use a mysql function (if there is one available) to byte swap the data (for integer types). I'm really just hoping someone has done this, or it is implemented as a mysql function and I just wasn't able to find it. Any help will be appreciated. Thanks. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Hi, David You could also try using Bit Functions: http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
