the max length of one SQL statement
Hi guys, Anybody knows that how many bytes the max length of on SQL statement can be in MySQL database? I know it's 64KB in Oracle. Thanks. *^_^* ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/
Re: the max length of one SQL statement
hi, According to the transfer limit, ... 16M? 2009/3/20 raid fifa raid_f...@yahoo.com.cn: Hi guys, Anybody knows that how many bytes the max length of on SQL statement can be in MySQL database? I know it's 64KB in Oracle. Thanks. *^_^* ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: copy blob between tables may get in consistent data?
hi, I found this: http://bugs.mysql.com/bug.php?id=34300 in the bug system. As it descibed, there is chance which I get corrupted data when read BLOB. Now I wonder why I also get corrupted data when write BLOB into table... I tried this: step 1: read BLOB data from TABLE1 step 2: log the BLOB data step 3: write the data into TABLE2 result: select hex(data) from TABLE1 is the same as what I've logged in step2 select hex(data) from TABLE2 is different with select hex(data) from TABLE2 Any suggestions? 2009/3/19 Cui Shijun rancp...@gmail.com: hi, I'm using mysql-5.1.22, innodb. For some reason, I need to add some columns to a table without locking it, so I do this: insert into TABLE2(col1, col2, ... ,colN) select col1, col2, ... colN from TABLE1 where ...(primary key traversal condition). which TABLE1 is under use, and TABLE2 has new structure. Before I really do the copy task, I created some triggers to synchronize the insert update. When I finally complete the task, I find ALL blob column appear to result in inconsistent status with the orignal table. There are users which do query and update on TABLE1. So what's going wrong? Thank you Cui Shijun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL versus PostgreSQL for GPS Data
Jim Ginn wrote: Not sure why you you need the trucks location 'every second' ie: 31,536,000 rows per year per truck ? doing every 30 seconds seems more manageable at 1,051,200 rows per year per truck? Maybe better at 60 seconds? OpenGGD is also designed to deliver GPS data in real time; we have customers that sometimes want to track their trucks in real time, that's why we think the worst scenario could be one position per second. Juan Karlos 2009/3/18 Jim Ginn j...@oats.com Juan: We've had success with spatial indexes and mysql on our sites however our numbers are smaller: http://brokersnetwork.com (200,000+ records) http://yearlyrentals.com (200,000+ records) http://avalonrealestate.com/map.php (4,400+ records) ... Not sure why you you need the trucks location 'every second' ie: 31,536,000 rows per year per truck ? doing every 30 seconds seems more manageable at 1,051,200 rows per year per truck? Maybe better at 60 seconds? Jim Juan, On Wed, Mar 18, 2009 at 11:14 AM, Juan Pereira juankarlos.open...@gmail.com wrote: Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? I think it depends on exactly what you want to do with the data. MySQL has fairly poor support for spatial types but you can achieve a lot just manipulating normal data types. Postgres (which i know nothing about) appears to have better spatial support via postgis http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html http://postgis.refractions.net/documentation/manual-1.3/ In terms of data size you should not have a problem, I think you need to look at how you are going to query the tables. Cheers, Ewen Thanks in advance Juan Karlos. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@oats.com
RE: Performance Spamassin PostgreSQL vs MySQL
We had an awkward setup, which forced us to use PGSQL for SpamAssassin. Unfortunately the SA queries are not processed well by PGSQL. Back in January we switched SA processing to MySQL. Bingo! Instant improvement in overall performance, and no PGSQL maintenance required. This is not sophisticated box - about 3 yr old, 2Mbytes RAM. - Miles Date: Wed, 18 Mar 2009 12:53:45 -0700 Subject: Performance Spamassin PostgreSQL vs MySQL From: mussa...@csz.com To: mysql@lists.mysql.com We are using the PostgreSQL currently to store the Bayes information. It seems to periodically spend a lot of time 'vacumming' which of course drives up disk load. The system admin has adjusted it so it only does this at low load. I'm curious if anyone has actually tested the PostgreSQL vs MySQL versions. We are currently running a uniprocessor system (Linux version 2.6.18-6-vserver-amd64 (Debian 2.6.18.dfsg.1-24). System appears disk limited, we have the files on hardware raid 0 and have moved nearly everything else off that set (they are the fastest drives). Just curious. Thanks. Bill Mussatto CyberStrategies, Inc. www.csz.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=one.point@hotmail.com _ Share photos with friends on Windows Live Messenger http://go.microsoft.com/?linkid=9650734
Re: MySQL versus PostgreSQL for GPS Data
Juan: Still seems excessive but in that case, ignore inserts that have no change in lat / lon ... Jim Jim Ginn wrote: Not sure why you you need the trucks location 'every second' ie: 31,536,000 rows per year per truck ? doing every 30 seconds seems more manageable at 1,051,200 rows per year per truck? Maybe better at 60 seconds? OpenGGD is also designed to deliver GPS data in real time; we have customers that sometimes want to track their trucks in real time, that's why we think the worst scenario could be one position per second. Juan Karlos 2009/3/18 Jim Ginn j...@oats.com Juan: We've had success with spatial indexes and mysql on our sites however our numbers are smaller: http://brokersnetwork.com (200,000+ records) http://yearlyrentals.com (200,000+ records) http://avalonrealestate.com/map.php (4,400+ records) ... Not sure why you you need the trucks location 'every second' ie: 31,536,000 rows per year per truck ? doing every 30 seconds seems more manageable at 1,051,200 rows per year per truck? Maybe better at 60 seconds? Jim Juan, On Wed, Mar 18, 2009 at 11:14 AM, Juan Pereira juankarlos.open...@gmail.com wrote: Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? I think it depends on exactly what you want to do with the data. MySQL has fairly poor support for spatial types but you can achieve a lot just manipulating normal data types. Postgres (which i know nothing about) appears to have better spatial support via postgis http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html http://postgis.refractions.net/documentation/manual-1.3/ In terms of data size you should not have a problem, I think you need to look at how you are going to query the tables. Cheers, Ewen Thanks in advance Juan Karlos. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@oats.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL General Discussion question
Sorting a varchar field alphabetically with correct numerical order help needed I have a varchar 50 field that contains product names, which are typically numerical, alphabetical and punctuation thrown in. I would like to have them returned in some sort of order that is roughly alphabetical, but with the numbers in numerical order. The basic Order By clause does not do it correctly. An example is: SELECT setname FROM sets ORDER BY setname Sample values of setname are: 658 #1 JCAL 011 #2 NOV #11 NOV #12 NOV 985 ABC #123 NOV The results I get are: #1 JCAL #11 NOV #12 NOV #123 NOV #2 NOVwrong 011 658 985 ABC The results I want are: #1 JCAL #2 NOV should be here #11 NOV #12 NOV #123 NOV 011 658 985 ABC In the above, the #2 JCAL should be second, otherwise, list is correct. I could also live with the values beginning with # or any alpha character coming after the numerical ones, but the 1, 11, 2, order is the issue. Does anyone have any idea how to do this? I have been playing around with various suggested ways, including casting and converting, but so far have not been able to solve this. Any ideas would be greatly appreciated. Thanks, Rich
Re: MySQL General Discussion question
Hi Rich,
I believe that since they are definitely considered as strings they are
sorted as such,
how can you pretend that '#2 NOV' is considered as a number?
If you cannot define a rule in the structure of the data it is
impossible to sort, not only in sql, but in life!
IF we take for granted that values start from first or second position
('#') you can use something like this:
SELECT setname from sets order by CASE SUBSTRING(setname,1,1) WHEN '#' THEN
SUBSTRING(setname,2) ELSE setname END;
Cheers
Claudio
Richard Gagnon wrote:
Sorting a varchar field alphabetically with correct numerical order help
needed
I have a varchar 50 field that contains product names, which are typically
numerical, alphabetical and punctuation thrown in. I would like to have them
returned in some sort of order that is roughly alphabetical, but with the
numbers in numerical order. The basic Order By clause does not do it
correctly.
An example is:
SELECT setname FROM sets ORDER BY setname
Sample values of setname are:
658
#1 JCAL
011
#2 NOV
#11 NOV
#12 NOV
985
ABC
#123 NOV
The results I get are:
#1 JCAL
#11 NOV
#12 NOV
#123 NOV
#2 NOVwrong
011
658
985
ABC
The results I want are:
#1 JCAL
#2 NOV should be here
#11 NOV
#12 NOV
#123 NOV
011
658
985
ABC
In the above, the #2 JCAL should be second, otherwise, list is correct. I
could also live with the values beginning with # or any alpha character
coming after the numerical ones, but the 1, 11, 2, order is the issue.
Does anyone have any idea how to do this? I have been playing around with
various suggested ways, including casting and converting, but so far have
not been able to solve this. Any ideas would be greatly appreciated.
Thanks, Rich
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL General Discussion question
I'd use a cursor loop, and parse through the string extracting the numeric
characters, convert the numeric characters to a Int to be used as a
sequence number. Insert the sequence number and the string into a temp
table, (or add seqnum to the current table) and
select from X order by seqnum.
I won't say anymore,
Mike, the crazy null guy!
Hi Rich,
I believe that since they are definitely considered as strings they are
sorted as such,
how can you pretend that '#2 NOV' is considered as a number?
If you cannot define a rule in the structure of the data it is
impossible to sort, not only in sql, but in life!
IF we take for granted that values start from first or second position
('#') you can use something like this:
SELECT setname from sets order by CASE SUBSTRING(setname,1,1) WHEN '#'
THEN SUBSTRING(setname,2) ELSE setname END;
Cheers
Claudio
Richard Gagnon wrote:
Sorting a varchar field alphabetically with correct numerical order help
needed
I have a varchar 50 field that contains product names, which are
typically
numerical, alphabetical and punctuation thrown in. I would like to have
them
returned in some sort of order that is roughly alphabetical, but with
the
numbers in numerical order. The basic Order By clause does not do it
correctly.
An example is:
SELECT setname FROM sets ORDER BY setname
Sample values of setname are:
658
#1 JCAL
011
#2 NOV
#11 NOV
#12 NOV
985
ABC
#123 NOV
The results I get are:
#1 JCAL
#11 NOV
#12 NOV
#123 NOV
#2 NOVwrong
011
658
985
ABC
The results I want are:
#1 JCAL
#2 NOV should be here
#11 NOV
#12 NOV
#123 NOV
011
658
985
ABC
In the above, the #2 JCAL should be second, otherwise, list is correct.
I
could also live with the values beginning with # or any alpha character
coming after the numerical ones, but the 1, 11, 2, order is the issue.
Does anyone have any idea how to do this? I have been playing around
with
various suggested ways, including casting and converting, but so far
have
not been able to solve this. Any ideas would be greatly appreciated.
Thanks, Rich
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mich...@j3ksolutions.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
