Re: Path information into database
Brian E Boothe wrote: so how would i get the Actual file path to the folder into the database, i know ill have to use a file selector in asp / php but i wanna select the folder not the Files, it would be really great if u could help me with these issues, i'm not asking u to Code anything for me , just links of examples and for helpfull boosts along the way thanks soo very much Couple of good examples FileSystemObject (ASP) http://www.google.com/search?hl=enq=asp+fso ADODB (Activex Data Objects Database - ASP) http://www.google.com/search?hl=enq=mysql+adodb+asp Of course, you'll need the odbc connector... If you get stuck on specific queries this is the place to ask, all other issues I would consider: http://www.google.com/search?hl=enq=asp+help I can't say what sites are better on those searches than others. I usually just Google my issue and try and sort it out myself. When I'm really stuck I'll post a question to http://www.Tek-Tips.com You also may want to consider a reading about SQL injection attacks (I searched on ASP, but php can have the same issue if you're also not careful): http://www.google.com/search?hl=enq=asp+mysql+sql+injection I hope those few links get you headed in the right direction :-) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL = Not Working?
Scott Baker wrote:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
In your last insert example, Last is inserted as 17423. Which is not null.
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL = Not Working?
JamesDR wrote:
Scott Baker wrote:
If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?
---
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTO_INCREMENT,
First VarChar(30),
Last VarChar(30) NOT NULL,
Zip INTEGER
);
INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);
In your last insert example, Last is inserted as 17423. Which is not null.
Yup, empty string, the manual says this...
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
However it does say that to enforce NOT NULL you would have to change
the sql_mode
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
EG:
SET SESSION sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo (zip) VALUES (12345);
SET SESSION sql_mode='';
I get an error on the insert statement:
Field 'Last' doesn't have a default value.
You may need to set sql_mode to STRICT_ALL_TABLES before the insert or
do it in my.cnf or as a command line parameter.
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transaction/locking confusion
Marcus Bointon wrote: Hi, (repeat posting) I have a simple PHP function that initialises a process definition. To prevent it happening more than once, I'm wrapping it in a transaction, however, it doesn't seem to be working and I get multiple initialisations. In pseudocode: BEGIN; UPDATE process SET status = 'ready' WHERE id = 123 AND status = 'init' LIMIT 1; ...do other stuff including some INSERTs if other stuff is OK: COMMIT; else ROLLBACK; If I have two simultaneous processes running this script, somehow they are both able to initialise. I guess that if the overlapping transactions are isolated, then both see the process in the 'init' status and that the 'other stuff' part takes long enough that it's reasonably likely to happen. I was under the impression that the UPDATE inside the transaction would lock the row and prevent the later query from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus --Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ With what you've provided us: You can not prevent this. You are running in a transaction which is isolated from any others. One way to prevent this may be to write a pid file in your script, then check for its existence. If it does exist then just die out (script was already running.) I have scripts that perform tasks on regular intervals. Some times the script runs long and may overlap into the next run time. I check for the file's existence, if it does exist just print a message and exit. If it does not exist, touch the file to create it and move on... deleting it later of course. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary into blob
Ed wrote: On Wednesday 07 March 2007 19:28, Jay Pipes wrote: Is there a specific reason you want to store this in a database? Why not use the local (or networked) file system and simply store the metadata about the PDF in the database? Cheers, Jay Hi Jay, Could you explain what you mean by metadata and how you would do it? Thanks -Ed Pretty much you store all the info about the file (since this is billing maybe a billing number customer id etc) then you would reference a path to the file (could be just a name or a full path.) I've done this with images. Stored height, width and file name (along with an ID.) The file name was just that, no path (everything was relative to a 'main' images directory but you can lay it out however you like.) This is usually easier on web apps to do this. You don't have gobs of info in a db, and anybody can backup/restore files. I can see other reasons not to keep them on a file system and on a DB (like cross site) but even then, with the proper replication scheme that can be worked out as well. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Argh! Another Server Has Gone Away
Kévin Labécot wrote: Le 21 févr. 07 à 14:17, Kévin Labécot a écrit : Hi, I know this problem is already on forums and archives but I don't understand mine ! My MySQL Server works well, there are many scripts and website using it, without any problem. Since few days I'm working on a new project and if a do (php) a mysql_connect and a mysql_query() I directly win a Server has gone away. My query is a simple INSERT INTO on an empty table. I already changed my MySQL config to an higher max_allowed_packet and watch all the configuration (setting to higher cache, timeouts ...)... I don't find any problem. Other idea ? :/ Sorry to bother you again with this problem ... I need to fix this problem for my projects... Anyone can help me ? [EMAIL PROTECTED]:~$ mysql --version mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.2 --Kévin Labécot Analyste Programmeur www.cv.labecot.fr --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Anything in the MySQL error log? Also, FWIW, 5.0.27 looks to be the current version of MySQL May try an upgrade? -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Data Vanishing with FireFox
Matt Neimeyer wrote:
I think it's your PHP application; how did you debug your application?
All this is doing is letting our customer add their contacts to the
database.
This is on the quick add form and asks them to enter a first and last
name
and an email.
I debugged by re-writing it temporarily to do this...
$SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID)
VALUES ('
.$_POST[ContFirst].','.$_POST[ContLast]
.','.$_POST[ContEmail].','.$MyID.')';
echo $SQL;
$result = mysql_query($SQL,$db);
On the theory that the echo shouldn't change the contents of the $SQL
variable.
Then on the same client machine, I open both browsers and launch the
page. In
both browsers I can see the exact same statement (including the value of
$MyID)
on the screen but in IE it works and on FireFox it doesn't.
As soon as I see the SQL on the screen I query directly (not through
PHP) to
pull out the records and see that UserID is missing.
If UserID is missing ($MyID) track back and see what sets this. If $MyID
is missing, it may/may not be the browsers fault. (You've not given us
enough code to really help. I have 20 or so 'simple form' apps here all
work fine with both IE and FireFox (inserts/updates/deletes/selects) in
both PHP and MSASP. )
Can you paste in both statements fully filled out from both browsers? So
we can see what the data is looking like (same data.)
As an aside, you may want to be doing SQL injection checking or
sanitizing if you aren't already doing so.
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: We want to get: Windows Server 2003 R2, Standard x64 Edition 2- Dual Core Intel Xeon 5080, 2x2MB Cache, 3.73GHz, 1066MHz FSB 8GB 533MHz (8x1GB), Dual Ranked DIMMs 3- 146GB, SAS, 3.5-inch, 15K RPM Hard Drives What would be the recommended RAID configuration settings for a dedicated MySQL db running on this system? Also, what is the general advice for separating MySQL and the MySQL/Data on different disks? I'm sorry if this sort of question has already been answered. Any help would be appreciated. David. We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Entries
Nicholas Vettese wrote: If I have a multiple choice on a form and want to store that in my DB, then how should I set up my table? I have been reading up on these, but everyone seems to have a different opinion on how to accomplish this task. What I am looking to do give the user a few options to check when submitting a form. select name=sports multiple id=sport_type option value=baseballBaseball/option option value=footballFootball/option option value=soccerSoccer/option option value=hockeyHockey/option /select Would my table look like this: CREATE TABLE sports ( sports_id int(11) not null auto_incremement, sport_name text not null, primary key (sports_id) ); INSERT INTO `tbl_options` VALUES (1, 'Baseball'); INSERT INTO `tbl_options` VALUES (2, 'Football'); INSERT INTO `tbl_options` VALUES (3, 'Soccer'); INSERT INTO `tbl_options` VALUES (4, 'Hockey'); ... Would using text as the way to store make it easier to retrieve the data in a manner that would be readable on a web page? Thanks, Nick I personally would fill the values with the ID numbers myself for a couple of reasons: a) Your sports_id is PK and is auto incremented b) You'll save some bytes for your users for downloading (and save some for yourself) by using a number (just a few bytes per id, opposed to the entire sports name.) Text would make it easier later if you ever needed to put some reay long sports name, but a char field may work in this case as well. ... You would end up with this where the sports_id is used for the value. select name=sports multiple id=sport_type option value=1Baseball/option option value=2Football/option option value=3Soccer/option option value=4Hockey/option /select Tho, this is what *I* would do. It may not be the best solution. You'll have to look at a number of factors before deciding on how to store/display data to/from a database. You'll want something fast and decently scalable, with out seeing the whole picture it is hard to tell you exactly what *you* should be doing. :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Duncan Hill wrote: On Tuesday 06 June 2006 15:38, [EMAIL PROTECTED] wrote: Quoting Barry [EMAIL PROTECTED]: Well my msql doesn't give me any errors using that query. neither a warning. Ditto. usemysql use test; Database changed mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.27 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.21 sec) mysql show warnings; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect datetime value: '0001-01-01 00:00:00' | +-+--+---+ Scanned by mailCritical. Fails here, with no warnings however -- but we have an older 4.0.x version... Wouldn't a DATETIME field be better used in this case? When I use the same queries on a DATETIME field, this works ok. Maybe there is some reason in a larger context that hasn't been explained yet -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving from PowWeb to Rackspace
Brian Dunning wrote: I have a bunch of databases - some are really big, 2GB - on a number of different accounts at PowWeb. I am buying a Rackspace server and want to move everything over -- hopefully all in one night. Can anyone suggest the best way to do this? Would it be to use the Export command in phpMyAdmin? --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I'm not familiar with phpMyAdmin, but I would dump everything to sql files, using the extended insert option then compressing the resulting sql files. Then create a hash (MD5) and ftp the files over, checking the hash on the remote system, uncompressing, and importing. I do something like this with my backups (all automated, except for the checking of the hash on the remote system, I just store the MD5 in an ascii file.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory problems?
Rohit Peyyeti wrote: Thanks for your quick response. Another question: So, what you are saying is that sum of the memory (RES - 20m) of all the mysql processes shown with the 'top' command is not total memory occupied by mysql server? Like 22 threads*22m = 440MB? Also, any idea why this keeps increasing? Thanks, Rohit - Original Message - From: Lars Heidieker [EMAIL PROTECTED] To: Rohit Peyyeti [EMAIL PROTECTED] Sent: Wednesday, February 01, 2006 4:37 PM Subject: Re: Memory problems? All these processes share the same address space (linux way of doing threads) therefor their sizes dont add up. It is correct one thread per connection plus a few from mysql (eg innodb has a few maintenance threads ) On 1 Feb 2006, at 12:00, Rohit Peyyeti wrote: Gurus, We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard. I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2478 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.31 mysqld 2507 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.50 mysqld 2508 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2509 mysql 17 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2510 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2511 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2547 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.58 mysqld 2548 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.27 mysqld 2549 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2550 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.16 mysqld 2758 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2760 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:02.13 mysqld 2835 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:57.11 mysqld 2933 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2947 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2948 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2949 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2950 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2962 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2963 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2964 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2965 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld My question: 1) I see 20 processes in using the 'top' command but I have only 10 open connections (I am using connection pooling). Does it mean that processes shown in the top command and open connections are not related? Can somebody please clarify? 2) At the begining of the server startup, I have noticed that each mysql process under top command begin taking approx 10-12 MB. This kept increasing. The current memory occupied by one mysql process now is 20m (as you can see above). This memory only increases over the period, but I have never seen it coming down throughout the application use. Why is this so? Is there any problem with the application or the way mysql is configured? Any pointers? Thanks in advance, Rohit There is queries going on, so some data will be cached in memory. If memory serves me correctly, Linux won't clear out memory until it has to, so unless you are running low, that memory may stay in use until the Kernel cleans house. Unless you are into swap really badly, I wouldn't worry too much. As an aside, your memory of 1GB seems a bit low to me. If I was you, and I had the money, I would toss in another 1GB. Take a look at your in use numbers. You may see that if you add up all the memory consumption of the system, that this will be greater than what is being displayed as in use. As the other respondent said, this is how Linux works. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Tables on separate hardisks
Melvin Zamora wrote:
Hi MySql,
Would it be possible to have the PK tables on hardisk-A and FK tables on
hardisk-B using only one database?
to envision:
CUSTOMER_MYSQL_DB
CUSTOMER_TBL {PK} [HD-A], CUSTOMER_PURCHASES_TBL {FK} [HD-B]
-Melvin
-
Bring words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
You can do it symlinks (see the manual for this info.)
HTH
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Right platform for huge db with huge RAM
Martins wrote: Hi! I'm trying to figure out which platform to use on my new server. Server's primary and almost only role is mysql, huge databases (can grow up to terabyte or so), so I'm considering enough big ammount of RAM which probably allso will grow over 4GB, so at this point I think x86 32bit becomes problematic becuase of PAE, so I will loose some ammount of RAM for remapping and also I will lose in performance. Is this true this far? So I'm considering to use AMD 64bit, so no problem with RAM? Which system would be faster: dual processor AMD 64bit with RAM4GB or dual Intel Xeon 32bit with RAM4GB? If AMD 64 bit wins, which motherboard would be more reliable and faster to use (2CPUs, DDR2 memory up to 16GB or so)? Thanks in advance! You'll want to go with 64bit either way... I'm an AMD fan, so that aside :-D http://www.tomshardware.com/2003/04/22/duel_of_the_titans/ That lists several benchmarks, and unless you are getting top of the line stuff, that is still valid (IMO, the review was written in 2003...) You say AMD and Xeon, so I'm under the assumption that you are looking at Opteron. opinion I would go with the Opteron for the performance/watt/$ /opinion -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning illegal characters from varchar field
Nathan Gross wrote:
snip
I get two lists from different sources which I merge into the database
via a Java program. Since these two lists themselves sometimes get
their data from the same source, my program first does a SELECT on the
varchar field (unique index) [to ensure that this data is not yet in
the db] before inserting the data. (Actually I am using j2ee
ejbFinds.)
Occasionaly, say 5% of the time, I get a duplicate (create) exception
from the db, even though the db just told my program that the data was
not there!
With my debugger I verified that for these fields, if you try to
find/select this data, mysql will return a null resultset, but yet if
you try to enter this data it will throw the create exception.
Now these [defective] fields always have (1 or more) suspicious
characters like {,[,',, or commas colons, what not. I do not know
which of these is/are the culprits.
So, my little problem is twofold.
1) How to trap this data in the first place in my Java program.
Obviously I need to know what to look for!
2) I do not want to leave my database in this state, so I want to clean it.
2b)I don't mind adding a boolean field to the table to be used as a
flag for all records that have suspect data, before deciding exactly
how to clean it. Although I think I can just replace these characters
with a space, in [almost] all cases.
One more important point. This field, although unique, is NOT the pk.
I can use the PK to correctly select and display the contents of all
fields in the record.
Thank you much.
-nat
Sounds like, to me, that you are not properly sanitizing the information
before doing your select. This is very dangerous and you leave yourself
open to SQL injection attacks. You may want to look at the same time as
you are trying to 'clean' your DB of these special characters. I have
many DB's here with special (illegal) characters, properly sanitized
before inserting, and in my select statements.
If you want more info about SQL injection attacks (elementary must know
for anyone making a DB based app) have a Google on the topic. There are
many ways to avoid it.
Sorry it's slightly OT. But it seems you aren't sanitizing the data in
before using it. Trust no data from any source (even the DB itself) to
be 'clean' :-D
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: important! help plsss
Umit tas wrote: hello i'm umit Tas; i have a problem. i'm writing programme in visual basic 6.0 but i must use MySQL server. i'm installed MySQL server and executing my program (no problem) but my problem is : MySQL must be installed A computer and my programme must be installed B computer in LOCAL area network :( pls help Google: ADO, ODBC, and MyODBC Should get you down the right path :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trojan Horse in MySQL
Anthony Ettinger wrote: hmmm...google is null on that one too. I'd suspect that it's just a false positive, in other words, better to be safe than sorry from Norton's point of view. On 12/1/05, Gary [EMAIL PROTECTED] wrote: I have installed Apache PHP and MySQL over a year ago on a WINXP machine and suddenly a file, presumably there all that time is found to be infected by an upto date Norton anti virus program. Norton does not say what trojan horse. It cannot quarantine nor delete the file so I stopped the MySQL process and renamed the file myself. File is: C:\mysqldata\erqed.dll Anything known about this? please reply to [EMAIL PROTECTED] Gary I don't have that DLL on any of my 2 systems.. anywhere. (4.1 mysql, win2k server) Have you tried a second virus scanner (free) to scan that file? It could be a Trojan placed neatly in the mysql dir to mask itself. You may also want to submit it to the Norton people to see if it really is a virus or a false positive. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem of VB with MySQL
vishal wrote: i am sending the files. all details are in the source code what you have to do is to create a database name test2 and have to set the password of the server. the code will automatically create a table name test2 with two double fields and it will reture the error while adding the record through recordset and after adding a text field it will work and another error is that it doesn't updates the record which filters with where condition. please take a stress to see the code and pls solve me the problem as early as possible. From Vishal Panchamia The code didn't come through. If you could, post it on a website. How are you connecting? What does your connecting string look like? If you are using a DSN, what are parameters? I moved away from a pure ADO way of doing things to directly passing the SQL statements using the ADODB.Connection.Execute parameter (you will need to sanitize the inputs from users, as not to cause SQL injection attacks.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL-MySQL Compatibility Question
J.R. Bullington wrote: Hi All, Quick question -- I have a client who co-owns a server with me. I am a die-hard MySQL guy, they are MSSQL. They have some proprietary Access-frontend/MSSQL-backend financial system that they want to continue to use (i.e. pigheaded and won't convert). Can MS-SQL and MySQL run on the same box and not conflict with each other? I don't have the SQL disks in front of me to test and I was wondering if anyone else ran into this situation. Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid. Thanks! J.R. I have MSSQL + MySQL on the same box here, no issues with that... My server is hardly Big Iron or Small Iron by any reach: P3 933 1GB Ram, SCSI160 non-raid ;-D More memory, if you can do it, is better... (roll on new server...) Best thing to look at is your current load, if you are pushing the limits, adding another RDBMS may cripple that box. As far as negative interaction, I've seen none. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
Merlin wrote: Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Merlin Show us the query that fails, fulltext works ok here. SELECT id,txt,ch FROM fulltxt WHERE txt LIKE %modem; id,txt,ch 0,DSL-modem,DSL-Modem 1,Cable-Modem,Cable-Modem SELECT id,txt,ch FROM fulltxt WHERE txt LIKE dsl%; id,txt,ch 0,DSL-modem,DSL-Modem id is int, txt is text, ch is char. HTH -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL
Nick Jones wrote: Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Yes, you can do it with odbc in excel. Create and ODBC connection using the MySQL driver. (Office XP/Excel XP): Data --Import External Data -- New Database Query -- (select your odbc connection) -- Setup the query (add cols) -Next- select a col. to select the data based upon (if any) -Next- Select a sort col and by (if any) -Next- Select Return Data to Microsoft Excel -Finish- Remember, Excel has a hard limit on the number of rows, (it is not a database.) -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Backup Strategy
Scott Purcell wrote: Hello, After many months of preparation, I am finally going to go live with a project I have created. It is your basic e-commerce site, where I need to make sure I have a current backup, specifically on the orders placed, etc. I am going to run the mysql server on a PC possibly running XP. (Small startup, and Tomcat is running there). I am considering buying a tape drive, and somehow backing up the database periodically throughout the day. I am sure there are some best practices for this. Could anyone enlighten me as to how to back this up, and how often. Up until now, I have always done database exports and imports, so I could use any info. Sincerely Scott I use MySQL dump to dump the contents of the databases at several intervals during the day (before work, during first break, lunch, last break, end of the day), zip the .sql files, and distribute them to two other holding machines. The last dump of the day is put to tape. We aren't 24x7, and during the off times there are no users active (on break/out to lunch) during the dumping/zipping/copying. I haven't noticed any problems with this setup, however, YMMV. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complete server lock
Ben Clewett wrote: Jigal, Thanks. I can confirm that there were no domains used for our permissions. All IP based. Although this may have been the cause, I don't think it was in this case. I think as well that DNS timeouts are in the region of 20 seconds to 2 minutes. Would any person know if there is any other part of MySQL which uses DNS lookups? For instance, logging of some kind? Or any other reason a MySQL daemon would not respond to a kill? Thank for the ideas, Ben Clewett. Jigal van Hemert wrote: Ben Clewett wrote: It had been suggested that our DNS failed prior to this event. I don't think MySQL uses DNS, but I am not entirely sure. If the db, user, etc. tables in the mysql system database (containing privileges, etc.) contain host names instead of IP-addresses I suspect it needs a DNS to resolve these... If I get a state where a 'kill' will not cause MySQL to exit. Is there any other know way to ask MySQL to exit cleanly? MySQL server was probably waiting for a bunch of DNS requests? Until a timeout occurs it will probably keep the connection waiting. This can cause a lot of connections to occur until you reach max_connections at which point it will not accept new connections anymore. Regards, Jigal. I noticed, on my Linux server, that MySQL makes a DNS lookup anyway. Even if the IP is used or not. It normally digs on the PTR record. I had all of my perms IP based, however, it still looked at the PTR record. At the time I hadn't set any up in my DNS server. After I added all the PTR records of the clients that were connecting to the server, it was fast again. ...Something to think about... -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network drive
Ruben Carvalho wrote: Well, thank you very much for your explanation. My problem is I would like to have the data files being saved in a machine behind a proxy but the server running in a machine outside the proxy (the clients don't have access to the machine behind the proxy). Any ideas? Thank you --- [EMAIL PROTECTED] wrote: Ruben Carvalho [EMAIL PROTECTED] wrote on 07/06/2005 11:06:10 AM: I think I haven't understood your question. I guess that in case of a network failure you can have the same behavior as a power shutdown. About the networked drives? Anyone? --- Martijn Tonies [EMAIL PROTECTED] wrote: Hi Ruben, I would like to make a short, quick and simple question. Is it possible to have the following line: innodb_data_home_dir=X:/data/ in a my.ini config file? I'm using windows XP, mysql 4.1.12, X: is a mapped network drive to a Linux folder using samba, all the permissions are set and tested. I have seen this posted many times but without many replies. I want to use a folder in a mapped drive to save my InnoDB data. Is this possible? I don't know this particular answer for MySQL, but I do have 1 question: If the database engine doesn't have control over the files and/or drive, what should it do in the case of a network failure? Let alone the latency of a networked file... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Rúben Carvalho RDBMS over a network: NOT recommended. Not only can you not enforce OS-level locking on your files (maybe you can, I guess it may depend on your device and inteface protocols) but the MOST COMMON bottleneck to database performance is disk I/O. If you went with networked storage, you are not only going to suffer through disk lag (seek time + rotational positioning before the operation can start) but you are incurring network overhead on top of it IN BOTH DIRECTIONS. Unless your network device is flash-only (all memory, no disks), you just cut your throughput by at least 75%. And even if your device is flash-only you will reduce your data throughput by 25-50% (all performance numbers are rough estimates pulled out of my a** but based on the number of extra network hops necessary to get at and read your files). I don't care how fast your network is, networked storage can't be as fast as local disks. Again, it is highly discouraged to use networked storage for anything but the most trivial database uses (small file sizes, low traffic, etc). For any application that requires even modest performance, spend your money on a fast RAID configuration. You will be much happier in the long run. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rúben Carvalho By proxy do you mean firewall? If so, open up/forward the mysql port. Much simpler than trying to get file sharing working through the proxy. I have clients that connect the mysql server through a firewall and there are no issues. Just open up the correct ports and you should be set. It's much more difficult (IMHO) to open up ports for file sharing. Also, if it is a true proxy, this means you will incur even more lag due to the proxy with file sharing. It may be better to keep the files on the machine that is accessible by the clients: then use whatever kind of firewall software/hardware necessary to keep the computer more secure (if that's the aim of the proxy.) If it's necessary to keep the files on the computer behind the proxy for backup purposes, then ftp|sftp etc through the proxy|firewall with hot backup|db dump may be the best option. Again, a quick breakdown of what you are trying to do may lead to better suggestions from the list. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Carlos J Souza wrote: The error as follows: when installation is try to finish, the install try a start de new service MYSQL 4 and does not success I try the install and reinstall many times and nothing. Regards On Wed, 29 Jun 2005 08:39:51 -0400, JamesDR wrote: Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James Is this the error number/message? It almost sounds like you are missing some info in your cnf/ini. Also check out perms on the data/(cnf/ini) directories. Short of the exact error message/number from windows/mysql it'll be hard to figure out what the error is. Check your event log and mysql's error logs for any details in the mysql.err log in your data dir. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
