Re: Get fields in one field, separated by comma's
Klaas Geldof [EMAIL PROTECTED] wrote: I have a question about MySQL queries. First I will explain the situation, then I will provide an example. Situation: I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' contains data about offers, table 'contact' about contacts. Table 'offer_contact' tells which contacts are associated with which offers (since offers can have multiple contacts associated with it and vice versa). Now I want to run a query so I get a row for each offer and in the result there's a field 'offer_contacts' which contains all the names of the contacts associated with the offer on that row separated by comma's. Example: Table 'offer' (offer_id - offer_title): 1 - Test 2 - Also test Table 'contact' (contact_id - contact_name): 1 - John 2 - Anna Table 'offer_contact' (offer_id - contact_id): 1 - 1 1 - 2 2 - 1 Wanted result of query (offer_id - offer_name - offer_contacts): 1 - Test - John, Anna 2 - Also test - John Is this possible? GROUP_CONCAT() function may help. This function is available from version 4.1: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run MySQL with ANSI mode
yau meileng [EMAIL PROTECTED] wrote: I try to run MySQL with ansi mode but failed. This was what I did: mysql SET GLOBAL sql_mode='ansi'; ERROR 1064: You have an error in your SQL syntax near 'sql_mode='ansi'' at line 1 What version do you use? The above syntax is supported since 4.1.1. Why I can't manage to change to ansi mode? Start MySQL server with --ansi option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run MySQL with ANSI mode
Friday, June 04, 2004, 1:24:35 PM, you wrote: ym Hi Victoria, ym Am running mysql Ver 11.18 Distrib 3.23.54, for ym redhat-linux-gnu (i386) ym Yes. In the documentation it asked us to specify ym option --ansi.I try to type this at the shell: ym $ mysql --ansi ym But not working. Because you try to start mysql client with --ansi option, not the server. Run mysqld with the above option or put to the my.cnf file: [mysqld] ansi ym --- Victoria Reznichenko ym [EMAIL PROTECTED] wrote: yau meileng [EMAIL PROTECTED] wrote: I try to run MySQL with ansi mode but failed. This was what I did: mysql SET GLOBAL sql_mode='ansi'; ERROR 1064: You have an error in your SQL syntax near 'sql_mode='ansi'' at line 1 What version do you use? The above syntax is supported since 4.1.1. Why I can't manage to change to ansi mode? Start MySQL server with --ansi option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id() value not updated
paqogomez [EMAIL PROTECTED] wrote: I am trying to build a stored procedure in v. 5. This is what I have so far. delimiter | create procedure get_id(out oid int) begin insert into mercury.merchant (name) values(null); select last_insert_id() into @mid; insert into mercury.customers( address1 ) values (null); select last_insert_id() into @cid; insert into mercury.item_information( description ) values (null); select last_insert_id() into @iiid; insert into mercury.fee_information ( delivery_fee ) values (null); select last_insert_id() into @fiid; insert into mercury.orders ( customer_id, merchant_id, item_information_id, fee_information_id ) values ( @cid, @mid, @iiid, @fiid ); select last_insert_id() into oid; end | My problem is that last_insert_id() is not updated for each insert statement, only on the connection. The documentation says something about using insert ignore, but I couldnt get this to give me any different results. Is there any way to do what I want here and still have the procedure be transaction safe? This issue is related to the known bug. It's already fixed: http://bugs.mysql.com/bug.php?id=3117 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unexpected subquery behaviour
Jaime [EMAIL PROTECTED] wrote: I'm using subqueries in MySQL 4.1.1 alpha. I use three subqueries and then want to get those combinations matching a given constraints. It is a very simple task, but I get less results than I expected. In fact, I tried to execute the same query in ORACLE and I get 23 results (in MySQL I only get 9). I wanted to know whether subqueries are not fully developed in this alpha version or it is because MySQL behaves differently. Here is the query: SELECT i1.id_document , i1.id_item , i2.id_item FROM (SELECT DISTINCT j.* FROM item j, itemtriplet it WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word 0 AND it.id_triplet IN (SELECT t.id_triplet FROM triplet t, unit u WHERE u.alphacode_unit = 'N' AND u.name_level = 'MORPHO-SYNTACTIC' AND t.id_unit = u.id_unit) AND it.id_item = j.id_item AND it.is_revised = 0 ) AS i1, (SELECT DISTINCT j.* FROM item j, itemtriplet it WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word 0 AND it.id_triplet IN (SELECT t.id_triplet FROM triplet t, unit u WHERE u.alphacode_unit = 'N' AND u.name_level = 'MORPHO-SYNTACTIC' AND t.id_unit = u.id_unit) AND it.id_item = j.id_item AND it.is_revised = 0 ) AS i2 WHERE i1.paragraph = i2.paragraph AND i1.sentence = i2.sentence AND 3 = i2.token - i1.token Could you test this query on version 4.1.2? If you get unexpexted result on v4.1.2 create a test case (structure of your tables and data), please. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unexpected subquery behaviour
Monday, May 31, 2004, 12:28:20 PM, you wrote: J I'm sorry, but I'm using Windows. There is no 4.1.2 version available yet Ok. Could you create a repeatable test case? You can upload structure of tables and data for testing to ftp://ftp.mysql.com/pub/mysql/upload/ and let me know a file name. J - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger and StoreProc Examples
Carlos J Souza [EMAIL PROTECTED] wrote: Hello ALL, I need a examples of stored procedures and triggers in mysql 5.0. Please send me all sintaxes of this Triggers are not supported in v5.0. Example of stored procedure you can find in the manual at: http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid newbie question - adding comments for columns tables?
Randy Burgess [EMAIL PROTECTED] wrote: Apologies for what is a pitifully simple question from someone who has just started using MySQL 4.0. I've looked in the documentation and elsewhere, but can't find the exact syntax for adding comments to columns when creating a table. My attempts at guessing the syntax so far have failed. Tips, hints, examples? Thanks-- Comment on the column level is supported from version 4.1. CREATE TABLE t1(id int COMMENT 'comment'); -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump format.
Josef Karthauser [EMAIL PROTECTED] wrote: In the old days of mysql (version 3.x) the mysqldump command would produce one text line per database row, but since then it appears to have been replaced with an extended insert format which is more efficient for getting data back into the database. The old format was good however for running diff on so that it was easy to just dump the database and then compare it to a previous dump using diff -u. Only the rows that had changed would show up. With the new format that doesn't work because many rows are now on the same textual line. Is there any way to pursuade the mysqldump from 4.1.x to use a single line per row insert format? I've not seen anything in the manual page, and I find it a loss of functionality. Use --skip-extended-insert or --skip-opt options of mysqldump. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Request from a Software Programmer
naresh bhalala [EMAIL PROTECTED] wrote: Respected Sir/Madam, I have download the MySQL4.0 from www.dev.mysql.com and file is :mysql-standard4.0.20-pc-linux-i686.tar.gz. I have installed it as bellow: #gunzip -9c standard4.0.20-pc-linux-i686.tar.gz | tar -xvf - and all files r extraced in folder:standard4.0.20-pc-linux-i686 Now i've run /standard4.0.20-pc-linux-i686.tar.gz/bin/mysql BUT ITS GIVING ERROR: ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.socket' (2) pl reply to this mail and solve my prob. Is MySQL server running? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Password on Latest MySQL, etc
Carlos Sunden [EMAIL PROTECTED] wrote: The mysql client mysql-devel packages did it. Was able to change the password which I did with a very easy one. Now when I want to change the existing password it fails. It does not let me. You must specify old password when you set a new one: mysqladmin -uuser_name -pold_password password new_password -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump format.
Josef Karthauser [EMAIL PROTECTED] wrote: On Fri, May 28, 2004 at 03:05:53PM +0300, Victoria Reznichenko wrote: Josef Karthauser [EMAIL PROTECTED] wrote: In the old days of mysql (version 3.x) the mysqldump command would produce one text line per database row, but since then it appears to have been replaced with an extended insert format which is more efficient for getting data back into the database. The old format was good however for running diff on so that it was easy to just dump the database and then compare it to a previous dump using diff -u. Only the rows that had changed would show up. With the new format that doesn't work because many rows are now on the same textual line. Is there any way to pursuade the mysqldump from 4.1.x to use a single line per row insert format? I've not seen anything in the manual page, and I find it a loss of functionality. Use --skip-extended-insert or --skip-opt options of mysqldump. Yes, that's the ticket. Shouldn't this be documented in the manual page for mysqldump? (And in the dig mysql manual documentation?) It's documented: --opt This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. As of MySQL 4.1, --opt is on by default, but can be disabled with --skip-opt. To disable only certain of the options enabled by --opt, use their --skip forms; for example, --skip-add-drop-table or --skip-quick. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange behaviour in foreign keys
[EMAIL PROTECTED] wrote: I've been running MySQL 4.0.x on a RH9 machine for some months and everything was fine. I could create tables of type InnoDB and define FOREIGN KEY's all was well. A few days ago it does not let me create tables with foreign keys anymore it says something like unable to create /databasename/tablename... If I remove the FOREIGN KEY constraint it accepts de table creation. You have incorrect foreign key definition. Use SHOW INNODB STATUS command to see more detailed error message. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error restoring and dumping
Fajar Priyanto [EMAIL PROTECTED] wrote: Hi all, I'm trying to move my Mambo (content management) database from one server into another. In the old server, the mysql version is 4.0.13, while in the new one it's 4.0.15. The error was when restoring: ERROR 1064 at line 141: 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 'option varchar(50) DEFAULT '' NOT NULL, ordering int(11) unsi These are the corresponding lines: # # Table structure for table `mos_components` # CREATE TABLE mos_components ( id int(11) NOT NULL auto_increment, name varchar(50) NOT NULL default '', link varchar(255) NOT NULL default '', menuid int(11) unsigned NOT NULL default '0', parent int(11) unsigned NOT NULL default '0', admin_menu_link varchar(255) NOT NULL default '', admin_menu_alt varchar(255) NOT NULL default '', option varchar(50) NOT NULL default '', ordering int(11) unsigned NOT NULL default '0', admin_menu_img varchar(255) NOT NULL default '', iscore tinyint(4) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=3DMyISAM; Is there any syntax that I should fix? OPTION is a reserved word in MySQL: http://dev.mysql.com/doc/mysql/en/Reserved_words.html Use backticks to quote column names: `option`. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Password on Latest MySQL, etc
Carlos Sunden [EMAIL PROTECTED] wrote: Installed the MySQL-server-4.0.20-0.i386.rpm on a RHL8 system Initialized the grant tables and then did: /usr/bin/mysqladmin -u rt password ACTUALPASSWORD And got: -bash: /usr/bin/mysqladmin: No such file or directory What gives mates? I had used the exact command before in another RHL8 system but it was a tar.gz package, I think. How can I changed or apply a password to mysql? Also, are there any other post-installation things to do after a Mysql installation? Newbie here. I'd like to know how to make Mysql secure more efficient. Install MySQL-client package. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Turkish
Mazhar Bilen [EMAIL PROTECTED] wrote: How can I add Turkish support to the latest version of MySQL? I tried many times, but I can't do this. latin5 is used for turkish language. Start MySQL server with --default-character-set=latin5 option or put in the my.cnf: [mysqld] default-character-set=latin5 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up InnoDB MySQL DB
ColdFusion Lists [EMAIL PROTECTED] wrote: we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? You can use mysqldump utility that comes with MySQL distribution: http://dev.mysql.com/doc/mysql/en/mysqldump.html There is also non-free InnoDB Hot Backup program: http://www.innodb.com/order.php Some info about backing up InnoDB database find at: http://dev.mysql.com/doc/mysql/en/Backing_up.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement returning to many answers
Jonas Lind?n [EMAIL PROTECTED] wrote: Hello, I am running MySQL5.0.0 on my OpenBSD system. When I excute this query I get 153 hits. SELECT b.batch_id, b.date, b.creator, b.comments FROM cd_batch as b; But when I add this table to the where clause i get 5831 hits SELECT b.batch_id, b.date, b.creator, b.comments FROM cd_batch as b, cd_donor as d; Why does this happend? I thought that these two queries would return the exact same amout of rows. Is there something I have forgotten or is the debug release of 5.x.x errornous? You got in the result set cartesian product of cd_batch and cd_donor tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.20 WINDOWS VERSION
Massimo Petrini [EMAIL PROTECTED] wrote: When it will be avalaible to download ? It will be available in a few days. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set wait_timeout
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi, i have problem with automatic setting of variable wait_timeout. mysql version 4.0.20 (and 4.0.18) on slackware linux. i've tried to set it in several ways: 1) in startup script as a parameter to mysql_safe: -O wait_timeout=30 2a) in configuration file /etc/my.cnf, section [mysqld]: set-variable=wait_timeout=30 2b) in configuration file /etc/my.cnf, section [mysqld]: wait_timeout=30 3) from the shell (i've planed to place it into the startup script after mysqld_safe start): mysql -e 'set wait_timeout=30' nothing worked, i got no error message and the value of wait_timeout stood default: 28800. when i run 'set wait_timeout=30;' as privileged user in interactive mysql session (running 'mysql'), the value changes as i wish. what should i do to set the variable at startup? If you check wait_timeout value using mysql command-line client, it takes value for 'wait_timeout' from 'interactive_timeout' variable which is 28800 by default. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: R: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: I tested it on with my test data for others PARTITIONED_*_* tables on v4.1.2 and SELECT worked well. v4.1.2? Where can I get it? You can install it from development source tree: http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html or wait when binaries will be available. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Re: Re: set wait_timeout]
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote: If you check wait_timeout value using mysql command-line client, it takes value for 'wait_timeout' from 'interactive_timeout' variable which is 28800 by default. thanks, now i see. this behaviour is rather strange, isn't it? why are there two different variables then? i thought that wait_timeout is for non-interactive and interactive_timeout for interactive clients. when i run: mysql -e 'show variables;' then i get right value (=30), as i set it on startup. when i start mysql command line client and run: show variables; then i get value for interactive_timeout (it's default =28800). } this is what you pointed out. but when i run: mysql -e 'set wait_timeout=26600;' mysql -e 'show variables;' then i get old values: wait_timeout=30 interactive_timeout=28800 Because you set session variable and SHOW VARIABLES display values for global variables. Use GLOBAL keyword in the SET statement. shell mysql -e SET @@GLOBAL.wait_timeout=26600 this behaviour is quite odd and i don't see a way how to set wait_timeout for non-interactive clients from interactive session. (i suppose that mysql command line client is non-interactive when it runs with the -e parameter) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: R: R: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: v4.1.2? Where can I get it? You can install it from development source tree: http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html or wait when binaries will be available. I'll wait for binaries... At the moment even a query like (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) UNION (SELECT * FROM REGULAR_copy) where REGULAR_copy is an empty table crashes mysql. I wasn't able to repeat in on v4.1.2: mysql CREATE TABLE REGULAR_copy (id int); Query OK, 0 rows affected (0.12 sec) mysql (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy) UNION - (SELECT * FROM REGULAR_copy); Empty set (0.00 sec) Test it on 4.1.2 and let me know if these queries will crash MySQL server. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: R: R: R: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: Test it on 4.1.2 and let me know if these queries will crash MySQL server. Mmh, I'm afraid I will have to wait for binaries... When do you think 4.1.2 binaries will be available? Probably it will be out next week. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: Could you provide structure of tables and some data for testing? How? I tried to post a couple of zip files (2 mails, 10k each) but it doesn't work. Don't send attachments to the mailing list. You can upload file to the ftp://support.mysql.com/pub/mysql/secret/ and tell a file name or enter a bug report at http://bugs.mysql.com/. Some data (and schema) I tested it on with my test data for others PARTITIONED_*_* tables on v4.1.2 and SELECT worked well. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help uploading
sbraun [EMAIL PROTECTED] wrote: I have many tables to upload, but each time I try I get a syntax error 1064. What format should my table be in? I've been attempting to upload tab delimited text, but I can't get past an error in line 1. I can't see any errors. How did you try to upload data? Which statement did you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fully compliant GIS
Trevor Price [EMAIL PROTECTED] wrote: What is the roadmap for fully compliant GIS? The OpenGIS specification defines the following functions, which MySQL does not yet implement. They should appear in future releases. When implemented, they will provide full support for spatial analysis, not just MBR-based support. GIS isn't high priority. Some features are planned for 5.0. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query string too long?
Leonardo Francalanci [EMAIL PROTECTED] wrote: I'm using MySql version 4.1.1. When I issue a query like (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHE RE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PARTITIONED2_1 AS PARTITIONE D2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, PARTITIONED2_1 AS PARTITI ONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED, PARTITIONED2_1 AS PART ITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIONED, PARTITIONED2_1 AS P ARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTITIONED, PARTITIONED2_1 A S PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_1 AS PARTITIONED, PARTITIONED2_ 2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_1 AS PARTITIONED, PARTITIONE D2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_1 AS PARTITIONED, PARTITI ONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_1 AS PARTITIONED, PART ITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_1_2 AS PARTITIONED, P ARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_2_2 AS PARTITIONED , PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_3_2 AS PARTITIO NED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_4_2 AS PARTI TIONED, PARTITIONED2_2 AS PARTITIONED2 WHERE PARTITIONED2.ID=PARTITIONED.ID1) ORDER BY 1,2,3,4,5 (sorry for the format, but it is what I get from telnet) mysql crashes. I started mysql with --max_allowed_packet=100M, but nothing changes: it keeps crashing. Should I change a variable? Which? Could you provide structure of tables and some data for testing? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges
[EMAIL PROTECTED] wrote: I am running MySQL 4.1.1-alpha-standard on RH Linux 9. I've found strange problem with privileges: mysql grant reload on *.* to 'fabackup'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql grant create, insert, drop on mysql.ibbackup_binlog_marker to 'fabackup'@localhost; Query OK, 0 rows affected (0.01 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | | GRANT INSERT, CREATE, DROP ON `mysql`.`ibbackup_binlog_marker` TO 'fabackup'@'localhost' | +--+ 2 rows in set (0.00 sec) mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.01 sec) After shutting down the database with: [EMAIL PROTECTED] init.d]# mysqladmin -p shutdown Enter password: 040517 13:32:44 mysqld ended and restarting it with: [EMAIL PROTECTED] init.d]# /etc/rc.d/init.d/mysql start [EMAIL PROTECTED] init.d]# Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED] init.d]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | +---+ 1 row in set (0.00 sec) The create, insert, drop privilege on mysql.ibbackup_binlog_marker has disappeared but still reported in mysql.tables_priv table.??!!! mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.00 sec) mysql exit Bye When logon as fabackup cannot even access mysql database. [EMAIL PROTECTED] init.d]# mysql -ufabackup Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql ERROR 1044 (42000): Access denied for user: 'fabackup'@'localhost' to database 'mysql' mysql Am I doing something wrong here, or this is a bug. I've searched bug database, but could not find this reported. Any help will be apprecieated. It's a known bug. Fixed in version 4.1.2: http://bugs.mysql.com/bug.php?id=2546 http://dev.mysql.com/doc/mysql/en/News-4.1.2.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB case sensitive collation
Matt Mastrangelo [EMAIL PROTECTED] wrote:
How can an InnoDB table be created with case sensitive collation? The
example below creates two identical tables, one MyISAM and the other
InnoDB. The InnoDB fails when inserting primary keys that differ in case
only. What am I doing wrong?
Which version do you use?
Worked fine for me on 4.1.2:
mysql INSERT INTO `table_02` VALUES ('victoria'), ('Victoria');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM `table_02`;
+--+
| tst_key |
+--+
| Victoria |
| victoria |
+--+
2 rows in set (0.00 sec)
Thanks.
drop database test;
create database test default character set latin1 default collate
latin1_general_cs;
CREATE TABLE `table_01` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=MyISAM DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
CREATE TABLE `table_02` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=InnoDB DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing integrity of Foreign Keys
Zachary Agatstein [EMAIL PROTECTED] wrote: Well, in order to enforce referential integrity, I converted the tables to InnoDB. But referential integrity is still not being enforced. I use mysql version 3-23-54-nt, and the tables are generated through the use of dbdesigner (ver. 4.0.5.6 beta). Here is a snippet of a create script: CREATE TABLE Types ( Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(Event_type) ) TYPE=InnoDB; CREATE TABLE Events ( event_id INTEGER NOT NULL, Types_Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(event_id), INDEX Events_FKIndex1(Types_Event_type), FOREIGN KEY(Types_Event_type) REFERENCES Types(Event_type) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; == Is there anything still wrong with this? CREATE TABLE statements look like Ok, but you should run mysqld-max or mysqld-max-nt servers on Windows if you want to use InnoDB tables and specify innodb_data_file_path variable: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html Note: ON UPDATE CASCADE is supported from version 4.0.8. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing integrity of Foreign Keys
Zachary Agatstein [EMAIL PROTECTED] wrote: A very simple question: If I have a table A with PRIMARY KEY K, and table B which has a column C defined as a FOREIGN KEY F referencing table A.K, then, I would expect, C can only take a value from those already existing in table A column K. So, let's assume, for simplicity's sake, table A has only 1 column K which is integer, and row are: 1, 3, 6, 7. Would it be logical to assume that the column C of the table B cannot have a value of 2, because no such value exists in A.K? Yes. For some reason, in my database no such rule seems to be enforced. Do you know why? Check that type of your tables is InnoDB. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT table1 SET col1 = table2.col1
P. Hill [EMAIL PROTECTED] wrote: Why when there is a figurae, figurae2 table does the follow give an error? INSERT INTO figurae2 SET id = figurae.id; The error is: ERROR 1109 at line 35: Unknown table 'figurae' in field list Line 35 is the Insert line shown. There are no following lines in the file. I cut and pasted figurae from mysqlcc just to make sure I have the right (Latin) spelling. The following placed on the line above in the same file above this INSERT returns a set of ids. SELECT id FROM figurae; I am using mysql (the command line tool). Can anyone tell me what I am doing wrong? Look at INSERT .. SELECT statement: http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Renaming Problem
Deepak Vishwanathan [EMAIL PROTECTED] wrote: I have a table named t1 with a column x1 which is the primary key for that table. There are some other tables that reference this table t1 on x1 with foreign key constraints. I wanted to rename the column x1 for some reason, so, I went ahead and renamed the column x1 to y1. I was able to change it successfully. Then, I wanted to rename the foreign key's column name from x1 to y1 in all referencing tables and I got the error ERROR 1025: Error on rename of './Test/t1' to './Test/#sql2-3c90-4bd' (errno: 121). So, then, I tried dropping the foreign key constraints on the referencing tables with alter table command with the foreign key value that I got from show create table command for those referencing tables...But, the system won't allow me to do that. I understand this problem is because of the foreign key constraintsDoes that mean, I should have dropped the foreign key constraint in referencing tables before I renamed the column x1 to y1 in the base table t1??? Yes, you should drop foreign key constraint first. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filling column
A Z [EMAIL PROTECTED] wrote: Is there a function to fill an integer column in incremental order (1,2,..,n). Cannot use Auto_Increment as the table has another primary key. Auto_increment column isn't necessarily to be primary key, it must be indexed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blocking Selects with LOCK TABLES
Lou Olsten [EMAIL PROTECTED] wrote: According to the docs (http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html) : If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can read from or write to the table. Other threads are blocked. So, I've got two threads going (T1, T2). T1 issues LOCK TABLES transtest WRITE; But when I go to T2, I can still issue: SELECT * FROM transtest; and retrieve all the data. I CANNOT update, so I know the command is at least partially working. As I understand it, I'm supposed to see a message from T2 that says something about This table has been locked with the LOCK TABLES command. It is an InnoDB table, if that matters. Looks like it's a query cache issue. In this case you get result from the cache. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export query to text file ***[SPAM]*** **SPAM HTMLM**
Yingyos [EMAIL PROTECTED] wrote:
I type this command line.
mysql -uuser -N -eselect date_format('2004-02-29','%X') sample2.txt
When i open in text editor,it show that '2004' only.
But i want format display on text file.
mysql select date_format('2004-02-29','%X');
++
| date_format('2004-02-29','%X') |
++
| 2004 |
++
1 row in set (0.00 sec)
How 's to do?
Use --tee/--no-tee options or tee/notee commands of mysql client:
http://dev.mysql.com/doc/mysql/en/mysql.html
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html
For example:
mysql tee file.txt;
Logging to file 'file.txt'
mysql SELECT DATE_FORMAT('2004-02-29','%X');
++
| DATE_FORMAT('2004-02-29','%X') |
++
| 2004 |
++
1 row in set (0.01 sec)
mysql notee;
Outfile disabled.
mysql
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.15 : Foreign Key - ERROR 1005 (errno: 150)
Andy Jefferson [EMAIL PROTECTED] wrote: I've got 2 tables in a circular dependency as follows CREATE TABLE USERDETAILS ( USERDETAILS_ID BIGINT NOT NULL, FORENAME VARCHAR(30) BINARY NULL, SURNAME VARCHAR(30) BINARY NULL, USER_USER_ID_OID BIGINT NULL, PRIMARY KEY (USERDETAILS_ID) ) TYPE=INNODB CREATE TABLE `USER` ( USER_ID BIGINT NOT NULL, DETAILS_USERDETAILS_ID_OID BIGINT NULL, LOGIN VARCHAR(20) BINARY NULL, PASSWORD VARCHAR(12) BINARY NULL, PRIMARY KEY (USER_ID) ) TYPE=INNODB CREATE INDEX USERDETAILS_N49 ON USERDETAILS (USER_USER_ID_OID) This all works fine. I then try to create one of the foreign keys between the 2 tables ALTER TABLE jpox.USERDETAILS ADD CONSTRAINT USERDETAILS_FK1 FOREIGN KEY (USER_USER_ID_OID) REFERENCES jpox.`USER` (USER_ID) MySQL (4.0.15) responds with ERROR 1005: Can't create table './jpox/#sql-5b3_5a.frm' (errno: 150) This works with 4.0.18, yet I need my system to work also on 4.0.15 and earlier. Is there something that I'm missing here with regards to my FK specification ? Worked fine for me on both 4.0.15 and 4.0.18. Check error message with SHOW INNODB STATUS command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'root@localhost'
Phil Ewington - 43 Plc [EMAIL PROTECTED] wrote: Hi All, I have just installed mysql-4.0.18 on my cobalt RaQ4 and for the first time seemed to actually got somewhere! however, my existing PHP scripts failed to connect to localhost as the password has not been set after the install. I ran the following command (obviously seriously misunderstood the docs) mysqladmin -u root password new-password I thought the above syntax would ask me for the password but it didn't now all commands return 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' How do I actually set a password, and more importantly, how do I reset the password for root, and what did I do? Any help will be greatly appreciated. Initially user root doesn't have a password. If you set password you should specify password when you connect to the MySQL server with -p option for command-line clients. For example: shell mysql -uroot -proot_password or shell mysql -uroot -p and then type a password. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
Tucker, Gabriel [EMAIL PROTECTED] wrote: Victoria=20 That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot = during the restore: ERROR 1114 at line 83 in file: = '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' = is full What can I do here? What is the type of 'cur_reject_tk_sum' table? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'drop database' failing silently?
Kevin O' Riordan [EMAIL PROTECTED] wrote: this morning, I had a problem dropping a database from within mysql. mysql drop database mis; Query OK, 0 rows affected (0.00 sec) mysql show databases; +--+ | Database | +--+ | mis | | mysql| +--+ 2 rows in set (0.00 sec) mysql use mis; Database changed mysql show tables; Empty set (0.00 sec) I was logged in as mysql's root user at the time, and had successfully deleted other databases. I also tried dropping the database with 'mysqladmin', which likewise told me the database had been dropped, but didn't actually drop it. The solution I used was to stop mysqld and then rm the database from the filesystem. I'm using mysql version 3.23.58 on redhat 7.3. Apologies if this has been discussed before, but I couldn't find a reference to it in the archive. Elsewhere, I found that someone else has seen this problem[1], but found no answer. If anyone can give any hints as to what I should've done, or what went might've gone wrong at the time, I'd be grateful. Are there any non-mysql files in the directory of the database 'mis'? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documentation on character sets for version 4.0.17
David Jourard [EMAIL PROTECTED] wrote: I got the 4.0.17 documentation but when it discusses character sets it discusses this topic wrt 4.1 Where can I find documentation specifically in regards to the production version on how to work with, store, and search asian character sets specifically Japanese. In version 4.0.17 you should specify character set with --default-character-set option. For Japanese language take a look at sjis and ujis character sets: http://dev.mysql.com/doc/mysql/en/Character_sets.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem (bug?) with LOCATE(substr,str,pos)
Boris Mueller [EMAIL PROTECTED] wrote:
I need to do a LOCATE(substr,str,pos) request where pos must be taken from a
column.
for unknown reason (bug?) it does not work.
I made the following example to ilustrate the problem:
select ERW, locate('xyz', A, 3000) as loc1, locate('xyz', A, ERW) as loc2
from B;
+--+-++
| ERW | loc1| loc2 |
+--+-++
| 2873 | 10363 | 0 |
| 2677 | 18027 | 0 |
| 2459 | 13016 | 0 |
| 2539 |3462 | 0 |
+--+-++
4 rows in set (0.00 sec)
in this example loc2 should have the same result as loc1, but loc2 always
presents 0.
whats going wrong here?
anyone can help?
I have mysql 4.0.18 on redhat 9.0.
ERW is a mediumint(8) unsigned not null
Could you create a test case? I wasn't able to repeat it with my test data.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
Richard A. DeVenezia [EMAIL PROTECTED] wrote: I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? No, but you can use output of SHOW CREATE TABLE command. Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring a db with RI enforced
Lou Olsten [EMAIL PROTECTED] wrote: If I'm using mysqldump to dump a database with referential integrity in place, does MySQL build the information in such a way that the referenced tables are loaded first to avoid invalid inserts into a table with a foreign key in place? I'm trying to find an option for mysqldump, and the closest one I see is --disable-keys, but I'm not sure if that's what I need or not. You can turn off foreign key checks with SET FOREIGN_KEY_CHECKS = 0 command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
Lou Olsten [EMAIL PROTECTED] wrote: Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. You should add the following statement to the beginning of the dump file: SET FOREIGN_KEY_CHECKS = 0; or load dump file like: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE dump_file.sql; mysql SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The MySQL Replication...
Dyego Souza Dantas Leal [EMAIL PROTECTED] wrote: Hello Guys !!! I'm want to solve this problem with the replication : MASTER - SLAVE OF MASTERSLAVE1 - SLAVE OF SLAVE 1SLAVE2 The replication on MySQL supports this schema ? Exists a possibility to use Slave of Slave on Replication ? The Insert is STORED on MASTER and REPLICATED to SLAVE1 and REPLICATED to SLAVE 2 AFTER stored on SLAVE1... This is possible ? Yes. In this case you should start SLAVE1 with --log-slave-updates replication option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore problem between MySQL on Win32 and MySQL on Linux?
[EMAIL PROTECTED] wrote: I performed the backup (mysqldump -u username -p db backup.sql) of a database on a Win32 (4.0.15-max-debug) server in order to restore it on Linux server (4.0.15-9) When I try to restore it on the linux machine (with mysql -u username -p db backup.sql), there is an error saying: ERROR 1064 at line 12748: 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 'Order tinyint(4) default NULL ) TYPE=MyISAM' at line 6 I searched the mailing list but nothing seems to apply to my case. The database has been created with the same name, cmsdb, which has no special characters ORDER is a reserved word in MySQL: http://dev.mysql.com/doc/mysql/en/Reserved_words.html Use -Q (--quote-names) option of mysqldump. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update if insert fails
Andy Ford [EMAIL PROTECTED] wrote: Is there such a statement where, if the insert fails (due to a duplicate record) an update will happen Take a look at REPLACE and INSERT .. ON DUPLICATE KEY UPDATE statements: http://dev.mysql.com/doc/mysql/en/REPLACE.html http://dev.mysql.com/doc/mysql/en/INSERT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to join in a count(*)
Victor Sp?ng Arthursson [EMAIL PROTECTED] wrote: Using mysql version 4.0.x, and would like to know if it is possible to join in a count(*) from a related table which count the number of corresponding posts in this per post in the first table? A little like: table1: +-+--+ | id | name | +-+--+ | 04002 | victor | +-+--+ | 04003 | pierre | +-+--+ table2 +-+--+ | id | relid | +-+--+ | 1| 04002 | +-+--+ | 2| 04002 | +-+--+ | 3| 04002 | +-+--+ | 4| 04003 | +-+--+ The result I want is: +-+--+ | id | count(*) | +-+--+ | 04002 | 3 | +-+--+ | 04003 | 1 | +-+--+ Is this possible? Sure. SELECT relid, count(*) from table1, table2 WHERE table1.id=table2.relid GROUP BY relid; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-Read My.cnf
Eric J. Janus [EMAIL PROTECTED] wrote: Is there a way to re-read the variables in my.cnf without having to re-start the server. No. I realize that I could manually change most of the variables, but being lazy as I am, having the server just re-read its settings would be idea. Some of variables you can set at runtime: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR ON INSERT DATA FROM FILE
adrian Greeman [EMAIL PROTECTED] wrote: I just tried to load edited data from a text file separated by | characters into a table in a simple data base I was using PhpMyAdmin version 2.5.6 and MySQL 4.0.18 running on Windows XP - I thought it was all set up right and configured properly - and earlier was able to populate the table with some initial data. Now I was trying to add more from a simple text file using the LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\php3E.tmp' INTO TABLE `headlines` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' instruction which phpMyAdmin creates. It refused to do it and returned the error message: #1148 - The used command is not allowed with this MySQL version I feel there must be a simple explanation LOAD DATA LOCAL should be enabled for client and for the server: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Perf Increase
Kumar [EMAIL PROTECTED] wrote: i have high performance dell server. i want to increase Read_Buffer_Size server variable to 5 MB. i have found docs on this in mysql site. like this mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M where i type that command i am getting status message as Mysqld is already running. how can i change those variables to take effect permanently. You can restart MySQL server with new value of read_buffer_size. If version of MySQL server = 4.0.3 you can set variable at runtime using SET statement: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help [resend]
Yonah Russ [EMAIL PROTECTED] wrote: Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? Use LEFT JOIN. For example: SELECT .. FROM book_table LEFT JOIN copy_table ON book_table.id=copy_table.book_id WHERE copy_table.book_id IS NULL; http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant problem!!!
[EMAIL PROTECTED] wrote: hi, I am having problems with mysql. 1. I downloaded mysql on to my system. 2.installed it 3. ran the server using the command prompt C:\mysql\bin\mysqld --console it gave me the results that i could start using the service. 4. i started mysql on another command prompt. what i am trying to do is i have to enter a huge genome sequence into a field. In order to do tht i have to change the global and session variables in mysql. i can change the session variables but i am not able to change the global variables. I gave the command: set global max_allowed_packet=4; it said: Access denied. You need the SUPER privilege for this operation. Then i created a database genome_db . then at the command promp i typed grant all privileges on genome_db.* to root@localhost identified by ' '; User 'root'@'localhost' by default has all privileges. i got the result as: Access denied for user: @'localhost' to database 'genome_db' dont know what to do:( confused You are connected as anonymous user. Use -u option of mysql client to specify username and connect to the MySQL server as a root: mysql -uroot -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Unicode
Stormblade [EMAIL PROTECTED] wrote: Hopefully this will be the last snag...least till the next one :) I'm having a problem setting the charset to unicode (utf-8). If I understood what I read I can set each table to support a charset. But what I want to do is set the default charset for a database to be utf-8. Is this possible? If so how? Yes, it's possible from version 4.1.0. CREATE DATABASE db_name DEFAULT CHARACTER SET utf8; http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html In any case, I need to be able to store unicode data and retrieve it. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column name contains #
Zhang Yu [EMAIL PROTECTED] wrote: I am migrating an old Access database to MySQL. After migration, we'll keep using Access forms as front end, and MySQL as database. I experienced a strange problem when creating table with a column name id #, which is used by many queries in Access. My command is like this: create table t1 (`id #` int(11), `First Name` varchard(20) ); I get this error:ERROR 1064: You have an error in your SQL syntax near '' at line 1 Once I substituted # to ! or @, the command worked fine. Is there any constraints with '#' for the column name? What version of MySQL do you use? Forked fine for me: mysql create table t1 (`id #` int(11), `First Name` - varchar(20) ); Query OK, 0 rows affected (0.00 sec) mysql show create table t1\G *** 1. row *** Table: t1 Create Table: CREATE TABLE `t1` ( `id #` int(11) default NULL, `First Name` varchar(20) default NULL ) TYPE=MyISAM 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HIGH_PRIORITY with UNION
Chad Attermann [EMAIL PROTECTED] wrote: I am using HIGH_PRIORITY in my SELECTs to force queries to take predcedence over updating due to replication. I have recently implemented UNION in some of my queries to optimize queries like WHERE table1.column1=something OR table1.column2=somethingelse. Anyway, I first tried formatting my UNION query like (SELECT HIGH_PRIORITY ...) UNION (SELECT HIGH_PRIORITY ...) but the server complained about the placement of HIGH_PRIORITY. I was finally able to get it to accept the query by only specifiying HIGH_PRIORITY in the first part of the UNION, like (SELECT HIGH_PRIORITY ...) UNION (SELECT ...), but it appears that my searches are not taking precedence as they should, and as non-UNION queries do. Could there be another explanation for why they are not taking precedence, or is there another way to specify HIGH_PRIORITY in UNION queries to make both sub-queries high-priority? You can specify HIGH_PRIORITY in the first SELECT statement, but you are right HIGH_PRIORITY doesn't take any effect in queries with UNION. Thank you for bug report! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when 4.1.2 release
Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: access denied when using load data infile
Jan Broermann [EMAIL PROTECTED] wrote: Hi, I've got a little Excel Macro that connects to MySQL via ADO below is the code. Funny thing is it connects to the database and executes the truncate orders without a problem. But when I come to Load data in file i receive an [MySQl][ODBC 3.5.1 Driver][mysql-4.0.18-nt]Access denied for user :'[EMAIL PROTECTED]' (using password: No) But, access is set to usage and user and password are ok. So what's bugging MySQL? User must have FILE privilege to use LOAD DATA INFILE. here is the code: Set conn = New ADODB.Connection With conn .ConnectionString = ODBC;DSN=Sport;option=65536;user=sport;password=IDONTTELLYOU;-) .Open DSN=Sport .Execute (TRUNCATE TABLE `angebot` ) .Execute (TRUNCATE TABLE `anbieter` ) .Execute (TRUNCATE TABLE `kategorien` ) .Execute (TRUNCATE TABLE `kategorien_sportart_zuordnung` ) .Execute (TRUNCATE TABLE `sportart` ) .Execute (TRUNCATE TABLE `sportstaette` ) .Execute (LOAD Data infile ' ThisWorkbook.Path \angebot.csv' into table angebot) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: alea mysql -V mysql Ver 12.22 Distrib 4.0.17, for pc-linux-gnu (i386) but mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.09 sec) why? the gentoo ebuild configure mysql with innodb support: where i m getting wrong? how can i see a yes working 'value'? Value NO means that MySQL server was configured without InnoDB support. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: - snip - Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; - snip - no, InnoDB is not enabled. how can i enable it? What version of MySQL do you use? 3.23.xx or 4.0.x? If you use 3.23 you should install MySQL-Max binaries or if you install from source distribution configure MySQL with --have-innodb option. More info you can find at: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html the referece manual show a my.cnf configuration for a machine with at least 2gb of ram and 60 of hard disk. how can i adapt this configuration for an home usage? For home usage you can use default values. is this a sufficient condition to emerge innodb tables? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Timeout Question
Paul Maine [EMAIL PROTECTED] wrote: I am running MySQL version 4.0.18 on a Windows 2000 server system. If I log in locally as an administrator and issues a command such as DESCRIBE TABLE some_table; - everything works fine. If I come back at a later time and run the same command again - it fails because of some type of timeout. If I immediately execute the same command again - it works. What timeout controls this behavior? Take a look at wait_timeout and interactive_timeout variables: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: permissions of ordinary users?
beginner [EMAIL PROTECTED] wrote: Hi, i got mysql 4.0.13 on my machine. When i installed it, i did only basic stuff to get it running (bascilly hit setup). Now, i'm getting a little bit closer to mysql (the system starts to became more friendly), but i got the following problem: - if i log as shellmysql this user seems to have access to modify all databases (i would want it to happen only when i log as shell mysql -h localhost -u root -p ) On Windows user ''@'localhost' has all privileges. How can i configure mysql NOT to accept logins with only shell mysql , i mean without beeing a specified user? Remove entries for anonymous user: DELETE FROM user WHERE user=''; FLUSH PRIVILEGES; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: i'm studing foreign keys but my sql code does not react as would imagine when i insert inconsistent data. i.e. create database urls; use urls; create table caths ( name varchar(7) primary key ) type = innodb; create table urls ( name varchar(10) primary key, home varchar(30) unique, cath varchar(7), constraint fk foreign key(cath) references caths(name) on update cascade on delete set null ) type = innodb; load data local infile caths.lst into table caths; load data local infile urls.lst into table urls; why it it possible to insert urls such as: mysql mysql.com dev-null when dev-null in not a value of the attribute caths.name? Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: international LIKE in 4.1.1-alpha
Juri Shimon [EMAIL PROTECTED] wrote: Hi! Win2000, MySql 4.1.1-alpha, cp1251 select 'ÃÎÍ×ÀÐÀ' like '%ÃÎÍ×ÀÐÀ%'- 1 select 'ÎËÅÑß ÃÎÍ×ÀÐÀ' like '%ÃÎÍ×ÀÐÀ%'- 0 It's a pity 8( Hint : select 'ÎËÅÑß ÃÎÍ×ÀÐÀ' like '%%ÃÎÍ×ÀÐÀ%'- 1(change leading % to %%) Variables: character_set_system=utf8 character_set_server=cp1251 character_set_database=cp1251 character_set_client=cp1251 character_set_connection=cp1251 character_set_results=cp1251 I was able to repeat it on 4.1.1 but your example worked fine on 4.1.2. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: international LIKE in 4.1.1-alpha
Juri Shimon [EMAIL PROTECTED] wrote: I was able to repeat it on 4.1.1 but your example worked fine on 4.1.2. Where I can get it? Binaries currently are not available, but you can install it from development source tree: http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I cant run mysql
homauoon mahboobi [EMAIL PROTECTED] wrote: I install mysql 4.1 on linux,but when i type mysql command,command line program of mysql is not run properly. please help me. What is exactly wrong with mysql program? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arabic character set support in mysql
homauoon mahboobi [EMAIL PROTECTED] wrote: Are you support Arabic character set an collations in my sql 4.1? I work with mysql 4.0 and store Arabic data on it,but i cant sort my varchar data.Is this problem solved when i upgrade mysql 4 to 4.1? From 4.1 you cam use cp1256 and unicode for arabic data: http://dev.mysql.com/doc/mysql/en/Charset-se-me-sets.html http://dev.mysql.com/doc/mysql/en/Charset-Unicode.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE query
Kevin Carlson [EMAIL PROTECTED] wrote:
I have a table with four columns, the first three of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)
In the case of this particular row, a row already exists with the
concatenated key of 580-0-205 and I am getting a duplicate key error. I
thought REPLACE was supposed to actually replace the contents of the row
if one exists. Does anyone have any ideas as to why this would be
causing a duplicate key error?
Works fine for me. The above CREATE TABLE statement has some syntax errors. What
exactly does CREATE TABLE look like?
What version of MySQL do you use?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: description of a field on mysql
Massimo Petrini [EMAIL PROTECTED] wrote: From which version it will be avalaible the possibility to define a descriptor of a field (column) ? From example if I define a column as CUSTOMER, I need to add a description for which customer is this column, as in microsoft office, sql. From version 4.1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE query
Emmett Bishop [EMAIL PROTECTED] wrote:
I ran into this problem when I installed 4.0.18. All
of the tables in my database are INNODB and the
REPLACE statement was failing on tables that had
foreign key constraints. I just rolled back to 4.0.16
and the problems went away. Not much of a solution,
but it's buying me a little time. Will I have to get
rid of all of the REPLACE INTO statements and replace
them with INSERT/UPDATE statements or is there some
configuration setting that needs to be changed to make
it work?
Could you provide a test case?
--- Victoria Reznichenko
[EMAIL PROTECTED] wrote:
Kevin Carlson [EMAIL PROTECTED] wrote:
I have a table with four columns, the first three
of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it
doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values
(580, 0, 205, 1)
In the case of this particular row, a row already
exists with the
concatenated key of 580-0-205 and I am getting a
duplicate key error. I
thought REPLACE was supposed to actually replace
the contents of the row
if one exists. Does anyone have any ideas as to
why this would be
causing a duplicate key error?
Works fine for me. The above CREATE TABLE statement
has some syntax errors. What exactly does CREATE
TABLE look like?
What version of MySQL do you use?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax error on create
warwick mayson [EMAIL PROTECTED] wrote: I am new to mysql and have a create script that when sourced throws a syntax error. The script : CREATE TABLE employee ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE position ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, position_type INTEGER UNSIGNED NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company_position ( company_id INTEGER UNSIGNED NOT NULL, position_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY(company_id, position_id), INDEX company_id_FKIndex1(company_id), INDEX position_id_FKIndex2(position_id), FOREIGN KEY(company_id) REFERENCES company(id) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(position_id) REFERENCES position(id) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; returns : ERROR 1064: 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 'position(id) ON DELETE NO ACTION ON UPDATE NO ACT Can anyone explain why this is happening ??? MySQL has a function with name POSITION. You should quote table name with backticks (i.e. REFERENCES `position`(`id`) ..) or add space between table name and bracket (i.e. REFERENCES position (id) .. ) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem while starting mysql daemon
Rohan Shrivastava [EMAIL PROTECTED] wrote: hello, I have installed mysql-3.23.58-pc-linux-i686 on Red Hat 9.0, while starting daemon i got the following message: ./bin/safe_mysqld --user=mysql [1] 5240 [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/mysql-3.23.58-pc-linux-i686/data 040409 21:56:46 mysqld ended [1]+ Done./bin/safe_mysqld --user=mysql When i saw the hostname.err file then it said mysql.host doesn't exist So how can i start the daemon Check if privilege tables exist in the 'mysql' database. (/usr/local/mysql-3.23.58-pc-linux-i686/data/mysql). If no, run mysql_install_db script to install these tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql scripting
Brad Tilley [EMAIL PROTECTED] wrote: Is there a way to do regex search are replace on all entries in a mysql field? Or, a way to uppercase or lowercase all characters for all entries in a filed? Some of the fields are varchars with numbers and letters. In my particular case, I have a field of serial numbers some of which are all caps some of which are not. Also the vendor name may be DELL for one entry and dell on another. We're standardizing the input to avoid this mess in the future, but I'd like to clean up what's current present. All tips are appreciated. Take a look at UPPER(), LOWER(), REPLACE() functions in MySQL. They may help you: http://dev.mysql.com/doc/mysql/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem while starting mysql daemon
Friday, April 09, 2004, 8:59:48 PM, Rohan Shrivastava wrote: RS there are some tables named RS columns_priv and tables_priv RS are you talking about these Yes. In this case check permissions on the MySQL data dir and files. MySQL should be owner of the data dir. RS Regards RS Rohan Rohan Shrivastava [EMAIL PROTECTED] wrote: hello, I have installed mysql-3.23.58-pc-linux-i686 on Red Hat 9.0, while starting daemon i got the following message: ./bin/safe_mysqld --user=mysql [1] 5240 [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/mysql-3.23.58-pc-linux-i686/data 040409 21:56:46 mysqld ended [1]+ Done./bin/safe_mysqld --user=mysql When i saw the hostname.err file then it said mysql.host doesn't exist So how can i start the daemon Check if privilege tables exist in the 'mysql' database. (/usr/local/mysql-3.23.58-pc-linux-i686/data/mysql). If no, run mysql_install_db script to install these tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS 2000 Advance Server
A Z [EMAIL PROTECTED] wrote: Hi, MySQL 4.0.14 Are there any known issues on windows 2000 advance server? After installation, on running the WinMySQLAdmin the green light comes on for a second or two and then disconnected. We tried running MySQLd-nt on its own, it simply cannot establish connection. What could be wrong here? Run mysqld-nt.exe from command line with --console option. In this case you can see error message on the screen. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error with loading table structure and data from a script file
Thursday, April 08, 2004, 1:07:27 PM, b wrote: hi, Error 1064: You have an error in your SQL syntax. Check the manual that corresponds to your mySQL for the right syntax to use near 'mysql -u root -p databasename scriptfile.sql' at line 1 however, I tried this. \u database name; \. scriptfile.sql; This way I managed to load my table structure and my data. Is the first command obsolete in mySQL ver 4.1.18? mysql -u root -p databasename some_file.sql is not the command of mysql client. You should run it from the command line. For example, if MySQL dir is C:\mysql, you should run C:\mysql\bin mysql -u root -p databasename file.sql You can also execute SQL script file with mysql command source (\.) Victoria Reznichenko [EMAIL PROTECTED] wrote: b b wrote: Hi, I'm using mySQL 4.1.18 on Windows XP pro. I tried this syntax and I received error. mysql -u root -p databasename What is the right syntax for this. I managed to unpack my table structure from the same script into another machine on Win2000 Server. However, I remember I had to try so many times and I cannot remember the exact syntax. Yes, it's correct. What error did you receive? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Robb Kerr [EMAIL PROTECTED] wrote:
I can't seem to get a FULLTEXT search in the form of MATCH...AGAINST to
work. I even copied verbatim the example listed on mysql.com at
(http://www.mysql.com/doc/en/Fulltext_Search.html). When I use...
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')
it works as expected. However when I use...
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('following')
it doesn't return any results.
I've got several tables with FULLTEXT indexes and on none of them can I get
this syntax to work. What's up?
Because 'following' is a stopword.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP !!! Can not restart server
Ginger Cheng [EMAIL PROTECTED] wrote: Here is the error msg: 040408 08:47:14 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 040408 8:47:14 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 040408 08:47:14 mysqld ended But I am not sure how to fix it. Could anyone help. If you didn't install privilege tables you must run mysql_install_db script. Otherwise check permissions on the MySQL data dir. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binlog strangeness?
Jonas Lind?n [EMAIL PROTECTED] wrote: hello, I came upon a strange thing just recently. I was trying to pipe a few sql statements but got a 1025 error all the time. I restarted, erased database manually but nothing worked so I tried deleting the binlogs and then my error disapered and everything was as normal. In what way does the bin log interfere with my data? Does MySQL read bin log and compare them in some way with new data? What SQL statements have you tried to execute? Which version of MySQL do you use? What OS? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication error
Ugo Bellavance [EMAIL PROTECTED] wrote: Hi, I'm trying to do a replication of two servers (one is mysql version = 4.1.0-alpha-debug-debug (slave) on debian and the other is = 4.1.0-alpha-debug-debug-log (master) on RH9). No I can't upgrade for = now. What I've done is that I stopped the master, copied the data folder to = the slave. I got the data from SHOW MASTER STATUS and input it in the = slave, and created the user on the master, as directed. The problem is that when I START SLAVE, I get this error: ERROR 1200: = The server is not configured as slave, fix in config file or with CHANGE = MASTER TO The problem might be caused by the fact that I have a firewall with nat = between them, but I set up the user accordingly ([EMAIL PROTECTED] = name of the firewall]. The slave is able to reach port 3306 on the = master and port-forwarding is enabled on the firewall, forwarding port = 3306 to the slave. SHOW MASTER STATUS on the master: mysql show master status; +--+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--+--+--+--+ | bobby-bin.23 | 79 | | | +--+--+--+--+ 1 row in set (0.00 sec) Did you put replication option to the my.cnf or use CHANGE MASTER TO command to configure your slave? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation in a mysql column
Mike R [EMAIL PROTECTED] wrote: I am using mysql 4.0.16. Is it possible to put a calculation of two other columns as the default value for a third column? Like: time = decimal 3,2 rate = decimal 3,2 totals = default is time * rate No. Default values must be constant. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query-cavhe questions revisited
Mark [EMAIL PROTECTED] wrote: I recently just upgraded to MySQL 4.0.18. I still have a few questions about the new query-cache. Since I am new to the query-cache, I really hope someone has some answers. The documentation says: The FLUSH TABLES statement also flushes the query cache. What does that mean exactly? Does it mean FLUSH TABLES, in effect, is equal to RESET QUERY CACHE, in that it purges the cache? Or does it mean FLUSH TABLES just defrags the query-cache, and has the effect of FLUSH QUERY CACHE? I would also be interesting to know whether defragging the query-cache takes any disk I/O. FLUSH TABLES removes all queries from the cache like RESET QUERY CACHE. Also, I initially set the query_cache_size to 128M. That is a bit excessive, perhaps. Is there a known, say, 'key_buffer to query_cache_size' ratio? A last question. :) I am correct in my assumption that, with the query_cache and all, I can reduce key_buffer_size? It is now set to 64M (against a 128M query_cache_size). My guess is that I will need less of key_buffer_size space. Right? query_cache_size isn't related to the key_buffer_size. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication between different database names
Andy Ford [EMAIL PROTECTED] wrote: I guess what I'm trying to ask is . is it possible for a MySQL server to be a slave to many MySQL masters? One slave can have only one master. But you can run multiple instances of MySQL on the slave. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop all keys / indexes on a table?
Daevid Vincent [EMAIL PROTECTED] wrote: It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? Specify several DROP INDEX clause in the single ALTER TABLE statement: ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, .. , DROP INDEX index_nameN; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replicating only certain tables?
Chris Petersen [EMAIL PROTECTED] wrote: I'm trying to set up two-way replication between our colo and our office (slow DSL line), so our web customers can get the fast speeds of our colo, and the people in the office can also get the fast speeds of our internal network. Since our colo machine is logging customer shopping cart info (etc.) that we don't need to send down the pipe to the office (and vice versa, a lot of stuff happening in-office that doesn't need to go out to the main web db box), is there any way to tell the MASTER not to send info? I can only see slave controls in the documentation (things like replicate-ignore-table or replicate-do-table), but those don't seem to prevent the data from being sent to the slave, only whether or not the slave decides to use it. You can restrict logging only on the database level with binlog-do-db/binlog-ignore-db options: http://www.mysql.com/doc/en/Binary_log.html Take a look also at SET SQL_LOG_BIN command: http://www.mysql.com/doc/en/SET_SQL_LOG_BIN.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR ON FOREIGN KEY ON WINDOWS 2000 WITH MYSQL 4.0.18
Massimo Petrini [EMAIL PROTECTED] wrote: Somebody can help me ? If you run the above code in a new db named prova, the last line create then error Can't create table '.\prova\#sql-654_2e.frm' (errno: 121) If you run the code on 4.0.17 all it is ok. Thank you for report! Entered to the bug database as: http://bugs.mysql.com/bug.php?id=3332 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: interesting....BUG? COMMENTS?
Nestor [EMAIL PROTECTED] wrote: I send this last week and no one commented. Nestor, I've already asked you check value of sql_select_limit variable: SELECT @@session.sql_select_limit; Is SELECT * FROM course_eng exact query that you use? Has anyone run into this simmilar problem? -Original Message- From: Nestor Florez [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 10:28 AM I have a php web application that has an admin page for inserting course records and one for selecting course records and a client page for selecting course record. In the admin side I insert records with an insert into Course_Eng and I select records witha select * from Course_Eng In the client side I get records witha select * from course_eng The kicker is that my client webbased select will only return 40 records (no limits are being use) but my admin webbased select returns 200 records. I SSH into the server and when I check the table desc course_eng look good. After scraching my head several times I found out that if manually typed on the server my select statement as select * from Course_Eng I would get 200 records back, but if I typed select * from course_eng I would get 40 records. Is this a bug? or a feature? Whe I did a show tables;, the table name is course_eng ther was no table Course_Eng If I remember correct in the SQL syntax the case should not matter? I change all my inserts and selects to Course_Eng that seem to work and returned me the most records My server is a Mac OS 10.2 and the Mysql version is server version: 4.0.16 Any ideas? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl prints only first 1032 character in output line
gowthaman ramasamy [EMAIL PROTECTED] wrote: hello list, this is a MySQL realted question I have a mySQL table with 15 columns. I query that table and group (using GROUP_CONCAT) them based on one filed (say coloumn1). I get the results. But, the result is truncated in some of the lines. It gets truncated exactly after 1032th character of that line. Truncation happens in lines having more than 1032 characters. how can solve this ? is this 1032 is limit set by mysql or linux (i use RH 7.3). How to sort it out. Check value of group_concat_max_len system variable: SELECT @@session.group_concat_max_len; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl prints only first 1032 character in output line
gowthaman ramasamy [EMAIL PROTECTED] wrote: On Thu, 2004-03-25 at 21:23, Victoria Reznichenko wrote: thank you dear victoria it says 1024. How can i increase it?. Use SET command. For example: SET @@global.group_concat_max_len=5000; or SET @@session.group_concat_max_len=5000; Check value of group_concat_max_len system variable: SELECT @@session.group_concat_max_len; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Bing Du [EMAIL PROTECTED] wrote: Greetings, I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: *** 1. row *** Host: localhost User: ltmuser Password: 132f058a4532ff99 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 I thought the *_priv fields should all have 'Y' rather than 'N'. Table 'user' contains global level privileges, but you set permissions on the database level. Check 'db' table. I also tried 'flush privileges' but it did not help. What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I can insert rows one by one into table ltm_enrollment as user 'ltmuser'@'localhost' without any problems. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Bing Du [EMAIL PROTECTED] wrote: Thanks for the heads-up! Bing Du [EMAIL PROTECTED] wrote: What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I did not realize FILE privilege was not covered by 'grant all'. Because FILE is a global level privilege: GRANT FILE ON *.* TO 'ltmuser'@'localhost' IDENTIFIED BY 'secret'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW_DATABASES / safe-show-database question
Robert Montgomery [EMAIL PROTECTED] wrote: I recently upgraded to mysql 4.0.18. In the past you could set the option safe-show-databases and that allowed users to list databases, but only those they have permissions to see. It seems that this 4.0.18 will only allow users to see ALL databases, or NO databases, depending on the SHOW_DATABASES field in the mysql.user table. No. Without SHOW DATABASES privilege only databases on which user has some kind of privileges will be listed in the output of SHOW DATABASES command. Is there any way to allow users to list ONLY the databases they have permissions on? Don't give user SHOW DATABASES privilege. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with this: ERROR 1047: Unknown command
Hiep Ho [EMAIL PROTECTED] wrote: i have problem adding user to mysql. I can create a database and there is no problem however I got this ERROR 1047: Unknown command when I tried to add new user. I don't think I have this problem before. I run this command GRANT ALL ON *.* TO usename IDENTIFIED BY 'password' ; All I got is this ERROR 1047: Unknown command Execute FLUSH PRIVILEGES and then GRANT command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Udbhav Shah [EMAIL PROTECTED] wrote: Dear Victoria Reznichenko, Thanks for reply. Could you tell me from where I can get patch to fix this bug. or I have to use Mysql 4.0/Mysql 5.0 You can install latest available version, including the bugfix, from the development source tree: http://www.mysql.com/doc/en/Installing_source_tree.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downloading Data
Rick Duley [EMAIL PROTECTED] wrote: Hi folks I am running MySQL 4..0.7-gamma on Red Hat Linux 7.2. I wish to download data from my database. I have a directory called 'Results' with permissions set to: drwxrwxrwx I enter the command: SELECT * INTO OUTFILE '/home/rick/Results/Results.txt' FROM Answer; and I get: ERROR 1045: Access denied for user '[EMAIL PROTECTED]' (Using password: YES) The command without the INTO OUTFILE '/home/rick/Results/Results.txt' clause works just fine. Where have I missed the bus? Thanks User must have FILE privilege to use SELECT INTO OUTFILE and LOAD DATA INFILE commands. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table tt union=(t1,t2,t3);
On Monday 22 March 2004 18:26, Shane Nelson wrote: Thanks, the help file cleared up the rest. In my case the table tt wasn't a merge table, it was just a table I created normally. Even so the alter table line didn't create an error. If the table type other than MERGE, UNION part of the ALTER TABLE statement is ignored. So, your ALTER TABLE statement will recreate table tt and copy data. Shane Egor Egorov wrote: Shane Nelson [EMAIL PROTECTED] wrote: Any idea what this would do? alter table tt union=(t1,t2,t3); With this statement you specify that MERGE table tt will union tables t1, t2, t3 and they will used as one: http://www.mysql.com/doc/en/MERGE.html The four tables are all the same and all contain data. I posted the details (schema, contents) of the tables here: http://nopaste.php.cd/10918 I'm running mysql Ver 11.18 Distrib 3.23.58, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If DataBase Exists
A Z [EMAIL PROTECTED] wrote: MySQL 4.0.14 Possible to query for existence of a DB. For example connect to MySQL and run run a command to check if Database (Test2) existed. Use SHOW DATABASES command: SHOW DATABASES LIKE 'Test2'; http://www.mysql.com/doc/en/Show_database_info.html But you can see only those databases for which you have some privileges. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
