Reversing DESC|ASC
I'm currently running a query on a db that looks as follows: SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; This produces: ++-++ | field1 | field2 | field3 | ++-++ | 1 | Title 1 | 2004-09-08 | | 2 | Title 2 | 2004-10-23 | | 3 | Title 3 | 2004-11-11 | | 4 | Title 4 | 2004-11-20 | | 5 | Title 5 | 2004-11-24 | ++-++ My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: ++-++ | field1 | field2 | field3 | ++-++ | 5 | Title 5 | 2004-11-24 | | 4 | Title 4 | 2004-11-20 | | 3 | Title 3 | 2004-11-11 | | 2 | Title 2 | 2004-10-23 | | 1 | Title 1 | 2004-09-08 | ++-++ ...which puts the two that I need at the top, but not in the order I need them (I need 20th listed before the 24th). So, how do I reverse DESC sorting, to get the records in the order that I need? -- H | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / WebSmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reversing DESC|ASC
- Original Message - From: Ashley M. Kirchner [EMAIL PROTECTED] SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: So, how do I reverse DESC sorting, to get the records in the order that I need? You can cheat a bit and use a UNION of one SELECT: (SELECT field1,field2,field3 FROM table ORDER BY field3 ASC LIMIT 2) ORDER BY field3 DESC; Usually you would use (SELECT ...) UNION (SELECT...) ORDER BY... to sort the result of the combined queries. MySQL seems to accept the use of only one SELECT with an implied UNION. I'm not certain how future versions of MySQL will respond to this variation. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Socket Error While Starting MySQL
Hello Mukund, Remove the /etc/my.cnf file and start the mysqld daemon again. It will work Thanks, Mathan - Original Message - From: Mukund N Rathi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:31 AM Subject: Socket Error While Starting MySQL Hi All, I am facing some problems with installation of MySQL on Mac. I have installed MySQL on mac 10.3.5. but now whenever I try to run and start MySQL, it gives me following error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I went to the directory /tmp and tried locating a .sock file, but it was not there. I think I have deleted the mysql user and group using netinfo manager and recreated one with the same name. What can be the possible solution? ~..::Mukund::..~ Thou shalt not follow the null pointer for at its end madness and chaos lie. -- 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: [MySQL] Re: Reversing DESC|ASC
Jigal van Hemert wrote:
You can cheat a bit and use a UNION of one SELECT:
(SELECT field1,field2,field3 FROM table ORDER BY field3 ASC LIMIT 2) ORDER
BY field3 DESC;
This didn't work as expected. First, I need DESC sorting instead of
ASC to get what I need:
SELECT field1,field2,field3 FROM
table ORDER BY field3 DESC LIMIT 2;
...gave me:
++--+-+
| field1 | field2 | field3 |
++--+-+
| 1037 | Thanksgiving Day | 2004-11-25 00:00:00 |
| 1040 | Veteran's Day| 2004-11-11 00:00:00 |
++--+-+
Good! It's the two records I need. Now to do the full query as you
suggested:
(SELECT field1,field2,field3 FROM
table ORDER BY field3 DESC LIMIT 2)
ORDER BY field3 ASC;
...something went wrong. This now gives me:
++---+-+
| field1 | field2| field3 |
++---+-+
| 1037 | Thanksgiving Day | 2004-11-25 00:00:00 |
| 1040 | Veteran's Day | 2004-11-11 00:00:00 |
| 1044 | Halloween | 2004-10-31 00:00:00 |
| 1045 | Daylight Savings Ends | 2004-10-31 00:00:00 |
++---+-+
How'd it go from 2 to 4 records, and they're also not reversed?
Interesting to note: whether I do the final sorting DESC or ASC, it
makes absolutely no difference to the order or quantity of records returned.
Anyway, for the moment, I came up with a convoluted way of doing it:
SELECT field1,field2,field3 FROM
(SELECT field1,field2,field3 FROM table
ORDER BY field3 DESC LIMIT 2)
AS s1 ORDER BY field3 ASC;
This will effectively give me:
s1 = {5, 4} and {4, 5} as the final select (which is what I need.)
Not sure why that works, but not your example.
--
H | I haven't lost my mind; it's backed up on tape somewhere.
+
Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130
IT Director / SysAdmin / WebSmith . 800.441.3873 x130
Photo Craft Imaging . 3550 Arapahoe Ave. #6
http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Reversing DESC|ASC
From: Ashley M. Kirchner Jigal van Hemert wrote: You can cheat a bit and use a UNION of one SELECT: (SELECT field1,field2,field3 FROM table ORDER BY field3 ASC LIMIT 2) ORDER BY field3 DESC; This didn't work as expected. Very odd! These are my results on MySQL 4.0.21 using InnoDB tables (it was suitable data for this test, InnoDB was used for other reasons): SELECT `msg_id` , `from_accountid` , `sent` FROM `msg_content` ORDER BY `sent` DESC LIMIT 5 msg_id from_accountid sent 48469 30328 2004-11-24 03:14:49 48468 16788 2004-11-24 03:06:47 48467 22935 2004-11-24 02:49:49 48466 18115 2004-11-24 02:46:45 48465 27356 2004-11-24 02:29:23 (SELECT `msg_id` , `from_accountid` , `sent` FROM `msg_content` ORDER BY `sent` DESC LIMIT 5 ) ORDER BY `sent` ASC msg_id from_accountid sent 48465 27356 2004-11-24 02:29:23 48466 18115 2004-11-24 02:46:45 48467 22935 2004-11-24 02:49:49 48468 16788 2004-11-24 03:06:47 48469 30328 2004-11-24 03:14:49 This works exactly as expected Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
binary column type
What is 'binary' in column type stand for? is it describe how the data is stored (in biner value)? what do we want use it for? - encryption ? - space efficiency ? - fast index ? - or ? I've searched the mysql manual but didn't find any clue about it thanks for your help before. __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using mysql and mysqladmin
Hello, I have installed MySQL-server-4.0.22-0.i386.rpm as server at a linux computer and at the PC (windows 2000) as client was installed mysql-administrator-1.0.14-win.msi and mysql-query-browser-1.1.1-gamma-win.msi. Now I have two questions: Although the installtion at the server was successful there doesn`t exists the tool mysql and mysqladmin in the path /user/bin/. There are only tools like mysqld_safe, mysqltest, myisamchk, I've done searches for that executive files but it wasn`t detected. After installation the access to the mysql server was denied. After the following commands at the server: kill `cat /usr/lib/x.pid` mysqld_safe --skip-grant-tables the access was allowed (no passwords are necessary after that modification). Now I`ve changed at the PC with the administration tool the passwords for the users. After that change the access to the server was denied as before. Do you know the causes for that problems? Thanks in advance Gunter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary column type
Chenri J [EMAIL PROTECTED] wrote on 26/11/2004 10:52:51: What is 'binary' in column type stand for? is it describe how the data is stored (in biner value)? what do we want use it for? - encryption ? - space efficiency ? - fast index ? - or ? I've searched the mysql manual but didn't find any clue about it thanks for your help before. BINARY is usually used on character columns to tell MySQL to do an case-dependent rather than case-independent indexing and selection. By default, character columns use case-independent ordering. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary column type
What is 'binary' in column type stand for? is it describe how the data is stored (in biner value)? what do we want use it for? - encryption ? - space efficiency ? - fast index ? - or ? AFAIK it's used for determining the behaviour while comparing values (operators, sorting, etc.). Normally 'Garçon' (4th character is c-cedille) and 'garcon' are seen as identical strings if the language collation settings are set accordingly. Characters with accents are used in many languages depending on various circumstances (compound words, plural/singular forms, etc.); so can come in handy to take this into consideration while handling the data. Also upper/lower case is ignored by default (since the letter case is often depending upon the context in which the word is used). You can use BINARY in a column type to indicate that sorting, indexing and comparing should always be done without taking the above into consideration; comparing is thus done as if it were binary data: byte by byte. You can use BINARY in an operation to indicate that this case needs to be handled as binary data. I've used it in one table where I wanted each record to be case/accent-dependent unique. Now MySQL considered 'WiNTer' as different from 'winter', etc. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to retrieve constraints and links from MySQL tables?
So, I'm running a MySQL database (version: 4.0.21) for a personal project of mine, and I would like to learn how to retrieve constraints and links from the table after it's been created. The create statements read thusly: CREATE TABLE positions ( position_id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, position_name varchar(20) NOT NULL, position_salary float NOT NULL, PRIMARY KEY (position_id), UNIQUE position_id (position_id) ); CREATE TABLE employees ( employee_id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, employee_first varchar(20) NOT NULL, employee_last varchar(20) NOT NULL, employee_address varchar(255) NOT NULL, position_id tinyint(4) NOT NULL default 1, employee_start date, employee_temp bool default 0, FOREIGN KEY (position_id) references positions(position_id), PRIMARY KEY (employee_id), UNIQUE employee_id (employee_id) ); When I run show columns from employees, there is no indication that the position_id field in employees is linked to that of positions. This is the resulting table: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | employee_id | tinyint(4) | | PRI | NULL | auto_increment | | employee_first | varchar(20) | | | || | employee_last| varchar(20) | | | || | employee_address | varchar(255) | | | || | position_id | tinyint(4) | | | 1 || | employee_start | date | YES | | NULL || | employee_temp| tinyint(1) | YES | | 0 || +--+--+--+-+-++ Is there a command or something that will display what constraints (or links) exist in a given table? Varakorn Ungvichian __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to retrieve constraints and links from MySQL tables?
So, I'm running a MySQL database (version: 4.0.21) for a personal project of mine, and I would like to learn how to retrieve constraints and links from the table after it's been created. The create statements read thusly: When I run show columns from employees, there is no indication that the position_id field in employees is linked to that of positions. This is the resulting table: The only way to do this is via SHOW CREATE TABLE name With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to retrieve constraints and links from MySQL tables?
You can use: show create table employee; or show table status like 'employee'; in the column comment you have the information you are looking for. Marc. -Message d'origine- De : Varakorn Ungvichian [mailto:[EMAIL PROTECTED] Envoyé : vendredi 26 novembre 2004 09:32 À : [EMAIL PROTECTED] Objet : How to retrieve constraints and links from MySQL tables? So, I'm running a MySQL database (version: 4.0.21) for a personal project of mine, and I would like to learn how to retrieve constraints and links from the table after it's been created. The create statements read thusly: CREATE TABLE positions ( position_id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, position_name varchar(20) NOT NULL, position_salary float NOT NULL, PRIMARY KEY (position_id), UNIQUE position_id (position_id) ); CREATE TABLE employees ( employee_id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, employee_first varchar(20) NOT NULL, employee_last varchar(20) NOT NULL, employee_address varchar(255) NOT NULL, position_id tinyint(4) NOT NULL default 1, employee_start date, employee_temp bool default 0, FOREIGN KEY (position_id) references positions(position_id), PRIMARY KEY (employee_id), UNIQUE employee_id (employee_id) ); When I run show columns from employees, there is no indication that the position_id field in employees is linked to that of positions. This is the resulting table: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | employee_id | tinyint(4) | | PRI | NULL | auto_increment | | employee_first | varchar(20) | | | || | employee_last| varchar(20) | | | || | employee_address | varchar(255) | | | || | position_id | tinyint(4) | | | 1 || | employee_start | date | YES | | NULL || | employee_temp| tinyint(1) | YES | | 0 || +--+--+--+-+-++ Is there a command or something that will display what constraints (or links) exist in a given table? Varakorn Ungvichian __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- 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]
Which MySQL software is useful for the clients?
Hello, At a linux server with Red Hat I have installed MySQL-server-4.0.22-0.i386.rpm. On the client (Win 2000) I have installed the MySQL Administrator mysql-administrator-1.0.14-win.msi and the MySQL Query Browser mysql-query-browser-1.1.1-gamma-win.msi. All software is downloaded from http://dev.mysql.com/. This software does not maintain a shell like the tool mysql and mysqladmin. I want to use that tools for testing because after a change of passwords with MySQL Adminstration the access to the server is denied and I have to use the following commands for getting full access again: kill `cat /usr/lib/x.pid` mysqld_safe --skip-grant-tables Has anyone an answer to that problem? Gunter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I want cluster, can I use MySQL?
Hello. MySQL cluster can be configured with a range of fail-over and load-balancing options. And it is really ready enough for commercial usage. Complete information about licensing you can request at [EMAIL PROTECTED] Koon Yue Lam [EMAIL PROTECTED] wrote: Hi list ! I want a high availability DB whcih means I need failover andload balancing. Can I use MySQL cluster to achieve this? becasue MySQL cluster is only in gramma, I wonder if I can use it in a commerical environment. Also if my website is commerical, do I need to have commerical license of MySQL? How about MaxDB? Regards -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.3 Java/UTF8 support question
Hello.
What output produced
show variables like '%char%';
show variables like '%colla%';
You should also set utf8 as server character set.
See:
http://dev.mysql.com/doc/mysql/en/Charset-defaults.html
Ramesh Vadlapatla [EMAIL PROTECTED] wrote:
Hi,
In my.cnf, I have:
[mysql]
default-character-set=utf8
When I connect via mysql client and try to run a query which has a
CONCAT string function, it works fine and I get the proper output.
Example: SELECT CONCAT ('a', ' - ', 'b') = a-b
Now, I am trying to do make this work via Java and:
I use this JDBC Driver: mysql-connector-java-3.0.10-stable-bin.jar
Conn. String:
jdbc:mysql://localhost:1/db1?useUnicode=truecharacterEncoding=utf8
I get the same error as I would have without the above
default-character-set=utf8, i.e:
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE),
(utf8_general_ci,IMPLICIT) for operation 'concat'
How do I fix this, so that even the Java Client understands to use the
utf8 character set and I don't have to put the _utf8 in my sql
statement?
Appreciate your help.
thanks,
Ramesh
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement problem
I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement problem
Stuart Felenstein [EMAIL PROTECTED] wrote on 26/11/2004 13:54:31: I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Insert statement problem
--- [EMAIL PROTECTED] wrote: I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec Thank you Alex. It works. ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Preventing Publishing to Web?
When I was first learning to work with MySQL, I discovered that I couldn't publish my databases to the Internet if they had foreign keys. So I stripped out all the foreign keys and got them online. I'm not trying to publish a table I haven't worked with in a while, but I just get error messages. The error message suggests it's a foreign key problem; at the very least, it suggests the table has a foreign key. But I can't remember how to even tell if a foreign key is present. When I look at my table in PPHPmyadmin, I just see one key - a primary key - under Keyname. When I click on SCode - the field that supposedly has the foreign key, I see no reference to a foreign key. I pasted the error message below. Can someone tell me what I'm missing? Thanks. SQL-query: # phpMyAdmin SQL Dump # version 2.5.3 # http://www.phpmyadmin.net # # Host: localhost # Generation Time: Aug 17, 2004 at 08:52 AM # Server version: 4.0.16 # PHP Version: 4.3.4 # # Database : `world` # # # # Table structure for table `counties` # CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB PACK_KEYS =0 AUTO_INCREMENT =3143 MySQL said: Documentation #1005 - Can't create table './world_gypsy/counties.frm' (errno: 150) __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which MySQL software is useful for the clients?
Gunter Götz wrote: At a linux server with Red Hat I have installed MySQL-server-4.0.22-0.i386.rpm. This software does not maintain a shell like the tool mysql and mysqladmin. I want to use that tools So download and install the mysql client -- why is this challenging? Note: if you installed from the tar file distribution rather than using RPMs, you'd have everything you need to start with... FWIW, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Preventing Publishing to Web?
hi, in the script you have CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE and this code it's a definition of the foreign key. run the script without this part of the code. Citando David Blomstrom [EMAIL PROTECTED]: When I was first learning to work with MySQL, I discovered that I couldn't publish my databases to the Internet if they had foreign keys. So I stripped out all the foreign keys and got them online. I'm not trying to publish a table I haven't worked with in a while, but I just get error messages. The error message suggests it's a foreign key problem; at the very least, it suggests the table has a foreign key. But I can't remember how to even tell if a foreign key is present. When I look at my table in PPHPmyadmin, I just see one key - a primary key - under Keyname. When I click on SCode - the field that supposedly has the foreign key, I see no reference to a foreign key. I pasted the error message below. Can someone tell me what I'm missing? Thanks. SQL-query: # phpMyAdmin SQL Dump # version 2.5.3 # http://www.phpmyadmin.net # # Host: localhost # Generation Time: Aug 17, 2004 at 08:52 AM # Server version: 4.0.16 # PHP Version: 4.3.4 # # Database : `world` # # # # Table structure for table `counties` # CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB PACK_KEYS =0 AUTO_INCREMENT =3143 MySQL said: Documentation #1005 - Can't create table './world_gypsy/counties.frm' (errno: 150) __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Ganha Câmaras de Filmar, Televisões e outros fantásticos prémios Samsung com o SAPO! Clica em http://dc.sapo.pt/sfc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Corruption with error logs now.
Well look what i did: I changed innodb_log_file_size as you asked me for 5M and then i tryed to start mysqld again then i it doesnt run. And the output at err.log was this one: I will remember that ibdata1 is my old corrputed data. And iblogile1 is a new iblog because the iblogfile corresponding to the old ibdata1 was deleted by someone here. -begin result InnoDB: Error: auto-extending data file c:\MySQL\server\data\ibdata1 is of a different size InnoDB: 305792 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 384000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041126 13:58:12 [ERROR] Can't init databases 041126 13:58:12 [ERROR] Aborting 041126 13:58:46 [Note] mysqld: Shutdown complete end result So i saw that this problem was in my.ini(at c:\windows) and i changed to this:innodb_data_file_path = ibdata1:3000M:autoextend then i tryied to run again the daemon and i get the following results -result-- 041126 13:59:13 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041126 13:59:13 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 041126 13:59:13 InnoDB: Error: page 7 log sequence number 1 2489338706 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 2 log sequence number 1 2489343508 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 11 log sequence number 1 2489345137 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 5 log sequence number 1 2476307008 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 6 log sequence number 1 2759486042 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 45 log sequence number 1 2489342324 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 1943 log sequence number 1 40742737 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 98320 log sequence number 1 3749111878 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 98305 log sequence number 1 2489339022 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 3587 log sequence number 0 486631928 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 3619 log sequence number 1 83786313 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 49198 log sequence number 1 3818468379 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 49153 log sequence number 1 2476605300 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: 2 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2 row operations to undo InnoDB: Trx id counter is 0 44866816 041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126 13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 69489132 bytes. Operating system errno: 8 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On
Re: Which MySQL software is useful for the clients?
At 14:46 +0100 11/26/04, Gunter Götz wrote: Hello, At a linux server with Red Hat I have installed MySQL-server-4.0.22-0.i386.rpm. That's the server RPM. There is also a client RPM, which is what you need to install for the mysql and mysqladmin programs. See: http://dev.mysql.com/doc/mysql/en/Linux-RPM.html On the client (Win 2000) I have installed the MySQL Administrator mysql-administrator-1.0.14-win.msi and the MySQL Query Browser mysql-query-browser-1.1.1-gamma-win.msi. All software is downloaded from http://dev.mysql.com/. This software does not maintain a shell like the tool mysql and mysqladmin. I want to use that tools for testing because after a change of passwords with MySQL Adminstration the access to the server is denied and I have to use the following commands for getting full access again: kill `cat /usr/lib/x.pid` mysqld_safe --skip-grant-tables Has anyone an answer to that problem? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
latin1/utf-8 problem
I have a strange, irritating problem that I think is to do with MySQL. I have a table on a live (shared hosting) system that, when I use it on my local system, seems to wind up in utf-8 rather than latin1. Here's the set-up: Live system - MySQL 3.2.3.56, PHP 4.1.2, Apache 1.3.27. MySQL is configured to use latin1, PHP to no default charset. Local system - MySQL 4.0.18-32, PHP 4.3.4, Apache2 (all from SuSE 9.1 distro). MySQL is configured to use latin1, PHP to no default charset. I have a table containing text with French accented characters. On the live system, these display correctly when viewed with a browser set to iso-8859-1 encoding (which is what the page specifies). On the local system, the page has to be viewed as utf-8. Huh? This is the same data. I dumped the table from the live system (via phpMyAdmin - also set to use latin1) to my local disk. I viewed that with Kwrite which showed me that the text was, indeed, latin1 in that file. I uploaded the table to the local system. In MySQLcc (set to latin1) the text reads correctly. Running myisamchk on the table tells me it's latin1. And yet, if I mysqldump the table, the resulting file appears to be utf-8! And data pulled from the table by PHP also appears to be utf-8. Help! @+ Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Corruption with error logs now.
Carlos, ok, this makes sense. InnoDB: 2 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2 row operations to undo InnoDB: Trx id counter is 0 44866816 041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126 13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of InnoDB: memory with malloc! The tablespace is corrupt, therefore the rollback crashes. Try to set: innodb_force_recovery=4 in your my.cnf or my.ini. Then, if you have the .frm files for the tables (I hope your customer did not delete them, too), there is a good chance that you can dump all the rows in your tables, or almost all. http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html If you do not have the .frm files, you can try creating the tables as empty in another MySQL installation, and copy the .frm files from there to this corrupt database. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Alkuperäinen viesti - Lähettäjä: Carlos Augusto [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Friday, November 26, 2004 6:11 PM Aihe: Re: Innodb Corruption with error logs now. Well look what i did: I changed innodb_log_file_size as you asked me for 5M and then i tryed to start mysqld again then i it doesnt run. And the output at err.log was this one: I will remember that ibdata1 is my old corrputed data. And iblogile1 is a new iblog because the iblogfile corresponding to the old ibdata1 was deleted by someone here. -begin result InnoDB: Error: auto-extending data file c:\MySQL\server\data\ibdata1 is of a different size InnoDB: 305792 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 384000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041126 13:58:12 [ERROR] Can't init databases 041126 13:58:12 [ERROR] Aborting 041126 13:58:46 [Note] mysqld: Shutdown complete end result So i saw that this problem was in my.ini(at c:\windows) and i changed to this:innodb_data_file_path = ibdata1:3000M:autoextend then i tryied to run again the daemon and i get the following results -result-- 041126 13:59:13 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041126 13:59:13 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 041126 13:59:13 InnoDB: Error: page 7 log sequence number 1 2489338706 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 2 log sequence number 1 2489343508 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 11 log sequence number 1 2489345137 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 5 log sequence number 1 2476307008 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 6 log sequence number 1 2759486042 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 45 log sequence number 1 2489342324 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 1943 log sequence number 1 40742737 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 98320 log sequence number 1 3749111878 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 98305 log sequence number 1 2489339022 InnoDB: is in the future! Current system log
Re: Foreign Key Preventing Publishing to Web?
--- [EMAIL PROTECTED] wrote: hi, in the script you have CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE and this code it's a definition of the foreign key. run the script without this part of the code. * * * * * * * * * * Thank you. So I can just create a table online by pasting the code into a query window, right? I've never done this before, and I'm not sure what the first step is. Should I choose Query, or the small pop-up Query Window? I've been playing with both. Sometimes my experiment goes nowhere, and other times I get an error message related to the last line. Below is a copy of what I pasted in. Thanks. CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , ) TYPE = InnoDB PACK_KEYS =0 AUTO_INCREMENT =3143 __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and PHP5 with old client
Hi, I have installed MySQL 4.1.7-nt + Apache 2 + PHP 5.0.2 + PHPMyAdmin 2.6.0-pl1 on my Windows XP Pro SP2. I am trying to solve the problem with MySQL PHP Old Clients using OLD_PASSWORD instead of just PASSWORD. How can I set on my.ini to start MySQL using as a default the OLD_PASSWORD? I tried to insert: [mysqld] --old-passwords and [mysqld] old-passwords but both didn't work. Does anybody know how to do this? Is any other better way to make PHP5 work with the new password format without recompiling (It is easy on Linux/Unix but not on Windows XP). Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to retrieve constraints and links from MySQL tables?
Hello, You can use: show create table employee; or show table status like 'employee'; in the column comment you have the information you are looking for. I don't think comment works properly - it's too small if you have multiple foreign key constraints. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and PHP5 with old client
At 11:21 -0500 11/26/04, Andre Matos wrote:
Hi,
I have installed MySQL 4.1.7-nt + Apache 2 + PHP 5.0.2 + PHPMyAdmin
2.6.0-pl1 on my Windows XP Pro SP2. I am trying to solve the problem with
MySQL PHP Old Clients using OLD_PASSWORD instead of just PASSWORD.
How can I set on my.ini to start MySQL using as a default the
OLD_PASSWORD?
I tried to insert:
[mysqld]
--old-passwords
and
[mysqld]
old-passwords
but both didn't work.
Don't use leading dashes in option files.
But old-passwords won't work, either, for accounts that already
have had their passwords changed to the new (41-byte) format.
You'll need to reset their passwords back to the old format
with SET PASSWORD = OLD_PASSWORD('the password goes here')
Does anybody know how to do this? Is any other better way to make PHP5 work
with the new password format without recompiling (It is easy on Linux/Unix
but not on Windows XP).
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary column type
At 2:52 -0800 11/26/04, Chenri J wrote: What is 'binary' in column type stand for? is it describe how the data is stored (in biner value)? what do we want use it for? - encryption ? - space efficiency ? - fast index ? - or ? I've searched the mysql manual but didn't find any clue about it thanks for your help before. http://dev.mysql.com/doc/mysql/en/BINARY_VARBINARY.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Preventing Publishing to Web?
Please disregard my last post. I found another workaround - I saved the database table on my computer in an unzipped format, and I was able to export it to my website. It looks good! http://www.geoworld.org/north_america/usa/az/counties/index.php Thanks. __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Take a normal Table and Make it a Heap table
I have to pull in 200 search rows and store them temporarily in a Table called xmllinks This is so I can track the click on the one link of the 200 I bring down. Nothing is permanently stored in this table This is just a normal table in a db right now but during peak traffic times it bogs down the MySql. What do I have to do to move this one table into a heap of ram. I didn't code this software so please give me a little detail as to whether this is done at the software side or the MySql side. Of what I need to edit to stick this up into ram and verify that its there. I do want to verify its actually there somehow. ThanksDonny LairsonPresidenthttp://www.gunmuse.com469 228 2183
Lost connection to MySQL server during query after 4.1 upgrade
Hello All! I have had mySQL 3.2 running on my server for about 2 years now without incident. Recently upgraded to 4.1, and suddenly I'm getting the following message from queries: Lost connection to MySQL server during query This is in response to a PHP script issuing a query to the mySQL server. Nothing by mySQL has changed on this server - all other pieces of software have been rebuilt using the same version as they were before the mySQL upgrade to take advantage of the new mySQL libraries. After I get five or six of these errors in a row, I get the following in my /var/db/mysql/my.host.name.err file: 041125 01:13:39 mysqld restarted 041125 1:13:40 InnoDB: Database was not shut down normally! 041125 1:13:40 InnoDB: Starting log scan based on checkpoint at 041125 1:13:40 InnoDB: Starting an apply batch of log records to the database... 041125 1:13:42 InnoDB: Starting an apply batch of log records to the database... 041125 1:13:43 InnoDB: Flushing modified pages from the buffer pool... 041125 1:13:43 InnoDB: Started; log sequence number 7 2215480040 The mySQL server appears to restart itself and resume normal operations. This is, obviously, not normal operation. Is there something with mySQL 4.1 on a FreeBSD box that causes this? Is this a known problem, of have I discovered something new? :) Thanks in advance for anything you can do to help! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ smime.p7s Description: S/MIME cryptographic signature
Re: MySQL 4.1.3 Java/UTF8 support question
Hi Gleb,
The problem was with the JDBC Connect string, earlier it was;
jdbc:mysql://localhost:1/db1?useUnicode=truecharacterEncoding=utf8
I changed that to:
jdbc:mysql://localhost:1/db1?useUnicode=truecharacterEncoding=UTF-8
and it works now.
thanks,
Ramesh
On Fri, 26 Nov 2004 13:53:43 +0200, Gleb Paharenko
[EMAIL PROTECTED] wrote:
Hello.
What output produced
show variables like '%char%';
show variables like '%colla%';
You should also set utf8 as server character set.
See:
http://dev.mysql.com/doc/mysql/en/Charset-defaults.html
Ramesh Vadlapatla [EMAIL PROTECTED] wrote:
Hi,
In my.cnf, I have:
[mysql]
default-character-set=utf8
When I connect via mysql client and try to run a query which has a
CONCAT string function, it works fine and I get the proper output.
Example: SELECT CONCAT ('a', ' - ', 'b') = a-b
Now, I am trying to do make this work via Java and:
I use this JDBC Driver: mysql-connector-java-3.0.10-stable-bin.jar
Conn. String:
jdbc:mysql://localhost:1/db1?useUnicode=truecharacterEncoding=utf8
I get the same error as I would have without the above
default-character-set=utf8, i.e:
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE),
(utf8_general_ci,IMPLICIT) for operation 'concat'
How do I fix this, so that even the Java Client understands to use the
utf8 character set and I don't have to put the _utf8 in my sql
statement?
Appreciate your help.
thanks,
Ramesh
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
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: Innodb Corruption with error logs now.
Well Heikki i don´t have those .frm and i don´t know the 'structure' of the corrputed databse. I make a report to my superior telling him that it´s not possible to recover the database without this information. I ´ll ask you just one more thing. Without .frm files or 'informations about the table'(like fields, primary keys) is possible to do anything more about the corrupted database? Thank you very much for helping me. Saved my life! lol [´z] Carlos On Fri, 26 Nov 2004 18:21:51 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Carlos, ok, this makes sense. InnoDB: 2 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2 row operations to undo InnoDB: Trx id counter is 0 44866816 041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126 13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of InnoDB: memory with malloc! The tablespace is corrupt, therefore the rollback crashes. Try to set: innodb_force_recovery=4 in your my.cnf or my.ini. Then, if you have the .frm files for the tables (I hope your customer did not delete them, too), there is a good chance that you can dump all the rows in your tables, or almost all. http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html If you do not have the .frm files, you can try creating the tables as empty in another MySQL installation, and copy the .frm files from there to this corrupt database. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Alkuperäinen viesti - Lähettäjä: Carlos Augusto [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Friday, November 26, 2004 6:11 PM Aihe: Re: Innodb Corruption with error logs now. Well look what i did: I changed innodb_log_file_size as you asked me for 5M and then i tryed to start mysqld again then i it doesnt run. And the output at err.log was this one: I will remember that ibdata1 is my old corrputed data. And iblogile1 is a new iblog because the iblogfile corresponding to the old ibdata1 was deleted by someone here. -begin result InnoDB: Error: auto-extending data file c:\MySQL\server\data\ibdata1 is of a different size InnoDB: 305792 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 384000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041126 13:58:12 [ERROR] Can't init databases 041126 13:58:12 [ERROR] Aborting 041126 13:58:46 [Note] mysqld: Shutdown complete end result So i saw that this problem was in my.ini(at c:\windows) and i changed to this:innodb_data_file_path = ibdata1:3000M:autoextend then i tryied to run again the daemon and i get the following results -result-- 041126 13:59:13 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041126 13:59:13 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 041126 13:59:13 InnoDB: Error: page 7 log sequence number 1 2489338706 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 2 log sequence number 1 2489343508 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 11 log sequence number 1 2489345137 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 5 log sequence number 1 2476307008 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. 041126 13:59:13 InnoDB: Error: page 6 log sequence number 1 2759486042 InnoDB: is in the future! Current system log sequence number 0 43634.
RE: Take a normal Table and Make it a Heap table
I agree and we are rewriting this application ourselves to accomidate these types of issues of making it faster faster faster. But I would like to patch what I have at the same time. Call me greedy Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: sol beach [mailto:[EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:25 AM To: [EMAIL PROTECTED] Subject: Re: Take a normal Table and Make it a Heap table When your only tool is a hammer, then all problems are views as nails. A shovel is a great tool for creating a hole in the ground, but only when the right end is contacts the ground. You seems to be using the wrong end of the computer. With computers, you can have it good, fast, or cheap. Pick any two ( pay the price in the third). I wish you luck in re-inventing the wheel rolling your own custom SCABALBLE application. P.S. Scalability needs to be designed into the archituecture from the start. It rarely can be bolted together after the bottlenecks are encountered, because bottlenecks result from inappropriate original design decisions. On Fri, 26 Nov 2004 11:12:38 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have to pull in 200 search rows and store them temporarily in a Table called xmllinks This is so I can track the click on the one link of the 200 I bring down. Nothing is permanently stored in this table This is just a normal table in a db right now but during peak traffic times it bogs down the MySql. What do I have to do to move this one table into a heap of ram. I didn't code this software so please give me a little detail as to whether this is done at the software side or the MySql side. Of what I need to edit to stick this up into ram and verify that its there. I do want to verify its actually there somehow. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Reversing DESC|ASC
Jigal van Hemert wrote: These are my results on MySQL 4.0.21 using InnoDB tables (it was suitable data for this test, InnoDB was used for other reasons): rpm -qa | grep -i mysql MySQL-server-4.1.7-0 MySQL-devel-4.1.7-0 MySQL-client-4.1.7-0 MySQL-shared-compat-4.1.7-0 MySQL-Max-4.1.7-0 And it's a MyISAM table. This works exactly as expected No matter how I performed your query, it always came back with more records than expected, almost like it's ignoring the LIMIT value. With my query it works fine. I wonder if it's a feature of the newer version. -- H | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / WebSmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Take a normal Table and Make it a Heap table
I ran this and got the following error. Why doesn't heap support autoincrement? Or does it and I need to do something different. CREATE TABLE `xmllinks2` ( `rowID` int(11) NOT NULL auto_increment, `affiliateID` int(11) NOT NULL default '0', `pluginName` varchar(255) NOT NULL default '', `linkID` int(11) NOT NULL default '0', `linkURL` varchar(255) NOT NULL, `bid` decimal(10,4) NOT NULL default '0.', `uniqueData` varchar(255) NOT NULL, `searchDate` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`rowID`) ) TYPE=HEAP AUTO_INCREMENT=1425725 ; #1164 - The used table type doesn't support AUTO_INCREMENT columns Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:59 AM To: sol beach Cc: Mysql Subject: RE: Take a normal Table and Make it a Heap table I agree and we are rewriting this application ourselves to accomidate these types of issues of making it faster faster faster. But I would like to patch what I have at the same time. Call me greedy Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: sol beach [mailto:[EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:25 AM To: [EMAIL PROTECTED] Subject: Re: Take a normal Table and Make it a Heap table When your only tool is a hammer, then all problems are views as nails. A shovel is a great tool for creating a hole in the ground, but only when the right end is contacts the ground. You seems to be using the wrong end of the computer. With computers, you can have it good, fast, or cheap. Pick any two ( pay the price in the third). I wish you luck in re-inventing the wheel rolling your own custom SCABALBLE application. P.S. Scalability needs to be designed into the archituecture from the start. It rarely can be bolted together after the bottlenecks are encountered, because bottlenecks result from inappropriate original design decisions. On Fri, 26 Nov 2004 11:12:38 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have to pull in 200 search rows and store them temporarily in a Table called xmllinks This is so I can track the click on the one link of the 200 I bring down. Nothing is permanently stored in this table This is just a normal table in a db right now but during peak traffic times it bogs down the MySql. What do I have to do to move this one table into a heap of ram. I didn't code this software so please give me a little detail as to whether this is done at the software side or the MySql side. Of what I need to edit to stick this up into ram and verify that its there. I do want to verify its actually there somehow. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -- 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: Take a normal Table and Make it a Heap table
Can I put a MyISAM table into Ram permanently? Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:59 AM To: sol beach Cc: Mysql Subject: RE: Take a normal Table and Make it a Heap table I agree and we are rewriting this application ourselves to accomidate these types of issues of making it faster faster faster. But I would like to patch what I have at the same time. Call me greedy Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: sol beach [mailto:[EMAIL PROTECTED] Sent: Friday, November 26, 2004 11:25 AM To: [EMAIL PROTECTED] Subject: Re: Take a normal Table and Make it a Heap table When your only tool is a hammer, then all problems are views as nails. A shovel is a great tool for creating a hole in the ground, but only when the right end is contacts the ground. You seems to be using the wrong end of the computer. With computers, you can have it good, fast, or cheap. Pick any two ( pay the price in the third). I wish you luck in re-inventing the wheel rolling your own custom SCABALBLE application. P.S. Scalability needs to be designed into the archituecture from the start. It rarely can be bolted together after the bottlenecks are encountered, because bottlenecks result from inappropriate original design decisions. On Fri, 26 Nov 2004 11:12:38 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have to pull in 200 search rows and store them temporarily in a Table called xmllinks This is so I can track the click on the one link of the 200 I bring down. Nothing is permanently stored in this table This is just a normal table in a db right now but during peak traffic times it bogs down the MySql. What do I have to do to move this one table into a heap of ram. I didn't code this software so please give me a little detail as to whether this is done at the software side or the MySql side. Of what I need to edit to stick this up into ram and verify that its there. I do want to verify its actually there somehow. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -- 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]
Newbie: How to deal with multiple languages
I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date query and date removal
This will seem an easy question, but I cannot find a sample anywhere: I have a database of bands appearing at a club. I want to update the list sorted by date and have the results only show current date and beyond (i.e. - remove band that played last night from results page). This query is driving me nuts and only thing left to finish a site. Thanks in advance, darrell Sent via the WebMail system at polariscomputers.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Corruption with error logs now.
Carlos, - Original Message - From: Carlos Augusto [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, November 26, 2004 8:46 PM Subject: Re: Innodb Corruption with error logs now. Well Heikki i don=B4t have those .frm and i don=B4t know the 'structure' of the corrputed databse. I make a report to my superior telling him that it=B4s not possible to recover the database without this information. I =B4ll ask you just one more thing. Without .frm files or 'informations about the table'(like fields, primary keys) is possible to do anything more about the corrupted database? you can print the table structure in your ibdata file in InnoDB's own internal data dictionary with the innodb_table_monitor. The output is not too clear, it is no beautiful CREATE TABLE statements. But with some work you can construct the table structure from the output. Then you can create the .frm files manually. See http://www.innodb.com/ibmanold.php#InnoDB.Monitor about how to start and stop InnoDB monitors. Thank you very much for helping me. Saved my life! lol [=B4z] Carlos Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ On Fri, 26 Nov 2004 18:21:51 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Carlos, =20 ok, this makes sense. =20 =20 =20 InnoDB: 2 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2 row operations to undo InnoDB: Trx id counter is 0 44866816 041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126 13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of InnoDB: memory with malloc! =20 The tablespace is corrupt, therefore the rollback crashes. Try to set: =20 innodb_force_recovery=3D4 =20 in your my.cnf or my.ini. =20 Then, if you have the .frm files for the tables (I hope your customer did not delete them, too), there is a good chance that you can dump all the r= ows in your tables, or almost all. =20 http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html =20 If you do not have the .frm files, you can try creating the tables as emp= ty in another MySQL installation, and copy the .frm files from there to this corrupt database. =20 Best regards, =20 Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyIS= AM tables http://www.innodb.com/order.php =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Carlos Augusto [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] L=E4hetetty: Friday, November 26, 2004 6:11 PM Aihe: Re: Innodb Corruption with error logs now. =20 =20 =20 =20 Well look what i did: I changed innodb_log_file_size as you asked me for 5M and then i tryed to start mysqld again then i it doesnt run. And the output at err.log was this one: I will remember that ibdata1 is my old corrputed data. And iblogile1 is a new iblog because the iblogfile corresponding to the old ibdata1 was deleted by someone here. -begin result InnoDB: Error: auto-extending data file c:\MySQL\server\data\ibdata1 is of a different size InnoDB: 305792 pages (rounded down to MB) than specified in the .cnf fi= le: InnoDB: initial 384000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do = not InnoDB: remove old data files which contain your precious data! 041126 13:58:12 [ERROR] Can't init databases 041126 13:58:12 [ERROR] Aborting 041126 13:58:46 [Note] mysqld: Shutdown complete end result So i saw that this problem was in my.ini(at c:\windows) and i changed to this:innodb_data_file_path =3D ibdata1:3000M:autoextend then i tryied to run again the daemon and i get the following results -result-- 041126 13:59:13 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041126 13:59:13 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0
Re: Date query and date removal
- Original Message - From: darrell troth [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 3:29 PM Subject: Date query and date removal This will seem an easy question, but I cannot find a sample anywhere: I have a database of bands appearing at a club. I want to update the list sorted by date and have the results only show current date and beyond (i.e. - remove band that played last night from results page). This query is driving me nuts and only thing left to finish a site. It should be a pretty straight-forward query: just compare the performance date to the current date in your WHERE clause. Something like this: select band from performances where performance_date = current_date(); Have you tried that? If so, what error did you get? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query and date removal
- Original Message -
From: darrell troth [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, November 26, 2004 3:29 PM
Subject: Date query and date removal
This will seem an easy question, but I cannot find a sample anywhere:
I have a database of bands appearing at a club. I want to update the list
sorted by date and have the results only show current date and beyond
(i.e. - remove band that played last night from results page). This query is
driving me nuts and only thing left to finish a site.
I just realized that I misread your initial post; you don't just want to
*see* future performances, you want to delete past ones.
Here is a quick script I knocked together to demonstrate the solution. When
I run the first SELECT, I get all of the performances listed in
chronological order. Then, the delete gets rid of all performances that have
already taken place. When I run the second SELECT, I get only the
performances that occur today or in the future, specifically Pat Metheny and
Yes, listed in chronological order.
---
use tmp;
drop table if exists performances;
create table if not exists performances
(performer char(30) not null,
performance_date date not null,
primary key(performer, performance_date));
insert into performances values ('Pink Floyd', '2004-11-04');
insert into performances values ('Pat Metheny', '2004-11-26');
insert into performances values ('Yes', '2004-11-30');
select * from performances
order by performance_date;
delete from performances
where performance_date current_date();
select *
from performances
order by performance_date;
---
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change field names in a query
what is the proper mysql syntax to change field names after the query is done if I have a table with: id englishText spanishText picture And I query the table with: select id, spanishText from myTable limit 30 how do I change the 'spanishText' field name to say 'language' ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change field names in a query
- Original Message - From: Graham Anderson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 6:28 PM Subject: change field names in a query what is the proper mysql syntax to change field names after the query is done if I have a table with: id englishText spanishText picture And I query the table with: select id, spanishText from myTable limit 30 how do I change the 'spanishText' field name to say 'language' ? many thanks g If I understand your question correctly, you need to use an 'as' expression. For example: select id, spanishText as 'language' from myTable limit 30; This example tells MySQL to display the contents of the columns named 'id' and 'spanishText'. The column headings in the result set will be 'id' for the 'id' column (if you don't supply an 'as' expression, the original column name is used most of the time) and 'language' for the 'spanishText' column since that is the column heading you preferred. There are some limitations on the aliases that you supply via the 'as' expression but you'd have to look them up in the MySQL manual to be sure what they are; if the MySQL rules are like rules for DB2, there is a length limit for aliases and aliases that contain embedded blanks need to be in quotes. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change field names in a query
many thanks :) that was exactly what I needed g On Nov 26, 2004, at 4:27 PM, Rhino wrote: - Original Message - From: Graham Anderson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 6:28 PM Subject: change field names in a query what is the proper mysql syntax to change field names after the query is done if I have a table with: id englishText spanishText picture And I query the table with: select id, spanishText from myTable limit 30 how do I change the 'spanishText' field name to say 'language' ? many thanks g If I understand your question correctly, you need to use an 'as' expression. For example: select id, spanishText as 'language' from myTable limit 30; This example tells MySQL to display the contents of the columns named 'id' and 'spanishText'. The column headings in the result set will be 'id' for the 'id' column (if you don't supply an 'as' expression, the original column name is used most of the time) and 'language' for the 'spanishText' column since that is the column heading you preferred. There are some limitations on the aliases that you supply via the 'as' expression but you'd have to look them up in the MySQL manual to be sure what they are; if the MySQL rules are like rules for DB2, there is a length limit for aliases and aliases that contain embedded blanks need to be in quotes. Rhino graham anderson 310.402.3980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy/Paste text from Ms Word into mysql
I am copy/pasting text from a microsoft word doc into phpmyadmin are there any special tricks to avoid strange characters...most of my text is in Spanish The only thing that seems to work is converting the text to ASCII which ditches all the Spanish formatting :( is there some process to make this a painless process ? g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
