Seeking contribution for MySQL Quality Assurance
Hi, all. MySQL is about to launch yet another contribution project. We are still in the planning phase, and I have outlined the issue in this article: http://datacharmer.blogspot.com/2006/10/contributing-to-mysql-qa-ideas-wanted.html Comments welcome! Giuseppe -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Instance manager and starting instances on demand
When setting up several instances in the instance manager, if you don't want them all to start at once, but you want to start instances on demand (like when you have instances of different MySQL versions) the only way I found to achieve this goal is is to set the option nonguarded. Then, when you start the instance with START INSTANCE name, it starts, but the IM does not monitor it. Justly so, because of the nonguarded option. So before submitting a bug (or feature request) report, my questions are: 1) Is this the correct way of setting several instances and firing them on demand? 2) Can I revert the effects of nonguarded? I tried with UNSET instance_name.nonguarded, but it does not have any effect. Thanks for any help. Giuseppe -- _ _ _ _ (_|| | |(_| The Data Charmer _| http://datacharmer.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: [SNIP] The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? A cheap solution, with a user variable: select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 and (@count := coalesce( @count, 0) + 1 ) % 10 = 0 ; ciao gmax -- _ _ _ _ (_|| | |(_| The Data Charmer _| http://datacharmer.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
puzzled by date functions (long)
Yesterday I was analyzing the behavior of the query optimizer, and I stumbled
into a most curious case.
I created two functions returning the extremes of a date range, and I wanted to
see how many times those functions were
called when used in a WHERE clause So I added log tracing instructions to both
of them. The result was quite surprising.
Let's set the environment first.
create database if not exists test ;
use test ;
delimiter //
drop function if exists today_start //
CREATE FUNCTION today_start() RETURNS datetime
begin
insert into mylog (routine_name) values ('today_start');
-- return current_date();
return '2006-02-28 00:00:00';
end //
drop function if exists today_end //
CREATE FUNCTION today_end() RETURNS datetime
begin
insert into mylog (routine_name) values ('today_end');
-- return current_date() + interval 1 day - interval 1 second;
return '2006-02-28 23:59:59';
end //
delimiter ;
drop table if exists t1;
create table t1 (
id int(11) NOT NULL auto_increment,
dt datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
drop table if exists mylog;
create table mylog (
id int not null auto_increment primary key,
routine_name varchar(20) not null,
TS timestamp
);
INSERT INTO `t1` VALUES
(1,'2006-02-28 11:19:35'), (2,'2006-02-28 11:19:38'),
(3,'2006-02-28 11:19:40'), (4,'2006-03-01 11:20:09'),
(5,'2006-03-01 11:20:11'), (6,'2006-03-01 11:20:12'),
(7,'2006-03-01 11:20:13');
select * from t1;
++-+
| id | dt |
++-+
| 1 | 2006-02-28 11:19:35 |
| 2 | 2006-02-28 11:19:38 |
| 3 | 2006-02-28 11:19:40 |
| 4 | 2006-03-01 11:20:09 |
| 5 | 2006-03-01 11:20:11 |
| 6 | 2006-03-01 11:20:12 |
| 7 | 2006-03-01 11:20:13 |
++-+
Now I select all today's rows from t1.
select * from t1 where dt between today_start() and today_end();
++-+
| id | dt |
++-+
| 1 | 2006-02-28 11:19:35 |
| 2 | 2006-02-28 11:19:38 |
| 3 | 2006-02-28 11:19:40 |
++-+
That's correct. Now, let's see how many times the function was called:
select * from mylog;
++--+-+
| id | routine_name | TS |
++--+-+
| 1 | today_start | 2006-02-28 12:26:24 |
| 2 | today_end| 2006-02-28 12:26:24 |
++--+-+
And that too was what I expected. But the story changes if I use a slightly
different table. This one has the same
columns as t1, but the primary key is the datetime column.
drop table if exists t2;
create table t2 (
id int not null,
dt datetime NOT NULL,
PRIMARY KEY (dt)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into t2 (id, dt) select id, dt from t1;
Now I did the same experiment with this table:
truncate mylog;
select * from t2 where dt between today_start() and today_end();
++-+
| id | dt |
++-+
| 1 | 2006-02-28 11:19:35 |
| 2 | 2006-02-28 11:19:38 |
| 3 | 2006-02-28 11:19:40 |
++-+
The query finds the same records. Let's see what happens to mylog:
select * from mylog;
++--+-+
| id | routine_name | TS |
++--+-+
| 1 | today_start | 2006-02-28 12:30:00 |
| 2 | today_end| 2006-02-28 12:30:00 |
| 3 | today_start | 2006-02-28 12:30:00 |
| 4 | today_end| 2006-02-28 12:30:00 |
++--+-+
I can't imagine why this is happening. The only difference is that dt is now
primary key. Instead of being called once,
the routine is called twice. If I simply drop the primary key in t2, then the
routine is called once per query, as expected.
The result does not change if I use InnoDB tables instead of MyISAM.
Can anyone explain what is happening here?
Thanks
Giuseppe
--
_ _ _ _
(_|| | |(_| The Data Charmer
_|
http://datacharmer.blogspot.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
Dan Nelson wrote: In the last episode (Mar 16), Giuseppe Maxia said: Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ # NOT OK This looks okay to me, according to http://dev.mysql.com/doc/mysql/en/comparison-operators.html#id2940868 : To comply with the SQL standard, from MySQL 4.1 on IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. Thanks for your comment. I knew about that page, and probably I was a bit too quick when I used this example as proof of concept. Actually, the real problem happens only with subqueries, as I reported in the rest of my previous message. 2 doesn't match 1, NULL, or 3, and there's a NULL in the list, so the IN expression must return NULL. NOT(NULL) is still NULL, so the entire expression returns NULL. Subqueries using IN() may not be the same as the IN() expression (I rarely use subqueries so I don't know); they are documented at http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html and http://dev.mysql.com/doc/mysql/en/all-subqueries.html . The whole point is actually in subqueries, not when using IN or NOT IN in a normal query. The bug occurs when a NOT IN is used in a subquery as a LEFT JOIN replacement. SELECT something from t1 where column1 NOT IN (SELECT nullable_column from t2); BTW, I posted to this list by mistake. I re-posted an amended version of the same bug report to the bugs list. Giuseppe Maxia -- Giuseppe Maxia CTO http://www.StarData.it MySQL Certified Professional __ __ __ ___ / / / /__ _/ / _ (_-/ __/ _ `/ __/ _ / _ `/ __/ _ `/ /___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/ Database is our business -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
Peter Brawley wrote: Giuseppe, mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK Isn't that standard SQL behaviour? Yes, it is. As I said before, I was too quick using this example, while the problem arises only during a subquery. Now, others have pointed out that even with subqueries this should be considered standard behaviour, even though severa people in my workplace agree that it does not look intuitive. NULL is not a value. NOT IN compares the values using '=' and correctly returns NULL if any value is NULL ie missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html I see now that this mechanism is intentional. Thanks for your link. Giuseppe -- Giuseppe Maxia CTO http://www.StarData.it MySQL Certified Professional __ __ __ ___ / / / /__ _/ / _ (_-/ __/ _ `/ __/ _ / _ `/ __/ _ `/ /___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/ Database is our business -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery fails when a NOT IN operator tests a subset with NULL valu
Hi. Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Cheers Giuseppe Maxia Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ 1 row in set (0.00 sec) #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK More complete proof: mysql drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql drop table if exists t2; Query OK, 0 rows affected (0.06 sec) mysql create table t1 (id int not null auto_increment primary key, c1 int); Query OK, 0 rows affected (0.01 sec) mysql mysql create table t2 (id int not null auto_increment primary key, c2 int); Query OK, 0 rows affected (0.02 sec) mysql insert into t1(c1) values (1),(2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql insert into t2(c2) values (2),(null),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from t1; ++--+ | id | c1 | ++--+ | 1 |1 | | 2 |2 | ++--+ 2 rows in set (0.01 sec) mysql select * from t2; ++--+ | id | c2 | ++--+ | 1 |2 | | 2 | NULL | | 3 |3 | ++--+ 3 rows in set (0.00 sec) mysql select t1.* from t1 left join t2 on (c1=c2) where t2.id is null; ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # OK. This is the normal way of checking for non-existence of records in a # related table mysql select t1.* from t1 where c1 not in (select distinct c2 from t2); Empty set (0.01 sec) # NOT OK. This query should have returned the same result as the previous one mysql select t1.* from t1 where c1 not in (select distinct c2 from t2 where c2 is not null); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # ugly workaround with an express filter mysql select t1.* from t1 where c1 not in (select distinct coalesce(c2,0) from t2 ); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # yet another ugly workaround Fix: as a temporary workaround, filter off the NULLs with a WHERE clause or a COALESCE function. Submitter-Id: submitter ID Originator: Giuseppe Maxia Organization: Stardata s.r.l MySQL support: Certified Consulting Partner Synopsis: subquery fails on test with NOT IN and NULL values Severity: serious Priority: high Category: mysql Class: sw-bug Release:mysql-4.1.10-standard (MySQL Community Edition - Standard (GPL)) Server: /usr/local/mysql/bin/mysqladmin Ver 8.41 Distrib 4.1.10, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.10-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 36 sec Threads: 2 Questions: 111 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables: 2 Queries per second avg: 0.044 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux ltstardata 2.6.9-1.667 #1 Tue Nov 2 14:41:25 EST 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 12 14:24 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 Oct 28 01:00 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 Oct 28 00:46 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Oct 28 00:08 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client
Error in NULLIF documentation
Description: The documentation for NULLIF states that expr1 is evaluated twice if the expressions are equal. Actually, it is the opposite. Expr1 is evaluated twice when the two exporessions are NOT equal. How-To-Repeat: mysql set @myval:=0; Query OK, 0 rows affected (0.00 sec) mysql select NULLIF(@myval:=@myval+1,1); # now @myval is 1, it matches, NULLIF returns NULL ++ | NULLIF(@myval:=@myval+1,1) | ++ | NULL | ++ 1 row in set (0.00 sec) mysql select NULLIF(@myval:=@myval+1,1); # After the first test, @myval is 2 ; doesn't match ++ | NULLIF(@myval:=@myval+1,1) | ++ | 3 | ++ 1 row in set (0.00 sec) # @myval is now 3, as a result of a double evaluation, when expr1 and expr2 were not equal. Fix: Correct the documentation Submitter-Id: submitter ID Originator:Giuseppe Maxia Organization: MySQL support: none Synopsis: error in documentation Severity: non-critical Priority: low Category: mysql Class: doc-bug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unable to drop table
I created a table with a buggy script. The name of the table is dbimport-import. drop table `dbimport-import`; # note: inverted quotes gmax - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Comparing an alias ...
It won't work this way. SELECT min(my_column) as MINIMAL_VALUE will give you ONE record only. Therefore, an additional condition (which you can get with HAVING, not WHERE) will be meaningless. e.g: SELECT min(my_column) as MINIMAL_VALUE FROM mytable HAVING MINIMAL_VALUE 10 will return an empty set if the minimal value is = 10. This one, will give you some more: SELECT other_column, min(my_column) as MINIMAL_VALUE FROM mytable GROUP BY other_column HAVING MINIMAL_VALUE 10 Here you will get one line for each distinct value of other_column, provided that the minimimum value is bigger than 10. Bye Giuseppe Maxia Hi ... I am need the following query to work : SELECT min(my_colum) as MINIMAL_VALUE WHERE MINIMAL_VALUE 10 The query is not EXACTLY as this one, but i think it is enough to get you the idea of my problem... Thnx for any help ! []~S julio - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GUIfront-end in Linux?
I'm moving from Win2k to Linux and was wondering if there was a GIU MySQL front-end in Linux similar to Mascon or mysqlfront for the windoz Oss. Thanks Frank Try MySQLnavigator binaries: http://ftp.kde.com/Database/MySQL_Navigator/mysqlnavigator-1.3.3.3.binary.tar.gz sources: http://ftp.kde.com/Database/MySQL_Navigator/mysqlnavigator-1.3.3.3.tar.gz It is not Mascon (BTW, they are planning a Linux version, but I don't know when), but it has a nice graphical interface. Giuseppe Maxia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How Auto_INCREMENT works
Hi all, I have a question: I made a table with a field that is auto_increment. This field I made to be the primary key. In my opinion an auto_increment field should fill itself, without the intervention of the user. So if I have a table like: f1,f2 --field names, where f1 is auto_increment... than in the insert sql statement I should insert only the f2 field, and mysql should fill the f1 field with the apropriate value. Exactly. Pass a null value to the auto_increment field, and it will kick off. INSERT INTO tablename (f2) VALUES (xyz); will automatically create values for f1. or INSERT INTO tablename (f1, f2) VALUES (NULL, xyz); will have the same effect. The difference is that in the first example the null value is implicit, while in the second one it is explicit. Bye Giuseppe Maxia Or I understood wrong the auto_increment property? If so, then what should I do to obtain such a behaviour I mentioned above; if not then what I do wrong, what should I do , or how the insert statement should look? Thank you.. best regards emil Jurj (xenon) :)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
affected rows and found rows
I would know in what cases found rows and updated rows could be different: what i think is that if i get no error code the two number would be the same, but if the update fails (example key violation) the two number would be different. Try this one, for a simple case: mysql create table test (id INT not null auto_increment primary key, title char(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into test (title) values (first),(second),(third),(fourth); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select * from test; +++ | id | title | +++ | 1 | first | | 2 | second | | 3 | third | | 4 | fourth | +++ 4 rows in set (0.00 sec) mysql update test set title = second where id 1; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 The query will match THREE rows (id 1), but only TWO will be affected, since one of the matching ones has already the value you wanted to enter. Hope it helps Giuseppe Maxia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem creating primary key on blob column
ERROR 1170 : BLOB column 'MY COLUMN' used in key specification without a key length. What is the syntax to settle a key length ? ALTER TABLE your_table add key blob_field (blob_field(50)); Or, you can create a table like this one: CREATE TABLE `your_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(2) NOT NULL default '', `blob_field` TEXT, PRIMARY KEY (`id`), KEY `blob_field` (`blob_field`(20)) # indexed on the first 20 characters of the field ) TYPE=MyISAM BLOB and TEXT field can be indexed on a fixed amount of data only. Therefore you must specify the length of the index. Giuseppe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
a select n a regexp expression
Hello, I'm always with my access to mysql port.
I noticve that access can dispaly a â but it s like a a.
So I make this query to select some specials row from my database :
select ort from localite_ortsnamen
where ort REGEXP
^([A-Za-z]|[àâäåáãçéèêëîïíñõôöóùúûüÿÅÅÀÂÄÇÉÈÊËÎÏÑÔÖÛÜß', -])+$=0;
I'd like to add the possibilite from the ort column to have char like (),-'
, but it doesn't work
i try to escape these cars with a \, but nothing. So how can I do this ?
thanks.
You should either use a double backslash
\\(
\\
or put the special symbols inside a character class
[-)('abc]. In this case, to avoid syntax errors, the dash (-) shoud be at the
beginning of the class.
select a(bc regexp [)(x-s]
gives you a syntax error, because the dash is interpreted (wrongly) as range operator
notice that
select a-bc regexp [)(x-z]
will execute without errors, but the dash in this is a range operator,
not a character to match. It means: match a parenthesis, or lowercase letters from x
to z.
Therefore it will return 0.
select a(bc regexp [-)(ba)];
returns 1.
select a(bc regexp \\( ;
returns 1
select a(bc regexp [-'(];
returns 1;
select a(bc regexp (
gives you an error.
Giuseppe Maxia
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SOURCE command somewhere in manual?
Is there really a SOURCE command? Where is it in the official manual? source is a command available within the mysql client only. It is not a SQL command. The client can also get external batch files by piping the commands, in Unix style. Once you have your file of SQL instructions (say lines.sql) you can call the client $ mysql -u username -p lines.sql mysql set @var = xyz; mysql SOURCE filename can @var be used in filename? Yes, it can. Variables are valid within the same thread. $ echo select @var; hhh.sql $ mysql -p -u xyz mysql select @var:=curdate(); +-+ | @var:=curdate() | +-+ | 2001-11-20 | +-+ 1 row in set (0.00 sec) mysql source hhh.sql ++ | @var | ++ | 2001-11-20 | ++ 1 row in set (0.00 sec) mysql select @var:=AAA; +-+ | @var:=AAA | +-+ | AAA | +-+ 1 row in set (0.00 sec) mysql source hhh.sql +--+ | @var | +--+ | AAA | +--+ 1 row in set (0.00 sec) Bye Giuseppe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Data
You can export using SELECT fieldlist INTO OUTFILE filename FROM table; This will create a file with tab separated fields. It is Excel default separator. Within Excel, you can open the file, by giving filetype text, and a wizard will guide you through the translation. You can create a CSV file by adding , at the end of the SQL statement, FIELDS TERMINATED BY ',' ENCLOSED BY '' Giuseppe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selling MySQL to Government
Hi, I have been through a similar case. I am working for a large organization, which had the Human Resources data scattered through seven different Access databases counting about 200K records. The challange was not only to migrate them, but also to unify into one armonized structure. I made a prototype with MySQL, after a cursory data migration, with ONE MILLION records (between real and simulated ones), which showed the potential users most of the benefits in matter of reliability, speed, accessibility and security. The users were fascinated. The IT department not so much, since they had in mind a more expensive tool (which, under the most optimistic view, is not going to be deployed before 18 months) and they don't like the open source philosophy. However, since the users were so supportive, I got over the opposition and managed to finish the project. Now I have 100 days uptime in my server and of course the users love it. The key part was the business case that I proposed to the users. With a prototype, they were able to appreciate the difference and support my choice. Best of luck Giuseppe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unique and case-insensitivity with indexes
12/11/2001 17:34:07, Fulko Hew [EMAIL PROTECTED] wrote:
I am using mySQL 3.22.4a-beta
yes, I know its old :-(
I have just stumbled across a problem with how it
treats 'uniqueness' in table contents.
I have a table with a column defined as:
create table test (name varchar(80) not null);
alter table test ADD UNIQUE (name), ADD INDEX (name);
When I now add entries that are case sensitive, I get duplicate entry errors:
mysql insert into test (name) values ('a');
Query OK, 1 row affected (0.04 sec)
mysql insert into test (name) values ('A');
ERROR 1062: Duplicate entry 'A' for key 1
I always considered the value of 'a' and 'A' to be unique,
but this version of mySQL doesn't appear to, at least with
indexes.
Can anyone either point out what I am doing wrong, or a workaround?
The workaround is to use the attribute BINARY for your field
create table test (myfield varchar(80) BINARY not null, UNIQUE KEY myfield);
This way, the index is case sensitive.
Unfortunately, this feature was introduced in MySQL 3.23, so your current server
is not able to deal with it.
Giuseppe
TIA
Fulko
---
Fulko Hew, Voice: 905-681-5570
Senior Engineering Designer, Fax:905-681-5556
SITA (Burlington)Email: [EMAIL PROTECTED]
777 Walkers Line,
Burlington, Ontario, Canada, L7N 2G1
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Run SQL Commands from a Text File stably?
12/11/2001 05:32:28, PI Xu [EMAIL PROTECTED] wrote:
Hi, Buddies:
I used perl to run sql commands from a Text file, it always can't finish
totally and report error in unstable lines .
The error report that sql syntax error, but the syntax is same, just same,
and many sql lines;
Would you please tell me what this happen and how to resolve it?
Thanks in Advance!
Bill
PERL COMMANDS: system('mysql -uusername -ppassword database
pricfile.sql');
if pricfile.sql has 37871 lines sql commands like
update products set retail=43.00 where partno='123456';
then the perl program give
ERROR 1064 at line 37850: You have an error in your SQL syntax near
''99922' at line 1
I think you should have a look at the lines 37849 and 37850.
The error could be that the previous one was wrongly terminated,
or the current one has an invalid value.
It could be a problem of unproper quoting.
Having a look at the lines involved could be helpful. How did you produce the file?
Bye
Giuseppe
if pricfile.sql has 1 lines, then the perl program give
ERROR 1064 at line 19932: You have an error in your SQL syntax near '' at
line 1
if pricfile.sql has 17872 lines sql commands like, then the perl program
give
ERROR 1064 at line 17848: You have an error in your SQL syntax near '' at
line
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
