Oracle , what else ?
hello people, bad joke is not it ? After MySQL bought by the java maker, and now Sun bought by Oracle, what are we gonna run as RDBMS ? _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - g...@iap.fr 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to display numbers with exponent
Hello again,
I inserted double values, like :
mysql insert into t (n) values ('0.54316E+11');
then I display it as :
5431600
which is not easy to read.
Is there a way to tell Mysql to display such numbers
with exponent ?
I could not find a trick through and around
http://dev.mysql.com/doc/refman/5.0/en/precision-math-numbers.html
thanks.
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default, Nullable and NULL : confused
Hello, I do not understand the behavior of a simple table : from what I red, in the following exemple the Null column tells the value can be set to NULL, and the Default value is NULL. It doesn't seem to work that way. Some one could explain it ? I run on a linux debian/etch 5.0.32 MySQL release. I have a table named t like : mysql describe t; +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | n | double | YES | | NULL| | | c | varchar(5) | YES | | NULL| | +---++--+-+-+---+ Now I load data infile like this : load data infile'/data/foo' into table t fields terminated by';'; with /data/foo containing : 0.12345;qwer 1.2345; ;asdf I get Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 mysql show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1265 | Data truncated for column 'n' at row 3 | +-+--++ from now, I expect to have NULL where the fields are empty, but instead I get '' in the 2nd row, columb 'c' '0' in the last row, column 'n' mysql select * from t; +-+--+ | n | c| +-+--+ | 0.12345 | qwer | | 1.2345 | | | 0 | asdf | +-+--+ mysql select * from t where c is null or n is null; Empty set (0.00 sec) For my purpose, '0' , '' and NULL Thank you for any help. regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to suppress the SHOW WARNINGS limit ?
hello, from the page http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html I understand that if I want to look at all the warnings with the command : show warnings; then I have first to set a limit bigger than any numbers of warnings that could happen, say : (I know that it might be painfull to look at billion of warnings... but) SET max_error_count=1000; Is there just a way to suppress the limit , and so never being limited to 64 default value ? thanx ! _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 et les charset sur debian etch
Hello, j'ai 2 machines Linux debian etch, avec MySQL 5.0.32 J'ai un problème d'affichage sur l'une des machines : j'ai créé la même table et chargé le même fichier data dans une base sur chaque machine, et l'affichage est différent. Je ne vois pas oú est la différence... my.cnf idem locale idem mysql select @@character_set_server,@@collation_server,@@character_set_connection; +---+++ | @@character_set_server | @@collation_server | @@character_set_connection +++---+ | utf8 | utf8_general_ci| utf8 +++---+ mysql select nom,id from t; +--++ | nom | id | +--++ | aàb | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +--++ sur l'autre machine, l'affichage n'est pas bon. mysql select @@character_set_server,@@collation_server,@@character_set_connection; ++++ | @@character_set_server | @@collation_server | @@character_set_connection ++++ | utf8 | utf8_general_ci| utf8 ++++ mysql select nom,id from t; +++ | nom| id | +++ | aà b | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +++ mais si je fais : mysql charset latin1; les accents sont là oú il faut, mais pas l'alignement des colones... mysql select nom,id from t; +--+--+ | nom | id | +--+--+ | aàb |1 | | été|2 | | cçoôeêeèeëi |3 | | EÉEÈEË|4 | | c'est tout |5 | +--+--+ si quelqu'un peut reproduire la chose et m'expliquer le problème... voici la commande de création de la table : mysql CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL auto_increment, `nom` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ; ensuite on rentre les données : mysql load data infile '/le_repertoire/t' into table t fields terminated by ':'; et le fichier t contient : 1:aàb 2:été 3:cçoôeêeèeëi 4:EÉEÈEË 5:c'est tout _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with accents and display alignement
hello, first, some settings : MySQL 5.0.32, under Linux debian etch the problem is the bad alignment : mysql select nom,id from t2; +--++ | nom | id | +--++ | aàb | 1 | | été| 2 | | cçoôeêeèeëi | 3 | | c'est tout | 4 | +--++ AS YOU SEE, THE DISPLAY IS NOT GOOD AT ALL : how could I got the column WELL ALIGNED ??? thank you. WHAT I HAVE DONE : mysql SHOW VARIABLES LIKE 'character_set_system'; +--+---+ | Variable_name| Value | +--+---+ | character_set_system | utf8 | +--+---+ I create a tiny test table, loaded with a file data2 made under editor vi, and on wich the unix command file data2 returns : data2: UTF-8 Unicode text Of course, there is no space before each end of line character mysql CREATE TABLE t2 (id int(10) unsigned NOT NULL auto_increment,nom varchar(255) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=UTF8; after loading the file data2, I display the content _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
columns_priv : how it works ? [ MySQL 4.1 ]
hello, There is something I obviously missed, can't figure out what, since it seems so simple. as user root (of mysql), mysql use mysql; I can see : mysql select * from columns_priv where User='demandeur' order by Column_name; and I get : +--+---+--+---++-+---+ |Host |Db |User |Table_name |Column_name |time |Column_priv| +--+---+--+---++-+---+ |localhost |people |demandeur |current|HOMEDIR |19h30|Select | ... now, as user 'demandeur', on the Db 'people' I got : mysql use people; mysql select HOMEDIR from current; ERROR 1142 (42000): SELECT command denied to user 'demandeur'@'localhost' for table 'current' This is not what I would expect from the Column_priv. What is wrong ? (I have done any way flush privileges; ) cheers ! _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ] + column Comment
Hello, thanks again to ViSolve DB Team. so, it turns out that for MySQL4.1, il will have to built a sql file, built with script (shell or perl). [ note that so far I use MySQL 4.1 on production server, and I only test features on MySQL 5 on an other machine linux Debian]. at last, my LAST QUESTION concerns column Comment In MySQL 5, I can do : mysql select column_name,COLUMN_COMMENT from information_schema.columns where table_schema=a_base and table_name='a_tab'; in MySQL 4.1 , the equivalent would be : mysql select distinct Column_name from columns_priv where Db='a_base' and Table_name='a_tab'; AND THERE IS NO COMMENT. but, from the page : http://dev.mysql.com/doc/refman/4.1/en/charset-show.html it seems that one could use Comment mysql SHOW FULL COLUMNS FROM a_tab; displays : Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | I cannot find how to set these Comment ? An idea ? thanks. -Gilles- On Thu, 8 Feb 2007, ViSolve DB Team wrote: Hi, The thing is, we cannot dynamically pass columnnames to GRANT or REVOKE statements through procedures from mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke SELECT on a column [ MySQL 4.1 ] + column privileges
hello, first thanks to ViSolve DB Team, and since then, my question turns out to be : in the base that contains a table of 100 columns, I want to disable SELECT on only 1 column hide_this, how to apply column privileges using a loop in mysql, that could do : for each column in the_base.t100 where column_name is NOT hide_this do GRANT SELECT(column_name_n) ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; done Or should I build a script to create sql commands for that ? thanks, You have applied TABLE level GRANT PRIVILEGES and tried to REVOKE that with COLUMN PRIVILEGES. Hence the error. To Fix it, apply column privileges --- mysql GRANT SELECT(hide_this) ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql select * from information_schema.column_privileges; mysql REVOKE SELECT(hide_this) ON the_base.t100 from 'a_user'@'localhost' identified by 'a_passwd'; Note: Always TABLE PRIVILEGES override COLUMN PRIVILEGES Thanks ViSolve DB Team - Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 11:05 PM Subject: revoke SELECT on a column [ MySQL 4.1 ] Hello, In a table [say t100], having 100 columns, I want to allow the select on all columns but 1. I tried to do this by granting all columns in the table t100, of the base, then revoke SELECT on the column hide_this, but this doesn't work. mysql GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql revoke SELECT (hide_this) on the_base.t100 from 'a_user'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'a_user' on host 'localhost' on table 'current' Is there a turn around, or should grant the select on the 99 other columns -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
revoke SELECT on a column [ MySQL 4.1 ]
Hello, In a table [say t100], having 100 columns, I want to allow the select on all columns but 1. I tried to do this by granting all columns in the table t100, of the base, then revoke SELECT on the column hide_this, but this doesn't work. mysql GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql revoke SELECT (hide_this) on the_base.t100 from 'a_user'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'a_user' on host 'localhost' on table 'current' Is there a turn around, or should grant the select on the 99 other columns ? regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to take advantage of STR_TO_DATE
hello,
I rewrite my question in a simpler (?) way :
How could I load in the database, data from a text file containaing date
in a NOT MySQL standard date format [precisely char(10)], so that I get
the date into a MySQL standard date format in a column of type date ?
an other way to ask my question :
how do I transform a text 15/10/1999 into a date 1999-10-15
when I load data from a text file into a MySQL database ?
I know that I could use a script to rewrite the text 15/10/1999
as text 1999-10-15, and then load the file into Mysql (mysql
will accept the 1999-10-15 as a date format). I think that
I might take advantage of STR_TO_DATE, but I dont' know how.
thanks.
==
On Thu, 11 Jan 2007, ViSolve DB Team wrote:
Hi,
STR_TO_DATE() simply converts the given format string to datetime value. So
to change the format of the date dispaly, go for DATE_FORMAT().
For Instance,
mysql select DATE_FORMAT('2007/10/01','%d/%m/%Y');
or
mysql select DATE_FORMAT(datecolumn,'%d/%m/%Y') from table1;
Thanks
ViSolve DB Team
- Original Message - From: Gilles MISSONNIER [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 11, 2007 12:49 AM
Subject: how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the load data infile procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert day/month/year_4digit into the standard MySQL format
that is year_4digit-month-day, then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---+
| 1999-10-15|
+---+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
15/10/1999
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
=
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the load data infile procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert day/month/year_4digit into the standard MySQL format
that is year_4digit-month-day, then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---+
| 1999-10-15|
+---+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
15/10/1999
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
solved : Re: system : Install Mysql 5.x binaries with php4 on Debian
Thank you Ian,
I used the link method {the least change}
{ It turned that the mysql.sock was in /tmp , I missed it because I
tried to find it with the locate command }
Create a symbolic link from the mysql.sock to the place where php
expects to find it:
ln -s /current/location/mysql.sock /new/location/mysql.sock
AND THEN I run in a little snag :
Warning: mysql_connect(): Client does not support authentication protocol
requested by server; consider upgrading MySQL client in ...
I SOLVED it thanks to google, with :
mysql UPDATE mysql.user SET Password = OLD_PASSWORD('the_password') WHERE
User='the_user';
regards to all,
this list is great, mysql too.
- gilles -
On 13 Dec 2006 at 18:59, Gilles MISSONNIER wrote:
Hello,
I use Debian Linux, and I could install (with apt-get install) fine
the Mysql-4.1+apache+php4.
I tried fine Mysql 5. on the same debian machine.
Now I want to use mysql 5. through web/php4
Then I run into problem, like :
Warning: mysql_connect(): Can't connect to local MySQL server through
socket '/var/run/mysqld/mysqld.sock' (2) in
/var/www/test/t1.php on line 9
Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link
resource in /var/www/test/t1.php on line 10
from script.php : connexion a la base refuse
the tool my_print_defaults doesn't help.
any hint?
Hi,
First of all, is the mysql server running? If not then the mysql.sock socket
file will not be
created.
If it is, then its probably in a different location to the one that php expects
to find it. In
which case you have several choices:
Change 'mysql.default_socket =' in php.ini to point at the real location of
mysql.sock
(check with the php.net site to make sure that this option has not changed in
the version
you have).
Change the location that mysql puts the socket file in my.ini (see the manual on
dev.mysql.com for the exact syntax).
Create a symbolic link from the mysql.sock to the place where php
expects to find it:
ln -s /current/location/mysql.sock /new/location/mysql.sock
I consider the latter the best option as it wont break anything else that
depends on the
socket.
Regards
Ian
--
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
system : Install Mysql 5.x binaries with php4 on Debian
Hello, I use Debian Linux, and I could install (with apt-get install) fine the Mysql-4.1+apache+php4. I tried fine Mysql 5. on the same debian machine. Now I want to use mysql 5. through web/php4 Then I run into problem, like : Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /var/www/test/t1.php on line 9 Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/test/t1.php on line 10 from script.php : connexion a la base refuse the tool my_print_defaults doesn't help. any hint? thank you _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
geometry with MySQL-5.0
Hello, I am discovering the Spatial Extensions features of MySQL-5.0 Is there a way to use a spherical geometry ? (instead of Euclidean) like for navigation, or on sky coordinates,... _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question+comment : Re: set DEC as a column name : forbidden
Thank you Paul DuBois. I have 2 questions for you : 1-Why the error code (1064) can't lead explicitly to the syntax problem ? 2-Could you tell me how I could go directly to the relevant page ? [ Hard for you to feel like a lambda user in MySQL ] my comment : The Search the MySQL manual works when you know where is the problem. Funny that the keywords were in my question to the list : DEC column name forbidden, but did not made sens to me to query with these word. first, I tried on the error code [ ERROR 1064 (42000) ] : not understandable to me. then I tried keyword DEC(matching all of the words) 2 pages : helpless then I tried keyword list 17 pages, off topics [ from excerpt ] - Now If you ask me why I used keyword DEC instead of reserved word the answer is right in the begining of the page : http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html keyword DEC or reserved word DEC are the same !! for instance : The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL. bla bla bla ... Regards. == On Tue, 30 May 2006, Paul DuBois wrote: At 15:46 +0200 5/30/06, Gilles MISSONNIER wrote: Hello I could not find the answer through the online Search the MySQL manual. I run MySQL 4.1 In the 4.1 manual, the reserved words are listed here: http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html DEC is in the list. Identifier-quoting guidance is here: http://dev.mysql.com/doc/refman/4.1/en/legal-names.html In astronomy, RA and DEC are widely used coordinate names. Then I try to add a column named DEC : mysql alter table my_table add dec float; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dec float' at line 1 UPPER case lead to the same error. It seems that the reason is that DEC is a keyword standing for decimal. I do not understand why this cannot be allowed for a column name. Is there a turn around ? This is annoying ; I add to name the column as DECL which is much less meaning full in the astronomy community. thanks, =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question+comment : Re: set DEC as a column name : forbidden
Hello again Paul, 1-Why the error code (1064) can't lead explicitly to the syntax problem ? I'm sorry, I don't understand the question. The mysql command returned the error code [ ERROR 1064 (42000) ] I search in the manual with ERROR 1064, and then get the page : http://dev.mysql.com/doc/refman/4.1/en/error-messages-server.html Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d I cannot figure out what is wrong from this info, whereas error code 1063 or 1065 are far more explicit. Why the error code (1064) is not telling : wrong usage of reserved word or something like this ? that could be linked to a page showing some exemple of what to do ? === 2-Could you tell me how I could go directly to the relevant page ? [ Hard for you to feel like a lambda user in MySQL ] It sounds like you mean, When I have a problem, how can I instantly find the solution? I don't think I can answer that. I'm not so childish : just like to have more pieces, but smaller pieces, ORTHOGONAL, so that each could be adressed with appropriate search : you give me a perfect opportunity to explain my point : the following page DO exist : http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html but http://dev.mysql.com/doc/refman/4.1/en/keyword.html http://dev.mysql.com/doc/refman/4.1/en/keywords.html http://dev.mysql.com/doc/refman/4.1/en/keyword-words.html http://dev.mysql.com/doc/refman/4.1/en/keywords-words.html do not exist. also, why is there a keyword list in the reserved-words.html page ? this is not orthogonal. In what keyword and reserved-words differ ? also, the relevant point for me was : The identifier quote character is the backtick (`): And it is found on the same page telling about - maximum length, - Unicode, - ANSI_QUOTES SQL mode, - default character set - recommendation on naming == Hope not being asking too much. regards. -Gilles Missonnier - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: grant modify, doc on grant.
Thanks Dilipkumar, the syntax works fine mysql grant file on *.* to 'wr'@'localhost'; so the file privilege is for ALL databases. by the way, to allow alter, the syntax is like : mysql grant alter on dr4.* to 'wr'@'localhost'; here the alter privilege is specific to a database, on a column in a database. If this is explained in the doc, I would like to know which keyword I should give to have a fast answer, through Search the MySQL manual: You can try this option by grant file on *.* to [EMAIL PROTECTED] identified by 'db123'; Query OK, 0 rows affected (0.03 sec) For all the Databases. == Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
set DEC as a column name : forbidden
Hello I could not find the answer through the online Search the MySQL manual. I run MySQL 4.1 In astronomy, RA and DEC are widely used coordinate names. Then I try to add a column named DEC : mysql alter table my_table add dec float; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dec float' at line 1 UPPER case lead to the same error. It seems that the reason is that DEC is a keyword standing for decimal. I do not understand why this cannot be allowed for a column name. Is there a turn around ? This is annoying ; I add to name the column as DECL which is much less meaning full in the astronomy community. thanks, =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant modify, doc on grant.
Thank you Sheeri for answering, I guess this syntax works for you, but for me NO, this DO NOT work ( I run MySQL 4.21 , on Linux Debian sarge ) mysql GRANT FILE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES the error code make no sense to me ( I tried various combination of ' ) # Error: 1221 SQLSTATE: HY000 (ER_WRONG_USAGE) Message: Incorrect usage of %s and %s It make no sense because the SAME usage of %s works fine in one command, not in the other command (the one for file privilege). I am running Mysql as root, I can create a new user, but not with the file (as you can read hereafter). mysql GRANT SELECT, INSERT, UPDATE, CREATE , FILE ON dr4.* to 'moi'@'localhost' IDENTIFIED BY 'the_passwd'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql GRANT SELECT, INSERT, UPDATE, CREATE ON dr4.* to 'moi'@'localhost' IDENTIFIED BY 'the_passwd'; Query OK, 0 rows affected (0.00 sec) mysql GRANT FILE ON dr4.* to 'moi'@'localhost' IDENTIFIED BY 'the_passwd'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES - On Fri, 26 May 2006, sheeri kritzer wrote: GRANT FILE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd'; It won't set up a new account, just add the privilege for you. -Sheeri On 5/26/06, Gilles MISSONNIER [EMAIL PROTECTED] wrote: Hello How to set FILE privilege enable to an already defined user ? It seems that I have to read the all manual for that. I cannot find an example in the on line manual. thanks. - Gilles - - I will end up running mysql as root. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant modify, doc on grant.
Hello How to set FILE privilege enable to an already defined user ? It seems that I have to read the all manual for that. I cannot find an example in the on line manual. thanks. - Gilles - - I will end up running mysql as root. 1-I set up an user mysql GRANT SELECT, INSERT, UPDATE, CREATE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd'; 2- It works fine : mysql select my_item from my_table ; give it fine. 3- Now I want to select and put the results into a file : mysql select my_item from my_table into outfile'/tmp/my_outfile'; ERROR 1045 (28000): Access denied for user 'wr'@'localhost' (using password: YES) 4- So I go the online doc , look for outfile keyword and I find that I must have FILE privilege enabled. So on the online doc, I look for the syntax for FILE privilege enable 5- hu then I decide to run mysql client as root (of mysql) the same command as above (-3) works fine A note to the manual writer : If you want that more people use database, do not make tons : just a tree-like doc. otherwise most people will continue to consider that grep into a file is fine for database. =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
hello, thank you for your answer, but this DO NOT work for me. I use MySQL 4.0.24 [ Linux Debian sarge stable ]. the FULL argument displays ONLY the Privileges, NOT the Comment, neither Collation. mysql CREATE TABLE a_table (a_column CHAR(30) COMMENT 'commentaire'); Query OK, 0 rows affected (0.03 sec) mysql SHOW FULL COLUMNS FROM a_table; +--+--+--+-+-+---+-+ | Field| Type | Null | Key | Default | Extra | Privileges | +--+--+--+-+-+---+-+ | a_column | char(30) | YES | | NULL| | select,insert,update,references | +--+--+--+-+-+---+-+ 1 row in set (0.03 sec) It seems this is a feature available for a more recent release. Is this right ? On Wed, 19 Apr 2006, Gabriel PREDA wrote: It is: SHOW FULL COLUMNS FROM a_table You will get 2 extra columns: - Privileges (showing the privileges of the user for that column) - Comment (showing a per column comment) When creating a table you can add a comment using COMMENT keyword: CREATE TABLE a_table ( a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' ); Is this... what you needed ? -- Gabriel PREDA Senior Web Developer =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
describe table : comment (some progress done, but...)
hello, I run MySQL 4.1.11 [ available for Linux Debian stable ]. and now, ok, the following command display 9 columns : mysql SHOW FULL COLUMNS FROM a_table; Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment BUT now I would just display 2 columns : Field | Comment and ALSO modify the Comment column after it has been created ; the alter syntax do not seem to be the good way, I cannot figure out how to do that ... If the syntax is described somewhere in the documentation, well... it is somehow burried deep... cheers =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
describe table : improvement
Hello I run MySql 4.0.24 [ the release for the Debian stable Linux ]. when I do a describe a_table, it displays the 6 following columns : Field - Type - Null - Key - Default - Extra It would be nice if I could have a 7th column for a comment that could be used to describe the meaning of a field. Extra is to be used for other info. I could make a table especially for this purpose : create table comment (field varchar, comment varchar); but this will end into inconsistency at last [ 2 times the same field ]... Any ideas ? cheers. = Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
describe table : improvement ?
Hello when I do a describe a_table, it displays : | Field | Type | Null | Key | Default | Extra | I would like to have a Comment that would show the meaning of a field. Extra is to be used for othe info. I looked around at : http://se2.php.net/manual/en/function.mysql-field-name.php but could not figure out how to do what I want. I could make a table especially for this purpose : create table comment (field varchar, comment varchar); but this will end into inconsistency at last [ 2 times the same field ]... I run MySql 4.0.24 Any ideas ? cheers my-folks ! = Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I agree totaly to what Sujay Koduri writes : http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how do I select among 2 columns ?
Hello, a simple question : I have a table like this : name, email_1, email_2 and I want to display in a web page name, email_1 if it exists, or email_2 if email_1 do NOT exist. of course I can do this inside a php script, by checking the content of the columns, BUT I had like to get the values to display directly from mysql. An idea ? thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help to display values but only when NOT NULL.
Hello ! what I want to do is : display all the NOT NULL fields of 1 row, this row being selected with a key [ for example : tabID.login='john' ] . I DO NOT KNOW IN ADVANCE WHICH FIELDS ARE SUPPOSED NOT NULL, since it varies along with the key [ tabID.login='jack' would give other NULL fields ] and when I do : mysql select * from tabID where tabID.login='john' ; I got hundreds of fields in the same row, and many are NULL. So I do not want to display them, but instead I want to display only the NOT NULL. a priori, the MySQL command for what I want should be something like: mysql select [all colums values that are NOT NULL] from tabID where tabID.login; the following is wrong : mysql select * from tabID where tabID.login='john' where tabID.* IS NOT NULL; So, I need to loop on the result, and check each item returned, and if the item is NOT NULL I display it. I do not know how to do that. any hints ? == Gilles MISSONNIER - Projet Terapix phone : [33] 01 44 32 81 36 http://terapix.iap.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to select only NOT NULL fields ?
Hi ! I need a help on the syntax. My table has over 100 different fields: I want to display only the NOT NULL fields and also the names of these NOT NULL fields. so the simple mysql command : mysql select * from tabID where tabID.login='john'; returns a long ... long line, with many items that are NULL. and I would try something like : mysql select * from tabID where tabID.login='john' and tabID.* NOT NULL; but the syntax is wrong ... the point is : how to use the * or a wild card ? my MySQL version is 3.22.22 on DIGITAL osf1 cheers, == Gilles MISSONNIER - Projet Terapix phone : [33] 01 44 32 81 36 http://terapix.iap.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
