Re: MySQL 4.1.8 InnoDB: data unavailability among different connections
simple answer is transactions. until you issue a commit, or otherwise specify extra settings in your SQL syntax, other connections do not see your data. Jose Antonio wrote: Hi! I am experiencing something weird using MySQL 4.1.8 with InnoDB tables. I have an application, let's call it A, that is monitoring the data that is available in the database. The data is inserted in the database by a different application, let's call it B. The problem is the following: A starts a connection with MySQL and all the data inserted until the connection time is available; however, the data that is inserted by B while A is running is not visible to A. If A is re-started all the data that was inserted by B is now available. A is a plotting program and B is a data importer process. Any clue on what may be going wrong? Thank you in advance. Jose. -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: actual size of a innodb tablespace
if you issue a show table status command from the mysql prompt, you'll get an estimate of how much free space exists in the files. Duhaime Johanne wrote: Hello Context: innodb per table. Is it possible to know the actual size of my innodb file? What part of the initial size (10M: autoextend) is actually used? I would like to have an idea of the data space progression? Per database (I have 3). Thank you in advance Johanne Duhaime IRCM courrier: [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert
insert/select has a different syntax than what you are trying to use. try insert into table_name (place, address, number) select stuff, stuff2, this from that where one=two i may yet have the syntax wrong, but its close. kalin mintchev wrote: hi all... i need some help. can somebody explain why is this insert/select not working? insert into the_db (place,address,number) values(stuff,stuff2,select this from that where one=two); i tried ()s around the 'select' too but it didn't work. thanks. -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migration of InnoDB 4.1.3 to 4.1.6
I see from the release notes that tables with timestamp column will need to be rebuilt. will the server automatically do this upon startup? thanks, jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
i don't think i missed any points raised by anyone in this discussion. email is often a difficult medium for technical issues. for most of our purposes, we run multiple queries in order to make sure we are actually getting the data we want. it makes the code simpler, easier to understand and less error prone. this was one example where conceptually we thought it should be able to be done -- and it can, if you use a nested select. thanks again to all for helped. we're on to bigger and better things... jeff Michael Stassen wrote: Jeff Mathis wrote: well, obviously some, if not all, of what you are saying is true. your I don't believe I said anything untrue. Did you have something in mind? table example below though is not the same as mine. My table stores time series data. for every symbol, there are a series of rows all with different dates. there is a unique constraint on the combination of symbol and close_date. so, for every symbol, there is one and only one maximum date. ... You've missed my point. My example table wasn't meant to be equivalent to yours. Rather, it was a simple example to illustrate the idea that the MAX() function does not look for the row with the largest value. Instead, it tells you what the largest value is with no reference to row. That is, it is an aggregate function for use with GROUP BY. In other words, it is a summary statistic, not a data point. Now, you may know that in your particular situation there will be only one row for each group's max date, but the MAX() function doesn't know that. Even if it did (because your dates are unique), it still wouldn't make sense for MAX() to think in terms of finding a row, because it is perfectly reasonable to ask for the MIN() in the same query. Look at my example again. I asked for the max, the min, and the average. Even if we change that to use your table rather than mine, which row should be pulled? The one with the max date, the one with the min date, or the one with the average date (which probably doesn't even exist)? ... i want that row and the name field it contains. Right, I got that. your example using the subquery works. when we used the subquery approach, we forgot to include the equivalent of t1.symbol = t2.symbol. I'm glad it worked. I was confident that it would. if we use: select max(close_date), symbol, name from TD where symbol in (quoted char string) group by symbol, name order by symbol; we end up getting multiple rows for each symbol if the names change over time. but that's ok for now -- we can parse the query output within our application and get the one row with the most recent date. Right. Adding name to the GROUP BY makes it legitimate to have name in the list of columns to select, but this query doesn't do what you want. It gives the maximum close_date for each symbol-name combination. As you say, you can parse the results to find the max close_date for each symbol subsection, but why would you do that when you already have a query which gives exactly the result you want? what we want to get is conceptually simple, but perhaps not so in terms of SQL. It's easy to say, but describe how to do it: For a given symbol, you have to look at all the close_date values to find the max, then you have to find the row with that value; or sort by close_date, then take the row at the high end of the sorted list; or compare the rows two at a time, storing the rest of the row for the winner of each comparison. They all amount to the same thing: a 2 step process. Those two steps are accomplished by the 2 queries in the temp table method, or by the query + subquery. Doing this in SQL, however, is tricky enough that yours is a frequently asked question. jeff Michael Michael Stassen wrote: No, Shawn's answer is correct. You are starting from a false assumption. You are expecting that MAX(closedate) corresponds to a row. It does not. Consider the table Table=stuff: sym val note --- --- AAA 2 one AAA 2 two AAA 4 three AAA 6 four AAA 12 five AAA 7 six AAA 12 seven BBB 1 eight BBB 2 nine BBB 3 ten Now consider the query SELECT sym, note, MAX(val), MIN(val), AVG(val) FROM stuff GROUP BY sym; Which row should be returned for sym='AAA'? Do you see the problem? It is clear that, for sym=AAA, MIN(val) is 2, MAX(val) is 12, and AVG(val) is about 6.43. Which row is that? The answer is that it's not a row. MAX(), MIN(), and AVG() are aggregate functions. They do not return rows, they return summary stats about a set of rows. Many dbs wouldn't even allow a query like that, because we are asking for a column not included in the GROUP BY. Mysql allows that, but the manual warns that it is pointless to do so if the extra column does not have a unique value per group http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html. Finding the notes which correspond to the maximum val
simple but frustrating query
hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
in the query.
for example
select max(close_date), symbol, name from TD where symbol in
('aa','bb','cc','dd','ee')
in fact this is the real problem. for a single value of symbol, we can
do this query. but we want to feed in a list of values for symbol
something like this:
select max(close_date), symbol, name from TD where symbol in
(list of
characters) group by symbol;
this returns the max close_date value, but the name that is
returned is
garbage and seems to point to the earliest row in the table.
any suggestions?
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
I'll be more explicit:
select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol, name order by symbol;
returns
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2002-05-03 | bb | drugs |
| 2002-02-05 | bb | medprovr|
| 2004-10-05 | cc | biotech |
| 2002-05-03 | cc | drugs |
| 2002-02-05 | cc | infosvcs|
+-++-+
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs|
+-++-+
which is wrong. what we want is
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
but we can't seem to fomrulate the query.
Jeff Mathis wrote:
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
in the query.
for example
select max(close_date), symbol, name from TD where symbol in
('aa','bb','cc','dd','ee')
in fact this is the real problem. for a single value of symbol, we can
do this query. but we want to feed in a list of values for symbol
something like this:
select max(close_date), symbol, name from TD where symbol in (list
of characters) group by symbol;
this returns the max close_date value, but the name that is returned
is garbage and seems to point to the earliest row in the table.
any suggestions?
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
we really don't want to issue two queries. this should be able to be done in one, and without using temp tables, but maybe not. thanks for the help though jeff [EMAIL PROTECTED] wrote: It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD X-Mozilla-Status: 8000 X-Mozilla-Status2: WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts X-Mozilla-Status: 8000 X-Mozilla-Status2: INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete data from more than one table
if your schema can be set up to use pk/fk constraints, you can add an on delete cascade statement. if not, then its maybe safer to delete table by table anyway. Jerry Swanson wrote: I can delete data for one table with no problem: delete from table; I need to delete data for more than one table. I tried to run this query: delete from account, survey; //But the query crashes. Any ideas how to delete data for more than one query. TH -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
4.1.3 and the innodb engine on solaris 5.8
Brad Eacker wrote:
Jeff Mathis writes:
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs|
+-++-+
Jeff,
What version of MySQL are you using? I ran your query on
4.0.18 and got a different answer...
mysql select max(close_date), symbol, name from TD
- where symbol in ('aa','bb','cc') group by symbol;
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-++-+
3 rows in set (0.01 sec)
Brad Eacker ([EMAIL PROTECTED])
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple but frustrating query
well, obviously some, if not all, of what you are saying is true. your table example below though is not the same as mine. My table stores time series data. for every symbol, there are a series of rows all with different dates. there is a unique constraint on the combination of symbol and close_date. so, for every symbol, there is one and only one maximum date. i want that row and the name field it contains. your example using the subsquery works. when we used the subquery approach, we forgot to include the equivalent of t1.symbol = t2.symbol. if we use: select max(close_date), symbol, name from TD where symbol in (quoted char string) group by symbol, name order by symbol; we end up getting multiple rows for each symbol if the names change over time. but thats ok for now -- we can parse the query output within our application and get the one row with the most recent date. what we want to get is conceptually simple, but perhaps not so in terms of SQL. jeff Michael Stassen wrote: No, Shawn's answer is correct. You are starting from a false assumption. You are expecting that MAX(closedate) corresponds to a row. It does not. Consider the table Table=stuff: sym val note --- --- AAA 2 one AAA 2 two AAA 4 three AAA 6 four AAA 12 five AAA 7 six AAA 12 seven BBB 1 eight BBB 2 nine BBB 3 ten Now consider the query SELECT sym, note, MAX(val), MIN(val), AVG(val) FROM stuff GROUP BY sym; Which row should be returned for sym='AAA'? Do you see the problem? It is clear that, for sym=AAA, MIN(val) is 2, MAX(val) is 12, and AVG(val) is about 6.43. Which row is that? The answer is that it's not a row. MAX(), MIN(), and AVG() are aggregate functions. They do not return rows, they return summary stats about a set of rows. Many dbs wouldn't even allow a query like that, because we are asking for a column not included in the GROUP BY. Mysql allows that, but the manual warns that it is pointless to do so if the extra column does not have a unique value per group http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html. Finding the notes which correspond to the maximum val is fundamentally a 2 step process. First you must find the maximum val, then you must find the rows(s) which have that val. This is what Shawn was telling you. The manual suggests 3 ways to solve this problem http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. The most efficient solution, and the one that works in all versions of mysql, is to use a temporary table, as Shawn described. As you have mysql 4.1, you could accomplish the same thing with a subquery. In your case, that would be SELECT close_date, symbol, name FROM TD t1 WHERE close_date = (SELECT MAX(t2.close_date) FROM TD t2 WHERE t1.symbol = t2.symbol) AND symbol IN (list of characters); Note that this is still really a 2 step process. The subquery handles the first step, finding the max close_date, while the parent query handles step 2, finding the matching rows. There is a third way, the MAX-CONCAT trick. It does it in one query without subqueries, and is very inefficient. See the manual for the details. In other words, this wasn't such a simple query, after all. Michael Jeff Mathis wrote: we really don't want to issue two queries. this should be able to be done in one, and without using temp tables, but maybe not. thanks for the help though jeff [EMAIL PROTECTED] wrote: It takes two steps: first determine the max(closedate) for each symbol, then use those results to get the name field. You could do this with a subquery (both steps in the one statement) because you are using a version of MySQL 4.0.0 but here is a temp table implementation that will work with just about anyone. CREATE TEMPORARY TABLE tmpSymbols SELECT symbol, max(close_date) as last_date FROM TD WHERE symbol in (list of symbols) GROUP BY symbol; SELECT ts.symbol, ts.last_date, TD.name FROM tmpSymbols ts INNER JOIN TD ON TD.symbol = ts.symbol AND TD.close_date = ts.last_date; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Mathis [EMAIL PROTECTED] wrote on 10/14/2004 02:22:32 PM: hello query gurus. we have a table TD with the following columns: close_date symbol name close_date is just a date field there is a unique constraint on the combination of close_date and symbol. what we want is the value for the name field corresponding to the row with the most recent close_date. something like this: select max(close_date), symbol, name from TD where symbol in (list of characters) group by symbol; this returns the max close_date value, but the name that is returned is garbage and seems to point to the earliest row in the table. any suggestions? jeff ps we're using mysql 4.1.3 with the innodb engine -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL
Re: ResultSet NotUpdatabelProblem
Its my impression that prepared statements are buggy with innodb tables. i've recently filed a bug, at heikki's request, where some buffer on the mysql server periodically flushes itself or otherwise is erased, with the result that the sql executed by a prepared statement is not what you think it is. i'm eagerly waiting fixes for this myself. jeff Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Hello: I have recently posted the message attached at the bottom of this one to the mailing list. Since then, I have continued to work the sporadic and troublesome errors that are described in that attached message on otherwise perfectly working and proven code. I now have some insights that I would like to share with the group and solicit their thoughts and ideas as to what the root cause(s) may be. [snip] Todd, Would you mind filing a bug report with a testcase at http://bugs.mysql.com/ ? This issue would get the proper attention it needs if you use that 'channel'. Thanks! -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBW7BFtvXNTca6JD8RAhP0AKDE4i8+lj5CCFGitdo41mW/U1t3tgCeMTII 7/QoWU8myY2J1FZFQoBRX9E= =8Mac -END PGP SIGNATURE- -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: increasing mysql/table performance..
one suggestion would be to get the latest mysql performance tuning book from o'reilly. its pretty good. bruce wrote: hi... i've got a basic question (with probably many answers) i'm creating a php/web app that uses mysql tbls. i have a number of pages that do various selects using 'left joins'/'right joins'/etc... i'm getting to the point where most of the basic logic works. now i want to start figuring out how to speed up the app/table interactions... i've read/seen information regarding indexes within a table. i'm curious as to what i can do to speed up the response time/tbl interactions for the users thanks -bruce ps. if need to, i could provide sample sql statements/table defs... -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple DB design question
I've got a history table that performs a similar function. except in my
case I can have more than 1 active row. I put in an is_active column
and defined the type as a bool. an enum is actually a String in mysql,
which i didn't want to deal with. This table has only a few thousand
rows, so performance is not impacted at all.
jeff
sean c peters wrote:
I have some data that is stored by the year it is related to. So I have one
table that stores the Year the data is related to, among other things. At any
given time, 1 year is considered the 'active year', and the rest are
considered inactive.
The table is something like:
CREATE TABLE Data_Info
Data_Info_IDINT,
YearINT,
...
So my question is how do i best store which year is active. 2 designs come to
mind:
1) add a column such as:
Status ENUM('Active', 'Inactive')
and adjust accordingly as the active year changes.
2) have a separate table:
CREATE TABLE Active_Data (
Data_Info_IDINT,
Key (Data_Info_ID),
FOREIGN KEY (Data_ID) References Data_Info(Data_Info_ID)
);
With design 1, i need to make sure that only 1 record is ever set as 'Active'.
With design 2, there will only ever be 1 record in the Active_Data table.
Neither idea seems very good to me.
Any suggestions?
thanks much
sean peters
[EMAIL PROTECTED]
mysql, query
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: time zone leap seconds
i seemed to have gotten around it by manually sourcing the script to update the password field, which had the create table statements in it, and then loading time zones based on our system time zone files. the manual section on upgrading from 4.0 to 4.1 did explain this -- i just had to dig a little bit to find it. a frustrating issue is that all of my timestamp columns got monged (we're using innodb tables). all years got set to the year 2036. in poking around on the java list group I got the impression this is a known issue with innodb tables when migrating from 4.0.x to 4.1.x :( oh well. its not that mission critical, and I executed a database wide update to set all timestamp columns to current_timestamp(). at least i'll know when we did the upgrade. jeff Victor Pendleton wrote: There is are five new time_% tables. You could install 4.1.3 in a clean area, export the tables and import the tables into the upgraded environment. You could also export your 4.0.x data and import this data into the newly created 4.1.3 environment. -Original Message- From: Jeff Mathis To: mysql Sent: 8/4/04 11:14 AM Subject: time zone leap seconds we just upgraded from 4.0.4 to 4.1.3, and are getting this warning. is there a script somewhere we can run to create the alleged missing time zone table? 040804 10:09:49 Warning: Can't open time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them thanks jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with 4.1.3
after upgrading from 4.0.4 to 4.1.3, we noticed that some of our float(11,9) columns refused to store anything other than the numer -100 or the number 100, even though the insert or update sql clearly specifies other numbers. We've tried dropping the table and reloading the data -- same problem. we're using Connector/J to load the data. is this a know issue? jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with 4.1.3
after lokking at this, it appears that our float(11,9) columns cannot store an number larger than 100 or smaller than -100. the database is rounding the number! If we insert numbers -100 x 100, then its fine. is there a configuration setting somewhere, or is this a known bug? thanks jeff Jeff Mathis wrote: after upgrading from 4.0.4 to 4.1.3, we noticed that some of our float(11,9) columns refused to store anything other than the numer -100 or the number 100, even though the insert or update sql clearly specifies other numbers. We've tried dropping the table and reloading the data -- same problem. we're using Connector/J to load the data. is this a know issue? jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with 4.1.3
well, it appears that you are quite correct. changing the column definition to float(11,3) for example now does the correct thing. sean c peters wrote: This is not a bug, its behaving exactly as it should. When you specify a float(11,9) - you're saying an 11 digit number where 9 are after the decimal, so only 2 digits are allowed before the decimal. sean peters [EMAIL PROTECTED] On Thursday 05 August 2004 15:47, Jeff Mathis wrote: after lokking at this, it appears that our float(11,9) columns cannot store an number larger than 100 or smaller than -100. the database is rounding the number! If we insert numbers -100 x 100, then its fine. is there a configuration setting somewhere, or is this a known bug? thanks jeff Jeff Mathis wrote: after upgrading from 4.0.4 to 4.1.3, we noticed that some of our float(11,9) columns refused to store anything other than the numer -100 or the number 100, even though the insert or update sql clearly specifies other numbers. We've tried dropping the table and reloading the data -- same problem. we're using Connector/J to load the data. is this a know issue? jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with 4.1.3
yes indeed. I will have to change my column definitions. However, this behavior was not the case with 4.0.4. What seemed to be going on with that branch was I could have an 11 digit number, with a maximum of 9 digits behind the decimal. so numbers like 100493.43 were fine. I'm assuming that this was a bug that was then fixed, and I just happened to get nipped by it. jeff Dan Nelson wrote: In the last episode (Aug 05), Jeff Mathis said: after lokking at this, it appears that our float(11,9) columns cannot store an number larger than 100 or smaller than -100. the database is rounding the number! If we insert numbers -100 x 100, then its fine. is there a configuration setting somewhere, or is this a known bug? It's doing exactly what you asked for. `FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]' `M' is the display width and `D' is the number of decimals. You asked for a field 11 digits wide, with 9 of them being to the right of the decimal point. 11-9 = 2, so you really should only be able to store -99 through 99, since 100 is 3 digits and puts you over the edge. So you did find a mysql bug, but not the one you thought you had :) mysql create table test ( f1 float(11,9) ); Query OK, 0 rows affected (0.07 sec) mysql insert into test values (1.1234567890123),(100.1234567890123),(200.123456789); Query OK, 3 rows affected, 2 warnings (0.09 sec) Records: 3 Duplicates: 0 Warnings: 2 mysql show warnings; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1264 | Data truncated; out of range for column 'f1' at row 2 | | Warning | 1264 | Data truncated; out of range for column 'f1' at row 3 | +-+--+---+ 2 rows in set (0.00 sec) mysql select * from test; +---+ | f1| +---+ | 1.123456836 | | 100.0 | | 100.0 | +---+ 3 rows in set (0.00 sec) This odd rounding is due to the low precision of a 'float' type (usually around 6 decimal digits). -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
time zone leap seconds
we just upgraded from 4.0.4 to 4.1.3, and are getting this warning. is there a script somewhere we can run to create the alleged missing time zone table? 040804 10:09:49 Warning: Can't open time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them thanks jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. you cannot overrun your disk. jeff [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying serveral databases (Views?)
in case someone hasn't answered you yet.. do you know that you can specify a database.tablename.column syntax in your queries to go across multiple databases? Daniel Ek wrote: Dear list, I wish to know if anyone have any experience in querying several databases at the same time. Today the company that I work for have about three different databases and I really feel that's fine because of the logic around it. We have one Customer database with customerData table, CustomerProducts table and so on, and two other Productspecific ones. I feel the information logic in having multiple databases are obvious but are there any way to use like pgsql views over several databases in Mysql? If not, can anyone please advice me to either when such a function will be implemented, or other solutions to the problem? And with that I don't mean the make more connections fix, since I don't feel it's a neat way to solve it. Thanks in advance Regards Daniel Ek -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hot innodb backup options
this is just my $0.02, but I would think $500/yr is certainly worth it. you'll get a fully supported product, and the money goes to a good cause. jeff Kevin Williams wrote: All, I have a database where the tables are InnoDB. I am working on the backup procedure, and would like to implement a process where the database is backed up without having to shut it down (live, or hot backup). Reading the documentation, it appears the only option I have is to purchase a solution from InnoDB ($500/yr). Is there a free option out there to do this? Thank you, Kevin Williams -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
but there are ways to get this value. The Java API handles this case just fine, and I'd be willing to bet other API's do it as well. here's some simple code to do with the Java API: String sql = some insert sql statement; Statement stmt = getConnection().createStatement(); stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); setId(rs.getInt(1)); -- this is the auto_generated row id stmt.close(); Paul DuBois wrote: At 17:02 + 2/24/04, David Scott wrote: Hi list peeps In many of my projects I have the need to insert a new record into a table and then later on in the script update this record, Right now I am doing this by doing the insert, then immediately doing a Select to return the latest id (unique id) which I store later for my update. Is there any way I can do an insert and have it return the unique auto-assigned id? If you mean, can you have the insert statement itself return the ID, no. Insert statements don't return records. -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bigint support in MySql 4
sorry, I don't know that one. Never have used any version of mysql lower than 4.04. a suggestion might be to call your 4.0 column an unsigned bigint, or if possible an unsigned int if the numbers can fit in 32 bits. unsigned means its always positive. jeff ps. you may want top reply all so the rest of the group can comment. you sent it to me directly. [EMAIL PROTECTED] wrote: Thanks for your quick reply. I had another question. I have some tables with columns of tyoe bigint running on MySql 3.23. When i copied these tables on to MySql 4.0 the values of these columns are displayed negative. When I copy them back the values are correctly displayed. Could you tell me as to how I can handle this situation ? yes it does. we use a bigint(20) for example. [EMAIL PROTECTED] wrote: Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bigint support in MySql 4
yes it does. we use a bigint(20) for example. [EMAIL PROTECTED] wrote: Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
its possible it was never installed for some reason. did you install the
mysql binary? you may simply be running on defaults. at any rate, i'm
willing to bet the farm that when you get your system configured right,
it will behave as you expect.
is there a permission problem that is not allowing you to see the file?
what user runs mysqld?
there must be an example of a my.cnf file somewhere on the mysql
website. grab it, set up innodb data files, and if you want, log files.
good luck
jeff
Rhino wrote:
I tried adding that space after the closing parenthesis in both Create Table
statements; it made no difference at all.
You're probably right about the InnoDB support not being turned on. I read
the article about configuring my.cnf and wanted to try playing with the
settings but I'm darned if I can find the my.cnf file!
a) I have no file called /etc/my.cnf.
b) I think MySQL was installed from an RPM as a binary but I don't recall
for sure. I'm not sure though so I checked /usr/local/mysql/data: I have a
/usr/local but no mysql directory in /usr/local. I also checked
/usr/local/var: I have no var directory in /usr/local.
c) I have no idea what was specified with --defaults-extra-file= and have no
idea how to find out.
d) I have no file called .my.cnf in my home directory (/home/rhino).
Any idea where I can find my my.cnf file? (For what it's worth, I tried find
/ -name 'my.cnf' but got the message Permission denied. I'm not sure why
permission is denied; I don't use Linux very often and haven't used it much
in several months but I know the 'find' command worked last time I tried
it).
Anyway, if anyone could tell me how to find my.cnf and verify that InnoDB is
set up correctly, I'd appreciate it.
Rhino
- Original Message -
From: Jeff Mathis [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Friday, February 13, 2004 6:44 PM
Subject: Re: Newbie Question
might be as simple as putting a space after your closing parenthesis on
the create table statement.
either that, or your mysql install somehow doesn't have innodb table
support. have you edited your my.cnf file and enabled the innodb
parameters, specifically log and data files?
Rhino wrote:
I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to create a pair of InnoDB tables that are related to one another
via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--
use Sample;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
)Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
)Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values
('10', 'Christine', 'I', 'Haas','A00',5.00),
('20', 'Cliff', ' ', 'Jones', 'D11', 3.00),
('30', 'FK', ' ', 'Mistake', 'X99', 12345.67),
('40', 'Brad', ' ', 'Dean', 'E21', 35000.00);
---
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type MyISAM, *not* InnoDB. If my
tables really are MyISAM, then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at
least as I understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino
---
rhino1 AT sympatico DOT ca
If you want the best seat in the house, you'll have to move the cat.
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
well, somewhere the documentation for mysql indicates that you need mysql-max in order to get InnoDB support. we made a special point of making sure we got the mysql-max binary specifically because of InnoDB. as far as upgrades on your platform, thats now out of my league. I don't think I'm qualified to give you an answer. also, you may want to reply all -- you seem to be sending mail to me specifically. no doubt the rest of the list may want to comment. jeff Rhino wrote: By it (in your first sentence), I assume you mean InnoDB as opposed to MySQL. We've been using MySQL without difficulty for several months. I just checked with the system admin and he says that he installed the binary from an RPM. We are running Mandrake 9.1 - I thought we were using RedHat 9.2 but I was mistaken - and he says that he used the MySQL distribution that is available from Mandrake. However, it turns out that we *didn't* install the MySQL-Max RPM, only the Common RPM. [I can't find any mention of a Common RPM in the MySQL manual but Clive says it is probably Mandrake's re-bundling of one or more of the RPMs available from the MySQL download page.] On reading the RPM documentation, he found that the MySQL-Max RPM contains the InnoDB support. [That's something I did NOT find in the MySQL manual! All it says is that MySQL-Max offers additional capabilities without saying what those are. Aside to the people doing the documentation for MySQL: PLEASE indicate somewhere in the InnoDB and/or Installing chapters that the MySQL-Max RPM needs to be installed in order to get InnoDB support!!!] As further confirmation, we did searches on the server and could not find any file named 'my.cnf' or 'my.ini' anywhere on the server. So that must be the problem: if we simply install the MySQL-Max RPM, we should get our InnoDB support. Right? Now, some followup questions. 1. Can we simply install the MySQL-Max RPM without any further preparation or should we do database backups first? If we need to do backups, which approach should we use and why? The backups chapter lists several different approaches without discussing the pros and cons of each approach in any way. We don't have enough MySQL experience to know if we should be doing BACKUP TABLE, mysqldump, or mysqlhotcopy. Can someone clue us in? 2. Does the MySQL-Max RPM need to be at the same level as the other MySQL RPM - 4.0.11 - or can the MySQL-Max RPM be later, like 4.0.15? 3. I was advised in another post to upgrade to something newer than 4.0.11 as this is obsolete. Would we be okay to go to 4.0.15 or should we go with 4.0.17? Clive strongly prefers to use the Mandrake RPMs over the MySQL RPMs. According to the Mandrake docs, the Mandrake version of 4.0.15 seems is the latest stable version they have while the Mandrake version of 4.0.17 is a cooker, which appears to be a synonym for an alpha or beta. I'd rather stay with something stable than mess with alpha or beta code but if 4.0.15 is not significantly better than 4.0.11, I'd rather stay with 4.0.11 because it works fine and is less work than upgrading to a newer version. Rhino - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, February 16, 2004 11:15 AM Subject: Re: Newbie Question its possible it was never installed for some reason. did you install the mysql binary? you may simply be running on defaults. at any rate, i'm willing to bet the farm that when you get your system configured right, it will behave as you expect. is there a permission problem that is not allowing you to see the file? what user runs mysqld? there must be an example of a my.cnf file somewhere on the mysql website. grab it, set up innodb data files, and if you want, log files. good luck jeff Rhino wrote: I tried adding that space after the closing parenthesis in both Create Table statements; it made no difference at all. You're probably right about the InnoDB support not being turned on. I read the article about configuring my.cnf and wanted to try playing with the settings but I'm darned if I can find the my.cnf file! a) I have no file called /etc/my.cnf. b) I think MySQL was installed from an RPM as a binary but I don't recall for sure. I'm not sure though so I checked /usr/local/mysql/data: I have a /usr/local but no mysql directory in /usr/local. I also checked /usr/local/var: I have no var directory in /usr/local. c) I have no idea what was specified with --defaults-extra-file= and have no idea how to find out. d) I have no file called .my.cnf in my home directory (/home/rhino). Any idea where I can find my my.cnf file? (For what it's worth, I tried find / -name 'my.cnf' but got the message Permission denied. I'm not sure why permission is denied; I don't use Linux very often and haven't used it much in several months but I know the 'find' command worked last time I tried it). Anyway, if anyone could tell me how to find my.cnf and verify
Re: Newbie Question
here it is: http://www.mysql.com/news/article-111.html its also all over the mysql reference manual, especially in the InnoDB tables section. Rhino wrote: - Original Message - From: Jeff Mathis [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, February 16, 2004 1:18 PM Subject: Re: Newbie Question well, somewhere the documentation for mysql indicates that you need mysql-max in order to get InnoDB support. we made a special point of making sure we got the mysql-max binary specifically because of InnoDB. Really? Could you point it out? In the installing chapter, it only says mysql-max gives additional capabilites without enumerating them. I didn't see anything the InnoDB section saying that mysql-max was necessary but I didn't read every single word so I could have missed it as far as upgrades on your platform, thats now out of my league. I don't think I'm qualified to give you an answer. Fair enough. also, you may want to reply all -- you seem to be sending mail to me specifically. no doubt the rest of the list may want to comment. Sorry! I'm still getting used to doing Reply All instead of Reply. I'll get it eventually Rhino -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
might be as simple as putting a space after your closing parenthesis on
the create table statement.
either that, or your mysql install somehow doesn't have innodb table
support. have you edited your my.cnf file and enabled the innodb
parameters, specifically log and data files?
Rhino wrote:
I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to create a pair of InnoDB tables that are related to one another
via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--
use Sample;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
)Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
)Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values
('10', 'Christine', 'I', 'Haas','A00',5.00),
('20', 'Cliff', ' ', 'Jones', 'D11', 3.00),
('30', 'FK', ' ', 'Mistake', 'X99', 12345.67),
('40', 'Brad', ' ', 'Dean', 'E21', 35000.00);
---
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type MyISAM, *not* InnoDB. If my
tables really are MyISAM, then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at
least as I understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino
---
rhino1 AT sympatico DOT ca
If you want the best seat in the house, you'll have to move the cat.
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique IDs
as everyone has pointed out, using timestamps as a unique id was a design flaw. you should fix the problem using an auto-increment field. that said, can you change the column type you are currently using as a timestamp to be an auto-increment int field? the return type in both cases is technically an integer for mysql versions 4.1, but your API call may need to be changed nevertheless. my experience has always been to fix the problem right rather than do a hork, even if it means down time. jeff Craig Jackson wrote: On Thu, 2004-02-12 at 11:47, Keith C. Ivey wrote: Craig Jackson [EMAIL PROTECTED] wrote: I have a very large web app that uses timestamp for unique IDs. Everything was rolling fine until we started getting many users per second, causing some of the unique IDs to not be unique -- users were being assigned the same timestamp. Since the web app is so large we don't want to change the method of assigning IDs as it would create a major project. I don't understand. If you're getting many users per second, and your timestamps have 1-second resolution, how could you possibly solve the problem without changing the method of assigning IDs? Are the many users per second periods just short bursts, and you're really only getting several hundred users per day? If so, I guess you could keep waiting a second and trying the insert again, but that could lead to indefinite delays if traffic gets high. I think you've got to bite the bullet and change the unique ID to something that's actually unique -- even an AUTO_INCREMENT would work. Thanks for the speedy reply and I have already recommended auto_increment for the solution. We do need that quick fix until the problem is fixed. How would I go about making Mysql wait one second between inserts. We only get about 1000 hits per day, but they tend to be concentrated in short time intervals. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shrinking innodb datafiles?
someone will no doubt echo what I'm about to say. InnoDB files are created at startup. the files use all the disk you allocate to them in your my.cnf startup file. If you want smaller InnoDB files, specify a smaller file size in your my.cnf file, but I have a feeling thats not what you want to do... jeff Lawrence Smith wrote: I have recently dropped a database with a big innodb table in it - but the data file (ibdata1) failed to get smaller. It's pretty big (nearly 2GB) and I'd like to reduce it if possible. Is there any way to do this? Thx BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TINYINT(1) vs ENUM?
maybe one more particle of information is that enums are actually strings, not numbers, so you may have to do a conversion somewhere. this is a pain for us, so we use tiny ints. jeff Mike [tmw] wrote: I always gone with ENUMs b/c TINYINT(1) still allows you to insert a '2' into the field and the last idiot at my current job actually used '0' for 'true' in a some fields in some tables. I believe both would use 1 byte. Mike Paul Fine wrote: Normally for a column with 2 possible values, I would use TINYINT(1) and programmatically assign a value (ie. Yes/No) to 0/1. I assume that this is the correct: way? Now how about using ENUM instead? Is there any difference in overhead? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running without logfiles
i run the same configuration . I have the following settings in my /etc/my.cnf file innodb_log_archive=0 innodb_flush_log_at_trx_commit=0 #log-update = /export/disk1/mysql/logs/snow once you have the first set to 0, the other 2 may not matter. Arnoldus Th.J. Koeleman wrote: I am using Innodb Tables but I Like to run this database without creating any logfiles. Is there a way that Mysql doesn't create any logfiles at all . I can recover from a master system so this database can or is allowed to loose the data -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
kevin, i tend to think the consultant really just read something that microsoft sent him. it doesn't sound like he's qualified to suggest one database or another. We've been usinf mysql for a year now. We use InnoDB tables, which give us primary key/foreign key constraints and transactions. We've gotten around the lack of stored procedures by putting the necessary logic and checks into the application that inserts/updates the database. We have several tables with 8 million rows, and growing every day. updating rows on the big tables still shows approximately constant time performance. In general, we are extremely satisfied with the product, and have purchased a license (about $400 -- mainly so we can contribute to the cause). When 4.1 becomes stable, we will upgrade in order to get the sub-select capability. I came from an Oracle/Sybase background. Those products have features that mysql does not have, in particular DBA-specific tables, views, and triggers, but you may not need these features. happy to give you more information if you need it. jeff KEVIN ZEMBOWER wrote: Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: practical MySQL database size limits
our database is about 20 Gb and growing daily. so far, I still see nearly constant time query performance on tables with ~10M rows. I don't think mysql is limited by file size per se. Ari Davidow wrote: We're talking about storing binary files (images) inside a MySQL database to take advantage of the granularity and control we get over file access that way. But we already have 1.5GB, and that could lead to a very large database very quickly. What are people's experiences with large MySQL databases? What are the practical limits under Solaris 2.8? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. jeff Per Andreas Buer wrote: Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Paul DuBois wrote: At 12:40 PM -0600 9/19/03, Jeff Mathis wrote: this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. 4.0 went beta in 4.0.3, gamma in 4.0.6, stable in 4.0.12. 3.23 went beta in 3.23.20, gamma in 3.23.28, and stable in 3.23.31. 3.22: 3.22.4 / 3.22.14 / 3.22.17 So, it varies. It'll happen faster if people try the alphas and pound hard on them. :-) I wish we had the luxury. But, I may try and force the issue anyway. set up another instance. jeff -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing Java Objects
I wonder if you could serialize your object, and then store it in a blob column. I'd be interested to know ... Dennis Knol wrote: Hello, Is it possible to store Java objects in the mysql database? Kind regards, Dennis Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
does shutting down the database server and restarting it have the same effect? jeff Heikki Tuuri wrote: Per, I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL .. Subject: innodb and fragmentation From: Per Andreas Buer Date: Fri, 22 Aug 2003 09:39:58 +0200 Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb on delete cascade
I'm using mysql-max 4.04. This works. drop table if exists Parent; create table Parent ( id int not null auto_increment primary key ) type = InnoDB; drop table if exists Child; create table Child ( id int not null auto_increment primary key, parent_id int not null ) type = InnoDB; alter table Child add index(parent_id); alter table Child add constraint foreign key(parent_id) references Parent(id) on delete cascade; mysql insert into Parent values(1); Query OK, 1 row affected (0.04 sec) mysql insert into Parent values(2); Query OK, 1 row affected (0.04 sec) mysql insert into Parent values(3); Query OK, 1 row affected (0.03 sec) mysql select * from Parent; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) mysql insert into Child (parent_id) values (1); Query OK, 1 row affected (0.04 sec) mysql insert into Child (parent_id) values (2); Query OK, 1 row affected (0.04 sec) mysql insert into Child (parent_id) values (3); Query OK, 1 row affected (0.11 sec) mysql select * from Child; ++---+ | id | parent_id | ++---+ | 2 | 1 | | 3 | 2 | | 4 | 3 | ++---+ 3 rows in set (0.00 sec) mysql delete from Parent where id = 1; Query OK, 1 row affected (0.04 sec) mysql select * from Child; ++---+ | id | parent_id | ++---+ | 3 | 2 | | 4 | 3 | ++---+ 2 rows in set (0.00 sec) you see the cascade. the only thing I can suggest is have a look at your msql version, or use the syntax I have above... good luck R.Dobson wrote: mmm, i've just tried the example within the mysql docs: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE cascade ) TYPE=INNODB; Now, insert a couple of lies of data: mysql insert into parent values(1); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(2); Query OK, 1 row affected (0.00 sec) mysql insert into parent values(3); Query OK, 1 row affected (0.00 sec) mysql insert into child values(1,1); Query OK, 1 row affected (0.01 sec) mysql insert into child values(2,2); Query OK, 1 row affected (0.00 sec) mysql insert into child values(3,3); Query OK, 1 row affected (0.00 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |1 | 1 | |2 | 2 | |3 | 3 | +--+---+ 3 rows in set (0.00 sec) mysql select * from parent; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) When I come to try to delete some data from the parent table i'm getting errors as in: mysql delete from parent where id=1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails any thoughts? cheers,tia r Jeff Mathis wrote: I just looked at your table syntax. you've got two auto_increment pk columns. do you always have a 1:1 correspondence between the name and gene tables? would it not be better to have a gene_id column in name, put an index on it, and then issue: alter table name add foreign key(gene_id) references gene(id) on delete cascade; In fact, I'm not sure you can actually create the constraint as you currently describe it R.Dobson wrote: Hi, yes, I should have included in the first mail. They are: mysql show table status like 'gene%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-+-+--+---++-+-+++---+ | gene | InnoDB | Dynamic|0 | 0 | 16384 |NULL |0 | 0 | 1 | NULL| NULL| NULL || InnoDB free: 55296 kB | +--+++--++-+-+--+---++-+-+++---+ 1 row in set (0.00 sec) mysql show table status like 'name%'; +--+++--++-+-+--+---++-+-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length
Re: innodb on delete cascade
; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql delete from gene where id=9; Query OK, 1 row affected (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql select * from gene; Empty set (0.00 sec) The entry from name should be deleted as well? Check if table types are InnoDB with SHOW CREATE TABLE or SHOW TABLE STATUS statements. -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb on delete cascade
| ++--+-+ | 9 | hi | human | ++--+-+ 1 row in set (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql delete from gene where id=9; Query OK, 1 row affected (0.00 sec) mysql select * from name; +++--+---+--++-+ | id | other_name | other_symbol | refseq_ID | GO | locus_link | other_species_index | +++--+---+--++-+ | 9 | hi | human| i | i| i | i | +++--+---+--++-+ 1 row in set (0.00 sec) mysql select * from gene; Empty set (0.00 sec) The entry from name should be deleted as well? Check if table types are InnoDB with SHOW CREATE TABLE or SHOW TABLE STATUS statements. -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reindexing
what is the command to update InnoDB table indexes? jeff Brent Baisley wrote: You are referring to fragmentation. In this case I'm pretty sure it's just a matter of updating the indexes so that MySQL makes the correct assumptions about the distribution of data. MySQL assumes data values have an equal distribution in a database, updating the indexes will force MySQL to update it's assumptions and thus do a better job of optimizing searches. On Wednesday, August 6, 2003, at 12:17 PM, Jeff Mathis wrote: I was under the impression that InnoDB tables took care of this for you. You only need to be concerned if you add/delete repeatedly from anywhere but the end rows of the table. I'd like to know if I'm wrong about this. Adam Nelson wrote: I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reindexing
I was under the impression that InnoDB tables took care of this for you. You only need to be concerned if you add/delete repeatedly from anywhere but the end rows of the table. I'd like to know if I'm wrong about this. Adam Nelson wrote: I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Table By ID Ranges
how about UPDATE testTable set testField='' WHERE test_id = 100 and test_id = 1000; Mike Doanh Tran wrote: Hi, How do i use the UPDATE statement to update a range of Primary Key id numbers. (let say 100 through 1000). Example, UPDATE testTable set testField='' WHERE test_id in (100:1000); Thanks in advance for any comments, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Triggers
the main reason I might suggest NOT to do this is a data integrity one. As long as someone can go in through a mysql prompt and touch data, PHP or ASP will do you no good. Data integrity checks should, in so far as possible, always be put into the schema, and not in an API. just my two cents Primaria Falticeni wrote: Why don't you use PHP or ASP functions on the server-side in Web page code. They act like a trigger and you don't need MySQL 5.0 for them. Remember that you can benefit from the both servers: MySQL and Web server. - Original Message - From: Kerry Colligan [EMAIL PROTECTED] To: Sent: Tuesday, June 17, 2003 9:10 PM Subject: Triggers I see that 5.x will support triggers. Does anyone have any advice for implementation of triggers on the DB? I will need to be able to utilize them from web applications AND from command-line type interface. Any suggestions welcome at this point. (RH 7.3, MySQL 3.23.56 presently) Thanks -- Kerry -- 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] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
I have no idea. sorry. Sibananda Sahoo wrote: Thanks for your reply. Could you please tell me is it possible to achieve foreign key constraint in MySQL 3.23.56. Rgds, Sibananda --- Jeff Mathis [EMAIL PROTECTED] wrote: i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help creating foreign keys
the syntax for foreign key creation is different than what you have. You need an alter table statement. create table bar ( bar_idint unsigned auto_increment primary key, foo_idint unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help creating foreign keys
yes, quite right. thanks for pointing that out. jeff Kevin Fries wrote: In InnoDB you *must* put an index on foreign keys. It doesn't do this for you automatically. You can do it in one statement: create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, constraint bar_pk primary key (bar_id), index (foo_id), foreign key (foo_id) references foo (foo_id) ) type=innodb; -Original Message- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:09 AM To: Justin Scheiber Cc: [EMAIL PROTECTED] Subject: Re: help creating foreign keys the syntax for foreign key creation is different than what you have. You need an alter table statement. create table bar ( bar_idint unsigned auto_increment primary key, foo_idint unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key/foreign key constraints with InnoDB
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
Stefan Hinz wrote:
Jeff,
I'm wondering if its somehow possible to create a pk/fk constraint for
the table below
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
if table_name is 'TabA', then I want to make sure the row exists in
TabA. Likewise if table_name is 'TabB'
You can find the syntax for MySQL / InnoDB and a good example here:
http://www.mysql.com/doc/en/SEC463.html
To avoid trouble, consider this sentence from that page:
Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.
The example on that page, however, shows exactly how you'd do that.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
[filter fodder: sql, mysql, query]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key/foreign key constraints with InnoDB
thanks for the advice Stephen. I'll admit though I am somewhat loathe to
adding an artifical row in the other tables, but it may not be a bad way
to go. In the past, I've written triggers to do this kind of check, but
mysql doesn't yet support triggers.
what I ended up doing is carefully rethinking the schema. It turns out
we came up with a better design that does not require the table_name,
table_id linking mechanism. We just link into one table, which of course
presents no problems in creating a foreign key constraint.
jeff
Stephen Giese wrote:
Jeff,
We faced a similar challenge in an application: Each child record must have
a parent in one of two tables, TabA or TabB, but not both. We solved it
by adding a foreign-key field for each possible parent in the child
table. Each column can have the FK constraint. We were using Sybase, but
I translate the DDL to MySQL below.
create table Example (
id int not null auto_increment primary key,
tableA_id int not null,
tableB_id int not null
) type = InnoDB;
alter table Example add index (tableA_id);
alter table Example add index (tableB_id);
alter table Example add constraint foreign key (tableA_id) references TabA(id);
alter table Example add constraint foreign key (tableB_id) references TabB(id);
However, you will notice that each child record now must have a parent
record in BOTH parent tables. We used our front end to enforce a rule that
the one of the two foreign key fields is always -1 (or some other default
value). Then we insert a record into each parent with a key value that
matches our default (-1).
This method is not as easily extensible as your model, but perhaps that's
OK. In SQL to join the parent and child you must decide which parent to
join based on which FK column has the non-default value.
You might be able to come up with a DB rule to ensure that exactly one of
the FK values is non-default.
Stephe
At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
host-bin files
we're using mysql-max4.04 on solaris 2.8. all tables are InnoDB tables. I see in the data directory a series of host-bin files. I'm assuming these are the index files. Is this correct? the sum total size of these files are larger than the files I've allocated for data. How can I manage these files? What happens if I delete them How can I tell if any of them are stale? thanks -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
Re: host-bin files
great. the files are exactly as you describe. we are doing transactions when we load. auto commit is off, and the loader commits rows after a certain number has hit the database. however, if I understand you correctly, once a load is complete and everything has been committed, we are free to delete these files if we want. database backup and recovery is a separate issue? thanks for your response jeff Jeff Kilpatrick wrote: Jeff- Actually, thsee sound like the binary log files used primarily for replication. If you're on host doctorpants, they'll be doctorpants-bin.001 doctorpants-bin.002 doctorpants-bin.index and so forth. If you don't want a write query log and aren't doing transactions, remove the binlog option from my.cnf and do a mysql RESET MASTER; to remove the files. They contain none of your data, just what it took to get there. You can use mysqlbinlog to translate them to SQL files. -jeff On Fri, 2003-03-21 at 12:52, Jeff Mathis wrote: we're using mysql-max4.04 on solaris 2.8. all tables are InnoDB tables. I see in the data directory a series of host-bin files. I'm assuming these are the index files. Is this correct? the sum total size of these files are larger than the files I've allocated for data. How can I manage these files? What happens if I delete them How can I tell if any of them are stale? thanks - 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 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
matlab interface
hello, does anyone know of a good mysql-matlab release 13 interface? the standard database connection capability within matlab is a really poor implementation based on JDBC. JDBC is fine, but not how the matlab people use it. thanks jeff -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
Re: Mysql Innodb performance slow
I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better. for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. (2) drop all indexes on your table(s). rebuild them after loading (this alone can give orders of magnitude improvement) (3) commit every 10,000 records or so. I can load several million rows into our InnoDB tables in a few minutes. good luck jeff Richard Clarke wrote: Jeroen, Two things are likely to make this umpteen times faster. a) Commit the insert transaction every.. say 1000 records? b) use mysql's extended insert statement, insert into mytable values (row1_field1,row1_field2),(row2_field1,row2_field2),(?,?),(?,?) etc etc Ric. - Original Message - From: Jeroen Geusebroek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 25, 2002 12:11 PM Subject: Mysql Innodb performance slow Hi There, We have currently an Interbase Database with millions and millions of rows which I would like to migrate to MySQL if possible to increase the speed. Transaction support is necessary, so I am using innoDB. When inserting 160K rows in the database (in an innoDB table) it takes about 700! seconds while the amount of same rows when inserted in a myisam table take about 100 seconds. Now probably this can be fine tuned (I hope), and would like to ask for some suggestions. Is anybody using innodb with this amount of rows? I'm curious of what the performance is. Is there something I should keep in mind when migrating? Kind regards, Jeroen Geusebroek - 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 - 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 -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
Re: Mysql Innodb performance slow
forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. I'm now using JDBC, and havne't explored this yet. I'm coming from the Oracle camp, and have only been using mysql for less than a month. so far, i find it very snappy. I do miss the ability to have stored procedures, triggers and views however. jeff Dan Nelson wrote: In the last episode (Oct 25), Jeff Mathis said: I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better. for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
show version problem
hello, I'm running version 4.0.3-beta-max-log on SunOS 5.8 (Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R) output from mysqlshow -V: mysqlshow Ver 9.4 Distrib 4.0.3-beta, for sun-solaris2.8 (sparc) I'm finding that executing either 'show variables' from a mysql session or 'mysqladmin -variables' from a shell prompt crashes the database. the database then restarts. this appears to be 100% repeatable on my install. Also, I cannot execute 'show table status from [tablename]'. All of my tables happen to be InnoDB tables. However, executing only 'show table status' or 'mysqlshow --status' works fine. any help you all can give wold be appreciated. thanks jeff mathis -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
error executing 'show variables' and 'show table status from [table]'
hello, I'm running version 4.0.3-beta-max-log on SunOS 5.8 (Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R) output from mysqlshow -V: mysqlshow Ver 9.4 Distrib 4.0.3-beta, for sun-solaris2.8 (sparc) I'm finding that executing either 'show variables' from a mysql session or 'mysqladmin -variables' from a shell prompt crashes the database. the database then restarts. this appears to be 100% repeatable on my install. Also, I cannot execute 'show table status from [tablename]'. All of my tables happen to be InnoDB tables. However, executing only 'show table status' or 'mysqlshow --status' works fine. any help you all can give wold be appreciated. thanks jeff mathis -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
