Re: Using BETWEEN or = =
On 13 Jan 2004 at 10:11, Eve Atley wrote: I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Eve If I assume that you are looking for a salary that lies between the values stored in the start and end field? In this case your query won't work cos your greater and less thans are a little bit confused. This query (compare with yours above) should work: $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
How does key buffer work ?
I've been trying to optimise the operation of a MySQL (4.0.13) installation on a Windows 2000 based web server. After going through the docs and browsing the net for some time, it seems that after ensuring that your database design is sound and your queries correctly structured with the right indexes present then further optimisation can be realised by tinkering with the MySQL server's startup parameters, principally the key buffer size. It seems that the key buffer is solely used as an index cache and that extending this, up to a point, potentially will significantly improve performance. However, after playing with this value on my system for a while, I have a number of questions about how it works... 1) I assume that the key buffer caches the contents of the myi files (I'm only talking MyISAM tables here) but is this a direct copy of the contents ? i.e. if you extend the key buffer so that it is bigger than the sum of the size of the myi files on your system, then will this be sufficient to be able to cache all the indexes all the time ? 2) Does the whole index get loaded into the cache on the first time it's accessed or do only 'parts' of the index get loaded as they are used for various queries ? 3) If an index is updated for any reason, is the whole cache copy of the index then invalidated or is the cache copy updated at the same time as the disk file? One idea I was toying with was to 'delay' all inserts to the sensitive tables (an update is done every five minutes for my particular system) so that the tables are updated pretty much in one single go and then ensure the key buffer is refreshed so that all select queries on these tables for the next five minute period will use only cached indexes. Does this sound plausible and or sensible ? Thanks and Regards, John Everitt PGN MSS Philips C/IT.
Re: why: mysqldump and mysqlimport?
On Tue, 13 Jan 2004, EP wrote: I am wondering: I can see the MySQL data files for my various databases. What technically prevents me from simply copying those files and using copies - to move my database to another file structure or server - to back-up my current db Copying will not work across certain versions, will also not work between system archtechtures, port to other RDBMS or if you do not have physical access to the files. For the cases above mysqldump works fine. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migration to 64 bit - successful!
What kind of my.cnf file are you using with that setup? -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 8, 2004, at 2:48 PM, [EMAIL PROTECTED] wrote: We moved our main production server to a dual opteron last night, running SuSE 9.0 x86_64 (kernel 2.4.21), and the binaries mysql offer for mysql4 work great. The only hitches doing the change were between the chair and the desk. MYD/MYI/frm are all binary compatible, and the server speed is awesome mostly down to all that extra memory bandwidth over our previous intel box. If anyone else is wondering whether linux+mysql is stable enough on 64bit, well, at least for us, it is. (so far - touch wood). Both kernel and server feel solid. The server handles 2000 questions per second, 150 mysqld processes, and about 400 tables, from 6gb in size down, in a 20gb database. So far it appears to be about 4x faster than then 1.4ghz pentium IIIs it replaced, but with other advantages as well, not the least of which is the 16gb of memory the motherboard now has! -J. -- 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: Fulltext creation on 4.1: ERROR 1034
Hi Eli, Eli Hen wrote: Kurt Haegeman [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sergei Golubchik wrote: Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table Hi Sergei, alter table articles add fulltext( text ); After several hours of processing, the error below is generated. Regards, Kurt. Did you try to check the table using myisamcheck or CHECK TABLE articles; ??? It might be that your table is corrupted.. mysql alter table articles - add fulltext( text ); ERROR 1034 (HY000): 121 when fixing table mysql check table articles; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | test.articles | check | status | OK | +---+---+--+--+ 1 row in set (8 min 33.00 sec) That's not it, at first sight. Regards, Kurt.
Re: Implement one statement w/o subqueries.
Mike Johnson wrote: From: Ruslan U. Zakirov [mailto:[EMAIL PROTECTED] Hello. Table 1: Items id, Name Table 2: Properties id, Item, Name, Value I want select Items _and_ all thier props only if Item have specified property. Example: Table Item: 1, Mouse 2, Monitor 3, Keyboard Table Properties: 1, 1, Color, Red 2, 2, Color, Gray 3, 1, Interface, Wireless 4, 2, MaxResolution, [EMAIL PROTECTED] 5, 3, Color, Gray 6, 3, NumberOfKeys, 101 I want select all Items and all thier properties if item's Color is Gray: Item, Name, Prop, Value 2, Monitor, Color, Gray 2, Monitor, MaxResolution, [EMAIL PROTECTED] 3, Keyboard, Color, Gray 3, Keyboard, NumberOfKeys, 101 I hope you've understand what I mean. Thanks. Ruslan. PS: MySQL 4.0.x I believe you just need to join the Properties table twice: SELECT I.id, I.Name, P2.Name, P2.Value FROM Properties P1 INNER JOIN Items I ON I.id=P1.Item INNER JOIN Properties P2 ON P2.Item=I.id WHERE P1.Name='Color' AND P1.Value='Gray'; Thanks for reply. This solution is right, was my fault used wrong alias in result set. Best regards. Ruslan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does key buffer work ?
Hi John, I'll give my comments. :-) - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 2:04 AM Subject: How does key buffer work ? I've been trying to optimise the operation of a MySQL (4.0.13) installation on a Windows 2000 based web server. First, I'd upgrade MySQL to the latest 4.0.x for bug fixes, etc. The biggest of which may be possible index corruption (I think) in versions before 4.0.14 (or is it .15?). After going through the docs and browsing the net for some time, it seems that after ensuring that your database design is sound and your queries correctly structured with the right indexes present then further optimisation can be realised by tinkering with the MySQL server's startup parameters, principally the key buffer size. It seems that the key buffer is solely used as an index cache and that extending this, up to a point, potentially will significantly improve performance. Yup, table/index design and optimized queries are very important for performance. I'm not of the opinion that a huge key_buffer is as important as a lot of people make it. :-) Sure, it's important, but I don't know if many changes will significantly improve performance. :-) Making it too large may actually hurt overall performance. You see, MySQL doesn't cache any row data from the .MYD files. The OS will use any free RAM to cache file data such as that (to save costly disk seeks/reads). (BTW, in Win2k, the Performance tab of Task Manager, where it says System Cache, I *think* is a good indicator of how much file data is cached.) And if you make your key_buffer too big, this will be [more] memory that a program (MySQL) has allocated, from the OS's view. That's that much less free RAM that could be used to cache the data file. Compared to randomly reading data file rows (especially larger, variable length ones) after index lookups, it's much faster to read the index file, even from disk (if key_buffer is too small). That's because the index file is smaller and everything is in order, to be read more sequentially, thus saving random disk seeks. Besides, even if the key_buffer is too small, the OS will also cache the index file data, so it may not actually have to be read from disk. However, after playing with this value on my system for a while, I have a number of questions about how it works... 1) I assume that the key buffer caches the contents of the myi files (I'm only talking MyISAM tables here) but is this a direct copy of the contents? Yes. i.e. if you extend the key buffer so that it is bigger than the sum of the size of the myi files on your system, then will this be sufficient to be able to cache all the indexes all the time ? Yes it will. Making it as big as your .MYI files is the *maximum* you should use. BUT, it's probably not the best. :-) It's more like, How much of those .MYI files are accessed *regularly*? Probably not all of them. And remember about leaving enough memory to cache row data. After the server's been running awhile, I think if Key_reads divided by Key_read_requests (from SHOW STATUS) is less than 0.01 like it says in the manual, you should be pretty good. 2) Does the whole index get loaded into the cache on the first time it's accessed or do only 'parts' of the index get loaded as they are used for various queries ? Only parts. :-) Blocks, actually. A block is usually 1024 bytes; though if you have an index more than like 255 bytes long, the blocks will be 2048 bytes. They are loaded on demand when they're not in the key_buffer (Key_reads status var). The status variable Key_blocks_used is like a high water mark. It's the most blocks that were ever in the key_buffer (not necessarily currently for some reason *shrug*) since the server was started. If the blocks are the usual 1K size, then 16384 Key_blocks_used, for example, would mean 16MB of indexes were in the key_buffer at some point -- and may still be, of course. Again, after MySQL's been running awhile (doing typical queries), if Key_blocks_used divided by 1024 is LESS THAN your key_buffer_size (in MB), your key_buffer is probably too big -- as it's never getting filled. 3) If an index is updated for any reason, is the whole cache copy of the index then invalidated or is the cache copy updated at the same time as the disk file? I think the block in the key_buffer is updated first, then on disk. Don't hold me to this, though. :-) If anything was invalidated, it would just be the block(s) that were updated. One idea I was toying with was to 'delay' all inserts to the sensitive tables (an update is done every five minutes for my particular system) so that the tables are updated pretty much in one single go and then ensure the key buffer is refreshed so that all select queries on these tables for the next five minute period will use only cached indexes. Does this sound plausible and or sensible ? To me, not really for the sake of
Newbie - from Oracle
Oracle has Dual does MySql have something similar? In Oracle I use SYSDATE and USER a lot as default column values when creating a table. Can you do something similar in MySql and how? Does MySql support sequences? How? Relationships - can the code be interfered with or do I have to wait for triggers? Thanks Ken
Re: Newbie - from Oracle
Hi Ken,
Oracle has Dual does MySql have something similar?
MySQL doesn't have a dual table. But if you take a look at the
Dual in Oracle, you'll only see this:
CREATE TABLE Dual (Dummy VARCHAR(1));
INSERT INTO Dual ('X');
So you can easily create one yourself.
In Oracle I use SYSDATE and USER a lot as default column values when
creating a table. Can you do something similar in MySql and how?
According to http://www.mysql.com/doc/en/CREATE_TABLE.html
a DEFAULT is:
A DEFAULT value has to be a constant, it cannot be a function or an
expression.
Does MySql support sequences? How?
No, not as seperate objects. It does support an auto-inc type column.
Relationships - can the code be interfered with or do I have to wait for
triggers?
Foreign key constraints? Only available in the InnoDB table type.
Read the docs on that.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a tool
[EMAIL PROTECTED] .org To: [EMAIL PROTECTED] cc: 01/13/2004 08:15 Subject: Looking for a tool PM Several years ago, I used a cgi based tool that allowed me to upload a csv file to a server and load the data into either a new table or an existing table. Anyone happen to know of one like it now. I can't seem to find it in my archives anymore. I know I can do it with load data infile, but I want the web interface so that I can had the task off to an admin with minimal skills. if you have php installed. phpmyadmin will do that for you and a whole lot more. (phpmyadmin.net) Jeff Thanks in advance -- 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]
PROBLEM WITH LOADING DATA
Hi fellas, I wrote a php program that loads and displays data into and from a mysql database. It connectes and displays the data correctly. But when you fill a form and click submit it acts as if the data is written on the data base while isn't if you check it by manual connection. What could be the problem, Regards, Aron __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does key buffer work ?
Matt, Many thanks for the answer. It has helped enormously. First, I have been getting the odd index corruption that has proved to be very annoying. I had checked the changes document for releases since 4.0.13 and there didn't seem to be any mention of an index problem but now I'll upgrade asap. Thanks for that. Next, I had extended my key buffer too much. When I calculated the high water mark for key buffer usage, I found that indeed it was considerably less than the space I had allocated. I will modify accordingly. However, I was just thinking about what you said about this only being a high water mark ... I can't see any way, apart from dropping an index or table, that information is going to be purged from the cache especially as you say that MySQL updates the contents of the cache when an index is modified, so won't that mean that during normal operation the key_blocks_used should indicate exactly how many blocks are currently in use ? Some of the contents may of not been used for a while but still they won't be purged unless the maximum extent of the cache is reached ? With regards to the caching on myd data, is the fact that MySQL doesn't cache myd data a design choice ? It makes perfect sense for MySQL installations on a dedicated machine as its a fair assumption that there's no other nasty apps around filling your system cache with other data and the OS is probably in the best position to cache the disks. However, in my case (and I would guess in the proportion of the cases), the database shares the machine and in my case this is with Apache which depending on the usage on the website, is likely to flush the cache reasonably quickly. It would be easier to get more consistent query execution times if MySQL maintained it's own caches (for myd data as well) so that more control could be kept on cache contents. In a previous life I worked with Sybase and one of the advantages (only ?) is that the administrator has control on the caching of index and data for each individual table. Very handy if it was benficial to ensure the contents of specific tables were available in a cache. Lastly, I'd love to use the query cache but I do have to update the indexes every 5 minutes (the system revolves around retrieving SNMP data from a bunch of routers every 5 mins then dumping it into the db ... a user then requests a report periodically) so the QCache is invalidated every 5 mins anyway. So, a) do you (or anyone else) know of any plans to extend the caching functionality and b) are there any other parameters that may be worth a tweak ? One thing I had considered was to extend a composite index to incorporate the data that is required in the problematic query then the query should be able to extract the data required without having to search the myd file. I understand that this will increase the index size and slow the inserts but otherwise is this a legitamate solution i.e. there must be some other downside surely ? Anyway, thanks again for the response. John Matt W [EMAIL PROTECTED] 2004-01-14 11:18 To: John Everitt/EHV/CORP/[EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:Re: How does key buffer work ? Classification: Hi John, I'll give my comments. :-) - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 2:04 AM Subject: How does key buffer work ? I've been trying to optimise the operation of a MySQL (4.0.13) installation on a Windows 2000 based web server. First, I'd upgrade MySQL to the latest 4.0.x for bug fixes, etc. The biggest of which may be possible index corruption (I think) in versions before 4.0.14 (or is it .15?). After going through the docs and browsing the net for some time, it seems that after ensuring that your database design is sound and your queries correctly structured with the right indexes present then further optimisation can be realised by tinkering with the MySQL server's startup parameters, principally the key buffer size. It seems that the key buffer is solely used as an index cache and that extending this, up to a point, potentially will significantly improve performance. Yup, table/index design and optimized queries are very important for performance. I'm not of the opinion that a huge key_buffer is as important as a lot of people make it. :-) Sure, it's important, but I don't know if many changes will significantly improve performance. :-) Making it too large may actually hurt overall performance. You see, MySQL doesn't cache any row data from the .MYD files. The OS will use any free RAM to cache file data such as that (to save costly disk seeks/reads). (BTW, in Win2k, the Performance tab of Task Manager, where it says System Cache, I *think* is a good indicator of how much file data is cached.) And if you make your key_buffer too big, this will be [more] memory that a program (MySQL) has allocated, from the
Searching for matching zipcode in a list of (ranges of) zipcodes
Hi all, I am looking for a simple solution to find a zipcode in lists of zipcodes. I have a table with customer data including the customer's zipcode and a table containing information about service engineers. The service engineers can define a list of zipcode (ranges) of areas they want to (or are able to) service. An example of such a list is: 3528,3529,3612-3621,3828. This list is stored in one field in the service engineer table. What I would like to do is by using the customer's zipcode select all engineers that have that zipcode in their list. E.g. 3529 3615 would match while 3530 would not in the above example. I can of course code this in perl, for example, but that would mean that for every search I need to do I would have to retrieve the entire service engineer table and go through it to find a match. I was hoping MySQL would have function to this. I searched the documentation but have not been able to find any. Thanks for your time. Kind regards, Tom Hesp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching for matching zipcode in a list of (ranges of) zipcodes
Tom, Change your database so that you have an engineer table and an engineer_zipcodes table. Each engineer can have multiple entries in the engineer_zipcodes table. Engineer Engineer_id integer auto_increment primary key Name Address Etc Create index engineer1 on engineer(engineer_id) Engineer_zipcodes Engineer_id Min_zipcode Max_zipcode Create index zipcodes1 on engineer_zipcodes(engineer_id, min_zipcode, max_zipcode) To handle ranges of zipcodes, have two columns min_zipcode and max_zipcode for every entry. For single zip codes both columns are identical, for ranges they are different Then to locate an engineer: Select * from engineer e, engineer_zipcodes z where e.engineer_id = z.engineer_id and min_zipcode = zzz and max_zipcode = zzz zzz is the zipcode you're searching for. Hope this helps, Andy -Original Message- From: Tom Hesp [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 12:56 To: [EMAIL PROTECTED] Subject: Searching for matching zipcode in a list of (ranges of) zipcodes Hi all, I am looking for a simple solution to find a zipcode in lists of zipcodes. I have a table with customer data including the customer's zipcode and a table containing information about service engineers. The service engineers can define a list of zipcode (ranges) of areas they want to (or are able to) service. An example of such a list is: 3528,3529,3612-3621,3828. This list is stored in one field in the service engineer table. What I would like to do is by using the customer's zipcode select all engineers that have that zipcode in their list. E.g. 3529 3615 would match while 3530 would not in the above example. I can of course code this in perl, for example, but that would mean that for every search I need to do I would have to retrieve the entire service engineer table and go through it to find a match. I was hoping MySQL would have function to this. I searched the documentation but have not been able to find any. Thanks for your time. Kind regards, Tom Hesp -- 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: Problem with LPAD() function
I am using 4.1.0 and 4.1.1 on Windows
On WinXP, it even crashes the server when I add extra date fields.
On Win2k AS, it doesn't crash the MySQL server but it returns unexpected
results.
Thanks
Emery
- Original Message -
From: Victoria Reznichenko [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 12:36
Subject: Re: Problem with LPAD() function
Director General: NEFACOMP [EMAIL PROTECTED] wrote:
I think there is a bug in LPAD() function.
Just try to run this query:
SELECT LPAD(12, 3, '0');
This will correctly return 012 as expected.
But when I run
SELECT LPAD(512, 3, '0');
It doesn't return 512, instead it returns special characters.
With that situation I tried the following
SELECT LPAD('512', 3, '0');
And it worked as required.
So, my conclusion will be that the type conversion will only take place
when the number of characters to PAD is bigger than the length of the
original string.
What version do you use?
Works like a charm for me:
mysql SELECT LPAD(12, 3, '0');
+--+
| LPAD(12, 3, '0') |
+--+
| 012 |
+--+
1 row in set (0.00 sec)
mysql SELECT LPAD(512, 3, '0');
+---+
| LPAD(512, 3, '0') |
+---+
| 512 |
+---+
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching for matching zipcode in a list of (ranges of) zipcodes
Original Message Subject: Re: Searching for matching zipcode in a list of (ranges of) zipcodes Date: Wed, 14 Jan 2004 14:14:19 +0100 From: Mirza [EMAIL PROTECTED] To: Tom Hesp [EMAIL PROTECTED] References: [EMAIL PROTECTED] Hi, If you rewrite ranges 3612-3621 to have all elements named (3612, 3613,... , 3621), you can use fulltext search to find matching engineers. Users can still enter 3612-3621 to it's frontend application, but you must write all numbers to database. hope this helps, mirza Tom Hesp wrote: Hi all, I am looking for a simple solution to find a zipcode in lists of zipcodes. I have a table with customer data including the customer's zipcode and a table containing information about service engineers. The service engineers can define a list of zipcode (ranges) of areas they want to (or are able to) service. An example of such a list is: 3528,3529,3612-3621,3828. This list is stored in one field in the service engineer table. What I would like to do is by using the customer's zipcode select all engineers that have that zipcode in their list. E.g. 3529 3615 would match while 3530 would not in the above example. I can of course code this in perl, for example, but that would mean that for every search I need to do I would have to retrieve the entire service engineer table and go through it to find a match. I was hoping MySQL would have function to this. I searched the documentation but have not been able to find any. Thanks for your time. Kind regards, Tom Hesp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Security issues
I wanted to run by everyone what I am doing in my application to help prevent someone from inadvertently or intensionally breaking the system and compromising security. First some quick background. This is an Apache/php/mysql project. It is a wish list database where people can create an account, then a wish list and share it with all their friends and family so they will know what to get them for their birthday or Christmas or whatever event. There are many other features that I won't go into here, if you want to know more, you can take a look at the work in progress at http://thewishzone.com:8086 The first thing I do when getting data from a post or a get is run it through a function that first checks to make sure it isn't any longer than I am expecting (I will also eventually have java script that does client side checking too, but a post or get can easily be faked so I am checking on the server side as well) I then verify that every character in the string is with in the ascii range of a space to the ~ which is basically all the characters on the key board. If either test fails I print an error and stop the script. If the value is supposed to be an integer or float I also check to make sure there aren't any non-numeric characters in the value. Then finally before I put it in the database I use the mysql_real_escape_string function and put single quotes around my values in the sql statements. Are there many php or mysql configuration considerations for making the site secure? I have already done the obvious with my sql and set up the grant tables with passwords for all users and removed the [EMAIL PROTECTED] user. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PROBLEM WITH LOADING DATA
Can you post the code that does the update? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PROBLEM LOADING DATA USING PHP
Hi there,
I am posting this message again sice my previous post
was vague.
I have recently configured a server which runs redhat
7.2. I followed the installation procedures to install
mysql database and PHP on the same server and they
seem to run properly.
My aim is to load data into a mysql database and view
the same data using web page.
Before I implement my own project i stolen the
following code from the web and tried to customize it
on my server. (section of the code customized code is
the following)
--
if (($REQUEST_METHOD=='POST')) {
// This loop removed dangerous characters from the
posted data
// and puts backslashes in front of characters that
might cause
// problems in the database.
for(reset($HTTP_POST_VARS);
$key=key($HTTP_POST_VARS);
next($HTTP_POST_VARS)) {
$this = addslashes($HTTP_POST_VARS[$key]);
$this = strtr($this, , );
$this = strtr($this, , );
$this = strtr($this, |, );
$$key = $this;
}
// This will catch if someone is trying to submit a
blank
// or incomplete form.
if ($name $email $message ) {
// This is the meat of the query that updates the
guests table
$query = INSERT INTO guests ;
$query .= (guest_id, guest_name, ;
$query .= guest_email, guest_time, guest_message)
;
$query .=
values(,'$name','$email',NULL,'$message');
mysql_pconnect(,bereket,iloveumom)
or die(Unable to connect to SQL
server);
mysql_select_db(employee) or die(Unable to
select database);
mysql_query($query) or die(Insert Failed!);
} else {
// If they didn't include all the required fields
set a variable
// and keep going.
$notall = 1;
}
}
-
Result: Everything seems fine. Except when you fill
the form and click submmit, it doesn't update the
database. But if i connect to mysql maually and add
some records, you can automatically see the new data
posted on the web, which is what I wanted.
problem: It is not updating the database from the web
form.
YOUR HELP IS APPRICIATED,
Cheers,
Aron
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching for matching zipcode in a list of (ranges of) zipcodes
Hi all, Thanks very much for the advice, it gave me more than enough input to resolve my problem! Kind regards, Tom Hesp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User quotas, how?
On Tuesday, January 13, 2004 11:52 PM CET, Anders Norrbring wrote: Hiya all! I'm curious about if there's a simple way to manage user quotas in a MySQL database? F.x. user one can have 10MB data in the database and user two can have a maximum of 5MB? Anders Norrbring AFAIK MySQL has no support for this. But as I also need to implement such a thing in some time, too, I've heard of an interesting approach some time ago: You need to periodically check the users' databases and calculate their sizes. If one exceeds its quota, which you will have to check against your own table, just revoke any insert/update/aso. rights from this user. So then, he can only delete data from the table to make it smaller. If the size is in quota again, you simply grant the rights 'back'. This is not an exact implementation of quota, and if you only check the databases hourly, one will be able to massively exceed the quota within this time, but if someone really does this, you can still kick him forever afterwards ;) Maybe you could also count the filesize of the database directories? Just a thought. I'm planning to write either a PHP or a C program for this, maybe I'll post it here, when it's finished... -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Super slow after index crash
Hi, My MySQL Server crashed yesterday and I repaired all the tables.. However now the server is performing about ½ its old speed. Is there anything I can do other than optimize the table? -- Keith
Re: Fulltext creation on 4.1: ERROR 1034
Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table Sorry, I still cannot repeat this :( Could you try to create a smaller test case ? I would expect that you need only a few rows from your table for this bug to appear. (of course, finding these exact rows in your gigabytes could be not easy :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting a variable
mysql 4.0.17 on redhat 9 or debian 3.0 mysql show variables like 'log'; +---+---+ | Variable_name | Value | +---+---+ | log | ON| +---+---+ 1 row in set (0.00 sec) mysql set global log=on; ERROR 1193: Unknown system variable 'log' What am I doing wrong? Thanks, Ugo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: setting a variable
Sorry, 4.0.16, not 4.0.17, if it changes anything. -Message d'origine- De : Ugo Bellavance Envoyé : Wednesday, January 14, 2004 12:07 PM À : [EMAIL PROTECTED] Objet : setting a variable mysql 4.0.17 on redhat 9 or debian 3.0 mysql show variables like 'log'; +---+---+ | Variable_name | Value | +---+---+ | log | ON| +---+---+ 1 row in set (0.00 sec) mysql set global log=on; ERROR 1193: Unknown system variable 'log' What am I doing wrong? Thanks, Ugo -- 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: Server Super slow after index crash
At 10:39 AM 1/14/2004, you wrote: Hi, My MySQL Server crashed yesterday and I repaired all the tables.. However now the server is performing about ½ its old speed. Is there anything I can do other than optimize the table? -- Keith Keith, You could try Analyze Table and Optimize Table to see if that improves anything. http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html The problem could also be a defragmented drive or if using Windows, lost chains (CHKDSK will fix it). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security issues
On Wed, 2004-01-14 at 13:32, Chris W wrote: Are there many php or mysql configuration considerations for making the site secure? I have already done the obvious with my sql and set up the grant tables with passwords for all users and removed the [EMAIL PROTECTED] user. Give the MySQL user you're using only the minimum permissions. I doubt your web app will need to ALTER table structures for example. I like to use privilege separation. In my code I have different MySQL users with different permission. One might have read-write access (SELECT, INSERT, UPDATE etc.) and another has read-only. I then use these users appropriately throughout my code. For example, a script that searches a table uses the read-only user. Then no matter how clever the attacker is, they won't be able to DELETE all my data by exploiting that code. John. -- GPG: B89C D450 5B2C 74D8 58FB A360 9B06 B5C2 26F0 3047 URL: http://www.johnleach.co.uk signature.asc Description: This is a digitally signed message part
enum version info
Hi, Quick question since I can't see to find version information online. What version was enum first supported under? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about IF statements...
Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- Cory Hicks [EMAIL PROTECTED] TRI International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question about IF statements...
From: Cory Hicks [mailto:[EMAIL PROTECTED] Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' AND total IS NOT NULL GROUP BY project_id; That should work, though I haven't tested it. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Like this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.13 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. umysql use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql create table time_daily (project_id int(3) primary key auto_increment, time_worked int(3), user_id varchar(3), period_id varchar(3)); Query OK, 0 rows affected (0.06 sec) mysql insert into time_daily (null, 3, 'clh', '27'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'null, 3, 'clh', '27')' at line 1 mysql insert into time_daily (time_worked, user_id, period_id) values (3, 'clh', '27'); Query OK, 1 row affected (0.52 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (0, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (5, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0 - ; ++---+ | project_id | total | ++---+ | 1 | 3 | | 3 | 5 | ++---+ 2 rows in set (0.18 sec) Douglas Sims wrote: Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting a variable
On Wed, 14 Jan 2004, Ugo Bellavance wrote: mysql 4.0.17 on redhat 9 or debian 3.0 mysql show variables like 'log'; +---+---+ | Variable_name | Value | +---+---+ | log | ON| +---+---+ 1 row in set (0.00 sec) mysql set global log=on; ERROR 1193: Unknown system variable 'log' What am I doing wrong? Currently you cannot start logging while the server is running. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreating InnoDB tables -WITHOUT- .frm
Matthew, Someone asked this question last year. It turns out that there's only a one-character difference between the InnoDB and MyISAM .frm files. See the posting below from last May for a way to recover the InnoDB table structure, given an InnoDB .frm file but no data files, basically by patching the InnoDB .frm file to look like a MyISAM .frm file. I understand that it worked pretty well. Good luck. Adam, This probably doesn't do anything for you. Sorry. Regards, Bill From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Recreating InnoDB tables -WITHOUT- .frm Date: Tue, 13 Jan 2004 22:02:36 +0200 Matthew, http://www.innodb.com/ibman.php#InnoDB_Monitor Starting from 3.23.44, there is innodb_table_monitor with which you can print the contents of the internal data dictionary of InnoDB. The output format is not beautiful, and you have to manually reconstruct the MySQL CREATE TABLE statements from it. Adam, you can try creating a dummy InnoDB table with enough PRIMARY KEY columns. Then replace its .frm file with an old one you have, and try to print SHOW CREATE TABLE. I do not know if mysqld will crash or assert. This question was discussed on this mailing list some 2 years ago. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ ... List:MySQL General Discussion« Previous MessageNext Message » From:Matthew ScottDate:January 13 2004 5:33pm Subject:Recreating InnoDB tables -WITHOUT- .frm To all the InnoDB gurus out there: I have a similar problem to this person's predicament, except my situation is that I have all the innodb data and log files, but have absolutely no .FRM files. Are there any general tools for data recovery from InnoDB databases? Any companies that can do this for a fee? Anything??? :) Thanks.crossing my fingers that myself and Adam can find resolutions to our respective situations! [EMAIL PROTECTED] wrote: I'm cleaning up a user-error where the innodb data files were deleted without a useful backup. I need to reconstruct the tables and still have the frm files. Is this possible? A significant amount of time was put into these tables' structures and I hate to lose that effort... Yes the users are kicking themselves about the backup... MTIA Adam -- Matthew Scott [EMAIL PROTECTED] Posting from 2003-05-31 Mark, Here's a brute force and ignorance approach. Disclaimer: It has worked once, and may work again some day. In particular, I haven't looked at the MySQL internals, and I've only tried it on a very small table. You have foo.frm, which used to be the .frm file for an InnoDB table. I note that the (only) difference between .frm files for MyISAM and InnoDB is that the fourth byte of the file is hex 09 for MyISAM and hex 0C for InnoDB. (This, from comparing .frm files for a very small and simple database.) 0. Make sure you have foo.frm saved somewhere other than your MySQL data directory. 1. Create a new MyISAM table foo; it doesn't matter what the layout is. For example, create table foo (n int); 2. Copy your foo.frm over the one created in step 1. 3. Change the fourth byte of foo.frm to hex 09 instead of hex 0C. 4. From the MySQL client, say show create table foo; Good luck. HTH. Bill Date: Thu, 29 May 2003 12:47:02 -0700 Subject: RE: Recovering table structures from .frm files? From: Mark Morley [EMAIL PROTECTED] To: Mark Morley [EMAIL PROTECTED], [EMAIL PROTECTED] I have a couple of .frm files with no corresponding data or index files. Is it possible to recover the table structure (field names, types, sizes) from these files? More info: these appear to have been created under MySQL 4.0.x and they were originally InnoDB files. I can see a list of field names by running strings on each .frm file, but I'd really like to get the colum types and sizes as well. Is the file format documented anywhere? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL GPL License Question
I understand some of the GPL but I am a little confused on this issue: If I create a program that just queries data from a MySQL table and processes it...am I required to release that program under the GPL? I have a MySQL server set up with some tables and I created a seperate application to query the tables and process the data. Is writing an app that is able to connect with MySQL a situation where I will need to release it under the GPL? There is no code or any part of MySQL used in the application. Any help would be greatly appreciated. __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
That did the trickthanks so much! Cory On Wed, 2004-01-14 at 12:50, Douglas Sims wrote: Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- Cory Hicks [EMAIL PROTECTED] TRI International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Replication in 4.0.17
Since I didn't get any replies to my previous message (see below), I am trying to compile MySQL myself, to see if it results in a more stable system when using replication. However this is failing consistently with the following error: make[4]: Entering directory `/usr/src/mysql-4.0.17/sql' source='sql_lex.cc' object='sql_lex.o' libtool=no \ depfile='.deps/sql_lex.Po' tmpdepfile='.deps/sql_lex.TPo' \ depmode=gcc3 /bin/sh ../depcomp \ gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW -DDEFINE_CXA_PURE_VIRTUAL -c -o sql_lex.o `test -f sql_lex.cc || echo './'`sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:171: `get_hash_symbol' undeclared (first use this function) I have tried this with the gcc compiler that comes with RedHat 7.3 (2.96), and also with gcc 3.2.3 (built from source, because of the advice on the MySQL website that says 2.96 might be unstable). I tried building 2.95 from source, but it didn't seem to be able to recognize my system and I couldn't see from the documentation how to fix this. However given that 2.96 (rpm) and 3.2.3 (src) both give exactly the same result, I wouldn't have high hopes for 2.95. I have tried several different invocations for ./configure, including the following three: ./configure --prefix=/usr/local/mysql CFLAGS=-O2 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static CXX=gcc ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared The latter two were from section 2.1.2.5 of the MySQL manual. Searching google for the error message I can see that other people have had a similar problem with this, but I have seen no solutions. Does anyone have the secret incantation to successfully build MySQL 4.0.17? I fully realize that MySQL AB recommends using the binary rpms supplied by them, but since I am having no luck with finding out what's wrong with replication, I think it makes sense to try and build it myself and see if that makes any difference. Any advice much appreciated... TIA, -Neil Neil Gunton wrote: I am using 4.0.17 rpm on Red Hat 7.3 (fully updated). I have a server colocated at my local ISP, and my workstation is on ADSL behind a Netsys router (the ADSL ISP uses PPPoE, don't know if that's relevant or not). The server has RAID 1, and has always been 100% reliable (up since 2000). I have been using MySQL for over four years now, and have never had any problems until recently, when I tried using replication. I wanted to mirror the database to my workstation over the DSL connection. I got it working correctly, but quickly found that the slave would just stop replicating if I went away and left it for a while (hours). It would be fine while I sat there, but overnight or after a couple of hours away from my workstation, I would return and it had just stopped. There were no errors in the log on either end. It just wasn't updating. Restarting the slave would quickly bring things up to date again. Eventually I tried lowering the master-connect-retry to 10 seconds, and slave-net-timeout to 60 seconds. This seemed to fix this particular problem. Overnight I could come back and everything was still synced up. I don't know why this could cause an issue, since I keep long-lived ssh connections to my server all day long without problem. I have also noticed other problems - most worrying of which is that records inserted into the master database have actually disappeared completely from the master and slave. My website has message boards, and on two occasions now I have posted a message, seen it in the database (i.e. read the website) and then come back to see that the new message is just gone. These boards have been in operation for years, and are extremely reliable. Never have messages simply vanished. The first time this happened, it only took a few seconds to go away. The second time, it was overnight. This is extremely scary behaviour. Also, in multiple unrelated instances, one of the master index files have become corrupted, and had to be repaired using myisamchk. All my tables are MyISAM. The same corruption has also
Re: Connecting to remote server
I have the user hardware set to be able to connect to the database from any host. That is why I am so confused as to why this doesn't work. Mike From: Andrew Boothman [EMAIL PROTECTED] Date: Wed, 14 Jan 2004 01:04:36 + To: Mike Tuller [EMAIL PROTECTED] Cc: MySql List [EMAIL PROTECTED] Subject: Re: Connecting to remote server Mike Tuller wrote: I have a shell script that is supposed to connect to a remote server running MySql 3.23.53. It comes up with an error ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) The script looks like this: /usr/local/mysql/bin/mysql --user=$username --password=$password cetechnology -e \ That makes sense to me, I don't have permissions set correctly right for the $username (hardware). I know the password is set correctly. Then why am I able to connect to the server in the terminal with: /usr/local/mysql/bin/mysql -h 204.xxx.xxx.xxx -u hardware -p cetechnology And then enter my password. What is the difference? I'm not certain about this one (I'm not totally confident with MySQL's permission system myself). But it seems to me that assuming that the IP that you x'd out in your second example _is_ the IP of the local server in your first example then MySQL is going to treat them incoming connections from two different locations. One is going to be a connection from [EMAIL PROTECTED] the other is a connection from [EMAIL PROTECTED] - I think there's a good chance that MySQL will treat these as entirely different hosts to be GRANTed on despite the fact that they are actually the same physical machine. Therefore - if connections to 204.xxx.xxx.xxx work and connections to localhost don't, you need to GRANT the right permissions to [EMAIL PROTECTED] Have a look over the relevent section of the handbook for how to use GRANT HTH Andrew -- 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]
Installing MySQL on Linux, HELP!
Well, RedHat 9 says mysql is *already* installed. But I can't start it. And when I attempt to configure it using mysql_install_db, it says to run make install on it first. When I try to ./configure, it says it can't be found. (i know gcc exists) When trying make make install, it says nothing to be done. Now I think I'm just goofing things up. Ran a 4.0.1 rpm, and it said it installed; but still nothing. Then ran another one, and now it says dependencies are missing. I am following instructions from http://www.brtnet.org/linux/lamp.htm. - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL GPL License Question
-Message d'origine- De : Computer Mail [mailto:[EMAIL PROTECTED] Envoyé : Wednesday, January 14, 2004 2:45 PM À : [EMAIL PROTECTED] Objet : MySQL GPL License Question I understand some of the GPL but I am a little confused on this issue: If I create a program that just queries data from a MySQL table and processes it...am I required to release that program under the GPL? I have a MySQL server set up with some tables and I created a seperate application to query the tables and process the data. Is writing an app that is able to connect with MySQL a situation where I will need to release it under the GPL? There is no code or any part of MySQL used in the application. I think that as long as you can distinguish mysql from your application you don't need a licence. When you can't remove mysql without playing into the code, it is embedded, thus require a licence. my 2cents Any help would be greatly appreciated. __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- 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]
Openbsd 3.4 performance
Hi list, two questions: - Any tips on improving openbsd performance for mysql 4.0.17? - Creating an index on a primary key improves performance? thank to all.
Re: why: mysqldump and mysqlimport?
EP wrote: I am wondering: I can see the MySQL data files for my various databases. What technically prevents me from simply copying those files and using copies - to move my database to another file structure or server - to back-up my current db Yes, I did put my finger in the electrical socket as a kid. But only several times. A simple file copy works if: a) No-one is accessing the database at the time, and b) There is no database corruption If you have database corruption, mysqldump will often be the first to know about it, as you're selecting all rows ( as opposed to other mysql clients which are selecting specific rows ). So using mysqldump and watching the output ( make a cron job and it'll email you the output ), you can catch database corruption early and have a good chance to do something about it, instead of simply copying corrupt files day after day until you finally don't have a valid backup around. Dan -- 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]
2 Quick Questions
Quick Question Here: I think I may have figured out my little problem with the thing wanting to use localhost as opposed to the actual name of NapMarilu. Could it be that when the server 2003 machine was originally setup before the installing of MYSQL that in the IIS portion of the machine, the website stuff only has the default of localhost and nothing more than that? If this is the case, then I may have very well solved that problem. Then the next problem is with WinMySqlAdmin. How in the heck do I change it to login in as root and show all the databases etc. Right now it is showing only the test DB and user as Administrator. Everything appears in grey and can't be changed? And that is pretty much all I need to know now. As of right now this particular box that mysql is on is for a testing phase for a client and more than likely will need to be replicated once the testing phase is over. If I can get those two questions answered, then I know I will more than be ready to replicate the whole thing in an ease. In any event for someone like me to not have messed with MySQL before, I think I did fairly well, and thanks for all the help from you guys that did help me out on parts where I did get stuck. If anyone has the answers to the two above questions please let me know. Thanks again everyone... Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED]
RE: 4.1.1 FTS 2-level?
Thanks for the additional information. When 4.1.2 comes out, I'll give it a test and return with some stats on real world result times (for my data set at least). -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Quick Questions
Why use WinMySqlAdmin to view databases? The MySQL Control Center has a lot more features and its usage is very straight forward. If you are referring to the name that you gave it the first time you ran it then I don't know. I'm curious about how to set it myself. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does key buffer work ?
Hi John, - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 6:37 AM Subject: Re: How does key buffer work ? Matt, Many thanks for the answer. It has helped enormously. First, I have been getting the odd index corruption that has proved to be very annoying. I had checked the changes document for releases since 4.0.13 and there didn't seem to be any mention of an index problem but now I'll upgrade asap. Thanks for that. It was in 4.0.15: http://www.mysql.com/doc/en/News-4.0.15.html Fixed rare bug in MyISAM introduced in 4.0.3 where the index file header was not updated directly after an UPDATE of split dynamic rows. The symptom was that the table had a corrupted delete-link if mysqld was shut down or the table was checked directly after the update. Next, I had extended my key buffer too much. When I calculated the high water mark for key buffer usage, I found that indeed it was considerably less than the space I had allocated. I will modify accordingly. However, I was just thinking about what you said about this only being a high water mark ... I can't see any way, apart from dropping an index or table, that information is going to be purged from the cache especially as you say that MySQL updates the contents of the cache when an index is modified, so won't that mean that during normal operation the key_blocks_used should indicate exactly how many blocks are currently in use ? Some of the contents may of not been used for a while but still they won't be purged unless the maximum extent of the cache is reached ? When a table is closed, its blocks are released from the key_buffer. So after running FLUSH TABLES, for example, Key_blocks_used should be 0 if it was current. You can see that the blocks are removed from the buffer by running a query that uses an index. Look at Key_reads. Run it again and Key_reads shouldn't change. Use FLUSH TABLES and run it again. Key_reads will be increased since the blocks were reloaded. With regards to the caching on myd data, is the fact that MySQL doesn't cache myd data a design choice ? It makes perfect sense for MySQL installations on a dedicated machine as its a fair assumption that there's no other nasty apps around filling your system cache with other data and the OS is probably in the best position to cache the disks. However, in my case (and I would guess in the proportion of the cases), the database shares the machine and in my case this is with Apache which depending on the usage on the website, is likely to flush the cache reasonably quickly. It would be easier to get more consistent query execution times if MySQL maintained it's own caches (for myd data as well) so that more control could be kept on cache contents. In a previous life I worked with Sybase and one of the advantages (only ?) is that the administrator has control on the caching of index and data for each individual table. Very handy if it was benficial to ensure the contents of specific tables were available in a cache. InnoDB may cache full row data too with its buffer_pool. But I don't know much about that. :-) But when you have something like Apache running, you want it to be able to use the memory it needs. Isn't it better to not have .MYD data cached than to have other processes swapping? Lastly, I'd love to use the query cache but I do have to update the indexes every 5 minutes (the system revolves around retrieving SNMP data from a bunch of routers every 5 mins then dumping it into the db ... a user then requests a report periodically) so the QCache is invalidated every 5 mins anyway. If you can get [repeated] queries to use the query cache for 5 minutes, I'd say that's a pretty long time. :-) So, a) do you (or anyone else) know of any plans to extend the caching functionality No. and b) are there any other parameters that may be worth a tweak ? One thing I had considered was to extend a composite index to incorporate the data that is required in the problematic query then the query should be able to extract the data required without having to search the myd file. I understand that this will increase the index size and slow the inserts but otherwise is this a legitamate solution i.e. there must be some other downside surely ? No, that would probably work pretty well if you don't mind making the index a bit bigger (there's a limit of 16 cols/index or 512 (?) bytes, and no [full] TEXT/BLOB columns). I've done this on one of my tables. If EXPLAIN on your SELECTs says Using index, then it's getting the data without going to the .MYD file (I guess you already know that). Making an index bigger (by adding columns) is not as bad as adding another separate index. With a bigger index, the only slowdown on inserts is writing the extra bytes -- not much. But for each separate index, MySQL needs to find where in the index to put the new row. So I don't think you'll notice any slowdown or
Re: MySQL GPL License Question
On Thu, 15 Jan 2004 08:44, Computer Mail wrote; If I create a program that just queries data from a MySQL table and processes it...am I required to release that program under the GPL? No. The below; 0. This License applies to any program or other work which contains a notice placed by the copyright holder saying it may be distributed under the terms of this General Public License. The Program, below, refers to any such program or work, and a work based on the Program means either the Program or any derivative work under copyright law: that is to say, a work containing the Program or a portion of it, either verbatim or with modifications and/or translated into another language. RMS has made it quite clear that a work based on the program means that you're linking the programs together, as with `ld' (or a *very* similar logical equivalent). Bundling GNU products with commercial software is openly encouraged. The output is unrestricted; Activities other than copying, distribution and modification are not covered by this License; they are outside its scope. The act of running the Program is not restricted, and the output from the ^^^ Program is covered only if its contents constitute a work based on ^^ the Program (independent of having been made by running the ^^^ Program). Whether that is true depends on what the Program does. This clause does not catch the output from a MySQL server. On the other hand, if you were to link the MySQL binary directly into your program - avoiding the SQL server - then you would need to purchase a commercial license from MySQL AB to avoid the requirement for the derived work to be covered by the GPL. -- Sam Vilain, [EMAIL PROTECTED] It is better to be violent, if there is violence in our hearts, than to put on the cloak of nonviolence to cover impotence. -- Mahatma Gandhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport question
I'm trying to use mysqimport instead of LOAD DATA INFILE from a shell script. I notice an option for mysqlimport is not working or im doing it wrong. This works with LOAD DATA INFILE : mysql -e LOAD DATA INFILE 'x' INTO TABLE x IGNORE 2 LINES but when i try: mysqlimport --ignore-lines=2 -uroot -ppassword month 0114.txt It says unknown option --ignore-lines=? Am i doing this wrong? Also will mysqlimport work only if the .txt file is the same name as the table in the db( mine is month.Jan04, but the files are always mmdd.txt) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
