heading off in mysql?
Hi all, Just a quick question here. May I know how to get a result without heading in Mysql (like set heading off in Oracle) ? Thanks and regards, Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23 vs. 4.0
Hi! Currently, I'm running 3.23.51 on Red Hat 7.1, and I'm contemplating upgrading to MySQL 4.0, but I'm not sure what to expect. I don't know if MySQL 4.0 is fully backwards-compatible with 3.23.x versions, or if something is going to break if I upgrade. What are the main advantages of upgrading to 4.0? Speed? Features? I can go through the whole changelog if need be, but I'd prefer to hear what actual users are reporting as far as benefits and disadvantages. - Jonathan Did you look at manual ? http://www.mysql.com/doc/en/Upgrading-from-3.23.html David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
On 7 Aug 2003 at 9:47, woody at nfri dot com wrote: While I don't know for sure, my guess is that it would have something to do with 32 bit as the magic number, but also...being that this won't become a problem until mysql select from_unixtime(2147483647); +---+ | from_unixtime(2147483647) | +---+ | 2038-01-18 21:14:07 | It doesn't become a problem until you want to start *using* dates later than that. Depending on your application, that may happen considerably before 2038. For instance, if you need to store retirement dates, a Unix timestamp would be a bad choice even today. Of course you wouldn't need a time for a retirement date, and wouldn't be concerned about time zone or summer adjustments, so a DATE column would be fine, but people do use Unix timestamps for future times as well as past ones. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
i've noticed that, when a thread sending data an update is first locked then other select is locked From: xuefer tinys [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Fri, 08 Aug 2003 02:31:14 +0800 i've once noticed it it has been 8 but i remove it now, problem still exists From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'xuefer tinys' [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Thu, 7 Aug 2003 11:39:34 -0700 Check you mysqld server veriable thread_concurrency. I'm not sure that this will solve your problem since your problem has changed from a select locking to concurrent selects. ---Original Message- --From: xuefer tinys [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 07, 2003 11:18 AM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query -- --but one guy in #mysql said: --[quote] --with myisam tables if an update is running everything else is blocked, --multiple selects are allowed to run at the same time, selects and non --interfering inserts can run at the same time --[/quote] -- --so i ask in mailinglist --because i can't get my multiple selects run at the same time. -- --From: Dathan Vance Pattishall [EMAIL PROTECTED] --To: 'xuefer tinys' [EMAIL PROTECTED], [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query --Date: Thu, 7 Aug 2003 10:59:46 -0700 -- --For MYISAM tables selects cause table locks while INNODB does row level --locking. -- --Now if your select is taking a long time you might want to look at your --index schema. If that does not work for you then split up your data. -- -Original Message- From: xuefer tinys [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:24 AM To: [EMAIL PROTECTED] Subject: 1 query lock all others query when i show processlist 1 of my queries is sending data (SELECT ...) while others queries (SELECT/UPDATE) is locked what's up? _ MSN Messenger: --http://messenger.msn.com/cn -- 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] -- -- --_ -- MSN Hotmail http://www.hotmail.com _ MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. Otherwise, it sounds like the config information is not properly set -- either some user setup error that is alluding all of us, or a config loading error in MySQL. I'd like to check how the config files are located, etc., before doing a bug report. My two cents. PS: I'd also have both servers running the latest version, just in case it was a bug that was already fixed. --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by question
Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways! -Original Message- From: Cabanillas Dulanto, Ulises [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 2:52 PM To: mysql Subject: RE: order by question I execute the SELECT and it works!. I have MySQL 4.0.4 Regards, Ulises -Mensaje original- De: motorpsychkill [mailto:[EMAIL PROTECTED] Enviado el: Jueves 7 de Agosto de 2003 04:43 PM Para: mysql Asunto: RE: order by question -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:54 AM To: mysql Cc: motorpsychkill Subject: Re: order by question * motorpsychkill SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the IN clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! Use the FIELD() function: SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY FIELD(gallery_id, 3, 1, 2) HTH, -- Roger Thanks Roger, but that didn't seem to work. It still gave me the results ASC, i.e. by gallery_id 1, 2, 3. Anyone else? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by question
* motorpsychkill Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways! It should work also in version 3.23.54... actually, it should work since version 3.20.17: URL: http://www.mysql.com/doc/en/News-3.20.17.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transfer InnoDb in correct order ??
Hellu, I want to transfer the structure of my InnoDb database to another mysql server 3.23. However, I don't seem to get this to work correctly as the tables are not transfered in the correct order such that I get a bunch a foreign key constraints. Even if I use the MysqlPhp admin tool, Mysql Manager, SqlYog, it doesn't work: they make an export of the db structure but the db structure, that consists of create table statements, isn't in the correct order, such that when I use the export script to make the new database somewhere else, the tables can't be created because of foreign key constraints. How can solve this, without putting the create statements in the correct order manually ? Isn't there a handy tool that first checks if other tables should be created, and if so, it does this, before adding the table itself ?? Please some help, because ordering it manually isn't an option at this moment with so periodically transfers and so many tables :( Eddie _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Scalability on SMPs
On Thu, Aug 07, 2003 at 10:27:38PM -0700, Steven Roussey wrote: MyISAM performance is limited right now by a global lock in the key cache. However, I believe there is work going on to fix that in the 4.1 tree. Really? I thought it was going to be fixed in the 5.1 tree, which will be years away from production quality. 4.1 would be really cool, but it seems so soon (non-InnoDB)... I'm pretty sure the multi-keycache code is in 4.1, but I'd have to scan the checkins to be sure. It might have been 5.0 but I'm virtually certain I saw it... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 223,356,851 queries (394/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by question
* motorpsychkill SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the IN clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! Use the FIELD() function: SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY FIELD(gallery_id, 3, 1, 2) HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld cannot access innodb
Dear Sirs, I can not start MySQL server. More specifically, MySQL was running fine all the time since installation. It stopped working after I used FmPro Migrator to convert some File Maker databases to MySQL It looks like mysqld has no access permissions to InnoDB and I do not know how to fix it. LOG: The following message is written to MySQL log 030807 11:56:24 mysqld started 030807 11:56:24 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030807 11:56:24 mysqld ended Permissions: /library/mysql/var total 41056 drwx-- 13 maciej staff 442 Aug 7 11:50 . drwxrwxr-x 14 rootmysql 476 May 31 08:11 .. -rw-rw 1 mysql mysql 10178 Aug 7 11:56 Trono-Monica.local..err -rw-rw 1 mysql mysql 25088 Jun 30 11:56 ib_arch_log_00 -rw-rw 1 mysql mysql 5242880 Aug 7 09:31 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Jun 30 11:56 ib_logfile1 -rw-rw 1 mysql mysql 10485760 Aug 7 09:17 ibdata1 drwx-- 20 mysql mysql 680 Aug 7 09:31 mice -rw-r--r-- 1 mysql mysql 4821 Jun 30 11:45 my.conf drwx-- 20 mysql mysql 680 Jul 13 20:51 mysql drwx-- 2 mysql mysql68 Jul 13 22:28 pvi drwx-- 2 mysql mysql68 Jul 13 00:22 test drwx-- 5 mysql mysql 170 Jul 17 18:52 virus I am using MacOSX 10.2.6 Please help, Again, I am extremely grateful for your kind help. Maciej Maciej Wiznerowicz MD, PhD Dpt of Genetics and Microbiology Faculty of Medecine at University of Geneva Rue Michel Servet 1, CH-1211 Geneva Switzerland Tel. +41-22-3795717 Fax +41-22-3795702 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Native Arabic speakers wanted
Dear all, the MySQL Documentation Team is looking for native Arabic speakers on this list who are interested in reviewing an Arabic translation of the MySQL reference manual. Reviewers will receive our gratitude, and will of course be mentioned in the credits section of the manual. If you're interested, please send a mail to [EMAIL PROTECTED] providing some background on who you are, where you are located, and how much experience you have with both MySQL and translations. Regards, -- Are you MySQL certified?, http://www.mysql.com/certification/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Stefan Hinz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Berlin, Germany ___/ www.mysql.com +49 30 8270294-0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
* Girish Agarwal Hi All, I have added the date column ( column name is dolv ) in mysql as CHAR(10) Then it is not a 'date column', but s string column... ;) See the manual for a description of the different column types: URL: http://www.mysql.com/doc/en/Column_types.html URL: http://www.mysql.com/doc/en/Date_and_time_types.html URL: http://www.mysql.com/doc/en/DATETIME.html There are many special functions for use with date time type columns: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html as I needed the date in mmdd separated by mm/dd/. The formatting of the date column should be done when you query the table: SELECT *,DATE_FORMAT(dolv,%m/%d/%Y) AS dolv FROM table WHERE ... Now I want to have this column contain the date whenever the updation to that column is done. Then you could use the TIMESTAMP coulmn type, which does exactly this, if you meant ... whenever the updation to that ROW is done.. If you use a DATETIME column type, you could use the NOW() function: UPDATE table SET some_column = 'new value', other_column = 'other value', dolv = NOW(); The TIMESTAMP column does this automatically. Please advise as to how I can achieve this without losing the data I have already entered. To convert your existing rows to proper dates: # make a backup of the entire table CREATE TABLE backup_table SELECT * FROM table # create a new column: ALTER TABLE table ADD dolvdate TIMESTAMP; (Use DATETIME or DATE instead, if you don't wan't the TIMESTAMP magic.) Now we populate the new dolvdate column with a date created by substrings from the dolv column. We must split the mmddyyy or mm/dd/ strings into mm, dd, and , so that we can feed them to mysql as -mm-dd. # Fix dates in mm/dd/ format: UPDATE table SET dolvdate = CONCAT( MID(dolv,7,4),'-',MID(dolv,1,2),'-',MID(dolv,4,2)); # Fix remaining dates in mmdd format: UPDATE table SET dolvdate = CONCAT( MID(dolv,5,4),'-',MID(dolv,1,2),'-',mid(MID,3,2)) WHERE dolvdate = '0'; # check that everything is converted: SELECT COUNT(*) FROM table WHERE dolvdate = '0'; # if something is not converted, check what it is: SELECT * FROM table WHERE dolvdate = '0' LIMIT 10; # remove old column ALTER TABLE table DROP dolv; # rename new column ALTER TABLE table CHANGE dolvdate dolv TIMESTAMP; HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- 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]
mysqlbug
Hi, I am a Sun solaris 5.6 user. I do not have su or any other system permissions We have PERL5.005 on the box We do not have DBD::mysql So I wanted to install that module into my private library. Mysql server is on a remote unix box So when I try to install the DBD::mysql module its looking for client and development file of the mysql. Please tell which is the best way to get these files.Also let me know if I am missing something. I tried to download mysql source and run the configure -server option...but running into permissions problems. If I can download those files from anywhere,please let me where I cant get them. Thanks, Santosh
RE: mysql LOAD DATA INFILE
Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- 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]
Tracking a delete
How can I see if a record was deleted from a database? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking a delete
Ok, I know it WAS there because we have two similar tables that should contain sister records. One table has a record the other doesn't so it had to have been deleted. I need to find out WHEN it was deleted. How do I create a log of record deletes? If you keep the update log or the binlog you can look through it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrade
Did you restart the server? ---Original Message- --From: Robert Morgan [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 07, 2003 1:19 PM --To: mysqllist --Subject: upgrade -- --Hi I am using mysql 3.23.54 that came bundled with RH9. I have tried --updating to version 4.0 using the RPM available from the mysql site but --on logon the version still says 3.23.54 any ideas? thanks in advance. -- --Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware recommendation
I am going to have to put MySQL on it's own box, and I am wondering what makes the biggest difference with hardware. I am hoping to set max_connections to 1000 at least (and I will probably use at least 500 several times a day). There are a few tables that are almost constantly running inserts, updates, and selects (they are InnoDB). These tables have millions of records. There are many other tables that are used less frequently. I mostly need the inserts, selects, and updates to be FAST with a high concurrency. Will I see the biggest difference by: 1) maxing out the ram 2) faster processors 3) faster drives 4) more drives 5) more processors Also, I am curious if RAID or replication would be faster in my situation. Thanks, -Jackson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.14 runs CPU usage up and eventually stop
Hi, I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS from last week) and I am having a problem. We run a fairly large website using PHP so there is always many connections to the database. On 4.0.12 the cpu usage never shot to over 20% and ran extremely fast. After upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many situations will not accept any database connections. After browsing the website for a few minutes, I will type mysql on the command line and it will give an error of too many connections. Does anyone know what may be causing this high load from the upgrade? Again the previous versions did not do this and ran very well. I could use 4.0.12 but I wanted to report this in case it continues in future versions. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error when using avg( DISTINCT column)
I'm getting a syntaxt error when using select avg( DISTINCT column) from table. the code above is from the teach urself sql book. i have mandrake 9.1 linux. here's exactly what's happening: mysql select avg(distinct prod_price) from Products; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct prod_price) from Products' at line 1 mysql can some1 tell me what's the mistake here. much thanks, mauricio ps. i've tried: mysql select distinct avg(prod_price) from Products; but that yields the wrong result -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Thu, Aug 07, 2003 at 07:54:18AM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 07:00:33PM -0700, Steven Roussey wrote: So does anyone else have any ideas what is going on here? Shall I report this as a bug? Did you post how you setup the servers to load the different my.cnf files? Hopefully you don't have one at a default location. I don't use an /etc/my.cnf for this reason. The main one uses /var/lib/mysql/my.cnf and the slave one /data/mysql-backup/mysql/my.cnf. ..and so putting the my.conf into /data/mysql-backup instead wasn't very useful. Problem solved. Sorry for the annoyance, and thanks to all who tried to help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating table based upon matching field in second table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start MySQL on Mac OS X
Nicos, Actually, MySQL should be installed by an administrator, but it should be executed by root, which would permit the writing to the tmp dir. Meaning everytime you kick off MySQL server, you'll need to do so as root. This is usually how I dow it: cd /usr/local/mysql sudo echo sudo ./bin/mysqld_safe --user=mysql [Note: You'll need to do this on an account with administrator access. When you type sudo echo, you'll need to type your OS X user password.] My boy Marc has a slam'in site for MySQL on OS X http://www.entropy.ch. Since MySQL nor Apple have a startup script for MySQL (to kick off the DB at boot), I use the one he's generated - see his site for more details. Regards, Adam On Thursday, August 7, 2003, at 10:54 PM, Nicos Kekchidis wrote: Todd and Guys, Your advice helped me too. I think either Apple screwed up when set up /tmp directory to be writeable by root only or since MySQL package has bug or shall be installed ONLY as root user. - Nicos -- 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: mutual declarations produce Error 1064
Morten Gulbrandsen [EMAIL PROTECTED] wrote:
I think something is wrong with my administration,
Basically I get these messages,
ERROR 1005 at line 9: Can't create table '.\company\department.frm'
(errno: 150)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
ERROR 1133: Can't find any matching row in the user table
C:\mysql\examples\elmasrimysql -u root -p***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql use mysql;
Database changed
mysql update user set password = password('navathe') where user =
'elmasri';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql quit
Bye
C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql quit
Bye
C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company
company_01.sql
ERROR 1005 at line 9: Can't create table '.\company\department.frm'
(errno: 150)
C:\mysql\examples\elmasri
C:\mysql\examples\elmasritype company_01.sql
# mysql -u root -h localhost -p -vvv company_01.sql
USE company;
# SHOW INNODB STATUS;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATEDATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN), # between employee and department
FOREIGN KEY (MGRSSN)# a sort of mutually declaration
REFERENCES EMPLOYEE(SSN) # this is declared in employee
)TYPE = INNODB;
You create table DEPARTMENT with foreign key, which is referenced to the non-existent
table EMPLOYEE. So, you need to create both table without foreign keys and then add
foreign key constraints or execute SET FOREIGN_KEY_CHECKS=0 before table creation.
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSNCHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
INDEX (SUPERSSN, SSN),
FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN),
INDEX (DNO, DNUMBER),
FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
)TYPE = INNODB;
C:\mysql\examples\elmasrimysql -u root -p***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql grant all on company.* to 'elmasri'@'localhost' identified by
'navathe';
Query OK, 0 rows affected (0.00 sec)
mysql quit
Bye
C:\mysql\examples\elmasrimysql -u elmasri -pnavathe company;
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
C:\mysql\examples\elmasrimysql -u root -p***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql set password for 'elmasri'@'localhost' = password('navathe');
Query OK, 0 rows affected (0.00 sec)
mysql set password for 'elmasri'@'%' = password('navathe');
ERROR 1133: Can't find any matching row in the user table
mysql
It means the there is no any entry 'elmasri'@'%' in the table user.
I try again with
Manually
mysql create table employee( fname varchar(15) )type=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS EMPLOYEE;
Query OK, 0 rows affected (0.00 sec)
And
mysql CREATE TABLE EMPLOYEE
- (
- FNAMEVARCHAR(15) NOT NULL,
- MINITCHAR,
- LNAMEVARCHAR(15) NOT NULL,
- SSN CHAR(9) NOT NULL,
- BDATEDATE,
- ADDRESS VARCHAR(30),
- SEX CHAR,
- SALARY DECIMAL(10,2),
- SUPERSSN CHAR(9),
- DNO INT NOT NULL,
-
- PRIMARY KEY (SSN),
- INDEX (SUPERSSN, SSN),
-
- FOREIGN KEY (SUPERSSN)
- REFERENCES EMPLOYEE(SSN),
-
- INDEX (DNO, DNUMBER),
- FOREIGN KEY (DNO)
- REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
- )TYPE = INNODB;
ERROR 1072: Key column 'DNUMBER' doesn't exist in table
I don't see column DNUMBER in the above create table definition.
mysql create table
replication w/o stopping the master
Hi guys gals, Tried to get an answer through the manual, but couldn't find one. So, is there a way to do replication w/o stopping the master. The only way I found up until now was to stop the master (in order to insure there are no changes to the database), copy the database directory, start the master again with logging enabled (and delete the old logging files, if any), and copy the database directory on the slave. Of course, stopping the master isn't that much fun, esspecially when the databases are large and copying them takes some time. Is there a way to avoid it? I'm using 4.0.13. Thanks, bogdan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
