Re: Looking to hire someone to configure MySQL on Windows Server 2003
At 12:42 2004-09-08, you wrote: Greetings We have installed MySQL 4.0 on a dedicated Windows Server 2003. I have the MySQL server running and it appears all is set to go minus one problem. We need to move a MySQL database from the old host to this server. To do that, I want to use a GUI - preferably DBACentral. However; I am unable to connect to the mySQL server that I have set-up. You mean the new one, right? The one You just set up? I have tried everything but the GUI will not let me connect. If You're _certain_ that it is the DBACentral that does not let You connect, I would presume the misconfiguration is there. I am not convinced that the 3306 port is open Find out. On the 2003 server run netstat and see... If You have the proper tools installed, You can `netstat -an|grep 3306` - that is what I would do first. because I tried putting the DBACentral GUI on the Web Server which allowed me to connect to the local MySQL database (which I cannot connect to from my desktop) but I then could not connect to the one we need to move (which I can connect to from my desktop). OK, so we have _three_ MySQL servers now: 1 old, 1 new and one on the web server, right? And You need to move from old to new, the webserver one does not enter into this, right? The fact that You can connect to the webserver one from localhost is a sensible precaution: after all, only the webserver has a legitimate need to make connections to it. I think You have allowed access to Your database from only the localhost. You need to create user accounts on the DB that are allowed remote access. Of course after You verify that: - the DB starts and does not complain - which port the listener is active on This is all covered in the post-install tasks in the manual and takes but a few minutes (assuming just simple setup). Does anyone have experience with configuring MySQL for Windows 2003? If so, please let me know. I would like to hire you to get this configured correctly so we can connect remotely and move this database. United States people only and you must be able to get it done ASAP - preferably today. Please contact me if you are interested. In a wider scope: what are the MySQL versions of the old and new DBs? Can't You use mysqldump or just set up replication, use the old until You're convinced that new works to Your satisfaction, and cut it over then? Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking to hire someone to configure MySQL on Windows Server 2003
At 13:19 2004-09-08, you wrote: I am going to amalgamate both Your messages. Hi Tomasz There are two MySQL servers - the one at the shared host and the new one that I just set-up on the dedicated. We need to move the MySQL database from the shared host to our dedicated. I am able to connect to the old one but not the new one. Let's give them names: let's call the old one A and the new one B. I believe A lives at the hosting company and B lives in Your office (for now), later You will want to colocate it. Correct so far? Now, to verify the versions, at the mysql prompt execute: SHOW VARIABLES; close to the end of the list there will be a version variable - find it for both hosts - it shouldn't be a problem, but let's try to eliminate that possibility now. Grep is not recognized when I try to do netstat. OK, You might want to install cygwin tools, but let's not worry about this now. Just do 'netstat -an|more' and look for a line that in the second column (local address) has B's IP address, then colon, and 3306. If You only find '127.0.0.1:3306', B is only listening on loopback. If You find no rows with :3306, MySQL is not running (at least not on a standard port). I was told the MySQL database at the shared company is 4.0. Again, I can connect to that one. The new one is 4.0 as well. See above, verify. I have created a user and allowed that user remote access. I presume on B, right? grant all privileges on text.* to 'user'@'%' identified by 'footman's using grant access; 1. You _did_ create database text, right? 2. I do hope that password is a typo: it was 'footmans', right? 3. I'm perplexed by this USING clause... Did You mean WITH GRANT OPTION? I'll assume so. 3. let me rewrite it a bit: is _this_ what You actually issued: mysql GRANT ALL ON text.* TO 'user'@'%' IDENTIFIED BY 'footmans' WITH GRANT OPTION; After You do that, verify: mysql SHOW GRANTS FOR 'user'@'%'; and, more importantly: mysql SHOW GRANTS FOR 'user'@'Your workstation IP, as seen by the server B'; Thanks.Rob At 13:25 2004-09-08, Rob Taylor wrote: And just to clarify. I an connect to MySQL server at the shared hosting company from my desktop. I cannot connect to it from the new server. That probably means that the user You are trying to connect as is not authorized to connect from the machine You are trying to connect from - let's assume the username is kkk, B's IP address is BBB.BBB.BBB.BBB, connect to A then issue: mysql SHOW GRANTS FOR 'kkk'@'BBB.BBB.BBB.BBB'; It says it cannot connect. That leads me to believe that something on the new server (probably the 3306 port) is not letting traffic remotely through it. Remember, B is likely _not_ using port 3306 to connect to DB on A. I'd be very surprised if it did. Consequently this issue is not likely related to B's inability to provide connection to workstation somewhere And a far more general question: any firewalls/NAT between Your workstation and A? What about on Your route to B? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Money data type in MySQL?
At 02:03 2003-12-03, David Garamond wrote: What do people recommend for storing money amounts? I've seen people use NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate and why? This is considering various existing currencies, some having low rates (like IDR, in which you can have large amount up to hundreds of trillions) and some high rates (like USD, in which you can have small amount like 0.1 cent). Are there places/industries which involve values lower than 0.1 cent? Off the top of my head: currency trading. And what about 'factor' field in currency conversion table? Should I use FLOAT, or DOUBLE (do we need 15-16 digit precision?) or NUMERIC (exact numbers). It _really_ depends on Your/ Your application needs. I'd hazard a guess that for most situations FLOAT should do just fine. The factor should range between 1E-3 (e.g. converting IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros). -- dave -- 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: Question about logging in to mysql via PAM or using existing login credentials
At 20:12 2003-09-17, Mike Klein wrote: Question: how is this done? I am getting tired of entering my existing unix login information (same login/pwd) every time I want to login to mysql. I would like to propagate my existing unix credentials (/etc/pwd) or possibly use pam/sasl/etc. Note...of course this isn't the same as mysql/pam interface. I don't want to authenticate THRU mysql, rather I want to authenticate TO mysql... I've checked mysql parms, google'd, etc. and can't find any pointers on this. thanks in advance... Kerberize it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Re: MySQL field data type for ISBN numbers
At 21:08 2003-08-10, you wrote: On Sun, Aug 10, 2003 at 05:25:05PM -0700, James Johnson wrote: I have a MySQL database for books. The ISBN field is set as varchar(15) and I've put a test ISBN number in of 1--111-11. Note that ISBN numbers are a maximum of 13 characters, not 15. Ten digits, three dashes. If you really want to save space, the last digit is just a check digit and can always be determined through a formula on the other digits, so as long as you verify every ISBN before you INSERT it, you can save another digit. Hmmm...And how do You come up with an 'X' as the last digit, as many books on the shelves around me do? I'll snip the rest, since I agree with it all... SQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID hardware suggestions/experience
At 13:14 2003-06-17, Bernd Jagla wrote: Sorry I forgot to mention: We are using IRIS on an Origion2000, 7GB memory, 8 CPUs. I was thinking of spending up to $10K. I also wanted the redundant data for speeding up the seeks, I also need to speed up the writes. Bernd I assume You mean IRIX on O2k. If so, Your best bet is to call Your friendly snowflake integrator (oh, soory, Origins do not use snowflake anymore...), but _not_ SGI. With one possible exception: SGI Montreal or Toronto (that's in Canada, so there will be no tax), they're not too far and are used to hopping the border for support/config calls. Out of interest: which IRIX? maintenance or feature? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are db files ??
At 23:46 2002-10-04, Chris Couture wrote: 1 - Where does mySQL normally store it's data base files? * From what I have seen, it depends on how you install it. You can check in the my.ini file and that will let you know where it is. On Linux (which I believe is the one in question) it normally goes in /var/lib/mysql/, but... 2 - Can you change the default directory for a data base? * Yes, you can change this in the my.ini file. Yes 3 - How do you get Linux to search the entire disk for a file? * Do you have any type of shell over linux? They usually offer some sort of search. You might want to look at installing webmin, it makes it easy to control a lot of things on your linux bot via a web based control center. type man find (without the quotes!) and it will tell You more than You wish to know about finding files on Your system. The quick and dirty on finding a file named kkk.ttt is: prompt# find / -name kkk.ttt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Failover possiblity
At 09:15 2002-06-05, Ramon Kagan wrote: Hi, (mysql 3.23.49, debian linux 2.4.18) I am looking at running a program called keepalived so I can monitor the availability of my mysql server. Data is stored on a NetApp filers so its readily available to multiple machines. What I'd like to do is use keepalived to failover to a secondary machine if the first machine dies. The question is, if the first machine dies and the second machine were to startup mysql server would the database be corrupt? Is this even possible? Try http://linux-ha.org/LinuxFailSafe/ . It works. If You need help with it, I'm in TO. Failsafe is/was SGI commercial HA product, they open-sourced the Linux version a while ago, the development continues with large involvement of SuSE, SGI and others. If You go to DL it, try off the CVS, it's much newer than version on SGI servers. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: High Availability questions
At 22:13 2002-03-19, Young Sul wrote: Hi, I've got a website that uses a mysql backend database. Due to the way in which the database and development has been architected, I'm unable to load-balance the database between multiple DB servers, taking advantage of replication inherent in mysql. (this is due largely to developmental constraints, and heavy use of write only session-management) I *need* to somehow mirror and create a decent failover environment for my database. Currently, I mirror the master DB on another server, and can switch over ...manually... if the master dies. I'm wondering if others on this list have encountered a similar situation -- how did you finally architect your DB environment? TIA, -y Check here: http://oss.sgi.com/projects/failsafe/ It doesn't specifically mention MySQL, but I just had a look at the other database failover scripts, and it shouldn't take You long to change them for MySQL. Once You do, You may want to put them up for others to use. Other than that it works like a charm. -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
At 01:57 2002-03-14, Heikki Tuuri wrote: Tomasz, are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no foreign keys. I tested this on mysql-max-4.0.1, and it worked. Well... [root@flow11 httpd]# mysqladmin -p version Enter password: mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 9 days 6 hours 12 min 44 sec Threads: 1 Questions: 2261 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 14 Queries per second avg: 0.003 Best regards, Heikki Tuuri Innobase Oy --- - sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
At 07:22 2002-03-14, Heikki Tuuri wrote: Tomasz, are you using a non-latin1 character set? No, didn't change the charset. As You can see from my reply to Victoria yesterday, will need to, eventually (for different reasons, though, not books), but right now I am in a proof-of-concept stage: only small tables, easy to change/recreate if I do something stupid. I do not care about charsets, optimization, various other settings in my.cnf. Hm... Maybe that's the problem? Did I miss something? The only thing I changed in my.cnf was adding a larger file for the InnoDB Thanks! sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: a quick sanity check ++
At 08:14 2002-03-14, Konstantin Tsolov wrote: right, of course ;-) (i neglected the join part) so, now i know exactly whom to ask ;-) i have the following bother (it's in the planning state): i have the idea of setting up a two-way replication system with 2 mysql servers for my radius database. i need them to act interchangeably: 1. if server1 is alive, all selects and updates (radius acct) should go to server1. server2 should act as a slave for server1. 2. if server1 dies, all selects and updates (radius acct) should go to server2. so far, so good. but here comes the tricky (for me) part - when server1 comes up again, it shold do 2 things: a) catch up with server2. b) become master again, leaving server2 as it's slave (same situation as in 1. above). how could this happen ? all ideas are appreciated (pointing to reading - also). Try here, since You are already thinking of using 2 machines: http://linux-ha.org/LinuxFailSafe/ Works rather well with IP, MAC and Oracle/SAP failover. I believe there is a driver for MySQL, if not You shouldn't t have much trouble writing one, examples are provided. It's all open source, same code _base_ as IRIX FailSafe (HA on SGI machines for the past 7? years). Since this is really off-topic, if You have any further questions, You probably should ask me off the list. I would only suggest using either DRBD or shared-SCSI for storage. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple charsets
At 13:26 2002-03-13, Victoria Reznichenko wrote: Alex, Wednesday, March 13, 2002, 7:16:02 PM, you wrote: AK I'm very much interested to know how do you manage multiple language support with AK mySQL. AK I haven't found how i can define different character sets for different tables or AK even for separate databases. AK Do i have to run several mySQL servers, one per each charset? You can use only one character set at a time for one instance of mysqld. If you want to use two character sets you need to restart or start another instance of mysqld. Is it something that someone is thinking about? Examples of it's use are abundant, to use a trivial one: my books. I have books in German, English, French, Polish, Russian and Czech. Obviously, they will require differing charsets to represent their titles and authors. As obviously, if I search for all the books on a specific subject, I want to find all I have, no matter which language they were published in. Single-charset restriction prevents me from doing that, I will have to perform as many searches as I have languages, wasting both time and resources: both at runtime and during development. Or am I completely out of my tree? -- sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
At 08:13 2002-03-13, Heikki Tuuri wrote: Tomasz, InnoDB in 3.23 and 4.0 is the same codebase. InnoDB versions are best counted from the 3.23 series, because they appear more frequently. I am sorry that this is confusing. MySQL/InnoDB-3.23.50 has not been released yet. It will probably be out at the end of March. OK, so I can just hang up my hat till then. From section 16 of http://www.innodb.com/ibman.html you find detailed information about every InnoDB version. For example, 4.0.1 == 3.23.47. Foreign keys should work in 4.0.1. Hmmm... That's what I read, too. And after several unsuccesful attempts to create my own tables, I did those contained on Your site, verbatim (as I put in my original message). Still, no effect. I guess the question then becomes: is 4.0.1 really able to keep track of constraints but unable to show them? In which case, how can one find out what they are (if extant)? Starting from version 3.23.50 InnoDB returns the foreign key definitions of a table when you call And which MySQL uses it? Can it be plugged into existing MySQL? SHOW CREATE TABLE yourtablename You can also list the foreign key constraints for a table T with SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' The foreign key constraints are listed in the table comment of the output. Best regards, Thanks a lot, hopefully You helped not just me Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Wednesday, March 13, 2002 1:05 AM Subject: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? Here is an excerpt from http://www.innodb.com/ibman.html#InnoDB_distros, section 4.2: Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints... Now, I assumed the version number above was suspiciously similar to MySQL one - and since I use 4.0.1, I thought I was OK (I need them references... ON DELETE and friends would be great, but plain references save most of the hassle). After trying to (and failing to) create my own tables, I did tables as in the example on InnoDB site: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=InnoDB; And what do I see? I see indices in the tables, but no FK! Yes, tables _are_ InnoDB. I have proper indices - so on to the next step (there was no error return), just in case - it says: Starting from version 3.23.50 InnoDB allows you to add a new foreign key constraint to a table... So, here I go: ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent(id); No error. No effect, either... So, here comes the big question: What am I doing wrong? -- sql, query Tomasz Korycki [EMAIL PROTECTED] -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
At 16:57 2002-03-13, Rick Flower wrote: Tomasz writes: From section 16 of http://www.innodb.com/ibman.html you find detailed information about every InnoDB version. For example, 4.0.1 == 3.23.47. Foreign keys should work in 4.0.1. Hmmm... That's what I read, too. And after several unsuccesful attempts to create my own tables, I did those contained on Your site, verbatim (as I put in my original message). Still, no effect. I guess the question then becomes: is 4.0.1 really able to keep track of constraints but unable to show them? In which case, how can one find out what they are (if extant)? Are you sure that you've got a MySQL-Max server, or at least one built with InnoDB support enabled? If you didn't, you might not get an InnoDB table even if you asked for one.. Unfortunately, the SQL parser is somewhat stupid and doesn't bother telling you that you did something dumb or that doesn't make sense in regards to how the server was built.. I've run into things like that numerous times.. SHOW TABLE STATUS sez it's InnoDB... As for listing out the foreign key constraints, that only works if you issue a show table status; for MySQL 3.23.4x, and you will get something like the following REFER statement : OK, which version, exactly, do You get following output from? Oh, never mind, I see it's .47. Which according to Heikki Tuuri of InnoDB is the same as mine... | ITEM | InnoDB | Dynamic| 0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 4901888 kB | | ITEM_DEF | InnoDB | Dynamic| 0 | 0 | 16384 |NULL |16384 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 4901888 kB; (ITEM_NAME) REFER dbname/ITEM(ITEM_NAME) Because mine stops after InnoDB free: whatever kB snip! Hopefully this might shed some light on your problem.. No, it didn't. It turns out we have (according to InnoDB) the same version of the DB, yet mine behaves differently than mine. Oh, well. Just so You needn't fish out beginning of this thread, mine is 4.0.1. I don't quite know what to think at this point... Below are the samples from above that you can feed directly into MySQL and see what it produces.. These work fine on our installation of 3.23.47 -- with InnoDB support enabled of course.. Thanks for the statements below. Unfortunately, after I try them (word for word, I want to eliminate possibility of my error), SHOW TABLE STATUS still ends right after InnoDB free: comment. create table if not exists ITEM ( ITEM_NAME varchar(64)not null, DESCRIPTIONvarchar(255), primary key (ITEM_NAME) ) TYPE=INNODB; create table if not exists ITEM_DEF ( ITEMDEF_ID intnot null, ITEM_NAME varchar(64), primary key (ITEMDEF_ID), INDEX FK_ITEM_NAME_INDEX(ITEM_NAME), FOREIGN KEY (ITEM_NAME) REFERENCES ITEM(ITEM_NAME) ) TYPE=INNODB; -- Rick Thanks! So, what's next? -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
Here is an excerpt from http://www.innodb.com/ibman.html#InnoDB_distros, section 4.2: Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints... Now, I assumed the version number above was suspiciously similar to MySQL one - and since I use 4.0.1, I thought I was OK (I need them references... ON DELETE and friends would be great, but plain references save most of the hassle). After trying to (and failing to) create my own tables, I did tables as in the example on InnoDB site: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=InnoDB; And what do I see? I see indices in the tables, but no FK! Yes, tables _are_ InnoDB. I have proper indices - so on to the next step (there was no error return), just in case - it says: Starting from version 3.23.50 InnoDB allows you to add a new foreign key constraint to a table... So, here I go: ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent(id); No error. No effect, either... So, here comes the big question: What am I doing wrong? -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trouble with the Listings (C'n Double)
At 00:02 2002-03-10, you wrote: Is this me or the mysql lists, I am getting a lot of double messages, are people sending double, or is it me and my outlook.. Just checking it's kind of annoying. Later Kacey A. Murphy netBuilder's, Inc. Methinks it's You, or the beer... ;) -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB issues - tables not found
Hi,
Newbie here, so please be kind...
I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE
TABLE) and I hit two problems. Maybe it's my clumsiness with search
specification, but I couldn't find answers in the archive. Oh, I tried it
on InnoDB tables created just for this test. So here goes:
First problem: SHOW CREATE does _not_ use the same syntax I did when
creating tables - it omits REFERENCES... completely. This is not a big
deal, I can just add those in the scripts if I need them, but it is
somewhat worrisome;
Second problem - and this is a big one - after I looked around in those
tables, inserting, selecting and updating data, I disconnected from the DB,
disconnected from the server and shut down the client. Until then I _could_
see the table structures, the fact they were InnoDB type, column
definitions, and so on. I did also see free space and so on. When I came
back after dinner and connected back... no such luck. Any attempt to get
any rows out (like SELECT * FROM `table_name`) gives me:
[FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno: 1)
I can't see structure, number of rows, free space, table type, size,
nothing. Logged out and in using different (sufficiently privileged) names
- no cigar. Looked through the hostname.err log - nothing there, as well.
Disconnected the client, shut down the MySQL - nothing seems wrong. It
started back without complaint as well - but I still can't see my tables...
I checked mysql database -everything is working as expected, things show up...
In a way I hope it is a FAQ, in which case I would appreciate a gentle
nudge. In case it's not, though, Is there something glaring I did/didn't do?
MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from
MySQL site. Here is how I created one of the tables in question (in a new
database):
CREATE TABLE `ALBUM` (
`Ix` int(11) NOT NULL auto_increment,
`Title` varchar(255) binary default NULL,
`Released` date default NULL,
`Billing` int(11) REFERENCES PERFORMER(Ix),
`Tracks` int(11) REFERENCES TRACK(Ix),
`Remarks` blob,
`Live` enum('Live','Studio','Both','Unknown') default 'Unknown',
`Compilation` enum('Yes','No') default No,
`RecordedFrom` date default NULL,
`RecordedTo` date default NULL,
`Company` varchar(255) binary default NULL,
`ID` tinytext,
PRIMARY KEY (`Ix`)
) TYPE=InnoDB;
The rest of them were similar.
Speak up, sages, I'm hanging on Your... well, fingers, in this case ;)
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote:
Tomasz,
are you running on Windows?
No, Linux 2.2.17
Please use innodb_table_monitor as explained in section 9.1 of
http://www.innodb.com/ibman.html, and also look into section 15.1.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
Speed up adding of features to MySQL/InnoDB through support contracts
See http://www.innodb.com for the online manual and latest news on InnoDB
-Original Message-
From: Tomasz Korycki [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Date: Tuesday, March 05, 2002 10:30 AM
Subject: InnoDB issues - tables not found
Hi,
Newbie here, so please be kind...
I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE
TABLE) and I hit two problems. Maybe it's my clumsiness with search
specification, but I couldn't find answers in the archive. Oh, I tried it
on InnoDB tables created just for this test. So here goes:
First problem: SHOW CREATE does _not_ use the same syntax I did when
creating tables - it omits REFERENCES... completely. This is not a big
deal, I can just add those in the scripts if I need them, but it is
somewhat worrisome;
Second problem - and this is a big one - after I looked around in those
tables, inserting, selecting and updating data, I disconnected from the DB,
disconnected from the server and shut down the client. Until then I _could_
see the table structures, the fact they were InnoDB type, column
definitions, and so on. I did also see free space and so on. When I came
back after dinner and connected back... no such luck. Any attempt to get
any rows out (like SELECT * FROM `table_name`) gives me:
[FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno:
1)
I can't see structure, number of rows, free space, table type, size,
nothing. Logged out and in using different (sufficiently privileged) names
- no cigar. Looked through the hostname.err log - nothing there, as well.
Disconnected the client, shut down the MySQL - nothing seems wrong. It
started back without complaint as well - but I still can't see my tables...
I checked mysql database -everything is working as expected, things show
up...
In a way I hope it is a FAQ, in which case I would appreciate a gentle
nudge. In case it's not, though, Is there something glaring I did/didn't
do?
MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from
MySQL site. Here is how I created one of the tables in question (in a new
database):
CREATE TABLE `ALBUM` (
`Ix` int(11) NOT NULL auto_increment,
`Title` varchar(255) binary default NULL,
`Released` date default NULL,
`Billing` int(11) REFERENCES PERFORMER(Ix),
`Tracks` int(11) REFERENCES TRACK(Ix),
`Remarks` blob,
`Live` enum('Live','Studio','Both','Unknown') default 'Unknown',
`Compilation` enum('Yes','No') default No,
`RecordedFrom` date default NULL,
`RecordedTo` date default NULL,
`Company` varchar(255) binary default NULL,
`ID` tinytext,
PRIMARY KEY (`Ix`)
) TYPE=InnoDB;
The rest of them were similar.
Speak up, sages, I'm hanging on Your... well, fingers, in this case ;)
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote: Tomasz, are you running on Windows? Please use innodb_table_monitor as explained in section 9.1 of http://www.innodb.com/ibman.html, and also look into section 15.1. Best regards, Re: 9.1: Well, can't find innodb* anywhere on the system Re: 15.1: The second problem seems to be mine, the one about orphaned' .frm files. That would seem to account for me seeing a tables list, but nothing about them. Obviously, the .frm files did exist. Couldn't drop the tables or the database (Unknown table ...,...,...). Let me repeat: everything was just fine until I quit mysql. When I logged back in - with the same command, I use ksh and 'set -o vi' - that's when the issues started. I got out of this trouble by shutting down the server, manually removing all InnoDB files (including .frm), then starting it again - which created the IDB files. I then used my script to create database and all the tables. And everything was fine until I logged out. I now logged back in and everything is still OK: I can desc tables, and so on. Just one issue: when I do SHOW CREATE ..., it still doesn't show any REFERENCES ... that I had when I created the tables. Either I'm exceptionally thick, or the docs really say that InnoDB has the capability to use the foreign keys... I took special care to reference only tables that already existed, in case it's a concern. Any idea? Also about how long will the tables stay healthy? I don't care about those 5 records I put in each, but to really test, I need to put a bit more... snip! THX! -- sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
