MySQL 4.1.1 create table : why negatives values need quotation marks?
Hi. First, sorry, if my problem is know or documented. I have made a rapid search on the MySQL site, but without success. Here is my problem : the following 'create table' command refuses to execute on 4.1.1, if -1 is not put between a quottation mark: create table testTable (name varchar(10), age smallint default -1); If the value -1 is changed into '-1', the command works. Is this the correct behaviour in 4.1.1 ? An application here (written by a colleague) was using numbers without quotation marks as default values in the create command. The application worked untill today. Before he updates the code, I would like to have more precisions. Following is the output of the create commande with a 4.1.1 and a 4.0.3 MySQL server. mysql select version(); +--+ | version()| +--+ | 4.1.1-alpha-standard | +--+ 1 row in set (0.00 sec) mysql create table testTable (name varchar(10), age smallint default -1); 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 '-1)' at line 1 mysql select version(); ++ | version() | ++ | 4.0.3-beta-max | ++ mysql create table testTable (name varchar(10), age smallint default -1); Query OK, 0 rows affected (0.01 sec) Does somebody have the same problem ? Any idea ? Thanks in advance. Christophe. -- *** Christophe DIARRA Institut de Physique Nucleaire 15, Rue Georges Clemenceau Bat 102 - S2I 91406 ORSAY Cedex Tel: (33) 1 69 15 65 60 Fax: (33) 1 69 15 64 70 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 : curious privilege problems (grant, use, show databases)
Hi. Upgrading to 4.1.1 from 4.1.0 seems to solve my problem. There is no more (at the moment) curious privileges problems. Everything seem OK. Thanks to MySQL. N.B.: Please se the history of the problem bellow. Christophe. On Tue, 2 Dec 2003, Sergei Golubchik wrote: Hi! On Nov 25, Christophe DIARRA wrote: Hello. MySQL 4.1.0 is not respecting the registered privileges. ... show databases displays an incorrect list of databases. Each user lists a database it should not, and doesn't show all the databases it should. Same thing with 'use database' : the access is denied for some databases for which the user s granted 'all privileges'. Sorry, I don't have any idea so far :( Some things you can do: 1. upgrade to 4.1.1 (should be out very soon) and try if the bug dissapears 2. try to create a complete repeatable test case that I can use to repeat this behaviour and submit it to bugs.mysql.com. Then the bug will be fixed asap. Regards, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 : curious privilege problems (grant, use, show databases)
Hello.
MySQL 4.1.0 is not respecting the registered privileges.
Before I switch back to the last stable version, I would like to know if
there is a workaround to my problem or if other persons have met the
same problems. Following is the description of the troubles.
I have installed MySQL 4.1.0-alpha-standard on Linux (RH 7.3 and RH 9).
I installed it from scratch using the MySQL tarball. After the installation,
I transfered by 'tar' the 4.03 databases (MyISAM and InnoDB) into the
'data' directory of MySQL4.1. I didn't transfer the MySQL privileges
database mysql (mysql.user, mysql.db, etc)'. So I am working with the
privileges 'format' of 4.1 and mysql_fix_privilege_tables is not needed.
I use MySQL 4.1 because of the subselect feature.
I have 7 databases and 2 users plus root. For each database, I grant
all privileges to the user for all host ('%'). Each user always use the same
password for all the databases.
show databases displays an incorrect list of databases. Each user lists a
database it should not, and doesn't show all the databases it should.
Same thing with 'use database' : the access is denied for some databases
for which the user s granted 'all privileges'.
An example from the client:
===
[EMAIL PROTECTED]64 mysql -p -u archive -h dbserver
Enter password:
mysql select user();
+---+
| user()|
+---+
| [EMAIL PROTECTED] |
+---+
mysql show grants for archive;
| Grants for [EMAIL PROTECTED]
...
GRANT USAGE ON *.* TO 'archive'@'%' IDENTIFIED BY PASSWORD '*blabla...'
| GRANT ALL PRIVILEGES ON `dbancien`.* TO 'archive'@'%'
| GRANT ALL PRIVILEGES ON `dbrecent2`.* TO 'archive'@'%'
| GRANT ALL PRIVILEGES ON `dbcourant`.* TO 'archive'@'%'
| GRANT ALL PRIVILEGES ON `dbcourant`.* TO 'archive'@'%'
mysql use cctest
Database changed == should not be possible (should not work) !
mysql use dbcourant == works according to the privileges
Database changed
mysql use dbancien
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'dbancien'
== should work, because all privileges are granted
mysql show databases;
+---+
| Database |
+---+
| cctest| === 'dbancien' should be displayed instead of cctest
| dbcourant |
| dbrecent1 |
| dbrecent2 |
+---+
On the server now:
==
Following are some coloumns of the defined privileges :
[EMAIL PROTECTED] data]# mysql -p -u root
Enter password:
mysql select version();
+--+
| version()|
+--+
| 4.1.0-alpha-standard |
+--+
1 row in set (0.00 sec)
mysql use mysql
mysql select user,host from user;
+-+---+
| user| host |
+-+---+
| archive | % |
| ccsql | % |
| root| dbserver |
| root| localhost |
+-+---+
4 rows in set (0.00 sec)
mysql select user,host,db from db;
+-+--++
| user| host | db |
+-+--++
| ccsql | %| cctandem |
| ccsql | %| cctandemOnline |
| ccsql | %| cctest |
| archive | %| dbancien |
| archive | %| dbcourant |
| archive | %| dbrecent1 |
| archive | %| dbrecent2 |
+-+--++
7 rows in set (0.00 sec)
Any idea ?
Thanks in advance.
Christophe.
--
***
Christophe DIARRA
Institut de Physique Nucleaire
15, Rue Georges Clemenceau
Bat 102 - S2I
91406 ORSAY Cedex
Tel: (33) 1 69 15 65 60
Fax: (33) 1 69 15 64 70
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fastest way to get the number of rows in a table ?
Hi. I need to know the number of rows in a table. select count(*) from a_table works fine but it is slow when the table is big. I am trying instead show table status like 'a_table'. The column 'Rows' should give me the number of rows in the table 'a_table'. This works fine for most of the tables but with one table, the value displayed is wrong. Demonstration (the database is quiet) : mysql select count(*) from photo\G *** 1. row *** count(*): 700 1 row in set (0.00 sec) mysql show table status like 'photo'\G *** 1. row *** Name: photo Type: InnoDB Row_format: Dynamic Rows: 603700 != 603 Avg_row_length: 135 Data_length: 81920 Max_data_length: NULL Index_length: 98304 Data_free: 0 Auto_increment: 19314 Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: InnoDB free: 2940928 kB; (iddatephoto) REFER dbcourant/datephoto(iddatephoto) ON DELETE CASCADE 1 row in set (0.01 sec) How do you explain the difference between count(*): 700 and Rows: 603 ? About the table photo: mysql show create table photo\G *** 1. row *** Table: photo Create Table: CREATE TABLE `photo` ( `idphoto` bigint(20) NOT NULL auto_increment, `idpropriete` int(11) NOT NULL default '0', `iddatephoto` bigint(20) NOT NULL default '0', `valeur` double default NULL, `prefixe` varchar(16) default NULL, `min` double default NULL, `max` double default NULL, PRIMARY KEY (`idphoto`), UNIQUE KEY `idpropriete` (`idpropriete`,`iddatephoto`), KEY `iddatephoto` (`iddatephoto`), FOREIGN KEY (`iddatephoto`) REFERENCES `dbcourant.datephoto` (`iddatephoto`) ON DELETE CASCADE ) TYPE=InnoDB 1 row in set (0.01 sec) mysql show create table datephoto\G *** 1. row *** Table: datephoto Create Table: CREATE TABLE `datephoto` ( `iddatephoto` bigint(20) NOT NULL auto_increment, `datephoto` datetime NOT NULL default '-00-00 00:00:00', `idarchive` bigint(20) NOT NULL default '0', `jamaiseffacer` char(1) NOT NULL default '', PRIMARY KEY (`iddatephoto`) ) TYPE=InnoDB 1 row in set (0.00 sec) Thanks in advance for your help. Christophe. *** Christophe DIARRA Institut de Physique Nucleaire 15, Rue Georges Clemenceau Bat 102 - S2I 91406 ORSAY Cedex Tel: (33) 1 69 15 65 60 Fax: (33) 1 69 15 45 03 (33) 1 69 15 64 70 E-mail: [EMAIL PROTECTED] *** - 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
Storing/retrieving long or very big double values
Hi. I have some problems to retrieve the doubles I write in my tables. I use MySQL server 4.0.3-beta-max. I have not found the solution of my problem on the mailing-lis archive. Following is the problem: mysql create table double_test (d double); mysql insert into double_test values(1.7976931348623157E308); mysql insert into double_test values(0.10012210012209899); mysql select * from double_test; +---+ | d | +---+ | 1.79769313486232e+308 | == truncation happened | 0.100122100122099 | == truncation happened +---+ The numbers are truncated. Why and how to avoid it ? I would like store and retrieve the double values without modification. In my Java program when I read back the data, 1.79769313486232e+308 is considered as 'Infinity' because after the truncation and rounding, the value becomes bigger than the maximum double value allowed (1.7976931348623157E308 under Java). When I use double(M,D) instead of double in the table creation, the insertion of 1.7976931348623157E308 leads to the value 'inf' when I read back the value by typing 'select * from ouble_test'). Any idea or experience to share. Thanks in advance, Christophe. *** Christophe DIARRA Institut de Physique Nucleaire 15, Rue Georges Clemenceau Bat 102 - S2I 91406 ORSAY Cedex Tel: (33) 1 69 15 65 60 Fax: (33) 1 69 15 45 03 (33) 1 69 15 64 70 E-mail: [EMAIL PROTECTED] *** - 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
