Using MySQL 4.1.x for a project that is released in february?
Hello! We are currently using MySQL 4.0.x to develop a project for a customer which should be released in early February 2004. The MySQL Download Page states that for new developments, 4.1.x can be used. I would also like to use this version, since it allows subqueries, a feature that 4.0.x currently lacks. Of course, my colleagues are concerned about the alpha state of the 4.1.x version. My question is: How is the general stability of the 4.1.x versions? Can it be used for new projects, or is it so unstable that the risk is too high? Greetings, Andreas Buschka
Re: Using MySQL 4.1.x for a project that is released in february?
On Wednesday 26 November 2003 08:42, Andreas Buschka (tops.net) wrote: My question is: How is the general stability of the 4.1.x versions? Can it be used for new projects, or is it so unstable that the risk is too high? In my testing so far, the connection capability seems to be intermittent - I have as yet to test using mysqlclient though. And earlier someone reported a possible problem with permissions not being honoured. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
functions in libmysqlclient.a conflicting with my own!
Hello all, I'm using mysql 3.23.49 under Debian Linux and gcc 2.95.4. I'm writing a mysql client in C, and therefore I link libmysqlclient.a in my program. The problem is that I also use and link a static list-handling library I've written some time ago. And of course problem arises with multiple definitions of various list functions!! Once in the mysqlclient library and once in my own list library. I don't use any list functions in the mysqlclient library, I didn't even knew they existed until now! So my question is how I can compile and link my program, so it uses my own list functions and not the list functions in mysqlclient? Right now, it won't link because of multiple definition of some of the list functions. I can't change the function names in my own list library either, because a lot of other applications are using it! Thankfull for any help or tip that could help me solve my problem!! Regards, /Bo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting data into table1 should insert data into table2's FK ???
Paul Fine [EMAIL PROTECTED] wrote: If I have two tables with the first table containing a field which is FK of table 2, when I insert a value into this field, should it not automagically insert a value into that PK in the second table? No, it should not insert data in the child table. You should insert data manually. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: --with-debug option
# uname -X System = SCO_SV Node = scospare Release = 3.2v5.0.6 KernelID = 2000-07-27 Machine = PentII(D) BusType = ISA Serial = 3IG004365 Users = 5-user OEM# = 0 Origin# = 1 NumCPU = 1 # Stefaan Van Dooren .--. Technical Support |o_o | Kompas Automatisering |:_/ | // \ \ Tel : +32 3 2350084(| | ) Fax : +32 3 2359792 /'\_ _/`\ Email : [EMAIL PROTECTED] \___)=(___/ -Original Message- From: Boyd Gerber [mailto:[EMAIL PROTECTED] Sent: dinsdag 25 november 2003 5:45 To: Stefaan Van Dooren Cc: [EMAIL PROTECTED] Subject: Re: --with-debug option On Tue, 25 Nov 2003, Stefaan Van Dooren wrote: Can anyone tell me what this option does, besides adding debug information ? When I add this option, I get a working mysqld server. When I remove it and recompile, my server won't work anymore It does a lot of things. With MySQL-4.0.16 is also aids in debug information for some malloc's issues for eample. This also gets around a OS bug that depends on your patches to the OS. Please provide a uname -X that would be helpful. Good Luck, Boyd Gerber [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN clause trouble
Kate Porter [EMAIL PROTECTED] wrote: I am using 4.0.15 on RedHat 7.3 and ran it to a strangest problem today. SELECT col FROM table WHERE site_id=123; returns a list of data. SELECT col FROM table WHERE site_id IN(123); returns an empty set. Upon further investigation, I found cases when the IN() syntax gets a smaller subset of the data. e.g. SELECT day FROM table WHERE site_id IN(123); gets me data up to 2003-03-23, whereas the other form of the query has data going all the way through current. I've been using the IN syntax quite a bit and never noticed a problem before. For those cases when you expect a list of values, this is a very convenient syntax. I still feel like there is something obvious I am not seeing. Or is this a bug? Could you create a repeatable test case? Table corruption? Use CHECK TABLE command to check table for errors: http://www.mysql.com/doc/en/CHECK_TABLE.html Only this is happenning across multiple tables (various summaries). -- 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: mysqldump
What mysqldump does? I dont know it because i am new in the world of databases Andre Winarko [EMAIL PROTECTED] wrote: I have problem with mysqldump. This query results an error. mysqldump danamon [trx_temp2] trx_temp2.sql The error message is : mysqldump : Can't get CREATE TABLE for table '[trx_temp2]' exist I'm sure the trx_temp2 table in danamon database exists. I'm running MySQL 4.0.13 __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Free Pop-Up Blocker - Get it now
Security Question
I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB many thanks in advalnce for info * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Zenzo [EMAIL PROTECTED] wrote: What mysqldump does? I dont know it because i am new in the world of databases mysqldump makes dump of the database or databases: http://www.mysql.com/doc/en/mysqldump.html -- 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]
4.1 crashes on geo select
Hi,
Version 4.1.0. crashing on my Windows 2000 box after this query:
SELECT * FROM `streets` where MBROverlaps(GeomFromText('Polygon(16345750
48185401, 16402348 48185401, 16402348 48156946, 16345750 48156946,
16345750 48185401)'), GEOM);
GEOM is field of type geometry inside streets table. I have tested
table and test shows that everything is OK.
Mirza Hadzic
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strings of variables, php, mysql
a simple way is to do select $form as new_column_name from structure; as the query. thus the return value of you column will be in new_column_name. Vinay joffrey leevy wrote: Would appreciate in anyone can help me. Let's say I do a query in php, eg. $query = select shed from structure; With the mysql_fetch_array function and a loop I could see all the values stored in the column, shed, using the command: echo $shed; Let's say now that I am carrying over a variable from a form called $form where $form = shed. I can still say $query = select $form from structure; because $form = shed. The problem I have now is with the strings and how do I see my column values. Using echo $$value; or echo $($value) or echo \$$value or echo $$value does not give me the values stored in the column shed. Help anyone? thanks __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Security Question
thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1. - the files would have tight unix security file permissions applied 2. - indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
On Wednesday 26 November 2003 13:22, Curley, Thomas wrote: Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying To look at it from another angle (and address the 'shouldn't be on the internet' issue), take the case of a webserver that has a script that can access the SQL server. Said SQL server is on a private, internal only network, with no access to the internet. Said script has a username and password that can read 'private' data. Someone is able to see the source if the script, and now has the username and password (assumption: the viewing is done from a local shell). How is having the SQL server hidden from the internet a benefit? So long as you provide any mechanism to access the server, you cannot consider the server data to be private, unless you redefine the word private. If you want to keep data on an SQL server, and not let people copy the database, then don't give them a login on the SQL server, and don't give them a username/password for connecting to the SQL engine. How do you stop someone from copying a piece of paper in an office? You lock it away from them. Or them from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Well, I'm not an expert on security, but I don't think this is a database issue. It is really a file/operating system issue. I don't think you can do anything in the database against copying the files. If somebody has access on file system level, the dbms is powerless. So I think you need to think about the OS. Stefan Am Wednesday 26 November 2003 14:22 schrieb Curley, Thomas: thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1.- the files would have tight unix security file permissions applied 2.- indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor *** ** This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. *** ** -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Security Question
One of the first things that I did at my former job was to turn off all external-facing network adapters to our DB machines. If you're fortunate enough that your DB resides on it's own box and not the webserver itself, then there's really no reason that you *need* to have it externally facing. There are PLENTY of solutions that you can put in place in order to still have remote access to those machines without them having an externally routable IP. While it is possible for a hacker to compromise one machine and then access the DB machine over your internal WAN at the hosting location, the more roadblocks you put between a potential hacker and your sensitive data, the better. -M -Original Message- From: Curley, Thomas [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 8:22 AM To: [EMAIL PROTECTED] Subject: RE: Security Question Importance: High thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1. - the files would have tight unix security file permissions applied 2. - indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- 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: Security Question
Mike Correct and this is the architecture. The internet facing box has a routable IP, the DB box is separate and is not ext routable. The issue the security review highlighted strongly was the fact that if a hacker got access to the box (however) then copying /var/lib/mysql/database would result in a major security breach To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) Thomas -Original Message- From: Mike Brum [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 13:36 To: Curley, Thomas; [EMAIL PROTECTED] Subject: RE: Security Question One of the first things that I did at my former job was to turn off all external-facing network adapters to our DB machines. If you're fortunate enough that your DB resides on it's own box and not the webserver itself, then there's really no reason that you *need* to have it externally facing. There are PLENTY of solutions that you can put in place in order to still have remote access to those machines without them having an externally routable IP. While it is possible for a hacker to compromise one machine and then access the DB machine over your internal WAN at the hosting location, the more roadblocks you put between a potential hacker and your sensitive data, the better. -M -Original Message- From: Curley, Thomas [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 8:22 AM To: [EMAIL PROTECTED] Subject: RE: Security Question Importance: High thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1. - the files would have tight unix security file permissions applied 2. - indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Security Question
On Wednesday 26 November 2003 13:43, Curley, Thomas wrote: Mike Correct and this is the architecture. The internet facing box has a routable IP, the DB box is separate and is not ext routable. The issue the security review highlighted strongly was the fact that if a hacker got access to the box (however) then copying /var/lib/mysql/database would result in a major security breach To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) In the end, it's all tradeoffs. You could put an encryption algorithm into your web interface, but then the key is public. However, cracking the DB server only gets you encrypted data. Tradeoff? Speed. Best data security practice (silly) - don't have the data in the first place. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ann: New Article At vbmysql.com!
Hi All; The last MySQL newsletter linked to my article titled Protecting MySQL Sessions With SSH Port Forwarding, available at http://www.vbmysql.com/articles/sshtunnel.html. Response was positive, but there were multiple requests for information on hosting SSH sessions on a Windows server. In response to requests I have issued a followup article called (most creatively) Protecting MySQL Sessions With SSH Port Forwarding (Part 2), which is available at http://www.vbmysql.com/articles/ssh-tunnel-part2.html. This followup gives instructions for installing the OpenSSH For Windows package and also covers opening and closing SSH tunnels from within Visual Basic. In any case, I hope you find these of use! Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) The chap was me :-) I'm sure it does on oracle. Once you have an Oracle installation and got hold of all database files (which is easy once an intruder got root on the machine) you have access to all data. Even oracle can't do anything about this, but there might be two difficulties with oracle compared to mysql: You need the oracle software (expensive, but do hackers buy software?) and it might be that the files are spread all over the computer and hard to find. But basically, it is the same with oracle (but I never used oracle, this is common sense). Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disorder result with ORDER BY with ENUM, INT
-- System: MySQL 4.0.13, 4.0.16 on Linux x86
-- Table type: MyISAM, InnoDB
-- Description / How-To-Repeat:
-- 1. When I use
-- SELECT id, type FROM test.report ORDER BY type, id;
-- the result is in wrong order
-- --
-- ++-+
-- | id | type|
-- ++-+
-- | 4 | general |
-- | 3 | general |
-- | 1 | general |
-- | 2 | general |
-- | 5 | inhouse |
-- | 6 | inhouse |
-- ..
-- --
-- 2. But when I use
-- SELECT id, type FROM test.report ORDER BY type AND id;
-- the result is in right order
-- --
-- ++-+
-- | id | type|
-- ++-+
-- | 1 | general |
-- | 2 | general |
-- | 3 | general |
-- | 4 | general |
-- | 5 | inhouse |
-- | 6 | inhouse |
-- ..
-- 3. After running statement(2) I rerun statement(1) again and the result is then in
order as (2).
--
-- Dummy data structure for testing is dumped as shown below.
-- Thank you,
--
-- -kk-
-- 2003/11/26
--
-- MySQL dump 9.09
--
-- Host: localhostDatabase: test
-- --
-- Server version 4.0.16
--
-- Table structure for table `report`
--
CREATE TABLE report (
id int(10) unsigned NOT NULL default '0',
type enum('general','inhouse') NOT NULL default 'general',
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='Available Reports';
--
-- Dumping data for table `report`
--
INSERT INTO report VALUES (1,'general');
INSERT INTO report VALUES (2,'general');
INSERT INTO report VALUES (3,'general');
INSERT INTO report VALUES (4,'general');
INSERT INTO report VALUES (5,'inhouse');
INSERT INTO report VALUES (6,'inhouse');
INSERT INTO report VALUES (7,'inhouse');
INSERT INTO report VALUES (8,'inhouse');
INSERT INTO report VALUES (9,'inhouse');
INSERT INTO report VALUES (10,'inhouse');
INSERT INTO report VALUES (11,'inhouse');
INSERT INTO report VALUES (12,'inhouse');
INSERT INTO report VALUES (13,'inhouse');
INSERT INTO report VALUES (14,'inhouse');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Hacker gets in this way: -[Webserver][rooted]-[DBServer][rooted]-File_Access(/var/lib/mysql/database) I'd say the major security breach is already when the Webserver is rooted.^ If he gets to your webserver he could still read WHATEVER DATA he wants from your database with the information he finds in your site's code. Look at below example: (Use Fixed Font) Internet | (80,443)--- - firewall w/ webports open | Webserver | (3306)- - another one allowing mysql access | DBServer Since you have a bulkhead between your servers your DBServer is completely* safe from anyone getting file-level access to it. But, since you have a working webserver with scripts and functions to access the database he can still access any data he wants from the database server. Stop worrying so much about mysql's filelevel security. If your webserver is rooted you are toast anyway! Mike ^Your security review needs to be reviewed? *Unless there's a security hole in mysql allowing code/command execution. On Wednesday 26 November 2003 14.43, Curley, Thomas wrote: Mike Correct and this is the architecture. The internet facing box has a routable IP, the DB box is separate and is not ext routable. The issue the security review highlighted strongly was the fact that if a hacker got access to the box (however) then copying /var/lib/mysql/database would result in a major security breach To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Andre Winarko wrote: I have problem with mysqldump. This query results an error. mysqldump danamon [trx_temp2] trx_temp2.sql The error message is : mysqldump : Can't get CREATE TABLE for table '[trx_temp2]' Table 'danamon.[trx_temp2]' doesn't exist I'm sure the trx_temp2 table in danamon database exists. Yes, but [trx_temp2] doesn't. I'm running MySQL 4.0.13 __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Hi! On Nov 26, Curley, Thomas wrote: thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1.- the files would have tight unix security file permissions applied 2.- indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying Just as you said above - tight unix security file permissions. That is - database files should be readable ONLY by the dedicated mysql user. Thus if somebody breaks in he will need to be root to copy these files. And if he can get root - no encryption will help, he can get the key straight from the mysqld memory image (via /proc/*/mem) or patch the server (again via /proc/*/mem) to decrypt all the data for him, or hijack your connections to the server and record all the traffic or anything. If somebody got root - you lost. Until he did - unix permissions will help. If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites See above. Web server should be on this internet accessible box, shouldn't it ? And it (or a CGI program) should be able to talk to mysqld (which resides on a dedicated secure box), and it should know the password. So if somebody can get into the box with httpd - he'll be able to access mysqld too. 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]
What is going on mysqld_multi
Why does only the [mysqld2] start see attached file Does anybody have a my.cnf file for 3.23.58 since I cant get it started with InnoDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Disorder result with ORDER BY with ENUM, INT
I'm using mySQL 4.0.14 Linux and the mySql CC 0.8.9 Beta.
I ran your script and I'm clear. So its version specific.
-Original Message-
From: Kriengkrai J. [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 2:05 AM
To: [EMAIL PROTECTED]
Subject: Disorder result with ORDER BY with ENUM, INT
-- System: MySQL 4.0.13, 4.0.16 on Linux x86
-- Table type: MyISAM, InnoDB
-- Description / How-To-Repeat:
-- 1. When I use
-- SELECT id, type FROM test.report ORDER BY type, id;
-- the result is in wrong order
-- --
-- ++-+
-- | id | type|
-- ++-+
-- | 4 | general |
-- | 3 | general |
-- | 1 | general |
-- | 2 | general |
-- | 5 | inhouse |
-- | 6 | inhouse |
-- ..
-- --
-- 2. But when I use
-- SELECT id, type FROM test.report ORDER BY type AND id;
-- the result is in right order
-- --
-- ++-+
-- | id | type|
-- ++-+
-- | 1 | general |
-- | 2 | general |
-- | 3 | general |
-- | 4 | general |
-- | 5 | inhouse |
-- | 6 | inhouse |
-- ..
-- 3. After running statement(2) I rerun statement(1) again and the result
is then in order as (2).
--
-- Dummy data structure for testing is dumped as shown below.
-- Thank you,
--
-- -kk-
-- 2003/11/26
--
-- MySQL dump 9.09
--
-- Host: localhostDatabase: test
-- --
-- Server version 4.0.16
--
-- Table structure for table `report`
--
CREATE TABLE report (
id int(10) unsigned NOT NULL default '0',
type enum('general','inhouse') NOT NULL default 'general',
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='Available Reports';
--
-- Dumping data for table `report`
--
INSERT INTO report VALUES (1,'general');
INSERT INTO report VALUES (2,'general');
INSERT INTO report VALUES (3,'general');
INSERT INTO report VALUES (4,'general');
INSERT INTO report VALUES (5,'inhouse');
INSERT INTO report VALUES (6,'inhouse');
INSERT INTO report VALUES (7,'inhouse');
INSERT INTO report VALUES (8,'inhouse');
INSERT INTO report VALUES (9,'inhouse');
INSERT INTO report VALUES (10,'inhouse');
INSERT INTO report VALUES (11,'inhouse');
INSERT INTO report VALUES (12,'inhouse');
INSERT INTO report VALUES (13,'inhouse');
INSERT INTO report VALUES (14,'inhouse');
--
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]
converting tables MyISAM to InnoDB
Hi, I recently upgraded from MySQL v3.23 to MySQL 4, and converted my tables from MyISAM to InnoDB by using the ALTER TABLE command. I did this for 16 tables. I know that MyISAM creates seperate data/index files for each table and InnoDB uses a single file for data and log. My question is, once Ive converted the tables from MyISAM to InnoDB, can I delete the .MYI and .MYD files for the tables ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Servers, One Database
Hello - we would like to be able to run multiple servers (probably on multiple networks) that all share one common database. By having more than one MySQL server on seperate networks we can guarantee a higher degree of resilience and stability. Is this possible without writing code to send out updates? Is this part of the current MySQL functionality by any chance? Thank you, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT NULL into NOT NULL should get DEFAULT
Inserting a NULL into a NOT NULL column used to automatically get the DEFAULT value. I realize that it was non-portable, non-standard behavior, but the code relies on it. How do I get this behavior in the 4.0.x server? mysql CREATE TABLE t (a INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t (a) VALUES (NULL); ERROR 1048: Column 'a' cannot be null -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Servers, One Database
http://www.mysql.com/doc/en/Replication.html On Wednesday 26 November 2003 17.49, Mark wrote: Hello - we would like to be able to run multiple servers (probably on multiple networks) that all share one common database. By having more than one MySQL server on seperate networks we can guarantee a higher degree of resilience and stability. Is this possible without writing code to send out updates? Is this part of the current MySQL functionality by any chance? Thank you, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT NULL into NOT NULL should get DEFAULT
On Wednesday 26 November 2003 17.51, Millaway, John wrote: Inserting a NULL into a NOT NULL column used to automatically get the DEFAULT value. I realize that it was non-portable, non-standard behavior, but the code relies on it. How do I get this behavior in the 4.0.x server? mysql CREATE TABLE t (a INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t (a) VALUES (NULL); ERROR 1048: Column 'a' cannot be null Just leave the NOT NULL column out when inserting and it will get set to its default. CREATE TABLE t2 (a INT NOT NULL DEFAULT 0, b INT); INSERT INTO t2 (b) VALUES(666); INSERT INTO t2 SET b=5; In both cases column a will get DEFAULT value. If you only have exactly one column in your table (makes any sense?) this will work: INSERT INTO t VALUES(); Hope it helps Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT NULL into NOT NULL should get DEFAULT
mysql CREATE TABLE t (a INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t (a) VALUES (NULL); ERROR 1048: Column 'a' cannot be null Just leave the NOT NULL column out when inserting and it will get set to its default. It's not just one sql statement. There is a lot of code that relies on the DEFAULT values being automatically added. I'm guessing this is a bug because it's supposed to work, according to the manual: http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html# constraint_NOT_NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Stefan Kuhn wrote: To the chap who siad its not a DB issue - I will check with Oracle but I'm sure that dropping in a directory in oracle will not give you full access to a database (a clear one that is) The chap was me :-) I'm sure it does on oracle. Once you have an Oracle installation and got hold of all database files (which is easy once an intruder got root on the machine) you have access to all data. Even oracle can't do anything about this, but there might be two difficulties with oracle compared to mysql: You need the oracle software (expensive, but do hackers buy software?) and it might be that the files are spread all over the computer and hard to find. But basically, it is the same with oracle (but I never used oracle, this is common sense). Stefan It isn't quite as simple as copying the datafiles to a new server and opening the Oracle database. There are controlfiles to deal with and a somewhat complex process to follow. But, Oracle documentation and Oracle database software is freely downloadable over the net, so a determined theif would be able to access your data without too much problem. It is far easier, however, if you can root an Oracle box, to become the software owner, change the sys/system password (database root), export the database and either import that file into another Oracle database or just do a strings on it to get readable data. You can do all that, anyway, faster than copying all of the datafiles off the server. -- Glenn Stauffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT NULL into NOT NULL should get DEFAULT
At 11:51 -0500 11/26/03, Millaway, John wrote: Inserting a NULL into a NOT NULL column used to automatically get the DEFAULT value. I realize that it was non-portable, non-standard behavior, but the code relies on it. How do I get this behavior in the 4.0.x server? mysql CREATE TABLE t (a INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t (a) VALUES (NULL); ERROR 1048: Column 'a' cannot be null Two points: - Inserting NULL into a NOT NULL column results in an error for a single-row INSERT. The behavior you show is expected. - Inserting NULL into a NOT NULL column inserts a value when this occurs in a multiple-row INSERT. However, the value inserted is not the DEFAULT value as specified in the column definition, it is the default value for the column *type*. Run this input using mysql -f db_name: DROP TABLE t; CREATE TABLE t (i INT NOT NULL DEFAULT 1); INSERT INTO t (i) VALUES(NULL); SELECT * FROM t; INSERT INTO t (i) VALUES(NULL),(NULL); SELECT * FROM t; The first INSERT will fail. The second INSERT will insert two values of 0 (not 1). See last part of: http://www.mysql.com/doc/en/INSERT.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replicating blob
This is my current setup, two MySQL servers. One master, the other slave. Suppose I created a table with one column named 'image', which is of type BLOB. Now, if I inserted binary data from an image file (using perl or something) into that column on the master, how will replication be handled? because the data is binary. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replicating blob
At 12:53 -0500 11/26/03, Mayuran wrote: This is my current setup, two MySQL servers. One master, the other slave. Suppose I created a table with one column named 'image', which is of type BLOB. Now, if I inserted binary data from an image file (using perl or something) into that column on the master, how will replication be handled? because the data is binary. Thanks Why does it matter that the data values are binary? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique Index efficiency query
Hello, Let me just outline the table in question first. I have a rather large (40,000,000 rows) table as follows: Table: MessageIDs Create Table: CREATE TABLE `MessageIDs` ( `mid_msgid` char(96) NOT NULL default '', `mid_fileid` int(10) unsigned NOT NULL default '0', `mid_segment` smallint(5) unsigned NOT NULL default '0', `mid_date` int(10) unsigned NOT NULL default '0', `mid_bytes` mediumint(8) unsigned NOT NULL default '0', KEY `fid_msgid` (`mid_fileid`,`mid_msgid`(5)), KEY `fid_bytes` (`mid_fileid`,`mid_bytes`), KEY `mid_date` (`mid_date`) ) TYPE=MyISAM PACK_KEYS=1 DELAY_KEY_WRITE=1 Index details: mysql show indexes from MessageIDs; +++---+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++---+--+-+---+-+--++--++-+ | MessageIDs | 1 | fid_msgid |1 | mid_fileid | A | 1114302 | NULL | NULL | | BTREE | | | MessageIDs | 1 | fid_msgid |2 | mid_msgid | A | 20057449 |5 | NULL | | BTREE | | | MessageIDs | 1 | fid_bytes |1 | mid_fileid | A | 1114302 | NULL | NULL | | BTREE | | | MessageIDs | 1 | fid_bytes |2 | mid_bytes | A | 40114898 | NULL | NULL | | BTREE | | | MessageIDs | 1 | mid_date |1 | mid_date| A | 1744126 | NULL | NULL | | BTREE | | +++---+--+-+---+-+--++--++-+ Now, what I want to do with this table is create a unique index on (mid_fileid, mid_segment). How does MySQL deal with ensuring that a unique index doesn't end up non-unique with a table this large? Is making this index going to proportionally slow down my inserts as the table grows? Would I be better making it a non-unique index, and doing a select to ensure I'm not inserting a duplicate? Thanks for any tips, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Slowed With new normalized structure (sort across two tables)
Hi All; I inherited an old Access database at work which was not at all normalized. I then managed to normalize the schema, but I am running into problems. First let me show you the two tables I need to query: -- # Host: 10.1.1.14 # Database: pricelink3 # Table: 'Item' # CREATE TABLE `Item` ( `Itemcode` char(16) NOT NULL default 'INVALID', `Supplier_ID` int(10) unsigned NOT NULL default '0', `Pricefile_ID` int(10) unsigned NOT NULL default '0', `Itemcode_Stripped` char(16) NOT NULL default '', `RowNum` int(10) unsigned NOT NULL auto_increment, `LastChanged` timestamp(14) NOT NULL, `Description` char(24) NOT NULL default 'No Description Available', `ListPrice` decimal(10,2) NOT NULL default '0.00', `Price1` decimal(10,2) NOT NULL default '0.00', `Price2` decimal(10,2) NOT NULL default '0.00', `Price3` decimal(10,2) NOT NULL default '0.00', `Price4` decimal(10,2) NOT NULL default '0.00', `Price5` decimal(10,2) NOT NULL default '0.00', `Cost` decimal(10,2) NOT NULL default '0.00', `CoreCost` decimal(10,2) NOT NULL default '0.00', `CoreSelling` decimal(10,2) NOT NULL default '0.00', `Weight` decimal(10,2) NOT NULL default '0.00', `Package` smallint(5) unsigned default NULL, `PriceEffectiveDate` date default NULL, `Barcode` char(40) default NULL, `URL` char(255) default NULL, `Popularity` char(2) default NULL, `File_ID` mediumint(8) unsigned default NULL, PRIMARY KEY (`Supplier_ID`,`Itemcode`), UNIQUE KEY `RowNum` (`RowNum`), KEY `Pricefile_ID` (`Pricefile_ID`), KEY `UpdateIndex` (`Itemcode`), KEY `FileRef` (`File_ID`), KEY `lastchang` (`LastChanged`), FOREIGN KEY (`Supplier_ID`) REFERENCES `Supplier` (`Supplier_ID`) ON UPDATE CASCADE, FOREIGN KEY (`Pricefile_ID`) REFERENCES `Pricefile` (`Pricefile_ID`) ON UPDATE CASCADE, FOREIGN KEY (`File_ID`) REFERENCES `File` (`File_ID`) ON UPDATE CASCADE ) TYPE=InnoDB; # Host: 10.1.1.14 # Database: pricelink3 # Table: 'Pricefile' # CREATE TABLE `Pricefile` ( `Pricefile_ID` int(10) unsigned NOT NULL auto_increment, `ProductGroup` char(3) NOT NULL default '', `Affiliation_ID` int(10) unsigned NOT NULL default '0' PRIMARY KEY (`Pricefile_ID`), KEY `ProductGroup` (`ProductGroup`,`Affiliation_ID`), KEY `newtry` (`Affiliation_ID`), FOREIGN KEY (`ProductGroup`, `Affiliation_ID`) REFERENCES `Productgroup` (`ProductGroup`, `Affiliation_ID`) ON UPDATE CASCADE ) TYPE=InnoDB; Pricefile create has been trimmed to remove columns not of interest. Now previously all Item data was in a single table, with ProductGroup and Itemcode information in the same table. The old query I wanted to run needed an ORDER BY productgroup, itemcode Clause, and it could be easily optimised because the two columns were in the same table. In fact, the whole query was on one table and involved no joins, and data returned very quickly. Now here's my current query: -- SELECT Pricefile.Affiliation_ID AS AffiliationID, Item.Supplier_ID AS SupplierID, Pricefile.Pricefile_ID AS PricefileID, CONCAT(Pricefile.Productgroup, '-') AS ProductGroup, Item.ItemCode AS Itemcode, Item.Description AS Description, Item.ListPrice AS ListPrice, Item.Price1 AS Price1, Item.Price2 AS Price2, Item.Price3 AS Price3, Item.Price4 AS Price4, Item.Price5 AS Price5, Item.Cost AS Cost, Item.CoreCost AS CoreCost, Item.CoreSelling AS CoreSelling, Item.Weight AS Weight, Item.Package AS Package, Item.PriceEffectiveDate AS PriceEffectiveDate, Item.Popularity AS Popularity, Item.RowNum AS rowid, Item.LastChanged AS lastchanged, Item.URL as url, Item.Barcode AS barcode From Item, Pricefile WHERE Item.Pricefile_ID = Pricefile.Pricefile_ID AND Pricefile.Affiliation_ID = 1 ORDER BY Productgroup, Itemcode; --- And it has the following EXPLAIN: +---+--++--+ -++--+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--++--+ -++--+-- ---+ | Pricefile | ref | PRIMARY,newtry | newtry | 4 | const | 317 | Using temporary; Using filesort | | Item | ref | Pricefile_ID | Pricefile_ID | 4 | Pricefile.Pricefile_ID | 216 | | +---+--++--+ -++--+-- ---+ Now to me the problem seems to be a matter of the sort. The temporary table is turning this query into a slow monster (there are 800,000 rows in the result set). Here's the SHOW INDEX for
RE: Inserting data into table1 should insert data into table2's FK ???
You can't INSERT, but with mysql 4.0.15 and better, you can ON UPDATE CASCADE and ON DELETE CASCADE Daevid Vincent http://daevid.com -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 1:24 AM To: [EMAIL PROTECTED] Subject: Re: Inserting data into table1 should insert data into table2's FK ??? Paul Fine [EMAIL PROTECTED] wrote: If I have two tables with the first table containing a field which is FK of table 2, when I insert a value into this field, should it not automagically insert a value into that PK in the second table? No, it should not insert data in the child table. You should insert data manually. -- 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]
Anyone using the Veritas Cluster Agent for MySQL?
If so, could you ping me off-list? I'd just like to ask a few quick questions. Thanks, Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 73 days, processed 2,905,787,388 queries (455/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone using the Veritas Cluster Agent for MySQL?
I would rather see these questions and answers in the list if you don't mind. We are setting-up a MySQL cluster right now with active/passive on the master node and 6 slave nodes. We are using GLP/in-house tools for our setup but we are always interested in seeing what else is out there. Thanks! Mike On 11/26/03 2:22 PM, Jeremy Zawodny [EMAIL PROTECTED] wrote: If so, could you ping me off-list? I'd just like to ask a few quick questions. Thanks, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Primary key
Good evening people. I'm trying to create a simple table via MySQl Navigator. The table il structured in this way dt_amt DateNot NullPrimary Key operINT Not NullPrimary Key amount Decimal(3,3)Null--- but when I choose fire it shows me a multiple primary key defined message and doesn't make me create the table. Isn't MySql able to handle multiple primary key or it's a navigator bug? In the same day I can receive data from different operators. Thanks in advance D. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Primary key
getting_out wrote: Good evening people. I'm trying to create a simple table via MySQl Navigator. The table il structured in this way dt_amtDateNot NullPrimary Key operINTNot NullPrimary Key amountDecimal(3,3)Null--- but when I choose fire it shows me a multiple primary key defined message and doesn't make me create the table. Isn't MySql able to handle multiple primary key or it's a navigator bug? In the same day I can receive data from different operators. Thanks in advance D. Ive never used MySQL navigator, but I think you should assign the primary keys in the same line, like so: dt_amtDateNot Null operINTNot Null amountDecimal(3,3)Null primary key (dt_amt, oper) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Primary key
At 20:50 +0100 11/26/03, getting_out wrote: Good evening people. I'm trying to create a simple table via MySQl Navigator. The table il structured in this way dt_amt DateNot NullPrimary Key operINT Not NullPrimary Key amount Decimal(3,3)Null--- but when I choose fire it shows me a multiple primary key defined message and doesn't make me create the table. Isn't MySql able to handle multiple primary key or it's a navigator bug? You cannot define multiple primary keys using PRIMARY KEY. The name of a PRIMARY KEY is PRIMARY, and you'd end up with two indexes having the same name. To work around this, use a UNIQUE index instead for one of them. In the same day I can receive data from different operators. Thanks in advance D. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
putting an array in a column
I'm new at SQL, and am teaching myself. I hope this isn't too basic a question, but I can't find any reference to this and I'd like not to hunt-and-peck yet another issue, just this once. I have a PHP array which is just a sequence of numbers: $ARA = [3,4,7,2,6]. I would like to stash this array in a column of a db, recalling and altering it from time to time. Of course, $ARA isn't a number of any sort. Is it a string? Do I need to dismantle it in PHP, re-assemble a string = 3,4,...6, then disassemble the string and reassemble an array once it's back in PHP? Any help will be deeply appreciated. Paul Freedman
Re: Disorder result with ORDER BY with ENUM, INT
Kriengkrai J. wrote: -- System: MySQL 4.0.13, 4.0.16 on Linux x86 -- Table type: MyISAM, InnoDB -- Description / How-To-Repeat: -- 1. When I use -- SELECT id, type FROM test.report ORDER BY type, id; -- the result is in wrong order -- -- -- ++-+ -- | id | type| -- ++-+ -- | 4 | general | -- | 3 | general | -- | 1 | general | -- | 2 | general | -- | 5 | inhouse | -- | 6 | inhouse | -- .. It is sorted EXACTLY as you specified. First by type, and then by ID. If you want it sorted first by ID, then do ORDER BY id, type; But, if your ID field is unique, then adding ,type doesn't do ANYTHING. -- 2. But when I use -- SELECT id, type FROM test.report ORDER BY type AND id; -- the result is in right order -- -- -- ++-+ -- | id | type| -- ++-+ -- | 1 | general | -- | 2 | general | -- | 3 | general | -- | 4 | general | -- | 5 | inhouse | -- | 6 | inhouse | -- .. The AND between the two fields is doing a BINARY AND on the two values. It is meaningless in this case. 1 AND general = 0 2 AND general = 0 5 AND inhouse = 0 So the system is just giving them to you in the natural order, as if you didn't have an order by clause. -- 3. After running statement(2) I rerun statement(1) again and the result is then in order as (2). you probably reversed the field order when you re-ran statement 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compilation fails for mysql-3.23.53
Description: g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local\ -DDATADIR=\/usr/local/var\ -DSHAREDIR=\/usr/local/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/src/utils/mysql-3.23.53/include -DHAVE_RWLOCK_T -c mysqld.cc mysqld.cc: In function `void* handle_connections_sockets(void*)': mysqld.cc:2410: invalid conversion from `size_socket*' to `socklen_t*' mysqld.cc:2476: invalid conversion from `size_socket*' to `socklen_t*' gmake[3]: *** [mysqld.o] Error 1 How-To-Repeat: Fix: Submitter-Id: submitter ID MySQL support: none Synopsis: compilation failure Severity: critical Category: mysql Class: sw-bug Release: mysql-3.23.53 (Source distribution) Environment: System: SunOS myhostname 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-Enterprise Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc /opt/SUNWspro/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/3.1/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1711920 Jun 13 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 28 1999 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1125872 Jun 13 2002 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1711920 Jun 13 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 28 1999 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1125872 Jun 13 2002 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-innodb --with-berkeley-db --enable-thread-safe-client 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX=gcc Perl: This is perl, version 5.005_03 built for sun4-solaris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone using the Veritas Cluster Agent for MySQL?
On Wed, Nov 26, 2003 at 02:30:42PM -0500, Mike Minard wrote: I would rather see these questions and answers in the list if you don't mind. We are setting-up a MySQL cluster right now with active/passive on the master node and 6 slave nodes. We are using GLP/in-house tools for our setup but we are always interested in seeing what else is out there. Well, I don't expect to convince anyone to give out a phone number on the list so I can spend 10 minutes asking the questions I'd like to ask. But I'll gladly summarize on the list. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 73 days, processed 2,908,943,374 queries (455/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone using the Veritas Cluster Agent for MySQL?
I couldn't ask for more :) Mike On 11/26/03 3:40 PM, Jeremy Zawodny [EMAIL PROTECTED] wrote: On Wed, Nov 26, 2003 at 02:30:42PM -0500, Mike Minard wrote: I would rather see these questions and answers in the list if you don't mind. We are setting-up a MySQL cluster right now with active/passive on the master node and 6 slave nodes. We are using GLP/in-house tools for our setup but we are always interested in seeing what else is out there. Well, I don't expect to convince anyone to give out a phone number on the list so I can spend 10 minutes asking the questions I'd like to ask. But I'll gladly summarize on the list. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Security Question
At 07:22 AM 11/26/2003, you wrote: Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt Not true. There are some databases that can encrypt records on the fly without any speed degradation ( 1%) using either Blowfish or AES. The data record, index, blob fields (memos) are all encrypted so if someone walks away with your database files, they are all gibberish. The transmission of the password over the network is also encrypted. See www.advantagedatabase.com for a Windows/Linux solution. (Unfortunately their free ALS version has a license agreement that does NOT permit its use on a web server.) If you have physical access to the web server then simply entering the password will get the database app up and running. Or there are various means to send the encrypted time sensitive password to the webserver so it can open the database. Anyone sniffing for the password will be out of luck. I too would love to have MySQL encrypt the records on he fly, especially if it is on a shared webserver. OS security will only get you so far. Other database companies have implemented transparent record encryption quite effectively, and I'm still waiting for MySQL to realize the importance of encryption. Mike ( holding breath :-0 ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Curley, Thomas wrote: I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! As all the other posters have mentioned, you should have tight file level security set up. However, if you use basic mysql user authentication, even copying the files over shouldn't allow them to view the information in a database since they would need the mysql user/passwd to do anything. Which got me to thinkingis this the case? If I am using MyISAM tables and just port them over to a different box with a different security scheme, would I be allowed to view those MyISAM tables? Also, is this the case for InnoDB as well? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
At 16:13 -0500 11/26/03, Kevin Carlson wrote: Curley, Thomas wrote: I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! As all the other posters have mentioned, you should have tight file level security set up. However, if you use basic mysql user authentication, even copying the files over shouldn't allow them to view the information in a database since they would need the mysql user/passwd to do anything. Which got me to thinkingis this the case? If I am using MyISAM tables and just port them over to a different box with a different security scheme, would I be allowed to view those MyISAM tables? Also, is this the case for InnoDB as well? Sure. That's why you establish filesystem level access privileges so that only the mysql user can copy them in the first place. If someone can copy your database files, you're hosed. All the attacker need do is start the server with --skip-grant-tables, and he can can connect to it with no password, and has complete access to any files managed by the server. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Primary key
Mayuran wrote: Ive never used MySQL navigator, but I think you should assign the primary keys in the same line, like so: dt_amtDateNot Null operINTNot Null amountDecimal(3,3)Null primary key (dt_amt, oper) thats, ok. It's a sql navigator bug! I had created the table using the primary key (field_1, filed_2, ...). I think I'm gonna remove mysql-navigator. I've always used console and i think it's always better. Slower to use better and more powerful :) While reading the documentation, I've discovered the command show create table table_name it's great! I didn't remember some options for creating table but I remember I've used in another table :) thanks a lot D. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with merging tables
Yeah the problem was the tables I created were not MyISAM. Once I changed
it; EVERYTING GONNA BE ALRIGHT!
/T
on 11/25/03 7:20, Victoria Reznichenko at [EMAIL PROTECTED]
wrote:
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I am getting the following error when I try to access a merged table:
This:
Select * from subjects_2; or insert into subjects_2 (subj_id) values('10');
Returns:
ERROR 1017: Can't find file: 'subjects_2.MRG' (errno: 2)
Any Ideas what this means and how I can correct it. I can access the two
tables merged by this table, just not the merged table itself.
Merging tables are new to me so take it easy on my novice self...
Does file 'subjects_2.MRG' exist in the database directory?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avg Queries per second...... per second
Greetings, From what I can gather the Queries per second average quoted by status is a pure division of Questions by Uptime in show status. Is there a way to flush these figures periodically? I want to be able to set the bin interval for this average, otherwise fluctuations get smoothed out. Many Thanks, Trevor
Re: putting an array in a column
My apologies. The answer was in PHP-land. - Original Message - From: Paul Freedman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 3:26 PM Subject: putting an array in a column I'm new at SQL, and am teaching myself. I hope this isn't too basic a question, but I can't find any reference to this and I'd like not to hunt-and-peck yet another issue, just this once. I have a PHP array which is just a sequence of numbers: $ARA = [3,4,7,2,6]. I would like to stash this array in a column of a db, recalling and altering it from time to time. Of course, $ARA isn't a number of any sort. Is it a string? Do I need to dismantle it in PHP, re-assemble a string = 3,4,...6, then disassemble the string and reassemble an array once it's back in PHP? Any help will be deeply appreciated. Paul Freedman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL Support
Hello, we currently have MySQL ver., 4.0.15 RPM installed on a RH/linux 7.3 system, and would like to enable SSL support, we have openssl installed. The SHOW VARIABLES indicates that SSL support is disabled, have_openssl=NO Is it possible to enable with our my.cnf file thx's Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL Support
At 0:05 + 11/27/03, [EMAIL PROTECTED] wrote: Hello, we currently have MySQL ver., 4.0.15 RPM installed on a RH/linux 7.3 system, and would like to enable SSL support, we have openssl installed. The SHOW VARIABLES indicates that SSL support is disabled, have_openssl=NO Is it possible to enable with our my.cnf file I believe you'll need to install the MySQL-Max RPM to get SSL support. thx's Mickalo -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Attributes
Hello, I was wondering if someone could provide a brief description for both of the following attributes within the 'db' table: 'create_tmp_table_priv' and 'References_priv' Any information you can provide will be helpful. Thanks, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Attributes
At 16:20 -0800 11/26/03, Aaron Efland wrote: Hello, I was wondering if someone could provide a brief description for both of the following attributes within the 'db' table: 'create_tmp_table_priv' and 'References_priv' The first allows you to create temporary tables with CREATE TEMPORARY TABLE. The second is unused. Any information you can provide will be helpful. Thanks, Aaron -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avg Queries per second...... per second
On Wed, Nov 26, 2003 at 02:16:16PM -0800, trevor%tribenetwork.com wrote: Greetings, From what I can gather the Queries per second average quoted by status is a pure division of Questions by Uptime in show status. Is there a way to flush these figures periodically? Yes, you can use FLUSH STATUS. I want to be able to set the bin interval for this average, otherwise fluctuations get smoothed out. You could also use mytop with a longer refresh cycle. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 74 days, processed 2,914,544,056 queries (455/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: check point command
On Wed, Nov 19, 2003 at 05:09:26PM -0800, kp gbr wrote: How do I perform checkpoint in MySQL. You cannot. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 74 days, processed 2,914,655,888 queries (455/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique Index efficiency query
Hi Chris, It doesn't take MySQL any more or less time to update a unique index than a non-unique one. :-) Hope that helps. Matt - Original Message - From: Chris Elsworth Sent: Wednesday, November 26, 2003 12:14 PM Subject: Unique Index efficiency query Hello, Let me just outline the table in question first. I have a rather large (40,000,000 rows) table as follows: Table: MessageIDs Create Table: CREATE TABLE `MessageIDs` ( `mid_msgid` char(96) NOT NULL default '', `mid_fileid` int(10) unsigned NOT NULL default '0', `mid_segment` smallint(5) unsigned NOT NULL default '0', `mid_date` int(10) unsigned NOT NULL default '0', `mid_bytes` mediumint(8) unsigned NOT NULL default '0', KEY `fid_msgid` (`mid_fileid`,`mid_msgid`(5)), KEY `fid_bytes` (`mid_fileid`,`mid_bytes`), KEY `mid_date` (`mid_date`) ) TYPE=MyISAM PACK_KEYS=1 DELAY_KEY_WRITE=1 Index details: mysql show indexes from MessageIDs; +++---+--+-+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++---+--+-+ ---+-+--++--++-+ | MessageIDs | 1 | fid_msgid |1 | mid_fileid | A | 1114302 | NULL | NULL | | BTREE | | | MessageIDs | 1 | fid_msgid |2 | mid_msgid | A |20057449 |5 | NULL | | BTREE | | | MessageIDs | 1 | fid_bytes |1 | mid_fileid | A | 1114302 | NULL | NULL | | BTREE | | | MessageIDs | 1 | fid_bytes |2 | mid_bytes | A |40114898 | NULL | NULL | | BTREE | | | MessageIDs | 1 | mid_date |1 | mid_date| A | 1744126 | NULL | NULL | | BTREE | | +++---+--+-+ ---+-+--++--++-+ Now, what I want to do with this table is create a unique index on (mid_fileid, mid_segment). How does MySQL deal with ensuring that a unique index doesn't end up non-unique with a table this large? Is making this index going to proportionally slow down my inserts as the table grows? Would I be better making it a non-unique index, and doing a select to ensure I'm not inserting a duplicate? Thanks for any tips, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Generating UPDATE in loop, how to deal with pesky comma
I'm generating a dynamic UPDATE command in a loop. The problem is the stupid
comma.
Given this general idea, how do I handle the pesky comma. It's either in the
front and in the way, or on the trailing end and in the way...
I either end up with something like this:
mysql UPDATE contact_table SET , contact_fname = 'Helen', contact_phone =
'(132) 316-1972' WHERE contact_id = '99' LIMIT 1;
Or this:
mysql UPDATE contact_table SET contact_fname = 'Helen', contact_phone =
'(132) 316-1972', WHERE contact_id = '99' LIMIT 1;
Depending on where I place my comma.
Is there some 'place holder' command, like 1=1 or something that I could
use like so:
$SQL = UPDATE .$table. SET 1=1 ;
So that the , will work properly?
I tried a few things, but all give syntax errors...
mysql UPDATE contact_table SET 1=1, contact_fname = 'Helen', contact_lname
= 'Wadel', contact_phone = '(132) 316-1972', contact_address1 = 'Main
Street', contact_address2 = 'Apartment 23', contact_city = 'Anyplace',
contact_state = 'HI' WHERE contact_id = '99' LIMIT 1;
- snip
$SQL = UPDATE .$table. SET ;
foreach($FIELDS as $field)
{
switch($field['type'])
{
case name:
$SQL .= ,
.$field['column']. =
'.$DATA[$field['type']][mt_rand(0,count($DATA[$field['type']])-1)].';
break;
case phone_fax:
$SQL .= sprintf(,
.$field['column']. = '(%03u) %03u-%04u', mt_rand(000,999),
mt_rand(000,999), mt_rand(,));
break;
case ipaddr:
$SQL .= sprintf(,
.$field['column']. = '%03u.%03u.%03u.%03u', mt_rand(000,999),
mt_rand(000,999), mt_rand(000,999), mt_rand(000,999));
break;
}
} //foreach field
$SQL .= WHERE .$pk_column. = '.$id.' LIMIT 1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Generating UPDATE in loop, how to deal with pesky comma - SOLVED
Well, how about that. Using PHP, it just get's better each time! I found
this little snippet and adjusted accordingly...
http://us2.php.net/manual/en/function.implode.php
while(list($column, $value) = each($FOO))
{
$upd[] = $column = '$value'; //this will loop until sql is built
}
$SQL = UPDATE table set .implode(',',$upd). Where blah = 3 LIMIT 5;
//now do it
$result = mysql_query($SQL,$db);
Daevid Vincent
http://daevid.com
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 4:54 PM
To: [EMAIL PROTECTED]
Subject: Generating UPDATE in loop, how to deal with pesky comma
I'm generating a dynamic UPDATE command in a loop. The
problem is the stupid
comma.
Given this general idea, how do I handle the pesky comma.
It's either in the
front and in the way, or on the trailing end and in the way...
I either end up with something like this:
mysql UPDATE contact_table SET , contact_fname = 'Helen',
contact_phone =
'(132) 316-1972' WHERE contact_id = '99' LIMIT 1;
Or this:
mysql UPDATE contact_table SET contact_fname = 'Helen',
contact_phone =
'(132) 316-1972', WHERE contact_id = '99' LIMIT 1;
Depending on where I place my comma.
Is there some 'place holder' command, like 1=1 or something
that I could
use like so:
$SQL = UPDATE .$table. SET 1=1 ;
So that the , will work properly?
I tried a few things, but all give syntax errors...
mysql UPDATE contact_table SET 1=1, contact_fname = 'Helen',
contact_lname
= 'Wadel', contact_phone = '(132) 316-1972', contact_address1 = 'Main
Street', contact_address2 = 'Apartment 23', contact_city = 'Anyplace',
contact_state = 'HI' WHERE contact_id = '99' LIMIT 1;
- snip
$SQL = UPDATE .$table. SET ;
foreach($FIELDS as $field)
{
switch($field['type'])
{
case name:
$SQL .= ,
.$field['column']. =
'.$DATA[$field['type']][mt_rand(0,count($DATA[$field['type']]
)-1)].';
break;
case phone_fax:
$SQL .= sprintf(,
.$field['column']. = '(%03u) %03u-%04u', mt_rand(000,999),
mt_rand(000,999), mt_rand(,));
break;
case ipaddr:
$SQL .= sprintf(,
.$field['column']. = '%03u.%03u.%03u.%03u', mt_rand(000,999),
mt_rand(000,999), mt_rand(000,999), mt_rand(000,999));
break;
}
} //foreach field
$SQL .= WHERE .$pk_column. = '.$id.' LIMIT 1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't deflate MySQL4.0 tar.gz file
Hi, I'm trying to get the MySQL-4.0 for linux (mysql-standard-4.0.16-pc-linux-i686.tar.gz), from the mirror mysql.secsup.org but it has a problem when attempting to uncompress the file (get unexpected EOF in archive). I'm getting this by using the ftp utility on a Linux server. It clearly states that it's transferring in binary mode. I've downloaded the 3.23.58 from the same site and it extracts fine. I would like to use the 4.0 though. Does anyone have any idea why this one has problems. I've heard that if it is compressed on a different machine (such as Solaris) that the gzip is slightly different or something and it causes problems. Does anyone have any suggestions? Jeff
MySqlManager.rc file missing in mysql source for windows
hi all., i tried to compile mysql-3.23.58 source distribution for windows using VisualStudio 6 and found that MySqlManager.rc file missing and hence the compilation failed , The MySqlManeger.rc file is not available in the MySqlManager folder downloaded from mysql home site. plz help me out insolving the problem. thanks and regards! Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySqlManager.rc file missing in mysql source for windows
hi all., i tried to compile mysql-3.23.58 source distribution for windows using VisualStudio 6 and found that MySqlManager.rc file missing and hence the compilation failed , The MySqlManeger.rc file is not available in the MySqlManager folder downloaded from mysql home site. plz help me out insolving the problem. thanks and regards! Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
At 03:21 PM 11/26/2003, you wrote: If someone can copy your database files, you're hosed. All the attacker need do is start the server with --skip-grant-tables, and he can can connect to it with no password, and has complete access to any files managed by the server. Paul Curley, And of course if they have physical access to the machine they can remove your hard drive and put them into their own machine as a slave. Hot swapable drives makes removal fast and easy; you don't even need a screwdriver. So if your data is worth something, make sure there are good locks on the door and check everyone's bag on the way out.g If you think this can't happen, a mega bookstore opened up in town and they had their file sever/database sever sitting beside a desk in the common area. I guess they were in a hurry to set it up and get the terminals up and running. Well a few days later the system went down and in a few minutes the techie went over to check it out. Well, their tower computer had disappeared. Apparently someone had disconnected (or cut the cables) it and snuck it out the door under a trench coat. It took less than 60 seconds and their data was gone, customer lists, vendor info, and credit card data now belonged to someone else. I don't know what database they were using, but once your hard drives are gone or copied or backed up, your data is vulnerable unless you're using encryption that is independent of the OS. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Generating UPDATE in loop, how to deal with pesky comma - SOL VED
this is in which version of MYSQL..
One cant write procedures and functions in MYSQL 4.0.12...
Regards
Swati kalia
http://www.mecklai.com
where risk meets its match
***
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 27, 2003 6:41 AM
To: [EMAIL PROTECTED]
Subject: RE: Generating UPDATE in loop, how to deal with pesky comma -
SOLVED
Well, how about that. Using PHP, it just get's better each time! I found
this little snippet and adjusted accordingly...
http://us2.php.net/manual/en/function.implode.php
while(list($column, $value) = each($FOO))
{
$upd[] = $column = '$value'; //this will loop until sql is built
}
$SQL = UPDATE table set .implode(',',$upd). Where blah = 3 LIMIT 5;
//now do it
$result = mysql_query($SQL,$db);
Daevid Vincent
http://daevid.com
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 4:54 PM
To: [EMAIL PROTECTED]
Subject: Generating UPDATE in loop, how to deal with pesky comma
I'm generating a dynamic UPDATE command in a loop. The
problem is the stupid
comma.
Given this general idea, how do I handle the pesky comma.
It's either in the
front and in the way, or on the trailing end and in the way...
I either end up with something like this:
mysql UPDATE contact_table SET , contact_fname = 'Helen',
contact_phone =
'(132) 316-1972' WHERE contact_id = '99' LIMIT 1;
Or this:
mysql UPDATE contact_table SET contact_fname = 'Helen',
contact_phone =
'(132) 316-1972', WHERE contact_id = '99' LIMIT 1;
Depending on where I place my comma.
Is there some 'place holder' command, like 1=1 or something
that I could
use like so:
$SQL = UPDATE .$table. SET 1=1 ;
So that the , will work properly?
I tried a few things, but all give syntax errors...
mysql UPDATE contact_table SET 1=1, contact_fname = 'Helen',
contact_lname
= 'Wadel', contact_phone = '(132) 316-1972', contact_address1 = 'Main
Street', contact_address2 = 'Apartment 23', contact_city = 'Anyplace',
contact_state = 'HI' WHERE contact_id = '99' LIMIT 1;
- snip
$SQL = UPDATE .$table. SET ;
foreach($FIELDS as $field)
{
switch($field['type'])
{
case name:
$SQL .= ,
.$field['column']. =
'.$DATA[$field['type']][mt_rand(0,count($DATA[$field['type']]
)-1)].';
break;
case phone_fax:
$SQL .= sprintf(,
.$field['column']. = '(%03u) %03u-%04u', mt_rand(000,999),
mt_rand(000,999), mt_rand(,));
break;
case ipaddr:
$SQL .= sprintf(,
.$field['column']. = '%03u.%03u.%03u.%03u', mt_rand(000,999),
mt_rand(000,999), mt_rand(000,999), mt_rand(000,999));
break;
}
} //foreach field
$SQL .= WHERE .$pk_column. = '.$id.' LIMIT 1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent Two Queries / Problems to be Solved----------Prashant Akerkar
Dear Mr Jeffrey Dyke There are some queries regarding PHP 4.1.As you mentioned in php 4.1 register_globals=off. are set for security reasons. Is it Required to have Form attribute as enctype=multipart/form-data if i have a File Upload on the Particular Form. form name=form1 action=test.php3 method=Post enctype=multipart/form-data i.e input type=file name=filename I am Facing a Problem as when i am removing the enctype attribute in Form object, i am able to pass form values data from one form to another form i.e the destination form will able to retrieve the form values, but from the source form when i keep the enctype attribute in the source form object, i am unable to pass the values i.e the destination form is unable to retrieve the values passed from source form. Second Problem is I am unable to retrieve the image name using $_FILES['filename']['name']; in the Destination form. The source form has the file upload html tag form name=form1 action=test.php3 method=Post enctype=multipart/form-data i.e input type=file name=filename input type=submit value=submit Awaiting Your Prompt Reply, Thanks Regards, Prashant S Akerkar - Original Message - From: [EMAIL PROTECTED] To: Prashant A [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:03 PM Subject: Re: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) you need to use $_POST['test1'] http://www.php.net/register_globals php, by default has register_globals=off. for security reasons...so you'll need to use $_POST, $_GET, $_FILES, $_REQUEST etc. hth jeff Prashant A [EMAIL PROTECTED]To: [EMAIL PROTECTED] thnyou.com cc: Subject: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) 10/28/2003 09:23 AM Dear Sir Our Site is running on Linux/Apache/PHP/MySQL Combination. I am unable to retrieve the Values passed from HTML FORM(test1.html) in a PHP Page(test1.php) Is it something to do with the Web Server Settings or any thing else...? The code is as follows; Test1.html html head titleTest Page/title /head body form name=form1 method=Post action=test1.php input type=text name=text1 value= input type=submit value=Ok /form /body /html Test1.php html head titleTest Page/title /head body form name=form1 ?php echo $text1; ? /FORM /body /html After Submitting the form text1.html to text1.php by adding some text in textbox text1, i am unable to print the textbox value text1 in Text1.php page. Can you help me in this regard asap? Thanks Regards, Prashant S Akerkar. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: [PHP] Urgent Two Queries / Problems to be Solved----------Prashant Akerkar
- Original Message - From: Prashant A [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 5:20 PM Subject: [PHP] Urgent Two Queries / Problems to be Solved--Prashant Akerkar Dear Mr Jeffrey Dyke There are some queries regarding PHP 4.1.As you mentioned in php 4.1 register_globals=off. are set for security reasons. Is it Required to have Form attribute as enctype=multipart/form-data if i have a File Upload on the Particular Form. form name=form1 action=test.php3 method=Post enctype=multipart/form-data i.e input type=file name=filename I am Facing a Problem as when i am removing the enctype attribute in Form object, i am able to pass form values data from one form to another form i.e the destination form will able to retrieve the form values, but from the source form when i keep the enctype attribute in the source form object, i am unable to pass the values i.e the destination form is unable to retrieve the values passed from source form. Second Problem is I am unable to retrieve the image name using $_FILES['filename']['name']; in the Destination form. The source form has the file upload html tag form name=form1 action=test.php3 method=Post enctype=multipart/form-data i.e input type=file name=filename input type=submit value=submit Awaiting Your Prompt Reply, Thanks Regards, Prashant S Akerkar - Original Message - From: [EMAIL PROTECTED] To: Prashant A [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:03 PM Subject: Re: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) you need to use $_POST['test1'] http://www.php.net/register_globals php, by default has register_globals=off. for security reasons...so you'll need to use $_POST, $_GET, $_FILES, $_REQUEST etc. hth jeff Prashant A [EMAIL PROTECTED]To: [EMAIL PROTECTED] thnyou.com cc: Subject: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) 10/28/2003 09:23 AM Dear Sir Our Site is running on Linux/Apache/PHP/MySQL Combination. I am unable to retrieve the Values passed from HTML FORM(test1.html) in a PHP Page(test1.php) Is it something to do with the Web Server Settings or any thing else...? The code is as follows; Test1.html html head titleTest Page/title /head body form name=form1 method=Post action=test1.php input type=text name=text1 value= input type=submit value=Ok /form /body /html Test1.php html head titleTest Page/title /head body form name=form1 ?php echo $text1; ? /FORM /body /html After Submitting the form text1.html to text1.php by adding some text in textbox text1, i am unable to print the textbox value text1 in Text1.php page. Can you help me in this regard asap? Thanks Regards, Prashant S Akerkar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
