CREATE TEMPORARY TABLE
Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Countries
Alternatively you can create a priority column defaulting to one number (99) this will allow you to easily change the country sequence as needed. SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY priority,countries_name; I hope this helps... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: "Don Read" <[EMAIL PROTECTED]> To: "Ralph" <[EMAIL PROTECTED]> Cc: "mySQL Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, June 25, 2003 4:38 AM Subject: Re: Sorting Countries > > On 25-Jun-2003 Ralph wrote: > > I am getting a list of all countries from database, and then I am > > sorting by country name. However since most orders will be from US I > > want the US to appear first over the rest of the countries. How can I go > > about doing this? > > > > Currently, this is my query: > > > > SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY > > countries_name; > > "SELECT IF(countries_id='US', 0, 1) as ctsort, countries_id, countries_name > FROM TABLE_COUNTRIES > ORDER BY ctsort, countries_name" > > Regards, > -- > Don Read [EMAIL PROTECTED] > -- It's always darkest before the dawn. So if you are going to >steal the neighbor's newspaper, that's the time to do it. > (53kr33t w0rdz: sql table query) > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: type casting help!!!
Prem Soman wrote:
hi!! i wrote a simple function that returns a string in C
the function fetches values from a table and stores all the value in a string variable
and then returns it ...This is small part inside the function i wrote!
while((row = mysql_fetch_row(res)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(res);
for(i=0;i{
str = strcat(str,row[i]);
}
str = strcat(str,"\n");
}
but i know that the content in red would rerturn an error!!
how to convert the output of mysql_fetch_row() function to an equivalent string ?
I think, perhaps, the C fragment above has got a bit mangled in
transmission. Assuming that
you want to concatenate the individual column and the code is based on
manual section
22.4.18 (My 3.23.28 manual) then you need some space in which to
construct the
concatenated columns.
unsigned long *lengths;
unsigned long tlength,i;
MYSQL_ROW row;
char *str;
row = mysql_fetch_row(res);
lengths = mysql_fetch_lengths(res); /* gives array of column
widths */
tlength = 0;
for(i=0;i
tlength += 1; /* need space
for string terminator */
str = (char *)calloc(tlength,sizeof char); /* get
some space */
for(i=0;i
/* don't forget to free the space !! */
-
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Execution Time in mysql
# [EMAIL PROTECTED] / 2003-06-24 20:32:48 -0700: > insert into table1 select * from table2; > > Table2 has some 10,000 records while table1 has around > 11,00,000 records. The time reqd. to execute the above > query is round about 80- 90 secs. > > The table1 has 7 columns out of which 5 are composite > primary keys. No other indexing is provided. I have > also tried to use the "optimize table table1" command > but to no effect. does this help? http://www.mysql.com/doc/en/Insert_speed.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP] emalloc() error being thrown {LONG} - SOLVED...partially
[snip the problem code, comments eliminated for clarity]
if (mysql_num_rows($dbnpanxx) > 0){
$npanxxcnt = 0;
while($urownpanxx = mysql_fetch_object($dbnpanxx)){
fputs($npanxxfile, $urownpanxx->o_npanxx);
fputs($npanxxfile, $urownpanxx->t_npanxx);
for($spaces = 13; $spaces <= 66; $spaces++){
fputs($npanxxfile, " ");
}
fputs($npanxxfile, "N");
for($spaces = 68; $spaces <= 86; $spaces++){
fputs($npanxxfile, " ");
}
fputs($npanxxfile, "\n");
$npanxxcnt++;
}
mysql_free_result($dbnpanxx);
}
[/snip the problem code]
Please excuse the cross-post again, but this is still dealing with how
PHP and MySQL together handle memory allocation. The results being
placed into the output file are from a large MySQL database, returning a
large dataset for each output file.
The answer ends up being sort of a two parter, and there are still some
emalloc() errors, but I am working to correct them.
1. - MySQL does not automatically release the memory when use of a
dataset is completed. If you are looping through datasets (as I have
been based on the change of an array variable). Once you complete a loop
and the dataset is going to change go ahead and clear the results (and
subsequent memeory use) by using mysql_free_result().
2. Tis apparently better to place a single line of output in one fputs
statement as fputs is very resource intensive. The code above, written
by one of our new programmers who has about 2 weeks of PHP experience
looks good on the surface. It is organized, logical and precise. It also
calls fputs approximately 84 times per line of output when you take into
account the for loops. It is better to either create variables to hold
calced spaces or to literally call the fputs like;
fputs($npanxxfile, $urownpanxx->o_npanxx.$urownpanxx->t_npanxx."
number of spaces N spaces \n");
with all of the spaces in place.
Also, I attempted to use mysql_unbuffered_query which would have also
cut down on memory use, but it came back as an undefined function even
though I am running ver 4.1.1 (albeit from the command line, so I may
not have it configured properly or something.
I HTH others with mem allocation problems. If anyone can see anything
else that can be done to improve performace of this please let me know.
We are processing some very large datasets and anything we can do to
increase efficiency would be like pennies from heaven.
TIA!
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Gant permission problems with domains and full hostnames for same user
Hello, I currently am struggling to get the permissions and table restrictions to work. Description of the case: -- For a database "examp", where table "foobar" which contains colums "A", "B" and C ,only a restricted access is allowed. Lets say that user "peter" idendified by "kieke" may only READ attributes "A" and "B" from hosts in the domain "siemens.com", but can also do an UPDATE attribute "A" when he is connecting from host "stardust.siemens.com". The attribute "C" cannot be read or updated. What I tried, is to apply these rules for given case. I used the (textbook) grant statement like: grant SELECT (A,B) on examp.foobar for 'peter'@'stardust.siemens.com' identified by "kieke"; grant UPDATE (A) on examp.foobar for 'peter'@'stardust.siemens.com' identified by "kieke"; This does the job well. only from that host I have the correct access and restrictions. BUT, when I apply the 'domain' rule, it does not work anymore. In detail, I can still SELECT the A and B, but cannot UPDATE A anymore from the specific host. grant SELECT (A,B) on examp.foobar for 'peter'@'%.siemens.com' identified by "kieke"; I tried by adding an entry to the host and db table of mysql-db, but there either then R/W is allowed on all attributes or on none. (all 'Y' or all 'N') Am I forgetting something ? I also tried to delete all created entries in the user table, and replaced them with one entry, namely by grant USAGE on *.* for 'peter'@'%' identified by "kieke"; So that default permissions are set to 'N'. Basic idea: restrict access to only READ for specific domains (using the % ) and allow WRITE to some specic hosts of the same domain Hope someone can hint me a solution. With regards, Peter De Leuze SIEMENS Atea [EMAIL PROTECTED] phone: +32 14 253493 Fax: +32 14 22 29 94 Mobile Solutions and Enabling Services http://www.ic.siemens.be Customer driven solution providers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
type casting help!!!
hi!! i wrote a simple function that returns a string in C
the function fetches values from a table and stores all the value in a string variable
and then returns it ...This is small part inside the function i wrote!
while((row = mysql_fetch_row(res)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(res);
for(i=0;i{
str = strcat(str,row[i]);
}
str = strcat(str,"\n");
}
but i know that the content in red would rerturn an error!!
how to convert the output of mysql_fetch_row() function to an equivalent string ?
-
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
Fwd: problems with farsi sorting!!
Note: forwarded message attached. Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!--- Begin Message --- I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). thanx for your helps... - Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!--- End Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 19:17、Victoria Reznichenko さんは書きました: > "Nils Valentin" <[EMAIL PROTECTED]> wrote: > > I have some questions about temporary tables. I would appreciate any > > replies: > > > > I created a temporary table f.e. like this: > > > > mysql> create temporary table temp SELECT * FROM relations; > > Query OK, 4 rows affected (2.35 sec) > > Records: 4 Duplicates: 0 Warnings: 0 > > > > When I do > > > > mysql> show create table temp; > > > > Then it will give me this: > > | temp | CREATE TEMPORARY TABLE `temp` ( > > > > `member_id` int(16) NOT NULL default '0', > > `company_id` int(16) NOT NULL default '0', > > `membership_id` int(16) NOT NULL default '0' > > ) TYPE=MyISAM CHARSET=latin1 | > > > > > > Now what confuses me is that > > > > a) it says here TYPE=MyISAM > > Because MyISAM is default table type > > > b) I understood that only HEAP tables are stored in the memory. > > Yes. > > > I was assuming (until now) that HEAP tables are (the only one type of) > > temporary tables. > > Temporary tables may have type HEAP. > > > I tried > > > > CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; > > or > > CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; > > > > and they are all created as in-memory tables no files are created. > > Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary > > tables > > You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. > Temporary table files are created in the temporary directory: > http://www.mysql.com/doc/en/Temporary_files.html > > > If, so what would be the difference between a temporary table in general > > and a HEAP table ? > > Temporary tables are the per-connection tables. > HEAP tables are stored in memory 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 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user@"%" vs user@"localhost" question
This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing SET PASSWORD and FLUSH PRIVILEGES. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password And now it works the new password must be supplied for the user to logon. Does the % domain not include the localhost domain? If not, what is the use of the % domain? When should I use % and when localhost ? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
Todd Burke wrote: Hello 1/ I have a search engine which crawls auction sites and returns information which is inserted into a mysql database. This activity represents on average about 3 inserts per second with a combined payload of 450 bytes per second added to the database. This updating occurs continuously throughout the day. Since this activity puts a burden on the machine on which it occurs (not only the mysqld inserts but also the scripts and http client activity), I am planning to dedicate one box to the crawling/inserting and replicate the data to one (or possibly more) other servers. These slave servers will handle all client requests (almost exclusively reads). The mysql documentation states: "You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves." My questions are: Since the updating occurs throughout the day and the same amount of data has to be eventually inserted into the slaves I assume the updating will require the same amount of resources (disk, cpu usage) on the slaves as on the master - the same number of writes will occur on the slaves. Correct. > So the the benefit of this configuration (in terms of performance) is that it is the extra processing required to do the crawling will be offloaded to the one master server, not the resources required for the mysql writes. Your benefit will be to spread the load from the client requests across multiple servers. If all you want to do is to limit the affect of the crawler, you could batch those up and add them during off hours. > I assume inserts done thru replication are not more efficient than regular inserts. Correct, replication inserts affect the db just as the original writes do. However, one nice thing that you can do to the slaves to limit the affect of inserts/updates is to turn on low priority updates. This prevents updates from locking out selects and makes your clients happier. (See the replication FAQ for more details on this: http://www.mysql.com/doc/en/Replication_FAQ.html) Also, as an alternative to batching the updates to the master as mentioned above, you could turn off replication during heavy times and turn it on until things catch up. This could easily be automated in a script. What is the performance hit of replication and is there a way to limit the effect of the writes on the slave servers, thru configuration parameters, for example, or would it even make sense to take the slave offline at regular intervals while replication is taking place? Performance hit for replication will just be extra load caused by the inserts from the master. The slave process has little or no extra overhead. As mentioned in the FAQ: You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write=ALL to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed. 2/ Is there any documentation on handling and configuring large tables? Hope this is not too confusing... Many thanks Todd Burke phbnyc.com Marc Prewitt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping data
Hi, Try mysqldump -T: -T| --tab=... Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysql‐ dump is run on the same machine as the mysqld daemon. This was taken from the man page. On Wed, 2003-06-25 at 08:56, Rob wrote: > Hi all, > > Is there any way to do a mysql dump in which each table is dumped into a > separate file. I know I can use the --tables option to specify a table, > but this means I have to type out each table name (and there are a lot > of them). Is there any way to get mysql to automatically iterate > through all the table names and dump each table to a separate file. The > reason for this is that the db is BIG and we don't want to have to lug > 200+mb files around. Plus mysql seems to have a real issue with dumping > large dbs into one file. We've tried about 5 - 10 times and we keep > getting corrupted data in the file. > > Thanks > > --- > Rob > > ** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ** -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect functionality
Hi Egor, Thank you for the reply. Do you mind to go abit mor into details ? It seems that I got domething wrong here. If nested queries are not only subselects, which other nested queries exist there ? Do you mind just writing a few samples, please ? Sorry if this seems a simple question - from the manual I misunderstood that nested queries and subselects are the same, which is obviously not the case as I heard now. I understood that Subselects originally exist of 2 or more SELECT... statements. Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 19:53、Egor Egorov さんは書きました: > "Nils Valentin" <[EMAIL PROTECTED]> wrote: > > I have a question regarding the MySQL feaures. > > > > From Version 4.1 Full subselect support was/is announced. > > > > However if I understood correctly then already from Version3.23-41 (or > > earlier) there are some subselect functions already included. For xample > > if I try this: > > > > CREATE TABLE tblname_new SELECT * FROM tblname_ori; > > > > then it works. Or do I have a misunderstanding of subselects (nested > > queries) ?? > > Nested queries is not only subselects. Yes, you are right, some nested > queries like INSERT .. SELECT, CREATE .. SELECT was implemented in earlier > than 4.1 > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.sock doesn't exist
Yes, I installed by RPM. I have now found the error file as you mentioned. The output for the last few days is: 030621 06:49:27 mysqld started 030621 6:49:28 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 030621 6:49:28 InnoDB: Flushing modified pages from the buffer pool... 030621 6:49:28 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.13-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 A mysqld process already exists at Mon Jun 23 10:55:32 CDT 2003 A mysqld process already exists at Mon Jun 23 10:59:02 CDT 2003 A mysqld process already exists at Mon Jun 23 11:19:42 CDT 2003 A mysqld process already exists at Mon Jun 23 11:21:32 CDT 2003 A mysqld process already exists at Mon Jun 23 12:17:43 CDT 2003 A mysqld process already exists at Mon Jun 23 13:26:53 CDT 2003 A mysqld process already exists at Mon Jun 23 16:04:24 CDT 2003 A mysqld process already exists at Mon Jun 23 16:04:31 CDT 2003 A mysqld process already exists at Mon Jun 23 16:21:38 CDT 2003 A mysqld process already exists at Tue Jun 24 08:02:47 CDT 2003 A mysqld process already exists at Tue Jun 24 13:27:15 CDT 2003 A mysqld process already exists at Tue Jun 24 13:31:30 CDT 2003 A mysqld process already exists at Tue Jun 24 16:09:39 CDT 2003 A mysqld process already exists at Tue Jun 24 16:37:12 CDT 2003 Was one of my installation attempts perhaps not removed cleanly? Kevin John Nichel wrote: Did you install by RPM? If so, look in /var/lib/mysql The file will be you machine name dot err, eg... localhost.err -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with farsi sorting!!
I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). thanx for your helps... - Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!
Re: comp-err.exe
At 12:54 25/6/2003 +0200, Morten Gulbrandsen wrote: Hi, for which purpose is this file included in the file C:\mysql\bin directory ? For to build the errmsg.sys files: Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. c:\mysql\bin>comp-err c:\mysql\share\english\errmsg.txt c:\temp\errmsg.sys Found 237 messages in language file c:\temp\errmsg.sys -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano <[EMAIL PROTECTED]> São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
Also doesnt work in 4.1 alpha. Best regards Nils Valentin Tokyo/Japan (As requested I took Roman of , as he doesnt like direct e-mails ;-) 2003年 6月 25日 水曜日 19:12、Roman Neuhauser さんは書きました: > # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: > > Sometime recently MaFai said: > > > A table contains a column named "mydate". > > > > > > //Wrong sql statement > > > alter table p_asset add mydate datetime default now(); > > > alter table p_asset add mydate datetime default time(); > > > alter table p_asset add mydate datetime default now; > > > alter table p_asset add mydate datetime default time; > > > alter table p_asset add mydate datetime default date(); > > > alter table p_asset add mydate datetime default datetime(); > > > > > > How can I add the default now value into the specified column? > > > I try to find in the mysql manual,but in the default value > > > charter,no relative information can be found. > > > > > >I also know this question is stupid,but hope you help. > > > > alter table p_asset add mydate datetime default 'now()'; > > > > - you need the '' around now(); apparently > > > > http://www.mysql.com/doc/en/DATETIME.html > > and scroll down to the comment made by > > Lazy Soul on Tuesday May 27 2003, @8:15am > > I'd like to know in which version of MySQL this does what it's > intended to do. Definitely not in 4.0.13: > > mysql> create table autotime (foo int, bar datetime default 'now()'); > Query OK, 0 rows affected (0.00 sec) > > mysql> insert autotime set foo = 1; > Query OK, 1 row affected (0.06 sec) > > mysql> insert autotime set foo = 2; > Query OK, 1 row affected (0.00 sec) > > mysql> select * from autotime; > +--+-+ > > | foo | bar | > > +--+-+ > > |1 | -00-00 00:00:00 | > |2 | -00-00 00:00:00 | > > +--+-+ > 2 rows in set (0.02 sec) > > mysql> select version(); > ++ > > | version() | > > ++ > > | 4.0.13-log | > > ++ > 1 row in set (0.00 sec) > > > -- > If you cc me or remove the list(s) completely I'll most likely ignore > your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect functionality
"Nils Valentin" <[EMAIL PROTECTED]> wrote: > > I have a question regarding the MySQL feaures. > > From Version 4.1 Full subselect support was/is announced. > > However if I understood correctly then already from Version3.23-41 (or > earlier) there are some subselect functions already included. For xample if I > try this: > > CREATE TABLE tblname_new SELECT * FROM tblname_ori; > > then it works. Or do I have a misunderstanding of subselects (nested queries) > ?? Nested queries is not only subselects. Yes, you are right, some nested queries like INSERT .. SELECT, CREATE .. SELECT was implemented in earlier than 4.1 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: comp-err.exe
"Morten Gulbrandsen" <[EMAIL PROTECTED]> wrote: > Compiler errormessage ver 1.3 > > C:\mysql\bin>comp-err.exe -I > comp-err.exe (Compile errormessage) Ver 1.3 > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > > Usage: comp-err.exe [-?] [-I] [-V] fromfile[s] tofile > Options: -Info -Version > > Hello Programmers, > > for which purpose is this file included in the file > C:\mysql\bin directory ? It's used for generating error message file: http://www.mysql.com/doc/en/Languages.html > I have tried to RTFM and I did not find any entry about > This specific file, perhaps it has another name under linux, > I am using windows and would like to know what useful tasks > Can be accomplished with comp-err.exe under windows 2000. -- 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: MyISAM
Cedric Gavage <[EMAIL PROTECTED]> wrote: > > I have a question about MyISAM, during an UPDATE for a row, is it a row > locking or a table locking? MySQL locks the whole MyISAM 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: Initializing primary key values for existing table
# [EMAIL PROTECTED] / 2003-06-25 02:11:05 -0400:
> I needed to add a new, autoincrementing, primary key column
> to a table and have been struggling to figure out how to
> assign an initial, unique value to each row.
>
> I finally accomplished my task, but feel sure there's an
> easier way.
>
> Here is my solution:
>
> 1. Add the column:
> alter table mytable add mycolumn int auto_increment;
>
> 2. Set up a user variable:
> @mycounter = 0;
>
> 3. Assign the initial values by incrementing the counter:
> Update mytable set mycolumn = max((@mycounter := @mycounter
> + 1), @mycounter);
>
> 4. Finally, set the column to be the primary key:
> alter table mytable set primary key mycolumn;
>
> This seems like a roundabout way of doing things. Can any
> of you improve on it?
doesn't simply adding the column + primary key in one ALTER TABLE
do what you want?
mysql> create table test (a char);
Query OK, 0 rows affected (0.00 sec)
mysql> insert test values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table test add b int auto_increment, add primary key (b);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+--+---+
| a| b |
+--+---+
| a| 1 |
| b| 2 |
| c| 3 |
+--+---+
3 rows in set (0.00 sec)
mysql> desc test;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| a | char(1) | YES | | NULL||
| b | int(11) | | PRI | NULL| auto_increment |
+---+-+--+-+-++
2 rows in set (0.00 sec)
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comp-err.exe
Compiler errormessage ver 1.3 C:\mysql\bin>comp-err.exe -I comp-err.exe (Compile errormessage) Ver 1.3 This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: comp-err.exe [-?] [-I] [-V] fromfile[s] tofile Options: -Info -Version Hello Programmers, for which purpose is this file included in the file C:\mysql\bin directory ? I have tried to RTFM and I did not find any entry about This specific file, perhaps it has another name under linux, I am using windows and would like to know what useful tasks Can be accomplished with comp-err.exe under windows 2000. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting default auto_increment start int
"Javan B." <[EMAIL PROTECTED]> wrote: > > Just a quick question: How do I set the auto increment > for a field to start from an integer other than 1. > > For example, I would like to be able to have the auto > increment counter start from 1000. > Use AUTO_INCREMENT option of CREATE TABLE statement: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
can you tell me how
any one can tell me how can i use the MySQL ++ function in CBuilder 6?? i include it in my project but i got linker error
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 07:10:46 +0100: > Sometime recently Roman Neuhauser said: > > # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: > > > alter table p_asset add mydate datetime default 'now()'; > > > > > > - you need the '' around now(); apparently > > > > > > http://www.mysql.com/doc/en/DATETIME.html > > > and scroll down to the comment made by > > > Lazy Soul on Tuesday May 27 2003, @8:15am > > > > I'd like to know in which version of MySQL this does what it's > > intended to do. Definitely not in 4.0.13: > > > > mysql> create table autotime (foo int, bar datetime default 'now()'); > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> insert autotime set foo = 1; > > Query OK, 1 row affected (0.06 sec) > > > > mysql> insert autotime set foo = 2; > > Query OK, 1 row affected (0.00 sec) > > > > mysql> select * from autotime; > > +--+-+ > > | foo | bar | > > +--+-+ > > |1 | -00-00 00:00:00 | > > |2 | -00-00 00:00:00 | > > +--+-+ > > 2 rows in set (0.02 sec) > > You're right, just tested it myself on 3.23.41; and I get exactly > the same result as you. We should post an amendment to the docs > on the website. > > However, (still with 3.23.41): > > mysql> create table autotime2 (foo int, bar timestamp default now()); > ERROR 1064: You have an error in your SQL syntax near 'now())' at line 1 > mysql> create table autotime2 (foo int, bar timestamp default 'now()'); > Query OK, 0 rows affected (0.00 sec) > > mysql> insert autotime2 set foo = 2; > Query OK, 1 row affected (0.00 sec) > > mysql> insert autotime2 set foo = 1; > Query OK, 1 row affected (0.00 sec) > > mysql> select * from autotime2; > +--++ > | foo | bar| > +--++ > |2 | 20030625102215 | > |1 | 2003062510 | > +--++ > 2 rows in set (0.00 sec) mysql> create table dt (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql> create table ts (foo int, bar timestamp default 'now()'); Query OK, 0 rows affected (0.02 sec) mysql> desc dt; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | foo | int(11) | YES | | NULL| | | bar | datetime | YES | | -00-00 00:00:00 | | +---+--+--+-+-+---+ 2 rows in set (0.02 sec) mysql> desc ts; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | foo | int(11) | YES | | NULL| | | bar | timestamp(14) | YES | | NULL| | +---+---+--+-+-+---+ 2 rows in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_pconnect
Hi My server www has Suse 8.2, apache-1.3.27, mod_php4-4.3.1, and my server mysql has Suse 8.2, and mysql.4.0.12-Max-log Sometimes, not always, it appears an error in www. Warning: mysql_pconnect() [function.mysql-pconnect]: Link to server lost, unable to reconnect in db_mysql.inc on line 73 I have changes mysql_pconnect to mysql_connect and it seems that no error appears but cpu increases. Any solution ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
"Nils Valentin" <[EMAIL PROTECTED]> wrote: > I have some questions about temporary tables. I would appreciate any replies: > > I created a temporary table f.e. like this: > > mysql> create temporary table temp SELECT * FROM relations; > Query OK, 4 rows affected (2.35 sec) > Records: 4 Duplicates: 0 Warnings: 0 > > When I do > > mysql> show create table temp; > > Then it will give me this: > > | temp | CREATE TEMPORARY TABLE `temp` ( > `member_id` int(16) NOT NULL default '0', > `company_id` int(16) NOT NULL default '0', > `membership_id` int(16) NOT NULL default '0' > ) TYPE=MyISAM CHARSET=latin1 | > > > Now what confuses me is that > > a) it says here TYPE=MyISAM Because MyISAM is default table type > b) I understood that only HEAP tables are stored in the memory. Yes. > I was assuming (until now) that HEAP tables are (the only one type of) > temporary tables. Temporary tables may have type HEAP. > I tried > > CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; > or > CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; > > and they are all created as in-memory tables no files are created. > Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. Temporary table files are created in the temporary directory: http://www.mysql.com/doc/en/Temporary_files.html > If, so what would be the difference between a temporary table in general and a > HEAP table ? Temporary tables are the per-connection tables. HEAP tables are stored in memory 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: datetime column dummy question
You're right, just tested it myself on 3.23.41; and I get exactly the same result as you. We should post an amendment to the docs on the website. However, (still with 3.23.41): mysql> create table autotime2 (foo int, bar timestamp default now()); ERROR 1064: You have an error in your SQL syntax near 'now())' at line 1 mysql> create table autotime2 (foo int, bar timestamp default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql> insert autotime2 set foo = 2; Query OK, 1 row affected (0.00 sec) mysql> insert autotime2 set foo = 1; Query OK, 1 row affected (0.00 sec) mysql> select * from autotime2; +--++ | foo | bar| +--++ |2 | 20030625102215 | |1 | 2003062510 | +--++ 2 rows in set (0.00 sec) Cheers, Jan Sometime recently Roman Neuhauser said: > # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: > > Sometime recently MaFai said: > > > A table contains a column named "mydate". > > > > > > //Wrong sql statement > > > alter table p_asset add mydate datetime default now(); > > > alter table p_asset add mydate datetime default time(); > > > alter table p_asset add mydate datetime default now; > > > alter table p_asset add mydate datetime default time; > > > alter table p_asset add mydate datetime default date(); > > > alter table p_asset add mydate datetime default datetime(); > > > > > > How can I add the default now value into the specified column? > > > I try to find in the mysql manual,but in the default value charter,no > > > relative information can be found. > > > > > >I also know this question is stupid,but hope you help. > > > > alter table p_asset add mydate datetime default 'now()'; > > > > - you need the '' around now(); apparently > > > > http://www.mysql.com/doc/en/DATETIME.html > > and scroll down to the comment made by > > Lazy Soul on Tuesday May 27 2003, @8:15am > > I'd like to know in which version of MySQL this does what it's > intended to do. Definitely not in 4.0.13: > > mysql> create table autotime (foo int, bar datetime default 'now()'); > Query OK, 0 rows affected (0.00 sec) > > mysql> insert autotime set foo = 1; > Query OK, 1 row affected (0.06 sec) > > mysql> insert autotime set foo = 2; > Query OK, 1 row affected (0.00 sec) > > mysql> select * from autotime; > +--+-+ > | foo | bar | > +--+-+ > |1 | -00-00 00:00:00 | > |2 | -00-00 00:00:00 | > +--+-+ > 2 rows in set (0.02 sec) > > mysql> select version(); > ++ > | version() | > ++ > | 4.0.13-log | > ++ > 1 row in set (0.00 sec) > > > -- > If you cc me or remove the list(s) completely I'll most likely ignore > your message.see http://www.eyrie.org./~eagle/faqs/questions.html > -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: > Sometime recently MaFai said: > > A table contains a column named "mydate". > > > > //Wrong sql statement > > alter table p_asset add mydate datetime default now(); > > alter table p_asset add mydate datetime default time(); > > alter table p_asset add mydate datetime default now; > > alter table p_asset add mydate datetime default time; > > alter table p_asset add mydate datetime default date(); > > alter table p_asset add mydate datetime default datetime(); > > > > How can I add the default now value into the specified column? > > I try to find in the mysql manual,but in the default value charter,no relative > > information can be found. > > > >I also know this question is stupid,but hope you help. > > alter table p_asset add mydate datetime default 'now()'; > > - you need the '' around now(); apparently > > http://www.mysql.com/doc/en/DATETIME.html > and scroll down to the comment made by > Lazy Soul on Tuesday May 27 2003, @8:15am I'd like to know in which version of MySQL this does what it's intended to do. Definitely not in 4.0.13: mysql> create table autotime (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql> insert autotime set foo = 1; Query OK, 1 row affected (0.06 sec) mysql> insert autotime set foo = 2; Query OK, 1 row affected (0.00 sec) mysql> select * from autotime; +--+-+ | foo | bar | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | +--+-+ 2 rows in set (0.02 sec) mysql> select version(); ++ | version() | ++ | 4.0.13-log | ++ 1 row in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AES 256-Bit Encryption and /etc/my.cnf
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, Daevid Vincent wrote: > It would be really nice if ANY of these kinds of configuration options > were settable via the /etc/my.cnf file. I use RPM's almost exclusively > for their ease of maintenance, compatibility and stability and I > certainly wouldn't want to go and have to re-compile anything just to > change a value from 128 to 256. As a feature request/suggestion on > behalf of every other RPM (or package such as .deb or .mdk or whatever) > user, I implore you guys at mysql.com to consider this going forward. Thanks for your suggestion, which I will forward to the developer in charge of that part of the code. However, I am not sure if we can fulfil this request. Enabling 256 bit encryption by default may pose legal problems (export regulations), but IANAL. We shall see. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD4DBQE++XVLSVDhKrJykfIRAltdAJjFMt9d98O0nvALrujhDQiP6D7IAJ9h/1VS jr/u+yS9CYe1ZOIyvVd6dg== =H0PG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting Countries
insert one space before US ;) in recieving do trim if not HTML output -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 7:32 AM To: mySQL Mailing List Subject: Sorting Countries I am getting a list of all countries from database, and then I am sorting by country name. However since most orders will be from US I want the US to appear first over the rest of the countries. How can I go about doing this? Currently, this is my query: SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY countries_name; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
>>> SELECT * FROM machines INNER JOIN people ON > >>> machines.peopleID=people.peopleID; > >> > >> Inludes NULL records I meant the first statement returns also empty record fields or should I say incomplete data records ? > >> > >>> Is not producing the same results as this: > >>> > >>> SELECT lastname, model FROM people, machines WHERE machines.peopleID > >>> = > >>> people.peopleID; > >> > >> Dosent include NULL records This one doesnt return any entries with incomplete data records (no data in them). So if you have an entry for machines.peopleID but not for people.peopeID than it wont show up while it does in the first statement. Does it make it a bit clearer ? Sorry dont know how to better explain it really. Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 17:[EMAIL PROTECTED] さんは書きました: > What do you mean when you say, in this case, "does not return the NULL > record"? > > I have no records that are completely "NULL" though some records do > have a NULL field or two? > > Thanks, > Ted > > On Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: > > Hi tlr7425, > > > > Besides what I said in my last e-mail I completely overlooked that > > > >>> SELECT * FROM machines INNER JOIN people ON > >>> machines.machinesID=people.peopleID; > > > > should be > > > > SELECT * FROM machines INNER JOIN people ON > > machines.peopleID=people.peopleID; > > > > Even though my first statement should still remain correct. The > > statement > > above does not return the NULL record while the other one does INNER > > JOIN) > > > > Best regards > > > > Nils Valentin > > Tokyo/Japan > > > > 2003年 6月 25日 水曜日 15:37、Nils Valentin さんは書きました: > >> 2003年 6月 25日 水曜日 15:[EMAIL PROTECTED] さんは書きました: > >>> This: > >>> > >>> SELECT * FROM machines INNER JOIN people ON > >>> machines.machinesID=people.peopleID; > >> > >> Inludes NULL records > >> > >>> Is not producing the same results as this: > >>> > >>> SELECT lastname, model FROM people, machines WHERE machines.peopleID > >>> = > >>> people.peopleID; > >> > >> Dosent include NULL records > >> > >>> Can someone please tell why, what's wrong? > >> > >> Nothing is wrong thats the way it is supposed to be. > >> > >>> (What happens is that the wrong person are listed with the wrong > >>> machine -using the INNER JOIN shown above, the second statement I > >>> listed works as expected, right person with right machine.) > >>> > >>> Thanks in Advance, > >>> Ted Rogers > >> > >> This is a good example which is also described on page 202-203 Michael > >> Kofler Mysql (Edition1 ) > >> > >> Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default value does not set using LOAD DATA INFILE ...
# [EMAIL PROTECTED] / 2003-06-25 15:18:09 +1000: > From: "Roman Neuhauser" <[EMAIL PROTECTED]> > > please don't piggyback on other people's threads. you can start your > > own for free. > > Although it was the first time for me to send the post on this list, I was > sure that I did not follow other people's threads. I checked my original > post again after read your response, and I did start with a new thread. > However, I apologize if somehow I did make any mistake. What I was referring to was the fact that you posted to the list by hitting the reply button (or whatever technic you use) to an unrelated post, replaced the subject, and sent it to the list. doing it this way leaves certain headers in you post that make it look like it's a reply to the message you "abused" this way, which screws threading in mail clients that can do such thing. > > # [EMAIL PROTECTED] / 2003-06-24 09:46:24 +1000: > > > I am using LOAD DATA INFILE to import some data to MySQL. I would > > > like to leave some fields blank and use the default value set in > > > the tables. I have spent many hours trying to find the problem, > > > but no luck so far. According to the manual, it should work. I > > > am hoping some one on this list would help me. The following is > > > sql query string I used: > > > > > > LOAD DATA LOCAL INFILE \' file \' REPLACE INTO TABLE table > > > FIELDS TERMINATED BY \'terminator\' ENCLOSED BY \'\"\' > > LOAD DATA LOCAL INFILE 'file' > > REPLACE > > INTO TABLE table > > FIELDS > > TERMINATED BY 'terminator' > > ENCLOSED BY '"' > > (col1, col3, col5) > I tried every thing I could think following the manual for LOAD DATA > before I joined this list. I probably did not explain my problem > clearly in my initial post. What I try to do is to import a csv data > file into MySQL using "LOAD DATA INFILE ...". I would like to leave > some fields blank in the csv file, and let MySQL to set default values > to those fields in the database when loading the data. But every > thing goes fine except the default values do not set. You made yourself quite clear in your original post. Does what I suggested not do what you want? Send in: * output of the DESCRIBE command for your table * the actual LOAD DATA command you used * SELECT * after LOADing a few lines of sample data into the table (just the imported lines) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SCO UnixWare porting of MYSQL
Hi ! Just wanted to know if there is any installation procedure available on = UnixWare 7.1.x. There is patch available for SCO UnixWare libz.so, which = I have downloaded but Iam not able to initialize ./Configure script. = Any help/support will be appreciated. Thanks In Advance Regards Sudhipan - Sudhipan Sharma SCO Group - formerly Caldera International. 56, Janpath New Delhi - 110 001 INDIA. Ph: 91-11-23736466, Fax : +91-11-23359997 Web :http://www.sco.com
Re: datetime column dummy question
alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am Jan Janice Wright Ingenta plc [EMAIL PROTECTED] http://www.ingentaselect.com/ Sometime recently MaFai said: > Hello, mysql, > > A table contains a column named "mydate". > > //Wrong sql statement > alter table p_asset add mydate datetime default now(); > alter table p_asset add mydate datetime default time(); > alter table p_asset add mydate datetime default now; > alter table p_asset add mydate datetime default time; > alter table p_asset add mydate datetime default date(); > alter table p_asset add mydate datetime default datetime(); > > > How can I add the default now value into the specified column? > I try to find in the mysql manual,but in the default value charter,no relative > information can be found. > >I also know this question is stupid,but hope you help. > > > > > Best regards. > > MaFai > [EMAIL PROTECTED] > 2003-06-25 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 12:05:49 +0800: > //Wrong sql statement > alter table p_asset add mydate datetime default now(); > alter table p_asset add mydate datetime default time(); > alter table p_asset add mydate datetime default now; > alter table p_asset add mydate datetime default time; > alter table p_asset add mydate datetime default date(); > alter table p_asset add mydate datetime default datetime(); > > > How can I add the default now value into the specified column? > I try to find in the mysql manual,but in the default value > charter,no relative information can be found. There *is* relevant information in the manual: http://www.mysql.com/doc/en/CREATE_TABLE.html: A DEFAULT value has to be a constant, it cannot be a function or an expression. http://www.mysql.com/doc/en/DATETIME.html: The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: * The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. * The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) * You explicitly set the TIMESTAMP column to NULL. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in last CVS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, Dyego Souza do Carmo wrote: > try to bk pull the mysql 4.0 from CVS tree... > > after I run: > > aclocal; autoheader; autoconf; automake > > inside dir... > > > the error is: > > autoconf: Undefined macros: > configure.in:8:AC_CONFIG_HEADERS(config.h) > configure.in: 8: `automake requires `AM_CONFIG_HEADER', not `AC_CONFIG_HEADER' What versions of autoconf/automake do you use? Seems like this new construct requires newer version of the toolchain. As a simple workaround, simply replace AC_CONFIG_HEADERS with AM_CONFIG_HEADER again. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE++WNrSVDhKrJykfIRArF+AJ0RyevuTDhPfzzB23Y1WgFBSTBzlACeN5Yo mZkRI0nUBFEXDsbhO1I2h74= =u3Hu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Conection to MySQL
Installed MySQL v4.0.13 on RH8, with phpMyAdmin-2.5.1 and PHP v4. The phpMyadmin is installed on another server (gateway) while MySQL and PHP is installed on a separate server. When the programmer tries to access MySQL he gets the following message: Error 2003 Cannot connect to MySQL server 203.153.229.84 (10061). Anyone know why this is happening. My my.cnf file has the following: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=3308 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I have the phpMyAdmin port set to 3308. Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au "I don't know the key to success, but the key to failure is trying to please everybody." -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Countries
On 25-Jun-2003 Ralph wrote: > I am getting a list of all countries from database, and then I am > sorting by country name. However since most orders will be from US I > want the US to appear first over the rest of the countries. How can I go > about doing this? > > Currently, this is my query: > > SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY > countries_name; "SELECT IF(countries_id='US', 0, 1) as ctsort, countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY ctsort, countries_name" Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about desc command
On 25-Jun-2003 MaFai wrote: > Hello, mysql, > > After create a table,it can use desc tablename to describe the table > scheme. > But it can not demonstrate that the column is unique or not.It just > simplely show the column is MUL type. > How can I get more information from the table by using mysql > commend?(Except use mysqldump to read the sql file) > >or How can I type command to show the table structure? > mysql>type some command > mysql>create tabe > //Show the specifing created table sql information DESC tbl; SHOW INDEX FROM tbl; SHOW CREATE TABLE tbl; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large file : InnoDB or MyISAM
Hi to all, I will have to do with a very big file (approx 600 millions of records). Which is the best table handler for this king of table : InnoDB or MyISAM (many INSERT and MANY SELECT, no UPDATE - statistics file). Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
What do you mean when you say, in this case, "does not return the NULL (Brecord"? (B (BI have no records that are completely "NULL" though some records do (Bhave a NULL field or two? (B (BThanks, (BTed (B (B (BOn Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: (B (B> Hi tlr7425, (B> (B> Besides what I said in my last e-mail I completely overlooked that (B> (B>>> SELECT * FROM machines INNER JOIN people ON (B>>> machines.machinesID=people.peopleID; (B> (B> should be (B> (B> SELECT * FROM machines INNER JOIN people ON (B> machines.peopleID=people.peopleID; (B> (B> Even though my first statement should still remain correct. The (B> statement (B> above does not return the NULL record while the other one does INNER (B> JOIN) (B> (B> Best regards (B> (B> Nils Valentin (B> Tokyo/Japan (B> (B> (B> 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:37$B!"(BNils Valentin $B$5$s$O=q$-$^$7$?(B: (B>> 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:01$B!"([EMAIL PROTECTED] $B$5$s$O=q$-$^$7$?(B: (B>>> This: (B>>> (B>>> SELECT * FROM machines INNER JOIN people ON (B>>> machines.machinesID=people.peopleID; (B>> (B>> Inludes NULL records (B>> (B>>> Is not producing the same results as this: (B>>> (B>>> SELECT lastname, model FROM people, machines WHERE machines.peopleID (B>>> = (B>>> people.peopleID; (B>> (B>> Dosent include NULL records (B>> (B>>> Can someone please tell why, what's wrong? (B>> (B>> Nothing is wrong thats the way it is supposed to be. (B>> (B>>> (What happens is that the wrong person are listed with the wrong (B>>> machine -using the INNER JOIN shown above, the second statement I (B>>> listed works as expected, right person with right machine.) (B>>> (B>>> Thanks in Advance, (B>>> Ted Rogers (B>> (B>> This is a good example which is also described on page 202-203 Michael (B>> Kofler Mysql (Edition1 ) (B>> (B>> Best regards
MyISAM
Hi all, I have a question about MyISAM, during an UPDATE for a row, is it a row locking or a table locking? -- Cedric Gavage <[EMAIL PROTECTED]> http://unixtech.be - http://gavage.com - OpenPGP: 0xED325C64 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB file...
I.-A., - Original Message - From: ""I-A.Kotopoulos"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 25, 2003 10:05 AM Subject: InnoDB file... > --=_NextPart_000_00B5_01C33B01.642AD2C0 > Content-Type: text/plain; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > When should I use the autoextend option for the InnoDB file...Even after = > emptying the tables the files keeps its size and for new insertions it = > gets even bigger instead of using the already allocated space(which I = > suppose should be available after emptyibg the tables). yes, it should free the space if you empty the table. The size of ibdata1 will stay the same, but other tables can use the freed space. Are you sure you do not have long-running transactions dangling? Use SHOW INNODB STATUS\G to print a list of transactions. The purge operation cannot remove delete-marked rows if there are old transactions which could still see them. > thank you friends > aik_b > > p.s. MySQL doesn't seem to do a good usage of the disk space and the = > InnoDB file size and its density..anyway Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping data
Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** All information contained in this email is confidential and may be used by the intended recipient only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone had a chance to try an Opteron yet?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, David Griffiths wrote: > I'm surprised there is not more interest in this; is it that not many > work with large-ish (10+ gig) databases that need high-end performance? Many of our customers do. > A 64-bit CPU won't have the 4-gig memory limit that a 32-bit processor > will; even worse, Linux is apparently limited to about a 2-gig process. It depends - there is a BIGMEM patch from Andrea Arcangeli that raised that limit to 3.5 GB on 32bit systems. I think the patch is in the mainline kernel as well by now. > SuSe Enterprise Linux supports 512-gigabyte processes with 16 > processors. Imagine 10 gigabyte database all in memory. > > Even better, larger file sizes - no more 2-gig files. Max file size is > 9-Exabytes ( "9" followed by 18 "0's" ). All the posts I see about > people trying to get around the 2 gig file limit should be really > excitied. Actually, you can create larger files on 32bit Linux systems as well. It's just that the file system and the C library must have support for LFS (Large File Support): http://www.suse.de/~aj/linux_lfs.html > I guess I'm just surprised by lack of interest. I've been bugging our > CTO once a week about this, and hopefully should have a server on my > desk by mid summer to late fall. Have fun with it! > If you're interested, SuSe has a good PDF on AMD64 and SuSe Enterprise Linux > 1.0: > > http://www.suse.com/en/business/products/server/sles/misc/sles8_amd64.pdf > > Anyone have some practical experience with the software and hardware? Yes, we do have two AMD64 systems (one dual Opteron with 1GB of RAM), running SLES here. Quite impressive. We also have some Itanium (1/2) systems and SGI kindly gave us access to an "Altix" system (16 x 900MHz Intel Itanium-2, 32GB of RAM). So MySQL runs quite happily on 64bit systems. The main benefit of a 64 bit platform is that MySQL can handle more concurrent threads and that you can give more than 4GB of memory to MySQL buffers. The disadvantage is that MySQL will be about 3-5 % slower because of the extra memory usage 64 bit pointers require (Structures are aligned to be 64 bit aligned, which makes the structures bigger and reduces the CPU cache usage, more stack space is used for registers). We tested this on Sun Solaris, using the Sun Forte compilers, but I assume this applies to other architectures as well. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE++VFtSVDhKrJykfIRAsLHAJ4nx0SfyJxBhtMQW+nodnjXNArSdQCeKxL8 G6ScGyAcT8tN4mI++T1K7j4= =7ti/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem setting/activating password
I don't know if this apply when using GRANT but have you tried afterwards: > flush privileges; HTH, Lian > -Original Message- > From: Riaan Oberholzer [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 25, 2003 9:19 AM > To: [EMAIL PROTECTED] > Subject: Problem setting/activating password > > > Hi, > > I am created a user by using: > > GRANT ALL ON *.* TO username@"%" IDENTIFIED BY > "password" > > I did this from the mysql command line tool, logged in > as root. The command succeeds and the new user is > created in the mysql.user table, but the password does > not work. I can only use this new user if I use a > empty string password (no password). > > I also tried to do the SET PASSWORD afterwords, no > luck. I then tried the Windows GUI / Administration to > grant access and then set the password, but again, > only an empty string password is accepted. > > I am using mysql 4.0.13. > > How do I actually get the password validation to be > activated and instruct the server that the new user > must provide his password? > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subselect functionality
Hi MySQL Fans ;-), I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB file...
When should I use the autoextend option for the InnoDB file...Even after emptying the tables the files keeps its size and for new insertions it gets even bigger instead of using the already allocated space(which I suppose should be available after emptyibg the tables). thank you friends aik_b p.s. MySQL doesn't seem to do a good usage of the disk space and the InnoDB file size and its density..anyway
