Re: Slow Query Question - Need help of Gurus.
Ben Dinnerville wrote:
You have a redundant clause in your query - the distinct is not needed when
you are doing a group by on the same field ('Call Svr Tag ID') - not sure
how the optimiser in MySQL will handle this. In some RDBMS's the duplicate
clause will be optimised down to 1 clause, so you wont cop a performance
hit, but just because you think an optimiser will do something is no reason
not to write the query right the first time - always makes it easier on the
optimiser and write the query with as few possible predicates and clauses as
possible.
DISTINCT is not a function you feed a column into. It is a SELECT keyword
which prevents duplicate rows. For example,
CREATE TABLE t (v1 int, v2 int);
INSERT INTO t VALUES (1, 1), (1, 2);
SELECT DISTINCT(v1), v2 FROM t;
+--+--+
| v1 | v2 |
+--+--+
|1 |1 |
|1 |2 |
+--+--+
2 rows in set (0.00 sec)
Once you add GROUP BY, you are guaranteed unique rows, one for each group,
so DISTINCT adds nothing.
What indexes do you have on the table?
You need an index on the 'Journal Create Date' (PS identifiers with spaces
in the name are bad practice!).
An index on the Call_Svc_Tag_ID column may also assist with the group by
function - you need to run an explain to see what indexes are needed.
The indexes were listed at the bottom of the original post. He already has
indexes on `Journal Create Date` and `Call Svc Tag ID`, but MySQL will only
use one index per table, so separate indexes won't help much. He should
definitely run an EXPLAIN. I expect EXPLAIN will list both as possible
keys, and which, if any, it picked. What is needed, I expect, is a
multi-column index on those 2 columns:
ALTER TABLE 31909_859552
ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`);
Then try again:
SELECT `Call Svc Tag ID`,
Count(*) as counter,
`Journal Create Date`
FROM 31909_859552
WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16'
AND `Call Svc Tag ID`'null'
GROUP BY `Call Svc Tag ID`
HAVING counter 3
ORDER BY counter;
Note that sorting by the count can't use an index, so it will be slower than
if you had ordered by `Call Svc Tag ID`.
Also, not sure how the optimiser and indexing in MySQL handles the between
predicate, but you might want to try doing a greater than equal to test and
a less than test in the place of the between test - eg
WHERE Journal_Create_Date = '2004-03-13' AND Journal_Create_Date
'2004-03-16'
MySQL handles BETWEEN just fine. If you think about it, you explicitly set
the range with BETWEEN, but the optimizer has to put the two inequalities
joined with AND together to get the same range. In other words, BETWEEN is
easier.
In answer to one of the other follow ups, a count() should not result in a
full table scan when there are other predicates in the query - they will be
resolved first and then the count() will be done over the temporary result
set.
Cheers,
Ben
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Hi All,
I have a huge Database which contains about 500,000 records, (will be
adding about 20-25k records every week)
I need to run group queries and output the same to a web interface.
snip
- At present a select takes anywhere from 20 seconds to 10 minutes on
such queries.
MySQL/MyISAM is designed to give fast SELECTS, so 10 minutes seems excessive.
What can I do?
Till now we were using MS Access, and no web based interface.
Running a normal MySQL 4.0 install on windows 2000, 256 MB RAM, 2.6 GHz
P4 NON-HT
About 10 users at a time would be connecting initially.
I'm no Win2000 expert, but that doesn't sound like enough RAM, especially if
the web server is on the same machine. Ideally, you'd like your data cached
in RAM, but this one table appears to be 100 Mb or so, not even counting the
indexes. With a query taking 10 minutes, I wonder if you're running out of
memory and thrashing the disk.
I know theres a lot of tweaking possible with MySQL.
Please advise, because I would need a lot of such queries, fetching
through JSP, and displaying them in a presentable form.
Table Structure is as Follows:
Journal Created By varchar(255) No
Case Substatus ID varchar(255) Yes NULL
Case Status ID varchar(255) Yes NULL
Call Rep Employee Name varchar(255) Yes NULL
Call Rep Employee ID varchar(10) Yes NULL
Call Rep DPS Login ID varchar(10) Yes NULL
Call Rep Profile ID varchar(15) Yes NULL
Call Rep Section Code varchar(15) Yes NULL
Call Rep Country Code char(3) Yes NULL
Journal Create Date date No -00-00
Journal Create Time time Yes NULL
Call Svc Tag ID varchar(255) Yes NULL
Order Num of Svc Tag varchar(255) Yes NULL
Indexes:
www.SQL-Scripts.com web site
Hello, Teh other day I posted an annoncement about my new web site www.SQL-Scripts.Com . It is a place were you can find SQL scripts for database system. You can even lodge your own scripts and help someone out there. I have started a monthly contest on the site now. The first people to lodge 20 new quality scripts in one month get a pick of the software prizes on offer. Currently we have one prize for Microsoft SQL , One for MySQL , One for PostgreSQL and a new prize coming soon for Access. To check out the details of the contest please go to www.sql-scripts.com The details are on the main page. Thank-you for your time webmaster www.sql-scripts.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Query Question - Need help of Gurus.
The indexes were listed at the bottom of the original post. Woops, didnt see that far down, should have scrolled a little further :) What is needed, I expect, is a multi-column index on those 2 columns: ALTER TABLE 31909_859552 ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`); Definatly get an index across all queried fields, especially in this case where there are only 2 columns in the result set, you may be able to avoid hitting the data leaf's of the table all together and retrieve all info from the index alone, saving you the added IO on the data leaf's Then try again: SELECT `Call Svc Tag ID`, Count(*) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' AND `Call Svc Tag ID`'null' GROUP BY `Call Svc Tag ID` HAVING counter 3 ORDER BY counter; The count(*) will be causing some havoc here, as all columns in the underlying table(s) will have to be read in, negating any direct hits to the index's and causing a lot more IO than is needed. Change it to a count on one of the columns in the result set or simply a count(1) as counter - will give you the same result without any IO. Note that sorting by the count can't use an index, so it will be slower than if you had ordered by `Call Svc Tag ID`. This is something that will need testing. Ordering on a varchar(255) column (call svc tag ID) is going to be a lot more inefficient that ordering on a numeric column, especially when the ordering is happening on the reduced result set of the group by and predicate clauses, however the indexing available on the real column may negate any deficit. MySQL handles BETWEEN just fine. If you think about it, you explicitly set the range with BETWEEN, but the optimizer has to put the two inequalities joined with AND together to get the same range. In other words, BETWEEN is easier. I am sure that MySQL does handle the between just fine, but this is not to say that it will handle the between better that a gt / lt clause in this particular scenario. I know the between sounds like it might be easier, but internally the between is likely to be translated into a gt / lt clause by the optimiser anyhow as the only way to check if something is between 2 values is the check if it is greater than the min and less than the max. Just give the 2 different queries a go and see if there is any time difference. Also noticed that the table seems to be a fairly flat structure (hard to tell defiantly from looking at one sample). By this I mean that there are a lot of varchar(255)'s in the table, and looking at the naming of the columns, these seem to be id type fields. You might be able to gain some performance by normalising the database a little by moving some of the large varchar fields out to a related table and referencing by a numeric type id, this could greatly reduce the amount of data in your base table, especially considering the number of rows you are talking about storing and could give you an IO based performance increase. Once again, might need to test and play around a little with different models here. What kind of proportion of null values are you expecting for the Call Svc Tag ID column? Depending on this, you may or may not be better off restructuring the query to filter out the null values post grouping - eg as an extra for the having clause having counter 3 and call svc tag id null) - My mind is not super clear on this one at the moment, but am not sure if / how null values get indexed in MySQL (any feedback on this one anyone?) another one that maybe only testing will show if it helps or not. Also, are you looking for null values, or the word null in the column? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Managing users in different OS enviroments with MySQL
Hello, Me and a few others are developing om linux boxes running MySQL 5.0.0. We often create users with different access rights on our development boxes and then it often occours problems with users when our programs goes into production. I would like to find a common user enviroment for our database users. I was thinking that we could perhaps use our Active Directory service which is running on a windows server in our network. Would it be possible to avoid using MySQLs own mysql (mysql.user, mysql.db and so on) database and instead directly authenticate against our active directory? Or is that not a smart way to go? Any suggestions are welcome /Jonas
Re: Slow Query Question - Need help of Gurus.
Ben, - Original Message - From: Ben Dinnerville Sent: Monday, April 19, 2004 1:49 AM Subject: RE: Slow Query Question - Need help of Gurus. snip Then try again: SELECT `Call Svc Tag ID`, Count(*) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' AND `Call Svc Tag ID`'null' GROUP BY `Call Svc Tag ID` HAVING counter 3 ORDER BY counter; The count(*) will be causing some havoc here, as all columns in the underlying table(s) will have to be read in, negating any direct hits to the index's and causing a lot more IO than is needed. Change it to a count on one of the columns in the result set or simply a count(1) as counter - will give you the same result without any IO. COUNT(*) is not a problem. It won't cause the data file to be read if just the index can be used. EXPLAIN will show the same plan for COUNT(*) and COUNT(1). :-) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column name contains #
Zhang Yu [EMAIL PROTECTED] wrote: I am migrating an old Access database to MySQL. After migration, we'll keep using Access forms as front end, and MySQL as database. I experienced a strange problem when creating table with a column name id #, which is used by many queries in Access. My command is like this: create table t1 (`id #` int(11), `First Name` varchard(20) ); I get this error:ERROR 1064: You have an error in your SQL syntax near '' at line 1 Once I substituted # to ! or @, the command worked fine. Is there any constraints with '#' for the column name? What version of MySQL do you use? Forked fine for me: mysql create table t1 (`id #` int(11), `First Name` - varchar(20) ); Query OK, 0 rows affected (0.00 sec) mysql show create table t1\G *** 1. row *** Table: t1 Create Table: CREATE TABLE `t1` ( `id #` int(11) default NULL, `First Name` varchar(20) default NULL ) TYPE=MyISAM 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for user 'lrmt@iidcm063' ( Using password: YES)
hi all
I have installed mysql and I created an users call lrmt and password
is lrmt.
So from command line(mysql -u lrmt -p lrmt) i can enter to mysql with out
any problem and also with mysql -u root -p socket.
I used this command to give permission to user
GRANT ALL ON database.* TO [EMAIL PROTECTED] IDENTIFIED BY
'mysqlpassword'
GRANT ALL ON database.* TO mysqlusername\@'%' IDENTIFIED BY 'mysqlpassword'
But through script I cannot access the database it shows an error message
DBI connect ('lab:iidcm063.iidc.lucent.com','lrmt',...) failed: Access
denied for user: '[EMAIL PROTECTED]' (Using password: YES) at test.cgi line 20.
This script is running from apache. The apache log file displays the same
message when I run the script through Internet browser. And also I got this
error message when I run this script from command line..
Please help me to solve the problem. to access database through
script what permission I have to set in mysql databases and table (user, db,
host, table) ..
if possible just send me the query that I can execute on mysql
database to solve this problem.
Regards joby james
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row locking
thanx for all the replies. i found the solution with a single select and update, it was a simple update(how did i miss tht???) but what if i want to do a compelete transaction with row level locking in consideration, as thr can be time lag between the queries(select and update) so a row level locking. On Fri, 2004-04-16 at 21:42, Paul DuBois wrote: At 18:57 +0530 4/16/04, Sahil Aggarwal wrote: hi all, i have select statements in my java code which are followed by updates in my Innodb table. the desired behaviour shud have been check and then update but because of tomcat accepting connections the above becomes check check update update i dont want to set the table locking, as it will make the system slower how can i set exclusive row locking until the update is finished? Have you tried using SELECT ... FOR UPDATE? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Sahil Aggarwal www.vfirst.com
Re: Learning curve
I hate to say this, but you may want to look into FileMaker if you don't know a lot about databases and programming. You'll be able to get things up to speed a lot quicker and other users would be able to learn it fairly easily to help you out. I often use FileMaker to prototype a database and then create the finished product in MySQL/PHP. It can handle you pictures and creating a web interface is just a button click away. On Apr 15, 2004, at 6:06 PM, Mike T. Caskey wrote: My problem: My databases are single-user systems for use in OpenOffice.org and would be difficult to roll them out to my entire team. I don't want to install OO.o on everyones computer and I don't want to learn MS Access as it is known for being a temporary solution. So I need something that can keep up with the times and can be rolled out easily (web interface?). I also need to be able to append scanned images to records (PDF or JPEG?). This is all pretty complex and I'm definitely not technically equipped to create this just yet. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spatial woes
hi - Ive posted this before but not had a satisfactory solution... Im trying to load spatial data as OGC WKT into a geometry field but a number of rows consistently fail. I can se no reaspn for this other than their length - does anyone know what the size limit for insertion into a geometry field is (Im looking at polygons failing anywhere between 140K to 50K)... Ive tried on msql on both a unix box and a win xp machien and get the same failures so I guess its something that Im missing ?? thoughts appreciated james, edinburgh To err is human... to programme is folly
key_buffer_size and INNODB
Is the key_buffer_size server variable useful for tuning databases that only have innodb tables or do I need to use the innodb_buffer_pool_size variable for this? Thx, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Load Problem
Hi, Apologies in advance for the large post but I've tried to explain my problem as much as I can. I'm having load issues with my InnoDB server and am not sure what I should try to resolve this problem. The machine is redhat Linux 7.2 with dual processors, 4GB ram and about 32GB disk space. The MySQL version is 4.0.13. The machine is used purely with InnoDB tables and has a few very large tables acting as cache data. I've allocated 10 data files each 2Gb, when busy the files are between 40% and 60% used. I have clients reading and writing to the tables constantly and these clients do not see any performance degradation which I'm really happy about. But every 10 minutes I run a cron job that deletes expired/duplicated records from the tables. This process seems to hammer the system. I have 3 tables, table A, B and C. Table A has a 1 to many relationship with table B and table B has a 1 to many relationship with table C. Table A is the master record table and holds the timestamp. So, this cron job does a select to get expired records, this query is very fast as retrieves anywhere between 5,000 and 50,000 records. For each expired record it then starts a transaction, deletes all records from C, deletes all records from B, deletes the master record from A and then ends the transaction. That's it. This process takes about 5 minutes if expiring a large number of records and can take the machines load average to about 10. Looking at top when this process runs I can see many mysql process but 1 or 2 are taking alot of CPU, 1 about 50% and 1 about 35%, sometimes the top one can go up as high as 90%. The my.cnf file for innodb is as follows innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 15M innodb_log_file_size = 256M innodb_log_buffer_size = 8M I've read the InnoDB tuning page ( http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html ), there are a few things I could try but I'd like to make sure that I'm on the right tracks. * Firstly I could put more queries into a single transaction. * Should I increase the innodb_buffer_pool_size ??, ( bare in mind there is a 2GB limit on linux/GLIBC ) * Change the innodb_flush_method to O_DSYNC Any advice on the steps I should take would be greatly appreciated. Please let me know if you require any additional information. Best Regards, Marvin Wright This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Question - Need help of Gurus.
Ben Dinnerville wrote: snip Note that sorting by the count can't use an index, so it will be slower than if you had ordered by `Call Svc Tag ID`. This is something that will need testing. Ordering on a varchar(255) column (call svc tag ID) is going to be a lot more inefficient than ordering on a numeric column, especially when the ordering is happening on the reduced result set of the group by and predicate clauses, however the indexing available on the real column may negate any deficit. The ordering happens on the reduced result set either way, so that isn't relevant here. The key point is the index. Without any index, it is certainly true that a varchar(255) should take longer to sort than an int, but in this case the varchar column is already sorted in the index, while the int is the result of a calculation. No additional work is needed to sort the varchar, while the int must be sorted. On the other hand, sorting ints is usually fast, so I don't think this should be a big factor. MySQL handles BETWEEN just fine. If you think about it, you explicitly set the range with BETWEEN, but the optimizer has to put the two inequalities joined with AND together to get the same range. In other words, BETWEEN is easier. I am sure that MySQL does handle the between just fine, but this is not to say that it will handle the between better that a gt / lt clause in this particular scenario. I know the between sounds like it might be easier, but internally the between is likely to be translated into a gt / lt clause by the optimiser anyhow as the only way to check if something is between 2 values is the check if it is greater than the min and less than the max. Just give the 2 different queries a go and see if there is any time difference. I won't pretend to be an expert on the internals of the optimizer, but I think you are missing the value of the index. Another way to tell if a value is between two others is to check its relative position in the index. Both the between and the 2 inequalities define a range. With the index, the optimizer need merely find the start and end of the range and then grab everything from the one to the other. I am certainly in favor of testing, but I'd be surprised if you saw a measurable difference between the two. Also noticed that the table seems to be a fairly flat structure (hard to tell definitely from looking at one sample). By this I mean that there are a lot of varchar(255)'s in the table, and looking at the naming of the columns, these seem to be id type fields. You might be able to gain some performance by normalising the database a little by moving some of the large varchar fields out to a related table and referencing by a numeric type id, this could greatly reduce the amount of data in your base table, especially considering the number of rows you are talking about storing and could give you an IO based performance increase. Once again, might need to test and play around a little with different models here. Excellent point. Normalizing would help a lot. Unless there are 500,000 Call Reps, there's a LOT of wasted space in this table. To make matters worse, the rows are variable length, so there's a penalty for wasted space. At the least, the Call Rep info should be in its own table, and the 6 Call Rep columns should be replaced with an int column containing the Call Rep key. Similarly, the Case Status and Substatus should be moved to their own table(s?). If at all possible, all tables should be fixed length (CHARs) of a reasonable size (will you ever really have a 255 char Call Rep Employee Name?) rather than variable length (VARCHARs). Usually, I'd say that disk is cheap, so go ahead and waste some space to save on time, but in this case, I expect we'll save so much space from normalizing that even changing VARCHAR to CHAR we'll still end up ahead spacewise. Dropping any unused indexes could also help, in general. For example, unless we need to select based on time of day without regard to date, the index on `Journal Create Time` is unlikely to be used. In that case drop both date and time indexes and replace them with one multicolumn index. snip Also, are you looking for null values, or the word null in the column? I noticed that too and forgot to mention it. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect the end of a replication cycle?
Hello everyone, Sorry for the delay in responding but I had to be out of town for the last 4 days and didn't have access to my mail. Here is a recap for those who are just joining: I have multiple branch locations that need to have their transactions rolled up into a common statistics database. Each branch will host a master database replicating their transactions to a central server (multiple slaves, one per branch). The common statistics database needs to be kept as up-to-date as possible. The changes to the common statistics database will be replicated in a hub-and-spoke pattern (master at the central server/slaves at each branch). What Garth and I have been discussing are ideas for synchronizing an external merge/process agent with a slaves replication activity. By coordinating with each slave, I can extract the new records from the central office's slaves and add the processed data to the statistics database in as close to real-time as possible. On Tue, 2004-04-13 at 20:06, [EMAIL PROTECTED] wrote: On Tue, 2004-04-13 at 13:13, [EMAIL PROTECTED] wrote: [snip] I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with --log-slave-updates and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. This seems to indicate that you are afraid of selecting rows on the slave that are in the middle of being updated from the master. A single update statement is still atomic, so you don't need to poll log files to determine if an update statement has finished. On the other hand, if there is some set of multiple updates and inserts that constitute a collection of data that you want to merge only when this collection is complete, you're better off finding a way to signal this through the database. You could have the master lock the tables in question until its finished and then the program quering the slave knows that when it gets a read lock, its will see the full set of data. You could also have a status column or a status table that has a flag letting the program on the slave side know when the data is ready. If this is off the mark, maybe some example statements would help... [more snipping] Yes, I AM concerned about getting a partial update to the warehouse. I know that transactions aren't logged until after they commit. If I use transactional boundaries to post multitable reports (for instance: an invoice takes two tables, one for the base information and one for the line items) into the branch masters then they will arrive intact and I won't corrupt the central slaves. (That's NOT the issue I am worried about!) I am worried that if I start processing those new records from the slave database to the Warehouse before all of the records have been processed from the Relay Logs (lets say I started trying to merge records when I see the relay log being created), I could miss some data (like the last few items on the invoice). That's why I am so worried about not merging until the END of the cycle. I need to be sure that everything has been committed to my central slave BEFORE I can merge the latest updates with the warehouse database. I can lock a slave database so that it won't replicate in the middle of my merging so I know that if I can catch a slave when it goes back to sleep (Waiting for master to send event), I would have a complete set of data. I could use --log-slave-updates to copy the updates to the slave's binlog and check that to see if I need to merge records( if slave status is waiting and the binlog is not empty then merge). Each merge could flush the binlog. However, there was a post from someone using binlogs for similar purpose that said that for 4.1.x+ the binlogs vary in size after flushing so I don't know how reliable that would be as a check. How could I tell when a binlog is empty? Using the binlog in this way will lead to a race condition. What if another update comes in during the few milliseconds between your 'ready' check (an empty binlog and a 'waiting' status) and when you select those rows for processing? Plus if MySQL does any buffering of its output to the binlog, you could be basing your check on seconds old data, further aggravating the problem. The only solution I can think of that won't cause a race condition is to lock your tables, but your program running on the slave database would have to be able to connect to the master. Your slave program would lock the necessary tables on the master side, wait to receive the lock, wait until the slave had caught up, do your merge, then release the lock. Likewise, the code updating tables on the master would need to lock the tables while they write. You make the process a little friendlier by creating a separate control table that the
XFS or ReiserFS?
Which filesystem is better for a MySQL server? I can't see the difference between them. All I need is a LFS that will handle a MyISAM table above the 4GB size level. Running Linux 2.4 (or can install the latest ver if necessary) on 32 bit Intel platform. My Linux books says XFS is the leader in supporting Access Control Lists...is that a consideration for a MySQL server? Thanks, Don
MySQL auth failure logging
Is there a way to log authentication failures against the mysqld by source IP and username attempted? -- Mark P. Hennessy [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column name contains #
At 20:43 -0700 4/17/04, Zhang Yu wrote: Deal all, I am migrating an old Access database to MySQL. After migration, we'll keep using Access forms as front end, and MySQL as database. I experienced a strange problem when creating table with a column name id #, which is used by many queries in Access. My command is like this: create table t1 (`id #` int(11), `First Name` varchard(20) ); I get this error:ERROR 1064: You have an error in your SQL syntax near '' at line 1 Once I substituted # to ! or @, the command worked fine. Is there any constraints with '#' for the column name? For the server, no. However, the mysql client also parses statements (so that it can find statement delimiters and send statements one by one to the server), and its parser is confused by the construct (and a few others) prior to, um, ... Well, I'm not sure prior to what. Prior for 4.1, I believe. I know that the statement you show above works in 4.1.2. Or at least it works when you spell varchard correctly. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value
At 13:30 -0400 4/18/04, Stormblade wrote: On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote: Stormblade wrote: Hey all, I'm currently converting a SQLServer 2000 database over to MySQL. I have a web application that currently uses SQLServer but will be using MySQL soon as I can get this done. I was able to match data types but so far I have not found a way to let the database handle setting the default value of a datetime field to the current date/time. Is this possible? Defaults must be constants, not functions, in MySQL. So, except for the TIMESTAMP type, the answer is no. I suspected as much. I wonder if they plan to add this in the future or if there is some technical reason that they did not allow for this. Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the first TIMESTAMP column is set to the current timestamp when a record is created by default *and* updated when any other column is changed. In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in the table), so that the column can be set to the current timestamp for record creation, but not changed afterward unless you change it explicitly. For example: mysql create table t (ts timestamp default current_timestamp, i int); Query OK, 0 rows affected (0.00 sec) mysql insert into t (i) values(1); Query OK, 1 row affected (0.18 sec) mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |1 | +-+--+ 1 row in set (0.04 sec) mysql update t set i = i + 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |2 | +-+--+ 1 row in set (0.00 sec) Note that the ts column did not change when the i column was updated. This isn't documented in the manual yet, because I am still working on it. In my research I found 2 main suggestions: 1. Use timestamp. While this suggestion is good for my modified fields it is not really suitable for the others which basically will get set once and that's it. You can preserve the value of a timestamp column by explicitly setting it to itself in an UPDATE. Something like this: UPDATE yourtable SET somecol='newvalue', timestampcol=timestampcol WHERE ... Yea I saw that but then I'd have to guarantee that any SQL that updated did this. I had visions of lots of pain in this case. It would only take a single mistake to permanently remove the creation date. 2. Use datetime but specify the date/time in your SQL. This is also not very suitable for two reasons. One is that it would require me to go through the web application and change all the SQL which I can do but rather not. The second reason is that this approach is dangerous. There is no guarantee that the database is on the same system as the web application. May not even be in the same timezone. So I prefer a more loosely coupled design. This need not be dangerous. You can use the CURDATE() and NOW() functions to let the server define the date and time. Something like this: INSERT INTO yourtable (datecol, datetimecol, othercols...) VALUES (CURDATE(), NOW(), othervals...); See http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html for more. Ok, I knew about the functions but what do you mean allow the server to to define the date/time. If I use an SQL like you have above does the database itself resolve those function then? If so then that's a good alternative. Sure I'll have to modify some SQL but I can set the date field not to allow null so that when they create a record they HAVE to supply a date. Now on updates if I remember my SQL I don't have to specify the date and it'll just leave it alone. So that means rather than changing all the SQL I only really have to modify the Inserts. If I can't find any other way I will have to go with the second option but I'd really like to find out a better way if one exists. So, you have two choices: Use timestamp and change your code to preserve the timestamp in updates, or use datetime and change your code to set it to NOW() on insert. Depending on your application, one of those may be easier to do (require fewer changes). All else being equal, I'd recommend using datetime, as it is intuitively closer to what you want (self-documenting). Yup that's what I think I will do. I will use timestamp only for when I need a last modified type value and datetime for all else. Michael Thanks much. If the curdate and now functions use the system date according to where the MySQL database is then that's the solution for me. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683
Re: varchar 4 = char 4? Why?
At 15:26 -0400 4/18/04, Stormblade wrote: Thanks for the explaination. Heh I should have realized that. varchars are probably implemented as null terminated strings and so they need an extra byte for the terminating char (Null or otherwise). They're stored as a length byte + string contents. Yeah it's definitely doing me a favor as my reason for varchar was space. So what happens if I store a single char in a field of char(2)? Does it get autofilled then? I saw an option for this so do I have to specify that this field should get autofilled? The answer to this is to do what you indicate you already know that you should do in the next sentence. :-) Spending some time in the column types chapter will give you some useful background. I'm definitely gonna have to read that manual you gave me the link to I see. That default thing means I'm gonna have to re-do all the SQL in that application. *sigh* I just knew I would. I fought a good fight I guess. On Sun, 18 Apr 2004 15:03:18 -0400, Michael Stassen wrote: In MySQL, the only practical difference between a CHAR and a VARCHAR is how it is stored. CHARs are fixed-length, so they are fast but can waste space. VARCHARs are variable length, so they are slow but (usually) save space. That's the key here -- the only advantage to a varchar is the space savings. But, each varchar takes one more byte than the length of the string in order to store the length. When your column is less than 4 characters, that extra byte makes a significant dent in your space savings, thus removing the sole advantage of varchar over char. For example, it takes 2 bytes to store a CHAR(2) and 1 to 3 bytes to store a VARCHAR(2). MySQL is trying to do you a favor by changing your column. Whether you agree it's done you a favor or not seems to be a matter of opinion. You should definitely read the Silent Column Specification Changes http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html section of the manual. Michael -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column name contains #
At 20:43 -0700 4/17/04, Zhang Yu wrote: Deal all, I am migrating an old Access database to MySQL. After migration, we'll keep using Access forms as front end, and MySQL as database. I experienced a strange problem when creating table with a column name id #, which is used by many queries in Access. My command is like this: create table t1 (`id #` int(11), `First Name` varchard(20) ); I get this error:ERROR 1064: You have an error in your SQL syntax near '' at line 1 Once I substituted # to ! or @, the command worked fine. Is there any constraints with '#' for the column name? For the server, no. However, the mysql client also parses statements (so that it can find statement delimiters and send statements one by one to the server), and its parser is confused by the construct (and a few others) prior to, um, ... Well, I'm not sure prior to what. Prior for 4.1, I believe. I know that the statement you show above works in 4.1.2. Or at least it works when you spell varchard correctly. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!!! - FRM -Files are deleted
At 0:11 +0200 4/19/04, Gerd JuppJacke wrote: Hello, I need fast help. My .frm-files are deleted on MyIsam MySQL. The other two files types (myd and myi) of each table are still there but the frm-files are deleted. The database does not work any more. How can i reconstruct the database with the two types to save everything. I assume you don't have the .frm files in a backup somewhere? 1. Determine why the .frm files got clobbered, so you can prevent it from happening again. 2. Make a backup of the .MYD and .MYI files, in case of a mistake during the following steps. 3. For each table to be restored, follow this procedure: a. In the shell, rename the .MYD and .MYI files. For example: mv x.MYD x.MYD.orig mv x.MYI x.MYI.orig b. In the mysql program, issue the *exact* CREATE TABLE statement that will create a table with the same structure as the to-be-restored table. If you don't know what this statement is, you're out of luck. c. In the mysql program, issue a FLUSH TABLES statement. d. In the shell, rename the file from a. back to their original names: mv x.MYD.orig x.MYD mv x.MYI.orig x.MYI Table x should now be restored. Repeat for the other tables. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!!! - FRM -Files are deleted
At 0:11 +0200 4/19/04, Gerd JuppJacke wrote: Hello, I need fast help. My .frm-files are deleted on MyIsam MySQL. The other two files types (myd and myi) of each table are still there but the frm-files are deleted. The database does not work any more. How can i reconstruct the database with the two types to save everything. I assume you don't have the .frm files in a backup somewhere? 1. Determine why the .frm files got clobbered, so you can prevent it from happening again. 2. Make a backup of the .MYD and .MYI files, in case of a mistake during the following steps. 3. For each table to be restored, follow this procedure: a. In the shell, rename the .MYD and .MYI files. For example: mv x.MYD x.MYD.orig mv x.MYI x.MYI.orig b. In the mysql program, issue the *exact* CREATE TABLE statement that will create a table with the same structure as the to-be-restored table. If you don't know what this statement is, you're out of luck. c. In the mysql program, issue a FLUSH TABLES statement. d. In the shell, rename the file from a. back to their original names: mv x.MYD.orig x.MYD mv x.MYI.orig x.MYI Table x should now be restored. Repeat for the other tables. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar 4 = char 4? Why?
At 15:26 -0400 4/18/04, Stormblade wrote: Thanks for the explaination. Heh I should have realized that. varchars are probably implemented as null terminated strings and so they need an extra byte for the terminating char (Null or otherwise). They're stored as a length byte + string contents. Yeah it's definitely doing me a favor as my reason for varchar was space. So what happens if I store a single char in a field of char(2)? Does it get autofilled then? I saw an option for this so do I have to specify that this field should get autofilled? The answer to this is to do what you indicate you already know that you should do in the next sentence. :-) Spending some time in the column types chapter will give you some useful background. I'm definitely gonna have to read that manual you gave me the link to I see. That default thing means I'm gonna have to re-do all the SQL in that application. *sigh* I just knew I would. I fought a good fight I guess. On Sun, 18 Apr 2004 15:03:18 -0400, Michael Stassen wrote: In MySQL, the only practical difference between a CHAR and a VARCHAR is how it is stored. CHARs are fixed-length, so they are fast but can waste space. VARCHARs are variable length, so they are slow but (usually) save space. That's the key here -- the only advantage to a varchar is the space savings. But, each varchar takes one more byte than the length of the string in order to store the length. When your column is less than 4 characters, that extra byte makes a significant dent in your space savings, thus removing the sole advantage of varchar over char. For example, it takes 2 bytes to store a CHAR(2) and 1 to 3 bytes to store a VARCHAR(2). MySQL is trying to do you a favor by changing your column. Whether you agree it's done you a favor or not seems to be a matter of opinion. You should definitely read the Silent Column Specification Changes http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html section of the manual. Michael -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value
At 13:30 -0400 4/18/04, Stormblade wrote: On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote: Stormblade wrote: Hey all, I'm currently converting a SQLServer 2000 database over to MySQL. I have a web application that currently uses SQLServer but will be using MySQL soon as I can get this done. I was able to match data types but so far I have not found a way to let the database handle setting the default value of a datetime field to the current date/time. Is this possible? Defaults must be constants, not functions, in MySQL. So, except for the TIMESTAMP type, the answer is no. I suspected as much. I wonder if they plan to add this in the future or if there is some technical reason that they did not allow for this. Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the first TIMESTAMP column is set to the current timestamp when a record is created by default *and* updated when any other column is changed. In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in the table), so that the column can be set to the current timestamp for record creation, but not changed afterward unless you change it explicitly. For example: mysql create table t (ts timestamp default current_timestamp, i int); Query OK, 0 rows affected (0.00 sec) mysql insert into t (i) values(1); Query OK, 1 row affected (0.18 sec) mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |1 | +-+--+ 1 row in set (0.04 sec) mysql update t set i = i + 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |2 | +-+--+ 1 row in set (0.00 sec) Note that the ts column did not change when the i column was updated. This isn't documented in the manual yet, because I am still working on it. In my research I found 2 main suggestions: 1. Use timestamp. While this suggestion is good for my modified fields it is not really suitable for the others which basically will get set once and that's it. You can preserve the value of a timestamp column by explicitly setting it to itself in an UPDATE. Something like this: UPDATE yourtable SET somecol='newvalue', timestampcol=timestampcol WHERE ... Yea I saw that but then I'd have to guarantee that any SQL that updated did this. I had visions of lots of pain in this case. It would only take a single mistake to permanently remove the creation date. 2. Use datetime but specify the date/time in your SQL. This is also not very suitable for two reasons. One is that it would require me to go through the web application and change all the SQL which I can do but rather not. The second reason is that this approach is dangerous. There is no guarantee that the database is on the same system as the web application. May not even be in the same timezone. So I prefer a more loosely coupled design. This need not be dangerous. You can use the CURDATE() and NOW() functions to let the server define the date and time. Something like this: INSERT INTO yourtable (datecol, datetimecol, othercols...) VALUES (CURDATE(), NOW(), othervals...); See http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html for more. Ok, I knew about the functions but what do you mean allow the server to to define the date/time. If I use an SQL like you have above does the database itself resolve those function then? If so then that's a good alternative. Sure I'll have to modify some SQL but I can set the date field not to allow null so that when they create a record they HAVE to supply a date. Now on updates if I remember my SQL I don't have to specify the date and it'll just leave it alone. So that means rather than changing all the SQL I only really have to modify the Inserts. If I can't find any other way I will have to go with the second option but I'd really like to find out a better way if one exists. So, you have two choices: Use timestamp and change your code to preserve the timestamp in updates, or use datetime and change your code to set it to NOW() on insert. Depending on your application, one of those may be easier to do (require fewer changes). All else being equal, I'd recommend using datetime, as it is intuitively closer to what you want (self-documenting). Yup that's what I think I will do. I will use timestamp only for when I need a last modified type value and datetime for all else. Michael Thanks much. If the curdate and now functions use the system date according to where the MySQL database is then that's the solution for me. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683
Re: HIGH_PRIORITY with UNION
Chad Attermann [EMAIL PROTECTED] wrote: I am using HIGH_PRIORITY in my SELECTs to force queries to take predcedence over updating due to replication. I have recently implemented UNION in some of my queries to optimize queries like WHERE table1.column1=something OR table1.column2=somethingelse. Anyway, I first tried formatting my UNION query like (SELECT HIGH_PRIORITY ...) UNION (SELECT HIGH_PRIORITY ...) but the server complained about the placement of HIGH_PRIORITY. I was finally able to get it to accept the query by only specifiying HIGH_PRIORITY in the first part of the UNION, like (SELECT HIGH_PRIORITY ...) UNION (SELECT ...), but it appears that my searches are not taking precedence as they should, and as non-UNION queries do. Could there be another explanation for why they are not taking precedence, or is there another way to specify HIGH_PRIORITY in UNION queries to make both sub-queries high-priority? You can specify HIGH_PRIORITY in the first SELECT statement, but you are right HIGH_PRIORITY doesn't take any effect in queries with UNION. Thank you for bug report! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when 4.1.2 release
Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to speed up a simple query?
Hello! I have a simple query on a table of about 1,000,000 records... The table is optimized and the query is pretty simple at this moment... something like this select id,name,desc,cat,date from table where cat='12' however I need to order the results by date desc... I have indexes on both the cat and date (of type timestamp) fields however this operation is much more slowly when I used the order So the result is something like this select id,name,desc,cat,date from table where cat='12' takes 0.7 seconds select id,name,desc,cat,date from table where cat='12' order by date desc takes 2.4 seconds any ideea what I can do to help speeding up things? I was thinking that I should recreate the table daily (something like insert into temp select * from table order by date desc delete from table insert into table select * from temp ) and add the last records at the begining so the mysql database can retreieve the first records the ones added last because this is my purpose in fact But I was wondering if you have any other ideas of how I could do this because I am not to happy with such operations with such large tables... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySqld [problem]
When trying to start mysqld I get the following error #safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/safe_mysqld: line 275: 32649 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 040419 22:08:23 mysqld ended I am on debian woody and upgraded with apt-get apgrade about two hours ago. I am @ my wits end, so sorry if someone saw one of my previous posts. Mozzi -- --- No Microsoft software was used, or employees hurt in creating this email. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting an older version of MySQL
Hi, I'm currently running some instances of 4.0.18. I read the following in the docs: Beginning with MySQL 4.0.3, many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also select their values using SELECT. But when I look at the downloads page, I only have options for 4.0.18 (production) or 4.1.1 (alpha). How do I upgrade my 4.0.18 to 4.0.3 or whatever the latest 4.0.x might be? Thanks, Lou
RE: Getting an older version of MySQL
4.0.18 is the latest 4.0.x release. 4.0.3 4.0.9 4.0.10 4.0.18 4.0.3 != 4.0.30 -Original Message- From: Lou Olsten [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 1:44 PM To: [EMAIL PROTECTED] Subject: Getting an older version of MySQL Hi, I'm currently running some instances of 4.0.18. I read the following in the docs: Beginning with MySQL 4.0.3, many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also select their values using SELECT. But when I look at the downloads page, I only have options for 4.0.18 (production) or 4.1.1 (alpha). How do I upgrade my 4.0.18 to 4.0.3 or whatever the latest 4.0.x might be? Thanks, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: when 4.1.2 release
Huh as in production ? -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:52 AM To: [EMAIL PROTECTED] Subject: Re: when 4.1.2 release Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting an older version of MySQL
Ah Ha! Thanks!!! Lou - Original Message - From: Chris [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 19, 2004 5:14 PM Subject: RE: Getting an older version of MySQL 4.0.18 is the latest 4.0.x release. 4.0.3 4.0.9 4.0.10 4.0.18 4.0.3 != 4.0.30 -Original Message- From: Lou Olsten [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 1:44 PM To: [EMAIL PROTECTED] Subject: Getting an older version of MySQL Hi, I'm currently running some instances of 4.0.18. I read the following in the docs: Beginning with MySQL 4.0.3, many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also select their values using SELECT. But when I look at the downloads page, I only have options for 4.0.18 (production) or 4.1.1 (alpha). How do I upgrade my 4.0.18 to 4.0.3 or whatever the latest 4.0.x might be? Thanks, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.SQL-Scripts.com web site
Hello, Announcing the release of a new web site : www.SQL-Scripts.com At www.SQL-Scripts.Com you can find a collection of SQL Scripts for many different database system. Using our search system you can find scripts that you need quickly and simply. If you have scripts that you use why not lodge them on our site. If you lodge your script you could win a prize. In addition to scripts there are many documents, news feeds and articles. We have a monthly contest for the first people to lodge 20 scripts in one month. Please help to make this site great. Webmaster at www.SQL-Scripts.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN:www.SQL-Scripts.Com
Sorry no subject on the last email Hello, Announcing the release of a new web site : www.SQL-Scripts.com At www.SQL-Scripts.Com you can find a collection of SQL Scripts for many different database system. Using our search system you can find scripts that you need quickly and simply. If you have scripts that you use why not lodge them on our site. If you lodge your script you could win a prize. In addition to scripts there are many documents, news feeds and articles. We have a monthly contest for the first people to lodge 20 scripts in one month. Please help to make this site great. Webmaster at www.SQL-Scripts.com
mysqlclient missing
Hello. I have installed binary version for aix 5.2 from www.mysql.net and when i try to compile cyrus-sasl with mysql support it don't find mysqlclient libraries. I use --with-mysql=/usr/local/mysql/ to build but i get: checking for mysql_select_db in -lmysqlclient... no configure: WARNING: MySQL library mysqlclient does not work Doesn't it include client libraries ? Thanks.
Re: Datetime Default Value
On Sun, 18 Apr 2004 14:18:40 -0500, Paul DuBois wrote: At 13:30 -0400 4/18/04, Stormblade wrote: On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote: Stormblade wrote: Hey all, I'm currently converting a SQLServer 2000 database over to MySQL. I have a web application that currently uses SQLServer but will be using MySQL soon as I can get this done. I was able to match data types but so far I have not found a way to let the database handle setting the default value of a datetime field to the current date/time. Is this possible? Defaults must be constants, not functions, in MySQL. So, except for the TIMESTAMP type, the answer is no. I suspected as much. I wonder if they plan to add this in the future or if there is some technical reason that they did not allow for this. Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the first TIMESTAMP column is set to the current timestamp when a record is created by default *and* updated when any other column is changed. In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in the table), so that the column can be set to the current timestamp for record creation, but not changed afterward unless you change it explicitly. I am a bit confused. You say that in MySQL 4.1.2 you'll be able to do this. What is this? I ask because what you replied to was me wondering about the ability to use functions/macros as default values in MySQL but then you continue on but talk about timestamp and what you can do with it. Are you talking about two different things here? Or are you telling me that yes we will be able to assign functions for default values and in addition to that timestamp can be used thusly..? For example: mysql create table t (ts timestamp default current_timestamp, i int); Query OK, 0 rows affected (0.00 sec) mysql insert into t (i) values(1); Query OK, 1 row affected (0.18 sec) mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |1 | +-+--+ 1 row in set (0.04 sec) mysql update t set i = i + 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |2 | +-+--+ 1 row in set (0.00 sec) Note that the ts column did not change when the i column was updated. This isn't documented in the manual yet, because I am still working on it. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C++ Object Relational Mapper tool?
Does anyone know of an open source tool like Torque or Hibernate for Java that can generate C++ classes rather than Java? We're starting a project with a mySQL backend that involves both a C++ part and a Java part. I've searched high and low for such a tool/library and haven't been able to find one which is very surprising considering I can easily come across several for Java, VB, and even C#, PHP, etc. There are a few commercial ones (currently evaluating Persistence EdgeXtend), but they are largely overkill for what I need. Thanks, Eric --- Eric Sword Group Logic, Inc. [EMAIL PROTECTED] 800.476.8781 (800.GROUP81) http://www.grouplogic.com/services/
Re: XFS or ReiserFS?
Don Dachner wrote: Which filesystem is better for a MySQL server? I can't see the difference between them. All I need is a LFS that will handle a MyISAM table above the 4GB size level. Running Linux 2.4 (or can install the latest ver if necessary) on 32 bit Intel platform. My Linux books says XFS is the leader in supporting Access Control Lists...is that a consideration for a MySQL server? Thanks, Don I've always 'felt' ( ie haven't benchmarked, but this has just been my impression ) that ReiserFS was faster than XFS. And no, Access Control Lists is not important for a MySQL server. -- sig Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySqld [problem]
Can you start it with strace running and see where it fails? I suspect its an issue with finding the group file. If this is the cause you will need to build mySQL yourself. Btw please send arch / version / system hardware spec. -- DVP -Original Message- From: Mozzi [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 12:24 PM To: [EMAIL PROTECTED] Subject: MySqld [problem] When trying to start mysqld I get the following error #safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/safe_mysqld: line 275: 32649 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 040419 22:08:23 mysqld ended I am on debian woody and upgraded with apt-get apgrade about two hours ago. I am @ my wits end, so sorry if someone saw one of my previous posts. Mozzi -- --- No Microsoft software was used, or employees hurt in creating this email. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XFS or ReiserFS?
Use JFS, unless your database server is going to have many many (on the order of 10K) small tables use Reiser. JFS hands down is faster for various mixed environments of reading / writing / random access etc. -- DVP -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 2:21 PM To: Don Dachner; [EMAIL PROTECTED] Subject: Re: XFS or ReiserFS? Don Dachner wrote: Which filesystem is better for a MySQL server? I can't see the difference between them. All I need is a LFS that will handle a MyISAM table above the 4GB size level. Running Linux 2.4 (or can install the latest ver if necessary) on 32 bit Intel platform. My Linux books says XFS is the leader in supporting Access Control Lists...is that a consideration for a MySQL server? Thanks, Don I've always 'felt' ( ie haven't benchmarked, but this has just been my impression ) that ReiserFS was faster than XFS. And no, Access Control Lists is not important for a MySQL server. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
RE: when 4.1.2 release
At 7:11 +1000 4/20/04, electroteque wrote: Huh as in production ? Surely you jest. 4.1.1 was an alpha release. It's unlikely that 4.1.x would go straight to production-level in a single step. I believe that 4.1.2 will also be an alpha release. That said, I encourage you to go ahead and try it out. It has lots of bugfixes and new stuff: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:52 AM To: [EMAIL PROTECTED] Subject: Re: when 4.1.2 release Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB Load Problem
-Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 8:55 AM To: [EMAIL PROTECTED] Subject: InnoDB Load Problem Hi, Apologies in advance for the large post but I've tried to explain my problem as much as I can. I've read the InnoDB tuning page ( http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html ), there are a few things I could try but I'd like to make sure that I'm on the right tracks. * Firstly I could put more queries into a single transaction. That will help, increase your innodb_log_file_size and changed your flush method to O_DIRECT - this avoids double writes. You can verify the benefit of the change by looking at SHOW INNODB STATUS in the LOG IO section. Also, look at your buffer pool hit ratio. If its close to 100% then there is no need to raise the allocation of the buffer_pool. Next, make sure you disk layout is okay. Ensure that your not saturating on IO. Next make sure your not doing queries like SELECT count(*) FROM table -- this is bad. Finally look at which queries are slow. Make sure that your indexes are proper. One last note if your doing many many small quiries think about tweaking your innodb_thread_conncurrency setting -- Ignore innodb_thread_io_conncurrency unless you want to change code. -- Dathan Vance Pattishall Sr. Database Engineer / Sr. Programmer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value
At 17:55 -0400 4/19/04, Stormblade wrote: On Sun, 18 Apr 2004 14:18:40 -0500, Paul DuBois wrote: At 13:30 -0400 4/18/04, Stormblade wrote: On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote: Stormblade wrote: Hey all, I'm currently converting a SQLServer 2000 database over to MySQL. I have a web application that currently uses SQLServer but will be using MySQL soon as I can get this done. I was able to match data types but so far I have not found a way to let the database handle setting the default value of a datetime field to the current date/time. Is this possible? Defaults must be constants, not functions, in MySQL. So, except for the TIMESTAMP type, the answer is no. I suspected as much. I wonder if they plan to add this in the future or if there is some technical reason that they did not allow for this. Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the first TIMESTAMP column is set to the current timestamp when a record is created by default *and* updated when any other column is changed. In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in the table), so that the column can be set to the current timestamp for record creation, but not changed afterward unless you change it explicitly. I am a bit confused. You say that in MySQL 4.1.2 you'll be able to do this. What is this? I ask because what you replied to was me wondering about the ability to use functions/macros as default values in MySQL but then you continue on but talk about timestamp and what you can do with it. Are you talking about two different things here? Or are you telling me that yes we will be able to assign functions for default values and in addition to that timestamp can be used thusly..? Sorry if I wasn't clear. One of the things you wanted to do was have a column that is set automatically to record-creation time when the record is created, but not updated automatically when the record is updated later. You will be able to do this in 4.1.2 with TIMESTAMP, which is what the example below demonstrates. This applies only to TIMESTAMP. You will not otherwise be able to specify functions as default values. For example: mysql create table t (ts timestamp default current_timestamp, i int); Query OK, 0 rows affected (0.00 sec) mysql insert into t (i) values(1); Query OK, 1 row affected (0.18 sec) mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |1 | +-+--+ 1 row in set (0.04 sec) mysql update t set i = i + 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t; +-+--+ | ts | i| +-+--+ | 2004-04-18 14:15:08 |2 | +-+--+ 1 row in set (0.00 sec) Note that the ts column did not change when the i column was updated. This isn't documented in the manual yet, because I am still working on it. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.0-alpha-max-debug running on localhost as ODBC@localhost
I'm not actually getting an error. I'm simply unable to access any of my databases while logged in as [EMAIL PROTECTED] I'd like to change this and specify what account is used to establish a connection. How and where do I make this change in MySQL? Thanks -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 10:22 AM To: [EMAIL PROTECTED] Subject: Re: MySQL 5.0.0-alpha-max-debug running on localhost as [EMAIL PROTECTED] Marvin Cummings [EMAIL PROTECTED] wrote: This appears when I attempt to open phpMyAdmin. It doesn't matter what account I set in the config.inc.php file, I continue to get this error. How do I change this so that another account logs in to the localhost? I unfortunately had to reboot my server and this became the result. I notice that I can log in to MySQLCC and the command line as root without a problem. What error do you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create table problems??
hi..
i am trying to create this table:
CREATE TABLE rnjresort_events (
Id int(25) NOT NULL auto_increment,
Type enum('Annual','OneTime') binary NOT NULL default 'Annual',
StartDate varchar(200) binary NOT NULL default '',
EndDate varchar(200) binary NOT NULL default '',
Name varchar(100) binary NOT NULL default '',
County varchar(50) binary NOT NULL default '',
Notes varchar(255) binary NOT NULL default '',
StartingDay varchar(30) NOT NULL default '',
StartingMonth int(2) default NULL,
StartingYear year(4) default NULL,
EndingDay varchar(30) NOT NULL default '',
EndingMonth int(2) default NULL,
EndingYear year(4) default NULL,
PRIMARY KEY (Id)
) TYPE=MyISAM CHARSET=latin1;
it was created on a test server (mysql 4.1-alpha) and needs to be created on
the real server (mysql 4.0.18)...
i try and do a \. events2.sql to run the script its in to create it and all
i end up getting on the real server is this:
error 1064: syntax error. check the manual for the correct syntax to use for
your version of mysql near default 'Annual', StartDate varchar(200) binary
NOT NULL default '',
EndDate varchar(200) bina
any ideas what could be the problem with this? the table unfortunately needs
to be created exactly as it is shown above... i wondering if the default
'' sections of the column defines are giving it the problem...shrug
any ideas...?
tnx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create table problems??
At 19:09 -0400 4/19/04, Andy B wrote:
hi..
i am trying to create this table:
CREATE TABLE rnjresort_events (
Id int(25) NOT NULL auto_increment,
Type enum('Annual','OneTime') binary NOT NULL default 'Annual',
StartDate varchar(200) binary NOT NULL default '',
EndDate varchar(200) binary NOT NULL default '',
Name varchar(100) binary NOT NULL default '',
County varchar(50) binary NOT NULL default '',
Notes varchar(255) binary NOT NULL default '',
StartingDay varchar(30) NOT NULL default '',
StartingMonth int(2) default NULL,
StartingYear year(4) default NULL,
EndingDay varchar(30) NOT NULL default '',
EndingMonth int(2) default NULL,
EndingYear year(4) default NULL,
PRIMARY KEY (Id)
) TYPE=MyISAM CHARSET=latin1;
it was created on a test server (mysql 4.1-alpha) and needs to be created on
the real server (mysql 4.0.18)...
i try and do a \. events2.sql to run the script its in to create it and all
i end up getting on the real server is this:
error 1064: syntax error. check the manual for the correct syntax to use for
your version of mysql near default 'Annual', StartDate varchar(200) binary
NOT NULL default '',
EndDate varchar(200) bina
any ideas what could be the problem with this? the table unfortunately needs
to be created exactly as it is shown above...
Why is that?
If that's really true, then you cannot do it. ENUM does not support
BINARY in 4.0. (In addition, the CHARSET table option at the end is
not supported, though that will just be ignored.)
If you have to create the table *exactly* as shown above, you're requiring
backward compatibility that does not exist. If you can make some changes,
then declare the table like this:
CREATE TABLE rnjresort_events (
Id int(25) NOT NULL auto_increment,
Type enum('Annual','OneTime') NOT NULL default 'Annual',
StartDate varchar(200) binary NOT NULL default '',
EndDate varchar(200) binary NOT NULL default '',
Name varchar(100) binary NOT NULL default '',
County varchar(50) binary NOT NULL default '',
Notes varchar(255) binary NOT NULL default '',
StartingDay varchar(30) NOT NULL default '',
StartingMonth int(2) default NULL,
StartingYear year(4) default NULL,
EndingDay varchar(30) NOT NULL default '',
EndingMonth int(2) default NULL,
EndingYear year(4) default NULL,
PRIMARY KEY (Id)
) TYPE=MyISAM;
i wondering if the default
'' sections of the column defines are giving it the problem...shrug
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value
On Mon, 19 Apr 2004 17:36:50 -0500, Paul DuBois wrote: Sorry if I wasn't clear. One of the things you wanted to do was have a column that is set automatically to record-creation time when the record is created, but not updated automatically when the record is updated later. You will be able to do this in 4.1.2 with TIMESTAMP, which is what the example below demonstrates. This applies only to TIMESTAMP. You will not otherwise be able to specify functions as default values. Ok thanks. Hmm. Will timestamp's value range be increased so that it can store the same range of dates as datetime? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key_buffer_size and INNODB
At 9:45 -0700 4/19/04, Emmett Bishop wrote: Is the key_buffer_size server variable useful for tuning databases that only have innodb tables or do I need to use the innodb_buffer_pool_size variable for this? The buffer controlled by key_buffer_size is used only for MyISAM and ISAM tables. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Datetime Default Value
At 20:23 -0400 4/19/04, Stormblade wrote: On Mon, 19 Apr 2004 17:36:50 -0500, Paul DuBois wrote: Sorry if I wasn't clear. One of the things you wanted to do was have a column that is set automatically to record-creation time when the record is created, but not updated automatically when the record is updated later. You will be able to do this in 4.1.2 with TIMESTAMP, which is what the example below demonstrates. This applies only to TIMESTAMP. You will not otherwise be able to specify functions as default values. Ok thanks. Hmm. Will timestamp's value range be increased so that it can store the same range of dates as datetime? Not that I know of. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Gripe with MySQL
Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. One scenario I can think of is this. My company uses MySQL as it's database. Different departments implement various interfaces to this database. All it would take is ONE client to have ONE bad SQL and although the insert works (Thanks to default values being put in) the data is not valid. I've only been working with MySQL for a little bit so this is just my first impressions. I'll be very happy to be told I'm wrong or that future updates (5.0 perhaps) will change some of the things I've mentioned. Relying on clients for database integrity is a bad idea in my experience. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data Local Infile confusion
Hello, I have a client that can connect to either a local or remote MySQL server. LOAD DATA INFILE works fine on a local server, but obviously fails when a remote server is used. When I add LOCAL to the command, I get: The used command is not allowed with this MySQL version I am using 4.0.18. The client is written using Delphi. What do I need to do to get this command to work with either server? Regards Dan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.643 / Virus Database: 411 - Release Date: 3/25/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA LOCAL INFILE confusion
Hello, I have a client that can connect to either a local or remote MySQL server. LOAD DATA INFILE works fine on a local server, but obviously fails when a remote server is used. When I add LOCAL to the command, I get: The used command is not allowed with this MySQL version I am using 4.0.18. The client is written using Delphi. What do I need to do to get this command to work with either server? Regards Dan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.643 / Virus Database: 411 - Release Date: 3/25/2004
Key file
On one database, I keep seeing similar errors related to the key file for the different tables. 1034 Incorrect key file for table: Can't read key from filepos: 672768 error 5 when fixing table Can't copy datafile-header to tempfile, error 9 How do I diagnose if the database file structure is corrupt? What do these errors in key file point to as a root cause? MySQL 4.0.17 Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key file
On one database, I keep seeing similar errors related to the key file for the different tables. 1034 Incorrect key file for table: Can't read key from filepos: 672768 error 5 when fixing table Can't copy datafile-header to tempfile, error 9 How do I diagnose if the database file structure is corrupt? What do these errors in key file point to as a root cause? MySQL 4.0.17 Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gripe with MySQL
On Mon, 19 Apr 2004, Stormblade wrote: Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. Yup, it is a bad idea, and thankfully it seems to be slowly improving as more and more people try to use mysql as a more serious database. However, keep in mind that it is not an adhoc arbitrary decision, but it based on a very fundamental traditional mysql design fundamental: not to support transactions because they aren't required most of the time, or so the claim goes. While there are a few mysql storage engines now that do support transactions, and at least one is in widespread use, this history explains why it is the way it is. If you don't support transactions, what do you do if you are running a statement that updates multiple rows and get an error with one row? If you just abort the whole statement, it is really ugly since then you leave the statement half executed. If you try to be able to undo the entire statement, it is really ugly because without transactions you are unlikely to have the backend support for doing that or for avoiding dirty reads, etc since that is one of the fundamentals of what a transaction is. So ... you bravely soldier on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gripe with MySQL
On Mon, 19 Apr 2004 20:17:38 -0700 (PDT), Marc Slemko wrote: On Mon, 19 Apr 2004, Stormblade wrote: Ok. Love MySQL and I will be using it for my personal use and recommending it to clients as a lower cost alternative. I've only been using it for a very short time but there one major gripe I have with it and I believe it's just a design thing. MySQL seems to put the burden of error checking and such on the client. - All fields have a default value even when I don't tell it to? - Allow Null will only result in an error if I explicitly try to set the field to null. These are the two things that I really dislike. I think it's a poor design to rely on clients only for error checking. MySQL supports foreign keys. This is good because the database handles referential integrity. But it shouldn't stop there. I should also be able to tell the database not to allow a field to be empty/null and it should not put anything in there that I have not told it to. Yup, it is a bad idea, and thankfully it seems to be slowly improving as more and more people try to use mysql as a more serious database. However, keep in mind that it is not an adhoc arbitrary decision, but it based on a very fundamental traditional mysql design fundamental: not to support transactions because they aren't required most of the time, or so the claim goes. While there are a few mysql storage engines now that do support transactions, and at least one is in widespread use, this history explains why it is the way it is. If you don't support transactions, what do you do if you are running a statement that updates multiple rows and get an error with one row? If you just abort the whole statement, it is really ugly since then you leave the statement half executed. If you try to be able to undo the entire statement, it is really ugly because without transactions you are unlikely to have the backend support for doing that or for avoiding dirty reads, etc since that is one of the fundamentals of what a transaction is. So ... you bravely soldier on. Ah this makes sense. Although for me I wouldn't have done it this way even if I hadn't planned on supporting transactions but that is because of my personality and the type of programming I usually did. My concerns were almost always with reliability, maintainability. Sure speed was a concern but not as much as the other two. But I can understand (If not agree with) the decision at least. Speed was more of a concern and with them not supporting transactions this makes sense. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ByteFX and blob
Does any one knows how to store and retrieve files in blob columns using VB .Net with ByteFx? FBR
