Re: locking issues

[Hans van Dalen]

Excuse me for late response.

When I do it from the mysql prompt it works fine but trough the API (I use 
DAC for MySQL from microolap, maybe the bug is in there software!!) it 
doesn't work fine.

I have try it on several versions: 4.1.1, 5.0.0 and 4.0.0.

Thank you for your help. If you think it is not a database bug or a API 
bug I contact microolap for more support.

hans

At 19:38 8-3-04, you wrote:
Hans van Dalen <[EMAIL PROTECTED]> wrote:
>
> A question about locking. In my code (delphi but that doesn't much 
matter)
> I fire ad-hoc queries (select). Before the query i fire a locking query
> (read) for that particular tables. Something like this:
>
> lock tables tab1 a read, tab2 b read
> (execute)
> select * from tab1 a, tab2 b where.
> (execute)
>
> In this case I get an error: tab1 is not locked using LOCK TABLES.

Works fine for me:

mysql> lock tables tab1 a read, tab2 b read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab1 a, tab2 b where a.id=b.id;
+--+--+
| id   | id   |
+--+--+
|2 |2 |
+--+--+
1 row in set (0.00 sec)
What exactly versions do you use?

> If I change the locking query to: lock tables tab1 read, tab2 b read
> The first times I didn't get any error. But after some times of 
running my
> app the error: a is not locked using LOCK TABLES occur!?!?
>
> Ok finally I use the statement: lock tables tab1 read, tab1 a read, 
tab2 b
> read
> en everything works fine.
>
> The strangest thing about this is that sometimes it doesn't go wrong
> (locking using the lock statement with only the table aliases), other 
times
> it goes.
>
> When I execute the locking and the select query from my SQL explorer
> (borland) nothing is going wrong (makes no sense whatever you lock).
>
> It makes no difference I use version 4.0/4.1/5.0 (on suse linux).
>
> Anybody who has seen this before?
>
> Okay the workaround is simple: don't use table aliases or use the above
> showed locking statement, but it makes me angry something like this 
errors
> are raised on unexpected moments in my production environment,.
>



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


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


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

Re: Migrating Access databases to MySQL

[Martijn Tonies]

Hi,

> Hello people ! I was hoping for some wisdom from the good folks on this
> list about moving databases from access to MySQL.
>
> I am aware of the brute force method of exporting in CSV format or tab
> delimited formats and using mysqlimport to move the tables.
>
> 1. I was wondering if there was a way to dump access databases in SQL
> queries like the mysqldump utility does ?
>
> 2. When exporting tab delimited values from access and using mysqlimport
> everything works fine except date columns. All the data moves except the
> date field which shows up zero's I mysql after I use mysqlimport. Does
> anyone have a clue on what might be happening?
>
> 3. Is there any way other than downloading some shareware third party
> programs to transfer my databases from access to MySQL ?

You might want to try Database Workbench - it has a Schema transfer
utility and datapump. Check www.upscene.com

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

AW: Migrating Access databases to MySQL

[Freddie Sorensen]

Arjun

The free DBTools DBManager can import Access databases directly to MySQL :
http://www.dbtools.com.br/EN/dbmanagerpro.php

Freddie 

-Ursprüngliche Nachricht-
Von: Arjun Subramanian [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 19. März 2004 02:56
An: [EMAIL PROTECTED]
Betreff: Migrating Access databases to MySQL

Hello people ! I was hoping for some wisdom from the good folks on this list
about moving databases from access to MySQL.

I am aware of the brute force method of exporting in CSV format or tab
delimited formats and using mysqlimport to move the tables. 

1. I was wondering if there was a way to dump access databases in SQL
queries like the mysqldump utility does ?

2. When exporting tab delimited values from access and using mysqlimport
everything works fine except date columns. All the data moves except the
date field which shows up zero's I mysql after I use mysqlimport. Does
anyone have a clue on what might be happening?

3. Is there any way other than downloading some shareware third party
programs to transfer my databases from access to MySQL ?


I would appreciate any help at all. Thanks to everyone in advance !

Arjun Subramanian
Georgia Tech Station 32003
Atlanta GA 30332
Cell: +404.429.5513
"I'm MySQL certified. Are you?"


-Original Message-
From: Casey Sheridan [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 18, 2004 6:32 PM
To: [EMAIL PROTECTED]
Subject: writing queries to get distinct results

I have a table that has employee names, pay rates, and unique IDs.  I want
to select all of the distinct employee names, and if there are two employees
with the same name, I want to be able to choose only one; the one with the
highest pay rate.  If there are two identical employee names with the same
pay rate, I want to select the one that has the lowest unique ID number.
Can anyone help me on how to write this query?   Thanks!
-Casey Sheridan
[EMAIL PROTECTED]




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




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

Guru's advice needed ........[Security: SQL injection]

[Tariq Murtaza]

*Dear Friends!*

Can someone shed some light  on how "SQL injection" attack occurs when 
*magic_quotes_gpc *is"ON" and how it prevents when its "OFF". To my 
understanding  apostrophise are escaped automatically in POST/GET/COOKIE 
when its ON, so how it tends towards SQL Injection.

Someone suggested to keep magic_quotes_qpc OFF through .htaccess file 
and use following line of codes to prevent attacks at start of the file...


But unfortunately it does not work for nested POST requests. do anyone 
have better idea?
Secondly why we have to stripslashes while DB (mysql for example) is 
doing it for us on execution and another question arises doesn't it 
prevent from SQL injection attack when apostrophise are escaped in query.

*What is the best practices handling 'quotation marks'  in input string 
and how to prevent SQL injection.

*Looking forward for some advice from panel of experts on forum.
Thanks and have a nice day!*
*Cheers!
*Tariq*

Sum for time

[Elly Wisata]

Hi *,

Can somebody show me how to sum for time, my format is hh:mm:ss, need to do
sum for the time as we usually sum a field with integer type. But I am not
sure sum can use for time format.

Thanks in advance

 

~Elle~

 

Re: Migrating Access databases to MySQL

[Karam Chand]

Try SQLyog - www.webyog.com

I use the ODBC Import Facility regularly.

regards
Karam

--- Arjun Subramanian <[EMAIL PROTECTED]> wrote:
> Hello people ! I was hoping for some wisdom from the
> good folks on this
> list about moving databases from access to MySQL.
> 
> I am aware of the brute force method of exporting in
> CSV format or tab
> delimited formats and using mysqlimport to move the
> tables. 
> 
> 1. I was wondering if there was a way to dump access
> databases in SQL
> queries like the mysqldump utility does ?
> 
> 2. When exporting tab delimited values from access
> and using mysqlimport
> everything works fine except date columns. All the
> data moves except the
> date field which shows up zero's I mysql after I use
> mysqlimport. Does
> anyone have a clue on what might be happening?
> 
> 3. Is there any way other than downloading some
> shareware third party
> programs to transfer my databases from access to
> MySQL ?
> 
> 
> I would appreciate any help at all. Thanks to
> everyone in advance !
> 
> Arjun Subramanian
> Georgia Tech Station 32003
> Atlanta GA 30332
> Cell: +404.429.5513
> "I'm MySQL certified. Are you?"
> 
> 
> -Original Message-
> From: Casey Sheridan [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 18, 2004 6:32 PM
> To: [EMAIL PROTECTED]
> Subject: writing queries to get distinct results
> 
> I have a table that has employee names, pay rates,
> and unique IDs.  I
> want
> to select all of the distinct employee names, and if
> there are two
> employees
> with the same name, I want to be able to choose only
> one; the one with
> the
> highest pay rate.  If there are two identical
> employee names with the
> same
> pay rate, I want to select the one that has the
> lowest unique ID number.
> Can anyone help me on how to write this query?  
> Thanks!
> -Casey Sheridan
> [EMAIL PROTECTED]
> 
> 
> 
> 
> -- 
> 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]
> 


__
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
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: Insert statement where value depends on auto-generated value from previous insert

[Scott Plumlee]

Found that function right after I posted.  Thanks, it should do exactly 
what I need.

Roger Baklund wrote:
* Scott Plumlee

I've got two separate tables, each with id fields that are
auto-increment.  The created fields below are timestamps.  The tables
are Innodb tables using transactions to process the statements.  This
will be an online registration process for our business, using PHP and
MySQL.  PHP is using session ids for tracking state.
table1

id
first_name
last_name
created
etc.
table2
-
id
table1_id
created
etc
I need to insert a row into table1, using a null value for the id so it
generate an id automatically.  Then I need to insert a row into table2,
including the id from table 1 (table1.id needs to be inserted into
table2.table1_id).
Any best practices to doing this?


Use the LAST_INSERT_ID() function:

INSERT INTO table1 SET first_name='roger',last_name='baklund';
INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot';
This function is connection specific, you will get the correct id even if
you have multiple simultaneous users.
http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 >
http://www.mysql.com/doc/en/Information_functions.html#IDX1428 >
--
Roger


--

Scott Plumlee
PGP Public key: http://plumlee.org/pgp/   D64C 47D9 B855 5829 D22A  D390 
F8E2 9B58 9CBF 1F8D

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

no create/update time for InnoDB from SHOW TABLE STATUS?

[Ray Kiddy]

It looks as though, when I go into my databases, and use the command  
"SHOW TABLE STATUS", that InnoDB tables do not have some information.

I am on MySQL 4.1.0-alpha-debug. Has this been fixed in later versions?

Example:

mysql> show table status;
+--+++-++- 
+-+--+---+ 
+-+-++--- 
++-+
| Name | Type   | Row_format | Rows| Avg_row_length | Data_length |  
Max_data_length | Index_length | Data_free | Auto_increment |  
Create_time | Update_time | Check_time | Charset   |  
Create_options | Comment |
+--+++-++- 
+-+--+---+ 
+-+-++--- 
++-+
| cur  | InnoDB | Dynamic|  325214 |   2279 |   741294080 |  
   NULL |114163712 | 0 | 337810 | NULL   
  | NULL| NULL   | latin1_swedish_ci | pack_keys=1|  
InnoDB free: 6601728 kB |
| old  | InnoDB | Dynamic| 1233005 |  10405 | 12830375936 |  
   NULL |455147520 | 0 |1545059 | NULL   
  | NULL| NULL   | latin1_swedish_ci | pack_keys=1|  
InnoDB free: 6601728 kB |
+--+++-++- 
+-+--+---+ 
+-+-++--- 
++-+
2 rows in set (2.78 sec)

Notice the "Create_time" and "Update_time" data is NULL.

Any reason?

thanx - ray

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

Re: Migrating Access databases to MySQL

[Bob Ramsey]

Arjun Subramanian wrote:

Do you have any
specific sites or utilities in mind ?
 

Try the code posted in this newsgroup posting:

http://groups.google.com/groups?q=macro+access+export+mysql&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=tchs8rl5phqib7%40corp.supernews.com&rnum=3

It's a little long to post here.  It is for Access 97, so it may need 
some tweaking

There's also this project on freshmeat:

http://freshmeat.net/projects/exportsql/

Another project called exportsql here:

http://www.cynergi.net/exportsql/

I don't know if those two are the same or just have similar names.

Another macro here:

http://elmo.engineering.tech.nhl.nl/config/two/tools/access_to_mysql.txt

It's been a long time since I used one of these macros and I can't 
remember which one it was that I used.  You will probably have to do a 
little bit of tweaking if you have Access XP, but I don't know for sure.

Bob

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

RE: Migrating Access databases to MySQL

[Arjun Subramanian]

Hi bob ! thanks for your quick reply. I did do a search on google. I
bumped into a bunch of stuff that really lead nowhere. Do you have any
specific sites or utilities in mind ?

Arjun Subramanian
Georgia Tech Station 32003
Atlanta GA 30332
Cell: +404.429.5513
"I'm MySQL certified. Are you?"


-Original Message-
From: Bob Ramsey [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 18, 2004 9:07 PM
To: Arjun Subramanian
Subject: Re: Migrating Access databases to MySQL

Arjun Subramanian wrote:

>3. Is there any way other than downloading some shareware third party
>programs to transfer my databases from access to MySQL ?
>  
>

I have seen free macros that create a text file you can import into 
mysql; it creates the tables and inserts all of your data.  Do a google 
search, and if you still can't find anything let me know.

bob





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

Migrating Access databases to MySQL

[Arjun Subramanian]

Hello people ! I was hoping for some wisdom from the good folks on this
list about moving databases from access to MySQL.

I am aware of the brute force method of exporting in CSV format or tab
delimited formats and using mysqlimport to move the tables. 

1. I was wondering if there was a way to dump access databases in SQL
queries like the mysqldump utility does ?

2. When exporting tab delimited values from access and using mysqlimport
everything works fine except date columns. All the data moves except the
date field which shows up zero's I mysql after I use mysqlimport. Does
anyone have a clue on what might be happening?

3. Is there any way other than downloading some shareware third party
programs to transfer my databases from access to MySQL ?


I would appreciate any help at all. Thanks to everyone in advance !

Arjun Subramanian
Georgia Tech Station 32003
Atlanta GA 30332
Cell: +404.429.5513
"I'm MySQL certified. Are you?"


-Original Message-
From: Casey Sheridan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 18, 2004 6:32 PM
To: [EMAIL PROTECTED]
Subject: writing queries to get distinct results

I have a table that has employee names, pay rates, and unique IDs.  I
want
to select all of the distinct employee names, and if there are two
employees
with the same name, I want to be able to choose only one; the one with
the
highest pay rate.  If there are two identical employee names with the
same
pay rate, I want to select the one that has the lowest unique ID number.
Can anyone help me on how to write this query?   Thanks!
-Casey Sheridan
[EMAIL PROTECTED]




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

writing queries to get distinct results

[Casey Sheridan]

I have a table that has employee names, pay rates, and unique IDs.  I want
to select all of the distinct employee names, and if there are two employees
with the same name, I want to be able to choose only one; the one with the
highest pay rate.  If there are two identical employee names with the same
pay rate, I want to select the one that has the lowest unique ID number.
Can anyone help me on how to write this query?   Thanks!
-Casey Sheridan
[EMAIL PROTECTED]




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

Query: count(distinct field1… max(fieldn)) where…?

[Victor Spång Arthursson]

I need some help with a complex query of mine.

The query in question looks like this:

SELECT  DISTINCT film_film.filmid, titel, pdf, termin, aar, 
film_serier.serieid, serienamn, screener, har_affisch, har_bilder, max( 
datum )
FROM (
(
(
`film_film`
LEFT  JOIN film_rel_regissoerer ON film_film.filmid = 
film_rel_regissoerer.filmid_relid
)
LEFT  JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = 
film_regissoerer.regissoerid
)
LEFT  JOIN film_visningar ON film_film.filmid = film_visningar.filmid
)
LEFT  JOIN film_serier ON film_visningar.serieid = film_serier.serieid
GROUP  BY titel 

Earlier, before I added the max(datum) to the query, i could get the 
number of rows by doing:

--- SQL ---
SELECT count(  DISTINCT film_film.filmid, titel,
IF (
aar IS  NULL ,  '', aar
),
IF (
termin IS  NULL ,  '', termin
) )  AS antal
FROM (
(
(
`film_film`
LEFT  JOIN film_rel_regissoerer ON film_film.filmid = 
film_rel_regissoerer.filmid_relid
)
LEFT  JOIN film_regissoerer ON film_rel_regissoerer.regissoerid_relid = 
film_regissoerer.regissoerid
)
LEFT  JOIN film_visningar ON film_film.filmid = film_visningar.filmid
)
LEFT  JOIN film_serier ON film_visningar.serieid = 
film_serier.serieid LIMIT 0 , 30

The if-clauses where added because count returned wrong number of rows 
when there where NULL-values in the result…

What I need to do is to count the number of rows in the first query. 
But I can't figure how. Adding a max(datum) to the count-list simply 
wont do it; reports sql-errors…

Extremely thankful for some feedback.

Sincerely

Victor

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

Optimal RAID stripe size(s) for InnoDB?

[Jeremy Zawodny]

Has anyone done much testing with RAID stripe sizes for heavy
concurrency InnoDB-based applications?

I'm expecting that using a stripe size that matches InnoDB's page size
would make sense, but it could save a lot of testing if someone else
has already done this.

Thanks,

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 186 days, processed 2,941,866,394 queries (182/sec. avg)

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

RE: Maintaining fulltext

[Andrew Braithwaite]

Not sure if you can...

Maybe add it to the ToDo list at:

http://www.mysql.com/doc/en/Fulltext_TODO.html

Cheers,

Andrew

p.s. also see you at the conference... 

-Original Message-
From: Trevor Price [mailto:[EMAIL PROTECTED] 
Sent: Thursday 18 March 2004 21:10
To: [EMAIL PROTECTED]
Subject: Maintaining fulltext 

Mysqlians,


1.  How can I tell how many words a fulltext index has indexed?(for purposes
of estimating performance) 2.  How can I tell how much index file space a
full text is responsible for?
( for regular indicies I use (key_length +4)/.067 ) but my fulltext's
are all on text's.


Thanks,

Trevor

p.s.  See you at the conference.


--
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: String Concatenation Operator?

[Andrew Braithwaite]

> or must you use the CONCAT() function?

Yes

SELECT concat(firstname,' ',lastname) AS fullname FROM customers;

Cheers,

Andrew

-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED] 
Sent: Thursday 18 March 2004 22:30
To: [EMAIL PROTECTED]
Subject: String Concatenation Operator?

Does MySQL have a string contatenation operator, or must you use the
CONCAT() function?

SELECT firstname + ' ' + lastname AS fullname FROM customers


--
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: MyODBC and Windows XP

[Miguel Angel Solorzano]

At 19:14 18/3/2004, José Angel Blanco Linares wrote:
Hi,
Hi, everybody

I have in my office two pc machines. One of them is a Pentium III with Windows
98 second edition and MyODBC 3.51.06; the other is a Pentium 4 with Windows XP
and MyODBC 2.50.39. The MySQL database is running on a linux server, and via
MyODBC I connect to the MySQL server to run my Visual FoxPro desk 
applications.

The problem is: the Pentium III machine loads faster the remote tables 
than the
Pentium IV; or better said: the Pentium IV pc loads very very slow the remote
tables. I've tried with the two version of MyODBC, and I've got the same 
result.
What can it be?
Great chance is to be the SP 1 on XP, that makes MyODBC loss
performance when calling setlocal.
In this case the work around is to set on XP the MyODBC flag:
don't use setlocale.
Regards,

For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/
Miguel Angel Solórzano <[EMAIL PROTECTED]>
São Paulo - Brazil


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

String Concatenation Operator?

[Jim McAtee]

Does MySQL have a string contatenation operator, or must you use the
CONCAT() function?

SELECT firstname + ' ' + lastname AS fullname
FROM customers


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

Re: Insert statement where value depends on auto-generated value from previous insert

[Roger Baklund]

* Scott Plumlee
> I've got two separate tables, each with id fields that are
> auto-increment.  The created fields below are timestamps.  The tables
> are Innodb tables using transactions to process the statements.  This
> will be an online registration process for our business, using PHP and
> MySQL.  PHP is using session ids for tracking state.
>
> table1
> 
> id
> first_name
> last_name
> created
> etc.
>
> table2
> -
> id
> table1_id
> created
> etc
>
>
> I need to insert a row into table1, using a null value for the id so it
> generate an id automatically.  Then I need to insert a row into table2,
> including the id from table 1 (table1.id needs to be inserted into
> table2.table1_id).
>
> Any best practices to doing this?

Use the LAST_INSERT_ID() function:

INSERT INTO table1 SET first_name='roger',last_name='baklund';
INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot';

This function is connection specific, you will get the correct id even if
you have multiple simultaneous users.

http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 >
http://www.mysql.com/doc/en/Information_functions.html#IDX1428 >

--
Roger


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

Re: help in creating table

[Ligaya Turmelle]

You're using a foreign key.
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html.  In order
for the constraints to work, the DB must be type InnoDB.  Otherwise the data
checks for integrety must be done by you in the INSERT program.

Respectfully,
Ligaya Turmelle

"pramilav" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Dear All,
>
> I have created a table Product1 with columns pid, data, keyword. I had
> been requested to create another table Product2 with the columns pid,
> cname, itis given that (pid  -int - reference to Product1).
>
> My problem is how will i create a second table.
>
> Help needed.
>
> Thanks,
> Pramilav
>



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

Re: MyODBC and Windows XP

[Daniel Kasak]

José Angel Blanco Linares wrote:

  Hi, everybody

I have in my office two pc machines. One of them is a Pentium III with Windows
98 second edition and MyODBC 3.51.06; the other is a Pentium 4 with Windows XP
and MyODBC 2.50.39. The MySQL database is running on a linux server, and via
MyODBC I connect to the MySQL server to run my Visual FoxPro desk applications.

The problem is: the Pentium III machine loads faster the remote tables than the
Pentium IV; or better said: the Pentium IV pc loads very very slow the remote
tables. I've tried with the two version of MyODBC, and I've got the same result.
What can it be?

TIA
  

Have you got ODBC tracing ( or logging or whatever ) turned on on the
Pentium 4?
It's in your ODBC configuration thing. Under Win 2000 it's in Control
Panel ==> Admin Tools ==> ODBC. There should be a 'tracing' tab.
Don't know where it is under XP.

-- 

signature
Daniel Kasak

IT Developer

NUS Consulting Group

Level 5, 77 Pacific Highway

North Sydney, NSW, Australia 2060

T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989

email: [EMAIL PROTECTED]

website: http://www.nusconsulting.com.au



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

MyODBC and Windows XP

[José Angel Blanco Linares]

Hi, everybody

I have in my office two pc machines. One of them is a Pentium III with Windows
98 second edition and MyODBC 3.51.06; the other is a Pentium 4 with Windows XP
and MyODBC 2.50.39. The MySQL database is running on a linux server, and via
MyODBC I connect to the MySQL server to run my Visual FoxPro desk applications.

The problem is: the Pentium III machine loads faster the remote tables than the
Pentium IV; or better said: the Pentium IV pc loads very very slow the remote
tables. I've tried with the two version of MyODBC, and I've got the same result.
What can it be?

TIA

__
José Angel Blanco Linares
Depto Servicios de Apoyo Académico
Centro de Enseñanza Técnica Industrial
3641-3250, ext: 264

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

DISREGARD - MySQL 4.0.15 on SuSE 9.0 Pro - how do I uninstall ?

[Kevin Carpenter]

I used YaSt to uninstall - though I wasn't really happy with the uninstall because I 
used the find command on 'mysql' and found lots of stuff that was left behind after 
the uninstall.

Anyway I then reinstalled with rpm -i

It probably isn't working 100% yet but it is working enough that I can play at the 
command line - text interface.

I'll read the Docs and install some GUI interfaces for it.

 . . . the learning curve is pretty steep from where I am standing - but I have strong 
legs and want to climb.

-:>Kevin

>>> "Kevin Carpenter" <[EMAIL PROTECTED]> 03/18/04 12:53PM >>>
I've made a mess of this MySQL  installation.  I would like to uninstall and start 
over.




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

Maintaining fulltext

[Trevor Price]

Mysqlians,

1.  How can I tell how many words a fulltext index has indexed?(for 
purposes of estimating performance)
2.  How can I tell how much index file space a full text is responsible for?
   ( for regular indicies I use (key_length +4)/.067 ) but my 
fulltext's are all on text's.

Thanks,

Trevor

p.s.  See you at the conference.

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

Re: Insert statement where value depends on auto-generated value from previous insert

[Scott Plumlee]

Scott Plumlee wrote:

I've looked through the PHP Cookbook and the MySQL cookbook and haven't 
seen a solution.  I've thought about trying to make some unique hash 
with the data to be inserted but if there's another identical set of 
data, then the hash would match.  I can't use a timestamp in the hash
I think I was trying to say I can't use the time that the row was 
created because I don't know if the timestamp value I get upon insert 
will exactly match the value I would get from something like a NOW() 
statement.  IE, how do I know if the insert happens at the same moment I 
get a time value?

--

Scott Plumlee
PGP Public key: http://plumlee.org/pgp/   D64C 47D9 B855 5829 D22A  D390 
F8E2 9B58 9CBF 1F8D

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

Insert statement where value depends on auto-generated value from previous insert

[Scott Plumlee]

I've got two separate tables, each with id fields that are 
auto-increment.  The created fields below are timestamps.  The tables 
are Innodb tables using transactions to process the statements.  This 
will be an online registration process for our business, using PHP and 
MySQL.  PHP is using session ids for tracking state.

table1

id
first_name
last_name
created
etc.
table2
-
id
table1_id
created
etc
I need to insert a row into table1, using a null value for the id so it 
generate an id automatically.  Then I need to insert a row into table2, 
including the id from table 1 (table1.id needs to be inserted into 
table2.table1_id).

Any best practices to doing this? I've considered adding additional 
fields to both tables to represent a unique id or hash that will be 
generated by PHP.  That way I can tie the two tables together and pull 
the newly-generated table1.id value out based on the unique hash and 
insert it into table2.  But I'd rather not do that if I don't have to.

I don't know if the tables are locked with transactions.  If they were, 
I could insert into table1, then find the last id generated for table1 
and then put that into table2.  Do transactions behave this way?

I can't just use the PHP session id because someone with the same 
session may register another person, and then I would have the same 
session ID in two rows.  I can't assume the names or other info are 
unique either.

Any ideas or is the PHP unique id/hash going to be my best bet?  It just 
seems wasteful to have to add another field just to tie the data 
together until I can tie it together with the generated ids.

I've looked through the PHP Cookbook and the MySQL cookbook and haven't 
seen a solution.  I've thought about trying to make some unique hash 
with the data to be inserted but if there's another identical set of 
data, then the hash would match.  I can't use a timestamp in the hash

Now that I'm thinking about it, could I do this:
1.  Generate a timestamp value
2.  Insert into table1 all the info I need.
3.  Create a unique hash from the timestamp and inserted info
4.  Find the row and id from table1 where the hash of the timestamp 
value from step 1 and the data in the row match the hash from step 3
5.  Put the id value into table 2

Seems like a lot of work to do to find the answer.  Any gurus got an idea?

Scott Plumlee
PGP Public key: http://plumlee.org/pgp/   D64C 47D9 B855 5829 D22A  D390 
F8E2 9B58 9CBF 1F8D

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

Re: lost sa password

[Brent Baisley]

You need to stop your MySQL process and then startup MySQL without the 
grant tables (skip-grant-tables option). You can then change the 
password.
Here some documentation on how to do it in Windows, but it's pretty 
much the same for Unix.
http://www.mysql.com/doc/en/Resetting_permissions.html

On Mar 18, 2004, at 11:31 AM, jose manuel wrote:

Hi all:

Long time ago I've installed MySQL on my Debian Machine and I'm pretty 
sure I did'n left the "sa" pwd blank as recommended.

Now, I'm installing  other apps that need that "sa" pwd in order to be 
installed properly butI can't remember the pwd.

is possible to recover the sa password?

how?

--
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: InnoDB - alter column

[Heikki Tuuri]

Hi!

- Original Message - 
From: "Victor Pendleton" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 18, 2004 8:21 PM
Subject: Re: InnoDB - alter column


> 1. The column types must match in order to establish a foreign key
> relationship. Alter the data type would invalidate the Foreign key.
Therefore
> the key must be dropped first.
>
> 2. You can not have a relationship on a column that does not exists. Once
again
> the key must first be dropped.
>
> 3-4. Currently the user is unable to name Foreign key constraints.
Consequently
> the only way to know the constraint names is to use `show create table`.

in 4.0.18, and upcoming 4.1.2 you can explicitly name a foreign key
constraint.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Register now for the 2004 MySQL Users Conference!
http://www.mysql.com/events/uc2004/index.html


>  On Thu, 18 Mar 2004, Gilad Buzi wrote:
> >
> > Hello,
> > Does anyone have any idea why the following don't work in MySQL
> > 4.1.1-alpha with InnoDB tables:
> > 1. cannot change column type without first dropping the foreign key
> > (when there is one)
> > 2. cannot drop the column without first dropping the foreign key (if
> > there is one)
> > 3. cannot explicitly define a constraint's name when creating it.
> > 4. cannot automatically figure out the names of the constraints
> > associated with a column without manually parsing the results of "show
> > create table"
> >
> > I sent a more detailed explanation a couple of days ago.  I'm just
> > wondering if this is by design or something planned to be fixed.
> >
> > thanks,
> >
> > *Gilad Ezra Buzi
> > *R&D Engineer
> > Open Source Advocate
> >
> > *Concatel*
> > Avenida Puertos de Europa 100,
> > 08040 Barcelona (Spain)
> > tel. +34.93.567.97.10
> > fax +34.93.567.97.11


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

Re: DB consistency checking

[James Hughes]

On Mar 17, 2004, at 4:41 PM, Paul DuBois wrote:

At 14:06 -0800 3/17/04, James Hughes wrote:
I want to set up some automated table checking and the research I've 
done has left me scratching my head.

I'm looking for a definitive answer to this thread:
http://marc.theaimsgroup.com/?l=mysql&m=106159147322752&w=2
With respect to MyISAM tables in 4.0, there seems to be two different 
scenarios:

1) Daemon running with "--skip-external-locking"
a) I do NOT need to shut down the daemon
b) I do NOT need to flush the tables
c) CHECK TABLE tbl_name
	--OR--

a) Shut down the daemon
b) mysqladmin flush-tables
c) myisamchk -m tbl_name
2) Daemon running without "--skip-external-locking"
a) I do NOT need to shut down the daemon
b) I do NOT need to flush the tables
c) CHECK TABLE tbl_name
	--OR--

a) I do NOT need to shut down the daemon
b) mysqladmin flush-tables
c) myisamchk -m tbl_name
So, other than the clarification about CHECK TABLE, everything above 
looks A-OK?

I'm a little confused about the "CHECK TABLE" sql statement. In the 
docs (http://www.mysql.com/doc/en/CHECK_TABLE.html) it says "On 
MyISAM tables, it's the same thing as running myisamchk 
--medium-check tbl_name on the table."

If that's accurate then you couldn't safely run it on a mysqld daemon 
that's running with "--skip-external-locking"! That would also imply 
that you need to flush the tables first!
However in the thread above, Paul Dubois claims "You can also use 
CHECK TABLE, REPAIR TABLE, which have no problem of interaction with 
the server, and can be issued from remote clients."

So either CHECK TABLE (without any options) isn't the same thing as 
"myisamchk --medium-check" or Paul is mistaken.
It's probably a bad idea to believe anything that DuBois character 
says.
He is an awfully shifty lookin' fella. ;)

"same thing" means "has the same effect as", not "does the check the
same way that myisamchk does".
CHECK TABLE cannot possibly have any problem of interaction with the
server, because the way that CHECK TABLE operates is to instruct
the server to do the work.  This doesn't mean that you couldn't have
a problem if some other external program happens to try to mess around
with the table at the same time.
This was my gut feeling, but programmers do stupid stuff all the time - 
I mean... except for the MySQL crew, of course! Thanks for the 
clarification.

I should probably qualify this by saying that I'm looking for a "Best 
Practices" type answer rather than a technical discussion of all the 
various exceptions to the rule.

Thanks for any insight!

-James Hughes


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
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]

interesting....BUG?

[Nestor Florez]

People,

I have a php web application that has an admin page for inserting course records and 
one for selecting course records 
and a client page for selecting course record.  
In the admin side I insert records with an "insert into Course_Eng" and I select 
records witha "select * from Course_Eng"
In the client side I get records witha "select * from course_eng"

The kicker is that my client webbased select will only return 40 records (no limits 
are being use) but my admin
webbased select returns 200 records.  I SSH into the server and when I check the table 
"desc course_eng"
look good.
After scraching my head several times I found out that if  manually typed on the server
my select statement as "select * from Course_Eng" I would get 200 records back, but if 
I
typed "select * from course_eng" I would get 40 records.

Is this a bug? or a feature?

Whe I did a "show tables;", the table name is  "course_eng"  ther was no table 
"Course_Eng"

If I remember correct in the SQL syntax the case should not matter?

I change all my inserts and selects to "Course_Eng"  that seem to work and returned me 
the most records
My server is  a Mac OS 10.2 and the Mysql version is "server version: 4.0.16"

Any ideas?

thanks,

Nestor :-)

Néstor A. Flórez



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

Re: InnoDB - alter column

[Victor Pendleton]

1. The column types must match in order to establish a foreign key
relationship. Alter the data type would invalidate the Foreign key. Therefore
the key must be dropped first.

2. You can not have a relationship on a column that does not exists. Once again
the key must first be dropped.

3-4. Currently the user is unable to name Foreign key constraints. Consequently
the only way to know the constraint names is to use `show create table`.



 On Thu, 18 Mar 2004, Gilad Buzi wrote:
> 
> Hello,
> Does anyone have any idea why the following don't work in MySQL 
> 4.1.1-alpha with InnoDB tables:
> 1. cannot change column type without first dropping the foreign key 
> (when there is one)
> 2. cannot drop the column without first dropping the foreign key (if 
> there is one)
> 3. cannot explicitly define a constraint's name when creating it.
> 4. cannot automatically figure out the names of the constraints 
> associated with a column without manually parsing the results of "show 
> create table"
> 
> I sent a more detailed explanation a couple of days ago.  I'm just 
> wondering if this is by design or something planned to be fixed.
> 
> thanks,
> 
> *Gilad Ezra Buzi
> *R&D Engineer
> Open Source Advocate
> 
> *Concatel*
> Avenida Puertos de Europa 100,
> 08040 Barcelona (Spain)
> tel. +34.93.567.97.10
> fax +34.93.567.97.11
> 
> http://www.concatel.com
> 
> 

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

Re: Update using fields from another table

[beacker]

>Table 1
>id_2   |   date
>
>Table 2
>id_1   |   id_2
>
>Table 3
>id_1
>
>I want to set the table1.date = '2004-03-18' for each record in table3. Any
>ideas?

Based upon your description you could probably use:

UPDATE table3, table2, table1
SET table1.date = '2004-03-18'
where (table3.id1 = table2.id1) AND (table2.id2 = table1.id2);

Brad Eacker ([EMAIL PROTECTED])





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

InnoDB - alter column

[Gilad Buzi]

Hello,
Does anyone have any idea why the following don't work in MySQL 
4.1.1-alpha with InnoDB tables:
1. cannot change column type without first dropping the foreign key 
(when there is one)
2. cannot drop the column without first dropping the foreign key (if 
there is one)
3. cannot explicitly define a constraint's name when creating it.
4. cannot automatically figure out the names of the constraints 
associated with a column without manually parsing the results of "show 
create table"

I sent a more detailed explanation a couple of days ago.  I'm just 
wondering if this is by design or something planned to be fixed.

thanks,

*Gilad Ezra Buzi
*R&D Engineer
Open Source Advocate
*Concatel*
Avenida Puertos de Europa 100,
08040 Barcelona (Spain)
tel. +34.93.567.97.10
fax +34.93.567.97.11
http://www.concatel.com

Going from 3.23 to 4 - sql statement errors

[Mike R]

Sorry if I posted this twice - I had some email issues yesterday, and I
didn't know if this made it to the list:


I am using php with mysql, and after moving my tables to a new server, I am
suddenly getting this error:

Query failed: 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
'this = 'No', fp_ext = 'No', username = 'jreft', password = 'sna

I find this rather generic - does anyone have any suggestions as to where I
can start to figure out why MySQL is erroring out a statement that worked
fine with 3.23, but doesn't with 4?  The fields are varchar types, a few
with default values.  Considering there isn't anything that unusual, I'm
having a hard time figuring this one out!

:\

Thanks for any help!

-Mike




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

MySQL 4.0.15 on SuSE 9.0 Pro - how do I uninstall ?

[Kevin Carpenter]

I've made a mess of this MySQL  installation.  I would like to uninstall and start 
over.

- Started out installing via YaST
 seemed to work but couldn't find MySQL or run it

- Tried installing binaries
 had trouble running 'make install'  (installed 'make' via YaST but never did find 
the install file)

- Installed using rpm -i
That seemed to work and I even got into MySQL once - But now it is all locked up

—---

How do I uninstall?

TIA,

-:>Kevin



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

Re: Update using fields from another table

[Victor Pendleton]

UPDATE table1 t1, table2 t2, table3 t3
SET t1.date = '2004-03-18'
WHERE t1.id_2 = t1.id_2
AND t3.id_1 = t2.id_1

On Thu, 18 Mar 2004, Russell Horn wrote:
> I haev three tables:
> 
> Table 1
> id_2   |   date
> 
> Table 2
> id_1   |   id_2
> 
> Table 3
> id_1
> 
> 
> I want to set the table1.date = '2004-03-18' for each record in table3. Any
> ideas?
> 
> Thanks,
> 
> Russell.
> 
> 
> -- 
> 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]

Update using fields from another table

[Russell Horn]

I haev three tables:

Table 1
id_2   |   date

Table 2
id_1   |   id_2

Table 3
id_1


I want to set the table1.date = '2004-03-18' for each record in table3. Any
ideas?

Thanks,

Russell.


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

Servicing concurrent data requests in a single threaded application

[Raghu Udupa]

We have various client applications who will need data stored in MySQL
database. We are planning to send data requests from these clients to MySQL
database server through a DBMGR application.

We would like to keep the DBMGR as a single threaded application servicing a
configurable number of connections.

I have following questions on MySQL c api
1) Is there a way mysql_query and mysql_store_results can be done in an
asynchronous way?

   The way we would like to do this,
   submit mysql_query on connection 1
   check for outstanding requests.
   if a request exists, submit mysql_query on connection 2
   check whether request is done on connection?
   if done, do mysql_store_result and pass the data to client
   .
   .


2) If not, is multi threading the only option to service multiple clients
concurrently?

Based on MySQL documentation, threading API interface was introduced from
version 4.0 onwards. I do believe this kind of requirement is fairly common
and am interested to know how a requirement like this was handled in a MySQL
version before 4.0.

Thanks,
Raghu Udupa


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

Re: lost sa password

[jeffrey_n_Dyke]

   
 
  jose manuel  
 
  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED]
  
  es>  cc: 
 
   Subject:  lost sa password  
 
  03/18/2004 11:31 
 
  AM   
 
   
 
   
 








Hi all:

Long time ago I've installed MySQL on my Debian Machine and I'm pretty sure
I did'n left the "sa" pwd blank as recommended.

Now, I'm installing  other apps that need that "sa" pwd in order to be
installed properly butI can't remember the pwd.

is possible to recover the sa password?
_
Don't think soBut you can set it back to nothing, take a look at this.
http://www.mysql.com/doc/en/Resetting_permissions.html
HTH
Jeff
__

how?


-

    Antivirus ? Filtros antispam ? 6 MB gratis
    ¿Todavía no tienes un correo inteligente?





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

lost sa password

[jose manuel]

Hi all:
 
Long time ago I've installed MySQL on my Debian Machine and I'm pretty sure I did'n 
left the "sa" pwd blank as recommended.
 
Now, I'm installing  other apps that need that "sa" pwd in order to be installed 
properly butI can't remember the pwd.
 
is possible to recover the sa password?
 
how?


-

    Antivirus • Filtros antispam • 6 MB gratis
    ¿Todavía no tienes un correo inteligente?

Create new columns

[Elisenda]

Hi all,

I'm trying to create new columns in a table with 308.000 records and 135
columns. It last 14 minuts to create one column. I make it trough
MySQL4XManager. I don't think is a problem of MySQL4XManager.

I guess is something about two many records and to many columns. Am I wrong?
If I am wrong what can I do?

Thank you in advanced.


Elisenda Sala


_
SetFile DATABASE FACTORY
 
- Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh )

<[EMAIL PROTECTED]>TEL 93 238 56 00

SetFile - FSA Partners

_



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

install mysql & create databases

[zeineb sellami]

we are faced to a problem where creating databases
the installation is succeded but we can't create the databases correctly
the instruction ./scripts/mysql_install_db shows
prepare installing database inspite of creating db table
 
what is the solution?
 

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Re: php script for new database and user

[Elly Wisata]

Oh, thanks for your information. I used phpMyAdmin b4, but I never noticed
that it makes from php script! Gosh!!!
Thanks for make it straight.
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Bernd Tannenbaum" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, March 18, 2004 9:45 PM
Subject: Re: php script for new database and user


>
>
>
>
> Hi,
>
> > -Original Message-
> > From: Elly Wisata [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, March 18, 2004 1:55 AM
> > To: [EMAIL PROTECTED]
> > Cc: 'BGLefty'
> > Subject: RE: php script for new database and user
> >
> > I don't think php script can create database. But maybe I am wrong.
> > If I am, please somebody make it straight.
> > Create a database should do it through console or some kind like
> > it.
>
> Well, phpMyAdmin can create databases. And if im not wrong its just a
> bundle of php-scripts.
> So maybe just install phpMyAdmin and create a DB. Iirc u can see the
> commands it does to create.
>
> ---
> php is only providing an interface to mysql.  If wish to script a database
> creation you can definitely use PHP.  As soon as you authenticate to mysql
> via mysql_connect(host,user,pass) you have the rights of that mysqluser,
> then you can create all the dbs you want through regular SQL, using PHP's
> mysql_query.
>
> mysql_connect('localhost','root','rootpassword');
> mysql_query('CREATE DATABASE PHP_TEST');
> mysql_select_db('PHP_TEST');
> mysql_query('CREATE TABLE STATEMENT')
>
> But if you just want to get some datbases created, then it may just be
> easier to use the command line/PHPmyAdmin/MySQLCC/lots of other programs.
>
> HTH
> jeff
> 
>
>
>
> Bernd
>
> --
> [Zufallssig 9]
> [NightGhost] on AO forum: "Perhaps it doesn't take two to make an
> idiotic argument, although you have proven for a fact, that one man
> indeed can make two idiotic arguments."
>
>
> --
> 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]
>
>


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

Re: php script for new database and user

[jeffrey_n_Dyke]

Hi,

> -Original Message-
> From: Elly Wisata [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 18, 2004 1:55 AM
> To: [EMAIL PROTECTED]
> Cc: 'BGLefty'
> Subject: RE: php script for new database and user
>
> I don't think php script can create database. But maybe I am wrong.
> If I am, please somebody make it straight.
> Create a database should do it through console or some kind like
> it.

Well, phpMyAdmin can create databases. And if im not wrong its just a
bundle of php-scripts.
So maybe just install phpMyAdmin and create a DB. Iirc u can see the
commands it does to create.

---
php is only providing an interface to mysql.  If wish to script a database
creation you can definitely use PHP.  As soon as you authenticate to mysql
via mysql_connect(host,user,pass) you have the rights of that mysqluser,
then you can create all the dbs you want through regular SQL, using PHP's
mysql_query.

mysql_connect('localhost','root','rootpassword');
mysql_query('CREATE DATABASE PHP_TEST');
mysql_select_db('PHP_TEST');
mysql_query('CREATE TABLE STATEMENT')

But if you just want to get some datbases created, then it may just be
easier to use the command line/PHPmyAdmin/MySQLCC/lots of other programs.

HTH
jeff




Bernd

--
[Zufallssig 9]
[NightGhost] on AO forum: "Perhaps it doesn't take two to make an
idiotic argument, although you have proven for a fact, that one man
indeed can make two idiotic arguments."


--
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: MySQL newbie installation

[Chris Nolan]

Yes! Yes you can!

Ahem

Basically, MySQL is always a network-aware database whether connections 
come in via Named Pipes (blame Bill for those), TCP/IP, UNIX sockets or 
via the funky shared memory whatsit that was brought in with MySQL 4.1.x.

All that will be happening in your case is that you'll just need to tell 
all of your applications and things that your computer is the database 
server, by specifying "localhost" as the database server.

Chris

[EMAIL PROTECTED] wrote:

I wish to learn MySQL on my Linux box [SuSE 8.1]

Can I install MySQL on my hard drive and use it for learning *from* the
hard drive, without having to set up a server/network?  This would include
MySQLCC.
In other words, can I run MySQL like Access, strictly as a local database,
for learning purposes?
I hope I'm not blaspheming.
  
  
Thanks
  
Mike  
  





 



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

Re: Your letter

[luuk]

Please read the attached file.

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

MySQL newbie installation

[michael . p . pochmara]

I wish to learn MySQL on my Linux box [SuSE 8.1]

Can I install MySQL on my hard drive and use it for learning *from* the
hard drive, without having to set up a server/network?  This would include
MySQLCC.

In other words, can I run MySQL like Access, strictly as a local database,
for learning purposes?

I hope I'm not blaspheming.
   
   
 Thanks
   
 Mike  
   





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

Re: php script for new database and user

[Bernd Tannenbaum]

Hi,

> -Original Message-
> From: Elly Wisata [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 18, 2004 1:55 AM
> To: [EMAIL PROTECTED]
> Cc: 'BGLefty'
> Subject: RE: php script for new database and user
>
> I don't think php script can create database. But maybe I am wrong.
> If I am, please somebody make it straight.
> Create a database should do it through console or some kind like
> it.

Well, phpMyAdmin can create databases. And if im not wrong its just a 
bundle of php-scripts.
So maybe just install phpMyAdmin and create a DB. Iirc u can see the 
commands it does to create.

Bernd

-- 
[Zufallssig 9]
[NightGhost] on AO forum: "Perhaps it doesn't take two to make an 
idiotic argument, although you have proven for a fact, that one man 
indeed can make two idiotic arguments."


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

Re: What causes this error, and how do i fix it?

[Egor Egorov]

On Thursday 18 March 2004 15:41, you wrote:
> Hm it really is the problem i think, so next question :) Where do I
> specify the location of the temp dir? Can I do that somehow in the
> /etc/my.cnf file?

Sure. Put into my.cnf:

[mysqld]
tmpdir=/path/to/the/tmpdir

User that runs mysqld ('mysql' by default) should have enough permissions on that dir.

>
> Best regards
> Søren
>
> -Original Message-
> From: Egor Egorov [mailto:[EMAIL PROTECTED]
> Sent: 18. marts 2004 14:15
> To: [EMAIL PROTECTED]
> Subject: Re: What causes this error, and how do i fix it?
>
> S?ren Neigaard <[EMAIL PROTECTED]> wrote:
> > The following SQL (bugzilla):
> >
> > SELECT =
> > products.product,description,disallownew,votesperuser,maxvotesperbug,v
> > ote=
> > stoconfirm,COUNT(bug_id) FROM products LEFT JOIN bugs ON =
> > products.product=3Dbugs.product GROUP BY products.product ORDER BY =
> > products.product;
> >
> >
> > Causes this error:
> >
> > ERROR 1: Can't create/write to file '/root/tmp/#sql_1e8_0.MYI'
> > (Errcode: =
> > 13)
> >
> > But why and what can I do?
>
> Seems, --tmpdir is set to /root/tmp and MySQL server doesn't permissions
> to write



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




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

RE: php script for new database and user

[HACKATHORN, TODD (SWBT)]

This is just my guess, I am still too new to know for sure, but I would say
php can do what ever the user that is logging in can.  phpMyAdmin will
create databases, and it is just a PHP Script.

Todd Hackathorn


-Original Message-
From: Elly Wisata [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 18, 2004 1:55 AM
To: [EMAIL PROTECTED]
Cc: 'BGLefty'
Subject: RE: php script for new database and user

I don't think php script can create database. But maybe I am wrong. If I am,
please somebody make it straight.
Create a database should do it through console or some kind like it.

~Elle~

-Original Message-
From: W. D. [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 18, 2004 2:31 PM
To: [EMAIL PROTECTED]
Cc: BGLefty
Subject: Re: php script for new database and user

At 11:45 3/17/2004, BGLefty wrote:
>
>Is there a simple php script out there somewhere (or an example of how to
do
>it) to create a new database, username and password? I would like to have a
>form in a password protected folder on my web site and be able to fill in
>those three variables to create a new database on my installation of mysql
>on my home computer (be able to do this from my office or another
computer).
>It seems like a simple enough thing to do but I have been unable to find
any
>examples...
>
>Any help would be appreciated.
>
>Thanks,
>BGLefty

Do you have PHPmyAdmin installed?  You can quite a bit of work
fairly easily with it.

Start Here to Find It Fast!T ->
http://www.US-Webmasters.com/best-start-page/


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

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

Re: What causes this error, and how do i fix it?

[Egor Egorov]

S?ren Neigaard <[EMAIL PROTECTED]> wrote:
> The following SQL (bugzilla):
> 
> SELECT =
> products.product,description,disallownew,votesperuser,maxvotesperbug,vote=
> stoconfirm,COUNT(bug_id) FROM products LEFT JOIN bugs ON =
> products.product=3Dbugs.product GROUP BY products.product ORDER BY =
> products.product;
> 
> 
> Causes this error:
> 
> ERROR 1: Can't create/write to file '/root/tmp/#sql_1e8_0.MYI' (Errcode: =
> 13)
> 
> But why and what can I do?
> 

Seems, --tmpdir is set to /root/tmp and MySQL server doesn't permissions to write



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




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

RE: What causes this error, and how do i fix it?

[Mechain Marc]

May be Unix acces permissions on /root/tmp

(Errcode: 13) = EACCES Permission denied

Like you are using an ORDER BY, mysql needs some temporary space to do the order.

Marc.

-Message d'origine-
De : Søren Neigaard [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 18 mars 2004 13:29
À : [EMAIL PROTECTED]
Objet : What causes this error, and how do i fix it?


The following SQL (bugzilla):

SELECT 
products.product,description,disallownew,votesperuser,maxvotesperbug,votestoconfirm,COUNT(bug_id)
 FROM products LEFT JOIN bugs ON products.product=bugs.product GROUP BY 
products.product ORDER BY products.product;


Causes this error:

ERROR 1: Can't create/write to file '/root/tmp/#sql_1e8_0.MYI' (Errcode: 13)


But why and what can I do?

Med venlig hilsen/Best regards
Søren Neigaard
System Architect

Mobilethink A/S
Arosgaarden
Åboulevarden 23, 4.sal
DK - 8000 Århus C
Telefon: +45 86207800
Direct: +45 86207810
Fax: +45 86207801
Email: [EMAIL PROTECTED]
Web: www.mobilethink.dk
 

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

What causes this error, and how do i fix it?

[Søren Neigaard]

The following SQL (bugzilla):

SELECT 
products.product,description,disallownew,votesperuser,maxvotesperbug,votestoconfirm,COUNT(bug_id)
 FROM products LEFT JOIN bugs ON products.product=bugs.product GROUP BY 
products.product ORDER BY products.product;


Causes this error:

ERROR 1: Can't create/write to file '/root/tmp/#sql_1e8_0.MYI' (Errcode: 13)


But why and what can I do?

Med venlig hilsen/Best regards
Søren Neigaard
System Architect

Mobilethink A/S
Arosgaarden
Åboulevarden 23, 4.sal
DK - 8000 Århus C
Telefon: +45 86207800
Direct: +45 86207810
Fax: +45 86207801
Email: [EMAIL PROTECTED]
Web: www.mobilethink.dk
 

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

reg Bitkeeper wizard parameters

[Arunachalam]

Hi all,

It is better i got the settings for *BitKeeper Wizard*
parameters. Becaz when i run it shows parameters and i 
select one out of them as;

description  : MySQL Source
category : development/building
email: [EMAIL PROTECTED]
name : Arun
street   : 
city : 
state: 
postal   : 
country  : India
phone: 
cell : 
pager: 
hours: 
logging  : none
keyword  : expand1
compression  : gzip
autofix  : yes
checkout : edit
single_user  : Arun
single_host  : nb-arun.

Atlast when i click the finish it take some time to create 
some directory in the specified folder and response with error 
message as;

*
Unable to obtain permission to use this version of BitKeeper 
(bk-3-0.4) from lease.openlogging.org. That server issues 
certificates to use BK for openlogging for 30 days at a time. 
The bk binary needs to be able to make a http connection to 
lease.openlogging.org at least once a month.

look at 'bk help url' if you need to tell 'bk' about a proxy.
   *

Anybody help/suggest me to attain my goal pls...

Arun.
 
 --- Arunachalam <[EMAIL PROTECTED]> wrote: > When I first mail to [EMAIL PROTECTED] 
they
said 
> 
> 
> Quick answer:  you probably have an incorrect Internet Proxy setting.
> Test if you can connect to mysql.bkbits.net port 14690 using telnet:
> 
> C:\> telnet mysql.bkbits.net 14690
>
> but when i use this telnet
> 
> C:\> telnet mysql.bkbits.net 14690
>Connecting To mysql.bkbits.net...Could not open a connection to host 
>on port 14690 : Connect failed
> 
> IS this is temporary problem connection to mysql.bkbits.net is failed? OR
> it's my net connection problem?
> 
> Important: I need to have MySQL source to do certain task, but i was make
> delay due to this simpe trouble i think...
> 
> suggestion please...
> 
> Thanks
> 
> Arun.
> 
> 
> Yahoo! India Insurance Special: Be informed on the best policies, services, tools 
> and more. 
> Go to: http://in.insurance.yahoo.com/licspecial/index.html
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>  


Yahoo! India Insurance Special: Be informed on the best policies, services, tools and 
more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html

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

Re: [Fwd: [Fwd: Do the following LOG enabling / disabling command work in 3.23??]]

[Victoria Reznichenko]

[EMAIL PROTECTED] wrote:
>  Hello colleagues,
> Im'using the MySQL 3.23,
> I've found the following commands
> SET SQL_LOG_BIN=0 Disables update logging if the user has process 
> privilege. Ignored otherwise. (Master)
> SET SQL_LOG_BIN=1   Re-enables update logging if the user has process 
> privilege. Ignored otherwise. (Master)
> 
> in the http://www.scit.wlv.ac.uk/appdocs/mysql/manual_toc.html manual ...
> 
> I suppose they enable/disable the binary log production, is it right? I 
> mean: all the log production, not just for the current client, is it right??
> .
> 
> Why they don't work properly???

What exactly doesn't work?
Dont' forget that SET SQL_LOG_BIN enables/disables binary logging for the _current_ 
connection.

> 
> Which is the right way to enable / disable ALL the LOG production at run 
> time, if any?

If MySQL server is running without --log-bin option, you should restart server anyway 
to enable binary logging.


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





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

[Fwd: [Fwd: Do the following LOG enabling / disabling command work in 3.23??]]

[Enrico . Venturi]

 Hello colleagues,
Im'using the MySQL 3.23,
I've found the following commands
SET SQL_LOG_BIN=0 Disables update logging if the user has process 
privilege. Ignored otherwise. (Master)
SET SQL_LOG_BIN=1   Re-enables update logging if the user has process 
privilege. Ignored otherwise. (Master)

in the http://www.scit.wlv.ac.uk/appdocs/mysql/manual_toc.html manual ...

I suppose they enable/disable the binary log production, is it right? I 
mean: all the log production, not just for the current client, is it right??
.

Why they don't work properly???

Which is the right way to enable / disable ALL the LOG production at run 
time, if any?

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

Re: Problems with 4.0.18 and mysqldump

[Thomas Spahni]

Jochen,

what's the result of

prompt> mysql -uroot -pXXX -hxxx.xxx.xxx.xxx

when you do it on the client host? (I still suspect that permissions are
not properly granted).

Regards, Thomas Spahni


On Wed, 17 Mar 2004, Jochen Kaechelin wrote:

> I use the following Script to backup a remote MySQL-Server.
>
>
> DATUM=`date +"%Y_%m_%d__%H_%M"`
> BACKUPDIR="/home/jochen/SICHERUNG/MySQL_Dumps/debby/$DATUM"
>
> echo ""
> echo "Erzeuge Sicherungvereichnis $BACKUPDIR..."
> echo ""
>
> mkdir -p $BACKUPDIR
>
>
> for DB in db1 db2
> do
>   echo ""
>   echo "Erzeuge lokele Sicherung der Datenbank $DB..."
>   echo ""
>
>   /usr/bin/mysqldump -uroot -pXXX -hxxx.xxx.xxx.xxx --opt $DB >
> $BACKUPDIR/$DB.sql
>
>   echo ""
>   echo "Entferne moegliche Sicherungsduplikate..."
>   echo ""
>
>   rm -rf $BACKUPDIR/$DB.sql.gz
>
>   echo ""
>   echo "Komprimiere aktuelle Sicherungsdateien..."
>   echo ""
>   gzip $BACKUPDIR/$DB.sql
> done
>
> and I always get the following error:
>
> /usr/bin/mysqldump: Got error: 2003: Can't connect to MySQL server
> on '212.87.142.236' (111) when trying to connect
>
>
> Permissions are ok and I can modify the complete db with my php
> scripts.
>
> where's the error?
>
>


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

Re: select help - multiple where/limits

[Victoria Reznichenko]

Kris Burford <[EMAIL PROTECTED]> wrote:
> hi
> 
> wondering whether someone can set me straight on whether it's possible to 
> request a set of records from a single table with multiple conditions.
> 
> for instance, a "story" table, containing id, title, text, section and 
> published_date. what i would like is to retrieve is the 5 most recently 
> published stories from each section (currently there are nine sections).
> 
> so, do i have to do this in nine separate queries or can i do something like:
> 
> SELECT id, title, text, sectioned, published_date
> FROM stories
> WHERE (section = 'events'  order by published_date desc limit 5) and 
> (section = 'features'  order by published_date desc limit 5)
> 

If I've got you right you need UNION:
(SELECT id, title, text, sectioned, published_date
  FROM stories
  WHERE section = 'events' ORDER BY published_date DESC LIMIT 5)
UNION
(SELECT id, title, text, sectioned, published_date
  FROM stories
  WHERE section = 'features' ORDER BY published_date DESC LIMIT 5);

 http://www.mysql.com/doc/en/UNION.html


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





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

COLLATE in CREATE TABLE

[programmer]

Hi,

I want to CREATE a TABLE an specify a collation.

If I do
CREATE TABLE ... DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
SHOW TABLE STATUS shows me Collation: latin1_swedish_ci
If I omit the keyword DEFAULT the requested collation latin1_german1_ci
is shown by SHOW TABLE STATUS.

So, what does DEFAULT before CHARACTER SET means?

If I don't use SHOW TABLE STATUS but SHOW CREATE TABLE the result is
interesting too.
For the table I applied the DEFAULT keyword the generated CREATE
TABLE-statement uses the DEFAULT keyword too, but does not show the
COLLATE part of the CREATE TABLE statement.
For the table I omited the DEFAULT keyword, the generated
CREATE-statement uses the DEFAULT keyword - though omited in my
statement -, and shows the COLLATE part of the CREATE TABLE statement.
It's in fact my first statement.

Is this regular behaviour?

I'm running MySQL 4.1.1a on an NT4-Box

Best wishes,
Bernhard

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

Re: MyISAM vs. INNODB for a single blob table

[Martijn Tonies]

Hi Alan,


> Thanks for that Chris, interesting thoughts.
>
> For clarification, there is *NO* UPDATEs running on this table. Not a
> single one! :)  Many more SELECTs than INSERTs

If you value your data, and these INSERTs are part of
a multi-insert batch of related data, go with the table-type
that supports transactions: InnoDB.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

select help - multiple where/limits

[Kris Burford]

hi

wondering whether someone can set me straight on whether it's possible to 
request a set of records from a single table with multiple conditions.

for instance, a "story" table, containing id, title, text, section and 
published_date. what i would like is to retrieve is the 5 most recently 
published stories from each section (currently there are nine sections).

so, do i have to do this in nine separate queries or can i do something like:

SELECT id, title, text, sectioned, published_date
FROM stories
WHERE (section = 'events'  order by published_date desc limit 5) and 
(section = 'features'  order by published_date desc limit 5)

etc...

many thanks

kris

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

Re: MyISAM vs. INNODB for a single blob table

[Alan Williamson]

Thanks for that Chris, interesting thoughts.

For clarification, there is *NO* UPDATEs running on this table. Not a 
single one! :)  Many more SELECTs than INSERTs

Chris Nolan wrote:
Alan Williamson wrote:

A quick question for the hardcore MySQL experts out there.

I have a simple table;

---
ID varchar (PK)
DATA longblob
---
This table is a simple persistence cache for one of our servers. It 
regularly INSERTs and SELECTs into this table data of approximately 
2KB - 200KB, although the majority of inserts are around the 2KB mark.

No fancy queries are ever performed, merely a single SELECT on a given 
key and no range queries are ever done.

So with that in mind, I just noticed the table was created as a 
MyISAM.   In your experience how does this compare to a table using 
INNODB? Should it have been created as a INNODB for better performance?

Any thoughts, insights, would be listened to intensely! :)

thanks

How often are DELETE and UPDATE statements executed on this table?

MyISAM is damned quick when it comes to workloads that always result in 
INSERTs ending up at the end of the tablespace. As MyISAM can allow 
SELECTs to execute while INSERTs are in progress at the end of the table 
(i.e When no DELETEs have been issued) thanks to it's versioning you'll 
find that thousands of queries a second is quite doable on modest hardware.

That said, InnoDB's speed defies belief. Given that it's multiversioned, 
transactional and able to lock at the row level the fact that it's even 
in the same leauge as MyISAM performance-wise for these sorts of loads 
is impressive. When you have UPDATEs flying around, InnoDB may edge 
MyISAM out for heavy workloads. Many places have moved to InnoDB due to 
concurrency issues of that type.

In summary, test test test!


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

Re: Variables for InnoDB only

[Victoria Reznichenko]

"Keith Thompson" <[EMAIL PROTECTED]> wrote:
> All of my tables are now InnoDB only.  So, what I'd like to do is
> reduce MySQL tunable variables down as much as possible for things
> that don't affect InnoDB so that I'm not wasting memory on buffers
> that will get little or no use.
> 
> It's obvious which variables are only for InnoDB (they start with
> "innodb_").  But, which of the others are never used by InnoDB?
> 
> For example, are the biggees like key_buffer_size, sort_buffer_size,
> etc. for both InnoDB and MyISAM, or just MyISAM?

sort_buffer_size, read_buffer_size, read_rnd_buffer_size are also relevant for InnoDB, 
not only for MyISAM.


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





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

Re: MyISAM vs. INNODB for a single blob table

[Chris Nolan]

Alan Williamson wrote:

A quick question for the hardcore MySQL experts out there.

I have a simple table;

---
ID varchar (PK)
DATA longblob
---
This table is a simple persistence cache for one of our servers. It 
regularly INSERTs and SELECTs into this table data of approximately 
2KB - 200KB, although the majority of inserts are around the 2KB mark.

No fancy queries are ever performed, merely a single SELECT on a given 
key and no range queries are ever done.

So with that in mind, I just noticed the table was created as a 
MyISAM.   In your experience how does this compare to a table using 
INNODB? Should it have been created as a INNODB for better performance?

Any thoughts, insights, would be listened to intensely! :)

thanks

How often are DELETE and UPDATE statements executed on this table?

MyISAM is damned quick when it comes to workloads that always result in 
INSERTs ending up at the end of the tablespace. As MyISAM can allow 
SELECTs to execute while INSERTs are in progress at the end of the table 
(i.e When no DELETEs have been issued) thanks to it's versioning you'll 
find that thousands of queries a second is quite doable on modest hardware.

That said, InnoDB's speed defies belief. Given that it's multiversioned, 
transactional and able to lock at the row level the fact that it's even 
in the same leauge as MyISAM performance-wise for these sorts of loads 
is impressive. When you have UPDATEs flying around, InnoDB may edge 
MyISAM out for heavy workloads. Many places have moved to InnoDB due to 
concurrency issues of that type.

In summary, test test test!

Regards,

Chris

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

what to do? to get MySQL source?

[Arunachalam]

When I first mail to [EMAIL PROTECTED] they said 


Quick answer:  you probably have an incorrect Internet Proxy setting.
Test if you can connect to mysql.bkbits.net port 14690 using telnet:

C:\> telnet mysql.bkbits.net 14690
   
but when i use this telnet

C:\> telnet mysql.bkbits.net 14690
   Connecting To mysql.bkbits.net...Could not open a connection to host 
   on port 14690 : Connect failed

IS this is temporary problem connection to mysql.bkbits.net is failed? OR
it's my net connection problem?

Important: I need to have MySQL source to do certain task, but i was make
delay due to this simpe trouble i think...

suggestion please...

Thanks

Arun.


Yahoo! India Insurance Special: Be informed on the best policies, services, tools and 
more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html

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

MyISAM vs. INNODB for a single blob table

[Alan Williamson]

A quick question for the hardcore MySQL experts out there.

I have a simple table;

---
ID varchar (PK)
DATA longblob
---
This table is a simple persistence cache for one of our servers. It 
regularly INSERTs and SELECTs into this table data of approximately 2KB 
- 200KB, although the majority of inserts are around the 2KB mark.

No fancy queries are ever performed, merely a single SELECT on a given 
key and no range queries are ever done.

So with that in mind, I just noticed the table was created as a MyISAM. 
  In your experience how does this compare to a table using INNODB? 
Should it have been created as a INNODB for better performance?

Any thoughts, insights, would be listened to intensely! :)

thanks

--
Alan Williamson, City Planner
w: http://www.BLOG-CITY.com/
e: [EMAIL PROTECTED]
b: http://alan.blog-city.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Wish List of Features

[Eldon Ziegler]

Mark,

I've had some of the same thoughts as yours. Others have mentioned SHOW 
CREATE TABLE.

I created a Windows program to put a GUI interface on designing tables for 
MySQL to ease the load on my memory while setting up a database. It 
includes the ability to insert new fields at any place and to move fields 
around to make the structure make sense for sometime down the road when I 
try to remember what I did. It's handy when first creating a table. I 
haven't released it but if someone wants to take a look, let me know.

Eldon Ziegler

At 10:38 am 3/16/2004, you wrote:
I downloaded MySQL and began playing with it.  It is a very impressive 
program and many awards should go to everyone who has ever worked on this 
project.  :-)

Since I'm mainly just playing around with the server right now I could 
never be considered a real user until I've created a few databases and 
have gotten into the feel of MySQL.  Still, after having played with it 
(and mysqlcc) for a while - there were a few things I wanted to ask 
about.  Especially since these features might already exist and I just 
haven't gotten to them in the MySQL book.  (I'm using the Paul DuBois book.)

I used to use FoxBase+ quite a bit and eventually, where I work, I wrote 
several database applications using my own code.  One of the things I 
found very useful was:

1. LIST STRUCTURE.  Which would dump an exact copy of the commands used to 
create a table in a database.  Thus, in MySQL, this command would display 
the correctly coded CREATE TABLE command for each table.  This command 
used to be in FoxBase+ and I have found it very useful several times.  I 
know about the SHOW COLUMNS command - it is not the same although I could 
write some code to extract it and then create the CREATE TABLE command 
from it.  (This is just one of those ease of use/convience things.)

The second thing I can not figure out, is how to rearrange my entries in 
the table.  In MySQLCC, I can not find any command which will allow me to 
move items up and down in the list.  Granted that, if there is data in the 
database, this could cause problems - but if you are just trying to set up 
a database and you go "Oops!  I forgot to put in X" - you can not put X 
anywhere except at the very bottom of the list.  So my next thing is a 
question:

2. Is there a way to reorganize your table's layout in MySQLCC?  Or do I 
have to delete the entire table and start over?

Again, I am pulling from my FoxBase+ background.  In FoxBase+ you can 
insert new fields into the table and what it would do in the background is 
to change the name of the table to a temporary name, build the new field 
list, and then port the old table over to the new one and get rid of the 
temporary table.  This is (again) an ease of use/convience thing.  A 
script could be written to do this I suppose but then I'd still have to 
create the new table, get out of mysqlcc, run the script, and then get 
back in again.  It would be nicer if mysqlcc had this built in.  (I 
hope!  :-) )

TIA! To whomever answers this message.  :-)

Mark



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eldon Ziegler
President
ProAtion Systems, Inc.
www.proation.com  

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

Re: Large lists of SQL commands choking PHPmyAdmin?

[W. D.]

At 02:03 3/18/2004, olinux wrote:
>--- "W. D." wrote:
>> I am a relative newbie to MySQL and PHPmyAdmin.  I
>> tried adding 
>> data into an existing table by 'uploading' a file in
>> PHPmyAdmin.
>> I know the INSERT INTO commands are valid, since I
>> can import
>> several hundred records using PHPmyAdmin.  However,
>> when I
>> try to import a few thousand records, everything
>> seems to 
>> go berserk.
>> 
>> Does anyone have a simple way of adding a 5 or 10
>> thousand
>> records at a time?
>
>
>You can read the dump file back into the server with: 
>
>shell> mysql db_name < backup-file.sql
>
>Or: 
>
>shell> mysql -e "source
>/path-to-backup/backup-file.sql" db_name
>
>More info here: 
>http://www.mysql.com/doc/en/mysqldump.html
>
>olinux

Thanks, O.

Is the dump file all SQL statements?  The file that I am importing
is all INSERT INTO statments.


Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/


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

Re: Large lists of SQL commands choking PHPmyAdmin?

[olinux]

--- "W. D." wrote:
> I am a relative newbie to MySQL and PHPmyAdmin.  I
> tried adding 
> data into an existing table by 'uploading' a file in
> PHPmyAdmin.
> I know the INSERT INTO commands are valid, since I
> can import
> several hundred records using PHPmyAdmin.  However,
> when I
> try to import a few thousand records, everything
> seems to 
> go berserk.
> 
> Does anyone have a simple way of adding a 5 or 10
> thousand
> records at a time?


You can read the dump file back into the server with: 

shell> mysql db_name < backup-file.sql

Or: 

shell> mysql -e "source
/path-to-backup/backup-file.sql" db_name

More info here: 
http://www.mysql.com/doc/en/mysqldump.html

olinux


__
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
http://mail.yahoo.com

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

how to obtain MySQL 4.1.1 source from Bitkeeper?

[Arunachalam]

Hi,

I have installed the package bk-3.0.4-x86-win32.EXE
from Bitkeeper.com, to get the Mysql-4.1.1 source code
for Windows 2000 box.

It's installed successfully and frame the shortcuts in 
my Start menu --> program --> Bitkeeper with
sub menus for
 bash shell1
 bash shell2
 Helptool
 Quick reference
 SetupTool as such.

after this when i give bk in my DOS prompt it produce
brief help about BK usage.

when I give 
C:\>bk clone bk://mysql.bkbits.net/mysql-4.1 mysql-4.1
   client helper: Connection failed

   why this error message comes?
   Am I doing proper steps?
   How to obatin source code for the MySQL 4.1.1?

Explain it detail please

Thanks 

Arun.



Yahoo! India Insurance Special: Be informed on the best policies, services, tools and 
more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html

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