Re: string->array question. ( 2 query questions)

[Peter Brawley]

YL,
(2) the rows in the table are
mysql> select * from tt;
++--+-+
| id | name | options |
++--+-+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
++--+-+

>(4) My intention is a query like
>mysql> select id,name from tt where 7 in options;
>and expect
>++--+
>| id | name |
>++--+
>|  1 | abc  |
>|  2 | bbc  |
>|  3 | cbc  |
>++--+

>This is obviously wrong since column 'options' is of type varchar
>but look at the values of options, they are like arrays. So my question
>is how to fix query (4)? In other words, is there any function that
>i can use to get the result illustrated in (4)?
SQL doesn't do arrays, indeed keeping more than one value in one column
breaks a basic rule of database design, but MySQL does have a column
type for storing multiple values
(http://dev.mysql.com/doc/mysql/en/set.html), so one solution available
to you is to make your options column a SET column. If your options
column values are ints, though, your database would be sounder and (not
accidentally) your query would be much simpler if you were to project
the options values out to a child table.

PB

-

YL wrote:

  My 1st question, can be understood after 3 steps below:
(1) Suppose we have a table defined as:
mysql> desc tt;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| id  | int(11) |  | | 0   |   |
| name| varchar(32) | YES  | | NULL|   |
| options | varchar(32) | YES  | | NULL|   |
+-+-+--+-+-+---+
(2) the rows in the table are
mysql> select * from tt;
++--+-+
| id | name | options |
++--+-+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
++--+-+
(3) the names of the matches of rows whoes id's are in an given array:
mysql> select name from tt where id in (2,3);
+--+
| name |
+--+
| bbc  |
| cbc  |
+--+
(4) My intention is a query like
mysql> select id,name from tt where 7 in options;
and expect
++--+
| id | name |
++--+
|  1 | abc  |
|  2 | bbc  |
|  3 | cbc  |
++--+

This is obviously wrong since column 'options' is of type varchar
but look at the values of options, they are like arrays. So my question
is how to fix query (4)? In other words, is there any function that
i can use to get the result illustrated in (4)?

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "YL" <[EMAIL PROTECTED]>
Cc: 
Sent: Friday, September 02, 2005 2:29 PM
Subject: Re: 2 query questions


  
  
Answers blended in

"YL" <[EMAIL PROTECTED]> wrote on 09/02/2005 04:18:27 PM:



  I've 2 basic questions. thanks for responses:

(1) Suppose i have a table with a varchar colum named options which
  

stores


  option id's in the form
 "(id1, id2, , idk)", where id1,... are string representations
  

of


  positive integers. Given an integer i,
 I want to fetch all ID's for the records in the table whoes
  

'options'


  value when interprets as an array,
 contains integer i. What the query looks like?
  


I do not understand your question. SQL does not have arrays. It has rows
and columns and tables and lots of other things but no arrays. Programming
languages have arrays, not SQL. Are you trying to get some data and
populate an array with it? That's not completely a MySQL question, it's
more of a question about how to create arrays from strings in your
particular language. However, since there are MANY people on this list, I
am sure someone will know the answer so please explain what it is you are
doing, what language you are using to do it with, what your data looks
like (use "SHOW CREATE TABLE", and explain what you want your results to
look like.



  (2) when using mysql command to query a value of type text, the
  

displayed


  value always surrounded by lots
'-'s. My question is: How to get rid of those extras? (The following is
  

a


  tail of display result:)
  template = {
   b5 = resources/articles/_backups/subIndex_b5.template;
   gb = resources/articles/_backups/subIndex_gb.template;
   };
   };
   };


  

  
  |---
  
  

  

  

--

  
  --
  
  

  ---

  

--

  
  --
  
  

  -

string->array question. ( 2 query questions)

[YL]

My 1st question, can be understood after 3 steps below:
(1) Suppose we have a table defined as:
mysql> desc tt;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| id  | int(11) |  | | 0   |   |
| name| varchar(32) | YES  | | NULL|   |
| options | varchar(32) | YES  | | NULL|   |
+-+-+--+-+-+---+
(2) the rows in the table are
mysql> select * from tt;
++--+-+
| id | name | options |
++--+-+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
++--+-+
(3) the names of the matches of rows whoes id's are in an given array:
mysql> select name from tt where id in (2,3);
+--+
| name |
+--+
| bbc  |
| cbc  |
+--+
(4) My intention is a query like
mysql> select id,name from tt where 7 in options;
and expect
++--+
| id | name |
++--+
|  1 | abc  |
|  2 | bbc  |
|  3 | cbc  |
++--+

This is obviously wrong since column 'options' is of type varchar
but look at the values of options, they are like arrays. So my question
is how to fix query (4)? In other words, is there any function that
i can use to get the result illustrated in (4)?

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "YL" <[EMAIL PROTECTED]>
Cc: 
Sent: Friday, September 02, 2005 2:29 PM
Subject: Re: 2 query questions


> Answers blended in
>
> "YL" <[EMAIL PROTECTED]> wrote on 09/02/2005 04:18:27 PM:
>
> > I've 2 basic questions. thanks for responses:
> >
> > (1) Suppose i have a table with a varchar colum named options which
> stores
> > option id's in the form
> >  "(id1, id2, , idk)", where id1,... are string representations
> of
> > positive integers. Given an integer i,
> >  I want to fetch all ID's for the records in the table whoes
> 'options'
> > value when interprets as an array,
> >  contains integer i. What the query looks like?
>
>
> I do not understand your question. SQL does not have arrays. It has rows
> and columns and tables and lots of other things but no arrays. Programming
> languages have arrays, not SQL. Are you trying to get some data and
> populate an array with it? That's not completely a MySQL question, it's
> more of a question about how to create arrays from strings in your
> particular language. However, since there are MANY people on this list, I
> am sure someone will know the answer so please explain what it is you are
> doing, what language you are using to do it with, what your data looks
> like (use "SHOW CREATE TABLE", and explain what you want your results to
> look like.
>
> >
> > (2) when using mysql command to query a value of type text, the
> displayed
> > value always surrounded by lots
> > '-'s. My question is: How to get rid of those extras? (The following is
> a
> > tail of display result:)
> >   template = {
> >b5 = resources/articles/_backups/subIndex_b5.template;
> >gb = resources/articles/_backups/subIndex_gb.template;
> >};
> >};
> >};
> >
> >
>
|---
> > 
> >
> --
--
> > ---
> >
> --
--
> > ---
> >
> --
--
> > ---
> >
> --
--
> > ---
> > --+
> >
>
> When using the command line client, the headers, the data areas, and the
> tails are each as wide as your data but they get "wrapped" to fit your
> screen buffer. To make the wrapping effect go away in Windows, change the
> Screen Buffer settings for your command shell window to something wide
> enough to fit your results. One way you can get to the settings is by
> right-clicking the window and picking "properties".
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>







Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Performance of DB with many tables

[Alan Williamson]

> But what about INNODB?  And are there
> table engine-independent implementation details which might cause
> performance problems for a database with many tables?

This thread got me wondering how many file-handles are open for INNODB tables
since it is only one large file on the file system.  A quick look at our 
production server that is running approximately 50 tables within an INNODB
instance, I see the number of open file handles is significantly less than
50.  Which is what i would have expected.

Looking at the other table formats, I do see a file handle open for every
single table (more than one file handle since there is multiple files that
describe a table under MyISAM).

BTW on Linux systems you can check this using:

  % lsof

-- 
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.
 Daily OS News @ http://compiledby.spikesource.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Multiple query execution (probably OT)

[Alvaro Cobo]

Hi guys:

This is probably an off-topic, and more related to PHP, but it is related to
mysql as well

I need to execute two insert queries into a single statement from a PHP
application, but it keeps giving me an error.

I have found this page
(http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html), but I don´t
know how to implement it:

"From version 4.1, MySQL supports the execution of multiple statements
specified in a single query string. To use this capability with a given
connection, you must specify the CLIENT_MULTI_STATEMENTS option in the flags
parameter of mysql_real_connect() when opening the connection. You can also
set this for an existing connection by calling MySQL Reference Manual ::
23.2.9 C API Handling of Multiple Query Execution mysql_set_server_option
MYSQL_OPTION_MULTI_STATEMENTS_ON) By default, mysql_query() and
mysql_real_query() return only the first query status and the subsequent
queries status can be processed using mysql_more_results() and
mysql_next_result()"

Does anybody has tried it in php. Does anyone could share an example.

Thanks and best regards.

Alvaro.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: querry problem ( datetime => Monday - Sunday )

[Michael Stassen]

inferno wrote:
> Hi,
>
>   I have a problem: I need to make a select for data that was entered
> more than 24 hours ago, but in that 24 hours I have to count only Monday
> - Friday since that is the working program, and does not have the status
> = '2' ( Solved ) and the problem is that I sincerly do not know how.
>   Any help/suggestions are apreciated since I am just a beginner.
>
> Best regards,
> Cristi Stoica
>
> P.S.: to give you a little idea on what I am using:
> ( the interface is coded in PHP for the users )
> MySQL 4.0.24 and the the data looks like this:
>
> 
+-+--+--+-+-++ 

> | Field   | Type | Null | Key | Default | 
Extra  |
> 
+-+--+--+-+-++ 

> | id  | int(25) unsigned |  | PRI | NULL| 
auto_increment |
> | client_name | varchar(100) |  | | | 
|
> | code| bigint(13)   |  | | 0   | 
|
> | status  | varchar(13)  |  | | 0   | 
|
> | date| datetime |  | | -00-00 00:00:00 | 
|
> 
+-+--+--+-+-++ 




[EMAIL PROTECTED] wrote:
> Hi Cristi,
> Look at this :
>
> mysql> select now();
> +-+
> | now()   |
> +-+
> | 2005-09-02 23:15:21 |
> +-+
> 1 row in set (0.00 sec)
>
> mysql> select DATE_ADD(now(), INTERVAL -1 DAY);
> +--+
> | DATE_ADD(now(), INTERVAL -1 DAY) |
> +--+
> | 2005-09-01 23:15:27  |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select date_format(now(),'%a');
> +-+
> | date_format(now(),'%a') |
> +-+
> | Fri |
> +-+
> 1 row in set (0.01 sec)
>
>
> So your query should be similar to  :
>
> Select * from tbl where status='2' and date <= DATE_ADD(now(),
> INTERVAL -1 DAY)
> and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');
>
>
> Hope that helps
> Mathias
>

inferno wrote:
> Hi,
>
>It is perfect, I was thinking of doing it in php but the solution
> that I've had was no way optimal.
>Thank you very much for the help.
>
> Best regards and have a nice week-end,
> Cristi Stoica

Are you sure?  I don't think that query does what you describe.  Mathias' 
query shows rows entered over 24 clock hours ago, but leaves out weekend 
rows.  I thought you wanted rows over 24 business hours old.  That is, if 
you run this query at 09:30 on a Monday, 24 hours ago means 09:30 last 
Friday.  Is that correct?  In other words, a row entered at 16:30 on Friday 
is not yet 24 business hours old at 09:30 on Monday, because weekends don't 
count.  Such an entry would be returned by Mathias' query.


If I'm right, you need a different query.  The key is that "yesterday" is 1 
day ago if today is Tuesday through Friday, but it is 3 days ago if today is 
Monday.  Hence, you need something like


  SET @daysago = IF(DAYNAME(CURDATE()) = 'Monday', 3, 1);
  SET @yesterday = NOW() - INTERVAL @daysago DAY;

  SELECT * FROM yourtable
  WHERE date <= @yesterday
AND status = '2';

You can do it in one query without user variables, if you like, but it's a 
little uglier:


  SELECT * FROM yourtable
  WHERE date <= NOW() - INTERVAL IF(DAYNAME(CURDATE()) = 'Monday', 3, 1) DAY
AND status = '2';

I've assumed the query will only be run on a business day.  If you need to 
be able to run this on the weekend and get correct results, it becomes a bit 
more complicated.  Something like:


  SET @yesterday =  CASE DAYNAME(CURDATE())
 WHEN 'Saturday' THEN CURDATE() - INTERVAL 1 DAY
 WHEN 'Sunday' THEN CURDATE() - INTERVAL 2 DAY 

 WHEN 'Monday' THEN NOW() - INTERVAL 3 DAY 

 ELSE NOW() - INTERVAL 1 DAY 


  END;

  SELECT * FROM yourtable
  WHERE date <= @yesterday
AND status = '2';

Again, you can do it in one query by replacing @yesterday in the SELECT with 
the CASE statement on the right side of the SET statement, but it's ugly.


For more information, see the manual:

Date and time functions


User variables


IF and CASE functions


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ERROR

[hueygeorge]

This message was undeliverable due to the following reason:

Your message was not delivered because the destination server was
not reachable within the allowed queue period. The amount of time
a message is queued before it is returned depends on local configura-
tion parameters.

Most likely there is a network problem that prevented delivery, but
it is also possible that the computer is turned off, or does not
have a mail system running right now.

Your message was not delivered within 3 days:
Server 222.125.129.225 is not responding.

The following recipients could not receive this message:


Please reply to [EMAIL PROTECTED]
if you feel this message to be in error.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with revoke all

[Scott Haneda]

on 9/2/05 4:10 PM, Michael Stassen at [EMAIL PROTECTED] wrote:

> So [EMAIL PROTECTED] is missing something (you can't GRANT a privilege you
> don't have).  In that case, I think you need to do as Gleb suggested.  Shut
> down mysqld, then start it with --skip-grant-tables and reissue
> 
>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
> 
> then stop and start the server again.

I take it my uptime value is going back to zero :-)

> Alternatively, you could try to do this witout stopping mysqld.  UPDATE
> [EMAIL PROTECTED]'s row in the mysql.user table to have the correct values,
> then FLUSH PRIVILEGES (but I'm suspicious something like that is how this
> started).

I never fiddle direct like that, but there was that fix_privs_table script
mysql provided I had to run at one time during an update.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with revoke all

[Michael Stassen]

Scott Haneda wrote:

on 9/2/05 9:08 AM, Michael Stassen at [EMAIL PROTECTED] wrote:



Modify them how?
show grants for 'root'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES,
EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost'
IDENTIFIED BY PASSWORD '' WITH GRANT OPTION

This looks pretty much full access to me?


I didn't look closely to see if anything's missing in that list, but full
access usually looks like this:

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
  '' WITH GRANT OPTION



And that is the odd thing, running that gives me:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)


So [EMAIL PROTECTED] is missing something (you can't GRANT a privilege you 
don't have).  In that case, I think you need to do as Gleb suggested.  Shut 
down mysqld, then start it with --skip-grant-tables and reissue


  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

then stop and start the server again.

Alternatively, you could try to do this witout stopping mysqld.  UPDATE 
[EMAIL PROTECTED]'s row in the mysql.user table to have the correct values, 
then FLUSH PRIVILEGES (but I'm suspicious something like that is how this 
started).


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with revoke all

[Scott Haneda]

on 9/2/05 9:08 AM, Michael Stassen at [EMAIL PROTECTED] wrote:

>> Modify them how?
>> show grants for 'root'@'localhost';
>> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
>> PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES,
>> EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost'
>> IDENTIFIED BY PASSWORD '' WITH GRANT OPTION
>> 
>> This looks pretty much full access to me?
> 
> I didn't look closely to see if anything's missing in that list, but full
> access usually looks like this:
> 
>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
>'' WITH GRANT OPTION

And that is the odd thing, running that gives me:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with revoke all

[Scott Haneda]

on 9/2/05 6:54 AM, Gleb Paharenko at [EMAIL PROTECTED] wrote:

> 
> I was confused by your "all I have is select privs." phrase in the
> 
> previous post. Please, send the output of the 'status' command.
> 
> What is in the 'Current user' field?

Sorry about the confusion:
mysql  Ver 12.22 Distrib 4.0.18, for apple-darwin6.8 (powerpc)

Connection id:  5793710
Current database:
Current user:   [EMAIL PROTECTED]
SSL:Not in use
Current pager:  stdout
Using outfile:  ''
Server version: 4.0.18-standard
Protocol version:   10
Connection: Localhost via UNIX socket
Client characterset:latin1
Server characterset:latin1
UNIX socket:/tmp/mysql.sock
Uptime: 311 days 20 hours 8 min 57 sec

Threads: 1  Questions: 46821849  Slow queries: 4396  Opens: 694346  Flush
tables: 1  Open tables: 64  Queries per second avg: 1.738
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to design junction table?

[Siegfried Heintze]

I have 100K job posting records and 40K job title records. There is a M:M
relationship here. I expect these tables to grow rapidly.

What is the best way to design a junction or link table? Do I need to create
a primary key?

My thought was no primary key, just two indices on each foreign key (FK).

Some folks feel you should always have a primary key. However, if I make
fkJobTitle the primary key, can it still have duplicates?

Some folks say I should have a composite primary key consisting of
fkJobTitle and fkJobPosting. This does buy me uniqueness. However, what is
the performance like when I only know the fkJobTitle and I want to find all
job postings for that title. Is it a linear search? If not, why not? I don't
know the fkJobPosting to exploit the primary key.

Some folks say I say I should have a separate auto increment integer
separate from fkJobPosting and fkJobTitle. Then I could make the "id" field
(what is your favorite naming convention for this field?) the primary key
and index fkJobTitle and fkJobPosting separately. But this means every time
I insert into the junction table, I have to update three index structures.
Is this a problem?

What is your opinion?

Lastly, I have learned that MySQL has a rich set of extensions for the SQL
syntax. Let's suppose I have several hundred jobs which may or may not have
been previously inserted into my data base already where each job contains a
posting and a title. For each one I have to look it up, insert it if it does
not already exist in the database, and, return the integer PK. 

So what SQL statements would I use to look up the job title, insert it if it
does not exist and return the integer PK. I could use SELECT and if that
fails, INSERT or vice versa. But a previous poster informed me there are
other statements like REPLACE or INSERT ...  ON DUPLICATE... and maybe there
are others. (Apparently REPLACE will INSERT if it is not already there). I
was using REPLACE and now that I am using V5 I can use INSERT... ON
DUPLICATE. Which would be best?

Thanks,
Siegfried


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to optimize fulltext selection?

[Michael Monashev]

Hello

GP> Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
GP> statements, amount of RAM, most problematic queries (use mysqldumpslow
GP> utility to find them) include tables' definitions (use SHOW CREATE
GP> TABLE).

SHOW VARIABLES;
back_log50
basedir /*/
binlog_cache_size   32768
bulk_insert_buffer_size 8388608
character_set_clientutf8
character_set_connectionutf8
character_set_database  cp1251
character_set_results   utf8
character_set_servercp1251
character_set_systemutf8
character_sets_dir  /***/share/mysql/charsets/
collation_connectionutf8_general_ci
collation_database  cp1251_general_ci
collation_servercp1251_general_ci
concurrent_insert   ON
connect_timeout 5
datadir /*/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit100
delayed_insert_timeout  300
delayed_queue_size  1000
expire_logs_days0
flush   OFF
flush_time  0
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit20
ft_stopword_file(built-in)
group_concat_max_len1024
have_archiveNO
have_bdbNO
have_compress   YES
have_crypt  YES
have_csvNO
have_example_engine NO
have_geometry   YES
have_innodb NO
have_isam   NO
have_ndbcluster NO
have_opensslNO
have_query_cacheYES
have_raid   NO
have_rtree_keys YES
have_symlinkYES
init_connect 
init_file
init_slave   
interactive_timeout 28800
join_buffer_size131072
key_buffer_size 536870912
key_cache_age_threshold 300
key_cache_block_size1024
key_cache_division_limit100
language/*/share/mysql/english/
large_files_support ON
license GPL
local_infileON
locked_in_memoryOFF
log OFF
log_bin OFF
log_error   /***/error.log
log_slave_updates   OFF
log_slow_queriesON
log_update  OFF
log_warnings1
long_query_time 10
low_priority_updatesOFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet  1047552
max_binlog_cache_size   4294967295
max_binlog_size 1073741824
max_connect_errors  10
max_connections 300
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads  20
max_join_size   4294967295
max_length_for_sort_data1024
max_relay_log_size  0
max_seeks_for_key   4294967295
max_sort_length 1024
max_tmp_tables  32
max_user_connections0
max_write_lock_count4294967295
myisam_data_pointer_size4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size   2147483647
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 67108864
net_buffer_length   16384
net_read_timeout30
net_retry_count 100
net_write_timeout   60
Variable_name   Value
new OFF
old_passwords   OFF
open_files_limit14781
pid_file/**/pid.file
port3306
preload_buffer_size 32768
protocol_version10
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit4096
query_cache_size16777216
query_cache_typeDEMAND
query_cache_wlock_invalidateOFF
query_prealloc_size 8192
range_alloc_block_size  2048
read_buffer_size2093056
read_only   OFF
read_rnd_buffer_size8384512
relay_log_purge ON
rpl_recovery_rank   0
secure_auth OFF
server_id   1
skip_external_locking   ON
skip_networking ON
skip_show_database  OFF
slave_net_timeout   3600
slow_launch_time2
socket  /tmp/.sock
sort_buffer_size2097144
sql_mode 
storage_engine  MyISAM
sync_binlog 0
sync_frmON
system_time_zoneMSD
table_cache 512
table_type  MyISAM
thread_cache_size   8
thread_stack196608
time_format %H:%i:%s
time_zone   SYSTEM
tmp_table_size  33554432
tmpdir   
transaction_alloc_block_size8192
transaction_prealloc_size   4096
tx_isolationREPEATABLE-READ
version 4.1.8-log
version_comment Source distribution
version_compile_machine i386
version_compile_os  unknown-freebsd5.2.1
wait_timeout28800


SHOW STATUS;
Aborted_clients 195
Aborted_connects161
Binlog_cache_disk_use   0
Binlog_cache_use0
Bytes_received  2375361016
Bytes_sent  1259738696
Com_admin_commands  2162461
Com_alter_db0
Com_alter_table 25
Com_analyze 0
Com_backup_table0
Com_begin   0
Com_change_db   778
Com_change_master   0
Com_check   0
Com_checksum0
Com_commit  0
Com_create_db   0
Com_create_function 0
Com_create_index0
Com_create_tabl

Re: Populate values in an Excel sheet from MySQL [SOLVED]

[Nick Jones]

Thanks to all for your help. I solved my problem the
quick and dirty way. Here's what I did:

1. Created my Excel spreadsheet to look the way I
wanted it, saved it as an XML spreadsheet.

2. Used PHP to pull the desired data from the MySQL
server on our iSeries machine.

3. PHP writes the XML data to a new text file using
the values from the MySQL DB and gives it the ".xls"
extension.

4. Browser is redirected to the new spreadsheet.

5. Spreadsheet opens like normal in IE for
saving/downloading. Or in Firefox it lets you download
it and it opens like a normal .xls spreadsheet.

Thanks to everyone here for their insight. I coudln't
have achieved it without all of you!

-Nick

--- inferno <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I had to do the same thing on a project and the
> problem was that if 
> you use CSV you will not be able to make a formated
> excel document.
> I am using now *Spreadsheet_Excel_Writer 
>  ( 
>
*http://pear.php.net/package/Spreadsheet_Excel_Writer
> ) and it does 
> everything I need, including formating the page for
> printing, color, 
> bold and boarder on cells and the best part is that
> it's no really hard 
> to use.
> If you change your mind and want to generate
> that from perl you also 
> have some PEAR packages for that, but I've sticked
> to PHP and with this 
> the problem was solved and I generate my data on
> access, custom build 
> depending on the select.
> 
> Best regards,
> Cristi Stoica
> 
> Arjan Hulshoff wrote:
> 
> >Hello Nick,
> >
> >This you can do with the MySQL ODBC Driver
> installed
>
>(http://dev.mysql.com/downloads/connector/odbc/3.51.html).
> Further more
> >you need to activate Microsoft ActiveX Data Objects
> in the references.
> >You can use the following code:
> >
> ><--Begin Code-->
> >Dim cn As ADODB.Connection
> >Dim rs As ADODB.RecordSet
> >
> >Set cn = New ADODB.Connection
> >Set rs = New ADODB.RecordSet
> >
> >cn.ConnectionString = "DRIVER={MySQL ODBC 3.51
>
>Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
> >name;PASSWORD=myPassword;OPTION=3;"
> >cn.Open
> >
> >sSQL = "SELECT * FROM database"
> >
> >rs.Open sSQL, cn
> >
> >If Not rs.BOF Then rs.MoveFirst
> >Do While Not rs.EOF
> > Cells(1, 1) = rs.Fields()' This line you
> can
> >adjust with your own code
> > rs.MoveNext
> >Loop
> >
> >On Error Resume Next ' This is my solution to make
> sure that the
> >recordset is always closed, _
> > without the errorhandling there
> >occurs an error when you use a query _
> > that doesn't return results
> >('INSERT' e.g.). If there is a better way _
> > to close the connection, then
> >let me know.
> >If rs.State = adStateOpen Then rs.Close
> >On Error Goto 0
> >cn.Close
> >
> >Set rs = Nothing
> >Set cn = nothing
> ><--End Code-->>
> >
> >HTH,
> >Arjan.
> >
> >-Original Message-
> >From: Nick Jones [mailto:[EMAIL PROTECTED] 
> >Sent: Thursday, September 01, 2005 08:23 PM
> >To: mysql@lists.mysql.com
> >Subject: Populate values in an Excel sheet from
> MySQL
> >
> >Does anyone know if it is possible to populate
> values into an Excel
> >spreadsheet from a MySQL database? Can I do this
> directly in Excel or do
> >I need to create an external program to do the work
> (i.e. in VB).
> >
> >Thanks
> >-Nick
> >
> >__
> >Do You Yahoo!?
> >Tired of spam?  Yahoo! Mail has the best spam
> protection around
> >http://mail.yahoo.com 
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
>
>http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >  
> >
> 
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Connecting using MyODBC

[Ryan Stille]

I am trying to connect to MySQL from within ColdFusion on a linux box.
ColdFusion (version 5) came with a Merant driver, but it is too old to
support transactions.

I've downloaded libmyodbc_mysql.so (MyODBC 2.5) and also libmyodbc3.so
(MyODBC 3.5) and could not get either of these to work.  But I'm not
sure if I'm using them correctly.

Basically all I've done is copy these files to my machine and change
this line in ColdFusion's odbc.ini file:
Old: Driver=/opt/coldfusion/lib/CFmysql15.so
New: Driver=/opt/coldfusion/lib/libmyodbc_mysql.so

Does this sound like I'm using them correctly?  Most of the
documentation I find about MyODBC talks about installing an odbc
manager, I believe ColdFusion already has one built in, since the
odbc.ini file already exists and is used.

When I use ODBC 3.5 my server just hangs.  When I use the 2.5 driver I
get this error:
ODBC Error Code = IM003 (Specified driver could not be loaded)

Thanks for any help.
-Ryan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: querry problem ( datetime => Monday - Sunday )

[inferno]

Hi,

   It is perfect, I was thinking of doing it in php but the solution that I've 
had was no way optimal.
   Thank you very much for the help.

Best regards and have a nice week-end,
Cristi Stoica


[EMAIL PROTECTED] wrote:


Hi Cristi,
Look at this :

mysql> select now();
+-+
| now()   |
+-+
| 2005-09-02 23:15:21 |
+-+
1 row in set (0.00 sec)

mysql> select DATE_ADD(now(), INTERVAL -1 DAY);
+--+
| DATE_ADD(now(), INTERVAL -1 DAY) |
+--+
| 2005-09-01 23:15:27  |
+--+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%a');
+-+
| date_format(now(),'%a') |
+-+
| Fri |
+-+
1 row in set (0.01 sec)


So your query should be similar to  :

Select * from tbl where status='2' and date <= DATE_ADD(now(), INTERVAL -1 DAY)
and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');


Hope that helps
Mathias

-Original Message-
From: inferno [mailto:[EMAIL PROTECTED]
Sent: vendredi 2 septembre 2005 22:58
To: mysql@lists.mysql.com
Subject: querry problem ( datetime => Monday - Sunday )

Hi,

  I have a problem: I need to make a select for data that was entered
more than 24 hours ago, but in that 24 hours I have to count only Monday
- Friday since that is the working program, and does not have the status
= '2' ( Solved ) and the problem is that I sincerly do not know how.
  Any help/suggestions are apreciated since I am just a beginner.

Best regards,
Cristi Stoica

P.S.: to give you a little idea on what I am using:
( the interface is coded in PHP for the users )
MySQL 4.0.24 and the the data looks like this:

++-+--+-+-++

| Field  | Type| Null | Key |
Default | Extra  |
++-+--+-+-++

| id | int(25) unsigned|  | PRI |
NULL| auto_increment |
| client_name| varchar(100)|  |
| ||
| code| bigint(13)  |  | |
0   ||
| status | varchar(13) |  | |
0   ||
| date   | datetime|  | | -00-00 00:00:00
||
++-+--+-+-++


--
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: Connections with bad DNS cause lockups

[Tuc at T-B-O-H]

> 
> Hello.
> 
> 
> > is that when it gets probed, it COMPLETELY offlines (DOS) the server.
> 
> One upon a time I solved such an issue with MySQL on FreeBSD by switching
> to the official binaries from:
>   http://dev.mysql.com/downloads
> 
> Still, the recommended way to run MySQL on FreeBSD is compiling it from the 
> ports. 
> 
> 
So if thats the way (FreeBSD ports), then besides the already suggested
changing to pure IP, is there any other ways to stop the DOS?

Thanks, Tuc

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: querry problem ( datetime => Monday - Sunday )

[mfatene]

Hi Cristi,
Look at this :

mysql> select now();
+-+
| now()   |
+-+
| 2005-09-02 23:15:21 |
+-+
1 row in set (0.00 sec)

mysql> select DATE_ADD(now(), INTERVAL -1 DAY);
+--+
| DATE_ADD(now(), INTERVAL -1 DAY) |
+--+
| 2005-09-01 23:15:27  |
+--+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%a');
+-+
| date_format(now(),'%a') |
+-+
| Fri |
+-+
1 row in set (0.01 sec)


So your query should be similar to  :

Select * from tbl where status='2' and date <= DATE_ADD(now(), INTERVAL -1 DAY)
and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');


Hope that helps
Mathias

-Original Message-
From: inferno [mailto:[EMAIL PROTECTED]
Sent: vendredi 2 septembre 2005 22:58
To: mysql@lists.mysql.com
Subject: querry problem ( datetime => Monday - Sunday )

Hi,

   I have a problem: I need to make a select for data that was entered
more than 24 hours ago, but in that 24 hours I have to count only Monday
- Friday since that is the working program, and does not have the status
= '2' ( Solved ) and the problem is that I sincerly do not know how.
   Any help/suggestions are apreciated since I am just a beginner.

Best regards,
Cristi Stoica

P.S.: to give you a little idea on what I am using:
( the interface is coded in PHP for the users )
MySQL 4.0.24 and the the data looks like this:

++-+--+-+-++

| Field  | Type| Null | Key |
Default | Extra  |
++-+--+-+-++

| id | int(25) unsigned|  | PRI |
NULL| auto_increment |
| client_name| varchar(100)|  |
| ||
| code| bigint(13)  |  | |
0   ||
| status | varchar(13) |  | |
0   ||
| date   | datetime|  | | -00-00 00:00:00
||
++-+--+-+-++


--
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]

querry problem ( datetime => Monday - Sunday )

[inferno]

Hi,

  I have a problem: I need to make a select for data that was entered 
more than 24 hours ago, but in that 24 hours I have to count only Monday 
- Friday since that is the working program, and does not have the status 
= '2' ( Solved ) and the problem is that I sincerly do not know how.

  Any help/suggestions are apreciated since I am just a beginner.

Best regards,
Cristi Stoica

P.S.: to give you a little idea on what I am using:
( the interface is coded in PHP for the users )
MySQL 4.0.24 and the the data looks like this:

++-+--+-+-++ 

| Field  | Type| Null | Key | 
Default | Extra  |
++-+--+-+-++ 

| id | int(25) unsigned|  | PRI | 
NULL| auto_increment |
| client_name| varchar(100)|  | 
| ||
| code| bigint(13)  |  | | 
0   ||
| status | varchar(13) |  | | 
0   ||
| date   | datetime|  | | -00-00 00:00:00 
||
++-+--+-+-++ 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 2 query questions

[SGreen]

Answers blended in

"YL" <[EMAIL PROTECTED]> wrote on 09/02/2005 04:18:27 PM:

> I've 2 basic questions. thanks for responses:
> 
> (1) Suppose i have a table with a varchar colum named options which 
stores
> option id's in the form
>  "(id1, id2, , idk)", where id1,... are string representations 
of
> positive integers. Given an integer i,
>  I want to fetch all ID's for the records in the table whoes 
'options'
> value when interprets as an array,
>  contains integer i. What the query looks like?


I do not understand your question. SQL does not have arrays. It has rows 
and columns and tables and lots of other things but no arrays. Programming 
languages have arrays, not SQL. Are you trying to get some data and 
populate an array with it? That's not completely a MySQL question, it's 
more of a question about how to create arrays from strings in your 
particular language. However, since there are MANY people on this list, I 
am sure someone will know the answer so please explain what it is you are 
doing, what language you are using to do it with, what your data looks 
like (use "SHOW CREATE TABLE", and explain what you want your results to 
look like.

> 
> (2) when using mysql command to query a value of type text, the 
displayed
> value always surrounded by lots
> '-'s. My question is: How to get rid of those extras? (The following is 
a
> tail of display result:)
>   template = {
>b5 = resources/articles/_backups/subIndex_b5.template;
>gb = resources/articles/_backups/subIndex_gb.template;
>};
>};
>};
> 
> 
|---
> 
> 

> ---
> 

> ---
> 

> ---
> 

> ---
> --+
> 

When using the command line client, the headers, the data areas, and the 
tails are each as wide as your data but they get "wrapped" to fit your 
screen buffer. To make the wrapping effect go away in Windows, change the 
Screen Buffer settings for your command shell window to something wide 
enough to fit your results. One way you can get to the settings is by 
right-clicking the window and picking "properties".

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: 2 query questions

[Jason Pyeron]

please write 2 emails for 2 unrelated questions, that said I will tackle 
#2


you have all those line drawing chars since the field is very wide.

The MySQL client does not 'know' about new lines, it just prints them out, 
so each one is counted as one char.


in your client you can ask for help, \?, ?.

Look at the command \G or ego; it will display your results vertically, 
avoiding this issue.


On Fri, 2 Sep 2005, YL wrote:


(2) when using mysql command to query a value of type text, the displayed
value always surrounded by lots
'-'s. My question is: How to get rid of those extras? (The following is a
tail of display result:)
 template = {
  b5 = resources/articles/_backups/subIndex_b5.template;
  gb = resources/articles/_backups/subIndex_gb.template;
  };
  };
  };

|---


---

---

---

---
--+





--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

2 query questions

[YL]

I've 2 basic questions. thanks for responses:

(1) Suppose i have a table with a varchar colum named options which stores
option id's in the form
 "(id1, id2, , idk)", where id1,... are string representations of
positive integers. Given an integer i,
 I want to fetch all ID's for the records in the table whoes 'options'
value when interprets as an array,
 contains integer i. What the query looks like?

(2) when using mysql command to query a value of type text, the displayed
value always surrounded by lots
'-'s. My question is: How to get rid of those extras? (The following is a
tail of display result:)
  template = {
   b5 = resources/articles/_backups/subIndex_b5.template;
   gb = resources/articles/_backups/subIndex_gb.template;
   };
   };
   };

|---


---

---

---

---
--+


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Remote access denial

[Gleb Paharenko]

Hello.



Have you run mysql_fix_privilege_tables script after the upgrade?





"T. Horsnell" <[EMAIL PROTECTED]> wrote:

> After replacing a 3.23.28 server with 4.1.14 server binaries on an Alpha

> running Tru64 5.1B, I get things like:

> 

> ERROR 1045 (28000): Access denied for user 'tsh'@'10.4.0.101' (using 
> password: YES)

> 

> when trying to connect from a remote host.

> 

> The problem goes away if I replace hostnames by IP addresses

> in the mysql 'user' tables. I see from the archives that this

> problem has been reported recently by others. Is there a fix?

> (apart from changing every relevant entry in the user tables)

> I cant see anything relevant in the Buglists.

> 

> 

> Cheers,

> Terry.

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: show master/slave status privileges ?

[Gleb Paharenko]

Hello.



> GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'



Are you sure that you are connected as [EMAIL PROTECTED] user? What

does 'status' command report (execute it in the mysql command line

client)? What version of MySQL do you use?



"Morten Kalles$e" <[EMAIL PROTECTED]> wrote:

> Hi

> 

> I have been trying to get my mysql server to do as i want, but its not =

> quite playing along.

> 

> I need to monitor the replication status from a remote server, first by =

> hand, 2nd by a perl script.

> 

> But i keep getting this darn message.=20

> 

> mysql> show master status;

> ERROR 1227: Access denied. You need the SUPER,REPLICATION CLIENT =

> privilege for this operation

> 

> I have made a user with the following command:

> GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'

> 

> Shouldnt that be enough? Or am i just missing something,

> 

> I have also tried to make the user with:

> GRANT SUPER,REPLICATION CLIENT TO 'test'@'123.123.123.123' IDENTIFIED BY =

> 'h4x0r';

> No luck there ethier.

> 

> 

> Regards

> Morten

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Trouble with revoke all

[Gleb Paharenko]

Hello.





> This looks pretty much full access to me?



I was confused by your "all I have is select privs." phrase in the

previous post. Please, send the output of the 'status' command.

What is in the 'Current user' field?





Scott Haneda <[EMAIL PROTECTED]> wrote:

> on 9/2/05 1:27 AM, Gleb Paharenko at [EMAIL PROTECTED] wrote:

> 

> 

> Modify them how?

> show grants for 'root'@'localhost';

> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,

> PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES,

> EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost'

> IDENTIFIED BY PASSWORD '' WITH GRANT OPTION

> 

> This looks pretty much full access to me?



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: a difficult join query question

[SGreen]

Klemens Ullmann <[EMAIL PROTECTED]> wrote on 09/02/2005 07:37:43 AM:

> hello!
> 
> I've got two tables for an IT hardware inventory:
> 
> ### table inventory:
> invid  model
> ---
> 1001   HP Notebook// no attributes
> 1002   Dell Desktop   // only one attribut 'pc-name'
> 1003   Acer Laptop// attributes 'pc-name' & 'harddisk'
> 1004   Apple iBook// only one attribut 'harddisk'
> 
> ### table attributes:
> id   invid  attribute  value
> -
> 501  1002   pcname atpc01
> 502  1003   pcname atpc02
> 503  1003   harddisk   20GB
> 504  1004   harddisk   40GB
> 
> what I want is a list of all computers (without exeptions) in the 
> following form:
> invid - modell - pcname
> 
> my best guess zu date of a possible query is:
> SELECT inventory.invid,inventory.model,attributes.value as pcname
> FROM inventory
> LEFT JOIN attributes ON (inventory.invid=attributes.invid)
> WHERE attributes.attribute='pcname' or attributes.attribute is NULL;
> 
> ### result:
> invid  model pcname
> ---
> 1001   HP Notebook   NULL
> 1002   Dell Desktop  atpc01
> 1003   Acer Laptop   atpc02
> 
> 
> now my problem are the missing computers which have an attribute, but 
> not a 'pc-name'-attribute.
> (in the example above the missing iBook)
> 
> thank you for suggestions how to solve the problem!
> 
> have a nice day,
> 
>  klemens ullmann / vienna
> 

That's a simple OUTER JOIN query, like you wrote, with one exception. 

By putting the checks for attributes.attribute='whatever' in the WHERE 
clause, you forced those values to be part of the results. This in effect 
changed your query into an INNER JOIN and eliminated all of the rows from 
`model` that didn't have those attributes. 

What you wanted to do is to add that comparison into the ON subclause of 
your JOIN clause like this:

SELECT inventory.invid,inventory.model,attributes.value as pcname
FROM inventory
LEFT JOIN attributes 
ON inventory.invid=attributes.invid
AND attributes.attribute='pcname'
;

Putting it here made it a condition of the JOIN which meant that rows from 
the `attributes` table that were not 'pcname' rows would be excluded from 
the "right" side of the LEFT JOIN. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: a difficult join query question

[Devananda]

Klemens Ullmann wrote:

hello!

I've got two tables for an IT hardware inventory:

### table inventory:
invid  model
---
1001   HP Notebook// no attributes
1002   Dell Desktop   // only one attribut 'pc-name'
1003   Acer Laptop// attributes 'pc-name' & 'harddisk'
1004   Apple iBook// only one attribut 'harddisk'

### table attributes:
id   invid  attribute  value
-
501  1002   pcname atpc01
502  1003   pcname atpc02
503  1003   harddisk   20GB
504  1004   harddisk   40GB

what I want is a list of all computers (without exeptions) in the 
following form:

invid - modell - pcname

my best guess zu date of a possible query is:
SELECT inventory.invid,inventory.model,attributes.value as pcname
   FROM inventory
   LEFT JOIN attributes ON (inventory.invid=attributes.invid)
   WHERE attributes.attribute='pcname' or attributes.attribute is NULL;

### result:
invid  model pcname
---
1001   HP Notebook   NULL
1002   Dell Desktop  atpc01
1003   Acer Laptop   atpc02


now my problem are the missing computers which have an attribute, but 
not a 'pc-name'-attribute.

(in the example above the missing iBook)

thank you for suggestions how to solve the problem!

have a nice day,

klemens ullmann / vienna



Quite a fun challenge! Using the test data you gave, this query works, 
assuming that you are running a version of MySQL that can do subqueries.


SELECT
inventory.invid
, model
, value AS pcname
FROM inventory
LEFT JOIN
( SELECT invid, value FROM attributes WHERE attribute='pcname' ) AS a
USING(invid);


+---+-++
| invid | model   | pcname |
+---+-++
|  NULL | HP Notebook | NULL   |
|  1002 | Dell Desktop| atpc01 |
|  1003 | Acer Laptop | atpc02 |
|  1004 | Apple iBook | NULL   |
+---+-++
4 rows in set (0.00 sec)


Regards,
Devananda vdv

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Can't figure out how to get it running

[SGreen]

Joe, you are not used to using a command line interface, are you

When you do a "START -> RUN -> cmd ", you are entering the world of 
computing that exists outside of Windows (have you ever heard of DOS?). 
What you are in is called a "shell" and on windows it's called the 
"command shell" because it is supported by the program "command.exe" or 
"cmd.exe", depending on what version of windows you are using.

When you type something into a shell and hit enter, it tries to treat what 
you entered like a command or as the name of a file you want to execute. 
Examples of shell commands are: help, copy, dir, type, set, path 
Examples of some executable names on windows are: notepad, wordpad, 
regedit, mysql, mysqladmin, mysqldump
Generally commands and file names are not case sensitive. This is true for 
Windows but definitely not so for other operating systems.

A file must end in either ".exe" or ".com" or ".bat" in order for the 
command shell to treat it as an executable file. If the command shell does 
not understand what you typed as a built-in command, it looks in your 
current directory to see if it's an executable file. If it isn't there, it 
checks a whole list of other directories to see if one of the files in 
them matches the name you entered.  You can see that list if you type the 
word PATH on the command line and hit enter. Please do that now...

Read through this list carefully. Look for "c:\mysql\bin". If you are like 
everyone else, it isn't there. That means that if you type the command

mysql

and hit enter, the command shell can't find the file mysql.exe and run it 
because it looked everywhere it was supposed to look and couldn't find it. 
That's what the error message means. 

How to solve this: 
Make your "current" directory the directory where the mysql tools live. By 
convention, MySQL is usually installed to the folder "C:\mysql\" and the 
program files are in "C:\mysql\bin\". You move yourself into that folder 
with the CD command. CD stands for "change directory" and the one you 
probably need looks like this

cd \mysql\bin

Now your shell session is "in" the bin folder of the mysql folder. If you 
pull a file listing of this directory by typing DIR and hitting enter, you 
should see several files, one of them should be called "mysql.exe"

If you type "mysql" on the command line and hit enter, the command shell 
will first look at is as an internal command (which it isn't) then start 
looking in the folders list to see if it's an executable. Because that 
file DOES exist in your current directory, the shell will find it, load 
it, and start executing the program. If you only type "mysql" and hit 
enter, you will attempt to connect to the server on your local machine as 
an anonymous user. To actually login to any server as an account with any 
kind of privileges you will need to use the -u option and probably the -p 
option. If you want to connect to a server on a different machine, use the 
-h option to specify which server to connect to.

Here are some sample logins (assuming that the server daemon is already 
running):

mysql -u root 

This will login to your local server attempting to connect as the mysql 
user "root" without using a password. This should let you connect to a 
"virgin" installation of MySQL

mysql -u root -p

Same as above except you will be prompted for a password.

mysql -h localhost -p -u root

Same as the previous command (the order in which the options appear is not 
important). This is the same command because the name "localhost" is a 
special name to indicate that you want to connect to the same machine.

mysql -h prodserver.mydomain.net -u prodadmin -p 

This works just like the others: you are asking to connect to the mysql 
server on the machine at "prodserver.mydomain.net" as the mysql user 
"prodadmin" and you will be prompted to provide a password.

If you specify your options correctly and the server to which you are 
attempting to connect likes the username you provided, the location you 
are connecting from, and the password (if you need one) then you should 
make it to the 

mysql>

prompt. This should allow you to continue with the installation 
instructions (whichever set you are following). If you are NOT following a 
set of setup instructions, please refer to these: 
http://dev.mysql.com/doc/mysql/en/windows-installation.html

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Joe Herman" <[EMAIL PROTECTED]> wrote on 09/02/2005 02:35:21 PM:

> Thanks for your reply.
> 
> I've done what you said. Unfortunately I get the message in the CMD
> Window:
> 
> 'MYSQLADMIN' is not a recognized as an internal or external command.
> 
> Can you tell me what this means? I did run the installer earlier. Thank
> you.
> 
> Joe.
> 
> -Original Message-
> From: Eric Mynes [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 02, 2005 12:35 PM
> To: Joe Herman
> Cc: mysql@lists.mysql.com
> Subject: Re: Can't figure out h

RE: Can't figure out how to get it running

[Joe Herman]

Thanks for your reply.

I've done what you said. Unfortunately I get the message in the CMD
Window:

'MYSQLADMIN' is not a recognized as an internal or external command.

Can you tell me what this means? I did run the installer earlier. Thank
you.

Joe.

-Original Message-
From: Eric Mynes [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 02, 2005 12:35 PM
To: Joe Herman
Cc: mysql@lists.mysql.com
Subject: Re: Can't figure out how to get it running


The way I could tell that mysl is running is to (assuming that you are 
running windows):
Click Start
Click Run
Type CMD
Press Enter
Type MYSQLADMIN PING
Press Enter
if you get the message "mysqld is alive", then it is running  Thanks,
Eric Network Administrator 
West Virginia Federal Credit Union
 On 9/2/05, Joe Herman <[EMAIL PROTECTED]> wrote: 
> 
> Hello,
> 
> I am just getting started with MySql and PHP (previously was just 
> designing web pages). I am very new to database-based web development.
> 
> I installed IIS and PHP. I thought I installed MySQL as well but I 
> can't tell if it is working or not. I was told if I make a phpinfo.php

> file and open it my browser I would see. Supposedly if there is a 
> listing for MySQL in the list that would mean it is running. There is 
> no listing of MySQL there. There are listings for other PHP items 
> however which I guess means PHP is running.
> 
> I saw that in the bin folder of MySQL there is a program called 
> MySQLadmin. Whenever I try to run that a small black screen flashes 
> momentarily on my screen (kind of looking like a command line window) 
> and then it disappears without giving me a chance to even see what it 
> is. Actually this happens with every program in the bin folder.
> 
> Would someone please tell me what I need to do to get MySQL to work? 
> Right now I am just trying to set things up so that I can start 
> learning PHP. Please help.
> 
> Thank you.
> 
> Joe Herman
> New York City
> 
> 
> --
> 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: delete, where, and subselects...

[mfatene]

Hi,
You can't delete selected rows from the same table in mysql.

Just create a temp table containing the select result. And delete from table A
where existe select ... from temp_table;

Mathias


-Original Message-
From: Jason Pyeron [mailto:[EMAIL PROTECTED]
Sent: vendredi 2 septembre 2005 19:53
To: mysql@lists.mysql.com
Subject: delete, where, and subselects...


to quote the manual:

Currently, you cannot delete from a table and select from the same table
in a subquery.

mysql> select * from paths where id>=(select max(ppathref) from paths);
++-++--+
| id | typeref | name   | ppathref |
++-++--+
| 216883 |   1 | shared |   216882 |
| 216884 |   1 | shared.bs  |   216883 |
| 216885 |   1 | shared.so  |   216883 |
| 216886 |   1 | threads.bs |   216882 |
| 216887 |   1 | threads.so |   216882 |
++-++--+
5 rows in set (0.00 sec)

mysql> delete from paths where id>=(select max(ppathref) from paths);
ERROR 1093 (HY000): You can't specify target table 'paths' for update in FROM
clause

Server version: 4.1.14-standard-log


Are there any workarounds?


Any ideas? TIA

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, purge the message from your system and
notify the sender immediately.  Any other use of the email by you
is prohibited.

--
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: Where exists query syntax error?

[SGreen]

"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 01:07:00 PM:

> I apologize for not providing more information.  I am currently 
> using 4.1.8 client and server on windows xp.
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 02, 2005 11:48 AM
> To: Barbara Deaton
> Cc: mysql@lists.mysql.com
> Subject: Re: Where exists query syntax error?
> 
> 
> 
> 
> "Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 
AM:
> 
> > I need help re-writing a query.
> > 
> > When accessing DB2 I can execute the below:
> > delete from DM_KOHLS.clr_plan_price_change_metrics A  
> > where A.start_dt>= Date( '05/15/2005' ) and  
> >   exists ( select * from SESSION.delete_table_tmp 
> B 
> >where A.clr_plan_sk = B.clr_plan_sk) 
> > 
> > 
> > I modified it to be what I thought was a valid MySQL statement but 
> > when I execute it against MySQL I get a syntax error:
> > 
> > ERROR: Execute error: You have an error in your SQL syntax.  Check 
> > the manual that corresponds
> >to your MySQL server version for the right syntax to use near
> > 'exists ( select * from
> >delete_table_tmp where clr_plan_price_ch
> > 
> > 
> > The statement I executed is:
> > delete from clr_plan_price_change_metrics 
> > where start_dt>= '2005-05-15' and 
> >   exists ( select * from delete_table_tmp 
> >where clr_plan_price_change_metrics.
> > clr_plan_sk = delete_table_tmp.clr_plan_sk)
> > 
> > 
> > I can successfully execute each piece of the query, but when I 
> > combine it with the 'exists' subquery it fails.
> > 
> > The examples in the doc show just selects... Exists() so I tried 
> > changing it to a select from table and still fails.  What is the 
> > correct syntax?
> > -Barb.
> > 
> 
> Your version, depending on how old it is, may not support the EXISTS
> predicate. It may not support the subquery. It may not even support 
> deletes involving more than one table at a time. Please tell us 
> which version you are using so that we can give you a form of your 
> DELETE query compatible with your server. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine


According to http://dev.mysql.com/doc/mysql/en/delete.html there are 3 
valid forms of the DELETE statement. You seem to be trying to use the 
first one listed

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_definition]
   [ORDER BY ...]
   [LIMIT row_count]

delete from clr_plan_price_change_metrics 
where start_dt>= '2005-05-15' 
and exists ( 
select * 
from delete_table_tmp 
where clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk
)

This is documented to work for SELECT queries but I am not 100% certain it 
works in DELETE queries. I looked for a bug on this. I seem to remember 
seeing a change log entry saying something like this was fixed but my 
boolean query keeps timing out so I can not reference the change log entry 
or what version it was fixed in. Sorry.

One way to actually do what you want to do is to NOT use a subquery and 
use either one of the "multi-table" forms. I prefer the first

DELETE clr_plan_price_change_metrics
FROM clr_plan_price_change_metrics
INNER JOIN delete_table_tmp
ON clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk
WHERE start_dt>= '2005-05-15' 


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

delete, where, and subselects...

[Jason Pyeron]

to quote the manual:

Currently, you cannot delete from a table and select from the same table 
in a subquery.


mysql> select * from paths where id>=(select max(ppathref) from paths);
++-++--+
| id | typeref | name   | ppathref |
++-++--+
| 216883 |   1 | shared |   216882 |
| 216884 |   1 | shared.bs  |   216883 |
| 216885 |   1 | shared.so  |   216883 |
| 216886 |   1 | threads.bs |   216882 |
| 216887 |   1 | threads.so |   216882 |
++-++--+
5 rows in set (0.00 sec)

mysql> delete from paths where id>=(select max(ppathref) from paths);
ERROR 1093 (HY000): You can't specify target table 'paths' for update in FROM 
clause

Server version: 4.1.14-standard-log


Are there any workarounds?


Any ideas? TIA

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't figure out how to get it running

[Eric Mynes]

The way I could tell that mysl is running is to (assuming that you are 
running windows):
Click Start
Click Run
Type CMD
Press Enter
Type MYSQLADMIN PING
Press Enter
if you get the message "mysqld is alive", then it is running
 Thanks,
Eric
Network Administrator 
West Virginia Federal Credit Union
 On 9/2/05, Joe Herman <[EMAIL PROTECTED]> wrote: 
> 
> Hello,
> 
> I am just getting started with MySql and PHP (previously was just
> designing web pages). I am very new to database-based web development.
> 
> I installed IIS and PHP. I thought I installed MySQL as well but I can't
> tell if it is working or not. I was told if I make a phpinfo.php file
> and open it my browser I would see. Supposedly if there is a listing for
> MySQL in the list that would mean it is running. There is no listing of
> MySQL there. There are listings for other PHP items however which I
> guess means PHP is running.
> 
> I saw that in the bin folder of MySQL there is a program called
> MySQLadmin. Whenever I try to run that a small black screen flashes
> momentarily on my screen (kind of looking like a command line window)
> and then it disappears without giving me a chance to even see what it
> is. Actually this happens with every program in the bin folder.
> 
> Would someone please tell me what I need to do to get MySQL to work?
> Right now I am just trying to set things up so that I can start learning
> PHP. Please help.
> 
> Thank you.
> 
> Joe Herman
> New York City
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

RE: Where exists query syntax error?

[Barbara Deaton]

I apologize for not providing more information.  I am currently using 4.1.8 
client and server on windows xp.



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 02, 2005 11:48 AM
To: Barbara Deaton
Cc: mysql@lists.mysql.com
Subject: Re: Where exists query syntax error?




"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 AM:

> I need help re-writing a query.
> 
> When accessing DB2 I can execute the below:
> delete from DM_KOHLS.clr_plan_price_change_metrics A 
> where A.start_dt>= Date( '05/15/2005' ) and  
>   exists ( select * from SESSION.delete_table_tmp B  
>where A.clr_plan_sk = B.clr_plan_sk) 
> 
> 
> I modified it to be what I thought was a valid MySQL statement but 
> when I execute it against MySQL I get a syntax error:
> 
> ERROR: Execute error: You have an error in your SQL syntax.  Check 
> the manual that corresponds
>to your MySQL server version for the right syntax to use near
> 'exists ( select * from
>delete_table_tmp where clr_plan_price_ch
> 
> 
> The statement I executed is:
> delete from clr_plan_price_change_metrics  
> where start_dt>= '2005-05-15' and 
>   exists ( select * from delete_table_tmp 
>where clr_plan_price_change_metrics.
> clr_plan_sk = delete_table_tmp.clr_plan_sk)
> 
> 
> I can successfully execute each piece of the query, but when I 
> combine it with the 'exists' subquery it fails.
> 
> The examples in the doc show just selects... Exists() so I tried 
> changing it to a select from table and still fails.  What is the 
> correct syntax?
> -Barb.
> 

Your version, depending on how old it is, may not support the EXISTS predicate. 
It may not support the subquery. It may not even support deletes involving more 
than one table at a time. Please tell us which version you are using so that we 
can give you a form of your DELETE query compatible with your server. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

MySQL 5.0.12-beta has been released

[Joerg Bruehe]

Hi,

MySQL 5.0.12-beta, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at http://dev.mysql.com/downloads/ and mirror sites.

Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.

This is the eighth published Beta release in the 5.0 series.
All attention will continue to be focused on fixing bugs and stabilizing
5.0 for later production release.

NOTE: This Beta release, as any other pre-production release, should not
be installed on ``production'' level systems or systems with critical
data. It is good practice to back up your data before installing any new
version of software. Although MySQL has done its best to ensure a high
level of quality, protect your data by making a backup as you would for
any software beta release.

Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.


Changes in release 5.0.12:

Functionality added or changed:
* Recursive  triggers  are  detected  and  disallowed.  (Bug  #11896
  (http://bugs.mysql.com/11896),Bug#12644(http://bugs.mysql.com/12644))
* Natural joins and joins with USING, including outer join variants, now
  are  processed  according  to  the  SQL:2003  standard. (Bug #4789
  (http://bugs.mysql.com/4789), Bug #6136 (http://bugs.mysql.com/6136),
  Bug  #6276 (http://bugs.mysql.com/6276), Bug #6495
  (http://bugs.mysql.com/6495), Bug #6558 (http://bugs.mysql.com/6558),
  Bug #9978 (http://bugs.mysql.com/9978), Bug #10646
  (http://bugs.mysql.com/10646),Bug#10972(http://bugs.mysql.com/10972),
  Bug #11710 (http://bugs.mysql.com/11710))
* SHOW  OPEN  TABLES now supports FROM and LIKE clauses. (Bug #12183
  (http://bugs.mysql.com/12183))
* SHOW TABLE STATUS FROM INFORMATION_SCHEMA now sorts output by table
  name the same as it does forother databases.  (Bug  #12315
  (http://bugs.mysql.com/12315))
* SHOW ENGINE INNODB STATUS now can display longer query strings. (Bug
  #7819 (http://bugs.mysql.com/7819))
* Added the SLEEP() function, which pauses for the number of seconds
  given by its argument. (Bug #6760 (http://bugs.mysql.com/6760))
* Trying to drop the default keycache by setting
  @@global.key_buffer_size
  to  zero now returns a warning that the default keycache cannot be
  dropped. (Bug #10473 (http://bugs.mysql.com/10473))
* The  stability of cursors when used with InnoDB tables was greatly
  improved.  (Bug  #11832  (http://bugs.mysql.com/11832), Bug #12243
  (http://bugs.mysql.com/12243),Bug#11309(http://bugs.mysql.com/11309))
* It is no longer possible to issue FLUSH commands from within stored
  functions or triggers. See Section I.1, "Restrictions on Stored
  Routines and Triggers" for details.
  (Bug #12280  (http://bugs.mysql.com/12280),
  Bug #12307   (http://bugs.mysql.com/12307))
* INFORMATION_SCHEMA objects are now reported as a SYSTEM VIEW table
  type.  (Bug #11711 (http://bugs.mysql.com/11711))

Bugs fixed:
* An optimizer estimate of zero rows for a non-empty InnoDB table used
  in a left or right join could cause incomplete rollback for the table.
  (Bug #12779 (http://bugs.mysql.com/12779))
* Interleaved  execution of stored procedures and functions could be
  written to the binary log incorrectly, causing replication slaves to
  get out of sync. (Bug #12335 (http://bugs.mysql.com/12335))
* A  query  of the form SHOW TABLE STATUS FROM db_name WHERE name IN
  (select_query)wouldcrashtheserver.   (Bug   #12636
  (http://bugs.mysql.com/12636))
* Users created using an IP address or other alias rather than a
  hostname listed in /etc/hosts could not set their own passwords.
  (Bug #12302 (http://bugs.mysql.com/12302))
* Using DESCRIBE on a view after renaming a column in one of the view's
  base   tables   caused   the   server   to   crash.   (Bug  #12533
  (http://bugs.mysql.com/12533))
* DELETE or UPDATE for an indexed MyISAM table could fail. This was due
  to a change in end-space comparison behavior from 4.0 to 4.1. (Bug
  #12565  (http://bugs.mysql.com/12565))
* Joins on VARCHAR columns of different lengths could produce incorrect
  results. (Bug #11398 (http://bugs.mysql.com/11398))
* A “Duplicate column name” error no longer occurs when selecting from a
  view defined as SELECT * from a join that uses a USING clause on
  tables  that have a common column name. (Bug #6558
  (http://bugs.mysql.com/6558))
* Invocations of the SLEEP() function incorrectly could get optimized
  away for statements in which it occurs. Statements containing SLEEP()
  incorrectly  could  be  stored  in  the  query  cache. (Bug #12689
  (http://bugs.mysql.com/12689))
* NDB Cluster: An ALTER TABLE command caused loss of data stored prior
  to the issuing of the command.(Bug

Re: migrating from Postgres to MySQL

[Alec . Cawley]

Wiebe de Jong <[EMAIL PROTECTED]> wrote on 02/09/2005 17:32:07:

> I used mixed case in naming my schemas, tables and fields in Postgres 
7.1.
> (i.e. onDemand.callDetailRecord) Now I have to move my app over to 
MySQL.
> 
> 
> 
> What are the best practices for naming in MySQL?

Definitely use lower case only for databases (schemas) and tables. Since 
(for MyIsam at least) these map on to the underlying filesystem, which is 
cases sensitive for Unixes and case independent for Windows, you avoid a 
lot of trouble if you stick to lower case. Fields are, I think, case 
insensitive throughout, so do as you will.

Alec Cawley




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

migrating from Postgres to MySQL

[Wiebe de Jong]

I used mixed case in naming my schemas, tables and fields in Postgres 7.1.
(i.e. onDemand.callDetailRecord) Now I have to move my app over to MySQL.

 

What are the best practices for naming in MySQL?

 

Wiebe

Can't figure out how to get it running

[Joe Herman]

Hello,

I am just getting started with MySql and PHP (previously was just
designing web pages). I am very new to database-based web development.

I installed IIS and PHP. I thought I installed MySQL as well but I can't
tell if it is working or not. I was told if I make a phpinfo.php file
and open it my browser I would see. Supposedly if there is a listing for
MySQL in the list that would mean it is running. There is no listing of
MySQL there. There are listings for other PHP items however which I
guess means PHP is running.

I saw that in the bin folder of MySQL there is a program called
MySQLadmin. Whenever I try to run that a small black screen flashes
momentarily on my screen (kind of looking like a command line window)
and then it disappears without giving me a chance to even see what it
is. Actually this happens with every program in the bin folder.

Would someone please tell me what I need to do to get MySQL to work?
Right now I am just trying to set things up so that I can start learning
PHP. Please help.

Thank you.

Joe Herman
New York City


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with revoke all

[Michael Stassen]

Scott Haneda wrote:

on 9/2/05 1:27 AM, Gleb Paharenko at [EMAIL PROTECTED] wrote:



Recreate grants for the 'root'@'localhost' user manually. For this

purpose restart server with --skip-grant-tables command line options

and modify the grant tables. See:

 http://dev.mysql.com/doc/mysql/en/resetting-permissions.html

http://dev.mysql.com/doc/mysql/en/privileges.html



Modify them how?
show grants for 'root'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES,
EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost'
IDENTIFIED BY PASSWORD '' WITH GRANT OPTION

This looks pretty much full access to me?


I didn't look closely to see if anything's missing in that list, but full 
access usually looks like this:


  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
  '' WITH GRANT OPTION

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Remote access denial

[T. Horsnell]

>Hi,
>It is skip-name-resolve - I just wrote it by memory and was wrong. There is 
>also a nice piece on "Access denied" errors at
>
>http://dev.mysql.com/doc/mysql/en/access-denied.html

Thanks - I'd already read that.
I've also discovered that its generating log entries (I've got a
3 Gbyte log file) even though I havent asked for them.
How can I turn this off? Do I have to use -l /dev/null ?
I'm definitely starting to suspect some compiled-in options.
Do you know how I can discover what the compile-time options
were? I cant (yet) find anything relevant in the MySQL Ref manual

Apologies for the continual questions - I'm new to 4.1 as of 1 week ago.

Cheers,
Terry.

>
>-- 
>Dobromir Velev
>[EMAIL PROTECTED]
>http://www.websitepulse.com/
>
>On Friday 02 September 2005 16:35, T. Horsnell wrote:
>> >Hi,
>> >Does the 10.4.0.101 reverse resolve to a specific hostname? If it does you
>> > are probably running MySQL with the skip_hostname_resolve option and if
>> > it doesn't there is no way the MySQL server could tell from which host
>> > the request is comming.
>>
>> It does. The problem exists for all remote hosts which try to connect,
>> and they all resolve. So far as I'm aware, I'm not using the
>> skip_hostname_resolve (do you mean skip-name-resolve ?) option:
>>
>> # ps -ef | grep mysql
>> mysql 71401  1  0.0   Aug 31 ??   0:00.01
>> /usr/local/mysql/bin/safe_mysqld mysql 71876  71401  0.0   Aug 31 ??   
>>   04:22:56 /usr/local/mysql/bin/mysqld
>> --defaults-extra-file=/usr/local/mysql/data/my.cnf
>> --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
>> --pid-file=/usr/local/mysql/data/alf1.lmb.internal.pid
>>
>> and I have no /usr/local/mysql/data/my.cnf
>>
>> # ls -l /usr/local/mysql/data/*.cnf
>> ls: No match.
>>
>> Could it be that the server-binary was compiled with skip-name-resolve ?
>>
>> >My opinion is that it is always better to use the IP-addresses instead of
>> > the hostnames and use skip_hostname_resolve - the connection overhead
>> > will be smaller this way.
>> >
>> >You can also check the comments on
>> >http://dev.mysql.com/doc/mysql/en/dns.html
>>
>> Thanks, I'll do this.
>>
>> Cheers,
>> Terry.
>>
>> >--
>> >Dobromir Velev
>> >[EMAIL PROTECTED]
>> >http://www.websitepulse.com/
>> >
>> >On Friday 02 September 2005 13:38, T. Horsnell wrote:
>> >> After replacing a 3.23.28 server with 4.1.14 server binaries on an Alpha
>> >> running Tru64 5.1B, I get things like:
>> >>
>> >> ERROR 1045 (28000): Access denied for user 'tsh'@'10.4.0.101' (using
>> >> password: YES)
>> >>
>> >> when trying to connect from a remote host.
>> >>
>> >> The problem goes away if I replace hostnames by IP addresses
>> >> in the mysql 'user' tables. I see from the archives that this
>> >> problem has been reported recently by others. Is there a fix?
>> >> (apart from changing every relevant entry in the user tables)
>> >> I cant see anything relevant in the Buglists.
>> >>
>> >>
>> >> Cheers,
>> >> Terry.
>> >
>> >--
>> >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: Where exists query syntax error?

[SGreen]

"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 AM:

> I need help re-writing a query.
> 
> When accessing DB2 I can execute the below:
> delete from DM_KOHLS.clr_plan_price_change_metrics A  
> where A.start_dt>= Date( '05/15/2005' ) and  
>   exists ( select * from SESSION.delete_table_tmp B  
>where A.clr_plan_sk = B.clr_plan_sk) 
> 
> 
> I modified it to be what I thought was a valid MySQL statement but 
> when I execute it against MySQL I get a syntax error:
> 
> ERROR: Execute error: You have an error in your SQL syntax.  Check 
> the manual that corresponds
>to your MySQL server version for the right syntax to use near
> 'exists ( select * from
>delete_table_tmp where clr_plan_price_ch
> 
> 
> The statement I executed is:
> delete from clr_plan_price_change_metrics 
> where start_dt>= '2005-05-15' and 
>   exists ( select * from delete_table_tmp 
>where clr_plan_price_change_metrics.
> clr_plan_sk = delete_table_tmp.clr_plan_sk)
> 
> 
> I can successfully execute each piece of the query, but when I 
> combine it with the 'exists' subquery it fails.
> 
> The examples in the doc show just selects... Exists() so I tried 
> changing it to a select from table and still fails.  What is the 
> correct syntax?
> -Barb.
> 

Your version, depending on how old it is, may not support the EXISTS 
predicate. It may not support the subquery. It may not even support 
deletes involving more than one table at a time. Please tell us which 
version you are using so that we can give you a form of your DELETE query 
compatible with your server.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Where exists query syntax error?

[Barbara Deaton]

I need help re-writing a query.

When accessing DB2 I can execute the below:
delete from DM_KOHLS.clr_plan_price_change_metrics A
   
where A.start_dt>= Date( '05/15/2005' ) and 

  exists ( select * from SESSION.delete_table_tmp B 

   where A.clr_plan_sk = B.clr_plan_sk) 


I modified it to be what I thought was a valid MySQL statement but when I 
execute it against MySQL I get a syntax error:

ERROR: Execute error: You have an error in your SQL syntax.  Check the manual 
that corresponds
   to your MySQL server version for the right syntax to use near 'exists ( 
select * from
   delete_table_tmp where clr_plan_price_ch


The statement I executed is:
delete from clr_plan_price_change_metrics  
where start_dt>= '2005-05-15' and 
  exists ( select * from delete_table_tmp 
   where clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk)


I can successfully execute each piece of the query, but when I combine it with 
the 'exists' subquery it fails.

The examples in the doc show just selects... Exists() so I tried changing it to 
a select from table and still fails.  What is the correct syntax?
-Barb.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: show master/slave status privileges ?

[Michael Stassen]

Morten Kallesøe wrote:
> Hi
>
> I have been trying to get my mysql server to do as i want, but its not
> quite playing along.
>
> I need to monitor the replication status from a remote server, first by
> hand, 2nd by a perl script.
>
> But i keep getting this darn message.
>
> mysql> show master status;
> ERROR 1227: Access denied. You need the SUPER,REPLICATION CLIENT privilege
> for this operation
>
> I have made a user with the following command: GRANT ALL ON *.* TO
> 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'
>
> Shouldn't that be enough? Or am i just missing something,
>
> I have also tried to make the user with: GRANT SUPER,REPLICATION CLIENT TO
> 'test'@'123.123.123.123' IDENTIFIED BY 'h4x0r'; No luck there ethier.
>
> Regards Morten

First, verify you are connected as the correct user:

  mysql> SELECT CURRENT_USER();

If that shows 'test'@123.123.123.123', then verify your GRANT worked:

  mysql SHOW GRANTS FOR 'test'@123.123.123.123';

If the solution is still not apparent, include the output in your next message.

Michael

P.S.  I hope 'h4x0r' isn't really the password.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: show master/slave status privileges ?

[Michael Stassen]

Alan Williamson wrote:

I have made a user with the following command:
GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'


Silly question Morten, and I am sure you have probably done it, but
you are definitely running:

% mysql> FLUSH PRIVILEGES;


You only need to FLUSH PRIVILEGES if you directly edit the mysql db tables. 
 It is not needed with GRANT, REVOKE, or SET PASSWORD.  See the manual for 
the details .


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Performance of DB with many tables

[Brent Baisley]

Usually the performance hit is coming from the OS/file system.  
Tracking 100,000 file handles along with all the other stuff the OS  
is doing will slow any system down. If you have 1,000 tables, you may  
want to rethink you data model. If you truly need that many tables,  
you're better off splitting the system among a few computers, putting  
some tables on one and other table on another.



On Sep 1, 2005, at 11:16 AM, Chris Cutler wrote:


Hello,

My apologies for repeating a question asked earlier[1] but the
question and the answer were a little vague:

Does the number of tables in a database affect MySQL's performance?
Specifically, if I have a database with 1,000 tables in it, will it
be slower than usual due to the number of tables?  What about a
database with 10,000 or 100,000 tables?

As one section of the MySQL manual points out[2]: "If you have many
MyISAM or ISAM tables in a database directory, open, close, and
create operations are slow".  But what about INNODB?  And are there
table engine-independent implementation details which might cause
performance problems for a database with many tables?

Thanks,
Chris

[1] http://lists.mysql.com/mysql/174461
[2] http://dev.mysql.com/doc/mysql/en/creating-many-tables.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]






--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Load parts of a file into a Table

[SGreen]

[EMAIL PROTECTED] wrote on 09/02/2005 08:46:59 AM:

> Hi,
> 
> i have a file which i need to import in about 7 tables. For this reason
> i need toimport only parts of this file into each of the tables.
> 
> Is there a way to do this with LOAD DATA INFILE or what would be the
> preferred way with just having mysql standard tool at hand??
> 
> Cheers
> 
> Thorsten
> 
> -- 
> http://www.fastmail.fm - IMAP accessible web-mail
> 
> 
Here is how I have approached this problem in the past. I create a table 
that matches the organization of the data in the raw text file you 
currently have. I bulk import the whole file into this table. 

Now, I take care of as many data scrubbing tasks as I can. I look for 
invalid records, incomplete records, duplicates (if they are not allowed) 
and anything else that is just "junk" and strip it out of the RawImport 
table. That way, as I write my queries to copy rows from RawImport into my 
"production" tables I know I shouldn't be filling up my good tables with 
bad data. 

If the raw data is heirarchical in nature, I will usually add an auto 
increment column to the RawData table (sometimes the data doesn't need 
it). This comes in VERY handy when trying to re-link child records back to 
their parents as you add them to the production tables.  I may also add a 
RawID field to my production tables. That way when I add a record from my 
raw data into my production table, I have an easy way to identify the new 
parent id value in an existing child-parent relationship. Also, fill in 
your production tables from the top of the heirarchy on down. Don't start 
at the bottom and try to work your way up.

One other thing you may consider doing is to disable keys and foreign keys 
on your production tables, at least until you stabilize your new 
production data set. I try to save my queries into a SQL script as I build 
each step. That way, if I realize I screwed something up, I can fix the 
script, reimport the raw data, then re-run the script (hopefully producing 
a proper data merge this time).  Take a backup of your production table 
just before you start merging in the raw data. That way you can restore to 
a "last known good state" if things turn pear shaped.

Best of luck!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Performance of DB with many tables

[Pooly]

2005/9/1, Chris Cutler <[EMAIL PROTECTED]>:
> Hello,
> 
> My apologies for repeating a question asked earlier[1] but the
> question and the answer were a little vague:
> 
> Does the number of tables in a database affect MySQL's performance?
> Specifically, if I have a database with 1,000 tables in it, will it
> be slower than usual due to the number of tables?  What about a
> database with 10,000 or 100,000 tables?
> 
> As one section of the MySQL manual points out[2]: "If you have many
> MyISAM or ISAM tables in a database directory, open, close, and
> create operations are slow".  

they are slow due to the underlying filesystems (AFAIK)

> But what about INNODB?  And are there
> table engine-independent implementation details which might cause
> performance problems for a database with many tables?


il you only have one innoDB tablespace, it should not be a problem,
but for the option to have one file per innodb table, that's the same
conclusion.

> 
> Thanks,
> Chris
> 
> [1] http://lists.mysql.com/mysql/174461
> [2] http://dev.mysql.com/doc/mysql/en/creating-many-tables.html
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: backup and restore a database in a query ？

[SGreen]

Arno Coetzee <[EMAIL PROTECTED]> wrote on 09/02/2005 04:37:48 AM:

> shuming wang wrote:
> 
> > Hi,
> > Could we do a database dump/backup  in a query like below ?
> > mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 
> > -u root -p -C mydb>mydbfile
> > or restore a database in a query like below ?
> > mysql.exe -h 192.168.0.1 -u root -p -C mydb >
> > Then we can do backup and restore in GUI mode without call 
> > mysqldump.exe,mysql.exe in character mode .
> > Best regard!
> > Shuming Wang
> >
> > _
> > Express yourself instantly with MSN Messenger! Download today it's 
> > FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> >
> Hi shuming
> 
> i did not look at all your options , but ignoring the options it most 
> def is possible.
> 
> i am currently doing backups like this
> 
> mysqldump -uuser -ppassword -hIPAddress -n -t >dumpfile
> 
> i then retrieve the files via ftp and then import them as follow
> 
> mysql -uuser -ppassword -hIPAddress  
> hope this helps.
> 
> contact me if you need any help.
> 
> -- 
> Arno Coetzee
> Developer
> Flash Media Group
> Office : 2712 342 7595
> Mobile : 2782 693 6180
> 

The alternative to using "mysqldump ... > dumpfile" is to use the -r 
option to specify a file. That avoids needing to redirect the console 
output of mysqldump by telling mysqldump where you want it to put the data 
directly. In a console window, call up 

mysqldump --help 

and it will show you all of the options it accepts. Or, if that is too 
cryptic, you could always RTFM : 
http://dev.mysql.com/doc/mysql/en/mysqldump.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Performance of DB with many tables

[Chris Cutler]

Hello,

My apologies for repeating a question asked earlier[1] but the 
question and the answer were a little vague:

Does the number of tables in a database affect MySQL's performance?  
Specifically, if I have a database with 1,000 tables in it, will it
be slower than usual due to the number of tables?  What about a
database with 10,000 or 100,000 tables?

As one section of the MySQL manual points out[2]: "If you have many
MyISAM or ISAM tables in a database directory, open, close, and
create operations are slow".  But what about INNODB?  And are there
table engine-independent implementation details which might cause
performance problems for a database with many tables?

Thanks,
Chris

[1] http://lists.mysql.com/mysql/174461
[2] http://dev.mysql.com/doc/mysql/en/creating-many-tables.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

INNODB memory allocation error during startup

[eddie]

Hello kind sirs,


I have a DL380 G4 box, Dual Xeon 3.0Ghz with 6GB of DDR2 Ram,
I'm Linux CentOS 4 with a 2.6.12.5 vanilla kernel, and a remote storage 
which I access via iSCSI (linux-iscsi-4.0.2 complied as a module) 
connected with two Gigabit interfaces. the 'balancing' between the two 
links is maintained by multipathd (mutlipath-tools 0.4.4).


The situation is like this:

I had a DL380 G4 box with 4GB of DDR2 Ram that was running RedHat 
ES3-Update4 with stock redhat kernel and mysql 4.0.22 Intel-ICC 
precomplied binaries (downloaded from mysql.com - 
mysql-standard-4.0.22-pc-linux-gnu-i686-icc-glibc23.tar.gz ) the storage 
 was a local Raid5 of 4 10K RPM 72Gb SCSI disks.


On the old box, everything worked fine, other than IO problems to the 
local raid, so I've moved it to the new box:


I've moved the data 'as-is' to the new iSCSI box, upgraded the MySQL 
binary to 4.0.25 (still, precomplied Intel-ICC binaries), and kept the 
my.cnf as it was on the previous box:


-- my.cnf --

innodb_additional_mem_pool_size = 200M
innodb_buffer_pool_size = 2G
innodb_data_file_path = 
ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G;ibdata5:10G;ibdata6:10G

innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

-- my.cnf --

The problem is that when I start MySQL I get the following error:

-- error log --

050902 16:06:12  mysqld started
InnoDB: Fatal error: cannot allocate 2097168384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 214666988 bytes. Operating system errno: 12
InnoDB: Cannot continue operation!
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We now intentionally generate a seg fault so that
InnoDB: on Linux we get a stack trace.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong

and this may fail.

key_buffer_size=16777216
read_buffer_size=1044480
max_used_connections=0
max_connections=200
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 1858782 K

bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x83a3008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
Stack trace seems successful - bottom reached
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa2a17e7d  is invalid pointer
thd->thread_id=1701522733
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
050902 16:06:12  mysqld ended

-- error log --

I've tried playing with 'innodb_additional_mem_pool_size' and 
'innodb_log_file_size' and the only thing that works is when I lower 
'innodb_additional_mem_pool_size' to 1500M, anything higher than that 
doesn't work.


I've checked ulimits on the machine and it looks fine:

-- ulimit -a --
core file size  (blocks, -c) 0
data seg size   (kbytes, -d) unlimited
file size   (blocks, -f) unlimited
pending signals (-i) 52735
max locked memory   (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files  (-n) 4096
pipe size(512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size  (kbytes, -s) 8192
cpu time   (seconds, -t) unlimited
max user processes  (-u) 52735
virtual memory  (kbytes, -v) unlimited
file locks  (-x) unlimited

-- ulimit -a --

Also, before I start MySQL I see the box has about 5900M of free memory 
(free -m).


Any help would be greatly appritiated.

Thanks in advanced,
Eddie.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LIMIT alternative

[Vladimir B. Tsarkov]

Hello!

Bastian Balthazar Bux, Pooly, Shawn Green, Peter Brawley, I thank you all!

In fact, I don't understand why this command is not standardized (ANSI 
SQL, etc.).

-- 
Удачи!
Владимир

Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: show master/slave status privileges ?

[Alan Williamson]

> I have made a user with the following command:
> GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'

Silly question Morten, and I am sure you have probably done it, but
you are definitely running:

% mysql> FLUSH PRIVILEGES;

-- 
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.
 Daily OS News @ http://compiledby.spikesource.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

show master/slave status privileges ?

[Morten Kallesøe]

Hi

I have been trying to get my mysql server to do as i want, but its not quite 
playing along.

I need to monitor the replication status from a remote server, first by hand, 
2nd by a perl script.

But i keep getting this darn message. 

mysql> show master status;
ERROR 1227: Access denied. You need the SUPER,REPLICATION CLIENT privilege for 
this operation

I have made a user with the following command:
GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r'

Shouldnt that be enough? Or am i just missing something,

I have also tried to make the user with:
GRANT SUPER,REPLICATION CLIENT TO 'test'@'123.123.123.123' IDENTIFIED BY 
'h4x0r';
No luck there ethier.


Regards
Morten

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Load parts of a file into a Table

[thorsten . moeller]

Hi,

i have a file which i need to import in about 7 tables. For this reason
i need toimport only parts of this file into each of the tables.

Is there a way to do this with LOAD DATA INFILE or what would be the
preferred way with just having mysql standard tool at hand??

Cheers

Thorsten

-- 
http://www.fastmail.fm - IMAP accessible web-mail


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqldump: Error 2013

[Gustafson, Tim]

Hello everyone!

I just wanted to give everyone an update.  I'm still getting this error
when I try to back up this database table.  I don't get it at the same
row each time - today was at row 1,618, yesterday it was at row 24,566.
Just a reminder of my symptoms:

1. mysqldump is the only thing reporting any errors
2. the database server itself is not crashing
3. the timeouts on the database server are all set to 86,400 seconds
4. there is plenty of disk space on both the database server and the
backup media
5. the max_packet_size is 100MB
6. the maximum row size is less than 50MB

I have run the backup by hand a few times (not as part of a cron job,
but rather from my session instead) and it does complete (after about
4-5 hours).  That would be fine, except that the backup slows the entire
system down, so I can't run it during the day - that's why it's usually
part of a cron job that runs at 1AM UTC.

Can anyone offer some suggestions as to what's causing this, and what I
might be able to do to fix it?  Is there any way to maybe split the
backups into 3 or 4 pieces so that no one .sql file is so big and no one
run against the database is so long?

Thanks in advance!

Tim Gustafson
MEI Technology Consulting, Inc
[EMAIL PROTECTED]
(516) 379-0001 Office
(516) 908-4185 Fax
http://www.meitech.com/ 


smime.p7s
Description: S/MIME cryptographic signature

MySQL Users

[Jaspreet Singh]

Hi,

I am having problems managing mysql users. I use Active directory/LDAP
for user management and the user names could be 

[EMAIL PROTECTED] , [EMAIL PROTECTED] etc etc...


I want to use same users or atleast users with the same name to be able
to create Database.

The problem is that MySQL users can only be 16 chars loong (bad !!) and
cannot have '@' or '#' in the name.

Is there a way out ... Is there a way so that i can directly use system
users and not maintain this users table inside MySQL

Thanx in anticipation,
Jaspreet Singh.

-- 

Don't Walk as if you own the world,
Walk as if you don't care who owns it.

Jaspreet Singh
Software Engineer,
Ensim India.
[EMAIL PROTECTED]
+91 9890712226


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Remote access denial

[Dobromir Velev]

Hi,
Does the 10.4.0.101 reverse resolve to a specific hostname? If it does you are 
probably running MySQL with the skip_hostname_resolve option and if it 
doesn't there is no way the MySQL server could tell from which host the 
request is comming.

My opinion is that it is always better to use the IP-addresses instead of the 
hostnames and use skip_hostname_resolve - the connection overhead will be 
smaller this way.

You can also check the comments on
http://dev.mysql.com/doc/mysql/en/dns.html

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 02 September 2005 13:38, T. Horsnell wrote:
> After replacing a 3.23.28 server with 4.1.14 server binaries on an Alpha
> running Tru64 5.1B, I get things like:
>
> ERROR 1045 (28000): Access denied for user 'tsh'@'10.4.0.101' (using
> password: YES)
>
> when trying to connect from a remote host.
>
> The problem goes away if I replace hostnames by IP addresses
> in the mysql 'user' tables. I see from the archives that this
> problem has been reported recently by others. Is there a fix?
> (apart from changing every relevant entry in the user tables)
> I cant see anything relevant in the Buglists.
>
>
> Cheers,
> Terry.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: a difficult join query question

[Dobromir Velev]

Hi,
I would rather put the attributes.attribute='pcname' in the join clause
It should look something like this

SELECT inventory.invid,inventory.model,attributes.value as pcname
 FROM inventory
 LEFT JOIN attributes ON (inventory.invid=attributes.invid and 
attributes.attribute='pcname' );

HTH

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 02 September 2005 14:37, Klemens Ullmann wrote:
> hello!
>
> I've got two tables for an IT hardware inventory:
>
> ### table inventory:
> invid  model
> ---
> 1001   HP Notebook// no attributes
> 1002   Dell Desktop   // only one attribut 'pc-name'
> 1003   Acer Laptop// attributes 'pc-name' & 'harddisk'
> 1004   Apple iBook// only one attribut 'harddisk'
>
> ### table attributes:
> id   invid  attribute  value
> -
> 501  1002   pcname atpc01
> 502  1003   pcname atpc02
> 503  1003   harddisk   20GB
> 504  1004   harddisk   40GB
>
> what I want is a list of all computers (without exeptions) in the
> following form:
> invid - modell - pcname
>
> my best guess zu date of a possible query is:
> SELECT inventory.invid,inventory.model,attributes.value as pcname
> FROM inventory
> LEFT JOIN attributes ON (inventory.invid=attributes.invid)
> WHERE attributes.attribute='pcname' or attributes.attribute is NULL;
>
> ### result:
> invid  model pcname
> ---
> 1001   HP Notebook   NULL
> 1002   Dell Desktop  atpc01
> 1003   Acer Laptop   atpc02
>
>
> now my problem are the missing computers which have an attribute, but
> not a 'pc-name'-attribute.
> (in the example above the missing iBook)
>
> thank you for suggestions how to solve the problem!
>
> have a nice day,
>
>  klemens ullmann / vienna

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: backup and restore a database in a query ？

['Alan Williamson']

> Could we do a database dump/backup  in a query like below ?
> mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u 
> root -p -C mydb>mydbfile
> or restore a database in a query like below ?
> mysql.exe -h 192.168.0.1 -u root -p -C mydb 
> Then we can do backup and restore in GUI mode without call 
> mysqldump.exe,mysql.exe in character mode .

Ah okay now i see what you are trying to do.   The [mysqldump] is a utility 
that sits outside of the main mysql engine.  You cannot invoke this from within 
the mysql shell from the best of my knowledge.

As for pulling in backups from the mysql shell, then yes that is possible using 
a number of techniques:

% mysql> SOURCE [path to your file]
or
% mysql> LOAD DATA [path to your file]

Links to more information:
http://dev.mysql.com/doc/mysql/en/load-data.html
http://blog.spikesource.com/mysql_hotbackup.htm

hope this helps,

alan

-- 
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.   
   t: 650 249 4279
   b: http://compiledby.spikesource.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

a difficult join query question

[Klemens Ullmann]

hello!

I've got two tables for an IT hardware inventory:

### table inventory:
invid  model
---
1001   HP Notebook// no attributes
1002   Dell Desktop   // only one attribut 'pc-name'
1003   Acer Laptop// attributes 'pc-name' & 'harddisk'
1004   Apple iBook// only one attribut 'harddisk'

### table attributes:
id   invid  attribute  value
-
501  1002   pcname atpc01
502  1003   pcname atpc02
503  1003   harddisk   20GB
504  1004   harddisk   40GB

what I want is a list of all computers (without exeptions) in the 
following form:

invid - modell - pcname

my best guess zu date of a possible query is:
SELECT inventory.invid,inventory.model,attributes.value as pcname
   FROM inventory
   LEFT JOIN attributes ON (inventory.invid=attributes.invid)
   WHERE attributes.attribute='pcname' or attributes.attribute is NULL;

### result:
invid  model pcname
---
1001   HP Notebook   NULL
1002   Dell Desktop  atpc01
1003   Acer Laptop   atpc02


now my problem are the missing computers which have an attribute, but 
not a 'pc-name'-attribute.

(in the example above the missing iBook)

thank you for suggestions how to solve the problem!

have a nice day,

klemens ullmann / vienna

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Remote access denial

[T. Horsnell]

After replacing a 3.23.28 server with 4.1.14 server binaries on an Alpha
running Tru64 5.1B, I get things like:

ERROR 1045 (28000): Access denied for user 'tsh'@'10.4.0.101' (using password: 
YES)

when trying to connect from a remote host.

The problem goes away if I replace hostnames by IP addresses
in the mysql 'user' tables. I see from the archives that this
problem has been reported recently by others. Is there a fix?
(apart from changing every relevant entry in the user tables)
I cant see anything relevant in the Buglists.


Cheers,
Terry.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Populate values in an Excel sheet from MySQL

[inferno]

Hi,

   I had to do the same thing on a project and the problem was that if 
you use CSV you will not be able to make a formated excel document.
   I am using now *Spreadsheet_Excel_Writer 
 ( 
*http://pear.php.net/package/Spreadsheet_Excel_Writer ) and it does 
everything I need, including formating the page for printing, color, 
bold and boarder on cells and the best part is that it's no really hard 
to use.
   If you change your mind and want to generate that from perl you also 
have some PEAR packages for that, but I've sticked to PHP and with this 
the problem was solved and I generate my data on access, custom build 
depending on the select.


Best regards,
Cristi Stoica

Arjan Hulshoff wrote:


Hello Nick,

This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:

<--Begin Code-->
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet

cn.ConnectionString = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;"
cn.Open

sSQL = "SELECT * FROM database"

rs.Open sSQL, cn

If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields()  ' This line you can
adjust with your own code
rs.MoveNext
Loop

On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close

Set rs = Nothing
Set cn = nothing
<--End Code-->>

HTH,
Arjan.

-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 08:23 PM

To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



 

Re: Trouble with revoke all

[Scott Haneda]

on 9/2/05 1:27 AM, Gleb Paharenko at [EMAIL PROTECTED] wrote:

> Recreate grants for the 'root'@'localhost' user manually. For this
> 
> purpose restart server with --skip-grant-tables command line options
> 
> and modify the grant tables. See:
> 
>   http://dev.mysql.com/doc/mysql/en/resetting-permissions.html
> 
> http://dev.mysql.com/doc/mysql/en/privileges.html

Modify them how?
show grants for 'root'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, LOCK TABLES,
EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'localhost'
IDENTIFIED BY PASSWORD '' WITH GRANT OPTION

This looks pretty much full access to me?
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Upgrade from 4.0.12 to 4.0.25

[Gleb Paharenko]

Hello.



Yes, that link was for 4.1.x, but probably it means

that you should read all change-logs between

4.0.12 and 4.0.25, to be aware about possible 

incompatibilities.







Osvaldo Sommer <[EMAIL PROTECTED]> wrote:

> Thanks, but that link is for 4.1.x and not for 4.0.x 

> 

> Osvaldo Sommer

> 

> -Original Message-

> From: Nuno Pereira [mailto:[EMAIL PROTECTED] 

> Sent: Thursday, September 01, 2005 7:38 AM

> To: Osvaldo Sommer

> Cc: mysql@lists.mysql.com

> Subject: Re: Upgrade from 4.0.12 to 4.0.25

> 

> Osvaldo Sommer wrote:

>> Hi List

>>  

>> I look up on the online manual for directions for this kind of upgrade

>> but i found nothing.

>> Is there something I have to do extra or the upgrade so smoth?

>>  

>> Osvaldo Sommer

> 

> See http://lists.mysql.com/mysql/186726

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: database

[Gleb Paharenko]

Hello.



Please, do not attache files to your messages. Lots of users

on the list don't read them.



> Release:mysql-3.23.38



Please, do not report bugs with such an old MySQL version. Check if

it still exists on the latest release. See:

  http://dev.mysql.com/doc/mysql/en/bug-reports.html





dEeZAcK SweETtY <[EMAIL PROTECTED]> wrote:

> [-- multipart/alternative, encoding 7bit, 0 lines --]

> 

>[-- text/plain, encoding 8bit, charset: iso-8859-1, 4 lines --]

> 

>  

>

> -

> Start your day with Yahoo! - make it your home page 

>[-- text/html, encoding 8bit, charset: iso-8859-1, 3 lines --]

> 

> [-- text/plain, encoding 7bit, charset: US-ASCII, 33 lines, name: 
> mysqlbug.txt --]

> [-- Description: 2766465791-mysqlbug.txt --]

> 

> From: [EMAIL PROTECTED]

> To:   [EMAIL PROTECTED]

> Subject: cannot create database

> 

> Description:

>How to get the promt "mysql>"?

>Usually i will follow the steps at the command prompt:

>1.cd..

>2.cd mysql\bin..

>

> How-To-Repeat:

>

> 

> Fix:

>

> 

> Synopsis:   Cannot create database

> Submitter-Id:   [EMAIL PROTECTED]

> Originator: Diana

> Organization:   Student from Multimedia Universuty

> MySQL support:  licence 

> Severity:   non-critical 

> Priority:   medium | high 

> Category:   mysql client

> Class:  sw-bug  doc-bug  change-request support 

> Release:mysql-3.23.38

> 

> Exectutable:   [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt]

> Environment:   

> System:XP

> Compiler:  VC++ 6.0

> Architecture:  i

> 

> [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --]

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Trouble with revoke all

[Gleb Paharenko]

Hello.



> I am ssh'd int the box, so I am localhost, I am root, and it just does not

> want to let me remove access privs, in this case, all I have is select



Recreate grants for the 'root'@'localhost' user manually. For this

purpose restart server with --skip-grant-tables command line options

and modify the grant tables. See:

  http://dev.mysql.com/doc/mysql/en/resetting-permissions.html

http://dev.mysql.com/doc/mysql/en/privileges.html





> Mysql 4.0.18-standard



Your version of MySQL is rather old, I recommend you to upgrade to the

latest release (use 4.1.14 or 4.0.25).





Scott Haneda <[EMAIL PROTECTED]> wrote:

> Mysql 4.0.18-standard

> 

> revoke all on *.* from 'deleteatanytime'@'localhost';

> 

> mysql> revoke all on *.* from 'deleteatanytime'@'localhost';

> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

> mysql> revoke all on *.* from [EMAIL PROTECTED];

> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

> 

> I am ssh'd int the box, so I am localhost, I am root, and it just does not

> want to let me remove access privs, in this case, all I have is select

> privs.  For the longest time I just delete the user and recreate the

> permissions, but I would like to figure out how to make this work.

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: How to optimize fulltext selection?

[Gleb Paharenko]

Privet!



Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'

statements, amount of RAM, most problematic queries (use mysqldumpslow

utility to find them) include tables' definitions (use SHOW CREATE 

TABLE).





Michael Monashev <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> I  have  200-300  kb slow log daily with fulltext queries only :-( All

> queries  using  fulltext  indexes.  I use huge mysql cofig (huge.cfg).

> What can I change in the mysql configuration for better performance?

>  

> 

> Sincerely,

> Michael,

> http://xoib.com/ http://3d2f.com/

> http://qaix.com/ http://ryxi.com/

> http://gyxe.com/ http://gyxu.com/

> http://xywe.com/ http://xyqe.com/

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Connections with bad DNS cause lockups

[Gleb Paharenko]

Hello.





> is that when it gets probed, it COMPLETELY offlines (DOS) the server.



One upon a time I solved such an issue with MySQL on FreeBSD by switching

to the official binaries from:

  http://dev.mysql.com/downloads



Still, the recommended way to run MySQL on FreeBSD is compiling it from the 
ports. 





> Hi,

> 

> 

>Thanks for the reply.  I did see that page before, but

> I guess my bigger question is why if the DNS is broken/slow, why

> does the entire server come to a COMPLETE halt, no commands can

> be done via either TCP *OR* the socket.  If it just errored, that

> session took forever, whatever... I could understand. The problem

> is that when it gets probed, it COMPLETELY offlines (DOS) the server.

> And just *1* connection!

> 

>Just also seems difficult to keep proper documentation if

> we are using IPs and not complete hostnames.

> 

> 

>Thanks, Tuc

>> 

>> Tuc at T-B-O-H <[EMAIL PROTECTED]> wrote:

>> > Hi,

>> > 

>> >We seem to be running into a problem with our 

>> > installation that we don't understand.

>> > 

>> >We are running "mysql-server-4.0.25" from

>> > the ports collection on a FreeBSD 5.3-RELEASE-p10

>> > machine. Its tcpwrapper'd to only allow from our

>> > /24, and a single machine outside the /24.

>> > 

>> >At times, all of a sudden the server seems

>> > to "freeze". It appears that we've narrowed it down

>> > to an issue with people attacking the server that

>> > come from a site that has a bad reverse DNS setup.

>> > 

>> >Has anyone else seen this, or knows how

>> > to stop it?

>> > 

>> >Thanks, Tuc

>> > 

>> 

>> 

>> 

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Install error on fedora Core3

[Gleb Paharenko]

Hello.



> Trying to upgrade MySQL from 3.23 to 4.0.16 on a new fedora core3



If you have a fresh installation, it is much better to use a latest 

release (4.1.14 now).



> 050901 17:33:34 [ERROR] Can't create interrupt-thread (error 13, errno:



[EMAIL PROTECTED] gleb]$ perror 13

OS error code  13:  Permission denied



Are you installing MySQL as root? SELinux might prohibit

the installation as well. 







"Jeff" <[EMAIL PROTECTED]> wrote:

> Hey all,

> 

> Trying to upgrade MySQL from 3.23 to 4.0.16 on a new fedora core3

> system.

> 

> I keep getting the following when I run the servers-standard rpm...

> 

> Preparing...###

> [100%]

>   1:MySQL-server-standard  ###

> [100%]

> 050901 17:33:34 [ERROR] Can't create interrupt-thread (error 13, errno:

> 13)

> Installation of system tables failed!

> 

> Examine the logs in /var/lib/mysql for more information.

> You can also try to start the mysqld daemon with:

> /usr/sbin/mysqld --skip-grant &

> You can use the command line tool

> /usr/bin/mysql to connect to the mysql

> database and look at the grant tables:

> 

> shell> /usr/bin/mysql -u root mysql

> mysql> show tables

> 

> Try 'mysqld --help' if you have problems with paths. Using --log

> gives you a log in /var/lib/mysql that may be helpful.

> 

> The latest information about MySQL is available on the web at

> http://www.mysql.com

> Please consult the MySQL manual section: 'Problems running

> mysql_install_db',

> and the manual section that describes problems on your OS.

> Another information source is the MySQL email archive.

> Please check all of the above before mailing us!

> And if you do mail us, you MUST use the /usr/bin/mysqlbug script!

> Starting MySQL...[FAILED]

> 

> Anyone have this problem and know how to fix it???

> 

> Thanks,

> 

> Jeff

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: install_driver (mysql) failed

[Gleb Paharenko]

Hello.



I'm not a Solaris expert but the general way for solving similar

problems is described at:

  http://dev.mysql.com/doc/mysql/en/perl-support-problems.html

  http://dev.mysql.com/doc/mysql/en/myodbc-unix-shared-libraries.html







Uthra Rao <[EMAIL PROTECTED]> wrote:

>We are running mysql 4.1.13 on solaris 9 box. Everything was fine until 

>someone accidentally deleted the mysql directory where the application was 

>installed. I had to retrieve it from our backup tape. I then stopped and 

>started mysqld daemon. Please note that we had installed mysql with the 

>binary tar file. Since we restored the mysql application directory form our 

>backup we are getting the following error with our databases that 

>interfaces with perl in the cgi-bin.

>

>

>Software error:

>

>install_driver(mysql) failed: Can't load 

>'/usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris/auto/DBD/mysql/mysql.so' 

>for module DBD::mysql: ld.so.1: perl: fatal: libmysqlclient.so.10: open 

>failed: No such file or directory at 

>/usr/local/lib/perl5/5.8.0/sun4-solaris/DynaLoader.pm line 229.

>  at (eval 4) line 3

>Compilation failed in require at (eval 4) line 3.

>Perhaps a required shared library or dll isn't installed where expected

>  at /PATH/PATH/PATH/cgi-bin/filename.cgi line 55

>

>

>I would appreciate some help as soon as possible. Thank you.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Master Log Files

[Gleb Paharenko]

Hello.



I've wanted to say that you should check if, for example, the command

line options of mysqld program had been changed. 



> I am sure that there is no change in the 'my.cnf'.



Check if it uses exactly this my.cnf, not another my.cnf which is

situated in a different place.





"Tedy Aulia" <[EMAIL PROTECTED]> wrote:

> Thanks for your reply Gleb,

> 

> I understand that we can setup the binlogfile name in 'my.cnf' file, but

> I am sure that there is no change in the 'my.cnf'.

> 

> 

> The hostname program reports the machine name correctly i.e. foo.

> 

> 

> Cheers,

> Tedy

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please I need inputs on "Lost connection to MySQL server during query"

[Jerome Macaranas]

On Thursday 01 September 2005 22:14, [EMAIL PROTECTED] wrote:
> JM <[EMAIL PROTECTED]> wrote on 09/01/2005 06:01:57 AM:
> > Hi all,
> >
> >Ive read this link "http://dev.mysql.com/doc/mysql/en/gone-away.
> > html" hoping
> > this has something to do with the error...
> >
> >Ive already checked based on the page the list of roots of error
> > to produce
> > gone-away.. w/c i hope leads to " Lost connection to MySQL server during
> >
> > query " and non of it is present.
> >
> >One thing Ive noticed.  When I tried connecting using mysql
> > client from web
> > server to my DB box its takes time before mysql console shows-up after
>
> giving
>
> > the password.
> >Client and Server are connected through a LAN.
> >
> >Based on ping an average of .1 ms reply..
> >
> > tia,
>
> OK, we have the error message (good). We know what you have looked at to
> solve the error (also good). What we don't have is what you did to create
> the error in the first place.

the error is somehow unpredictable.. sometimes when i access the first page.. 
its works.. trying refreshing it.. after sometime its works. trying to do it 
every once so often then i encounter the problem...

>
> Please describe what you are doing to create the error, the data you are
> doing it with, and the tools involved in both ends of the process. Then we
> can understand your problem.
>


> Thanks!
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please I need inputs on "Lost connection to MySQL server during query"

[Jerome Macaranas]

On Thursday 01 September 2005 20:41, Pooly wrote:
> Hi,
>
> what is the error in your error log ?

im using  MySQL-client-4.1.12-1 binary from mysql website

i got this..

050901 14:50:44 [Warning] Asked for 196608 thread stack, but got 126976 
-- im trying to fix this by adding set-variable thread_stack=192K
but everytime I restart the server i still get the warning..

050901 14:50:45  InnoDB: Started; log sequence number 0 43951
050901 14:50:45 [Warning] mysql.user table is not updated to new password 
format; Disabling new password usage until mysql_fix_privilege_tables is run

if ill be doing what the log file says execute mysql_fix_privilege_tables .. 
would there be any draw backs or problems that ill be encountering..

> Does your server crash for every query it makes ? Or only some ?

the server doesnt crash i check the uptime for the DB and its up fror 12 
days..

> Are you using the official binary from MySQL or the one used for you
> distro vendor ?

no i downloaded the binary from mysql website..

> Are you using UDF ?

no

> ... We need more inputs ;-)
>
> 2005/9/1, JM <[EMAIL PROTECTED]>:
> > Hi all,
> >
> > Ive read this link
> > "http://dev.mysql.com/doc/mysql/en/gone-away.html"; hoping this has
> > something to do with the error...
> >
> > Ive already checked based on the page the list of roots of error
> > to produce gone-away.. w/c i hope leads to " Lost connection to MySQL
> > server during query " and non of it is present.
> >
> > One thing Ive noticed.  When I tried connecting using mysql
> > client from web server to my DB box its takes time before mysql console
> > shows-up after giving the password.
> > Client and Server are connected through a LAN.
> >
> > Based on ping an average of .1 ms reply..
> >
> > tia,
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
> --
> Pooly
> Webzine Rock : http://www.w-fenec.org/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: backup and restore a database in a query ？

[Arno Coetzee]

shuming wang wrote:


Hi,
Could we do a database dump/backup  in a query like below ?
mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 
-u root -p -C mydb>mydbfile

or restore a database in a query like below ?
mysql.exe -h 192.168.0.1 -u root -p -C mydbThen we can do backup and restore in GUI mode without call 
mysqldump.exe,mysql.exe in character mode .

Best regard!
Shuming Wang

_
Express yourself instantly with MSN Messenger! Download today it's 
FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/




Hi shuming

i did not look at all your options , but ignoring the options it most 
def is possible.


i am currently doing backups like this

mysqldump -uuser -ppassword -hIPAddress -n -t >dumpfile

i then retrieve the files via ftp and then import them as follow

mysql -uuser -ppassword -hIPAddress http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

backup and restore a database in a query ？

[shuming wang]

Hi,
Could we do a database dump/backup  in a query like below ?
mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u 
root -p -C mydb>mydbfile

or restore a database in a query like below ?
mysql.exe -h 192.168.0.1 -u root -p -C mydbThen we can do backup and restore in GUI mode without call 
mysqldump.exe,mysql.exe in character mode .

Best regard!
Shuming Wang

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Populate values in an Excel sheet from MySQL

[Arjan Hulshoff]

Hello Nick,

This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:

<--Begin Code-->
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet

cn.ConnectionString = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;"
cn.Open

sSQL = "SELECT * FROM database"

rs.Open sSQL, cn

If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields()' This line you can
adjust with your own code
rs.MoveNext
Loop

On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close

Set rs = Nothing
Set cn = nothing
<--End Code-->>

HTH,
Arjan.

-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
The information contained in this communication and any attachments is 
confidential and may be privileged, and is for the sole use of the intended 
recipient(s). Any unauthorized review, use, disclosure or distribution is 
prohibited. If you are not the intended recipient, please notify the sender 
immediately by replying to this message and destroy all copies of this message 
and any attachments. ASML is neither liable for the proper and complete 
transmission of the information contained in this communication, nor for any 
delay in its receipt.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]