MYISAM + Group By

[Harini Raghavan]

Hello All,

I have a MYISAM table (employment_summary) with over 6 Lac records. I use it
to do fast keyword searches on employments and want to retrieve the results
grouped by the executive. The executive table is also a huge INNODB table
and has over 6 Lac records. If I do any join operations with
employment_summary and executive table, the search becomes very slow and
sometimes takes over 3 mins to return. To improve the performance, I moved
all the searchable data from executive to employment_summary and tried to
avoid doing any join between MYISAM and INNODB.

But even after this the search is not really fast. For instance, a simple
query below takes a around 50 sec:

select *  from employment_summary where (MATCH(title) AGAINST(' +director'
IN BOOLEAN MODE))  group by executive_id limit 0,200;

Running explain on the above query, I realised, it is using the full text
index on title but it is not using the index on executive_id for grouping
the results. If I try to run the same query without doing the text search,
it returns really fast and it is using the index on executive_id column:

select *  from employment_summary group by executive_id limit 0,200;

Here is the employment_summary table on which I am trying the above queries.

| employment_summary | CREATE TABLE `employment_summary` (
 `id` varchar(32) NOT NULL default '',
 `executive_id` varchar(32) NOT NULL default '',
 `firstName` text,
 `lastName` text,
 `title` text,
 `job_description` text,
 KEY `execIdIndex` (`executive_id`),
 KEY `empIdIndex` (`id`),
 FULLTEXT KEY `jobDescriptionFullTextIndex` (`job_description`),
 FULLTEXT KEY `titleFullTextIndex` (`title`),
 FULLTEXT KEY `firstNameIndex` (`firstName`),
 FULLTEXT KEY `lastNameIndex` (`lastName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |


Is there anything I am missing? How can I make the query use the index for
grouping?

-Harini

innodb error

[Rilawich Ango]

Recently, I got the following error.  I found the solution to solve
the problem by increasing the log file size.  However, I want to know
why this error will happen.  I can't find the explanation for that
error.  Anyone can give me some information about the error?

070321 16:38:41  InnoDB: Started; log sequence number 3 26658057
070321 16:38:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.27-standard-log'  socket: '/var/lib/mysql/mysql.sock'
port: 3306  MySQL Community Edition - Standard (GPL)
070322  9:24:54  InnoDB: ERROR: the age of the last checkpoint is 9433633,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Are there any tools to get "diff" (alter commands) between tow tables ?

[Daevid Vincent]

> > > I wonder if there are any tools to do a "diff" between two tables
> > > struture to result alter command to convert table A 
> > > structure to table B structure ?
> >
> > Yes, our tool "Database Workbench" includes a so-called
> > "Schema Compare" tool which allows you to compare structures
> > and create a change script.
> 
> I would like script tool, not gui software.

Wouldn't we all...

This tool has been asked for, for years. I myself have asked several times
and searched several times.

If you find one, let the rest of us know would you?

I wish mySQL would make one as part of the package.

d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: What does NOW() return: linux time or something else?

[Tim Lucia]

> -Original Message-
> From: murthy gandikota [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 21, 2007 6:05 PM
> To: mysql@lists.mysql.com
> Subject: What does NOW() return: linux time or something else?
> 
> Hi
>   I tried to look up the time functions specifically for the disparity
> between NOW() and the linux time obtained with 'date'. Why are they
> different? How can I set the database time?

What is the disparity between them?  On my Linux (RHEL V.4) box, the two are
in sync:

[EMAIL PROTECTED] ~]# uname -a
Linux mysql1 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64
x86_64 x86_64 GNU/Linux
[EMAIL PROTECTED] ~]# date
Wed Mar 21 22:33:31 EDT 2007
[EMAIL PROTECTED] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 115302 to server version: 5.0.24-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

([EMAIL PROTECTED]) [(none)]> select now();
+-+
| now()   |
+-+
| 2007-03-21 22:33:36 |
+-+
1 row in set (0.00 sec)


> 
>   Thanks
>   Murthy
> 
> 
> -
> Looking for earth-friendly autos?
>  Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Rename of Table That Receives a High Volume of Reads

[Paul J. Boyes]

Great.  That is the assumption under which I have been working.  I was 
really hoping for confirmation.  I appreciate it!  Thanks for 
responding.


Thanks,

Paul


Chris Comparini wrote:

On Wednesday 21 March 2007 16:31, Paul J. Boyes wrote:
  

Hello,

I have a fairly sizeable aggregate table that is built from a large
amount of data that is going to receive a large volume of reads. I am
looking at possibly using the rename to swap the table out regularly
with an updated one. Is this a viable solution for a table that
experiences a large number of reads? Will it work? What are the drawbacks?

Having never done this with MySQL, I am hoping to get some advice from
others who may have attempted this, are in the know, etcAny info you
can provide would be greatly appreciated.



In my experience it is safe and fast to do this sort of thing:

RENAME TABLE live_table TO old_table, new_table TO live_table;

As far as I know doing the 2 renames in one command (as above)
is the only way to do them atomically in MySQL.

Hope that helps,

  -Chris

  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Rename of Table That Receives a High Volume of Reads

[Chris Comparini]

On Wednesday 21 March 2007 16:31, Paul J. Boyes wrote:
> Hello,
>
> I have a fairly sizeable aggregate table that is built from a large
> amount of data that is going to receive a large volume of reads. I am
> looking at possibly using the rename to swap the table out regularly
> with an updated one. Is this a viable solution for a table that
> experiences a large number of reads? Will it work? What are the drawbacks?
>
> Having never done this with MySQL, I am hoping to get some advice from
> others who may have attempted this, are in the know, etcAny info you
> can provide would be greatly appreciated.

In my experience it is safe and fast to do this sort of thing:

RENAME TABLE live_table TO old_table, new_table TO live_table;

As far as I know doing the 2 renames in one command (as above)
is the only way to do them atomically in MySQL.

Hope that helps,

  -Chris

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Rename of Table That Receives a High Volume of Reads

[Paul J. Boyes]

Hello,

I have a fairly sizeable aggregate table that is built from a large 
amount of data that is going to receive a large volume of reads. I am 
looking at possibly using the rename to swap the table out regularly 
with an updated one. Is this a viable solution for a table that 
experiences a large number of reads? Will it work? What are the drawbacks?


Having never done this with MySQL, I am hoping to get some advice from 
others who may have attempted this, are in the know, etcAny info you 
can provide would be greatly appreciated.


Thanks,

Paul J. Boyes

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

What does NOW() return: linux time or something else?

[murthy gandikota]

Hi
  I tried to look up the time functions specifically for the disparity between 
NOW() and the linux time obtained with 'date'. Why are they different? How can 
I set the database time?
   
  Thanks
  Murthy

  
-
Looking for earth-friendly autos? 
 Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.  

comparing storing engines

[Octavian Rasnita]

Hi,

Is there somewhere a speed comparison between the storage engines that can 
be used in MySQL?


Thank you.

Octavian


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Functions and NULL with standard install

[Lucas . CTR . Heuman]

I don't see how  to use this here, I will have to research the |/||/|

select rpad(|IFNULL(|null, ''),5,'1');

|/||/|


but this below works thank you 

SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1');

If you are using it on an actual field, you would replace the first NULL 
and
the '2' with the field to be checked.



On 3/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> I am running a basic install of MySQL 5.0 with "strict mode" turned on I
> would like to use RPAD however at times a NULL var will be sent to the
> function. My goal is to have a function that will convert the NULL to a
> blank string to get the result of EXAMPLE3 if a NULL is returned.
>
>
> EXAMPLE 1
>
> mysql> select rpad(null,5,'1');
> +--+
> | rpad(null,5,'1') |
> +--+
> | NULL |
> +--+
> 1 row in set (0.00 sec)


Try this:

RE: ORDER BY question

[Gordon]

I think you can also do 
SELECT *, 
   DATE_FORMAT(deadline, '%d-%m-%Y') AS deadline_f,
   Status + 0 AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

That way you do not have to change the code if you add a value to the
enum list via ALTER TABLE.
-Original Message-
From: Mike van Hoof [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 21, 2007 3:19 AM
To: Christophe Gregoir
Cc: mysql
Subject: Re: ORDER BY question

Thanks, that is also a solution.

Friend of mine pointed me to the following:

SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
CASE `status`
WHEN 'not yet started' then 1
WHEN 'in progress' then 4
WHEN 'finished' then 5
WHEN 'now hiring' then 3
WHEN 'waiting' then 2
WHEN 'closed' then 6
END AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

- Mike

Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
 
Uw bedrijf voor Multimedia op Maat



Christophe Gregoir schreef:
> Hey Mike,
>
> Sounds like you would be better of with an ENUM of integers, e.g. 
> ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so

> on.
> To answer your question:
> ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
> 'finished', `status` = 'canceled'
>
> Mike van Hoof wrote:
>> Hello everybody,
>>
>> I got a small problem with ordering on en ENUM field. The values in 
>> this field are:
>> - to be started
>> - started
>> - finished
>> - canceled
>>
>> And i want to order on this field, but in the direction the are above

>> here (and not alpabetically).
>> Is that possible?
>>
>> - Mike
>>
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Functions and NULL with standard install

[Francesco Riosa]

select rpad(|IFNULL(|null, ''),5,'1');

|/||/|
[EMAIL PROTECTED] ha scritto:
I am running a basic install of MySQL 5.0 with "strict mode" turned on  I 
would like to use RPAD however at times a NULL var will be sent to the 
function. My goal is to have a function that will convert the NULL to a 
blank string to get the result of EXAMPLE3 if a NULL is returned.



EXAMPLE 1

mysql> select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

EXAMPLE 2
mysql> select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0   |
+-+
1 row in set (0.00 sec)

mysql>

EXAMPLE 3

mysql> select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1  |
++
1 row in set (0.02 sec)


Is there a function I can use to convert the null string to a blank string 
before it is sent to RPAD in a basic installation of MySQL or will I need 
to create a function?



Wishing you the best you know you deserve,

__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone 609.485.5401
  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Functions and NULL with standard install

[Joshua Marsh]

On 3/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


I am running a basic install of MySQL 5.0 with "strict mode" turned on  I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.


EXAMPLE 1

mysql> select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)



Try this:

SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1');

If you are using it on an actual field, you would replace the first NULL and
the '2' with the field to be checked.

Updates and Records Changed. Bug?

[Brian Bird]

I think this is a bug in MySQL 5.1.16-beta (I'm using SUSE 10.2 Linux),
but I can't find any information on it.

 

When performing an update which doesn't change any rows, I get different
results back depending on the declarations of the fields in the table.

 

If a 'normal' NDB table is created and an inserted row is updated:

 

mysql> CREATE TABLE table1 (field1 int(11), field2 int(11)) engine ndb;

Query OK, 0 rows affected (1.17 sec)

 

mysql> insert into table1 values (1,2);

Query OK, 1 row affected (0.04 sec)

 

mysql> update table1 set field2=3 where field1=1; -- This should return
one changed row

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> update table1 set field2=3 where field1=1; -- The same update
should return 0 rows

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

 

The second update is 'changing' the record to the same value currently
in the database, so it returns 0 records changed.

 

However, if the table is changed so that field1 is a primary key, the
number of rows changed is different:

 

mysql> CREATE TABLE table2 (field1 int(11) primary key, field2 int(11))
engine ndb;

Query OK, 0 rows affected (4.18 sec)

 

mysql> insert into table2 values (1,2);

Query OK, 1 row affected (0.00 sec)

 

mysql> update table2 set field2=3 where field1=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> update table2 set field2=3 where field1=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

Here the second update reports 1 row has changed, even though the data
is the same.

 

This doesn't happen in MySQL 5.1.11 on the same machine. In both cases
I'm running a single node cluster on one box (I know that's a bit
pointless but it's meant to be a proof of concept :-)). I tried the same
tests using InnoDB and got 1 Changed row in each case, and with MyISAM I
got 0 rows changed.

 

Can anyone confirm if they have the same problem or if it's something
I've done wrong?

 

Brian

 

Functions and NULL with standard install

[Lucas . CTR . Heuman]

I am running a basic install of MySQL 5.0 with "strict mode" turned on  I 
would like to use RPAD however at times a NULL var will be sent to the 
function. My goal is to have a function that will convert the NULL to a 
blank string to get the result of EXAMPLE3 if a NULL is returned.


EXAMPLE 1

mysql> select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

EXAMPLE 2
mysql> select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0   |
+-+
1 row in set (0.00 sec)

mysql>

EXAMPLE 3

mysql> select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1  |
++
1 row in set (0.02 sec)


Is there a function I can use to convert the null string to a blank string 
before it is sent to RPAD in a basic installation of MySQL or will I need 
to create a function?


Wishing you the best you know you deserve,

__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone 609.485.5401

Re: Database creation question

[Brent Baisley]

You might try issueing a FLUSH PRIVILEGES command instead of restarting. This is supposed to be implicit when you use the GRANT 
statement. If you do a direct insert into the user table you have to issue this command.



- Original Message - 
From: "Lopez, Denise" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 20, 2007 7:55 PM
Subject: RE: Database creation question


That still didn't work.  I think I just need to restart the mysql
service.

Denise Lopez
UCLA Center for Digital Humanities
Network Services
Systems Engineer
337 Charles E. Young Drive East
PPB 1020
Los Angeles, CA 90095
310/206-8216



From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 20, 2007 4:03 PM
To: Lopez, Denise
Cc: mysql@lists.mysql.com
Subject: Re: Database creation question



Denise,

Hola!.

1) Connect to the system like root user

2) $ chown -R mysql:mysql /usr/local/mysql/

3) In oder to create user use :


mysql> GRANT ALL PRIVILEGES ON DATABASENAME.* TO USERNAME@"%"
  IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Regards,
Juan Eduardo

On 3/20/07, Lopez, Denise < [EMAIL PROTECTED]
 > wrote:

Hello everyone,

I had a really weird thing happen and I was wondering if anyone has seen

anything like this. From a shell command line I connected to a running
instance of mysql with the mysql -u root -p command. I successfully get
to a mysql prompt. I needed to create a new database and user for the
database.
mysql > create database 'database name';
mysql > grant all privileges on 'database_name'.* to [EMAIL PROTECTED]
identified by 'password';

These 2 commands finished successfully and when I run the show databases

command, my new database shows up.  I can exit back to shell prompt and
reconnect to mysql with the command above and it still displays the new
database.

Up to here is what I expected, here's the weird part.  With the username

and password from above I tried to create a table in my database and
received this error:

Can't create/write to file
'/usr/local/mysql/var/timetrackerdb/mytemp.MYI'
I google'd this and the only suggestion was the disk that mysql was
trying to write to was out of space.  So I checked the space on
/usr/local/mysql/var and there is plenty of room on the drive.

Come to find out that the database directory didn't get created in the
data directory where all the database directories are located.

Any ideas why the mysql process didn't create the database directory?  I
already checked permissions on the /usr/local/mysql root mysql 755 and
/usr/local/mysql/var mysql mysql 700.

Thanks in advance.

Denise Lopez
UCLA Center for Digital Humanities
Network Services
Systems Engineer
337 Charles E. Young Drive East
PPB 1020
Los Angeles, CA 90095
310/206-8216





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problems with replication when db is changed

[Bgs]

 Greetings,

We have regular problems with mysql replication when there is a db 
change. This is mostly ALTER TABLE. The sync breaks and we either have 
to do the changes manually or either shut down the whole system for a 
new sync from zero. Is there a way to sync alter table commands on the 
fly? Shouldn't they be executed just as the insert/update commands?


Regards
Bgs

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating Password & Username with phpMyAdmin

[David Blomstrom]

Ah, that does look like a better way. Thanks!

Schalk Neethling <[EMAIL PROTECTED]> wrote: Hi David,

Actually the way to do this would be as follows:

When you have created a new database, click on the SQL tab and the use 
the following to create the user and password:
GRANT ALL PRIVILEGES ON databasename.* TO [EMAIL PROTECTED] IDENTIFIED 
BY 'password'

Hit the submit button and you are set.



 
-
We won't tell. Get more on shows you hate to love
(and love to hate): Yahoo! TV's Guilty Pleasures list.

Re: Database creation question

[Juan Eduardo Moreno]

In your conf file, please add :

Previously create a temp directory


[mysqld]
tmpdir=/usr/local/mysql/temp


Regards,
Juan

On 3/20/07, Lopez, Denise <[EMAIL PROTECTED]> wrote:


That still didn't work.  I think I just need to restart the mysql
service.

Denise Lopez
UCLA Center for Digital Humanities
Network Services
Systems Engineer
337 Charles E. Young Drive East
PPB 1020
Los Angeles, CA 90095
310/206-8216



From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 20, 2007 4:03 PM
To: Lopez, Denise
Cc: mysql@lists.mysql.com
Subject: Re: Database creation question



Denise,

Hola!.

1) Connect to the system like root user

2) $ chown -R mysql:mysql /usr/local/mysql/

3) In oder to create user use :


mysql> GRANT ALL PRIVILEGES ON DATABASENAME.* TO USERNAME@"%"
   IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Regards,
Juan Eduardo

On 3/20/07, Lopez, Denise < [EMAIL PROTECTED]
 > wrote:

Hello everyone,

I had a really weird thing happen and I was wondering if anyone has seen

anything like this. From a shell command line I connected to a running
instance of mysql with the mysql -u root -p command. I successfully get
to a mysql prompt. I needed to create a new database and user for the
database.
mysql > create database 'database name';
mysql > grant all privileges on 'database_name'.* to [EMAIL PROTECTED]
identified by 'password';

These 2 commands finished successfully and when I run the show databases

command, my new database shows up.  I can exit back to shell prompt and
reconnect to mysql with the command above and it still displays the new
database.

Up to here is what I expected, here's the weird part.  With the username

and password from above I tried to create a table in my database and
received this error:

Can't create/write to file
'/usr/local/mysql/var/timetrackerdb/mytemp.MYI'
I google'd this and the only suggestion was the disk that mysql was
trying to write to was out of space.  So I checked the space on
/usr/local/mysql/var and there is plenty of room on the drive.

Come to find out that the database directory didn't get created in the
data directory where all the database directories are located.

Any ideas why the mysql process didn't create the database directory?  I
already checked permissions on the /usr/local/mysql root mysql 755 and
/usr/local/mysql/var mysql mysql 700.

Thanks in advance.

Denise Lopez
UCLA Center for Digital Humanities
Network Services
Systems Engineer
337 Charles E. Young Drive East
PPB 1020
Los Angeles, CA 90095
310/206-8216

Re: ORDER BY question

[Mike van Hoof]

Thanks, that is also a solution.

Friend of mine pointed me to the following:

   SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
   CASE `status`
   WHEN 'not yet started' then 1
   WHEN 'in progress' then 4
   WHEN 'finished' then 5
   WHEN 'now hiring' then 3
   WHEN 'waiting' then 2
   WHEN 'closed' then 6
   END AS sorted_grade
   FROM v_issue_project_task
   ORDER BY sorted_grade

- Mike

Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat



Christophe Gregoir schreef:

Hey Mike,

Sounds like you would be better of with an ENUM of integers, e.g. 
ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so 
on.

To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
'finished', `status` = 'canceled'


Mike van Hoof wrote:

Hello everybody,

I got a small problem with ordering on en ENUM field. The values in 
this field are:

- to be started
- started
- finished
- canceled

And i want to order on this field, but in the direction the are above 
here (and not alpabetically).

Is that possible?

- Mike






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY question

[Christophe Gregoir]

Hey Mike,

Sounds like you would be better of with an ENUM of integers, e.g. 
ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so on.

To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
'finished', `status` = 'canceled'


Mike van Hoof wrote:

Hello everybody,

I got a small problem with ordering on en ENUM field. The values in 
this field are:

- to be started
- started
- finished
- canceled

And i want to order on this field, but in the direction the are above 
here (and not alpabetically).

Is that possible?

- Mike




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ORDER BY question

[Mike van Hoof]

Hello everybody,

I got a small problem with ordering on en ENUM field. The values in this 
field are:

- to be started
- started
- finished
- canceled

And i want to order on this field, but in the direction the are above 
here (and not alpabetically).

Is that possible?

- Mike

--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567

url: www.medusa.nl
mail: [EMAIL PROTECTED]

Uw bedrijf voor Multimedia op Maat


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating Password & Username with phpMyAdmin

[Schalk Neethling]

Hi David,

Actually the way to do this would be as follows:

When you have created a new database, click on the SQL tab and the use 
the following to create the user and password:
GRANT ALL PRIVILEGES ON databasename.* TO [EMAIL PROTECTED] IDENTIFIED 
BY 'password'


Hit the submit button and you are set.

HTH

Schalk Neethling
AlliedBridge (code.google.com/p/alliedbridge)


David Blomstrom wrote:

Sorry for the beginner's question, but I'm confused. I can easily create 
passwords and usernames for my online databases by going into my C-Panel and 
clicking a MySQL icon. It fetches a page that allows me to easily create and 
delete databases, add and change passwords, etc. But I can't remember how I 
created my password and username for the database on my PC.

I'm now trying to duplicate that database on a MacBook Pro running MySQL 5 and 
the current version of phpMyAdmin. But I'm not sure about creating a password 
and username with phpMyAdmin.

Suppose I have a database named World, and I want to give it the password 
Global and the username Citizen. I created the database, then clicked the 
Privileges tab, which took me to the page
User 'root'@'localhost'  : Edit PrivilegesAm I correct in ignoring everything 
(Global Privileges, Database-Specific Privileges, etc.) except the section?...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]