Re: REPLACE INTO and CASCADEs.
Hi! The REPLACE + FOREIGN KEY behavior indeed changed between 4.0.17 - 4.0.18, because the old behavior was wrong, according to the MySQL manual: Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual states that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions 4.0.18 and 4.1.2, MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) I am sorry for the inconvenience. The old behavior was maybe more intuitive, if you think of REPLACE as an 'UPSERT' command. But in MySQL it is a 'DELSERT' command. Best regards, Heikki - Original Message - From: Tan Shao Yi [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 14, 2004 10:29 PM Subject: REPLACE INTO and CASCADEs. Hello, I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the behaviour of REPLACE INTO appears to have changed. I have two tables: table2 references table1 with ON DELETE CASCADE and ON UPDATE CASCADE on a primary key. Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the row in table1 gets replaced. No rows in table2 get deleted. Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 gets replaced, but this replacement gets cascaded into table2 and all rows in table2 with the primary key get deleted. Has anyone encountered something similar? I have only one box available so I am not able to re-test my observations again (I have reverted to 4.0.17). Thanking in advance. Regards, Tan Shao Yi -- 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]
resolving mysql_prepare
I am running mysql 4.1.1 on windows using VC++ version 6. I am trying to use the parameterized queries and so I need to call mysql_prepare. This symbol seems to be in mysqlclient.lib but there are other symbols in libmysql.lib that I need. Using both yields many multiply defined symbols. Is there a library avialable the represents the union of the aforementioned two libraries? Thanks, -Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group by problem
Hi I am using mysql 3.23 have a table 'scores' with following structure compint(11) scoreint(11) I have total 1 lakh rows consider following sample data compscore ~ 10.25 22.5 2.1.7 10.1 30.9 10.3 I want to sort my data such a way, the company having maximum score should come at top followed by all its rows in descending order of scores and after that company having the next highest score should come followed by all rows of that company. Also i want to know if i can limit the rows within a company. my data should come in following way: compscore ~ 13.71 10.3 10.1 22.5 21.7 30.9 regards Pawan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior with IF?
Hi, Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? --Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Advanced Data Generator beta 3, including specialized database versions
Dear ladies and gentlemen, Upscene Productions is happy to announce a new beta of the database developer tool: Advanced Data Generator (public beta 3) A fast test-data generator tool that comes with a library of real-life data, presets and much more. This new release consists of four versions: - Pro, ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOEIGN KEY
Mulugeta Maru [EMAIL PROTECTED] wrote: Can a field in one table be a foreign key of another table's field which is also a foreign key of a third table? Sure. For example: mysql CREATE TABLE t1 ( - id int NOT NULL default '0', - PRIMARY KEY (id) - ) TYPE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE t2 ( - id int NOT NULL default '0', - pid int default NULL, - PRIMARY KEY (id), - KEY (pid), - FOREIGN KEY (pid) REFERENCES t1 (id) - ) TYPE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE t3 ( - id int NOT NULL default '0', - pid int default NULL, - PRIMARY KEY (id), - KEY (pid), - FOREIGN KEY (pid) REFERENCES t2 (pid) - ) TYPE=InnoDB; Query OK, 0 rows affected (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
Batara Kesuma [EMAIL PROTECTED] wrote: Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? You can't refer to the column alias in the WHERE clause. Use HAVING clause instead. -- 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]
Enabling Mysql in php
Hi. I haven't mysql enabled in php. How may I do it? I have php 4.0.4, mysql 3.23.36, both installed on linux RH 7.3. Thanks Mic.
Re: Enabling Mysql in php
Hi. I haven't mysql enabled in php. How may I do it? I have php 4.0.4, mysql 3.23.36, both installed on linux RH 7.3. Install the php-mysql RPM. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Please, reply to the mailing list, not to me directly. Date: Sat, 14 Feb 2004 09:11:14 -0500 From: Rhino [EMAIL PROTECTED] To: Egor Egorov [EMAIL PROTECTED] Subject: Re: Newbie Question Thanks Egor! I ran the query you suggested and it said 'NO' so the server was apparently configured without InnoDB support. What do I need to do to activate the InnoDB support? Is it just a matter of configuring a few settings or do I need to re-install MySQL? You can download and install official 4.0.x binary distribution: http://www.mysql.com/downloads/mysql-4.0.html All official 4.0.x binaries include InnoDB storage engine. 4.0.11 is an outdated version, so upgrade is recommended in any case. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 4:04 AM Subject: Re: Newbie Question Rhino [EMAIL PROTECTED] wrote: I'm new to MySQL but I have extensive experience with DB2 so I'm getting quite confused about how MySQL is supposed to work. I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am trying to create a pair of InnoDB tables that are related to one another via a foreign key. I created the tables successfully but when I try to insert a row into the child table that violates the foreign key, MySQL loads the bad row, even though the foreign key doesn't exist! [skip] I got a very big clue when I ran this command: show table status from Sample; It showed that my two tables were type MyISAM, *not* InnoDB. If my tables really are MyISAM, then I'm not surprised that the foreign key constraint doesn't work since MyISAM doesn't support foreign keys, at least as I understand the manual. However, this doesn't answer the big question: *Why* aren't my tables InnoDB since I explicitly defined them that way?? Can any MySQL veterans clear up this mystery for me? If you try to create table type that is disabled or not compiled-in, MySQL creates MyISAM table type. Execute statement SHOW VARIABLES LIKE have_innodb; If you see 'DISABLED' in the output, it means that you run MySQL server with skip-innodb option. If you see 'NO' in the output, it means that MySQL server was configured without InnoDB support. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /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: Enabling Mysql in php
Hullos, Am Montag, 16. Februar 2004 12:08 schrieb michele digioia: Hi. I haven't mysql enabled in php. How may I do it? I have php 4.0.4, mysql 3.23.36, both installed on linux RH 7.3. If u compiled php urself u may wanna do it again with: --with-mysql=/PATH to mysql If its a finished packet then there will surely exist a ready-buildt RPM for it. Bernd -- [Zufallssig 10] [Deng] on AO Forum: Good judgement is the result of experience... experience often comes from bad judgement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Heikki, Thank you greatly for answering my questions. Your obvious dedication to the open source world and high-quality of software cannot be understated. Upon reading some of the InnoDB source code, I've noticed that C seems to be your language of choice. That said, it seems more and more of the literature that is promoted around my university emphasises testing methods related to OO languages like Java and C++ or looking at different programming paradigms such as logical programming (my university formally trains all students in Prolog and optionally Mercury) and functional programming (Our very first CS subject involves Haskell programming! It used to involve Miranda). Is there anything in the way of strategies that you would recommend to developers (I'm working on a MySQL/InnoDB based server app at the moment. Anyone else who reads this message please hit me with your 2c) regarding C programming and testing (they drill C into us with great rigor at the university I attend, but after second year it boils down to assignment submissions and that's about it)? The reason that I ask is that you have managed to create the world's fastest transactional database handler that runs on such a large number of different architectures and operating systems. Also, the fact that you've taken everything that Oracle tout as making them brilliant and doing it many times better than they have displays your commitment and incredible set of skills. I hope that one day that the software I am currently building will also contribute to both the commercial software world and the open source community. The fact that you've achieved this with such success and that you post to the MySQL mailing list so often is why I ask you these questions. Regards, Chris On Sat, 2004-02-14 at 08:13, Heikki Tuuri wrote: Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 12, 2004 2:26 PM Subject: Re: InnoDB Hot Backup + MySQL embedded? Dear Heikki, Thanks for the quick response! It never ceases to amaze me that such compartively small teams at Innobase Oy and MySQL AB produce such incredibly high-quality software. thank you for the praise :). Being a final-year Software Engineering student, I'm curious as to what you consider the most difficult problem to solve in building InnoDB and ibbackup has been. Of technical matters, multiversioning (consistent read) has been the most difficult to get working reliably. I wrote it probably around 1996, and still last year a bug was found in it. Adaptive hash indexes and the insert buffer have also been difficult to debug. The reason is obvious: in these 3 things parallel execution of threads as well as background cleanup operations complicate things. One-threaded, deterministic processing is much easier to debug than multithreaded nondeterministic execution. Given the fact that you set your goals extremely high for InnoDB (and have acheived them quite handily), I want to ensure that if anything remotely similar comes up in my final-year project that I either put my hand up for it and take on the challenge or run screaming in the other direction and have a somewhat easier year. In academic circles it is best not to put too much emphasis on programming work :). Referees tend to favor papers written in plain English, and want algorithms in pseudocode. They are not willing to dive into the full complexity of a real-world implementation. There has been quite a lot of debate and criticism around this gap between typical university research and practical applications, but things will probably not change in the next 2500 years. Regards, Chris Best regards, Heikki Heikki Tuuri wrote: Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, February 12, 2004 2:45 AM Subject: InnoDB Hot Backup + MySQL embedded? Hi all, I'm looking at developing an (open source) server-style application with the embedded MySQL library to be employed as the primary data store. Has anyone attempted to use InnoDB Hot Backup for such a beast? I ask as I do not know whether communication between ibbackup and the database engine is required (thus requiring a few changes to the embedded lib before compilation). A quick scan through the docs involved doesn't seem to point me in the right direction for a definitive answer on this. ibbackup can be used with the MySQL Embedded Server Library, no problem in that. ibbackup does not communicate with the mysqld server at all. It just reads the InnoDB data and log files. On the other hand, the innobackup Perl script does start a mysql connection to the server. Regards, Chris Best regards, Heikki Tuuri Innobase Oy
Is it correct, for DB accessing
Hi all, I have installed the MySQL 5.0.0a-aplha for Windows Previously I have some Database in 4.0.17-max, to access those database now from current version MySQL Server, I have just Copy the C:\mysql\Data\myDB folder into current server's Data file path. Is this enough to integrate thoes Databases into current version or anything i have to do further... I have easily access those databases till now... suggest me, is this make any trouble in future.. regard, 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]
Re: Enabling Mysql in php
php-mysql RPM is already installed. In any case, I downloaded last php version. Now configuration requires both mysql and apache path to specify in --with-mysql and --with-apache options. But what are these paths? Mysql and Apache files are everywhere. Thanks a lot. Mic. - Original Message - From: Alex Greg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 16, 2004 12:12 PM Subject: Re: Enabling Mysql in php Hi. I haven't mysql enabled in php. How may I do it? I have php 4.0.4, mysql 3.23.36, both installed on linux RH 7.3. Install the php-mysql RPM. -- Alex -- 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: AW: Is it correct, for DB accessing
hi, Thanks for prompt reply... I have installed MySQL 5.0.0a-alpha for windows, the purpose of my installation is to use the C API for prepared statements I have searched it on 4.1.1 also, it don't have some API what i have needed. i.e, mysql_prepare, mysql_bind_param, mysql_execute, mysql_fetch I want to confirm that Is this is the version implemented this C APIs? OR can I able to get these API from any of earlier versions? thanks Arun. --- Schmuck, Michael [EMAIL PROTECTED] wrote: Hi, Just copying will work but it's not really a good solution. Because your BIN files (the files where all SQL insert/update/delete statements are stored) will beginn with an existing database. In future, when you want to reconstruct the database from the bin files, you didn't have the create statements of the tabels and so on. A better way is to pump all data from the old to the new DB. Okay, just copying will work fine, but you will distroy the history of your BIN files! Rgds, Michael Schmuck -Ursprüngliche Nachricht- Von: Arunachalam [mailto:[EMAIL PROTECTED] Gesendet: Montag, 16. Februar 2004 13:15 An: [EMAIL PROTECTED] Betreff: Is it correct, for DB accessing Hi all, I have installed the MySQL 5.0.0a-aplha for Windows Previously I have some Database in 4.0.17-max, to access those database now from current version MySQL Server, I have just Copy the C:\mysql\Data\myDB folder into current server's Data file path. Is this enough to integrate thoes Databases into current version or anything i have to do further... I have easily access those databases till now... suggest me, is this make any trouble in future.. regard, 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]
Locking
How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James
Re: Locking
Hi! Next-key locking essentially doesn't work on rows - it works on indexes. It ensures that phantom reads can't happen. InnoDB does indeed do row-locking. In fact, it has one of the most efficient representations of locks of any relational database. Regards, Chris On Mon, 2004-02-16 at 23:43, James Kelty wrote: How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexed searching with OR ?
Hi List,
Can someone explain the results below? It seems that MySQL has a hard
time choosing keys for 'or' searches. The example here is very simple
but reflects the more complex cases where lots of rows or joins are
used perfectly:
1) That's the table I have:
artikelnummer varchar(13) not null
wordidint(11) not null
typ enum('interntitel', ...15 others cut off... ,'forlag')
with keys on:
wordid(Collation=A, Cardinality= 52447, Index_type=BTREE)
typ (Collation=A, Cardinality= 5, Index_type=BTREE)
artikelnummer (Collation=A, Cardinality= 59003, Index_type=BTREE)
2) It's optimized and analysed and I even tried rebuilding it from
scratch by dumping and re-reading it:
mysql analyze table wordlist;
+---+-+--+-+
| Table | Op | Msg_type | Msg_text|
+---+-+--+-+
| test.wordlist | analyze | status | Table is already up to date |
+---+-+--+-+
3) Some simple query to compare to:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000);
id 1
select_typeSIMPLE
table wordlist
type range
possible_keys wordid
keywordid
key_len4
refNULL
rows 3
Extra Using where; Using index
4) Now add a 'or' on the same field. It's still fine:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) or wordid in (2000,4500,8000);
id 1
select_typeSIMPLE
table wordlist
type range
possible_keys wordid
keywordid
key_len4
refNULL
rows 6
Extra Using where; Using index
5) Same thing on two different fields. Say good-bye to indexed
searching:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) or artikelnummer = '834534857345';
id 1
select_typeSIMPLE
table wordlist
type ALL
possible_keys wordid,artikelnummer
keyNULL
key_lenNULL
refNULL
rows 472026
Extra Using where
6) Now for fun an 'and' on the same conditions:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) and artikelnummer = '834534857345';
id 1
select_typeSIMPLE
table wordlist
type ref
possible_keys wordid,artikelnummer
keyartikelnummer
key_len13
refconst
rows 1
Extra Using where
What's the point of indices if I cannot combine two indexed fields with
OR ?
Any help appreciated,
Andreas Pardeike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed searching with OR ?
Hi!
MySQL's optimizer has a slight problem. OR queries cause it to get very
confused.
Try the following to get the best performance:
Rewrite SELECT FROM table WHERE (condition1) OR (condition2);
As:
(SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE
condition2);
Hope this helps!
Regards,
Chris
On Tue, 2004-02-17 at 00:05, Andreas Pardeike wrote:
Hi List,
Can someone explain the results below? It seems that MySQL has a hard
time choosing keys for 'or' searches. The example here is very simple
but reflects the more complex cases where lots of rows or joins are
used perfectly:
1) That's the table I have:
artikelnummer varchar(13) not null
wordidint(11) not null
typ enum('interntitel', ...15 others cut off... ,'forlag')
with keys on:
wordid(Collation=A, Cardinality= 52447, Index_type=BTREE)
typ (Collation=A, Cardinality= 5, Index_type=BTREE)
artikelnummer (Collation=A, Cardinality= 59003, Index_type=BTREE)
2) It's optimized and analysed and I even tried rebuilding it from
scratch by dumping and re-reading it:
mysql analyze table wordlist;
+---+-+--+-+
| Table | Op | Msg_type | Msg_text|
+---+-+--+-+
| test.wordlist | analyze | status | Table is already up to date |
+---+-+--+-+
3) Some simple query to compare to:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000);
id 1
select_typeSIMPLE
table wordlist
type range
possible_keys wordid
keywordid
key_len4
refNULL
rows 3
Extra Using where; Using index
4) Now add a 'or' on the same field. It's still fine:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) or wordid in (2000,4500,8000);
id 1
select_typeSIMPLE
table wordlist
type range
possible_keys wordid
keywordid
key_len4
refNULL
rows 6
Extra Using where; Using index
5) Same thing on two different fields. Say good-bye to indexed
searching:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) or artikelnummer = '834534857345';
id 1
select_typeSIMPLE
table wordlist
type ALL
possible_keys wordid,artikelnummer
keyNULL
key_lenNULL
refNULL
rows 472026
Extra Using where
6) Now for fun an 'and' on the same conditions:
mysql explain select wordid from wordlist where wordid in
(4000,5000,6000) and artikelnummer = '834534857345';
id 1
select_typeSIMPLE
table wordlist
type ref
possible_keys wordid,artikelnummer
keyartikelnummer
key_len13
refconst
rows 1
Extra Using where
What's the point of indices if I cannot combine two indexed fields with
OR ?
Any help appreciated,
Andreas Pardeike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database user access
* Andre MATOS Is it possible to allow one user to access MySQL using for example PHP but stop him when he tried to access MySQL directly? Yes, but only if the user does _not_ have shell access to your web/mysql server or if you create a application-specific user table. When you set up mysql users, you can decide from what computer they can access the database. Examples: - 'user'@'hostname' - can access from computer 'hostname' - 'user'@'localhost' - can access from localhost, using sockets - 'user'@'192.168.0.%' - can access from subnet 192.168.0.* - 'user'@'%' - can access from any computer URL: http://www.mysql.com/doc/en/GRANT.html If you run the web server and the mysql server on the same machine, you can restrict the user to connect only from localhost. If the user does not have shell access to that computer, he can not access the database except through the web interface (for example PHP). If the user does have shell access to the web server and/or the mysql server, you can restrict the access by creating a user table in your application. Let's say the user is named 'peter'. You create a record in _your_ user table, with 'peter', his encrypted password, email address and other information. You GRANT access to a different user, say 'PHPuser'. When 'peter' log in to your PHP application, your code connect to the database using 'PHPuser', check _your_ user table if the password for 'peter' was correct, and allows/denies access. That way the web interface allways have access to the database, and you can use the database regardless of the user, even for not-logged-in/unregistered users, and you don't need to distribute the _real_ mysql username/password. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Innodb Tablespace Management
Hi, I need to manage Innodb Tablespace very effectively from my application. To do that I require clear picture of Innodb tablespace file and its organization. My application needs to do the following 1. backup/restore of databases While doing so should calculate the size of source database, and check it against free space available in data directory. How do I calculate this figure for InnoDB databases 2. Free-Disk Space controlled inserts My application uses two databases normal and faults. I have to restrict faults database in disk size. At present if I have huge number of faults, fault database eats away the disk space(ibdata) and normal is not able to insert. I want to restrict disk space occupied by fault database(say, it can take max 5MB), how do I do it? 3. Innodb reaches table full soon. My system has only 50 MB Flash partition, where I have to store data. Innodb logs occupy 10MB and other files (.frm) occupies 10MB more, which leaves 30MB for ibdata file. Transactions return back table full when about 6000 rows are inserted to them. but show table status reports 3072KB free against the table name. Why is this contradiction? SHOW TABLE STATUS FROM Agora LIKE 'objects' | 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 | Create_options | Comment | | objects | InnoDB | Dynamic| 6054 |955 | 5783552 | NULL |0 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 3072 kB | Any Help will be greatly appreciated. Thanks a lot, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed searching with OR ?
On 2004-02-16, at 14.13, Chris Nolan wrote: MySQL's optimizer has a slight problem. OR queries cause it to get very confused. Try the following to get the best performance: Rewrite SELECT FROM table WHERE (condition1) OR (condition2); As: (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE condition2); Hope this helps! Then what would be your recommendation if I have the following search form: Search [A] in [field list A] ['AND'/'OR'/'AND NOT'] Search [B] in [field list B] ['AND'/'OR'/'AND NOT'] Search [C] in [field list C] ['AND'/'OR'/'AND NOT'] ... ( '[]' indicating popup's or text fields ) So far, I was building my query the normal way and it was already pretty complex code because of many different joins. Now, with your suggestion of UNION (which I tested and found working) everything gets VERY complex because I can't see an easy way to do an AND. I.e. if the users choses 'Cond. A' OR 'Cond. B' AND 'Cond. C', I have the problem that I can do a UNION on 'Cond. A' and 'Cond. B' (which in themselves can be complex) but I have no idea how to implement an 'AND' like between 'Cond. B' and 'Cond. C'. Is there a corresponding 'AND' version of the 'UNION' ? Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Documenting a mySQL server
I've just inherited a mySQL server. It has 13 databases, 5-50 tables per database. The largest table may have 10,000 records, but most tables are much smaller. What began as a test database server for a web developer in one department, has now become a tangled web of production databases, test databases, and abandoned databases for multiple departments. Of course, the only documentation I have is a Post-It note with the root password on it. The first thing I did was a complete backup of all databases. The second thing I did was change the root password. My next step is to try and document what is on the server. I am thinking I should document each database on the server, the purpose of the database, the department it belongs to, and a primary contact name, and a backup schedule for the database. Next, I think I should document all the user accounts and what permissions they have on the databases. Does this sound reasonable? Any advice? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: options file /root/.my.cnf ignored ???
Michael D Schleif [EMAIL PROTECTED] wrote: I have inherited several mysql v4x servers. All but one of them works as expected with /root/.my.cnf files. That last server is driving me nuts ; I have RTFM, and I do not find this particular problem anywhere. Pointers to TFM are welcome. # mysql -h localhost -u root ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) # sudo cat /root/.my.cnf [client] user = root password = This works OK: # mysql -h localhost -u root -p Enter password: Yes, I do know that password, and the one in /root/.my.cnf is the same, and I have changed the password for root with the same results. My own user, mds, works as expected via options file ~/.my.cnf . What am I missing? Check value of HOME environment variable. Does HOME environment variable point to the /root directory? -- 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: Newbie Question
On Feb 16, 2004, at 6:18 AM, Egor Egorov wrote: Please, reply to the mailing list, not to me directly. How many times do we have to see this sort of comment before the list administrators realize that not setting the Reply-to: header to the list address is doing the community a great disservice? I frequently see questions that could be relevant to my work, but never see the answers. If the goal of this list is to share knowledge among MySQL developers, adhering to some lame call for email purity by not munging headers (which is total bunk, IMO), is not helping that goal. ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
I'm confused. According to your note and to a passage I found in the manual, InnoDB support is installed in all 4.0.x versions of MySQL, which should obviously include my 4.0.11 install. Yet SHOW_VARIABLES LIKE 'have_innodb' returned NO. Why? It would appear that I need to do something besides installing a 4.0.x version of MySQL but I can't tell what that is from the manual. I found one section that said installing MySQL-Max-VERSION.i386.rpm will give me additional capabilities - without specifying what those capabilities were in any way. Do I need this RPM to get InnoDB support? By the way, I'm willing to install a newer version of MySQL if that will help. However, that raises some additional questions. I found the Upgrading/Downgrading section of the manual but it's pretty vague. For example it doesn't explicity say whether I need to uninstall 4.0.11 before installing the newer version. I suspect from the wording that I don't need to uninstall first but I'd feel a lot happier is someone could confirm that. Also, the manual says that we are supposed to take backups of our databases before upgrading but the Database Backups section describes several different ways to do backups, each of which seems to be different. Which one should I use: a regular backup or an SQL level backup? Should I use SELECT INTO OUTFILE? BACKUP TABLE? mysqldump? mysqlhotcopy? What are the pros and cons of each? Do I need to do LOCK TABLES and FLUSH TABLES for each of these approaches? If yes, what is the right sequence: do I need to LOCK TABLES and FLUSH TABLES first, then do the backup? Or do I LOCK TABLES, backup, then FLUSH TABLES? The manual leaves a lot to the imagination and I've got a good imagination so I need some clarification ;-) Rhino - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 16, 2004 6:18 AM Subject: Re: Newbie Question Please, reply to the mailing list, not to me directly. Date: Sat, 14 Feb 2004 09:11:14 -0500 From: Rhino [EMAIL PROTECTED] To: Egor Egorov [EMAIL PROTECTED] Subject: Re: Newbie Question Thanks Egor! I ran the query you suggested and it said 'NO' so the server was apparently configured without InnoDB support. What do I need to do to activate the InnoDB support? Is it just a matter of configuring a few settings or do I need to re-install MySQL? You can download and install official 4.0.x binary distribution: http://www.mysql.com/downloads/mysql-4.0.html All official 4.0.x binaries include InnoDB storage engine. 4.0.11 is an outdated version, so upgrade is recommended in any case. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 14, 2004 4:04 AM Subject: Re: Newbie Question Rhino [EMAIL PROTECTED] wrote: I'm new to MySQL but I have extensive experience with DB2 so I'm getting quite confused about how MySQL is supposed to work. I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am trying to create a pair of InnoDB tables that are related to one another via a foreign key. I created the tables successfully but when I try to insert a row into the child table that violates the foreign key, MySQL loads the bad row, even though the foreign key doesn't exist! [skip] I got a very big clue when I ran this command: show table status from Sample; It showed that my two tables were type MyISAM, *not* InnoDB. If my tables really are MyISAM, then I'm not surprised that the foreign key constraint doesn't work since MyISAM doesn't support foreign keys, at least as I understand the manual. However, this doesn't answer the big question: *Why* aren't my tables InnoDB since I explicitly defined them that way?? Can any MySQL veterans clear up this mystery for me? If you try to create table type that is disabled or not compiled-in, MySQL creates MyISAM table type. Execute statement SHOW VARIABLES LIKE have_innodb; If you see 'DISABLED' in the output, it means that you run MySQL server with skip-innodb option. If you see 'NO' in the output, it means that MySQL server was configured without InnoDB support. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /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]
Restore And Dump
Hello, I am running two instances of mysql. They both run on different boxes, not networked. Anyway, I have been developing on one machine, and trying to do a dump and keep the other box updated with Database changes. I have ran this from the dev box. mysqldump --add-drop-table -u dbusername -p dbname dbname.bak.dump // or better yet... mysqldump --opt -u dbusername -p dbname dbname.bak.dump to get the latest dump. Then copy this to CD to reimport on the production box. The only problem, is I don't seem to understand how to reimport, or use this dump. Can anyone help. PS, I have a lot of blobs and clobs within the Database. Is there anything else I may be missing to keep these two synchronized? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
To quote the words of an old song I second that emotion. As the latest poster to fall into this trap, I just assumed that this list worked like others to which I subscribe: the user simply hits Reply on the post to which he is replying and the note goes to everyone on the list, not just the person who made the original post. It's going to be a pain in the a** to remember that I have to manually enter [EMAIL PROTECTED] for all posts to this list but NOT to my other lists Rhino - Original Message - From: Ed Leafe [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, February 16, 2004 8:50 AM Subject: Re: Newbie Question On Feb 16, 2004, at 6:18 AM, Egor Egorov wrote: Please, reply to the mailing list, not to me directly. How many times do we have to see this sort of comment before the list administrators realize that not setting the Reply-to: header to the list address is doing the community a great disservice? I frequently see questions that could be relevant to my work, but never see the answers. If the goal of this list is to share knowledge among MySQL developers, adhering to some lame call for email purity by not munging headers (which is total bunk, IMO), is not helping that goal. ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.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: options file /root/.my.cnf ignored ???
Michael D Schleif wrote: I have inherited several mysql v4x servers. All but one of them works as expected with /root/.my.cnf files. Make sure /root/.my.cnf is owned by root, and mode 600. That last server is driving me nuts ; I have RTFM, and I do not find this particular problem anywhere. Pointers to TFM are welcome. # mysql -h localhost -u root ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) # sudo cat /root/.my.cnf [client] user = root password = This works OK: # mysql -h localhost -u root -p Enter password: Yes, I do know that password, and the one in /root/.my.cnf is the same, and I have changed the password for root with the same results. My own user, mds, works as expected via options file ~/.my.cnf . What am I missing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore And Dump
Hello, I am running two instances of mysql. They both run on different boxes, not networked. Anyway, I have been developing on one machine, and trying to do a dump and keep the other box updated with Database changes. I have ran this from the dev box. mysqldump --add-drop-table -u dbusername -p dbname dbname.bak.dump // or better yet... mysqldump --opt -u dbusername -p dbname dbname.bak.dump to get the latest dump. Then copy this to CD to reimport on the production box. The only problem, is I don't seem to understand how to reimport, or use this dump. Can anyone help. On the production box: mysql -u dbusername -p dbname dbname.bak.dump PS, I have a lot of blobs and clobs within the Database. Is there anything else I may be missing to keep these two synchronized? These should be OK. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed searching with OR ?
Hello again! Andreas Pardeike wrote: On 2004-02-16, at 14.13, Chris Nolan wrote: MySQL's optimizer has a slight problem. OR queries cause it to get very confused. Try the following to get the best performance: Rewrite SELECT FROM table WHERE (condition1) OR (condition2); As: (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE condition2); Hope this helps! Then what would be your recommendation if I have the following search form: Search [A] in [field list A] ['AND'/'OR'/'AND NOT'] Search [B] in [field list B] ['AND'/'OR'/'AND NOT'] Search [C] in [field list C] ['AND'/'OR'/'AND NOT'] ... ( '[]' indicating popup's or text fields ) So far, I was building my query the normal way and it was already pretty complex code because of many different joins. Now, with your suggestion of UNION (which I tested and found working) everything gets VERY complex because I can't see an easy way to do an AND. Hmmforgive me, but I am about to resort to boolean algebra. (A OR B) AND C = (A AND C) OR (B AND C) . Thus: (SELECT * FROM table WHERE (condA AND condC)) UNION (SELECT * FROM TABLE WHERE (condB AND condC)); I guess you could rewrite you queries using the above method, or bug the MySQL team to prioritise the modification to the optimiser. I.e. if the users choses 'Cond. A' OR 'Cond. B' AND 'Cond. C', I have the problem that I can do a UNION on 'Cond. A' and 'Cond. B' (which in themselves can be complex) but I have no idea how to implement an 'AND' like between 'Cond. B' and 'Cond. C'. Is there a corresponding 'AND' version of the 'UNION' ? Andreas Pardeike Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Rhino wrote: To quote the words of an old song I second that emotion. As the latest poster to fall into this trap, I just assumed that this list worked like others to which I subscribe: the user simply hits Reply on the post to which he is replying and the note goes to everyone on the list, not just the person who made the original post. Try reply all instead of reply. This should work with all lists. It's going to be a pain in the a** to remember that I have to manually enter [EMAIL PROTECTED] for all posts to this list but NOT to my other lists Rhino - Original Message - From: Ed Leafe [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, February 16, 2004 8:50 AM Subject: Re: Newbie Question On Feb 16, 2004, at 6:18 AM, Egor Egorov wrote: Please, reply to the mailing list, not to me directly. How many times do we have to see this sort of comment before the list administrators realize that not setting the Reply-to: header to the list address is doing the community a great disservice? I frequently see questions that could be relevant to my work, but never see the answers. If the goal of this list is to share knowledge among MySQL developers, adhering to some lame call for email purity by not munging headers (which is total bunk, IMO), is not helping that goal. ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.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: Reply-to [was Newbie Question]
On Monday 16 February 2004 14:18, gerald_clark wrote: Rhino wrote: To quote the words of an old song I second that emotion. As the latest poster to fall into this trap, I just assumed that this list worked like others to which I subscribe: the user simply hits Reply on the post to which he is replying and the note goes to everyone on the list, not just the person who made the original post. Try reply all instead of reply. This should work with all lists. Whee, old dead beaten and flogged horse resurrected again - campaign for your mail client author company to support the RFC that specifies how mailing list headers are generated. Namely, the headers: List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] I just have to hit 'Reply to list' and my reply goes to the list. If I want to reply to the person, I just hit reply. Both, reply to all. Works quite well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to speed up bit field queries?
Hi, I like to use bit fields for status flag information, using a simple attributes unsigned NOT NULL default '0' column. I used to use the set datatype for this, but - there is no simple set a bit or turn off a bit function for the set datatype (one has to use bitwise operations, anyway) - reading set data from selects requires string processing - one has to use alter table when a new flag is added (which is quite a penalty on large databases) Nonetheless, both the set datatype and a simple int bitfield as described above both have a major disadvantage: One cannot use an index to speed up queries such as select * from bla where attributes 1; Are there any plans to add a special key type for bit fields (just as there is a special key type for full-text search now)? Or is there a key type for sets by now? ([1] claims there isn't.) Thanks, Hanno [1] http://www.vbmysql.com/articles/mysqlsetdatatype.html -- Hanno Müller, Dipl.-Inform. epublica GmbH http://www.epublica.de Tel. +49 (0)40/4109879-4 Open Business Club - Mitglied werden: http://www.openbc.com/go/invanon/2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting a mySQL server
Mark Russell [EMAIL PROTECTED] wrote on 16/02/2004 13:41:52: What began as a test database server for a web developer in one department, has now become a tangled web of production databases, test databases, and abandoned databases for multiple departments. Of course, the only documentation I have is a Post-It note with the root password on it. That's a historic quote which should be framed and put above any database developers monitor. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Some Information..
Dear Sir or Madam, So far ( for my existing applications in Java ) I was using MySQL as my primary DBMS. I would appreciate if you could guide / advice me about the New MaxDB. What is better to do ( for my New Programs ) ; keep using MySQL ? Thanks Christos
Re: Strange behavior with IF?
On Mon, 2004-02-16 at 09:07, Batara Kesuma wrote: Hi, Can someone tell me why this query doesn't work? SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, network) should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level = member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple concurrent transactions per connection
Funny you should mention the totally excellent DBMail - I've deployed it for quite a few of my clients. The project I am working on is more of a sod off in the direction of Exchange. I'm aiming for something collaborative, with a proper backing store that's easily deployable and an original open source work. Regards, Chris Curtis Maurand wrote: checkout http://www.dbmail.org On Sun, 15 Feb 2004, Chris Nolan wrote: Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting a mySQL server
Mark Russell wrote: What began as a test database server for a web developer in one department, has now become a tangled web of production databases, test databases, and abandoned databases for multiple departments. Of course, the only documentation I have is a Post-It note with the root password on it. The first thing I did was a complete backup of all databases. The second thing I did was change the root password. My next step is to try and document what is on the server. I am thinking I should document each database on the server, the purpose of the database, the department it belongs to, and a primary contact name, and a backup schedule for the database. After documenting who ownes which database, ask each department what SLA they expect on their database to see if there are people having unrealistic high uptime and time-to-fix expectations. That should give you a way to prioritize the rest. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
its possible it was never installed for some reason. did you install the
mysql binary? you may simply be running on defaults. at any rate, i'm
willing to bet the farm that when you get your system configured right,
it will behave as you expect.
is there a permission problem that is not allowing you to see the file?
what user runs mysqld?
there must be an example of a my.cnf file somewhere on the mysql
website. grab it, set up innodb data files, and if you want, log files.
good luck
jeff
Rhino wrote:
I tried adding that space after the closing parenthesis in both Create Table
statements; it made no difference at all.
You're probably right about the InnoDB support not being turned on. I read
the article about configuring my.cnf and wanted to try playing with the
settings but I'm darned if I can find the my.cnf file!
a) I have no file called /etc/my.cnf.
b) I think MySQL was installed from an RPM as a binary but I don't recall
for sure. I'm not sure though so I checked /usr/local/mysql/data: I have a
/usr/local but no mysql directory in /usr/local. I also checked
/usr/local/var: I have no var directory in /usr/local.
c) I have no idea what was specified with --defaults-extra-file= and have no
idea how to find out.
d) I have no file called .my.cnf in my home directory (/home/rhino).
Any idea where I can find my my.cnf file? (For what it's worth, I tried find
/ -name 'my.cnf' but got the message Permission denied. I'm not sure why
permission is denied; I don't use Linux very often and haven't used it much
in several months but I know the 'find' command worked last time I tried
it).
Anyway, if anyone could tell me how to find my.cnf and verify that InnoDB is
set up correctly, I'd appreciate it.
Rhino
- Original Message -
From: Jeff Mathis [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Friday, February 13, 2004 6:44 PM
Subject: Re: Newbie Question
might be as simple as putting a space after your closing parenthesis on
the create table statement.
either that, or your mysql install somehow doesn't have innodb table
support. have you edited your my.cnf file and enabled the innodb
parameters, specifically log and data files?
Rhino wrote:
I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to create a pair of InnoDB tables that are related to one another
via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--
use Sample;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
)Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
)Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values
('10', 'Christine', 'I', 'Haas','A00',5.00),
('20', 'Cliff', ' ', 'Jones', 'D11', 3.00),
('30', 'FK', ' ', 'Mistake', 'X99', 12345.67),
('40', 'Brad', ' ', 'Dean', 'E21', 35000.00);
---
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type MyISAM, *not* InnoDB. If my
tables really are MyISAM, then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at
least as I understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino
---
rhino1 AT sympatico DOT ca
If you want the best seat in the house, you'll have to move the cat.
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
Hi Diana, SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) ORDER BY member.last_login DESC LIMIT 0,3 +---+-+ | level | photo_level | +---+-+ | 4 | 4 | | 4 | 4 | | 4 | 4 | +---+-+ 3 rows in set (0.01 sec) Then, when I add WHERE. SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) WHERE level = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Empty set (0.00 sec) I think it is supposed to return all rows, since all level is the same as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, network) should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level = member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 Thank you for the reply. I have it fixed, the problem is I should have used HAVING instead of WHERE and I couldn't use AS level, because HAVING will confuse it with network.level which is NULL. So here is the working query. SELECT IF(ISNULL(network.level), 4, network.level) AS level_alias, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) HAVING level_alias = member.photo_level ORDER BY member.last_login DESC LIMIT 0,3 Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Constant time factor for subqueries?
Hi List, why are subqueries so slow? Is this because their implementation is still not optimized? Take for example the following queries and have a look at the timing: mysql select wordid from words where val = 'henning'; ++ | wordid | ++ | 144005 | ++ 1 row in set (0.00 sec) mysql select count(*) from wordlist where wordid in (select wordid from words where val = 'henning'); +--+ | count(*) | +--+ | 55 | +--+ 1 row in set (0.88 sec) mysql select count(*) from wordlist where wordid in (144005); +--+ | count(*) | +--+ | 55 | +--+ 1 row in set (0.00 sec) I did more tests and observed a constant time factor around 1 sec that is introduced by using subqueries. I depend on those queries running fast and if I have a 0.88 sec overhead it will sum up quickly. So for now, I either use local code to do the query first and insert the value manually or use a join. Bottom line: I would really love to use subqueries but something seems not right. Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Levenshtein
When will Levenshtein algorithm be availble in mySQL? I know that there are some UDF's but I find them awkward to install. Is there a special reason that this isn't included in MySQL? Are there license fees or what? At least the algorithm seems most simple. ..Hakon
Re: Multiple concurrent transactions per connection
checkout http://www.dbmail.org On Sun, 15 Feb 2004, Chris Nolan wrote: Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in subqueries?
Hi List, should I report the following as a bug? mysql select count(*) from words as w1 where w1.val in (select w2.val from words as w2 where w2.val like 'm%'); +--+ | count(*) | +--+ | 3552 | +--+ 1 row in set (0.78 sec) mysql select count(*) from words as w1 where w1.val in (select w3.val from words as w3 where w3.val like 'e%'); +--+ | count(*) | +--+ | 1626 | +--+ 1 row in set (0.78 sec) mysql select count(*) from words as w1 where w1.val in (select w2.val from words as w2 where w2.val like 'm%') and w1.val in (select w3.val from words as w3 where w3.val like 'e%'); +--+ | count(*) | +--+ |53556 | +--+ 1 row in set (0.08 sec) mysql select count(*) from words; +--+ | count(*) | +--+ |53556 | +--+ 1 row in set (0.00 sec) Regards, Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locking
Does it have exclusive and shared? -James -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 4:52 AM To: James Kelty Cc: [EMAIL PROTECTED] Subject: Re: Locking Hi! Next-key locking essentially doesn't work on rows - it works on indexes. It ensures that phantom reads can't happen. InnoDB does indeed do row-locking. In fact, it has one of the most efficient representations of locks of any relational database. Regards, Chris On Mon, 2004-02-16 at 23:43, James Kelty wrote: How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Question About Replication
Hi All, A short question: If I have a slave MySQL Server (updating via logs). Supposing the master MySQL (for any reason) goes down. The users start using (and updating) the slave database. When the master MySQL goes up. Does it will update it´s data from the slave database? Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting PACK_KEYS=1 on existing tables
Is there a way to set PACK_KEYS=1 on existing tables without doing a dump and load? I didn't find it in the ALTER TABLE syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ib* files in /var/lib/mysql
I noticed these files in my mysql data directory: -rw-rw1 mysqlmysql 5242880 Feb 15 20:54 ib_logfile0 -rw-rw1 mysqlmysql10485760 Feb 15 20:54 ibdata1 -rw-rw1 mysqlmysql 25088 Jun 3 2003 ib_arch_log_00 -rw-rw1 mysqlmysql 5242880 Jun 3 2003 ib_logfile1 I believe they are InnoDB related, but as far as I know, I'm not using any InnoDB tables. Is it OK to delete them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting PACK_KEYS=1 on existing tables
On 16 Feb 2004 at 12:46, Bill Marrs wrote: Is there a way to set PACK_KEYS=1 on existing tables without doing a dump and load? I didn't find it in the ALTER TABLE syntax. It's there. alter_specification can be table_options, and (looking at the CREATE TABLE syntax) table_options can be a single table_option, and table_option can be PACK_KEYS = 1. So the syntax is just ALTER TABLE table_name PACK_KEYS = 1; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting PACK_KEYS=1 on existing tables
In the last episode (Feb 16), Bill Marrs said: Is there a way to set PACK_KEYS=1 on existing tables without doing a dump and load? I didn't find it in the ALTER TABLE syntax. It's there: ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...] alter_specification: ... | table_options It's a table option. They're detailed in the CREATE TABLE syntax description. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL SQL Conversion
I am using MySQL (4.0.17) and am getting errors with the following SQL statement originally writen for MS products. SELECT survey_questions.Question_ID, First(survey_questions.Question_Text) AS FirstOfQuestion_Text, == this line returns errors i believe it is the AS Avg(survey_responses.Response) AS AvgOfResponse, Count(survey_responses.Response) AS CountOfResponse, survey_questions.survey_num FROM survey_questions INNER JOIN survey_responses ON survey_questions.Question_ID = survey_responses.QuestionID GROUP BY survey_questions.Question_ID, survey_questions.survey_num HAVING (((Avg(survey_responses.Response))-1) AND ((survey_questions.survey_num)=0)); Thanks in advance, ~Logan
Re: Newbie Question
well, somewhere the documentation for mysql indicates that you need mysql-max in order to get InnoDB support. we made a special point of making sure we got the mysql-max binary specifically because of InnoDB. as far as upgrades on your platform, thats now out of my league. I don't think I'm qualified to give you an answer. also, you may want to reply all -- you seem to be sending mail to me specifically. no doubt the rest of the list may want to comment. jeff Rhino wrote: By it (in your first sentence), I assume you mean InnoDB as opposed to MySQL. We've been using MySQL without difficulty for several months. I just checked with the system admin and he says that he installed the binary from an RPM. We are running Mandrake 9.1 - I thought we were using RedHat 9.2 but I was mistaken - and he says that he used the MySQL distribution that is available from Mandrake. However, it turns out that we *didn't* install the MySQL-Max RPM, only the Common RPM. [I can't find any mention of a Common RPM in the MySQL manual but Clive says it is probably Mandrake's re-bundling of one or more of the RPMs available from the MySQL download page.] On reading the RPM documentation, he found that the MySQL-Max RPM contains the InnoDB support. [That's something I did NOT find in the MySQL manual! All it says is that MySQL-Max offers additional capabilities without saying what those are. Aside to the people doing the documentation for MySQL: PLEASE indicate somewhere in the InnoDB and/or Installing chapters that the MySQL-Max RPM needs to be installed in order to get InnoDB support!!!] As further confirmation, we did searches on the server and could not find any file named 'my.cnf' or 'my.ini' anywhere on the server. So that must be the problem: if we simply install the MySQL-Max RPM, we should get our InnoDB support. Right? Now, some followup questions. 1. Can we simply install the MySQL-Max RPM without any further preparation or should we do database backups first? If we need to do backups, which approach should we use and why? The backups chapter lists several different approaches without discussing the pros and cons of each approach in any way. We don't have enough MySQL experience to know if we should be doing BACKUP TABLE, mysqldump, or mysqlhotcopy. Can someone clue us in? 2. Does the MySQL-Max RPM need to be at the same level as the other MySQL RPM - 4.0.11 - or can the MySQL-Max RPM be later, like 4.0.15? 3. I was advised in another post to upgrade to something newer than 4.0.11 as this is obsolete. Would we be okay to go to 4.0.15 or should we go with 4.0.17? Clive strongly prefers to use the Mandrake RPMs over the MySQL RPMs. According to the Mandrake docs, the Mandrake version of 4.0.15 seems is the latest stable version they have while the Mandrake version of 4.0.17 is a cooker, which appears to be a synonym for an alpha or beta. I'd rather stay with something stable than mess with alpha or beta code but if 4.0.15 is not significantly better than 4.0.11, I'd rather stay with 4.0.11 because it works fine and is less work than upgrading to a newer version. Rhino - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, February 16, 2004 11:15 AM Subject: Re: Newbie Question its possible it was never installed for some reason. did you install the mysql binary? you may simply be running on defaults. at any rate, i'm willing to bet the farm that when you get your system configured right, it will behave as you expect. is there a permission problem that is not allowing you to see the file? what user runs mysqld? there must be an example of a my.cnf file somewhere on the mysql website. grab it, set up innodb data files, and if you want, log files. good luck jeff Rhino wrote: I tried adding that space after the closing parenthesis in both Create Table statements; it made no difference at all. You're probably right about the InnoDB support not being turned on. I read the article about configuring my.cnf and wanted to try playing with the settings but I'm darned if I can find the my.cnf file! a) I have no file called /etc/my.cnf. b) I think MySQL was installed from an RPM as a binary but I don't recall for sure. I'm not sure though so I checked /usr/local/mysql/data: I have a /usr/local but no mysql directory in /usr/local. I also checked /usr/local/var: I have no var directory in /usr/local. c) I have no idea what was specified with --defaults-extra-file= and have no idea how to find out. d) I have no file called .my.cnf in my home directory (/home/rhino). Any idea where I can find my my.cnf file? (For what it's worth, I tried find / -name 'my.cnf' but got the message Permission denied. I'm not sure why permission is denied; I don't use Linux very often and haven't used it much in several months but I know the 'find' command worked last time I tried it). Anyway, if anyone could tell me how to find my.cnf and verify that
Cygwin and Mysql Tools
I'm looking for the Cygwin-compiled ports of the Mysql tools mysql and mysqldump. There is an old version on the Mysql site, but being that it was compiled back in 1998, it does not function with the current versions of Cygwin. -- A.Clausen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Newbie Question
By it (in your first sentence), I assume you mean InnoDB as opposed to MySQL. We've been using MySQL without difficulty for several months. I just checked with the system admin and he says that he installed the binary from an RPM. We are running Mandrake 9.1 - I thought we were using RedHat 9.2 but I was mistaken - and he says that he used the MySQL distribution that is available from Mandrake. However, it turns out that we *didn't* install the MySQL-Max RPM, only the Common RPM. [I can't find any mention of a Common RPM in the MySQL manual but Clive says it is probably Mandrake's re-bundling of one or more of the RPMs available from the MySQL download page.] On reading the RPM documentation, he found that the MySQL-Max RPM contains the InnoDB support. [That's something I did NOT find in the MySQL manual! All it says is that MySQL-Max offers additional capabilities without saying what those are. Aside to the people doing the documentation for MySQL: PLEASE indicate somewhere in the InnoDB and/or Installing chapters that the MySQL-Max RPM needs to be installed in order to get InnoDB support!!!] As further confirmation, we did searches on the server and could not find any file named 'my.cnf' or 'my.ini' anywhere on the server. So that must be the problem: if we simply install the MySQL-Max RPM, we should get our InnoDB support. Right? Now, some followup questions. 1. Can we simply install the MySQL-Max RPM without any further preparation or should we do database backups first? If we need to do backups, which approach should we use and why? The backups chapter lists several different approaches without discussing the pros and cons of each approach in any way. We don't have enough MySQL experience to know if we should be doing BACKUP TABLE, mysqldump, or mysqlhotcopy. Can someone clue us in? 2. Does the MySQL-Max RPM need to be at the same level as the other MySQL RPM - 4.0.11 - or can the MySQL-Max RPM be later, like 4.0.15? 3. I was advised in another post to upgrade to something newer than 4.0.11 as this is obsolete. Would we be okay to go to 4.0.15 or should we go with 4.0.17? Clive strongly prefers to use the Mandrake RPMs over the MySQL RPMs. According to the Mandrake docs, the Mandrake version of 4.0.15 seems is the latest stable version they have while the Mandrake version of 4.0.17 is a cooker, which appears to be a synonym for an alpha or beta. I'd rather stay with something stable than mess with alpha or beta code but if 4.0.15 is not significantly better than 4.0.11, I'd rather stay with 4.0.11 because it works fine and is less work than upgrading to a newer version. Rhino - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, February 16, 2004 11:15 AM Subject: Re: Newbie Question its possible it was never installed for some reason. did you install the mysql binary? you may simply be running on defaults. at any rate, i'm willing to bet the farm that when you get your system configured right, it will behave as you expect. is there a permission problem that is not allowing you to see the file? what user runs mysqld? there must be an example of a my.cnf file somewhere on the mysql website. grab it, set up innodb data files, and if you want, log files. good luck jeff Rhino wrote: I tried adding that space after the closing parenthesis in both Create Table statements; it made no difference at all. You're probably right about the InnoDB support not being turned on. I read the article about configuring my.cnf and wanted to try playing with the settings but I'm darned if I can find the my.cnf file! a) I have no file called /etc/my.cnf. b) I think MySQL was installed from an RPM as a binary but I don't recall for sure. I'm not sure though so I checked /usr/local/mysql/data: I have a /usr/local but no mysql directory in /usr/local. I also checked /usr/local/var: I have no var directory in /usr/local. c) I have no idea what was specified with --defaults-extra-file= and have no idea how to find out. d) I have no file called .my.cnf in my home directory (/home/rhino). Any idea where I can find my my.cnf file? (For what it's worth, I tried find / -name 'my.cnf' but got the message Permission denied. I'm not sure why permission is denied; I don't use Linux very often and haven't used it much in several months but I know the 'find' command worked last time I tried it). Anyway, if anyone could tell me how to find my.cnf and verify that InnoDB is set up correctly, I'd appreciate it. Rhino - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Friday, February 13, 2004 6:44 PM Subject: Re: Newbie Question might be as simple as putting a space after your closing parenthesis on the create
grant problem
Greetings, This is how the entry looks like in the mysql.user table. So basically, root has all the global privileges. | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | myhost.edu | root | 443dfb6b3a231d1e | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | +--+--+--+-+-+-+-+-+---+-+---+--+---++-+++ 1 row in set (0.00 sec) == I did the following grant as user 'root' and got the error: mysql grant all on *.* to 'someuser'@% identified by 'secret'; ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Any idea what I did wrong? Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: client and server question
In a message dated 2/15/2004 4:39:00 PM Eastern Standard Time, [EMAIL PROTECTED] writes: I've got an iBook running gentoo and MySQL version 4.0.17 and it's on the same router with my desktop running gentoo and MySQL version 4.0.17. I would simply like to use the iBook as a mysql client and have it connect to the mysql server running on my desktop. What is the simplest way to accomplish this? I am not familiar with 'gentoo' but connecting a mysql client to a server is pretty straigh-forward: at the command prompt on the client: mysql -h DB-server-hostname-or-IP -u YourMySQL-username -p YourMySQL-user-name password DB-name From an application standpoint, that depends on the tool being used, but there are several methods, a C/C++ API, ODBC drivers, a JDBC driver, Perl'd infamous DBI engine, PHP, etc. Not trying to duck the question, but it really depends on the amount of data, size of the database, your programming skillset,, whether you are using off-the-shelf products, etc. Many paths, much documentation on the mysql.com site. I'd start there. HTH. /t
Foreign ky referencing more than 1 table
Hi, I have an address table, and employee and customer tables. I am thinking of adding an address_owner field to the address table and use it as a foreign key referencing both the customer_no and the employee_no number. I have just done that on mysql, but I do not feel that confident about it - is this acceptable? what are the pros and cons? Am I on the right track? and how else can I handle this? (I understand I can have link tables tables to keep the links but that looks like too much and unnecessary?) Best Regards __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
here it is: http://www.mysql.com/news/article-111.html its also all over the mysql reference manual, especially in the InnoDB tables section. Rhino wrote: - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, February 16, 2004 1:18 PM Subject: Re: Newbie Question well, somewhere the documentation for mysql indicates that you need mysql-max in order to get InnoDB support. we made a special point of making sure we got the mysql-max binary specifically because of InnoDB. Really? Could you point it out? In the installing chapter, it only says mysql-max gives additional capabilites without enumerating them. I didn't see anything the InnoDB section saying that mysql-max was necessary but I didn't read every single word so I could have missed it as far as upgrades on your platform, thats now out of my league. I don't think I'm qualified to give you an answer. Fair enough. also, you may want to reply all -- you seem to be sending mail to me specifically. no doubt the rest of the list may want to comment. Sorry! I'm still getting used to doing Reply All instead of Reply. I'll get it eventually Rhino -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can 64-bit MySQL replicate to 32-bit MySQL?
Are the binary logs in the same format? Are data types the same? Thanks in advance, David
Newbie
I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql on a encrypted loopback partitition
hi, all. i'm looking into the possibility of mounting my /var/lib/mysql directory (or only some databases therein) on a AES128-encrypted loop partitition. could anyone share his/her experience with that? is the performace-drain tolerable (i'd reallt hate to switch to XOR 'encryption')? is this whole thing fault-tolerant (i'm guessing not really, since there are now two 'layers of possible failure' [the 'real' fs, and the loop'ed one]). is there any better solution to secure data? thanks, M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie
Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- 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: ft_boolean_default
Hi! On Feb 13, Heath, Brad wrote: Is this a legitimate variable? It seems like it'd be useful, but when I tried it (on 4.0.12-nt and 4.0.17-nt) it didn't work. Anybody know what's going on? When it was added there was not possible to have a --server_variable that takes a string as a value. Now it's possible, but 4.0 is stable, so I cannot make ft_boolean_syntax a changeable variable there. But I just pushed this to 4.1. Should be in 4.1.2 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie
Thanks for the quick reply! As root, I tried: mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; But I got the following error: ERROR 1: Can't create/write to file './research1data/oldreport_2002_12_08.frm' (Errcode: 13) I'm SU and root in mysql so I don't thinks it's a permissions problem. Is my SQL correct? Cam On Mon, 2004-02-16 at 13:09, Bing Du wrote: Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- 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: Newbie
Hi I am not familiar with the syntax mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; try mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports; to create populated copy or mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; to create an empty copy I hope this helps Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -Original Message- From: Cameron Ortis [mailto:[EMAIL PROTECTED] Sent: 16 February 2004 21:35 To: Bing Du Cc: [EMAIL PROTECTED] Subject: Re: Newbie Thanks for the quick reply! As root, I tried: mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; But I got the following error: ERROR 1: Can't create/write to file './research1data/oldreport_2002_12_08.frm' (Errcode: 13) I'm SU and root in mysql so I don't thinks it's a permissions problem. Is my SQL correct? Cam On Mon, 2004-02-16 at 13:09, Bing Du wrote: Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Mailscanner thanks transtec Computers for their support. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Mailscanner thanks transtec Computers for their support. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting rows during an import
I would like to update a mysql database using mysqlimport, from a text file. Of course new rows will be added, and I assume modified rows will be modified, but I would also like to Delete rows in the DB that are somehow flagged for deleteion in the text file. I notice that in the documentation that there is a Records Deleted stat reported back after the import is complete. Can anyone tell me if this can or can't be done and how I could do it? Sean Laurence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie
Thanks for the response. The syntax: mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; wouldn't that just create an empty file? I need to get access to the data in oldreport_2002_12_08.MYD using oldreports.frm. $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm Cam On Mon, 2004-02-16 at 13:46, Simon Windsor wrote: Hi I am not familiar with the syntax mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; try mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports; to create populated copy or mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; to create an empty copy I hope this helps Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -Original Message- From: Cameron Ortis [mailto:[EMAIL PROTECTED] Sent: 16 February 2004 21:35 To: Bing Du Cc: [EMAIL PROTECTED] Subject: Re: Newbie Thanks for the quick reply! As root, I tried: mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; But I got the following error: ERROR 1: Can't create/write to file './research1data/oldreport_2002_12_08.frm' (Errcode: 13) I'm SU and root in mysql so I don't thinks it's a permissions problem. Is my SQL correct? Cam On Mon, 2004-02-16 at 13:09, Bing Du wrote: Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Mailscanner thanks transtec Computers for their support. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie
Yes, that would create a new table (only the oldreport_2002_12_08.MYD file will be zero, the MYI and frm files are not) which has exactly the same definition of the table oldreports but without any data in it. So make sure you have a good copy of both oldreport_2002_12_08.MYD and oldreport_2002_12_08.MYI. Better rename them temporarily to something else and move them back after table 'oldreport_2002_12_08' is created. Bing Thanks for the response. The syntax: mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; wouldn't that just create an empty file? I need to get access to the data in oldreport_2002_12_08.MYD using oldreports.frm. $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm Cam On Mon, 2004-02-16 at 13:46, Simon Windsor wrote: Hi I am not familiar with the syntax mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; try mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports; to create populated copy or mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; to create an empty copy I hope this helps Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -Original Message- From: Cameron Ortis [mailto:[EMAIL PROTECTED] Sent: 16 February 2004 21:35 To: Bing Du Cc: [EMAIL PROTECTED] Subject: Re: Newbie Thanks for the quick reply! As root, I tried: mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; But I got the following error: ERROR 1: Can't create/write to file './research1data/oldreport_2002_12_08.frm' (Errcode: 13) I'm SU and root in mysql so I don't thinks it's a permissions problem. Is my SQL correct? Cam On Mon, 2004-02-16 at 13:09, Bing Du wrote: Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1 -- -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Mailscanner thanks transtec Computers for their support. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ft_boolean_default
It would be good as spaces between words are taking OR as the default, i ave devised a system where the user selects the AND in the dropdown and if they select that is will add a + to every word. Hi! On Feb 13, Heath, Brad wrote: Is this a legitimate variable? It seems like it'd be useful, but when I tried it (on 4.0.12-nt and 4.0.17-nt) it didn't work. Anybody know what's going on? When it was added there was not possible to have a --server_variable that takes a string as a value. Now it's possible, but 4.0 is stable, so I cannot make ft_boolean_syntax a changeable variable there. But I just pushed this to 4.1. Should be in 4.1.2 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ 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: Newbie
O.k. I renamed oldreport_2002_12_08.MYD and oldreport_2002_12_08.MYI to tempo.MYI and tempo.MYD. Then I ran: CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; And now I have: $ls -lh -rw-rw 1 mysql mysql 12G Jun 20 2003 tempo.MYI -rw-rw 1 mysql mysql 8K Feb 16 15:37 oldreport_2002_12_08.frm -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm -rw-rw 1 mysql mysql 21G Jun 20 2003 tempo.MYD -rw-rw 1 mysql mysql 1K Feb 16 15:37 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 0B Feb 16 15:37 oldreport_2002_12_08.MYD Now I just have to get the data in tempo.MYI and tempo.MYD into the newly generated oldreport_2002_12_08.MYI and oldreport_2002_12_08.MYD Thanks for all your help, Cam -- On Mon, 2004-02-16 at 14:23, Bing Du wrote: Yes, that would create a new table (only the oldreport_2002_12_08.MYD file will be zero, the MYI and frm files are not) which has exactly the same definition of the table oldreports but without any data in it. So make sure you have a good copy of both oldreport_2002_12_08.MYD and oldreport_2002_12_08.MYI. Better rename them temporarily to something else and move them back after table 'oldreport_2002_12_08' is created. Bing Thanks for the response. The syntax: mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; wouldn't that just create an empty file? I need to get access to the data in oldreport_2002_12_08.MYD using oldreports.frm. $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm Cam On Mon, 2004-02-16 at 13:46, Simon Windsor wrote: Hi I am not familiar with the syntax mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; try mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports; to create populated copy or mysql CREATE TABLE oldreport_2002_12_08 as select * from oldreports where 1=2; to create an empty copy I hope this helps Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -Original Message- From: Cameron Ortis [mailto:[EMAIL PROTECTED] Sent: 16 February 2004 21:35 To: Bing Du Cc: [EMAIL PROTECTED] Subject: Re: Newbie Thanks for the quick reply! As root, I tried: mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) mysql CREATE TABLE oldreport_2002_12_08 LIKE oldreports; But I got the following error: ERROR 1: Can't create/write to file './research1data/oldreport_2002_12_08.frm' (Errcode: 13) I'm SU and root in mysql so I don't thinks it's a permissions problem. Is my SQL correct? Cam On Mon, 2004-02-16 at 13:09, Bing Du wrote: Have you tried creating a table called 'oldreport_2002_12_08' following the instructions on http://www.mysql.com/doc/en/CREATE_TABLE.html? Bing I have spent some time in the past with mysql but am far from an experienced database head. A while back I received a some mysql data for a research project. The files are: $ cd /usr/local/mysql/research1data $ ls -lh -rw-rw 1 mysql mysql 21G Jun 20 2003 oldreport_2002_12_08.MYD -rw-rw 1 mysql mysql 12G Jun 20 2003 oldreport_2002_12_08.MYI -rw-rw 1 mysql mysql 17G Jun 19 2003 oldreports.MYD -rw-rw 1 mysql mysql 10G Dec 5 19:32 oldreports.MYI -rw-rw 1 mysql mysql 8K Jun 20 2003 oldreports.frm In mysql I type: mysql show databases; +---+ | Database | +---+ | research1data | | mysql | +---+ mysql use research1data; mysql show tables; +-+ | Tables_in_research1data | +-+ | oldreports | +-+ 1 row in set (0.00 sec) I have fumbled around mailing lists, tutorials, and a reference manual or two. My newbie questions are: how do I load oldreport_2002_12_08 in addition to oldreports? Do I need to somehow create an oldreport_2002_12_08.frm file? I assumed that I would have been sent two .frm files but ... is there a way to use oldreports.frm for both? Many thanks for your help, Cam Additional info: OS = Freebsd 4.9 MySQL = 4.1.1
Re: Locking
Yes, it does. Both of the following statements give you the locks you'd expect: SELECT * FROM table WHERE id BETWEEN 4 AND 20 FOR UPDATE; SELECT * FROM table WHERE id BETWEEN 40 AND 50 LOCK IN SHARE MODE; Regards, Chris James Kelty wrote: Does it have exclusive and shared? -James -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 4:52 AM To: James Kelty Cc: [EMAIL PROTECTED] Subject: Re: Locking Hi! Next-key locking essentially doesn't work on rows - it works on indexes. It ensures that phantom reads can't happen. InnoDB does indeed do row-locking. In fact, it has one of the most efficient representations of locks of any relational database. Regards, Chris On Mon, 2004-02-16 at 23:43, James Kelty wrote: How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE INTO and CASCADEs.
Hi Heikki, everyone, Other than changing all my REPLACE INTO codes to UPDATE, does anyone have a quick solution to this? I have quite alot of code that makes use of what I thought REPLACE INTO was supposed to be doing... so was hoping that someone has managed to come up with some neat trick that can simulate the UPSERT. :) Thanking in advance. Regards, Tan Shao Yi - Original Message - From: Heikki Tuuri Date: February 16 2004 9:12am Subject: Re: REPLACE INTO and CASCADEs. Hi! The REPLACE + FOREIGN KEY behavior indeed changed between 4.0.17 - 4.0.18, because the old behavior was wrong, according to the MySQL manual: Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual states that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions 4.0.18 and 4.1.2, MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) I am sorry for the inconvenience. The old behavior was maybe more intuitive, if you think of REPLACE as an 'UPSERT' command. But in MySQL it is a 'DELSERT' command. Best regards, Heikki On Sun, 15 Feb 2004, Tan Shao Yi wrote: Hello, I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the behaviour of REPLACE INTO appears to have changed. I have two tables: table2 references table1 with ON DELETE CASCADE and ON UPDATE CASCADE on a primary key. Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the row in table1 gets replaced. No rows in table2 get deleted. Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 gets replaced, but this replacement gets cascaded into table2 and all rows in table2 with the primary key get deleted. Has anyone encountered something similar? I have only one box available so I am not able to re-test my observations again (I have reverted to 4.0.17). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrate from one MySQL table format to another
Where do I find information on how to move data from one table format to another? I have a phpnuke site that uses a certain Journal program, and we'd like to use a new Journal program. Unfortunately, the tables and resulting structure are not the same. And, since they don't use the same tables/structures, I am afraid I'd lose the information already added. Where do I find information on how to migrate that data from one format to another? Thanks eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate from one MySQL table format to another
I'm afraid this is a completly application-related question and not MySQL one. Migrating data from old table format to the new one should be done using a specially made software for that (PHP, Perl, etc etc). You should read all data from old tables and insert them in the new ones. If you're changing to a new version of the same software, there must be some 'upgrade' script available which will do that for you automatically. Sincerily, Leonardo Rodrigues - Original Message - From: Eric Wagar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 16, 2004 10:43 PM Subject: Migrate from one MySQL table format to another Where do I find information on how to move data from one table format to another? I have a phpnuke site that uses a certain Journal program, and we'd like to use a new Journal program. Unfortunately, the tables and resulting structure are not the same. And, since they don't use the same tables/structures, I am afraid I'd lose the information already added. Where do I find information on how to migrate that data from one format to another? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrate from one MySQL table format to another
Yes, it is a new version, but unfortunately, there is no 'upgrade' script, yet. I was just being a bit impatient. :) Hopefully, it will pay off to be patient. Thanks! eric -Original Message- From: Leonardo Rodrigues Magalhães [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 6:08 PM To: [EMAIL PROTECTED] Subject: Re: Migrate from one MySQL table format to another I'm afraid this is a completly application-related question and not MySQL one. Migrating data from old table format to the new one should be done using a specially made software for that (PHP, Perl, etc etc). You should read all data from old tables and insert them in the new ones. If you're changing to a new version of the same software, there must be some 'upgrade' script available which will do that for you automatically. Sincerily, Leonardo Rodrigues - Original Message - From: Eric Wagar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 16, 2004 10:43 PM Subject: Migrate from one MySQL table format to another Where do I find information on how to move data from one table format to another? I have a phpnuke site that uses a certain Journal program, and we'd like to use a new Journal program. Unfortunately, the tables and resulting structure are not the same. And, since they don't use the same tables/structures, I am afraid I'd lose the information already added. Where do I find information on how to migrate that data from one format to another? -- 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]
mysqlcc syntax file?
Hi there. I am trying to create a table using the mysql control center but i am taking the error. Unable to locate syntax.txt Please locate the file. Any sugestion? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calling a procedure with parameters
Hi, If this possible in MYSQL (capability of procedure with parameterized parameters). Assume a procedure with parameters a1,a2,a3 test(a1,a2,a3) begin . . end Can I call the procedure test by following way call test(a2 = 5, a1 = 4, a3 = 7); i.e changing the order of the parameters and evaluating the values to the parameters. Thanks, Ram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to another server
Is it possible to query one server from another even if those servers are in diferent machines at diferent locations? for example: select * from localtable left join remoteserver.remotedatabase.remotetable where localtable.key = remoteserver.remotedatabase.remotetable.key FBR
Re: Query to another server
On Mon, Feb 16, 2004 at 11:54:43PM -0700, [EMAIL PROTECTED] wrote: Is it possible to query one server from another even if those servers are in diferent machines at diferent locations? for example: select * from localtable left join remoteserver.remotedatabase.remotetable where localtable.key = remoteserver.remotedatabase.remotetable.key No. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 156 days, processed 1,835,266,782 queries (135/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to another server
I dont think so :( Karam --- [EMAIL PROTECTED] wrote: Is it possible to query one server from another even if those servers are in diferent machines at diferent locations? for example: select * from localtable left join remoteserver.remotedatabase.remotetable where localtable.key = remoteserver.remotedatabase.remotetable.key FBR __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Question About Replication
On Mon, Feb 16, 2004 at 02:41:30PM -0300, Ronan Lucio wrote: Hi All, A short question: If I have a slave MySQL Server (updating via logs). Supposing the master MySQL (for any reason) goes down. The users start using (and updating) the slave database. When the master MySQL goes up. Does it will update it´s data from the slave database? No. Replication in MySQL is one-way. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 156 days, processed 1,835,286,410 queries (135/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to another server
you'll need to create temporary tables in one of the servers based on the results of the other and then join. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 2:54 PM Subject: Query to another server Is it possible to query one server from another even if those servers are in diferent machines at diferent locations? for example: select * from localtable left join remoteserver.remotedatabase.remotetable where localtable.key = remoteserver.remotedatabase.remotetable.key FBR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to another server
You might be able to cheat and replicate the required database to the local machine. Regards, Chris Terence wrote: you'll need to create temporary tables in one of the servers based on the results of the other and then join. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 2:54 PM Subject: Query to another server Is it possible to query one server from another even if those servers are in diferent machines at diferent locations? for example: select * from localtable left join remoteserver.remotedatabase.remotetable where localtable.key = remoteserver.remotedatabase.remotetable.key FBR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling a procedure with parameters
Prasad Budim Ram [EMAIL PROTECTED] wrote: Hi, If this possible in MYSQL (capability of procedure with parameterized parameters). Assume a procedure with parameters a1,a2,a3 test(a1,a2,a3) begin . . end Can I call the procedure test by following way call test(a2 = 5, a1 = 4, a3 = 7); i.e changing the order of the parameters and evaluating the values to the parameters. No, you can't. -- 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]
Stored Function with many parameters?
To make it simple, is it possible to make function having many undecided parameters with MySQL 5.0 stored procedure/function? For example, add(3) add(3,5) add(3,5,1,4,9) add(3,5,1,4,9,8,6,7,2) In C, we use pointer to handle this, but I don't know how to code this at stored function CREATE FUNCTION statement. I looked at the document, but it didn't say about many undecided parameters. Someone help me please. __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
