Find Missing Sequence Numbers
All - I've got a table that has an unsigned int that stores increasing sequence numbers that are stored in UDP payloads. Occasionally, messages get lost and we'll have missing numbers in the sequence. These are not auto_incrementing columns, the sequence numbers are assigned by the application that generates the network traffic. What's the easiest way to identify gaps in the sequence numbers? For example, sequence numbers might look like the following: 100, 101, 102, 112, ... How can I easily detect the gaps? Thanks, Mark Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Office: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630
Auto Increments and Other Keys
All - Running MySQL 4.0.17 under Red Hat 9, using MyISAM tables. I'm trying to add the auto_increment attribute to column in a table that already has a primary key defined. Here is the table I'm trying to create: CREATE TABLE IF NOT EXISTS dataTypes ( id int unsigned NOT NULL AUTO_INCREMENT KEY, dataType varchar(64) PRIMARY KEY, description tinytext, dbName tinytext, directory tinytext, updated timestamp, UNIQUE (dataType)) This create statement works w/o the id column. However, when I add the id column, I get an error on table creation. Is it complaining because it thinks I'm trying to add a second primary key (since all of the AUTO_INCREMENT attribute ints are unique)? What's the easiest way to do this? Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data and Timestamps
All - I'm running MySQL 4.0.13 under Red Hat 9. I've defined a table, and the last column is a timestamp type. +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | UNI | NULL| auto_increment | | dataType| varchar(64) | | PRI | || | description | tinytext | YES | | NULL|| | dbName | tinytext | YES | | NULL|| | directory | tinytext | YES | | NULL|| | updated | timestamp(14)| YES | | NULL|| +-+--+--+-+-++ I'm using load data to parse local CSV files. All of the columns are being properly parsed and inserted into the tables, however, the timestamp column is always all zeros (000). I don't have a column in the CSV file that maps to the timestamp column (didn't think I needed it based on the documentation). An insert off the command line with a standard insert works fine, timestamp has the proper value in it. Any suggestions? Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load Data and Timestamps
Sorry for the confusion, maybe I wasn't clear in the question. I reread my post and thought it might be misunderstood. The data in my CSV file isn't all zeros, but the timestamp column in my table is all zeros. According to the timestamp docs, auto update of the first timestamp column occurs if the column isn't specified in an INSERT or LOAD DATA INFILE statement. Let's say that my table has 3 columns (last is a timestamp type). My CSV file has two columns. Shouldn't my timestamp column get auto updated using LOAD DATA INFILE? Thanks, Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 2:17 PM To: Mark Riehl Cc: [EMAIL PROTECTED] Subject: Re: Load Data and Timestamps An invalid datetime, or in your case, an empty value, will be set to the nearest zero value representation. Original Message On 3/2/04, 1:06:50 PM, Mark Riehl [EMAIL PROTECTED] wrote regarding Load Data and Timestamps: All - I'm running MySQL 4.0.13 under Red Hat 9. I've defined a table, and the last column is a timestamp type. +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | UNI | NULL| auto_increment | | dataType| varchar(64) | | PRI | | | | description | tinytext | YES | | NULL| | | dbName | tinytext | YES | | NULL| | | directory | tinytext | YES | | NULL| | | updated | timestamp(14)| YES | | NULL| | +-+--+--+-+-++ I'm using load data to parse local CSV files. All of the columns are being properly parsed and inserted into the tables, however, the timestamp column is always all zeros (000). I don't have a column in the CSV file that maps to the timestamp column (didn't think I needed it based on the documentation). An insert off the command line with a standard insert works fine, timestamp has the proper value in it. Any suggestions? Thanks, Mark -- 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]
Doubles and selects
All - We're using MySQL under Linux to capture and analyze network traffic. We have two tables that store all the messages sent, and, another table that stores all the messages received. The sent table has an transmit column (as a double) and the received table has a received time sent time (from the incoming packet) both stored as doubles (seconds and fractions of seconds). In addition, there are a few other fields that distinctly define each message (e.g., source IP, source port, etc.). We're trying to perform some analysis on the data and seem to be having trouble matching columns of type double. For example, we loop through all of the received messages and try to find the corresponding message in the transmit table based on the transmit time (which appears on both tables). However, when the double transmit time is part of the query, we can't seem to find matches. Wrapping the transmit time in the select statement with ROUND() finds the matching records. Is it possible that some rounding is taking place here? We've considered storing time as two separate INT columns (like the timeval struct), however, we'd prefer to have 1 time column. Any suggestions? Thanks, Mark Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
IP Address Conversion
All - We're using MySQL under Linux to store data collected during network tests. One of our columns store the integer representation (in network byte order) of an IP address. Is there any current support in MySQL (or future planned support) to convert from the integer representation of an IP address to a dotted quad (e.g., 192.168.1.1) representation. Currently, we're doing this with a Perl script as part of our post test analysis. Ideally, I'd like to be able to see the dotted quad representation inside MySQL. We want to store the IP addresses as ints rather than chars (since it's more efficient to store as an int rather than the equivalent chars). Thanks, Mark
SQL Query and DATETIME
All - Is it possible to use the DATETIME column type inside of a SQL query ? Let's say I have a table named foo and that one of the columns (named start_time) is defined as a DATETIME. I'd like to do something like the following: select * from foo where (start_time 2002-05-30 13:00:00) What's the proper syntax for this? Can it be done? Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
