Changing the sort ordering of an existing ENUM field
hi, I have a table with a 'priority' field defined as an ENUM: priority enum('SOMEDAY','NOW','SOON') NOT NULL I would like to change it to the following so the sort order is more intuitive/desirable. priority enum('NOW','SOON', 'SOMEDAY') NOT NULL I am guessing that some form of ALTER TABLE t MODIFY command is required, but I am hesitant as the database is populated and I do not wish to lose data if I can help it. :) Any help would be appreciated. thanks, Avram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL newbie: table gone after reboot
I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newbie: table gone after reboot
I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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 newbie: table gone after reboot
Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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 newbie: table gone after reboot
I have tried logging in as root ( mysql -p -u root todo ) and also as myself and both methods yield the same results, shown below: mysql SHOW TABLES FROM todo; ERROR 12: Can't read dir of './todo/' (Errcode: 13) mysql It continues to be curious... -A On Wednesday, September 24, 2003, at 08:22 AM, Victor Pendleton wrote: Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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 newbie: table gone after reboot
Changing the ownership fixed everything. I did not realize there were multiple notions of 'root'. Thank-you, Avram On Wednesday, September 24, 2003, at 12:10 PM, Victor Pendleton wrote: The user `root` in MySQL is not the same as the Unix `root` user. The user, mysql, or whatever you have selected needs to own the directory and files in the todo directory. chown -R mysql todo chgrp -R mysql todo ... Error 13 means that the user that started the MySQL server does not have permission to access the todo directory. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 1:33 PM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I have tried logging in as root ( mysql -p -u root todo ) and also as myself and both methods yield the same results, shown below: mysql SHOW TABLES FROM todo; ERROR 12: Can't read dir of './todo/' (Errcode: 13) mysql It continues to be curious... -A On Wednesday, September 24, 2003, at 08:22 AM, Victor Pendleton wrote: Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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]