InnoDB Conversion
Hi. I have a query that has been running for 10.7 hours. It is converting a 3.6GB MyISAM to Innodb. 38448 | copy to tmp table | alter table `MESSAGES` type=innodb The innodb data file has increased 8.6GB in size since the command started. How do I tell how far through the conversion the process is? Can I stop the process (without incurring a huge rollback penalty) and resume it in a more efficient manner (e.g. with different my.cnf startup options)? Regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Eval Information
Hi All, I am evaluating mysql. Can you please tell the following info related to Oracle MysqlConversion.. * Decision making steps to decide if an oracle database can be moved to MySql or not * List of things to be cautious about before developing new applications on MySql database Thanks for your time. Best Regards, piyush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: understanding foreign keys
rb [EMAIL PROTECTED] wrote: I am a new user trying to learn mysql (using InnoDB tables, mysql version 3.23.54) and to create a useful database for a project I am working on. In trying to understand how to use foreign keys effectively, I studied examples at http://sqlzoo.net - A Gentle Introduction to SQL I am trying to understand why some columns which refer to other tables are defined as foreign keys and why others are not. Basically, what is the difference between a column which refers to another table but is not explicitly a foreign key and one which is? I would be grateful if anyone could help me to understand this distinction. From one of the sample databases, here is a create table statement which includes both types: CREATE TABLE order_line ( order_ref INTEGER NOT NULL REFERENCES dress_order ,line_no INTEGER NOT NULL ,ol_style INTEGER REFERENCES garment ,ol_size INTEGER NOT NULL ,ol_material INTEGER REFERENCES material ,PRIMARY KEY (order_ref, line_no) ,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities ); I already discovered that in order for me to make this work, I had to insert a line creating indexes: INDEX (ol_style, ol_size), before the foreign key definition and that I had to explicitly identify the columns in the referenced table for the foreign keys to be created: FOREIGN KEY (ol_style, ol_size) REFERENCES quantities (style_q, size_q) Thanks very much for any help. sorry if I'm asking in the wrong place... In the first case (order_ref INTEGER NOT NULL REFERENCES dress_order) REFERENCES without FOREIGN KEY is parsed, but MySQL does nothing. In the second case REFERENCES is a part of FOREIGN KEY definition. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Eval Information
Hi, First, please do not prioritize any emails to a mailinglist. It might be high priority to you, it isn't for the mailinglist readers who are supposed to respond. It's not good mailinglist-etiquette. I am evaluating mysql. Can you please tell the following info related to Oracle MysqlConversion.. * Decision making steps to decide if an oracle database can be moved to MySql or not Of course this highly depends on your application needs. MySQL does have transactions and foreign keys with the InnoDB table type, but doesn't have CHECK constraints. It also doesn't have triggers, or a stable (MySQL 5 is in early Alpha) Stored Procedure implementation. So if you're using those, you're out of luck or you need to convert them to application code or middle tier code. As far as I know, it also doesn't have automatic build in scheduling, so if you're using Oracle jobs, you need to work around that too. Are there any specific things you want to know about? * List of things to be cautious about before developing new applications on MySql database Read the docs on column types, automatic type conversions and this one as well: http://sql-info.de/mysql/gotchas.html With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding max values
Keith [EMAIL PROTECTED] wrote: i'm trying to find the max value of a certain field and return a field linked with the max value. The tables: planets-- population government sysID systems-- sysID For examples sake lets say we are searching through three sysID's. For each sysID I want to look in planets and calculate the largest population value where the sysID's are the same then return the government value for the planet with the largest population for each sysID. If you use 4.1.x version of MySQL you can use subqueries to get needed result. If you use older version you can use temporary table and JOIN or MAX-CONCAT trick: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL takes 8Sek, MySQLcc takes 0.1Sek to run the same query. WHY???
MySQL puts an automatic limit of 1000 on all select queries. Try adding LIMIT 10, and perhaps then it will take a good long time like you want. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dual-installation: MySQl 3 + 4 ?
Hello! Is there a possibility to install MySQL 3.X and MySQL 4.X parellel on one FreeBSD-Machine? I would like to work with the new version, but a lot of utilities published as free php-software don't run with MySQL withoutn massive changings. With best regards Mark Schanovsky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can MySql run over windows os?
Dear sir: I've used MySql for several years. It IS the best database I've used, small and efficient. However, I now meet a problem. Can u tell me whether MySql run over windows os? ---Just tell me no. ok? Thx! Urs, thyme _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can MySql run over windows os?
hi Íõ ÊË, Yes you can have MySQL for Windows OS ... visit: http://www.mysql.com/downloads/mysql-4.0.html --- Windows downloads - OK --- Íõ ÊË [EMAIL PROTECTED] wrote: Dear sir: I've used MySql for several years. It IS the best database I've used, small and efficient. However, I now meet a problem. Can u tell me whether MySql run over windows os? ---Just tell me no. ok? Thx! Urs, thyme _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£ http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can MySql run over windows os?
Hi, I'm not sure if I understand your question, but if your asking: Does MySQL run on Windows, then: Yes, it does. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Dear sir: I've used MySql for several years. It IS the best database I've used, small and efficient. However, I now meet a problem. Can u tell me whether MySql run over windows os? ---Just tell me no. ok? Thx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can MySql run over windows os?
Hi, Have a look here : http://www.mysql.com/doc/en/Windows_installation.html Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can MySql run over windows os?
(B (B (B (B (B (B $B2&(B $B;E(B [EMAIL PROTECTED] wrote on 15/03/2004 10:30:12: (B (B Dear sir: (BI've used MySql for several years. It IS the best database I've used, (B small and efficient. (BHowever, I now meet a problem. Can u tell me whether MySql run over (B windows os? ---Just tell me no. ok? (B (BMySQL runs very well on Windows. It is probably better run on Windows 2000 (Bor XP, where it can be installed as a Service, than on Winmdows 98, where (Bit has to be started manually. Download it and try it - it has a (BWindows-style installer. (B (B Alec Cawley (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dual-installation: MySQl 3 + 4 ?
[EMAIL PROTECTED] wrote: Hello! Is there a possibility to install MySQL 3.X and MySQL 4.X parellel on one FreeBSD-Machine? Sure. I would like to work with the new version, but a lot of utilities published as free php-software don't run with MySQL withoutn massive changings. Some options should be different for each server, like port number, socket file, pid file etc. You can find info in the manual: http://www.mysql.com/doc/en/Multiple_servers.html http://www.mysql.com/doc/en/mysqld_multi.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log production option enabling
Hello colleagues: I would like to know if it's possible to start the log file production at run time, i.e. without stopping and restarting the database ... I would like to know also of it's possible to change at run time the directory where the log files are stored after their generation. I'm working with 3.23 release. Thanks a lot Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting Identity after INSERT
Hi All Whats the preferred syntax to insert values in multiple (related) tables I insert one record, then i need the ID of the inserted value to add it the with the other inserts with MSSQL stored procs its INSERT INTO . SELECT @MyID = @@IDENTITY after that you can use @MyID So whats the preferred method with MySQL, is there a easy way to get the Identity back from a just inserted record, or do i just have to do a select based on a value i did just insert ? Regards Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied from a single machine...
Server version: MySQL 4.0.18, running on Red Hat Linux 8.0 Symptoms: All our machines except for one can connect to the database. That one machine gets 'access denied'. All users are affected, even root. SHOW GRANTS for an example user: Grants for [EMAIL PROTECTED]: GRANT ALL PRIVILEGES ON *.* TO 'ensadmin'@'%' IDENTIFIED BY PASSWORD 'x' And yet any attempt to connect to that database from one particular machine results in failure. For example, here are the logs for two successive connections, one from machine bc-1-1-03 and one from bc-1-1-02: Time Id CommandArgument 040315 10:44:00 1 Connect [EMAIL PROTECTED] on 1 Query select USER() 040315 10:44:09 1 Quit 040315 10:44:21 2 Connect Access denied for user: '[EMAIL PROTECTED]' (Us ing password: YES) I've tried re-starting the database, to no effect (fortunately this is just a test instance). Am I missing something ridiculously simple, or is this a bug? Thanks in advance... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Identity after INSERT
Alex Curvers [EMAIL PROTECTED] wrote: Whats the preferred syntax to insert values in multiple (related) tables I insert one record, then i need the ID of the inserted value to add it the with the other inserts with MSSQL stored procs its INSERT INTO . SELECT @MyID = @@IDENTITY after that you can use @MyID So whats the preferred method with MySQL, is there a easy way to get the Identity back from a just inserted record, or do i just have to do a select based on a value i did just insert ? Use AUTO_INCREMENT column and LAST_INSERT_ID() function to retrieve most recent auto_increment value: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log production option enabling
[EMAIL PROTECTED] wrote: Hello colleagues: I would like to know if it's possible to start the log file production at run time, i.e. without stopping and restarting the database ... No, you should restart MySQL server. I would like to know also of it's possible to change at run time the directory where the log files are stored after their generation. By default log files are stored in the MySQL data directory. I'm working with 3.23 release. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Got an error reading communication packets - ???
Hi MySQLians, After such a long span (nearly 1 month) of searching the solution for the server restart while executing C API prepared statments I found that the execution met the error in the server ; 040315 17:48:55 Aborted connection 2 to db: 'test' user: '' host: `localhost' (Got an error reading communication packets). Hope this is well known error to experianced MySQLians... Suggest me possible remedies for this error I am using MySQL 5.0.0-alpha for windows Windows 2000 Service Pack 4 MS VC++ 6.0 Thanking you Arun. :) Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Please help with createing database
Hey, Thanks for replying. I login with username root and a password I set in the ensim control panel, it works perfectly. I dont really know if I am in the mysql database...but there are a couple of tables or dbs there... What id are you using to create the table? I guess root, coz thats what I login withnot really sure what id is. I do think its a permissions issue...but I dont want to touch any settings till I hear from someone who knows about this as I know squat. - Let us know what type of OS do you have this on. First guesses are persmissionsHave you set the permissions properly on the data directory for mysql? And what user is running mysql currently? When you started it, if *NIX, you should have supplied a user, i.e. bin/mysqld_safe --user=mysql This would mean that the mysql user would have to exist and have all rights to the data directory. hth Jeff - Cheers, -Ryan * * * * * * * * * * * * * * * * I'm not an expert (by any means) but are you in the mysql database when you're trying to create the new db? If so that might be the reason. What id are you using to create the table? Does this ID have the appropriate permissions to create the db? James . Hi, After 4 hours I am finally seeing a bit of results...am a newbie in MySql and have just been able to setup PHPMyAdmin, after loggin in I see the database MySql and 6 tables: columns_priv db func host tables_priv user I then tried to create a database and it gives me this error Error SQL-query : CREATE DATABASE `test1` MySQL said: Can't create database 'test1'. (errno: 13) Back can ANYBODY please tell me what to do? am going totally nuts trying to figure this out. Thanks in advance, -Ryan -- 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]
any select statement like uniq in unix
Hi! Is there a select statement , wich works like the command uniq in unix, if I shall list a table with a column wich for example is telephonenumber and the same number occurs like 1000 times, and I wan,t i present just once and also the count how many times it occurs in the table. /Jan Blomqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: any select statement like uniq in unix
you can do something like: select phone_number, count(1) from your_table_name_here group by phone_number -Original Message- From: Jan Blomqvist [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 8:27 AM To: [EMAIL PROTECTED] Subject: any select statement like uniq in unix Hi! Is there a select statement , wich works like the command uniq in unix, if I shall list a table with a column wich for example is telephonenumber and the same number occurs like 1000 times, and I wan,t i present just once and also the count how many times it occurs in the table. /Jan Blomqvist -- 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: understanding foreign keys
I think, the two formats you raised are both that of foreign key constraint: when there is a composite foreign key (more than 1 attribute in a table involved), you must use foreign key clause. if there is only a simple foreign key, you can use either format. --- Egor Egorov [EMAIL PROTECTED] wrote: rb [EMAIL PROTECTED] wrote: I am a new user trying to learn mysql (using InnoDB tables, mysql version 3.23.54) and to create a useful database for a project I am working on. In trying to understand how to use foreign keys effectively, I studied examples at http://sqlzoo.net - A Gentle Introduction to SQL I am trying to understand why some columns which refer to other tables are defined as foreign keys and why others are not. Basically, what is the difference between a column which refers to another table but is not explicitly a foreign key and one which is? I would be grateful if anyone could help me to understand this distinction. From one of the sample databases, here is a create table statement which includes both types: CREATE TABLE order_line ( order_ref INTEGER NOT NULL REFERENCES dress_order ,line_no INTEGER NOT NULL ,ol_style INTEGER REFERENCES garment ,ol_size INTEGER NOT NULL ,ol_material INTEGER REFERENCES material ,PRIMARY KEY (order_ref, line_no) ,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities ); I already discovered that in order for me to make this work, I had to insert a line creating indexes: INDEX (ol_style, ol_size), before the foreign key definition and that I had to explicitly identify the columns in the referenced table for the foreign keys to be created: FOREIGN KEY (ol_style, ol_size) REFERENCES quantities (style_q, size_q) Thanks very much for any help. sorry if I'm asking in the wrong place... In the first case (order_ref INTEGER NOT NULL REFERENCES dress_order) REFERENCES without FOREIGN KEY is parsed, but MySQL does nothing. In the second case REFERENCES is a part of FOREIGN KEY definition. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any select statement like uniq in unix
Jan Blomqvist [EMAIL PROTECTED] wrote: Hi! Is there a select statement , wich works like the command uniq in unix, if I shall list a table with a column wich for example is telephonenumber and the same number occurs like 1000 times, and I wan,t i present just once and also the count how many times it occurs in the table. It's possible with COUNT() function and GROUP BY clause: SELECT telephonenumber, COUNT(*) FROM table GROUP BY telephonenumber; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any select statement like uniq in unix
Given a table named MyTab that contains a column named TelephoneNumber that has 8 instances of 123-4567 in it and 3 instances of 444- in it: select distinct(TelephoneNumber) from MyTab will give you each of the phone numbers, once each, i.e. the result will be: 123-4567 444- This query: select count(distinct TelephoneNumber) from MyTab will tell you how many different phone numbers there are, i.e. the result set will be: 2 I can't think of a way to give you both quantities in the same query though. Rhino - Original Message - From: Jan Blomqvist [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 8:27 AM Subject: any select statement like uniq in unix Hi! Is there a select statement , wich works like the command uniq in unix, if I shall list a table with a column wich for example is telephonenumber and the same number occurs like 1000 times, and I wan,t i present just once and also the count how many times it occurs in the table. /Jan Blomqvist -- 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]
restoring a database from tape
Hi, Recently we EOLed a machine that had a mysql instance. The /usr/local/mysql dir was backed up to tape before EOLing. Having now installed a new machine, we need to copy the old database from tape to the new machine. Mysql has been successfully installed on the new machine. To install the database, can I just copy the ../mysql/data directory from the tape to the corresponding dir on the new machine? thanks for your time. -M _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restoring a database from tape
If the table types are all MyISAM and you took a consistent backup the answer is most likely yes. Original Message On 3/15/04, 7:50:39 AM, mt m [EMAIL PROTECTED] wrote regarding restoring a database from tape: Hi, Recently we EOLed a machine that had a mysql instance. The /usr/local/mysql dir was backed up to tape before EOLing. Having now installed a new machine, we need to copy the old database from tape to the new machine. Mysql has been successfully installed on the new machine. To install the database, can I just copy the ../mysql/data directory from the tape to the corresponding dir on the new machine? thanks for your time. -M _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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]
What exactly is happening with this table create?
Using a tool to generate a data model I go the following statement: Create table StateN ( StateId Int NOT NULL AUTO_INCREMENT, StateNm Char(50) NOT NULL DEFAULT '', StateCd Char(7) NOT NULL DEFAULT '', SortInt Int NOT NULL DEFAULT 0, UNIQUE (StateId), UNIQUE (StateCd), Primary Key (StateId), UNIQUE Index ak_State (StateCd) ) TYPE = InnoDB ROW_FORMAT = Default; The last four statements seem to be redundant in that the UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement and is StateCd to its index. My question is, what is happening in terms of the objects I am creating? That is: Am I creating for indexes or two? Are they the pk and ak that I want (last two statements) or is the server only creating the first two? Any insight here is greatly appreciated! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 6:24 AM To: [EMAIL PROTECTED] Subject: Re: Getting Identity after INSERT Alex Curvers [EMAIL PROTECTED] wrote: Whats the preferred syntax to insert values in multiple (related) tables I insert one record, then i need the ID of the inserted value to add it the with the other inserts with MSSQL stored procs its INSERT INTO . SELECT @MyID = @@IDENTITY after that you can use @MyID So whats the preferred method with MySQL, is there a easy way to get the Identity back from a just inserted record, or do i just have to do a select based on a value i did just insert ? Use AUTO_INCREMENT column and LAST_INSERT_ID() function to retrieve most recent auto_increment value: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What exactly is happening with this table create?
You did not state the tool you are using so I am not sure if the syntax is meant to be a constraint or an index. Since an Index was stated once for each column I am assuming the first UNIQUE is a constraint while the actual index is preceded by the INDEX keyword. Original Message On 3/15/04, 8:03:59 AM, Boyd E. Hemphill [EMAIL PROTECTED] wrote regarding What exactly is happening with this table create?: Using a tool to generate a data model I go the following statement: Create table StateN ( StateId Int NOT NULL AUTO_INCREMENT, StateNm Char(50) NOT NULL DEFAULT '', StateCd Char(7) NOT NULL DEFAULT '', SortInt Int NOT NULL DEFAULT 0, UNIQUE (StateId), UNIQUE (StateCd), Primary Key (StateId), UNIQUE Index ak_State (StateCd) ) TYPE = InnoDB ROW_FORMAT = Default; The last four statements seem to be redundant in that the UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement and is StateCd to its index. My question is, what is happening in terms of the objects I am creating? That is: Am I creating for indexes or two? Are they the pk and ak that I want (last two statements) or is the server only creating the first two? Any insight here is greatly appreciated! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 6:24 AM To: [EMAIL PROTECTED] Subject: Re: Getting Identity after INSERT Alex Curvers [EMAIL PROTECTED] wrote: Whats the preferred syntax to insert values in multiple (related) tables I insert one record, then i need the ID of the inserted value to add it the with the other inserts with MSSQL stored procs its INSERT INTO . SELECT @MyID = @@IDENTITY after that you can use @MyID So whats the preferred method with MySQL, is there a easy way to get the Identity back from a just inserted record, or do i just have to do a select based on a value i did just insert ? Use AUTO_INCREMENT column and LAST_INSERT_ID() function to retrieve most recent auto_increment value: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: What exactly is happening with this table create?
On Mon, 15 Mar 2004, Boyd E. Hemphill wrote: Using a tool to generate a data model I go the following statement: Create table StateN ( StateId Int NOT NULL AUTO_INCREMENT, StateNm Char(50) NOT NULL DEFAULT '', StateCd Char(7) NOT NULL DEFAULT '', SortInt Int NOT NULL DEFAULT 0, UNIQUE (StateId), UNIQUE (StateCd), Primary Key (StateId), UNIQUE Index ak_State (StateCd) ) TYPE = InnoDB ROW_FORMAT = Default; The last four statements seem to be redundant in that the UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement and is StateCd to its index. All four statements aren't redundant, only two of them are. My question is, what is happening in terms of the objects I am creating? That is: Am I creating for indexes or two? Are they the pk and ak that I want (last two statements) or is the server only creating the first two? All four indexes are created in this case. You have one primary key on the StateId column, one unique index on the StateId column called StateId (-- redundant) one unique index on the StateCd column called StateCd and one unique index on the StateCd column called ak_State. The primary key should be removed if you want StateId to contain NULL values, but most likely you will want to remove the unique index on this column (since a primary key in itself is unique). Then you will remove one of the two indexes on the StateCd column. for example: DROP INDEX StateId ON StateN; DROP INDEX StateCd ON StateN; That would leave you with the Primary Key on StateId and a unique index called ak_State on the StateCd column. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log production option enabling
On 15 Mar 2004, at 12:34, Victoria Reznichenko wrote: [EMAIL PROTECTED] wrote: Hello colleagues: I would like to know if it's possible to start the log file production at run time, i.e. without stopping and restarting the database ... No, you should restart MySQL server. Which is really irritating if you're debugging a problem with a busy production server. You don't want the general query log on all the time, but you don't want to have to have database downtime (even if it's only momentary) to switch the logging on and then off again. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log production option enabling
Tim Cutts [EMAIL PROTECTED] wrote: On 15 Mar 2004, at 12:34, Victoria Reznichenko wrote: [EMAIL PROTECTED] wrote: Hello colleagues: I would like to know if it's possible to start the log file production at run time, i.e. without stopping and restarting the database ... No, you should restart MySQL server. Which is really irritating if you're debugging a problem with a busy production server. You don't want the general query log on all the time, but you don't want to have to have database downtime (even if it's only momentary) to switch the logging on and then off again. You can enable/disable logging for session with SET SQL_LOG_OFF statement if user has SUPER privilege. It's supported from version 4.0: http://www.mysql.com/doc/en/SET_OPTION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Processlist : state Writing to net
Hi listers, Using MySQL 4.0.15-max-debug on Windows 2000, I am working with perlscripts. When one of my program runs, I have an unexpected long time for one query which take at least 10 min (in the best case, but it stayed blocked most of the time) instead of 10 sec when the query is on the mysql command line (a SELECT query about 81000 lines). The command SHOW PROCESSLIST shows that the state of that command is Writing to net ! I don't see what that means and how I can find a solution ? Anybody can help me ? best regards, Rekin's - Yahoo! Mail : votre e-mail personnel et gratuit qui vous suit partout ! Créez votre Yahoo! Mail Dialoguez en direct avec vos amis grâce à Yahoo! Messenger !
Re: Aliases and bookmarks
Scott Haneda [EMAIL PROTECTED] wrote: There are some things I do in mysql often, is there some way to alias or otherwise bookmark a a sql statement for simple rapid use in the future? You can put SQL statements to the file and then execute them from the file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied from a single machine...
Tim Cutts [EMAIL PROTECTED] wrote: Server version: MySQL 4.0.18, running on Red Hat Linux 8.0 Symptoms: All our machines except for one can connect to the database. That one machine gets 'access denied'. All users are affected, even root. SHOW GRANTS for an example user: Grants for [EMAIL PROTECTED]: GRANT ALL PRIVILEGES ON *.* TO 'ensadmin'@'%' IDENTIFIED BY PASSWORD 'x' And yet any attempt to connect to that database from one particular machine results in failure. For example, here are the logs for two successive connections, one from machine bc-1-1-03 and one from bc-1-1-02: Time Id CommandArgument 040315 10:44:00 1 Connect [EMAIL PROTECTED] on 1 Query select USER() 040315 10:44:09 1 Quit 040315 10:44:21 2 Connect Access denied for user: '[EMAIL PROTECTED]' (Us ing password: YES) I've tried re-starting the database, to no effect (fortunately this is just a test instance). Am I missing something ridiculously simple, or is this a bug? Probably you specified wrong password in the option file on 'bc-1-1-02' box. Check the output of mysql --print-defaults. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Training in the North West of UK
** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error you must delete it and notify the system manager (e-mail: [EMAIL PROTECTED]). This banner also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. http://www.oldham.gov.uk ** Hi Folks, Sorry of this is off-topic, but I am running out of places to look! I am trying to find training for MySQL in the UK, located in the north-west. The course must cover MySQL database and system administration. The person who would be going on the course already has a vast amount of Oracle and MS-SQL knowledge however they currently know nothing about MySQL. I have done a Google for some courses, but I can only find courses in London or the south. Does anyone know anyone/anywhere that can help me? Thanks, Andrew McCall
Re: Access denied from a single machine...
On 15 Mar 2004, at 15:35, Victoria Reznichenko wrote: Probably you specified wrong password in the option file on 'bc-1-1-02' box. Check the output of mysql --print-defaults. Nice idea, but no - I typed the password in manually both times, and I have repeated the experiment several times (and so has another user). The software in use on both client machines is identical, and the user accounts are on shared home directories, and so would have been reading the same ~/.my.cnf Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auditing
/* repost: considering I have no answers, is it safe to conclude it's not feasible?*/ I'd like to be able to audit connections to mysql databases. Here is what I'd like to see: Who connected to the server. What IP did they come from. When they disconnected. Any permission denied errors they may have had. The only thing that comes close to that is the general log, which is quite a bit of overhead. Has anyone come up with a solution for this? Does anyone see this in future versions? I've attempted the general query log, but it's not really in the best format for generating reports. Additionally, it's quite bloated when you do 300q/s. I've tried pushing it to a fifo, so I can have a daemon get the information, but mysql doesn't support that either. (my.cnf:log=/var/log/mysql.fifo) or (my.cnf:log=|/var/log/mysql.fifo, like syslog.conf) Has anyone else found solutions for this? Thanks! Dan Vande More -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql error grant tables, help
Hola, I made 4 changes and it worked. changed 3306 and mysql.sock for both client and server, now it works fine. Those two options killed so much of my time. I followed the instructions line by line and there was never any mention of changing the default my.cnf file. Gracias, Chris -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: Saturday, March 13, 2004 10:37 AM To: Chris Sanchez; [EMAIL PROTECTED] Subject: Re: mysql error grant tables, help Hi! try to look at your my.cnf you haven't config it correctly! At 05:54 p.m. 12/03/2004, Chris Sanchez wrote: get this when i run scripts/mysql_install_db. also tried mysqld --skip-grant and got the same thing. how do i fix? mysql-standard-4.0.18-sun-solaris2.8-sparc binary on SunOS ipdev1 5.8 Generic_108528-06 sun4u sparc SUNW,Ultra-5_10. 8 Saludos, _ ____ __ ___ ___ _ | | ___ _ _ ___ | | _._ _ _ _ __ | \ \ _ _ / __| . || | _| |_ || | |_ || |_ | || ' || | |\ \/| || | |\__ \| | || |_ \__/___||__/ ___||___||_||_|_|`___|/\_\|_|_|_|`_. |___/`___\|___| ___' Victor Medina M Java - Linux - MySQL [EMAIL PROTECTED] geek by nature, linux by choice
Re: Can't create thread
We wrote a wrapper with LD_PRELOAD. When mysqld does pthread_attr_setstacksize() we translate it into an anonymous mmap() and use pthread_attr_setstackaddr() instead. It's the equivalent of FLOATING_STACKS. ;) Our mysqld (which is running as slave) will seg fault if we set the thread_stack to 256k. Running it with thread_stack set to 2MB works flawlessly (that is until we allocate 256 threads). mysql 3.23.58 doesn't like 2MB thread stacks in our environment. Is this normal? I suspect you have a bug in your wrapper somewhere. Note that you delegate the stack creation at 2MB or higher to the regular pthread_attr_setstacksize() Michael: I noticed a couple more problems with your wrapper upon further examination. pthread_attr_setstacksize() is called only once from main() and it initializes the variable in the global thread descriptor structure. When you allocate memory, and then point the user stack at it with pthread_attr_setstackaddr(), what you end up with is all threads sharing the same stack, which would lead to a quick coredump. In order to use user stacks and do your own stack memory management you would need to either make changes in mysqld ( look at create_new_thread() in sql/mysqld.cc, and remember that you need to clean up on thread exit, see man pthread_cleanup_push), or overwrite pthread_create() as well as pthread_exit(). It would seem to me that recompling libpthread.so with FLOATING_STACKS and putting it in LD_PRELOAD would be a simpler solution. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Switch replication master
Hi, I use replication with 1 master and 1 slave. Right now the slave is only used to make a backup. Next week I am going to change my master motherboard. So I need to take it offline for a while (about 90 minutes). I am thinking about switching the master for that 90 minutes, so the slave will become master when master is offline, and after master is back online switch back slave to slave. Here is how I am going to do it: 1. Change all my code to make the DB query to slave. 2. Take master offline, change motherboard. 3. Master back online. How do I switch back the master now? Any idea? I don't mind to take offline both slave and master for a while if it is not too long. Please help. Thank you, --Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is Read Only
On Monday 15 March 2004 01:02, Chuck Gadd wrote: Smartyone wrote: The database used to work, but then something must have broke, because now it is suddenly Read-Only (reports Read Only when I try to add a record using MySQLCC). Make sure the owner and group of the database files is set to mysql. Chuck, Thanks for the comment! But I do not have a group mysql. OK, I can create one, if that really is required, but that does still not explain why all other databases work (user: mysql, group: root), only this one has become ReadOnly. Even this one used to work, but then something must have changed. And, as I mentioned earlier, if I make an identical copy of the ReadOnly database under a new name (user/group = mysql:root), or even just rename the database (i.e. rename the folder), the new database is Read/Write. I need to understand the problem, because the clients are remote and run automatically by cron. If I change the name of the database I need to make changes to every host, and that is unacceptable - especially if this happens again and I have to rename the database over and over again. /Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test Email - Please Disregard
Test Email - Please Disregard -:Kevin Carpenter CHOP - Biomedical Engineering (215) 590-5762 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Test Email - Please Disregard
Successfully disregarded ! -Ursprüngliche Nachricht- Von: Kevin Carpenter [mailto:[EMAIL PROTECTED] Gesendet: Montag, 15. März 2004 19:33 An: [EMAIL PROTECTED] Betreff: Test Email - Please Disregard Test Email - Please Disregard -:Kevin Carpenter CHOP - Biomedical Engineering (215) 590-5762 -- 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-test 4.1 fails with the gis test
Description: After pulling the 4.1 version from bk-internal.mysql.com:/home/bk/mysql-4.1, I compiled with BUILD/compile-pentium-debug and startet tests with make test. This failed with the gis test. I try to follow the instructions given at the end of the test output and from the HTML page http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html, to which it directed me. I sent the results to ftp://support.mysql.com/pub/mysql/secret/. The file is bugreport-2004-03-12-4.1-gis.txt How-To-Repeat: bk clone username@bk-internal.mysql.com:/home/bk/mysql-4.1 mysql-4.1 cd mysql-4.1 BUILD/compile-pentium-debug make test Fix: Submitter-Id: [EMAIL PROTECTED] Originator:MySQL Development Organization: MySQL GmbH MySQL support: none Synopsis: mysql-test 4.1 fails with the gis test Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.2-alpha-debug (Source distribution) C compiler:gcc (GCC) 3.3.3 20031206 (prerelease) (Debian) C++ compiler: gcc (GCC) 3.3.3 20031206 (prerelease) (Debian) Environment: PC, Debian GNU/Linux (sid), Pentium 4 libc62.3.2.ds1-10 zlib1g 1.2.1-3 System: Linux chilla 2.6.3 #4 Fri Mar 12 10:21:12 CET 2004 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc i486-linux Thread model: posix gcc version 3.3.3 (Debian 20040306) Compilation info: CC='gcc' CFLAGS='-g -march=pentium4' CXX='gcc' CXXFLAGS='-g -march=pentium4 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2004-01-27 15:06 /lib/libc.so.6 - libc-2.3.2.so -rw-r--r--1 root root 1244004 2004-01-20 18:29 /lib/libc-2.3.2.so -rw-r--r--1 root root 2653638 2004-01-20 18:29 /usr/lib/libc.a -rw-r--r--1 root root 204 2004-01-20 17:56 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock' '--with-debug' 'CFLAGS=-g -march=pentium4' 'CXXFLAGS=-g -march=pentium4 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 - Download - Viruses ?
While downloading this a I got a number of virus warnings from McAfee. I aborted the download. Anyone else experienced this? I emailed MySQL but didn't hear back. -:Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hard boot killed replication execution
My master server was hard booted and now my slave can't execute the replicated binary sql logs. mysql show slave status\G *** 1. row *** Master_Host: post.somehost.com Master_User: replicator Master_Port: 3306 Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651380657 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5987610630 1 row in set (0.00 sec) I can execute slave start and the IO will keep on running. (I think I had to execute some command to accomplish this, but it was a couple of weeks ago, and I don't remember what the command was.) mysql slave start; Query OK, 0 rows affected (0.00 sec) mysql show slave status\G *** 1. row *** Master_Host: post.somehost Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651817308 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5988047320 1 row in set (0.00 sec) As you can see, my relay logs are starting to take up a _lot_ of space and I really need to start executing them or I'll never catch up. This is the contents of my directory where the logs are: -rw-rw1 mysqld mysqld 79 Oct 7 23:10 backups-bin.001 -rw-rw1 mysqld mysqld 79 Oct 10 08:47 backups-bin.002 etc -rw-rw1 mysqld mysqld 3035 Feb 9 14:18 backups-bin.023 -rw-rw1 mysqld mysqld573 Mar 8 18:41 backups-bin.024 -rw-rw1 mysqld mysqld 79 Mar 9 10:39 backups-bin.025 -rw-rw1 mysqld mysqld450 Mar 9 10:39 backups-bin.index -rw-rw1 mysqld mysqld 611349208 Mar 9 07:42 backups-relay-bin.024 -rw-rw1 mysqld mysqld 1073741983 Mar 10 08:52 backups-relay-bin.025 etc... -rw-rw1 mysqld mysqld 1073742041 Mar 15 11:12 backups-relay-bin.029 -rw-rw1 mysqld mysqld 19673092 Mar 15 11:34 backups-relay-bin.030 -rw-rw1 mysqld mysqld168 Mar 15 11:12 backups-relay-bin.index -rw-rw1 mysqld root 315954 Mar 15 11:31 backups.somehost.com.err -rw-rw1 mysqld mysqld 5 Mar 9 10:39 backups.somehost.com.pid -rw-rw1 mysqld mysqld 10485624 Mar 1 22:32 log.55 etc... -rw-rw1 mysqld mysqld9005000 Mar 15 11:14 log.63 drwx--2 mysqld mysqld 16384 Aug 18 2003 lost+found -rw-rw1 mysqld mysqld 81 Mar 15 11:34 master.info -rw-r--r--1 mysqld mysqld 5023 Mar 9 10:39 my.cnf drwx--2 mysqld mysqld 4096 Nov 18 06:52 mysql -rw-rw1 mysqld mysqld 59 Mar 9 11:13 relay-log.info and the directories for my databases. I used mysqlbinlog to check what the last part of the backups-relay-bin.024 contents were and queried and saw that it really has completed it's statements. Looking at the head of the backups-relay-bin.025 reveals that its queries haven't been executed. So I think I need to somehow change the server to start executing at 025, right? When I execute CHANGE MASTER TO RELAY_LOG_FILE='backups-relay-bin.025' RELAY_LOG_POS=4; I get this: ERROR 1105: Failed initializing relay log position: Could not find target log during relay log initialization What does that mean? I can't find documenation on it anywhere. Am I going about recovering this correctly? HELP! Thanks, Daniel Gibby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get configuration info
Is there a way (utility, command, etc...) to find out what config settings a mysql server was started with? For example, someone started a server and I'd like to check to make sure the correct .cnf file was used? Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get configuration info
One way is to execute the SHOW VARIABLES command in the mysql client. Then you can see what all the values are set to and figure it out. You could also read the documentation and determine the order that locations are searched for to find my.cnf and figure it out. Daniel Gibby David B Hamby wrote: Is there a way (utility, command, etc...) to find out what config settings a mysql server was started with? For example, someone started a server and I'd like to check to make sure the correct .cnf file was used? Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: How to get configuration info
Is there a way (utility, command, etc...) to find out what config settings a mysql server was started with? For example, someone started a server and I'd like to check to make sure the correct .cnf file was used? Thanks. David e.g. SHOW VARIABLES LIKE 'key_buffer_size%'; etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two masters, one slave
Hi, Yes, but what about two masters doing circular replication with each other and one slave reading from one of the masters. Since all updates/inserts happen on both masters, they also happen on the slave, so in effect you have two masters. I just wish you could do that with three masters :) Thanks, Eric At 10:45 AM 3/12/2004, Ken Menzel wrote: Not at this time, however you could have two different slaves on the same physical server using mysql_mutli type of configuration. http://www.mysql.com/doc/en/mysqld_multi.html Hope it helps Ken - Original Message - From: Ari Davidow [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 1:30 PM Subject: two masters, one slave Is it possible to have two masters and one slave? I'm trying to replicate two master databases so that I have a failover. The alternate, I guess, would be to have two instances of mysql listing on different ports on the slave server platform? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hard boot killed replication execution
Did you look in the error log for the slave to find out the reason it can not contact the master? Original Message On 3/15/04, 1:39:44 PM, Daniel Gibby [EMAIL PROTECTED] wrote regarding hard boot killed replication execution: My master server was hard booted and now my slave can't execute the replicated binary sql logs. mysql show slave status\G *** 1. row *** Master_Host: post.somehost.com Master_User: replicator Master_Port: 3306 Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651380657 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5987610630 1 row in set (0.00 sec) I can execute slave start and the IO will keep on running. (I think I had to execute some command to accomplish this, but it was a couple of weeks ago, and I don't remember what the command was.) mysql slave start; Query OK, 0 rows affected (0.00 sec) mysql show slave status\G *** 1. row *** Master_Host: post.somehost Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651817308 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5988047320 1 row in set (0.00 sec) As you can see, my relay logs are starting to take up a _lot_ of space and I really need to start executing them or I'll never catch up. This is the contents of my directory where the logs are: -rw-rw1 mysqld mysqld 79 Oct 7 23:10 backups-bin.001 -rw-rw1 mysqld mysqld 79 Oct 10 08:47 backups-bin.002 etc -rw-rw1 mysqld mysqld 3035 Feb 9 14:18 backups-bin.023 -rw-rw1 mysqld mysqld573 Mar 8 18:41 backups-bin.024 -rw-rw1 mysqld mysqld 79 Mar 9 10:39 backups-bin.025 -rw-rw1 mysqld mysqld450 Mar 9 10:39 backups-bin.index -rw-rw1 mysqld mysqld 611349208 Mar 9 07:42 backups-relay-bin.024 -rw-rw1 mysqld mysqld 1073741983 Mar 10 08:52 backups-relay-bin.025 etc... -rw-rw1 mysqld mysqld 1073742041 Mar 15 11:12 backups-relay-bin.029 -rw-rw1 mysqld mysqld 19673092 Mar 15 11:34 backups-relay-bin.030 -rw-rw1 mysqld mysqld168 Mar 15 11:12 backups-relay-bin.index -rw-rw1 mysqld root 315954 Mar 15 11:31 backups.somehost.com.err -rw-rw1 mysqld mysqld 5 Mar 9 10:39 backups.somehost.com.pid -rw-rw1 mysqld mysqld 10485624 Mar 1 22:32 log.55 etc... -rw-rw1 mysqld mysqld9005000 Mar 15 11:14 log.63 drwx--2 mysqld mysqld 16384 Aug 18 2003 lost+found -rw-rw1 mysqld mysqld 81 Mar 15 11:34 master.info -rw-r--r--1 mysqld mysqld 5023 Mar 9 10:39 my.cnf drwx--2 mysqld mysqld 4096 Nov 18 06:52 mysql -rw-rw1 mysqld mysqld 59 Mar 9 11:13 relay-log.info and the directories for my databases. I used mysqlbinlog to check what the last part of the backups-relay-bin.024 contents were and queried and saw that it really has completed it's statements. Looking at the head of the backups-relay-bin.025 reveals that its queries haven't been executed. So I think I need to somehow change the server to start executing at 025, right? When I execute CHANGE MASTER TO RELAY_LOG_FILE='backups-relay-bin.025' RELAY_LOG_POS=4; I get this: ERROR 1105: Failed initializing relay log position: Could not find target log during relay log initialization What does that mean? I can't find documenation on it anywhere. Am I going about recovering this correctly? HELP! Thanks, Daniel Gibby -- 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: hard boot killed replication execution
The problem isn't that it can't connect to the master. The IO is running fine. It is just the SQL slave thread that won't restart. I have looked in the server log and that just has a duplication of the message I get when I try to CHANGE MASTER TO RELAY_LOG_FILE=... Error initializing relay log position: Could not find target log during relay log initialization Daniel Gibby [EMAIL PROTECTED] wrote: Did you look in the error log for the slave to find out the reason it can not contact the master? Original Message On 3/15/04, 1:39:44 PM, Daniel Gibby [EMAIL PROTECTED] wrote regarding hard boot killed replication execution: My master server was hard booted and now my slave can't execute the replicated binary sql logs. mysql show slave status\G *** 1. row *** Master_Host: post.somehost.com Master_User: replicator Master_Port: 3306 Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651380657 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5987610630 1 row in set (0.00 sec) I can execute slave start and the IO will keep on running. (I think I had to execute some command to accomplish this, but it was a couple of weeks ago, and I don't remember what the command was.) mysql slave start; Query OK, 0 rows affected (0.00 sec) mysql show slave status\G *** 1. row *** Master_Host: post.somehost Connect_retry: 60 Master_Log_File: post-bin.118 Read_Master_Log_Pos: 651817308 Relay_Log_File: backups-relay-bin.025 Relay_Log_Pos: 4 Relay_Master_Log_File: post-bin.111 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 1 Exec_master_log_pos: 462778298 Relay_log_space: 5988047320 1 row in set (0.00 sec) As you can see, my relay logs are starting to take up a _lot_ of space and I really need to start executing them or I'll never catch up. This is the contents of my directory where the logs are: -rw-rw1 mysqld mysqld 79 Oct 7 23:10 backups-bin.001 -rw-rw1 mysqld mysqld 79 Oct 10 08:47 backups-bin.002 etc -rw-rw1 mysqld mysqld 3035 Feb 9 14:18 backups-bin.023 -rw-rw1 mysqld mysqld573 Mar 8 18:41 backups-bin.024 -rw-rw1 mysqld mysqld 79 Mar 9 10:39 backups-bin.025 -rw-rw1 mysqld mysqld450 Mar 9 10:39 backups-bin.index -rw-rw1 mysqld mysqld 611349208 Mar 9 07:42 backups-relay-bin.024 -rw-rw1 mysqld mysqld 1073741983 Mar 10 08:52 backups-relay-bin.025 etc... -rw-rw1 mysqld mysqld 1073742041 Mar 15 11:12 backups-relay-bin.029 -rw-rw1 mysqld mysqld 19673092 Mar 15 11:34 backups-relay-bin.030 -rw-rw1 mysqld mysqld168 Mar 15 11:12 backups-relay-bin.index -rw-rw1 mysqld root 315954 Mar 15 11:31 backups.somehost.com.err -rw-rw1 mysqld mysqld 5 Mar 9 10:39 backups.somehost.com.pid -rw-rw1 mysqld mysqld 10485624 Mar 1 22:32 log.55 etc... -rw-rw1 mysqld mysqld9005000 Mar 15 11:14 log.63 drwx--2 mysqld mysqld 16384 Aug 18 2003 lost+found -rw-rw1 mysqld mysqld 81 Mar 15 11:34 master.info -rw-r--r--1 mysqld mysqld 5023 Mar 9 10:39 my.cnf drwx--2 mysqld mysqld 4096 Nov 18 06:52 mysql -rw-rw1 mysqld mysqld 59 Mar 9 11:13 relay-log.info and the directories for my databases. I used mysqlbinlog to check what the last part of the backups-relay-bin.024 contents were and queried and saw that it really has completed it's statements. Looking at the head of the backups-relay-bin.025 reveals that its queries haven't been executed. So I think I need to somehow change the server to start executing at 025, right? When I execute CHANGE MASTER TO RELAY_LOG_FILE='backups-relay-bin.025' RELAY_LOG_POS=4; I get this: ERROR 1105: Failed initializing relay log position: Could not find target log during relay log initialization What does that mean? I can't find documenation on it anywhere. Am I going about recovering this correctly? HELP! Thanks, Daniel Gibby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables in mySQL
Hi, Is it possible to create a temporary table in mySQL using columns from tables in two databases? Basically I want to split a table between two databases and if some logic is satisfied I want to merge(the right word?) the two tables into one in one of these databases. What's the best way to achieve this? I am using mySQL 4.0.15. Any information/help/suggestions or pointers would be much appreciated. Thanks -Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need to know how to export database to CSV format
I have a php website connecting to mysql database. How can i have a user export his database through the webpage to csv format? I would like the user to have a button he can press that would pop up a save as screen so he can save his database. I need to also be able that he can only save HIS OWN entries (PERSON_ID = ***) Can anyone direct me to a link that shows how you do that or explain to me how to do this? thanks very much Noam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auditing
Dan Vande More wrote: /* repost: considering I have no answers, is it safe to conclude it's not feasible?*/ I'd like to be able to audit connections to mysql databases. Here is what I'd like to see: Who connected to the server. What IP did they come from. When they disconnected. Any permission denied errors they may have had. The only thing that comes close to that is the general log, which is quite a bit of overhead. Has anyone come up with a solution for this? Does anyone see this in future versions? I've attempted the general query log, but it's not really in the best format for generating reports. Additionally, it's quite bloated when you do 300q/s. I've tried pushing it to a fifo, so I can have a daemon get the information, but mysql doesn't support that either. (my.cnf:log=/var/log/mysql.fifo) or (my.cnf:log=|/var/log/mysql.fifo, like syslog.conf) Has anyone else found solutions for this? Without source modifications, your current solution is as good as it gets. However, a fairly simple source change could give you want you need. In sql/mysqld.cc initialize what_to_log to ((1L (uint)COM_CONNECT) (1L (uint)COM_QUIT)) instead of ~(1L (uint) COM_TIME), recompile, and the run with --log - it will log only connects and disconnects. With a little bit more work, you can make it more configurable if desired. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a tough query
I didn't think this was too hard when I first created it, but now that I'm loading test data it's not working as expected. The core of the query is three tables: Event, Contact, and Regarding. There can be zero or more Contacts for each event and zero or more Regardings for each event. There are also 7 left joins I do to get other linked data, but that's working fine. So, the question is, how do I get a list of events with possible multiple contacts and multiple regardings? The problem I run into with my current query is that it fails when there are no contacts or regardings for an event. Is this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID It is possible that I can make the Contacts database a one to many relation since 99% of the time there will be an associated contact record. I can make a dummy record for the 1% exception, but it feels unclean. But if it makes it easier, so be it. Right now I am running 4.0, but I'm not tied to any version yet. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need to know how to export database to CSV format
Don't know if this will help, but this is how I offer web clients to save as excel in cold fusion: cfif #ViewInExcel# is Y cfcontent type=application/vnd.ms-excel /cfif the key is to change the content like above. I am not sure how to do this in PHP. But I am sure if you look around you will find it. Then just output the table, with each field broken up by td tags. No html tags or headings nothing but the table, TD, and TH tags need to be output. I have just recently started working in php, if I get a minute to put a example together in php I'll let you know. This will ask them if they want to save the file or open the file. The thing is you have to tell the client browser it is a type of file that it recognizes. Hope that helps, good luck. Todd Hackathorn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: need to know how to export database to CSV format I have a php website connecting to mysql database. How can i have a user export his database through the webpage to csv format? I would like the user to have a button he can press that would pop up a save as screen so he can save his database. I need to also be able that he can only save HIS OWN entries (PERSON_ID = ***) Can anyone direct me to a link that shows how you do that or explain to me how to do this? thanks very much Noam -- 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: Temporary tables in mySQL
It's dead easy; see the example below. By the way, the term you want is 'join', not 'merge'. In the example, Sample is the name of the database. 'emp' and 'dept' are two tables that can be joined on a common value; the common value is called 'workdept' in the 'emp' table and 'deptno' in the 'dept' table. Here are the table layouts: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | deptno | char(3) | | PRI | | | | deptname | varchar(36) | | | | | | mgrno| varchar(6) | YES | | NULL| | +--+-+--+-+-+---+ +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | empno| char(6) | | PRI | | | | firstnme | char(12) | | | | | | midinit | char(1) | YES | | NULL| | | lastname | char(15) | | | | | | workdept | char(3) | | MUL | | | | salary | decimal(9,2) | | | 0.00| | +--+--+--+-+-+---+ Here is the sample script, written in bash, that demonstrates what you want to do: use Sample; select Create temporary table containing join result as Comment; drop table if exists join_temp; create temporary table if not exists join_temp select * from dept as d inner join emp as e on e.workdept = d.deptno; select Display temporary table as Comment; select * from join_temp; Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 3:31 PM Subject: Temporary tables in mySQL Hi, Is it possible to create a temporary table in mySQL using columns from tables in two databases? Basically I want to split a table between two databases and if some logic is satisfied I want to merge(the right word?) the two tables into one in one of these databases. What's the best way to achieve this? I am using mySQL 4.0.15. Any information/help/suggestions or pointers would be much appreciated. Thanks -Ravi -- 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: Help with a tough query
Remarks interspersed below. Rhino - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: MYSQL list [EMAIL PROTECTED] Sent: Monday, March 15, 2004 4:32 PM Subject: Help with a tough query I didn't think this was too hard when I first created it, but now that I'm loading test data it's not working as expected. The core of the query is three tables: Event, Contact, and Regarding. There can be zero or more Contacts for each event and zero or more Regardings for each event. There are also 7 left joins I do to get other linked data, but that's working fine. So, the question is, how do I get a list of events with possible multiple contacts and multiple regardings? The problem I run into with my current query is that it fails when there are no contacts or regardings for an event. What do you mean 'fails'? Do you get an error message - if so, what is it? - or does it just not produce the result you want? Is this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID You're doing a natural/inner join here and that is why you never get Events when the Event is missing a Contact or a Regarding. Clearly, you need to do a left/right type join to handle the cases where an Event has no corresponding Contact or Regarding. Something like this: SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events as e left join Contacts as c left join Regarding as r on Events.EventID = Contacts.EventID c AND Events.EventID=Regadings.EventID I'm not saying that will work but it's closer than what you have so far. A little tweaking should get this to work. It is possible that I can make the Contacts database a one to many relation since 99% of the time there will be an associated contact record. I can make a dummy record for the 1% exception, but it feels unclean. But if it makes it easier, so be it. You really shouldn't have to make up dummy records to make this work. But it's nice to know you're open-minded if all else fails ;-) Right now I am running 4.0, but I'm not tied to any version yet. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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]
ERROR 2002: Can't connect to local MySQL server through socket
I have installed MySQL provided with SuSE Linux 9.0. My intention was to figure out how it could be used with PHP that is running on Apache. I managed to install all required RPM packages, but when I try to laynch MySQL (just typing MySQL], the following error message is displayed: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/ mysql/mysql.sock' (2) I checked the directory '/var/lib/mysql/' and there is no such file. How should I generate one? The server is just a localhost if this has any impact. Regards Sami -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 2002: Can't connect to local MySQL server through socket
You have to run the 'mysqld_safe ' command before you run 'mysql'. That creates the socket for you. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tough query
You're right, it doesn't fail, it just fails to give me the desired results. Left joining won't work because it will only grab one record from contacts and/or regarding if one exists, when there could be many. Thanks for the suggestion though. Thanks On Mar 15, 2004, at 4:47 PM, Rhino wrote: Remarks interspersed below. Rhino - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: MYSQL list [EMAIL PROTECTED] Sent: Monday, March 15, 2004 4:32 PM Subject: Help with a tough query I didn't think this was too hard when I first created it, but now that I'm loading test data it's not working as expected. The core of the query is three tables: Event, Contact, and Regarding. There can be zero or more Contacts for each event and zero or more Regardings for each event. There are also 7 left joins I do to get other linked data, but that's working fine. So, the question is, how do I get a list of events with possible multiple contacts and multiple regardings? The problem I run into with my current query is that it fails when there are no contacts or regardings for an event. What do you mean 'fails'? Do you get an error message - if so, what is it? - or does it just not produce the result you want? Is this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID You're doing a natural/inner join here and that is why you never get Events when the Event is missing a Contact or a Regarding. Clearly, you need to do a left/right type join to handle the cases where an Event has no corresponding Contact or Regarding. Something like this: SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events as e left join Contacts as c left join Regarding as r on Events.EventID = Contacts.EventID c AND Events.EventID=Regadings.EventID I'm not saying that will work but it's closer than what you have so far. A little tweaking should get this to work. It is possible that I can make the Contacts database a one to many relation since 99% of the time there will be an associated contact record. I can make a dummy record for the 1% exception, but it feels unclean. But if it makes it easier, so be it. You really shouldn't have to make up dummy records to make this work. But it's nice to know you're open-minded if all else fails ;-) Right now I am running 4.0, but I'm not tied to any version yet. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tough query
Brent, The problem I run into with my current query is that it fails when there are no contacts or regardings for an event. Is this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID Did you try ... SELECT Events.EventID, Contact.Name, Regarding.Name FROM Events LEFT JOIN Contacts USING (EventID) LEFT JOIN Regarding USING (EventID) GROUP BY Events.EventID; ? PB - Original Message - From: Brent Baisley To: MYSQL list Sent: Monday, March 15, 2004 3:32 PM Subject: Help with a tough query I didn't think this was too hard when I first created it, but now that I'm loading test data it's not working as expected. The core of the query is three tables: Event, Contact, and Regarding. There can be zero or more Contacts for each event and zero or more Regardings for each event. There are also 7 left joins I do to get other linked data, but that's working fine. So, the question is, how do I get a list of events with possible multiple contacts and multiple regardings? The problem I run into with my current query is that it fails when there are no contacts or regardings for an event. Is this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID It is possible that I can make the Contacts database a one to many relation since 99% of the time there will be an associated contact record. I can make a dummy record for the 1% exception, but it feels unclean. But if it makes it easier, so be it. Right now I am running 4.0, but I'm not tied to any version yet. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
composite Keys
Hi, does MySQL support composite keys? I have a table 'Group_Map' This has 3 fields contact_id (primary key in another table) group_id (candidate key in another table) user_id (primary key in another table) is it possible / advisable to create and maintain a composite key for this table? Kind Regards Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A join I can not wrap my head around...
Account table has a field state and a field id. Transaction table has fields id, user_id, amount, added Id is the PK in the account table, related to the user_id in the transaction table. I need to get a sum of the amount column for all users in state of TX in the year of 2003. Tried a few ways, cant seem to get this one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restoring a database from tape
Victor, Will this not work for Innodb tables? If my backup also includes system tablespaces and .ibd files ( we using multiple tablepsace), will that be a problem? Thanks Raza -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 9:03 AM To: mt m Cc: [EMAIL PROTECTED] Subject: Re: restoring a database from tape If the table types are all MyISAM and you took a consistent backup the answer is most likely yes. Original Message On 3/15/04, 7:50:39 AM, mt m [EMAIL PROTECTED] wrote regarding restoring a database from tape: Hi, Recently we EOLed a machine that had a mysql instance. The /usr/local/mysql dir was backed up to tape before EOLing. Having now installed a new machine, we need to copy the old database from tape to the new machine. Mysql has been successfully installed on the new machine. To install the database, can I just copy the ../mysql/data directory from the tape to the corresponding dir on the new machine? thanks for your time. -M _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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]
Re: restoring a database from tape
You should not have any problem. The only caveat is ensure you have the same version and directory structure. Good Luck! Pat... Patrick Sherrill Southwest Florida's First ISP CocoNet Corporation - Original Message - From: Ansari, Raza (GEI, GEFA) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Cc: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 5:52 PM Subject: RE: restoring a database from tape Victor, Will this not work for Innodb tables? If my backup also includes system tablespaces and .ibd files ( we using multiple tablepsace), will that be a problem? Thanks Raza -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 9:03 AM To: mt m Cc: [EMAIL PROTECTED] Subject: Re: restoring a database from tape If the table types are all MyISAM and you took a consistent backup the answer is most likely yes. Original Message On 3/15/04, 7:50:39 AM, mt m [EMAIL PROTECTED] wrote regarding restoring a database from tape: Hi, Recently we EOLed a machine that had a mysql instance. The /usr/local/mysql dir was backed up to tape before EOLing. Having now installed a new machine, we need to copy the old database from tape to the new machine. Mysql has been successfully installed on the new machine. To install the database, can I just copy the ../mysql/data directory from the tape to the corresponding dir on the new machine? thanks for your time. -M _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A join I can not wrap my head around...
create table account ( id int(11), state varchar(2) ); create table transaction ( id int(11), user_id int(11), amount int(6), added timestamp ); select sum(transaction.amount) from transaction, account where transaction.id = account.id AND account.state = 'TX' AND transaction.added between '2003010100' and '20031231235959'; On Mon, 2004-03-15 at 14:37, Scott Haneda wrote: Account table has a field state and a field id. Transaction table has fields id, user_id, amount, added Id is the PK in the account table, related to the user_id in the transaction table. I need to get a sum of the amount column for all users in state of TX in the year of 2003. Tried a few ways, cant seem to get this one. So, given: create table account ( id int(11), state varchar(2) ); create table transaction ( id int(11), user_id int(11), amount int(6), added timestamp ); Did you try: select sum(transaction.amount) from transaction, account where transaction.id = account.id AND account.state = 'TX' AND transaction.added between '2003010100' and '20031231235959'; ? -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Great QUERY question
This is for those who love a challenge. I am trying to come up with a query that would calculate the Standard Deviation and Variance for 15 fields. Although in theory this is easily done in Access, MySQL does not have the same mathematical calculations that Access/SQL does. Here is the query as it stands in Access: Select avg(Item01) as Item01a, stdev(Item01) as Item01d, avg(Item02) as Item02a, stdev(Item02) as Item02d, avg(Item03) as Item03a, stdev(Item03) as Item03d, avg(Item04) as Item04a, stdev(Item04) as Item04d, avg(Item05) as Item05a, stdev(Item05) as Item05d, avg(Item06) as Item06a, stdev(Item06) as Item06d, avg(Item07) as Item07a, stdev(Item07) as Item07d, avg(Item08) as Item08a, stdev(Item08) as Item08d, avg(Item09) as Item09a, stdev(Item09) as Item09d, avg(Item10) as Item10a, stdev(Item10) as Item10d, avg(Item11) as Item11a, stdev(Item11) as Item11d, avg(Item12) as Item12a, stdev(Item12) as Item12d, avg(Item13) as Item13a, stdev(Item13) as Item13d, avg(Item14) as Item14a, stdev(Item14) as Item14d, avg(Item15) as Item15a, stdev(Item15) as Item15d, avg(overallscore) as overa, stdev(overallscore) as overd from tblFacultyEvalSurgery This is for an online grading system that, except for 5 pages, has been converted over to MySQL and Linux / Apache. 2 of which have this problem. Avg() is easy, but it's the StDev that I can't get. St Dev is made from Variance (or the mean), which again is not a function of MySQL. To view the Variance and StDev formulae, http://davidmlane.com/hyperstat/A16252.html. If you have any ideas, I will be working on this for the next few days. Thanks! J.R.
Re: Switch replication master
Depending on the size of your databases, it might be a good idea to copy back your 'data' directory from slave to master. I think this should work better, even because the master wouldn't get the INSERT/UPDATEs made on the slave. []s, Conrado - Original Message - From: Batara Kesuma [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 2:30 PM Subject: Switch replication master Hi, I use replication with 1 master and 1 slave. Right now the slave is only used to make a backup. Next week I am going to change my master motherboard. So I need to take it offline for a while (about 90 minutes). I am thinking about switching the master for that 90 minutes, so the slave will become master when master is offline, and after master is back online switch back slave to slave. Here is how I am going to do it: 1. Change all my code to make the DB query to slave. 2. Take master offline, change motherboard. 3. Master back online. How do I switch back the master now? Any idea? I don't mind to take offline both slave and master for a while if it is not too long. Please help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Indian languages and working with them using C API()
Hello, I have a project where I need to develop a database product with working handling languages like hindi etc. The application will be written in Win32 API so I will be using MySQL C API(). Can somebody give me pointers how to store and retrieve data in Hindi using MySQL C API and MySQL in general. Since alll the hindi text will be in unicode format and C API uses char* parameter how do I interface between my Hindi text data and C API. DO I convert them to some other format like utf-8 etc. If anybody has developed an application using MySQL with complex script like Hindiatheir ideas will be very helpful. Regards Karam Rega __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How could I do this insert/select?
Hi,
How could I do this insert/select?
create table category
(
id tinyint(4) NOT NULL auto_increment,
name varchar(20) NOT NULL,
parent tinyint(4) NULL,
primary key(id)
);
insert into category (name, parent) values('cc_1',null);
insert into category (name, parent) select 'cc_2a', id from category where
name = 'cc_1';
MySQL don't like the insert/select.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Using Indian languages and working with them using C API()
Can somebody give me pointers how to store and retrieve data in Hindi using MySQL C API and MySQL in general. Since alll the hindi text will be in unicode format and C API uses char* parameter how do I interface between my Hindi text data and C API. DO I convert them to some other format like utf-8 etc. I don't have very much experience writing clients in C, but I have been following the MySQL unicode development. If you haven't already it might help to look at the MySQL source that deals with unicode. In the strings directory there are two files ctype-utf8.c and ctype-ucs2.c. All the sorting and string comparisons for unicode are defined there. I think the conversion from char* to unicode is done there. I suggest you look at the latest source for 4.1.2 in the bitkeeper tree because changes are still being made to this code. I expect there will be more significant changes in the next couple of weeks. It might also help to read the SET CHARACTER SET and SET NAMES commands here: http://www.mysql.com/doc/en/Charset.html Good luck, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switch replication master
Hi, Could you take the database down long enough (scheduled downtime) to make the master a slave to the slave? I seem to remember reading on this mail list that you can do circular replication... or am I talking nonsense? That way when you took the original master down for maintenance, then brought it up later, it would synchronize properly. Maybe someone more knowledgeable will respond... Cheers, Steve cvarda wrote: Depending on the size of your databases, it might be a good idea to copy back your 'data' directory from slave to master. I think this should work better, even because the master wouldn't get the INSERT/UPDATEs made on the slave. []s, Conrado - Original Message - From: Batara Kesuma [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 2:30 PM Subject: Switch replication master Hi, I use replication with 1 master and 1 slave. Right now the slave is only used to make a backup. Next week I am going to change my master motherboard. So I need to take it offline for a while (about 90 minutes). I am thinking about switching the master for that 90 minutes, so the slave will become master when master is offline, and after master is back online switch back slave to slave. Here is how I am going to do it: 1. Change all my code to make the DB query to slave. 2. Take master offline, change motherboard. 3. Master back online. How do I switch back the master now? Any idea? I don't mind to take offline both slave and master for a while if it is not too long. Please help.
Re: Switch replication master
You can do a two-way replication. According to MySQL docs, however, this isn't recommended since atomicity of update operations (depending on the scenario, of course, this isn't a problem). I'm not sure, but I think that making the master a slave of the slave that would become a master temporarily wouldn't work well because no binary logs will have been maintained for such slave (previous master). This is in theory, of course. Then, so far, I still think that copying by hand the data dir back to the master would be the best way (and easiest) to get the master up again and up-to-date. []s, Conrado - Original Message - From: Steve Williams To: cvarda Cc: Batara Kesuma ; [EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 3:28 AM Subject: Re: Switch replication master Hi, Could you take the database down long enough (scheduled downtime) to make the master a slave to the slave? I seem to remember reading on this mail list that you can do circular replication... or am I talking nonsense? That way when you took the original master down for maintenance, then brought it up later, it would synchronize properly. Maybe someone more knowledgeable will respond... Cheers, Steve cvarda wrote: Depending on the size of your databases, it might be a good idea to copy back your 'data' directory from slave to master. I think this should work better, even because the master wouldn't get the INSERT/UPDATEs made on the slave. []s, Conrado - Original Message - From: Batara Kesuma [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 15, 2004 2:30 PM Subject: Switch replication master Hi, I use replication with 1 master and 1 slave. Right now the slave is only used to make a backup. Next week I am going to change my master motherboard. So I need to take it offline for a while (about 90 minutes). I am thinking about switching the master for that 90 minutes, so the slave will become master when master is offline, and after master is back online switch back slave to slave. Here is how I am going to do it: 1. Change all my code to make the DB query to slave. 2. Take master offline, change motherboard. 3. Master back online. How do I switch back the master now? Any idea? I don't mind to take offline both slave and master for a while if it is not too long. Please help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How could I do this insert/select?
It seems to work on my MySQL (4.0.18).
Tell us which version are you using and the error code/message returned.
-- excerpt --
mysql create table category
- (
- id tinyint(4) NOT NULL auto_increment,
- name varchar(20) NOT NULL,
- parent tinyint(4) NULL,
- primary key(id)
- );
Query OK, 0 rows affected (0.03 sec)
mysql insert into category (name, parent) values('cc_1',null);
Query OK, 1 row affected (0.01 sec)
mysql
mysql insert into category (name, parent) select 'cc_2a', id from category
where name = 'cc_1';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql select * from category;
++---++
| id | name | parent |
++---++
| 1 | cc_1 | NULL |
| 2 | cc_2a | 1 |
++---++
2 rows in set (0.00 sec)
mysql
-- excerpt --
[]s,
Conrado
- Original Message -
From: stephen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 16, 2004 2:57 AM
Subject: How could I do this insert/select?
Hi,
How could I do this insert/select?
create table category
(
id tinyint(4) NOT NULL auto_increment,
name varchar(20) NOT NULL,
parent tinyint(4) NULL,
primary key(id)
);
insert into category (name, parent) values('cc_1',null);
insert into category (name, parent) select 'cc_2a', id from category where
name = 'cc_1';
MySQL don't like the insert/select.
Thanks
--
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: How could I do this insert/select?
mysql use zzz
Database changed
mysql create table category
- (
- id tinyint(4) NOT NULL auto_increment,
- name varchar(20) NOT NULL,
- parent tinyint(4) NULL,
- primary key(id)
- );
Query OK, 0 rows affected (0.00 sec)
mysql insert into category (name, parent) values('cc_1',null);
insert into category (name, parent) select 'cc_2a', id from category where
name = 'cc_1';
Query OK, 1 row affected (0.01 sec)
mysql insert into category (name, parent) select 'cc_2a', id from category
where name = 'cc_1';
ERROR 1066: Not unique table/alias: 'category'
mysql show tables;
+---+
| Tables_in_zzz |
+---+
| category |
+---+
1 row in set (0.00 sec)
mysql status
--
/usr/local/mysql/bin/mysql Ver 11.17 Distrib 3.23.49a, for pc-linux-gnu
(i686)
Connection id: 25
Current database: zzz
Current user: [EMAIL PROTECTED]
Current pager: stdout
Using outfile: ''
Server version: 3.23.49a
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset:latin1
Server characterset:latin1
UNIX socket:/tmp/mysql.sock
Uptime: 2 days 14 hours 36 min 36 sec
Threads: 1 Questions: 313 Slow queries: 0 Opens: 70 Flush tables: 1
Open tables: 15 Queries per second avg: 0.001
--
I don't have version 4 at my ISP either.
Thanks
- Original Message -
From: cvarda [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 15, 2004 10:57 PM
Subject: Re: How could I do this insert/select?
It seems to work on my MySQL (4.0.18).
Tell us which version are you using and the error code/message returned.
-- excerpt --
mysql create table category
- (
- id tinyint(4) NOT NULL auto_increment,
- name varchar(20) NOT NULL,
- parent tinyint(4) NULL,
- primary key(id)
- );
Query OK, 0 rows affected (0.03 sec)
mysql insert into category (name, parent) values('cc_1',null);
Query OK, 1 row affected (0.01 sec)
mysql
mysql insert into category (name, parent) select 'cc_2a', id from
category
where name = 'cc_1';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql select * from category;
++---++
| id | name | parent |
++---++
| 1 | cc_1 | NULL |
| 2 | cc_2a | 1 |
++---++
2 rows in set (0.00 sec)
mysql
-- excerpt --
[]s,
Conrado
- Original Message -
From: stephen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 16, 2004 2:57 AM
Subject: How could I do this insert/select?
Hi,
How could I do this insert/select?
create table category
(
id tinyint(4) NOT NULL auto_increment,
name varchar(20) NOT NULL,
parent tinyint(4) NULL,
primary key(id)
);
insert into category (name, parent) values('cc_1',null);
insert into category (name, parent) select 'cc_2a', id from category
where
name = 'cc_1';
MySQL don't like the insert/select.
Thanks
--
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]
Jumping to prev and next records
I know I can do this in code, but was wondering if I am overlooking something in the docs about it. Table images --- Id url Currently, I show a simple list on a page where one can click on the image and it pops open a new window and loads url as a img src. I have been asked to add in prev | next links at the bottom of the pop up window. I was thinking I can do a select id from images to get a list of all the id's and use some code to create the proper links, if I am at the last link, I can wrap to the first one and vice versa. Is perhaps there some way to get the next id and prev id in a mysql query based on id being sorted? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
