Re: set "DEC" as a column name : forbidden
You are right, the "DEC" is a reserved keyword in the MySQL system. We use "DE" in our databases, which will be better then "DECL". cheers, Gilles MISSONNIER wrote: Hello I could not find the answer through the online "Search the MySQL manual". I run MySQL 4.1 In astronomy, RA and DEC are widely used coordinate names. Then I try to add a column named "DEC" : mysql> alter table my_table add dec float; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dec float' at line 1 UPPER case lead to the same error. It seems that the reason is that "DEC" is a keyword standing for "decimal". I do not understand why this cannot be allowed for a column name. Is there a turn around ? This is annoying ; I add to name the column as "DECL" which is much less meaning full in the astronomy community. thanks, =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] -------- -- = Chenzhou Cui National Astronomical Observatory | Tel: (8610)64841695/64872500 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 25% slower
, `Is_g` tinyint(2) default NULL, `Ixi` float(6,2) default NULL, `Ieta` float(6,2) default NULL, KEY `DEdeg` (`DEdeg`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Did you use prepared statements. This should be enough to comment.In general using single insert statements is the most inefficient way to insert data. No. I just use the single insert mode. I didn't know batch mode would improve the performence at that time. :) -- ==== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 is 25% slower then 4.1
The file is in fix column width format. If it is in CSV format, I don't
need a program to read it.
The first few lines are as following:
---
usnob_read: no zone specified, read from South Pole
#USNOB (whole)
#USNO-B1.0 Tycho-2RA (J2000) DecsRA sDE Epoch pmRA
pmDE P spA spD Fit N MsY| Bmag1 C Surv. cl <-xi-><-eta>| Rmag1 C Surv.
cl <-xi-><-eta>| Bmag2 C Surv. cl <-xi-><-eta>| Rmag2 C Surv. cl
<-xi-><-eta>| Imag C Surv. cl <-xi-><-eta>| ; r(")
-001 001.087156-89.918600 65 0 1986.9 -2
-6 8 4 0 0 0 3 ..Y| --- - ---- | 14.00 1 5-001 9
+00.04-00.01| 14.43 1 4-001 3 -00.04+00.00| 13.68 1 6-001 0
-00.02+00.00| --- - ---- |
-002 001.330223-89.933234 5 192 1986.9 +2
+0 6 0 12 0 1 3 ...| --- - ---- | 17.26 1 5-001 10
+00.00+00.14| 17.43 1 4-001 8 -00.01-00.10| 17.20 1 6-001 0
-00.01-00.06| --- - ---- |
-003 003.255817-89.964103 2 80 1986.9 +8
+18 8 0 5 0 0 3 ...| --- - ---- | 19.22 1 5-001 8
+00.00+00.06| 21.20 1 4-001 2 -00.01-00.04| 19.66 1 6-001 0
-00.01-00.03| --- - ---- |
-004 003.646575-89.906014 204 224 1986.9 +0
+0 0 0 0 3 3 3 ...| --- - ---- | 19.50 1 5-001 3
+00.17-00.29| 19.86 1 4-001 8 +00.10+00.01| 18.60 1 6-001 0
-00.29+00.26| --- - ---- |
-005 006.311025-89.952395 114 45 1986.9 +0
+0 0 0 0 1 0 3 ...| --- - ---- | 18.10 1 5-001 9
-00.11-00.05| 18.61 1 4-001 8 -00.06-00.01| 18.23 1 6-001 0
+00.16+00.05| --- - ---- |
Gary Richardson wrote:
Also, if the file looks anything like a CSV file, I recommend using
LOAD DATA INFILE
http://dev.mysql.com/doc/refman/4.1/en/load-data.html
You'll probably load that data in half to a quarter of the time.
On 11/30/05, Daniel Kasak <[EMAIL PROTECTED]> wrote:
Chenzhou Cui wrote:
I didn't use multiple insert statements and "LOAD DATA INFILE", but
only "insert into" statement. The Java program reads one line from the
source file, and then execute an "insert" statement.
I can't comment on the speed of 5.0.x vs 4.1.x, but I can suggest that
you optimize this 'insert' process.
Instead of issuing a new insert command per record, how about using
placeholders and binding values? I don't know any Java, but from Perl
you can do this. Actually, I'm not certain that MySQL supports this yet
or not - you'd have to check your server version and drivers.
Or you could do something like collect a series of records - say 100
records at a time, and issue an insert query that includes all of them, eg:
insert into SomeTable ( field_1, field_2, field_3 ) values
( 3, 56, 45 ),
( 45, 3456, 345 ),
( 345, 76, 345 ),
( 345, 45, 546 )
This is a lot faster than issuing separate inserts per record. You don't
want to include *too* many records at once - there's a maximum packet
size or something like that that you can't exceed.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Chenzhou Cui (China-VO Project)
National Astronomical Observatory | Tel: (8610)64841695
Chinese Academy of Sciences | FAX: (8610)64878240
Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED]
Beijing 100012, China | WWW: www.lamost.org/~cb
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 is 25% slower then 4.1
After 4 days and a half, my program finished last night. The 1045175762 rows costed the Java program 109 hours to load. The speed is about 9.6 million per hour. I didn't use multiple insert statements and "LOAD DATA INFILE", but only "insert into" statement. The Java program reads one line from the source file, and then execute an "insert" statement. The configuration of my server is as follows: Two Intel Xeon 2.8GHz CPUs 3GB memory Two RAID 5 of SATA disks The huge file locates on a RAID, the MySQL server datadir locates on another RAID. The two RAIDs link to the server by Ultra SCSI 320 interface. Gary Richardson wrote: Are you doing single insert statements, multiple insert statements or LOAD DATA INFILE statements? On 12/4/05, Chenzhou Cui <[EMAIL PROTECTED]> wrote: Dear MySQL fans, I have a 260 GB huge file with 1045175762 rows. Two weeks ago, I wrote a Java program to read the huge plain text file into MySQL 4.1.12. 300 million of rows could be loaded in one day. Last Thursday, I updated the MySQL to 5.0.16 and then run the same program. Only 225 million of rows can be loaded in one day. In additional to the version difference, the MySQL 4.1.12 was compiled from SRPM packages from RedHat Updates, while the MySQL 5.0.16 is installed directly using the MySQL binary RPM packages for Redhat AS 4. Any information and suggestion are welcome. Regards, Chenzhou CUI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- ==== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 is 25% slower then 4.1
Dear MySQL fans, I have a 260 GB huge file with 1045175762 rows. Two weeks ago, I wrote a Java program to read the huge plain text file into MySQL 4.1.12. 300 million of rows could be loaded in one day. Last Thursday, I updated the MySQL to 5.0.16 and then run the same program. Only 225 million of rows can be loaded in one day. In additional to the version difference, the MySQL 4.1.12 was compiled from SRPM packages from RedHat Updates, while the MySQL 5.0.16 is installed directly using the MySQL binary RPM packages for Redhat AS 4. Any information and suggestion are welcome. Regards, Chenzhou CUI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 3.x to 4.1 for Chinese databases
No one can provide a perfect solution. It seems that this is really a difficult problem for the MySQL. YL wrote: I tried from 4.1 to 5.01, everything works fine without change the data files. I used Chinese in both gb and b5 (different columns in the same table) without any encoding setting at database level. So the problem seems in 3.* to 4.*. If mysqldump worded for you. My suggestion is to write a script to automat this data conversion. - Original Message - From: "Chenzhou Cui" <[EMAIL PROTECTED]> To: Cc: "YL" <[EMAIL PROTECTED]> Sent: Friday, September 23, 2005 6:54 AM Subject: Re: MySQL upgrade from 3.x to 4.1 for Chinese databases YL wrote: Did you tried just copy those 3.23 files into a newly installed 4.1 data folder? What's happen? Yes. I tried. The result is all Chinese words were turned into unrecognized characters. In my system, there are tens of databases, which include thousands of tables. A very large system, I feel. - Original Message - From: "Chenzhou Cui" <[EMAIL PROTECTED]> To: Sent: Friday, September 23, 2005 12:40 AM Subject: MySQL upgrade from 3.x to 4.1 for Chinese databases Dear all, On my server there are thousands of database files contenting Chinese records. The total data volumn is over 20GB. Is there an easy way to upgrade the whole system from MySQL 3.23.x to 4.1.x? "mysqldump" is NOT a good way for me. It will be a hard and time costing work. Is it possible to directly use the binary db files, "*.frm", "*.MYI", "*.MYD", and keep the Chinese support? This is a very big and important problem facing many Chinese users. cheers, Chenzhou -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 3.x to 4.1 for Chinese databases
YL wrote: Did you tried just copy those 3.23 files into a newly installed 4.1 data folder? What's happen? Yes. I tried. The result is all Chinese words were turned into unrecognized characters. In my system, there are tens of databases, which include thousands of tables. A very large system, I feel. - Original Message - From: "Chenzhou Cui" <[EMAIL PROTECTED]> To: Sent: Friday, September 23, 2005 12:40 AM Subject: MySQL upgrade from 3.x to 4.1 for Chinese databases Dear all, On my server there are thousands of database files contenting Chinese records. The total data volumn is over 20GB. Is there an easy way to upgrade the whole system from MySQL 3.23.x to 4.1.x? "mysqldump" is NOT a good way for me. It will be a hard and time costing work. Is it possible to directly use the binary db files, "*.frm", "*.MYI", "*.MYD", and keep the Chinese support? This is a very big and important problem facing many Chinese users. cheers, Chenzhou -- ==== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 -- ==== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL upgrade from 3.x to 4.1 for Chinese databases
Dear all, On my server there are thousands of database files contenting Chinese records. The total data volumn is over 20GB. Is there an easy way to upgrade the whole system from MySQL 3.23.x to 4.1.x? "mysqldump" is NOT a good way for me. It will be a hard and time costing work. Is it possible to directly use the binary db files, "*.frm", "*.MYI", "*.MYD", and keep the Chinese support? This is a very big and important problem facing many Chinese users. cheers, Chenzhou -- ======== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL upgrade from 3.x to 4.1 for Chinese databases
Dear all, On my server there are thousands of database files contenting Chinese records. The total data volumn is over 20GB. Is there an easy way to upgrade the whole system from MySQL 3.23.x to 4.1.x? "mysqldump" is NOT a good way for me. It will be a hard and time costing work. Is it possible to directly use the binary db files, "*.frm", "*.MYI", "*.MYD", and keep the Chinese support? This is a very big and important problem facing many Chinese users. cheers, Chenzhou -- ======== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max Mysql database size
I have a database, which is more than 120GB with about 500 million records. MySQL works well. Andrew stolarz wrote: Hello Everyone, Hopefully easy question, What is the Max size of a MySQL server database? -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character set in MySQL 4.1
Dear all, I am a Chinese and using Chinese in my MySQL databases. On my old server, the version of MySQL is 3.23.58. And my new MySQL is 4.1.7. On my old server, the MySQL works well with my Chinese contents. However, after I transfer tables to the new server using: mysqldump --opt database | mysql -h 'newserver' database The new server can't display Chinese contents correctly. What need I do? What's more, I can't find "gbk.xml" and "gb2312.xml" at "/usr/share/mysql/charsets", need I download them from somewhere? Thanks a lot, CB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.host: 3.23 to 4.0.2
Tim Johnson wrote: I am current working with mysql 3.23 on Red Hat 9.0 in the process of moving to slackware 10.0 on same machine, different partition. The version on the Slackwae partition is 4.0.2*. Question: Will I be able to simply mysqldump my mysql database on the RH 9.0 partion and then load the mysql DB on the Slackware partition safely? I feel it is no problem for normal dbs except "mysql" db. First, output your RH 9 dbs into ascii files using mysqldump and then load them into your Slackware DBMS. What issues are there, if any? Pointers to docs, URLs etc are appreciated. Thanks tim -- ==== Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it the time to upgrade my server?
Dear all, Following is a statistical result for my MySQL usage. Connections per hour are 16832, however 3767 attempts are failed (22.4%) and 94 attempts are aborted. Queries per second are 91. The most important applications of the server are httpd, php and mysql. Is my MySQL overloaded? Should I upgrade my hardware now? At present, the hardware system inclodes: 2.0GHz(Intel XEON)*2, 2GB DDR RAM, (Intel 1000Mbps NIC)*2, 73GB SCSI HD (RAID 1+0) and 36GB SCSI system disk. All kinds of comments are welcome, cheers, -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
