Index selection problem
Hi, I have a table orders with the columns item_id INT FK items(id) customer_id INT FK customers(id) status_id TINYINT -- Between 1 and 4 always ordered_at DATETIME delivered_at DATETIME There are indexes: index_a: (item_id, customer_id, status_id) index_b: (item_id, status_id, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index selection problem
On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: MySQL is unable to use your index when you use IN and/or OR on yoru column. Is this really true? I'm reading High Performance MySQL 2nd ed. these days and specifically got the impression that using IN will allow usage of the index. The below quote is from the book, and the multiple equality condition refers to an IN (...) expression. ... we draw a distinction between ranges of values and multiple equality conditions.The second query is a multiple equality condition, in our terminology. We’re not just being picky: these two kinds of index accesses perform differently. The range condition makes MySQL ignore any further columns in the index, but the multiple equality condition doesn’t have that limitation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index selection problem
The other index does have a way higher cardinality, but the query is for 3 columns all of which are in the first index. I guess this is just one of the situations where MySQL makes a wrong assessment. On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote: Try doing a SHOW INDEX FROM orders and look at the cardinality column. These are the stats MySQL uses to determine which index to use. Sometimes they aren't always update properly and you may need to run ANALYZE on the table. But, you can also tell MySQL to use the index you want. SELECT * FROM orders USE INDEX (index_a) WHERE ... Brent Baisley On Tue, Jul 21, 2009 at 5:52 AM, Mortenmy.li...@mac.com wrote: Hi, I have a table orders with the columns item_id INT FK items(id) customer_id INT FK customers(id) status_id TINYINT -- Between 1 and 4 always ordered_at DATETIME delivered_at DATETIME There are indexes: index_a: (item_id, customer_id, status_id) index_b: (item_id, status_id, ordered_at, delivered_at) Given this query: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id IN (1,2) Then the key chosen is index_b. Same happens if I use (status_id = 1 OR status_id = 2). If I only check against one status_id, then the correct index_a gets picked with ref const,const,const. I'm not even doing a range scan on status_id and even if I were, it's the last column in index_a. Since ordered_at and delivered_at are both dates then index_b will have a very high selectivity. In reality, index_b may make little sense, but I still don't understand why MySQL would ever pick that when 3 columns in the query can use the covering index_a Can anyone give me some input on how to make sense of this? Thanks, Morten select count(*) from orders where item_id = 9602 - 4534 records select count(*) from orders where item_id = 9602 and status_id IN (1,2) - 4181 records select count(*) from orders where item_id = 9602 and customer_id = 5531 - 1226 records select count(*) from orders where item_id = 9602 and customer_id = 5531 and status_id IN (1,2) - 1174 records -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
I've been reading High Performance MySQL today and got some great tips from that which will help a lot. Yes it is a good book. I hope you have the 2nd edition. I do, I should have read this years ago (well.. the 1st edition then at least). So many caveats to using indexes. So why not have 2 tables: Cases_Active for the cases currently open, and Cases_Closed. Reporting across open and closed, but as you state I could be using UNION for this. Reporting is not expected to be fast any way. You can use a Memory table but they don't work with Merge tables. I would only consider this if the table gets updates every second or two and that flushes the cache. I'm not sure how much money you're willing to throw at this project, but I know of some additional hardware that can squeeze out more speed. Well.. I could just throw some more RAM at it. But ideally, I would have a sound setup first before considering mindlessly adding resources (however tempting it is). Thanks for your tips. I'll be looking further into splitting the tables. Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need advice on a good setup for generic queries
Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need advice on a good setup for generic queries
Mike, you're right - sorry. I've been reading High Performance MySQL today and got some great tips from that which will help a lot. I think the fundamental challenge now, is that the table contains a lot of timestamps, and querying against these involves multiple range queries which makes indexing hard. The actions table has the following columns (of relevance to the example): status_id assignee_id company_id created_at assigned_at opened_at updated_at verified_at due_at solved_at closed_at Queries could be: Show all actions which are assigned to Tom, were created in October and solved in November Show all open actions which were opened before August, do not have an assignee and were verified last week These queries which involve easily indexable fields (status_id, assignee_id, company_id) and multiple conditions on different ranges are what's difficult. The table is about 2.500.000 records and grows at a daily rate of about 50.000 records (that number is growing though). Once an action has been closed, it gets status closed and is no longer of interest. 70% of the records in the table will be status closed. I think what I'm looking for now, is some way to encode the different date values into a single column which can be indexed and the value of which gets calculated and updated by a background job. This will cost some precision, but I hope that can be done. Otherwise I'm back to considering alternative index/query-mechanisms. Does my problem make a little more sense now? Thanks. Morten Let's say I would like to see all actions that were created in october and solved in november. On Jul 12, 2009, at 3:54 PM, mos wrote: Morten, Perhaps you could also add how many rows are in the table, how many rows are added each day, what are the column types, and what do the search queries look like? Mike At 11:39 AM 7/12/2009, Morten wrote: Hi, I'm working on a table that has about 12 columns against which arbitrary queries must perform really well. Currently there are a lot of indexes on the table, but I'm hitting some problems - and adding more indexes seems a slippery slope (there are ~15 multi-column indexes, I'd like that reduced). So I'm looking for a way out and I'm currently considering: * Building a memory table on top of the existing table * Sphinx indexing and then throw the queries against Sphinx instead * Using a different in-memory-DB like Tokyo Cabinet for the queries * Building a series of reporting tables which each handle a subset of the supported queries All of the solutions would maintain the current table for consistency and it's acceptable with a couple of minutes lag. I'm tempted to go for the memory table and update that depending on which rows have been updated in the parent table since last update. Eliminating duplicates could be a challenge, unless I build a new table for each update and then rename the tables - but that's costly in terms of memory. What do people usually do in this situation? Any other solutions to consider? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my.li...@mac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: The size of an index (INDEX_LENGTH)
Hi, It's InnoDB on 5.0.51. The only thing I can think of that *may* be different about this is that this index used to be on a composite key (some_id, some_varchar) but then the VARCHAR column got dropped. Other than that, it's just a plain index on an INT(11). Morten On Jun 16, 2009, at 5:51 AM, Andrew Braithwaite wrote: Hi, Is your table MyISAM or InnoDB? Andrew -Original Message- From: Morten [mailto:my.li...@mac.com] Sent: 15 June 2009 21:23 To: mysql@lists.mysql.com Subject: The size of an index (INDEX_LENGTH) Hi, I dropped an index on a table with 25M records today. The INDEX_LENGTH in information_schema.tables shrank from 3834642432 to 3215982592, ie. ~618Mb difference The index was on an int(11) column. That means each index key takes up ~618Mb/25M ~= 25 bytes but that doesn't sound right? Is that true, or is information_schema.tables unreliable or? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
The size of an index (INDEX_LENGTH)
Hi, I dropped an index on a table with 25M records today. The INDEX_LENGTH in information_schema.tables shrank from 3834642432 to 3215982592, ie. ~618Mb difference The index was on an int(11) column. That means each index key takes up ~618Mb/25M ~= 25 bytes but that doesn't sound right? Is that true, or is information_schema.tables unreliable or? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql support materialized views?
On Apr 20, 2009, at 11:34 AM, Olexandr Melnyk wrote: No, you will have to emulate them using triggers or stored procedures. Or: 1. Create table this_is_not_a_view and use that as the materialized view 2. Build a new version in the background this_is_not_a_view_pending as per your data freshness requirements 3. Use RENAME TABLE to push the pending table to production so to speak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Speeding up character set conversion
Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Separate customer databases vs all in one
On Mar 18, 2009, at 11:47 AM, Johnny Withers wrote: I have an application and database doing this right now. We run both ways, a single db per customer and a single customer per db. Smaller customers are in a shared enviroment and larger customers have their own db, sometimes they even have their own server. We use a company_id field in each table to identify the data. How do you go about table changes and such - run a script that connects to each DB or something more fancy than that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
I certainly appreciate this discussion and the clear opinions of Mike. Somehow it turned my somewhat simple question into something much more interesting. I believe in referential integrity, I also believe that enforcing a fully saturated data model like Mike proposes, is a constraint on data modeling that is not acceptable for me. Orphans exist in my (data modeling) world, and arguing about the validity of that will probably just end up in discussing contrived examples. Anyway, thanks for sharing your opinions Mike. Oracle could have used you when the implemented the empty string... :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
The = operator
Hi, I have a query where I want to retrieve all the people who are not in a specific group. Like so: SELECT id, name FROM people WHERE group_id != 3; Since people may not be in a group at all, I also need to test if the column is NULL: SELECT id, name FROM people WHERE group_id != 3 OR group_id IS NULL; Running that through EXPLAIN things look fine, but if I instead use: SELECT id, name FROM people WHERE NOT group_id = 3; I get a full table scan. Is that because is = equivalent to a function in a sense? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SQL_NO_CACHE
Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query cache, but judging from the below it doesn't. What can I do to avoid the query cache? Thanks. Morten mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (7.22 sec) mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.43 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
Thanks for all the suggestions. The caching must be done somewhere else. There is no index on the column and there are about 500.000 rows in the table. A MySQL restart doesn't flush the cache in play, but a full restart of my laptop does (OS X). I may be chasing the wrong problem, but we have seen a query take a lot of time on a production machine and have not been able to pin point why, as the EXPLAIN looks good and the query is responsive enough when I run it manually. I was just trying to reproduce that. The below is after a full restart: mysql SET SESSION query_cache_type=off; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 456 = 456; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (28.80 sec) mysql RESET QUERY CACHE; Query OK, 0 rows affected (0.00 sec) mysql select SQL_NO_CACHE count(*) from users where email = 'hello' AND 789 = 789; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.44 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
On Mar 4, 2009, at 8:38 PM, Jocelyn Fournier wrote: Just curious : if there's no index on the column why don't you try to add one ? That's probably why it takes a lot of time on the production machine. Hehe.. I can understand why you ask, I over simplified the question which was wrong of me. I'm just using this specific column because I was playing around while trying to figure out why the first query was slow and the subsequent snappy. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Cost of using over dimensioned columns
Hi list, I'm wondering what the cost (if any) is by using eg. mediumtext over text, or largetext over mediumtext. Is there a non-negligible reason to not just just largetext? And how about varchars, what's the cost of using varchar(1000) rather than varchar(20)? My intuition tells me that I should keep things as tight as possible, but I may be narrow minded and need a sanity check. Thanks! Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Result ordering
Hi, I'm retrieving the name of some records where either one of two criteria are satisfied: SELECT name FROM foo WHERE bar = 34 OR baz 100 ORDER BY baz DESC LIMIT 5; I would like to sort that result set, such that the records matching bar = 34 occur before records with baz 100. I could do this using a CASE: SELECT name, (CASE WHEN bar = 34 THEN 0 ELSE 1 END) AS rank FROM foo WHERE bar = 34 OR baz 100 ORDER BY rank ASC, baz DESC LIMIT 5; But I need the name DISTINCT, how can I accomplish this? I could wrap the above select in a SELECT DISTINCT name FROM (SELECT ... ) but is there a nicer way to use the rank than including it in the result set? Thanks! Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Result ordering
On Nov 30, 2008, at 11:42 PM, Andy Shellam wrote: Hi Morten, I think this is valid in MySQL (it certainly is for SQL Server) but you can use a CASE statement directly in the ORDER BY clause. Try something like this: SELECT name FROM foo WHERE bar = 34 OR baz 100 ORDER BY CASE bar WHEN 34 THEN 0 ELSE 1 END ASC, baz DESC LIMIT 5; Indeed! Thanks! I also found that I can simplify it a little by replacing CASE .. END with IF(condition, 0, 1). Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEXP vs LIKE/OR
Hi, I want to retrieve all records where the field value contains either foo, bar or baz. Like so: SELECT id FROM table WHERE value LIKE '%foo%' OR value LIKE '%bar%' OR value LIKE '%baz%'; But then I stumbled upon REGEXP, and can do the same this way: SELECT id FROM table WHERE value REGEXP 'foo|bar|baz' != 0; Any opinions on what's the better approach and why? Thanks Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Picking the better query (join vs subselect)
Hi guys, I have 2 tables cars and parts where car has many parts. I need a query to return some fields from the cars table as well as a field from multiple parts records. I've come to the following approaches, and would like to understand which is the better, and why, or if there's a 3rd and even better approach: Approach 1 - subselects: SELECT cars.id, (SELECT parts.value FROM parts WHERE car_id = cars.id AND id = 3) AS part_3, (SELECT parts.value FROM parts WHERE car_id = cars.id AND id = 4) AS part_4 FROM cars WHERE id = 2; Approach 2 - joins: SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4 FROM cars INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id WHERE parts_3.id = 3 AND parts_4.id = 4 AND cars.id = 2; I've tried to find out if joins are preferred over subselects, but am not able to come to a definite conclusion. I read that correlated subselects are bad, and I should go for the join, but I know the id of the record in the outer query and can hard code that into the subselect (if that makes a difference). The EXPLAIN result only differs in that the select_type is SIMPLE in the JOIN approach, but PRIMARY/DEPENDENT SUBQUERY in the subselect approach. Any tips much appreciated, the full example below. Br, Morten CREATE TABLE cars ( id integer, make varchar(32) ); CREATE TABLE parts ( id integer, car_id integer, value varchar(64) ); INSERT INTO cars (id, make) VALUES (1, 'Ford'); INSERT INTO cars (id, make) VALUES (2, 'Honda'); INSERT INTO parts (id, car_id, value) VALUES (1, 1, 'Wheel'); INSERT INTO parts (id, car_id, value) VALUES (2, 1, 'Tire'); INSERT INTO parts (id, car_id, value) VALUES (3, 2, 'Wheel'); INSERT INTO parts (id, car_id, value) VALUES (4, 2, 'Tire'); INSERT INTO parts (id, car_id, value) VALUES (5, 2, 'Dice'); SELECT cars.id, (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 3) AS part_3, (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 4) AS part_4 FROM cars WHERE id = 2; SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4 FROM cars INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id WHERE parts_3.id = 3 AND parts_4.id = 4 AND cars.id = 2; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rewriting query to avoid inline view
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
Any tips greatly appreciated, thanks.
Morten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
Thanks Rob and Baron, I'd never heard of the integers table approach before, really good stuff! First off your porting over or dealing with formerly oracle code, right? Nah, I just learned SQL on Oracle back in the day. DUAL works under MySQL also - don't know since what revision, but it works on 5.0.45 at least - but seeing that I don't need it, I'll stop using it, thanks for the tip! Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left outer join trouble
Hi, I'm trying to write a query which returns a single record which contains concatenated values for referencing records: SELECT tickets.id AS id, CAST(GROUP_CONCAT(tags.name SEPARATOR ' ') AS CHAR) AS tags, CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text FROM tickets LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id LEFT OUTER JOIN taggings AS taggings ON taggings.taggable_id = tickets.id LEFT OUTER JOIN tags AS tags ON taggings.tag_id = tags.id GROUP BY id; The problem with this query is, that it returns too many matches in the concatenated fields when more than one concatenation is used. Ie. given the data: CREATE TABLE tickets (id integer); CREATE TABLE events (ticket_id integer, value varchar(32)); CREATE TABLE tags (id integer, name varchar(32)); CREATE TABLE taggings (taggable_id integer, tag_id integer); INSERT INTO tickets VALUES (1); INSERT INTO events VALUES (1, 'Event A'); INSERT INTO events VALUES (1, 'Event B'); INSERT INTO events VALUES (1, 'Event C'); INSERT INTO tags VALUES (1, 'Tag A'); INSERT INTO tags VALUES (2, 'Tag B'); INSERT INTO taggings VALUES (1, 1); INSERT INTO taggings VALUES (1, 2); The query returns duplicates: +--+-+-+ | id | tags| text | +--+-+-+ |1 | Tag A Tag B Tag A Tag B Tag A Tag B | Event A Event A Event B Event B Event C Event C | +--+-+-+ I suspect this has to do with the multiple GROUP_CONCATs as it works fine when using only a single GROUP_CONCAT: mysql SELECT tickets.id AS id, -CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text - FROM tickets - LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id - GROUP BY id - ; +--+-+ | id | text| +--+-+ |1 | Event A Event B Event C | +--+-+ I'm tempted to solve this using a view or two, but would like to know if there's a better way. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join trouble
Baron Schwartz wrote: I'm tempted to solve this using a view or two, but would like to know if there's a better way. GROUP_CONCAT() takes an optional DISTINCT modifier, and that might do what you're looking for. It sure does the trick. I'll use that, I was afraid that I was missing something fundamental in the joins. Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join trouble
Martin Gainty wrote: Good Morning- Good afternoon :-) http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by- extension/ I did'nt see your where clause ? I'm probably missing your point here. But there's no where clause because I want all records from the tickets table returned, and I do the joins using outer left joins. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficient use of inline virtual views
Hi, I have the below setup: --A man can have multiple cars CREATE TABLE man (id INTEGER, name VARCHAR(64)); CREATE TABLE car (id INTEGER, owner_id INTEGER, make VARCHAR(64)); --Add data INSERT INTO man VALUES (1, 'John'); INSERT INTO car VALUES (1, 1, 'Mazda'); INSERT INTO car VALUES (2, 1, 'Ford'); INSERT INTO car VALUES (3, 1, 'Toyota'); --I want a single record for the result, containing owner name and --the makes of all the cars he owns SELECT man.name, virtual_cars.makes FROM man, (SELECT owner_id, GROUP_CONCAT(car.make) AS makes FROM car GROUP BY owner_id) AS virtual_cars WHERE man.id = virtual_cars.owner_id Now, I'm concerned that MySQL will actually build the entire virtual_cars view which will be quite expensive, so I'd like to hear if it's somehow possible to join on man.id within the SELECT declaring the view? Any tips for improving the query are much appreciated, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky query
Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Peter Brawley wrote: Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) Would you be looking for ... SELECT DISTINCT key FROM keyval k1 JOIN keyval k2 USING (key) WHERE k1.val IN(8,9,10); The tricky part is that there must be a record for EACH of the values (8, 9, 10) and not just any one value (as IN requires). Your proposal will return 1,2,3,4 and not just 1,4. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) HAVING c=3; Clever! Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication
Hi on a slave, is it possible to only stop the SQL Thread? (Slave_SQL_Running: Yes/No) i need to do some testing with my script and i got to make sure it reads that value correctly. regards Morten
SV: replication
that would make both Slave_IO_Running: Yes Slave_SQL_Running: Yes go to No i only want to stop the Slave_SQL_Running and keep the Slave_IO running so my show slave status would report the following: Slave_IO_Running: Yes Slave_SQL_Running: No it happens if the mysql recives an update and failes during the update. ive tried reading the documentation about mysql replication, but i cannot find what i am looking for. Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: on 25-01-2006 22:56 Til: Morten Kallesøe Cc: mysql@lists.mysql.com Emne: Re: replication SLAVE STOP; or STOP SLAVE; work equally well. Or am I not understanding your question correctly? -Sheeri On 1/25/06, Morten Kallesøe [EMAIL PROTECTED] wrote: Hi on a slave, is it possible to only stop the SQL Thread? (Slave_SQL_Running: Yes/No) i need to do some testing with my script and i got to make sure it reads that value correctly. regards Morten
Re: convert varchar/char to integer
morten bjoernsvik [EMAIL PROTECTED] skrev: Hi Gents Is it possible to sort char/varchars that only contains integers as integers and not as characters. I can't use integer because the standard says it may contain characters aswell. Currently I do this outside mysql in a perl-procedure. I found the answer to left pad the numbers so they all had the same amount of characters: mysql select LPAD(card_acceptor_id_42,12,'00') from merchant order by LPAD(card_acceptor_id_42,12,'0') limit 10; +---+ | LPAD(card_acceptor_id_42,12,'00') | +---+ | 0026 | | 0042 | | 0083 | | 0273 | | 0307 | | 0331 | | 0448 | | 0620 | | 0620 | | 0794 | +---+ 10 rows in set (0.01 sec)
convert varchar/char to integer
Hi Gents Is it possible to sort char/varchars that only contains integers as integers and not as characters. I can't use integer because the standard says it may contain characters aswell. Currently I do this outside mysql in a perl-procedure. Whatever I try I get this order: | 2000651 | | 2000701 | | 20008 | | 2001956 | | 2003077 | | 2003176 | | 2003788 | | 2003903 | | 20040 | | 2004281 | | 2004380 | | 2004414 | | 2004489 | | 2004547 | | 200543 | Thanks Morten Bjørnsvik, Oslo, Norway
Charset problems (utf-8 - latin1)
Hi, I'm trying to migrate an old system to a newer - while doing that I have to move the database. The old database i UTF-8 encoded, and the new one is Latin-1. To move the data I'm doing something similar to: INSERT INTO new_db.table (new_db.field) SELECT CONVERT(old_db.field USING latin1) FROM old_db.table; When doing a SELECT CHARSET(field) FROM new_db.table; It returns latin1, so everthings seems to be A-Okay - but since I'm posting this message you could prolly guess that it didn't - and right, it failed... Using SSH to the mysql-server, and using `mysql` it always returns the data as UTF-8 (when setting the encoding to anything else it trashes the special-chars). I don't know if this is normal - I might be, so I continued my work on the migration. I then reloaded the webpage (yes, it's for a PHP driven CMS), and no - the problem still existed. All data was returned as UTF-8 (when running a utf8_decode() in PHP on the db-output the output was corrected and the speciel-chars showed up. But - this isn't normal. Normally you can just enter the data as latin1, and display the output on the webpage (assuming that content-type has a charset og iso-8859-1). And this works everywhere else on the website. SO, my question is: How the heck do you actually transfer data from the utf8-table to a latin1-table and properly re-encoding the text so that the latin1-table wouldn't contain raw utf8-data (because that my friends - sucks) Below are som excerps from SHOW VARIABLES - tell if your wan't more info... just included a few of the lines: +-+---+ | Variable_name | Value| +-+---+ | back_log| 50| | basedir | /pack/mysql-4.1.13/| | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608| | character_set_client| latin1| | character_set_connection| latin1| | character_set_database | latin1| | character_set_results | latin1| | character_set_server| latin1| | character_set_system| utf8| | version | 4.1.13-log| +-+---+ Regards Morten Fangel // fangel
SV: show master/slave status privileges ?
Alan Williamson wrote: I have made a user with the following command: GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r' Silly question Morten, and I am sure you have probably done it, but you are definitely running: % mysql FLUSH PRIVILEGES; You only need to FLUSH PRIVILEGES if you directly edit the mysql db tables. It is not needed with GRANT, REVOKE, or SET PASSWORD. See the manual for the details http://dev.mysql.com/doc/mysql/en/privilege-changes.html. Michael Strange, because it work after i did a FLUSH PRIVILEGES. thank you alan. Morten
show master/slave status privileges ?
Hi I have been trying to get my mysql server to do as i want, but its not quite playing along. I need to monitor the replication status from a remote server, first by hand, 2nd by a perl script. But i keep getting this darn message. mysql show master status; ERROR 1227: Access denied. You need the SUPER,REPLICATION CLIENT privilege for this operation I have made a user with the following command: GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r' Shouldnt that be enough? Or am i just missing something, I have also tried to make the user with: GRANT SUPER,REPLICATION CLIENT TO 'test'@'123.123.123.123' IDENTIFIED BY 'h4x0r'; No luck there ethier. Regards Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alias not allowed in WHERE clause?
Well, it might not be SQL standard, but most databases out there allow you to use the alias in your where clauses. It helps make the sql more readable, and it shouldn't be that hard to add this feature to the parser, so it can translate that alias back to the original row-source selection, during parse time. /morten Paul DuBois wrote: At 11:32 -0700 10/11/04, Nathan Clark wrote: SELECT city, state, SQRT( POWER( ( latitude - 39.039200 ), 2 ) + POWER( ( longitude + 95.689508 ), 2 ) ) AS distance FROM geographics WHERE distance 1 ORDER BY distance LIMIT 10; Returns: #1054 - Unknown column 'distance' in 'where clause' Are alias not allowed in WHERE clauses? How could they be? The WHERE clause determines which rows to select. Aliases are defined for columns from the rows that have been selected. I am able to replace the alias with the entire math function, and it works as desired. However, I do not like the heaviness/repetiveness of the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Where clause question]
Sorry, mailed it in html format. Read answer below Original Message Subject:Re: Where clause question Date: Tue, 12 Oct 2004 00:00:12 +0200 From: Morten Egan [EMAIL PROTECTED] To: Ed Lazor [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Not knowing how the inards of mysql works, I would say the having clause is usually slow, and if done correctly you actually dont execute the calculation twice, because the parser should recognize this as beeing the same as what you've specified in your select part. /morten Ed Lazor wrote: Interesting. I thought you could sort by aliases. Thanks Shawn. The easy answer was to just add the calculation to the where section as well. But which approach is faster - having or the calculation? Ie. select ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount from products where round( ( (MSRP - Price) / MSRP) * 100) 10 - OR - select ... HAVING discount 10 ? -Original Message- Great explanation. By the way, Ed, what you might be looking for is the HAVING clause, which culls records right before the LIMIT is applied. SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` HAVING discount 10 LIMIT 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie installation question
sorry for spamming the list with this, i tried asking on the forums, but didn't receive a reply. i'm a complete newbie to mysql/php, but have programmed professionally (in C/C++/ASM) for about 9 years now. i originally installed mysql into C:/Program Files/mysql, before deleting and reinstalling in the default subdirectory, which meant some paths in the registry had been setup incorrectly. i changed these manually (2 of them located), and this fixed a few issues. however after reinstalling, when i do a mysqlshow, it shows only test in the list of available databases, and nothing i seem to change makes a difference. i edited the my.ini to the base+base/data directories, but that didn't change anything. i even tried changing the paths in my.ini to invalid paths, and that didn't change anything either (it still located test, and test only). i searched for a test subdirectory elsewhere on my hard drive, but failed to locate one. i then created a new database, which it did in the correct subdirectory, so since it seems to be writing to the correct subdirectory, why on earth doesn't it read from the directory as well? i have administrator access to the machine. any help is much appreciated, cheers.
C API
Hey, I'm currently working on a mini interface to mysql. I've been browsing through your online documentation as well as mailing list archives but I havent been able to find an answer there. I was wondering if there is a way to just return a 'string' or data structure of the result so I get output much like you do with mysqlclient. That will make it so much easier to grab data and make it look nice and neat. Thanks. -- Morten Boenloekke Post * mortens (at) box (dot) sk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ON UPDATE CASCADE
Hello,
If I change the value of a reference a ,
for instance by means of updating or inserting values,
I'd expect both updated values and inserted values to
cascade, hence to change b,
where
FOREIGN KEY (b) REFERENCES A(a)
ON UPDATE CASCADE
In this example foreign key and reference
are taken from one and the same table,
that should be possible,
please help me , what is wrong ?
My code woun't cascade anything.
Yours Sincerely
Morten Gulbrandsen
===
--
DROP TABLE IF EXISTS A
--
Query OK, 0 rows affected (0.04 sec)
--
CREATE TABLE A
(
a CHAR(9) NOT NULL,
b CHAR(9),
c INT NOT NULL DEFAULT 1,
PRIMARY KEY (a),
INDEX (b, c)
)ENGINE = INNODB
--
Query OK, 0 rows affected (0.05 sec)
--
SHOW WARNINGS
--
Empty set (0.00 sec)
--
DESCRIBE A
--
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| a | char(9) | | PRI | | |
| b | char(9) | YES | MUL | NULL| |
| c | int(11) | | | 1 | |
+---+-+--+-+-+---+
3 rows in set (0.01 sec)
--
ALTER TABLE A
ADD FOREIGN KEY (b) REFERENCES A(a)
ON DELETE SET NULL
ON UPDATE CASCADE
--
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
--
SHOW WARNINGS
--
Empty set (0.00 sec)
--
DESCRIBE A
--
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| a | char(9) | | PRI | | |
| b | char(9) | YES | MUL | NULL| |
| c | int(11) | | | 1 | |
+---+-+--+-+-+---+
3 rows in set (0.00 sec)
--
SELECT * FROM A
--
Empty set (0.00 sec)
--
INSERT INTO A (a) VALUES ('a0')
--
Query OK, 1 row affected (0.05 sec)
--
INSERT INTO A (a) VALUES ('a1')
--
Query OK, 1 row affected (0.02 sec)
--
SELECT * FROM A
--
++--+---+
| a | b| c |
++--+---+
| a0 | NULL | 1 |
| a1 | NULL | 1 |
++--+---+
2 rows in set (0.02 sec)
--
UPDATE A SET a='updated' WHERE a='a0'
--
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--
SELECT * FROM A
--
+-+--+---+
| a | b| c |
+-+--+---+
| a1 | NULL | 1 |
| updated | NULL | 1 |
+-+--+---+
2 rows in set (0.02 sec)
Bye
Re: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
Thank you, I am comparing the two \mysql\data\mysql directories, finding that many files are simply missing in my installation. I had another mysql version previously, which I removed, I also have a different operating system. win2k Pasta de c:\mysql\data\mysql 07/01/2004 11:39DIR . 07/01/2004 11:39DIR .. 22/12/2003 21:03 8.820 columns_priv.frm 22/12/2003 21:03 0 columns_priv.MYD 22/12/2003 21:03 1.024 columns_priv.MYI 22/12/2003 21:03 9.178 db.frm 22/12/2003 21:03 306 db.MYD 22/12/2003 21:03 3.072 db.MYI 22/12/2003 21:03 8.665 func.frm 22/12/2003 21:03 0 func.MYD 22/12/2003 21:03 1.024 func.MYI 22/12/2003 21:03 8.700 help_category.frm 22/12/2003 21:0320 help_category.MYD 22/12/2003 21:03 3.072 help_category.MYI 22/12/2003 21:03 8.612 help_keyword.frm 22/12/2003 21:03 0 help_keyword.MYD 22/12/2003 21:03 1.024 help_keyword.MYI 22/12/2003 21:03 8.630 help_relation.frm 22/12/2003 21:03 0 help_relation.MYD 22/12/2003 21:03 1.024 help_relation.MYI 22/12/2003 21:03 8.770 help_topic.frm 22/12/2003 21:03 0 help_topic.MYD 22/12/2003 21:03 1.024 help_topic.MYI 22/12/2003 21:03 9.148 host.frm 22/12/2003 21:03 0 host.MYD 22/12/2003 21:03 1.024 host.MYI 22/12/2003 21:03 9.470 proc.frm 07/01/2004 13:33 156 proc.MYD 07/01/2004 13:33 2.048 proc.MYI 22/12/2003 21:03 8.925 tables_priv.frm 22/12/2003 21:03 0 tables_priv.MYD 22/12/2003 21:03 1.024 tables_priv.MYI 22/12/2003 21:03 9.992 user.frm 22/12/2003 21:03 160 user.MYD 22/12/2003 21:03 2.048 user.MYI 33 arquivo(s)116.960 bytes Here is my \data\mysql\ directory Directory of I:\mysql\data\mysql 05/01/2004 20:44 DIR . 05/01/2004 20:44 DIR .. 22/12/2003 21:038,820 columns_priv.frm 22/12/2003 21:030 columns_priv.MYD 22/12/2003 21:031,024 columns_priv.MYI 22/12/2003 21:039,178 db.frm 22/12/2003 21:03 306 db.MYD 22/12/2003 21:033,072 db.MYI 22/12/2003 21:038,665 func.frm 22/12/2003 21:030 func.MYD 22/12/2003 21:031,024 func.MYI ## no help_category keyword relation topic 22/12/2003 21:039,148 host.frm 22/12/2003 21:030 host.MYD 22/12/2003 21:031,024 host.MYI ## no proc.form .MYD .MYI 22/12/2003 21:038,925 tables_priv.frm 22/12/2003 21:030 tables_priv.MYD 22/12/2003 21:031,024 tables_priv.MYI 22/12/2003 21:039,992 user.frm 08/01/2004 04:27 576 user.MYD 08/01/2004 04:542,048 user.MYI 18 File(s) 64,826 bytes 2 Dir(s) 3,904,598,016 bytes free Obviously some files are missing, what can I do, install again ? I could unzip the mysql-5.0.0-alpha-win.zip without a problem, and mysql actually runs, under windows 2000, Perhaps another version of MySQL 5.0 is required for win 2k ? Has anyone tried mysql-5.0.0-alpha under win2k ? Please ? Yours Sincerely Morten Gulbrandsen - Original Message - From: miguel solorzano [EMAIL PROTECTED] To: Morten Gulbrandsen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 6:42 AM Subject: Re: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist At 05:26 8/1/2004 +0100, Morten Gulbrandsen wrote: Hi, I wasn't able to repeat: Microsoft Windows XP [versão 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. c:\cd\mysql\bin C:\mysql\binmysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.0-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE A ( - SSN CHAR(9) NOT NULL, - SUPERSSN CHAR(9), - DNO INT NOT NULL DEFAULT 1, - PRIMARY KEY (SSN), - INDEX (SUPERSSN), - INDEX (DNO) - )TYPE = INNODB; Query OK, 0 rows affected, 1 warning (0.11 sec) mysql DESCRIBE A; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | SSN | char(9) | | PRI | | | | SUPERSSN | char(9) | YES | MUL | NULL| | | DNO | int(11) | | MUL | 1 | | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql ALTER TABLE A - ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN
Re: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
Miguel Angel Solórzano MySQL 5.0 should install and run on any Win32 OS, then I guess that some installation's steps/options has failed for to add the missed files. Morten Gulbrandsen Interesting, obviously something is missing, anyway 5.0.0.a is present. I could try that, Miguel Angel Solórzano It is important for us to know what exactly is failing, my guess is that you tried to install 5.0 over existing installation and the whole Grant Tables group it isn't installed, then in this case the solution should be to install in new directory. Morten Gulbrandsen This is true, I had another version 4.0.x alpha running, installed in the same directory. But I removed all of it, except my own sql files. some traces of it could make problems, I'd prefer not to format my hard disk in order to get completely rid of mysql. 4 ;-) I used the uninstaller from control panel, which also renamed a my.cnf file. The environment variable I believe is correct. Miguel Angel Solórzano installed, then in this case the solution should be to install in new directory. Morten Gulbrandsen OK, I can do that, and then I can copy the missing stuff back into the correct directory. however I got no warnings or error messages, that files already were present or options which asked me to choose between overwrite or replace. I simply can't see why another installation which is simply a file copying procedure, fails if I somehow uninstall older MySQL versions. Since I renamed the other directory a clash with older installation is impossible, I created a new mysql directory with nothing, before I installed again. ERROR 1146 at line 24: Table 'mysql.proc' doesn't exist the same error again, I did the following, C:\Documents and Settings\Administratornet stop mysql The MySql service is stopping.. The MySql service was stopped successfully. then I renamed the old directory to mysql_old, I did not remove it just in order to compare, I installed again , selected custom and verified that all stuff was checked, then I did C:\Documents and Settings\Administratornet start mysql The MySql service is starting. The MySql service was started successfully. took my file and ran it, I:\mysql\binmysql -u root -vvv testtest.sql -- DROP TABLE IF EXISTS A -- ## Table was nonexistent. Query OK, 0 rows affected, 1 warning (0.06 sec) -- CREATE TABLE A ( SSN CHAR(9) NOT NULL, SUPERSSN CHAR(9), DNO INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB -- ## please why this warning ? can I have more details ? Query OK, 0 rows affected, 1 warning (0.13 sec) -- DESCRIBE A -- +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | SSN | char(9) | | PRI | | | | SUPERSSN | char(9) | YES | MUL | NULL| | | DNO | int(11) | | MUL | 1 | | +--+-+--+-+-+---+ 3 rows in set (0.02 sec) -- ALTER TABLE A ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN) ON DELETE SET NULL ON UPDATE CASCADE -- ERROR 1146 at line 24: Table 'mysql.proc' doesn't exist Bye I:\mysql\data\mysqldir Volume in drive I is Application program files Volume Serial Number is 906C-357F Directory of I:\mysql\data\mysql 08/01/2004 19:01 DIR . 08/01/2004 19:01 DIR .. 22/12/2003 21:038,820 columns_priv.frm 22/12/2003 21:030 columns_priv.MYD 22/12/2003 21:031,024 columns_priv.MYI 22/12/2003 21:039,178 db.frm 22/12/2003 21:03 306 db.MYD 22/12/2003 21:033,072 db.MYI 22/12/2003 21:038,665 func.frm 22/12/2003 21:030 func.MYD 22/12/2003 21:031,024 func.MYI 22/12/2003 21:039,148 host.frm 22/12/2003 21:030 host.MYD 22/12/2003 21:031,024 host.MYI 22/12/2003 21:038,925 tables_priv.frm 22/12/2003 21:030 tables_priv.MYD 22/12/2003 21:031,024 tables_priv.MYI 22/12/2003 21:039,992 user.frm 22/12/2003 21:03 160 user.MYD 22/12/2003 21:032,048 user.MYI 18 File(s) 64,410 bytes 2 Dir(s) 3,870,883,840 bytes free The same 18 files, nothing could have prevented the missing files to be correctly written. first installation was typical, second installation was custom with all checkboxes, in fact even during the typical installation it was impossible to uncheck two of the options, one of the mandatory options was something with grant tables. It is ok for me to know that this problem is not known under win XP, What about Linux
Re: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
Miguel Angel Solórzano Well I will test that on my win2k server machine is now down running instead Suse 9.0. Morten Gulbrandsen Thanks, If I know that it runs satisfactorily under suse, I'm satisfied, I have suse 8.2, but I can go for suse 9.0 If necessary. Miguel Angel Solórzano Anyway you have the option for to download the package without installer, then is just an issue to unzip the files in the right place. You should this option package in our web site in the same place of one you have already downloaded one line below. Morten Gulbrandsen This could be the reason, I saw the package without installer, I'll go for it, I understand the distributions with and without installer are not quite the same, Did you use the win distribution without the installer ? Anyway it is an alpha version and I'd like to test the features I need. Previously the linux version was slightly ahead of the same windows version. So I believe that you primarily target the linux users. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1005 at line 38: Can't create table '.\test\#sql-748_17.frm' (errno: 150)
USE test; DROP TABLE IF EXISTS relvar_a; CREATE TABLE relvar_a ( #PK SSNCHAR(9) NOT NULL, #FK SUPERSSN CHAR(9), DNOINT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )ENGINE = INNODB; SHOW WARNINGS; DESCRIBE relvar_a; DROP TABLE IF EXISTS relvar_b; CREATE TABLE relvar_b ( #PK DNUMBER INTNOT NULL, #FK MGRSSN CHAR(9) NOT NULL DEFAULT '88866', PRIMARY KEY (DNUMBER), INDEX(MGRSSN) )ENGINE = INNODB; SHOW INNODB STATUS ; ALTER TABLE relvar_a# won't do line 38 ADD FOREIGN KEY (DNO) REFERENCES relvar_b(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE; ERROR 1005 at line 38: Can't create table '.\test\#sql-748_17.frm' (errno: 150) Bye mysql select version(); ++ | version() | ++ | 5.0.0-alpha-max-nt | ++ 1 row in set (0.11 sec) Microsoft Windows 2000 [Version 5.00.2195]
ERROR 1005 at line 38: Can't create table '.\test\#sql-748_17.frm' (errno: 150)
Hello, please help me solve this problem, ON DELETE SET DEFAULT should be supported, == USE test; DROP TABLE IF EXISTS relvar_a; CREATE TABLE relvar_a ( #PK SSNCHAR(9) NOT NULL, #FK SUPERSSN CHAR(9), DNOINT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )ENGINE = INNODB; SHOW WARNINGS; DESCRIBE relvar_a; DROP TABLE IF EXISTS relvar_b; CREATE TABLE relvar_b ( #PK DNUMBER INTNOT NULL, #FK MGRSSN CHAR(9) NOT NULL DEFAULT '88866', PRIMARY KEY (DNUMBER), INDEX(MGRSSN) )ENGINE = INNODB; SHOW INNODB STATUS; ALTER TABLE relvar_a# won't do line 38 ADD FOREIGN KEY (DNO) REFERENCES relvar_b(DNUMBER) ON DELETE SET DEFAULT # is supported in 5.0.0 alpha ON UPDATE CASCADE; = ERROR 1005 at line 38: Can't create table '.\test\#sql-748_17.frm' (errno: 150) Bye mysql select version(); ++ | version() | ++ | 5.0.0-alpha-max-nt | ++ 1 row in set (0.11 sec) Microsoft Windows 2000 [Version 5.00.2195] What can I do, please ? Yours Sincerely Morten Gulbrandsen
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
MySQL 5.0.0-alpha-max-nt under win2k a small problem: If I have a foreign key with reference to the same table, I get an error, ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist what can I do ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS A; CREATE TABLE A ( SSN CHAR(9) NOT NULL, SUPERSSN CHAR(9), DNOINT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; DESCRIBE A; ALTER TABLE A # line 42 here is the buggy code ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN) ON DELETE SET NULL ON UPDATE CASCADE; #ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist mysql select version(); ++ | version() | ++ | 5.0.0-alpha-max-nt | ++ 1 row in set (0.00 sec) Microsoft Windows 2000 [Version 5.00.2195] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction support
Well, I think this statement does it all, http://www.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8q=MySQL+toy+marston Subject: Re: Can MySQL table handle 3 million+ entries? Newsgroups: comp.lang.php Date: 2003-04-11 15:20:10 PST MySQL is NOT a toy database - it is far superior to many I have used in my long career. The lack of constraints is NOT a weakness. It is eminently possible to create reliable applications without the need for database constraints - I should know because I have designed and built many applications that did not use database constraints (mainly because they were not available). Developers only rely on database constraints to circumvent their sloppy code. Anything that can be done within the database can also be done within application code. I have seen what happens when poor programmers try to shift logic from their code into the database - they get it wrong and then blame the database for their incompetence. I am used to designing and building applications without relying on database 'features', so I write my code accordingly. It also means that the logic is maintained in one place and not it bits and pieces here and there. Tony Marston http://www.tonymarston.co.uk/php-mysql/index.html some advantage does MySQL have, Rock stable, fast, good support, I got much respone from a comparison about what other developers feel here, http://groups.google.com/groups?q=mysql+gulbrandsen+rdbmshl=enlr=ie=UTF-8oe=UTF-8selm=60ca69db.0308210016.822e230%40posting.google.comrnum=1 Yours sincerely Morten Gulbrandsen - Original Message - From: Bryan Koschmann - GKT [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 3:37 AM Subject: transaction support Hi, I'm trying to get a software designer to write us some software using MySQL as the database server (he currently requires MS SQL). It is all windows based software (written in VB). So far his arguments against it are this (not my words): -No explicit transactional support -MySQL is still buggy -MyODBC is buggy and not used in production environments -Only way to connect using ODBC is third party drivers that cost over half as much as MS SQL This is just for our current software, the new software he is bidding on says he would use .NET so that supposedely causes other problems. Now, I know there are a few discrepancies there but I just don't know enough to argue it. I * need* to use MySQL as the server because of cost reasons. I *WANT* to use MySQL because I don't care for MS choose not to run their products. If you can give me any information to help me argue this I would really appreciate it. Thanks, Bryan -- 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]
Enabling more detailed logging
Hi, I would like some more extensive logging output, so I can see what sessions begin etc. In other words, enable general logging. I'm running RedHat 9 w. mysql-3.23.58 I've tried to add the log=[file] to my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysql/mysqld-err.log pid-file=/var/run/mysqld/mysqld.pid log=/var/log/mysqld/mysqld.log log-long-format But the mysqld.log does not come. The mysqld-err.log does. The DB gets started via the init script that comes with the RPM, ie: /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf What am I missing? How do I enable more detailed logging? Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
Dear Toro Hill, Thank you for the gentle explanation, I believe you mean this: Hence it is not possible to insert, update or modify anything into TABLE PRODUCT_ORDER Because : No is auto Increment, cannot be directly manipulated, product_category product_id customer_id is all Foreign Keys and hence will be actualised through the references indirectly, when they are changed, the new values will be propagated, no insert statement is possible into a foreign key or anything which is auto Increment, Please correct me, foreign key constraints prevent values to be modified in variables, it is only possible through references, When an external variable is inserted, and through references copied into TABLE PRODUCT_ORDER, then the auto increment will do what it should. #INSERT INTOPRODUCT_ORDER(some variable) VALUES( some values ); is not possible, for any variable or value. Is this true for PRODUCT_ORDER ? Pleas tell me, what is the purpose of ON UPDATE CASCADE ON DELETE RESTRICT ? Is that necessary ? Why ? Example please ? Yours Sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Toro Hill [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 17. September 2003 01:35 An: Morten Gulbrandsen Cc: [EMAIL PROTECTED] Betreff: Re: AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT The first foreign key contraints in the following table definition mean this: Any record that is inserted into the product_order table must have values for product_category and product_id that exist in the category and id fields of a record in the product table. The second foreign key contraints means that any record that is inserted into the product_order table must have a value for customer_id that exists in the id field of a record in the customer table. CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB; Therefore, when you try and execute the last insert statement below it fails because the value for customer_id is not in the id field of any of the records in the customer table. Hence the foreign key constraint defined stops you from doing the last insert, which is what it should do. INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); INSERT INTOPRODUCT_ORDER(customer_id) VALUES(1); I hope this helps. Toro -Ursprüngliche Nachricht- Von: Toro Hill [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 16. September 2003 03:36 An: Morten Gulbrandsen Cc: [EMAIL PROTECTED] Betreff: Re: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT I believe that your ON UPDATE CASCADE clause should be in the definition for the PRODUCT and CUSTOMER table rather than the PRODUCT_ORDER table. However, I don't think that it will work how you expect. ON UPDATE CASCADE means that everytime you update a row in this table then all rows in other tables that reference this table (via a foreign key) will be updated also. So if there are no rows in PRODUCT_ORDER then ON UPDATE CASCADE will not insert new rows when you add rows to the other tables. What will happen instead is that any row (that already exists) in your PRODUCT_ORDER table will be updated with the new data that has been updated in one of the other tables. This is my understanding of how it works anyway. For further information go to http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Hope this helps. Toro Morten Dear Toro Thank you for your honest comment, the statement ON UPDATE CASCADE does not INSERT, UPDATE or CASCADE anything. The code needs explanation. It is from the reference manual as a complex example with minor modifications. I hope that the company MySQL AB could take a look at it. Confer 7.5.5.2 FOREIGN KEY Constraints http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Please correct me, if some of you are able to get anything relational out of it. The first I expect from any database is that the examples from the Reference manual compiles and runs. I do get error messages, which are 'correct' I believe due to the constraints. But no expected relational behaviour. Like the parent child example from the reference manual. I believe the tables PRODUCT and CUSTOMER are entity types and the table PRODUCT_ORDER is a relationship type. However foreign keys are not correctly implemented in MySQL. As we all can see from this example. /Morten USE test; DROP TABLE IF EXISTS PRODUCT_ORDER
AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
-Ursprüngliche Nachricht- Von: Toro Hill [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 16. September 2003 03:36 An: Morten Gulbrandsen Cc: [EMAIL PROTECTED] Betreff: Re: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT I believe that your ON UPDATE CASCADE clause should be in the definition for the PRODUCT and CUSTOMER table rather than the PRODUCT_ORDER table. However, I don't think that it will work how you expect. ON UPDATE CASCADE means that everytime you update a row in this table then all rows in other tables that reference this table (via a foreign key) will be updated also. So if there are no rows in PRODUCT_ORDER then ON UPDATE CASCADE will not insert new rows when you add rows to the other tables. What will happen instead is that any row (that already exists) in your PRODUCT_ORDER table will be updated with the new data that has been updated in one of the other tables. This is my understanding of how it works anyway. For further information go to http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Hope this helps. Toro Morten Dear Toro Thank you for your honest comment, the statement ON UPDATE CASCADE does not INSERT, UPDATE or CASCADE anything. The code needs explanation. It is from the reference manual as a complex example with minor modifications. I hope that the company MySQL AB could take a look at it. Confer 7.5.5.2 FOREIGN KEY Constraints http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Please correct me, if some of you are able to get anything relational out of it. The first I expect from any database is that the examples from the Reference manual compiles and runs. I do get error messages, which are 'correct' I believe due to the constraints. But no expected relational behaviour. Like the parent child example from the reference manual. I believe the tables PRODUCT and CUSTOMER are entity types and the table PRODUCT_ORDER is a relationship type. However foreign keys are not correctly implemented in MySQL. As we all can see from this example. /Morten USE test; DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT; CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) ) TYPE=INNODB; CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB; INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); #INSERT INTOPRODUCT_ORDER(customer_id) VALUES(1); # ERROR 1216 at line 40: Cannot add or update a child row: # a foreign key constraint fails # it is impossible to directly add or update PRODUCT_ORDER # what I find correctly, incorrect is the foreign key constraints, # since this table is from the reference manual, # I'd like some explanation on how to use the table, please. SELECT * FROM PRODUCT; SELECT * FROM CUSTOMER; SELECT * FROM PRODUCT_ORDER; Dear Programmers, At the end of this query, I make a select * from the table product_order, Which happens to be empty, Why ? Is something wrong with my insert statements please ? I inserted something into the tables CUSTOMER and PRODUCT, and I expected it to appear into the table PRODUCT_ORDER which is some kind of relationship between the two entity types CUSTOMER and PRODUCT. What do I please have to insert in order to achieve some evidence for the existence of referential integrity ? please ? ON UPDATE CASCADE I feel means something like that the actual data is propagated due to the references ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
Dear Programmers, At the end of this query, I make a select * from the table product_order, Which happens to be empty, Why ? Is something wrong with my insert statements please ? I inserted something into the tables CUSTOMER and PRODUCT, and I expected it to appear into the table PRODUCT_ORDER which is some kind of relationship between the two entity types CUSTOMER and PRODUCT. What do I please have to insert in order to achieve some evidence for the existence of referential integrity ? please ? ON UPDATE CASCADE I feel means something like that the actual data is propagated due to the references ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT; CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) )TYPE=INNODB; CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) )TYPE=INNODB; CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) )TYPE=INNODB; INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); SELECT * FROM PRODUCT; SELECT * FROM CUSTOMER; SELECT * FROM PRODUCT_ORDER; C:\mysql\binmysql -u sampadm -psecret -vvv Foreign_Key_02.sql out.txt -- DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT -- Query OK, 0 rows affected (0.02 sec) -- CREATE TABLE PRODUCT ( category INT NOT NULL, id INT NOT NULL, priceDECIMAL(1,2), PRIMARY KEY(category, id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE CUSTOMER ( id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB -- Query OK, 0 rows affected (0.02 sec) -- INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ) -- Query OK, 1 row affected (0.00 sec) -- INSERT INTOCUSTOMER(id) VALUES (2) -- Query OK, 1 row affected (0.00 sec) -- SELECT * FROM PRODUCT -- +--++---+ | category | id | price | +--++---+ |1 | 1 | 0.10 | +--++---+ 1 row in set (0.00 sec) -- SELECT * FROM CUSTOMER -- ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) -- SELECT * FROM PRODUCT_ORDER -- Empty set (0.00 sec) Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: How To Create Users In MySQL?
http://www.mysql.com/doc/en/Adding_users.html First, use the mysql program to connect to the server as the MySQL root user: shell mysql --user=root mysql Then you can add new users by issuing GRANT statements: mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT ALL PRIVILEGES ON *.* TO monty@'%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED]; mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED] Dear Caroline Jen, Sometimes this is not quite clear explained in All text books, But the above snip does the trick, Under windows XP. If security is a very important topic for you, A different Operating system could offer you more safety. But please take into consideration that mysql behaves different under Linux and windows. The commands are not exactly the same. Even if it is the same MySQL version. Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Caroline Jen [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 10. September 2003 22:33 An: [EMAIL PROTECTED] Betreff: How To Create Users In MySQL? I have the MySQL-3.23.55 installed in my PC. Therefore, I am the DBA without the required DBA knowledge. First, how do I create users in the MySQL database? Second, how do I grant table creation privilege to users? Is GRANT ALL PRIVILEGES ON databasename TO someuser IDENTIFIED BY 'somepassword'; the correct command? Thanks for your guidance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The value of def_value must be a constant; it cannot be an expression or refer to other columns.
Creating Tables The general attributes can be given for any column type, with a few exceptions. You can specify NULL or NOT NULL to indicate whether a column can hold NULL values. For all but BLOB and TEXT types, you can specify DEFAULT def_value to indicate that a column should be assigned the value def_value when a new row is created that does not explicitly specify the column's value. The value of def_value must be a constant; it cannot be an expression or refer to other columns. === Hello programmers, This is from Mr. Paul DuBois MySQL textbook, Page 101. I'd like to know if this is a definition from the SQL standard, Can this be possible by correct implementation of 'actions', 'triggers' and constrains ? Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: different between index and key when create table
Dear Vivian Wang: This depends a little upon what version you are using, === Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 77 to server version: 4.1.0-alpha-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select version(); +---+ | version() | +---+ | 4.1.0-alpha-max-debug | +---+ 1 row in set (0.00 sec) === And also it does matter if you'd later like to add some foreign or primary key, to the table whatever table type eg MyISAM, InnoDB ... After the table is created. Foreign keys however has to be indexed before they are created, Indexing Tables increases the performance of a query if you use some kind of JOIN ( or simply a comma in the from clause ) between multiple tables, it does exist several table types, and column data types with some attributes, which sometimes permit or deny indexing. The table types behave differently When you index their identifiers. I'm primarily interested in indexing a column integer key data type, Which should be a foreign key with reference to some other table. But I have never ever succeeded in this, with MySQL. The main problem lies according to what I feel in How NULL values are treated. But this is something I guess, no table can contain any key with only NULL values. In MySQL we have to index a foreign key before we are creating or using it. The datatype and column type has to be some integer, or string datatype, The column type has to have some attribute NOT NULL, In order to prevent NULL values. Even fulltext indexes exists for some table types. I find this topic quite good explained in the excellent Book Written by Mr. Paul DuBois, which replies here quite frequently. Said easily if it helps, A key can be indexed, but it can also have some inherent meaning without ever being indexed. Some database scientists believe that for some table types, indexed columns can be NULL as long as the index is not a PK. But I have never dared to challenge MySQL with anything like that. Indexing speeds up the query performance of a select statement, if you succeed in getting your database program started. I hope ? Please correct me if I'm wrong, especially all of you who really understand some of what I'm trying to express. = -Ursprüngliche Nachricht- Von: Vivian Wang [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 2. September 2003 20:45 An: [EMAIL PROTECTED] Betreff: different between index and key when create table Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); mysql DROP TABLE IF EXISTS info ; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE info - ( - fname char(9), - lname char (15), - address char(30), - index(lname) - ); Query OK, 0 rows affected (0.01 sec) mysql DESCRIBE info; +-+--+---+--+-+-+--- + | Field | Type | Collation | Null | Key | Default | Extra | +-+--+---+--+-+-+--- + | fname | char(9) | latin1_swedish_ci | YES | | NULL| | | lname | char(15) | latin1_swedish_ci | YES | MUL | NULL| | | address | char(30) | latin1_swedish_ci | YES | | NULL| | +-+--+---+--+-+-+--- + 3 rows in set (0.00 sec) mysql mysql DROP TABLE IF EXISTS info ; Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE info - ( - fname char(9), - lname char(15), - address char(30), - key(lname) - ); Query OK, 0 rows affected (0.00 sec) mysql DESCRIBE info; +-+--+---+--+-+-+--- + | Field | Type | Collation | Null | Key | Default | Extra | +-+--+---+--+-+-+--- + | fname | char(9) | latin1_swedish_ci | YES | | NULL| | | lname | char(15) | latin1_swedish_ci | YES | MUL | NULL| | | address | char(30) | latin1_swedish_ci | YES | | NULL| | +-+--+---+--+-+-+--- + 3 rows in set (0.00 sec) As you can see the only difference is 10 milliseconds in creating, And 20 milliseconds in dropping. ;-) I need some more queries ;-) Like insert into table ... values ... and select from ... where ... As is, I'll get an empty set, mysql SELECT * FROM info; Empty set (0.00 sec) Yours Sincerely Morten Gulbrandsen Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1115: Unknown character set: 'ucs2'
Dear programmers,
is this correct ?
mysql select version();
+---+
| version() |
+---+
| 4.1.0-alpha-max-debug |
+---+
1 row in set (0.00 sec)
mysql SET @s = CONVERT('ABC' USING ucs2);
ERROR 1115: Unknown character set: 'ucs2'
mysql
the manual has some statement about ucs2 :
Temporary restriction:
UCS-2 can't (yet) be used as a client character set. That means that
SET NAMES ucs2
will not work.
Can I please have an example how to utilize this ?
Yours sincerely
Morten Gulbrandsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1115: Unknown character set: 'latin1_de'
mysql create table mytbl( c1 char(10) character set latin1_de); ERROR 1115: Unknown character set: 'latin1_de' mysql create table mytbl( c1 char(10) character set utf8); ERROR 1115: Unknown character set: 'utf8' mysql create table mytbl( c1 char(10) character set sjis); ERROR 1115: Unknown character set: 'sjis' mysql select version(); +---+ | version() | +---+ | 4.1.0-alpha-max-debug | +---+ 1 row in set (0.01 sec) mysql Hi again, is this solved in a next release, Please ? I need to insert statements in german language, öäüß is part of that. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
63 LSB ok but bit 64 is sign bit
mysql select - 0x+0 AS '18446744073423220462', - 0x7fff+0 AS '9223372036568444654 ', - 0x8fff+0 AS '10376293541175291630' ; +--+--+--+ | 18446744073423220462 | 9223372036568444654 | 10376293541175291630 | +--+--+--+ | -286331154 | 9223372036568444654 | -8070450532534259986 | +--+--+--+ 1 row in set (0.00 sec) Hi, on my operating system Mysql offers this output, is it correct ? I think not. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to code an 'IS - A' relationship ?
Hello MySQL programmers, suppose we have an Enhanced ER diagram, with entities as classes/ subclasses connected through some IS-A relationship. How can this be Coded in MySQL Please? My prerequisites are the basic database texts from http://www-db.stanford.edu/~ullman/dscb.html http://www.aw-bc.com/info/database/elmasri.html Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comp.databases.theory
Hello, I have just started a thread in comp.databases.theory with the subject Is mysql a RDBMS ? Of course it is not a RDBMS It never was and will never be It is an RDBMS as we all know ;-) Hope some of you can take time to Reply on the most important misunderstandings. At present the problem is constraints, actions and triggers, And especially this: === Thus, according to the definition of Elmasri and Navathe, MySQL/InnoDB is a relational database. It is not a relational database according to so-called Codd's 12 rules, but none of the well-known databases satisfies all those rules. === I am really satisfied if some of you could kindly help me to implement the basic fundamental Code examples from good explained E-ER diagrams into MySQL / InnoDB ANSI SQL2 code, without having to use other RDBMS, Some additional perl or PHP coding is ok. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using joins to create code from E- ER relationships
Hello, starting from some software database spesification, defined in some Enhanced entity relationship diagram, resulting in all kinds of relationships, 1:1 1:Many Many:1 Many : many And last but not least: Subclass/superclass relationship This seems to be a small problem for me, With the current version of MySQL. How can I use joins to code in SQL syntax an arbitrary model of An Entity relationship diagram ? With all constraints correctly implemented, Without any foreign keys ? Without any on delete set default, I hope this can be implemented also without Combining MySQL to PHP, Perl or C. Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I simply can't compile this
CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '88866', MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN) )TYPE = INNODB; ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE EMPLOYEE # here is the buggy code ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE; ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ; Please tell me what could be wrong, Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: mutual declarations produce Error 1005 (errno: 150)
--
*** 1. row ***
Name: employee
Type: InnoDB
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: NULL
Index_length: 32768
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Charset: latin1_swedish_ci
Create_options:
Comment: InnoDB free: 3072 kB; (SUPERSSN) REFER
company/employee(SSN) ON
UPDATE CASCADE
1 row in set (0.00 sec)
mysql SHOW TABLE STATUS FROM company LIKE DEPARTMENT \G
--
SHOW TABLE STATUS FROM company LIKE DEPARTMENT
--
*** 1. row ***
Name: department
Type: InnoDB
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: NULL
Index_length: 32768
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Charset: latin1_swedish_ci
Create_options:
Comment: InnoDB free: 3072 kB
1 row in set (0.00 sec)
mysql
=
Hi again,
I have just tried to investigate and record what InnoDB status has
noticed,
According to the MySQL reference manual,
A foreign key constraint is in My Code erroneously coded,
==
If MySQL gives the error number 1005 from a CREATE TABLE
statement, and the error message string refers to errno 150, then
the table creation failed because a foreign key constraint was not
correctly formed. Similarly, if an ALTER TABLE fails and it refers
to errno 150, that means a foreign key definition would be incorrectly
formed for the altered table. Starting from version 4.0.13, you can
use SHOWINNODB STATUS to look at a detailed explanation of the
latest InnoDB foreign key error in the server.
==
According to my general SQL experience it is all correctly
ANSI SQL-92 coded.
Please help me,
What else can be done ?
Yours Sincerely
Morten Gulbrandsen
-Ursprüngliche Nachricht-
Von: Fred van Engen [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 13. August 2003 15:36
An: Morten Gulbrandsen
Cc: 'Victoria Reznichenko'; [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
Morten,
In your extremely long mail, I think I managed to find your question and
removed all other stuff.
On Wed, Aug 13, 2003 at 03:18:26PM +0200, Morten Gulbrandsen wrote:
mysql
mysql # from now on the coding causes trouble
mysql
mysql ALTER TABLE EMPLOYEE
- FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
mysql
6.5.4 ALTER TABLE Syntax
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification
...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (create_definition, create_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD FULLTEXT [index_name] (index_col_name,...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
(index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
So what it clearly tells you, is to use this (note the 'ADD'):
mysql ALTER TABLE EMPLOYEE
- ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
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]
AW: mutual declarations produce Error 1064
Please notice that INDEX (DNO, DNUMBER), is changed to be INDEX (DNO), And I managed to get another error message, But it still woun't compile. Also manually typing the code gave another result as Piping the company_01.sql file through an input operator Please tell me what could be wrong ? Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2003 13:54 An: [EMAIL PROTECTED] Betreff: Re: mutual declarations produce Error 1064 Morten Gulbrandsen [EMAIL PROTECTED] wrote: USE company; # SHOW INNODB STATUS; DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN), # between employee and department FOREIGN KEY (MGRSSN),# a sort of mutually declaration REFERENCES EMPLOYEE(SSN) # this is declared in employee Remove comma before REFERENCES. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mutual declarations produce Error 1064
USE company; # SHOW INNODB STATUS; DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN), # between employee and department FOREIGN KEY (MGRSSN),# a sort of mutually declaration REFERENCES EMPLOYEE(SSN) # this is declared in employee )TYPE = INNODB; DROP TABLE IF EXISTS EMPLOYEE; CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), INDEX (SUPERSSN, SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), INDEX (DNO, DNUMBER), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) # this is declared in department )TYPE = INNODB; === ERROR 1064 at line 9: You have an error in your SQL syntax. Check the manual th at corresponds to your MySQL server version for the right syntax to use near ' REFERENCES EMPLOYEE(SSN) )TYPE = INNODB' at line 10 Bye In short I have Department Dnumber References employee (ssn) Employee Ssn References department(dnumber) What can be done , please ? Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: mutual declarations produce Error 1064
--
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
--
Bye
Please ,
what else can I do ?
Alter Table add foreign key runs fine,
alter table Xadd foreign key referencec Y
with X!=Y simply woun't compile !
All I can get is
alter table X
add foreign key
referencec X
No mutual references between any two different tables,
Or does it exist another solution or workaround ?
Yours Sincerely
Morten Gulbrandsen
The one who loves MySQL ;-)
-Ursprüngliche Nachricht-
Von: Fred van Engen [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 13. August 2003 15:36
An: Morten Gulbrandsen
Cc: 'Victoria Reznichenko'; [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
Morten,
In your extremely long mail, I think I managed to find your question and
removed all other stuff.
On Wed, Aug 13, 2003 at 03:18:26PM +0200, Morten Gulbrandsen wrote:
mysql
mysql # from now on the coding causes trouble
mysql
mysql ALTER TABLE EMPLOYEE
- FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
mysql
6.5.4 ALTER TABLE Syntax
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification
...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (create_definition, create_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD FULLTEXT [index_name] (index_col_name,...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
(index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
So what it clearly tells you, is to use this (note the 'ADD'):
mysql ALTER TABLE EMPLOYEE
- ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
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]
AW: mutual declarations produce Error 1064
in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(MGRSSN)REFERENCES EMPLOYEE(SSN)
ON DELETE SET D
mysql
mysql
I try again with
C:\mysql\binmysql -u elmasri -pnavathe companyCompany_08.sql
out.txt
ERROR 1064 at line 40: You have an error in your SQL syntax. Check the
manual t
hat corresponds to your MySQL server version for the right syntax to use
near 'F
OREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
C:\mysql\bintype Company_08.sql
USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSNCHAR(9),
DNO INT NOT NULL DEFAULT
1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB;
DESCRIBE EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '88866',
MGRSTARTDATEDATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB;
ALTER TABLE EMPLOYEE
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE;
ALTER TABLE EMPLOYEE
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE;
ALTER TABLE DEPARTMENT
FOREIGN KEY (MGRSSN)REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT
ON UPDATE CASCADE ;
DESCRIBE DEPARTMENT;
SHOW CREATE TABLE EMPLOYEE;
SHOW CREATE TABLE DEPARTMENT;
SHOW TABLE STATUS FROM company LIKE EMPLOYEE;
SHOW TABLE STATUS FROM company LIKE DEPARTMENT;
Can someone please take a look at my coding,
-Ursprüngliche Nachricht-
Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 8. August 2003 13:02
An: [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
Morten Gulbrandsen [EMAIL PROTECTED] wrote:
I think something is wrong with my administration,
Basically I get these messages,
ERROR 1005 at line 9: Can't create table '.\company\department.frm'
(errno: 150)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password:
YES)
ERROR 1133: Can't find any matching row in the user table
C:\mysql\examples\elmasrimysql -u root -p***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version:
4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql use mysql;
Database changed
mysql update user set password = password('navathe') where user =
'elmasri';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql quit
Bye
C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version:
4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql quit
Bye
C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company
company_01.sql
ERROR 1005 at line 9: Can't create table '.\company\department.frm'
(errno: 150)
C:\mysql\examples\elmasri
C:\mysql\examples\elmasritype company_01.sql
# mysql -u root -h localhost -p -vvv company_01.sql
USE company;
# SHOW INNODB STATUS;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATEDATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN), # between employee and department
FOREIGN KEY (MGRSSN)# a sort of mutually declaration
REFERENCES EMPLOYEE(SSN) # this is declared in employee
)TYPE = INNODB;
You create table DEPARTMENT with foreign key, which is referenced to the
non-existent table EMPLOYEE. So, you need to create both table without
foreign keys and then add foreign key constraints or execute SET
FOREIGN_KEY_CHECKS=0 before table creation.
===
I tried to add foreign key constrains after
Both tables was created,
I believe my code is pure sql, but not yet implemented in mysql
I'd like to distribute the foreign keys according to software
specification,
Yours Sincerely
Morten Gulbrandsen
===
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX
AW: mutual declarations produce Error 1064
), - INDEX (SUPERSSN, SSN), - - FOREIGN KEY (SUPERSSN) - REFERENCES EMPLOYEE(SSN), - - INDEX (DNO), - FOREIGN KEY (DNO) - REFERENCES DEPARTMENT(DNUMBER) # this is declared in department - )TYPE = INNODB; ERROR 1005: Can't create table '.\company\employee.frm' (errno: 150) mysql exit Bye C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company company_01.sql ERROR 1005 at line 9: Can't create table '.\company\department.frm' (errno: 150) This shows that I actually can create a table, My question is now, Is this code ansi sql correct ? How can I Modify it to run under the latest MySQL ? Is it something wrong with the users I have created ? I expect one user and one password for entering mysql, And depending upon which database I'd like to use I expect for each database I'd like to use, one second password, This seems to be another topic, When I write mysql grant all on company.* to 'elmasri'@'localhost' identified by 'navathe'; Query OK, 0 rows affected (0.00 sec) mysql grant all on company.* to 'elmasri'@'%' identified by 'navathe'; Query OK, 0 rows affected (0.00 sec) mysql Then I should be able to do whatever SQL permits ? Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2003 13:54 An: [EMAIL PROTECTED] Betreff: Re: mutual declarations produce Error 1064 Morten Gulbrandsen [EMAIL PROTECTED] wrote: USE company; # SHOW INNODB STATUS; DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATEDATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), INDEX (MGRSSN), # between employee and department FOREIGN KEY (MGRSSN),# a sort of mutually declaration REFERENCES EMPLOYEE(SSN) # this is declared in employee Remove comma before REFERENCES. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045: Access denied
Hi programmers, Good morning ;-)
I think something is wrong,
Running windows I believe first I have to set some password,
Which creates some user,
Then I can grant the required privileges, for some databases.
However granting privileges after setting password seems to reset some
Other access,
===
C:\temp\Menagerie_01mysql -u root -p** mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql set password for 'elmasri'@'localhost'=password('navathe');
Query OK, 0 rows affected (0.02 sec)
mysql set password for 'elmasri'@'%'=password('navathe');
Query OK, 0 rows affected (0.00 sec)
mysql grant all on company.* to 'elmasri'@'localhost' identified by
'navathe';
Query OK, 0 rows affected (0.02 sec)
mysql grant all on company.* to 'elmasri'@'%' identified by 'navathe';
Query OK, 0 rows affected (0.00 sec)
mysql quit
Bye
C:\temp\Menagerie_01mysql -u elmasri -pnavathe company
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
===
However if I use only the grant statement,
I don't need any set password statement,
Both setting password and granting all privileges
- ERROR 1045: Access denied
But Why ?
===
C:\temp\Menagerie_01mysql -u root -p* mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql grant all privileges on company.* to 'monty'@'localhost'
identified by
'python' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql quit
Bye
C:\temp\Menagerie_01mysql -u monty -ppython company
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql show tables
- ;
+---+
| Tables_in_company |
+---+
| blabla|
+---+
1 row in set (0.01 sec)
mysql drop table blabla;
Query OK, 0 rows affected (0.00 sec)
mysql show tables;
Empty set (0.00 sec)
mysql create table blabla ( fname varchar(15) not null )type=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql show tables;
+---+
| Tables_in_company |
+---+
| blabla|
+---+
1 row in set (0.00 sec)
mysql
Yours Sincerely
Morten Gulbrandsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.0.1-alpha
7.5.5.2 Foreign Key Constraints Hi programmers, I try to code this CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), # INDEX (SUPERSSN, DNO), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) )TYPE = INNODB; And mysql complains with ERROR 1005 at line 9: Can't create table '.\company\employee.frm' (errno: 150) Bye Can someone please help me to fix this code? I think something is wrong with the index declaration, or with the foreign key constraint. But what can I do ? Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error after upgrading to 4.1.0-alpha-max-debug LibMySQL.dll missing
Hello programmers this is my path, C:\mysql\binpath PATH=C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\mysql\bin After installing 4.1.0-alpha-max-debug I try to start winmysqladmin.exe Then I get this error, === Die dynamic link library libmysql.dll wurde nicht im angegebenen Pfad C:\mysql\bin;.; C:\WINNT\System32; C:\WINNT\System; C:\WINNT; C:\WINNT\System32; C:\WINNT; C:\WINNT\System32\Wbem; C:\mysql\bin Gefunden. === However I found it in C:Mysql\lib\debug And C:Mysql\lib\opt What can I do, please ? I think my path identifier is correct, I simply appended the last ;C:\mysql\bin entry myself and it run fine under 4.1 beta. Also with support for InnoDB tables. In english the error means that winmysqladmin.exe can't run under 4.0.1 alpha, unfortunately Is this true ? When is final release of 4.1.0 scheduled please ? I highly appreciate and thank you all eternally for Enabling nested selects like SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop) Which is pure SQL-99 I think, and compatible with oracle sql. And finally I'd like to know what is support for named pipes? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf is not available under windows 2000
Hi programmers, according to the manual, There are two option files with the same function: `C:\my.cnf', and the `my.ini' file in the Windows directory. Is it sufficient with only one of the files ? I have only my.ini For which purpose is my.cnf , please? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Constraints under 3.23.55-nt
Hello programmers: I use Version DBMS MySQL 3.23.55 runing under Windows 2000. I have coded this from a textbook; === C:\mysql\bintype constraint.sql USE company; DROP TABLE IF EXISTS EMPLOYEE; CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB; DESCRIBE EMPLOYEE; C:\mysql\bin === This is what happens : === C:\mysql\binmysql -vvvconstraint.sql -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DESCRIBE EMPLOYEE -- +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | FNAME| varchar(15) | | | | | | MINIT| char(1) | YES | | NULL| | | LNAME| varchar(15) | | | | | | SSN | varchar(9)| | PRI | | | | BDATE| date | YES | | NULL| | | ADDRESS | varchar(30) | YES | | NULL| | | SEX | char(1) | YES | | NULL| | | SALARY | decimal(10,2) | YES | | NULL| | | SUPERSSN | varchar(9)| YES | | NULL| | | DNO | int(11) | | | 1 | | +--+---+--+-+-+---+ 10 rows in set (0.00 sec) Bye C:\mysql\bin === My problem is, I want some feedback from MySQL that my versions actually supports those statements, including innodB and all additional constraint information, Describe does not give me feedback about foreign keys and referential triggered actions. Is InnoDB the correct table type for properly supporting referential triggered actions, please ? If not, what can I do instead ? Upgrade to some newer MySQL version please ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Dear MySQL developers, Could some experienced Database developer please take a look at this ? It is supposed to be plain SQL2. How can it be coded under MySQL Especially all referential triggered actions. According to the manual : 7.5.5.2 Foreign Key Constraints Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data. Yours Sincerely Morten Gulbrandsen === -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.01 sec) -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPARTMENT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '88866', MGRSTARTDATEDATE, CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER), CONSTRAINT DEPTSK UNIQUE (DNAME), CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPT_LOCATIONS -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPT_LOCATIONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS PROJECT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE PROJECT ( PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION varchar(15), DNUMint NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME), FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) TYPE = InnoDB -- Query OK, 0 rows affected (0.02 sec) -- DROP TABLE IF EXISTS WORKS_ON -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE WORKS_ON ( ESSNCHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3,1)NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER))TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPENDENT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPENDENT ( ESSNCHAR(9) NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR, BDATE DATE, RELATIONSHIPVARCHAR(8), PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN))TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `FNAME` varchar(15) NOT NULL default '', `MINIT` char(1) default NULL, `LNAME` varchar(15) NOT NULL default '', `SSN` varchar(9) NOT NULL default '', `BDATE` date default NULL
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Dear Fred, according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? C:\mysql\binmysqld -v mysqld Ver 3.23.55-max-debug for Win95/Win98 on i32 C:\mysql\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 264 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql C:\mysql\binver Microsoft Windows 2000 [Version 5.00.2195] This seems fine to me, I believe I have the default binaries. If InnoDB is not supported in MySQL, then I would have expected a warning, Or an error message. As we all can see, no warning or error is issued, perhaps another error level can give me more feedback ? Yours Sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Fred van Engen [mailto:[EMAIL PROTECTED] TYPE = InnoDB only works if MySQL is compiled with InnoDB support, which is the default in binaries from MySQL. If you compile it yourself you need to configure --with-innodb . Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- 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]
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Hi Programmers, after this legal statement ALTER TABLE EMPLOYEE TYPE = InnoDB; I get Warnings: 0 and still MySQL claims to have to use Type = MyISAM, Which has never supported Foreign Key constraints. There must be a workaround for this, My queries are unambiguous, and MySQL does not obey. Is this fixed in later releases, please ? How can I increase the levels and sensitivity of the warnings, please ? Yours Sincerely Morten Gulbrandsen === C:\mysql\bintype constraint.sql USE company; DESCRIBE EMPLOYEE; SHOW CREATE TABLE EMPLOYEE; ALTER TABLE EMPLOYEE TYPE = InnoDB; SHOW CREATE TABLE EMPLOYEE; SHOW TABLE STATUS FROM company LIKE EMPLOYEE C:\mysql\binmysql -vvv Constraint.sqlout.txt C:\mysql\bintype out.txt -- DESCRIBE EMPLOYEE -- +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | SUPERSSN | varchar(9)| YES | | NULL| | | DNO | int(11) | | | 1 | | | SSN | varchar(9)| | PRI | | | | FNAME| varchar(15) | | | | | | MINIT| char(1) | YES | | NULL| | | LNAME| varchar(15) | | | | | | BDATE| date | YES | | NULL| | | ADDRESS | varchar(30) | YES | | NULL| | | SEX | char(1) | YES | | NULL| | | SALARY | decimal(10,2) | YES | | NULL| | +--+---+--+-+-+---+ 10 rows in set (0.00 sec) -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `SUPERSSN` varchar(9) default NULL, `DNO` int(11) NOT NULL default '1', `SSN` varchar(9) NOT NULL default '', `FNAME` varchar(15) NOT NULL default '', `MINIT` char(1) default NULL, `LNAME` varchar(15) NOT NULL default '', `BDATE` date default NULL, `ADDRESS` varchar(30) default NULL, `SEX` char(1) default NULL, `SALARY` decimal(10,2) default NULL, PRIMARY KEY (`SSN`) ) TYPE=MyISAM | +--+ --+ 1 row in set (0.00 sec) -- ALTER TABLE EMPLOYEE TYPE = InnoDB -- Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `SUPERSSN` varchar(9) default NULL, `DNO` int(11) NOT NULL default '1', `SSN` varchar(9) NOT NULL default
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
mysql SHOW VARIABLES LIKE have_innodb; +---+--+ | Variable_name | Value| +---+--+ | have_innodb | DISABLED | +---+--+ 1 row in set (0.02 sec) mysql === Goodygood, Victoria !!! Thank you Sir, This saves my keyboard, now please, how can I enable this fundamental Value have_innodb? Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 31. Juli 2003 16:01 An: [EMAIL PROTECTED] Betreff: Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ? Morten Gulbrandsen [EMAIL PROTECTED] wrote: according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? C:\mysql\binmysqld -v mysqld Ver 3.23.55-max-debug for Win95/Win98 on i32 C:\mysql\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 264 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql C:\mysql\binver Microsoft Windows 2000 [Version 5.00.2195] This seems fine to me, I believe I have the default binaries. If InnoDB is not supported in MySQL, then I would have expected a warning, Or an error message. As we all can see, no warning or error is issued, perhaps another error level can give me more feedback ? See output of SHOW VARIABLES LIKE have_innodb. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
schema and Catalog under MySQL 3.23.55
Hi programmers, I try to investigate some of the basics behind schemas and cataloges, Which is part of SQL2 Language this is the error message I get: C:\mysql\binmysql -u administrator -h localhost -p Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 246 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql use company; Database changed mysql create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql create schema company_00 authorization administrator; === it seems to me that the sql statements schema and catalog is not working in my version of mysql. What can be done in order to remedy this ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
working with linked Tables
Database mysql running on localhost Error The additional Features for working with linked Tables have been deactivated. To find out why click here. - Database mysql running on localhost PMA Database ... not OK[ Documentation ] General relation features Disabled response from phpmyadmin 2.3.2 I did nothing but this: # create absence table for grade-keeping project DROP TABLE IF EXISTS absence; CREATE TABLE absence ( student_id INT UNSIGNED NOT NULL, date DATE NOT NULL, PRIMARY KEY (student_id, date) ); All tables and all databases has the same error. What does this mean ? Where is it documented, can it prevent nested tables or linked tables To perform properly ? Software mysql -V mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) uname -a Linux debian 2.2.17 #1 Sun Jun 25 09:24:41 EST 2000 i686 unknown Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.7 Queries from Twin Project Taken from the Documentation tutorial
Dear mysql users, in the tutorial of the documentation I found an interesting example of a complicated non trivial sql query: URL: http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html# example-Maximum-column-group-row 3.7 Queries from Twin Project 3.7.1 Find all Non-distributed Twins However, I can find no databases with tables to run the queries on. What I need is some dummy tables with example randome data To run the queries on. person_data lentus twin_project twin_data informant_data harmony postal_groups It is no problem for me to create a database, the problem is that I have no idea about which structure the tables has, the added URL : http://www.imm.ki.se/TWIN/TWINUKW.HTM relinked to http://www.mep.ki.se/twin/index.html has a malfunction I don't need sensitive personal data, but the table structure and some example dummy data would be higly appreciated. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to code an IS - a relationship ?
Hello MySQL programmers, suppose we have an Enhanced ER diagram, with entities as classes/ subclasses connected through some IS-A relationship. How can this be Coded in MySQL Please? My prerequisites are the basic database texts from http://www-db.stanford.edu/~ullman/dscb.html http://www.aw-bc.com/info/database/elmasri.html Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comp-err.exe
Compiler errormessage ver 1.3 C:\mysql\bincomp-err.exe -I comp-err.exe (Compile errormessage) Ver 1.3 This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: comp-err.exe [-?] [-I] [-V] fromfile[s] tofile Options: -Info -Version Hello Programmers, for which purpose is this file included in the file C:\mysql\bin directory ? I have tried to RTFM and I did not find any entry about This specific file, perhaps it has another name under linux, I am using windows and would like to know what useful tasks Can be accomplished with comp-err.exe under windows 2000. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERWin 4.1 not applicable for MySQL
Dear MySQL users developers and programmers, ERWin 4.1 not applicable for MySQL I'd like to model some logical databases in Entity Relationships diagrams From here I found interesting software URL: http://www3.ca.com/trials/ https://www3.ca.com/register/form.aspx?cid=8403 Download ERwin 4.1 Evaluation Software ftp://ftp.ca.com/pub/erwin/EvalLic/ERwin41SP2-2765.exe According to the Documentation this visual tool supports a variety of databases: Target Databases Supported by ERwin Desktop Databases Microsoft Access Clipper DBASE IV FoxPro Paradox SQL Databases AS/400 DB2 390 DB2 UDB HiRDB INFORMIX Ingres II InterBase ODBC Generic OpenIngres Oracle PROGRESS Rdb Red Brick Warehouse SAS SQL Anywhere SQLBase SQL Server Sybase Teradata WATCOM/SQL Anywhere Since I am fond of MySQL I'd like to know your opinion, Is it possible to access MySQL from AllFusion ERwin Data Modeler ? What design tool do you use under Windows or Linux for modeling a logical database ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MIN(), MAX() not working with LEFT JOIN? Bug?
Hello I would asume that these two queries would give the same output? Am I wrong? Seems like the where part don't have any effect on the LEFT JOIN when it comes to MIN() and MAX(). SELECT count( plads_ordre.id ) as antal, MIN( forestilling.arrangement_id ) as min, MAX( forestilling.arrangement_id ) as max FROM plads_ordre INNER JOIN forestilling ON forestilling.id = plads_ordre.forestilling_id WHERE plads_ordre.ordre_id = '25408' Output: antal min max 1 18 18 SELECT count( plads_ordre.id ) as antal, MIN( forestilling.arrangement_id ) as min, MAX( forestilling.arrangement_id ) as max FROM plads_ordre LEFT JOIN forestilling ON forestilling.id = plads_ordre.forestilling_id WHERE plads_ordre.ordre_id = '25408' Output: antal min max 1 7 19 -- / morten There are only 10 types of people in the world: Those who understand binary, and those who don't - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transaction with InnoDB - how to lock?
Hello I have a system where users are able to buy tickets to shows etc. Right now I use the MyISAM as tabletype but I would like to try InnoDB to make safe transactions. For the moment I use a TABLE LOCK to make sure that the same ticket isn't sold twise. One of the table consists on these 3 fields: (order_id, show_id, seat_id) So a normal script does this: Lock tables select to see if the tickets is already sold insert new sold ticket if no one is already there unlock Obviously I don't want another user to insert a new ticket just after if checked but right before current user inserts. Now - how would I do this with InnoDB to make it safe? Best regards Morten (now pass the maillist filter; sql, mysql, query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do temporary tables die?
How do tamporary tables die? Is there a time limit, og do I have to drop them manually? -- Morten Dreier http://www.pobox.com/~mdreier/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql as a backend to openldap
I want to set up a openldap server that should import data from some tables on my mysql server. There are different strategies. 1. Use the perl scripting APIs in OpenLdap 2. Go by way of BerkleyDB files 3. Some way of direct (odbc?) communication. Does anyone have any experience with this? -- Morten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Problem
i have a MySQL DB whit this tabel: Id ColPoint CntDateTime Cnt1 Cnt2 Cntx. Cnt32 11 2001-08-01 00:05:0012 14 21 2001-08-01 00:10:0011 12 31 2001-08-01 00:15:00109 41 2001-08-01 00:20:00032 51 2001-08-01 00:25:0064 61 2001-08-01 00:30:0014 22 71 2001-08-01 00:35:0011 17 . . x122001-08-01 00:05:00324 x222001-08-01 00:10:0031 22 x322001-08-01 00:15:0030 19 x422001-08-01 00:20:00022 x522001-08-01 00:25:0026 14 x622001-08-01 00:30:0024 22 x72 2001-08-01 00:35:001 17 . . Ect. I am collecting data from 8 collecting points whit 32 counters each I have made a delphi program to show the data in bars or curves, but the uses shal have the opportunite to add and subtrach the datas Exampel for user 1 ColPoint(Cnt1) +ColPoint5(Cnt3) -ColPoint2(Cnt11) How can i build a sql-string that satisfied this need - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Group by
i have a tabel whit 5 min counting Id CntDateTime Cnt1 1 2001-08-01 00:05:0012 2 2001-08-01 00:10:0011 3 2001-08-01 00:15:0010 4 2001-08-01 00:20:000 5 2001-08-01 00:25:006 6 2001-08-01 00:30:0014 7 2001-08-01 00:35:0011 . . What i want is to make a Select/group by, so tha i do have 15. min. countings The Resultatet of my SELECT shall be: CntDateTime Cnt1 2001-08-01 00:05:0033 2001-08-01 00:20:0020 2001-08-01 00:35:00xx It can be made in acces whit this sql-string SELECT Sum([CountTabel].[cnt1]) AS hmm, Year([CntDateTime]) Month([CntDateTime]) Day([CntDateTime]) Hour([CntDateTime]) Minute([CntDateTime])-(Minute([CntDateTime]) Mod 15) FROM CountTabel GROUP BY (Year([CntDateTime]) Month([CntDateTime]) Day([CntDateTime]) Hour([CntDateTime]) Minute([CntDateTime])-(Minute([CntDateTime]) Mod 15)); Can anybody translate it to a working SQL-statement - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
