Hi folks,


I ran;

mysql> CREATE TABLE `tblPerdition` (
     -> `user` varchar(128) not null default 'NULL',
     -> `domain` varchar(255) not null default 'NULL',
     -> `servername` varchar(255) not null default 'NULL',
     -> `port` varchar(8) not null default 'NULL'
     -> ); Query OK, 0 rows affected (0.00 sec)

creating following table;


mysql> DESCRIBE tblPerdition;

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user       | varchar(128) | NO   |     | NULL    |       |
| domain     | varchar(255) | NO   |     | NULL    |       |
| servername | varchar(255) | NO   |     | NULL    |       |
| port       | varchar(8)   | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

But can't figure out how to set MUL KEY

Adding follow did not work;

    -> MUL KEY (`user`), (`domain`), (`servername`), (`port`) 
    -> );
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY ( `user`), (`domain`), (`servername`), (`port`)
)' at line 6

Please help. TIA


B.R.
satimis

First, let me just tell you that defining a column not null, with a default string "null" is pretty like trying to climb a mountain blinded.
You can do that, but it looks like you have missed the point.

But appart that, what is the "MUL KEY" operator ?
I'm DBA for 4 years, and I never have heard of that beast.
You surely are confused by the MUL value that appears on a describe under the column "keys", which means that the column is part of a non unique index (MUL like multiple).
http://lists.mysql.com/mysql/111893

Originally Posted by tripy

First, let me just tell you that defining a column not null, with a default string "null" is pretty like trying to climb a mountain blinded.
You can do that, but it looks like you have missed the point.

But appart that, what is the "MUL KEY" operator ?
I'm DBA for 4 years, and I never have heard of that beast.
You surely are confused by the MUL value that appears on a describe under the column "keys", which means that the column is part of a non unique index (MUL like multiple).
http://lists.mysql.com/mysql/111893

Hi tripy,


Thanks for your advice.

I tried follows without solution;

mysql> ALTER TABLE tblPerdition
    -> ADD MULTIPLE KEY (`user`,`domain`,`servername`,`port`);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY (`user`,`domain`,`servername`,`port`)' at line 2

mysql> ALTER TABLE tblPerdition
     -> ADD KEY=multiple (`user`,`domain`,`servername`,`port`);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=multiple (`user`,`domain`,`servername`,`port`)' at line 2

Please help. TIA


B.R.
satimis

http://dev.mysql.com/doc/refman/5.0/...ate-index.html

Thanks Chris, I would not have directed him elsewhere.

Originally Posted by chrishirst

http://dev.mysql.com/doc/refman/5.0/...ate-index.html

Hi chrishirst,


Thanks for your URL

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]

I haven't resolve how to apply it on my case.

mysql > CREATE INDEX mul
         - > [index_type] (what shall I replace it ?)
         - > ON tblPerdition (Key)
         - > [index_type] (again what shall I replace it ?)

Please advise. TIA


B.R.
satimis

Some storage engines allow you to specify an index type when creating an index. The allowable index type values supported by different storage engines are shown in the following table. Where multiple index types are listed, the first one is the default when no index type specifier is given.

Storage Engine		Allowable Index Types
MyISAM			BTREE, RTREE
InnoDB			BTREE
MEMORY/HEAP		HASH, BTREE
NDB			HASH, BTREE

In SQL Server, the syntax would be very easy.

Create [Unique /*if desired*/] Index give_your_index_a_name On myTable ( keyColumn1, keyColumn2, keyColumn3 )

I would imagine the syntax is similar in MySQL?

I would imagine the syntax is similar in MySQL?

Yep, it's identical.

Interestingly, these compound indexes can help tremendously. They'll slow down changes to your data, but, on the other hand, if all the columns you need in your query are in the index, the query engine will only read the index, and never even look at the table, to satisfy your query.

You can do the same thing with constraints like primary keys.

Alter Table myTable Add Table_Primary_Key Primary Key ( keyColumn1, keyColumn2 )

It didn't seem to be a question about primary keys specifically, but since the shortcut in a create table command can only be attached to one column, that's how you would do it.

Originally Posted by Learning Newbie

In SQL Server, the syntax would be very easy.

Create [Unique /*if desired*/] Index give_your_index_a_name On myTable ( keyColumn1, keyColumn2, keyColumn3 )

I would imagine the syntax is similar in MySQL?

Hi,


Whether in my case run;

mysql > CREATE INDEX mul On tblPerdition ( Key );

TIA


B.R.
satimis

Originally Posted by chrishirst

Storage Engine        Allowable Index Types
MyISAM            BTREE, RTREE
InnoDB            BTREE
MEMORY/HEAP        HASH, BTREE
NDB            HASH, BTREE

Hi chrishirst,


Advice noted with thanks.


Performed following test;

mysql> ALTER TABLE tblPerdition
    -> ADD KEY (`user`,`domain`,`servername`,`port`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE tblPerdition;

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user       | varchar(128) | NO   | MUL | NULL    |       |
| domain     | varchar(255) | NO   |     | NULL    |       |
| servername | varchar(255) | NO   |     | NULL    |       |
| port       | varchar(8)   | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MUL key only displayed on user row, others without it.


However if running;

mysql> SHOW CREATE TABLE tblPerdition;

+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table               |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tblPerdition | CREATE TABLE `tblPerdition` (
  `user` varchar(128) NOT NULL default 'NULL',
  `domain` varchar(255) NOT NULL default 'NULL',
  `servername` varchar(255) NOT NULL default 'NULL',
  `port` varchar(8) NOT NULL default 'NULL',
  KEY `user` (`user`,`domain`,`servername`,`port`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

KEY `user` (`user`,`domain`,`servername`,`port`)

They are displayed there.


Did I make something wrong? TIA


B.R.
satimis

But what is it whith your multiple index anyway?
What are you trying to achieve?
Having a key is one thing, the "mul" keyword is just an indication if the value is unique or not.

What are you so obsessed about getting that schema?
Let the DB decide for itself what way the index would be the more useful.

Originally Posted by tripy

But what is it whith your multiple index anyway?
What are you trying to achieve?
Having a key is one thing, the "mul" keyword is just an indication if the value is unique or not.

What are you so obsessed about getting that schema?
Let the DB decide for itself what way the index would be the more useful.

Hi tripy,


I'm trying to create a table with following content and blank port;

mysql> SELECT * from tblPerdition;

+---------+----------------------+----------------------------+------+
| user    | domain               | servername                 | port |
+---------+----------------------+----------------------------+------+
| satimis | satimis.changeip.net | xen08.satimis.changeip.net |      |
| satimis | satimis.com          | xen10.satimis.com          |      |
| satimis | satimis.dnsalias.com | xen09.satimis.dnsalias.com |      |
+---------+----------------------+----------------------------+------+
3 rows in set (0.01 sec)

But now I got;

mysql> SELECT * from tblPerdition;

+---------+----------------------+----------------------------+------+
| user    | domain               | servername                 | port |
+---------+----------------------+----------------------------+------+
| satimis | satimis.changeip.net | xen08.satimis.changeip.net | NULL |
| satimis | satimis.com          | xen10.satimis.com          | NULL |
| satimis | satimis.dnsalias.com | xen09.satimis.dnsalias.com | NULL |
+---------+----------------------+----------------------------+------+
3 rows in set (0.01 sec)

Anything I did it wrongly? How to fix it?

TIA


B.R.
satimis

Anything I did it wrongly? How to fix it?

Nothing, if the field is empty it will be NULL

I'm trying to create a table with following content and blank port;

Well, in that case, why do you define a string value "NULL" as default for the field, if you don't want it ?

`port` varchar(8) NOT NULL default 'NULL',

A string 'NULL' is the the same as a null value.
In your create table you said that you didn't wanted the column to have a null value, but that you wanted it to be filled with the string 'NULL' for each new rows, if you did not specified it.
Simply ignore the default value, and you should be ok.

port` varchar(8) NOT NULL

Originally Posted by chrishirst

Nothing, if the field is empty it will be NULL

Hi chrishirst,


Ah I see.

I ran;

mysql> INSERT into tblPerdition (user,domain,servername)
     -> VALUE ('satimis','satimis.changeip.net','xen08.satimis.changeip.net'); 
Query OK, 1 row affected (0.01 sec)

Whether I should run;

mysql> INSERT into tblPerdition (user,domain,servername,port)
     -> VALUE ('satimis','satimis.changeip.net','xen08.satimis.changeip.net',' ');

to enter the data?


Is there any way to change it without dropping the table? Thanks


B.R.
satimis

UPDATE tblPerdition SET port = " " WHERE port IS NULL ;

Originally Posted by chrishirst

UPDATE tblPerdition SET port = " " WHERE port IS NULL ;

Hi chrishirst,


It needs minor change on your command;

mysql> UPDATE tblPerdition SET port=" " WHERE port="NULL";
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * from tblPerdition;

+---------+----------------------+----------------------------+------+
| user    | domain               | servername                 | port |
+---------+----------------------+----------------------------+------+
| satimis | satimis.changeip.net | xen08.satimis.changeip.net |      |
| satimis | satimis.com          | xen10.satimis.com          |      |
| satimis | satimis.dnsalias.com | xen09.satimis.dnsalias.com |      |
+---------+----------------------+----------------------------+------+
3 rows in set (0.01 sec)

B.R.
satimis

Originally Posted by tripy

Well, in that case, why do you define a string value "NULL" as default for the field, if you don't want it ?
A string 'NULL' is the the same as a null value.
In your create table you said that you didn't wanted the column to have a null value, but that you wanted it to be filled with the string 'NULL' for each new rows, if you did not specified it.
Simply ignore the default value, and you should be ok.

port` varchar(8) NOT NULL

Hi tripy,


Advice noted with thanks


B.R.
satimis