Do not use on any
shared computer
May 9, 2008 09:57am pdt
03.25.2008 at 12:30PM PDT, ID: 23268268
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

MySQL table - Need to preserve natural order.
I have written a perl script that parses a log file, which is just a text file, every 5 minutes and populates a MySQL table.  The log file grows as time goes on, and rather than attempt to determine if a line from the text file had been added during a prior run, I am just deleting all records inserted for that day and processing the text file in it's entirety.  I have noticed that the records that get inserted are no longer in their natural order, probably due to MySQL reusing deleted rows.  The log file can consist of up to 60,000 lines, so if the log file processing takes place each 5 minutes that's about 288 times per day that tens of thousands of rows are getting deleted and re-added.  How can I preserve the natural order of the lines that appeared in the text file?  I thought I could add a field to the table called 'record' and make it an unsigned bigint autoincrement field, but I thought I would ask if there were any more elegant solutions.

Thanks,
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: CousinDupree
Solution Provided By: Adam314
Participating Experts: 3
Solution Grade: A
Views: 0
Translate:
Loading Advertisement...
03.25.2008 at 12:36PM PDT, ID: 21205277

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.25.2008 at 12:49PM PDT, ID: 21205406

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.25.2008 at 02:37PM PDT, ID: 21206511

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.25.2008 at 03:40PM PDT, ID: 21206991

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.25.2008 at 07:26PM PDT, ID: 21208454

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.26.2008 at 01:13AM PDT, ID: 21209646

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
03.25.2008 at 12:36PM PDT, ID: 21205277

Rank: Guru

My suggestion would be to have a timestamp on each of your log records you can then:
a. parse your logs file(s) and sort them before entering into the db
OR
b. just insert all the records into a temporary table, sort the records based on the date, extract them and put them in the logs table.
Assisted Solution
 
03.25.2008 at 12:49PM PDT, ID: 21205406

Rank: Genius

Or you could delete or rotate your logs when they are processed.  This would also probably make the script run much faster, as you would only deal with the new log entries whenever the script is run.

Or you could have a perl script running continously, using File::Tail
http://search.cpan.org/~mgrabnar/File-Tail-0.99.3/Tail.pm
The script would update the database as new log entries were created.
Accepted Solution
 
03.25.2008 at 02:37PM PDT, ID: 21206511
Each log file is named as a function of the date, so I can get the date from the log file name, and each event in the log file has a time associated with it, down to the second.  The issue is that there are usually several events within the same second.  Querying and sorting by date and time won't necessarily guarantee the records are presented in the same order that they originally appeared in the log file.  That is why I was looking for an efficient way to process the log files and provide a way to reconstruct the original chain of events when pulling the records using SQL.  Would it be possible to generate a time stamp that would be unique for each row entered?
 
03.25.2008 at 03:40PM PDT, ID: 21206991

Rank: Genius

The timestamp data type has resolution down to 1 second, so that will not help.

Database tables are inherently unsorted.  Unless the log entries themselves have a field which can be used to determine order, the only way to maintain the same order as they are entered is to have some field be used to store that order - the autoincrement is the way to do this.

The advantage you get with only adding new log entries instead of deleting and adding all entries is speed, and your autoincrement field won't have numbers skipped (if you add 1,2,3, then delete 3, the next add will have number 4).
 
03.25.2008 at 07:26PM PDT, ID: 21208454
I guess I could do a select count(*) for records with a date of the day in question, and ignore all records in the log file that have already been entered, only add records with line numbers greater than the count.  I would then avoid large gaps in the record number field.  I wonder if MySQL will let me edit an autoincrement field if I need to?
 
03.26.2008 at 01:13AM PDT, ID: 21209646

Rank: Guru

> I wonder if MySQL will let me edit an autoincrement field if I need to?

No, and why would you?

Import the rows (using the date of the file) and later sort on Date, Time, and autoincrement Id.

/gustav
Assisted Solution
 
 
20080206-EE-VQP-25 / EE_QW_2_20070628