Re: which duplicate key was hit on last insert?
Hi, CREATE TABLE Test ( COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COL2VARCHAR(10) NOT NULL, COL3VARCHAR(10) NOT NULL, UNIQUE(COL2, COL3); --(not that) FULLTEXT(col1,col2) ); Try this property FULLTEXT -- Yaya SIRIMA Engineer - Database Developer +226 76868436 [EMAIL PROTECTED]
How to find Free space in innodb
Hi I am running 5.0.46-enterprise-gpl-log version in linux (Red Hat Enterprise Linux AS release 4 (Nahant Update 5). In my.cnf i have configured innodb as : innodb_data_file_path = datafile1:500M;datafile2:500M;datafile3:500M;datafile4:500M;datafile5:500M;datafile6:500M;datafile7:500M and datafile is created as per the configuration in the disk. My Question is how to find the exact free space avaliable in these datafile.How much data is occupied in the disk. Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: secure mysql port
Kelly Opal wrote: Hi Is there any way to restrict access to the tcp port on mysql. I only want my 5 class C's to be able to access the port but it is a public server. Iptables Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 6:47 PM, Yves Goergen [EMAIL PROTECTED] wrote: From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. I don't think that's correct. At least that's not how I read this: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html It sounds like you issue a LOCK TABLES at the beginning of your transaction, and doing a COMMIT unlocks the tables at the end. From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. I think I'll go for transactions and check the error code in most cases. Only where a custom check is needed, I'll lock the tables without using a transaction. I'll see how far I get with it. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. Bad. The only thing I can do then is write the data and afterwards count if there are two of them. But this still isn't safe, in concurrency means. Any solution? May be a bug report? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. I don't understand what you mean. It's not portable to SQLite, but you can use a sequence there instead. To my knowledge, SQLite doesn't support sequences either, only auto_increment. I've began to convert my code to evaluate error codes now, but I see the next problem already: At one place, I insert a row where two columns could potentially violate a uniqueness constraint. With just reading the error code, I can't figure out which of them caused the problem. The error message I can present to the user will be somewhat generic then. (Either this or that of your input already exists. Find out which one. Haha!) Maybe I'll use error codes or table locks depending on the situation. It's all a big hack, but so is databases (and portability) it seems. I'm not sure yet. It's late. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy
I am trying to use mysqlhotcopy on 2 different machines and I am having trouble on both of them. On the first machine, which is a Sun Solaris running mysql 4.0.15a, when I give the mysqlhotcopy command, I get the following error: DBD::mysql::db do failed: File './zemed/form_342.MYD' not found (Errcode: 24) at /usr/local/mysql/bin/mysqlhotcopy line 449. The file zemed/form_342.MYD does exist, but it's size is 0. On the second machine, which is a linux machine running mysql 5.0.45, when I give the mysqlhotcopy command, mysqlhotcopy web_positions /tmp/web_positions I get the error Invalid db.table name 'web_positions.web_positions`.`acadsec' at /usr/local/mysql/bin/mysqlhotcopy line 855. If I try to copy the mysql database, I get the error Invalid db.table name 'mysql.mysql`.`columns_priv' at /usr/local/mysql/bin/mysqlhotcopy line 855. Does anyone have any suggestions as to what I can do to get this to work. thanks. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On Thu, 2007-11-08 at 17:56 -0800, Lucky Wijaya wrote: Yes, the trigger code is works. Many thanks !! Now I understand the use of delimiter command. Thanks again... =) My next question is, do we able to view the triggers that has been created ? And how ? David Schneider-Joseph [EMAIL PROTECTED] wrote: My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com mysql show triggers; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems backing up 4.1.20 database
Hello, I'm using CentOS 4.5 with MySQL 4.1.20. I've got 2G RAM on the system, and am running an x86_64 kernel (2.6.9-55.0.9.EL). When I try to use mysqldump, MySQL crashes (then restarts). Here is the output from mysqldump, and what happens in /var/log/mysql.log. From mysqldump: --- [symbiot (06:35:45) ~]# mysqldump -u root -p UniversalTimeline Enter password: -- MySQL dump 10.9 -- -- Host: localhostDatabase: UniversalTimeline -- -- -- Server version 4.1.20 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES From /var/log/mysql.log: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Number of processes running now: 0 071113 06:36:06 mysqld restarted 071113 6:36:07 InnoDB: Started; log sequence number 0 43644 /usr/libexec/mysqld: ready for connections. Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution Any hints as to what's going on here? I don't remember having this problem with MySQL 3.x (but that was many moons ago). Cheers, Arpotu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 4:53 AM, Yves Goergen [EMAIL PROTECTED] wrote: From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. I read that as saying that you can't issue a LOCK TABLES and then another LOCK TABLES in the same transaction, because it causes a COMMIT before locking the tables. You can use one LOCK TABLES at the beginning of your transaction with no problems. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. Yes, and you will be in a transaction, and the insert will be rolled back. But maybe UNLOCK TABLES would commit your transaction, in which case, you do need to keep the lock until the transaction is over. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. No, the text you're quoting there says that LOCK TABLES is impossible without a transaction in InnoDB. You plan to use a transaction. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find Free space in innodb
Hi, John Dba wrote: Hi I am running 5.0.46-enterprise-gpl-log version in linux (Red Hat Enterprise Linux AS release 4 (Nahant Update 5). In my.cnf i have configured innodb as : innodb_data_file_path = datafile1:500M;datafile2:500M;datafile3:500M;datafile4:500M;datafile5:500M;datafile6:500M;datafile7:500M and datafile is created as per the configuration in the disk. My Question is how to find the exact free space avaliable in these datafile.How much data is occupied in the disk. Use SHOW TABLE STATUS on any InnoDB table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Use select within delete
Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves,
Damn, I found out that I need table locking *and* transactions.
What makes you say that?
BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
That Perl module uses the exact technique I described to you with
updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
I would suggest the following --
create a table called SEQUENCES:
create table SEQUENCES
( table_name varchar(128/maxlength of tablename) not null primary key,
sequence_value largeint not null) ;
Create a row for each table, eg:
insert into sequences values('CUSTOMERS', 0);
Next, whenever you want to get a new value, do:
select sequence_value as current_value
from sequences
where table_name = 'CUSTOMERS';
Next, do this:
update sequences
set sequence_value = sequence_value + 1
where sequence_value = your current value you just got
and table_name = 'CUSTOMERS'
Now, repeate the above sequence until the UPDATE statement
above says that it's updated 1 row. If it updated 0 rows, it means
someone else did it just before you.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Result set flipped on it's axis
Let's say I have the following table: CREATE TABLE `Users` ( `id` blahblah, `firstName` blahblah, `lastName` blahblah, `phone` blahblah, `fax` blahblah, `email` blahblah ); If I do SELECT id, firstName, lastName, email FROM Users, my result set is returned as follows: ++---++-+ | Id | Firstname | LastName | EmailAddress| ++---++-+ | 1 | John | Doe| [EMAIL PROTECTED] | | 2 | Joe | Bob| [EMAIL PROTECTED] | ++---++-+ as expected. But I'm wondering if I could somehow form the query such that the result set is turned on it's axis like so: ++--++ | 1 | Firstname| John | | 1 | LastName | Doe| | 1 | EmailAddress | [EMAIL PROTECTED] | | 2 | Firstname| Joe| | 2 | LastName | Bob| | 2 | EmailAddress | [EMAIL PROTECTED] | ++--++ or some approximation thereof? thnx, Chris
indexing tables using my owns functions
Hi I've created a function that return a float value the code for it is : create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7 varchar(20),pin9 varchar(20),MOL varchar(20)) returns float DETERMINISTIC begin declare output float; declare P1 float; declare P4 float; declare P6 float; declare P7 float; declare P9 float; select VALUE into P1 from PSSMS where AA=pin1 and POS='1' and MOLEC=MOL; select VALUE into P4 from PSSMS where AA=pin4 and POS='4' and MOLEC=MOL; select VALUE into P6 from PSSMS where AA=pin6 and POS='6' and MOLEC=MOL; select VALUE into P7 from PSSMS where AA=pin7 and POS='7' and MOLEC=MOL; select VALUE into P9 from PSSMS where AA=pin9 and POS='9' and MOLEC=MOL; select P1+P4+P6+P7+P9 into output; return output; end // And it works, now, i would like index a table using this function. The table description is: mysql describe precalc; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(6)| NO | PRI | NULL| auto_increment | | P1| char(1) | YES || NULL|| | P4| char(1) | YES || NULL|| | P6| char(1) | YES || NULL|| | P7| char(1) | YES || NULL|| | P9| char(1) | YES || NULL|| +---+-+--+-+-++ 6 rows in set (0.01 sec) and i try index by the following command: mysql create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? Thanks Pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: indexing tables using my owns functions
mysql create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? As far as I can see ( http://dev.mysql.com/doc/refman/5.0/en/create-index.html ) you can only use columns, not a function. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger/cron process questions...
Hi. I'm considering a situation where I have a number of child/client servers, each of which are running local apps that feed into a local mysql db/tbl. In order to manage the data, I want to copy all the mysql db/tbl data from the chil/client systems, to a single central/master db. I do not want to simply have the local apps write directly to the central db for a number of reasons. The approach I need, is to write local, and then copy this information from the local mysql, to the central/parent mysql/db on a separate machine. I've considered Replication (Master/Slave) but then realized that you can't have a slave, with multiple masters. In my case, each of the child systems, would be considered to be Masters, with the central machines being the slave. So it appears that the mysql replication isn't suitable. I'm considering simply using cron processes on the child machines, where the cron app would simply fir on a periodic basis, and write any new data from the child db to the central system (assuming the network/central machine is up/running). This kind of process is simple, full proof, and pretty straightfoward to implement. In researching, I've come across articles discussing triggers, and I'm wondering if triggers might prove usefful or this issue. Is it possible to have a periodic trigger, IE a trigger that gets fired based on time. I could have a cron process that updates a tbl on a periodic basis, and a trigger on that tbl. When that trigger fires, it could then update/insert the local data into the remote/central db/tbl. Thoughts/Comments/Pointers/Etc.. .would be helpful. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: loading scripts to mysql
hi, Tanks for your help, finally i found the source command. It work like this: mysql source /Lhome/geruppa/mhc/Pack_Ref_web/prova.sql 2007/11/9, Michael Gargiullo [EMAIL PROTECTED]: On Fri, 2007-11-09 at 13:22 +0100, Pau Marc Munoz Torres wrote: Hi everybody I'm writing a function script in a flat file using vim, now i would like load it into my sql, there is some command to do it similar to load data into to fill tables? thanks Sure, From command line: mysql -u username -p databasefile-containing-sql -Mike -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
indexing tables using my owns functions
Hi I've created a function that return a float value the code for it is : create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7 varchar(20),pin9 varchar(20),MOL varchar(20)) returns float DETERMINISTIC begin declare output float; declare P1 float; declare P4 float; declare P6 float; declare P7 float; declare P9 float; select VALUE into P1 from PSSMS where AA=pin1 and POS='1' and MOLEC=MOL; select VALUE into P4 from PSSMS where AA=pin4 and POS='4' and MOLEC=MOL; select VALUE into P6 from PSSMS where AA=pin6 and POS='6' and MOLEC=MOL; select VALUE into P7 from PSSMS where AA=pin7 and POS='7' and MOLEC=MOL; select VALUE into P9 from PSSMS where AA=pin9 and POS='9' and MOLEC=MOL; select P1+P4+P6+P7+P9 into output; return output; end // And it works, now, i would like index a table using this function. The table description is: mysql describe precalc; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(6)| NO | PRI | NULL| auto_increment | | P1| char(1) | YES || NULL|| | P4| char(1) | YES || NULL|| | P6| char(1) | YES || NULL|| | P7| char(1) | YES || NULL|| | P9| char(1) | YES || NULL|| +---+-+--+-+-++ 6 rows in set (0.01 sec) and i try index by the following command: mysql create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? Thanks Pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: Use select within delete
Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp table. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing tables using my owns functions
, as far as i can see, from mysql 5.0 and upper it is possible create index using functions. http://www.faqs.org/docs/ppbook/r24254.htm But i keep having problems with the exemple from the link. Is there any bug in mysql 5.0.24a-log? 2007/11/13, Martijn Tonies [EMAIL PROTECTED]: mysql create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? As far as I can see ( http://dev.mysql.com/doc/refman/5.0/en/create-index.html ) you can only use columns, not a function. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: indexing tables using my owns functions
, as far as i can see, from mysql 5.0 and upper it is possible create index using functions. http://www.faqs.org/docs/ppbook/r24254.htm But i keep having problems with the exemple from the link. Is there any bug in mysql 5.0.24a-log? The above website says: Practical PostgreSQL I cannot find MySQL anywhere on that page. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Baron Schwartz wrote: Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You.
select sum order
hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. thank much, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Issue with Upgrade from 4.0.x to 5.0.x
I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log in a single master-slave environment. I've previously upgraded the slave to 5.0.22-log and restarted replication without issue. The current master is running on RH9 and the slave is running on CentOS 5, which is what I'm trying to upgrade the master to, also. After synchronizing the databases, I've run 'reset master' and 'reset slave' on their respective servers, then 'change master to...' on the slave, but 'show slave status' always displays: 'Slave_IO_Running: No' And here is the log entry from the slave: 071113 9:16:19 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 071113 9:17:35 [Note] Slave I/O thread killed while connecting to master The replication user has 'replication slave' privileges, and basically I've duplicated my.cnf from the old to the new master. I've done some Googling and searching of archives but not much luck. Fortunately, I've been trying out the master upgrade on a temp machine, and it's fairly easy to revert back to the current master server. But if anyone has thoughts or suggestions about what to try next, I'd be most appreciative. Thanks, David.
Re: Replication Issue with Upgrade from 4.0.x to 5.0.x
Hi, [EMAIL PROTECTED] wrote: I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log in a single master-slave environment. I've previously upgraded the slave to 5.0.22-log and restarted replication without issue. The current master is running on RH9 and the slave is running on CentOS 5, which is what I'm trying to upgrade the master to, also. After synchronizing the databases, I've run 'reset master' and 'reset slave' on their respective servers, then 'change master to...' on the slave, but 'show slave status' always displays: 'Slave_IO_Running: No' And here is the log entry from the slave: 071113 9:16:19 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 071113 9:17:35 [Note] Slave I/O thread killed while connecting to master The replication user has 'replication slave' privileges, and basically I've duplicated my.cnf from the old to the new master. I've done some Googling and searching of archives but not much luck. Fortunately, I've been trying out the master upgrade on a temp machine, and it's fairly easy to revert back to the current master server. But if anyone has thoughts or suggestions about what to try next, I'd be most appreciative. Check the server_id on each server. Sounds like one or more of the servers either doesn't have it explicitly set in my.cnf, or there is a duplicated server ID somewhere. Note that the default value doesn't count as being set in MySQL 5. It has to be in the my.cnf file. That's my experience anyway. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select sum order
Hi, Hiep Nguyen wrote: hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Processlist full of Opening tables
Thanks for the reply. It helped alot, since I did not know where to look. I read the documentation regarding the issue and some more pages google turned up for me. I did increase my table cache to 16k and my open files are at 30k. I do run debian etch 32-bit. How would I determine what reasonable means for open files are on my system? I have 4GB Ram and a 2.6 Ghz DualCore. What also interests me is my key buffer. I have it set to 768MB: mysql show status like key%; ++--+ | Variable_name | Value| ++--+ | Key_blocks_not_flushed | 0| | Key_blocks_unused | 661370 | | Key_blocks_used| 40169| | Key_read_requests | 22935142 | | Key_reads | 65152| | Key_write_requests | 1941899 | | Key_writes | 162770 | ++--+ 7 rows in set (0.00 sec) This would mean the following: 65152 Key_reads / 22935142 Key_read_requests * 100 = 0.29 % I think this would be an reasonable value. Am I right? Regards, Samy Brent Baisley schrieb: The problem is your table cache setting. MySQL will only keep a certain amount of tables open and ready for access. Once that limit is hit, if a table that is not already open needs to be accessed, it needs to close a table to make room for opening the new table. This doesn't mean you can just set the table cache size really high. The operating system has limits as to how many file handles it can have open at one time. In mysql type this: show status like open% Your Opened_tables number is probably huge. That's how many times mysql had to open a table for access. Meaning the table wasn't opened already. The Open_tables is how many tables MySQL will keep open (table cache). You need to increase this number using the table_cache variable. set global table_cache=#; The Open_files number from the show status command is important, make sure that stays within limits of your operating system. On Nov 12, 2007 5:09 PM, Samuel Vogel [EMAIL PROTECTED] wrote: Hey guys, I do run MySQL on a high traffic Server with approximately 10k databases. Since some time MySQL is has become very sluggish. When I look at my processlist it shows more than 25 processes (sometimes of the same table) with status Opening tables. Some processes also show closing tables. Since I am running I shared hosting environment, I can not examine the situation to such extent, where I could see if table locks are involved. But it does not seem to be the case, since it does not appear for just some users, but for everybody. Also when I run a simple select query it takes more than 2 sec. I guess MySQL is trying to tell me, that my hard drive is too slow. How could I ease this situation ? My key_buffer is set to 1Gb of my 4Gb system memory. Other values are tweaked as well, but I do not think the matter in that case. Would partitioning the disk as Raid 0 ease the situation? What other places do I have to look at, to further narrow down the problem? Regards, Samy -- 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: Replication Issue with Upgrade from 4.0.x to 5.0.x
[EMAIL PROTECTED] wrote: I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log in a single master-slave environment. I've previously upgraded the slave to 5.0.22-log and restarted replication without issue. The current master is running on RH9 and the slave is running on CentOS 5, which is what I'm trying to upgrade the master to, also. After synchronizing the databases, I've run 'reset master' and 'reset slave' on their respective servers, then 'change master to...' on the slave, but 'show slave status' always displays: 'Slave_IO_Running: No' And here is the log entry from the slave: 071113 9:16:19 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 071113 9:17:35 [Note] Slave I/O thread killed while connecting to master grep deny /etc/hosts.deny Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select sum order
thanks, T. Hiep On Tue, 13 Nov 2007, Baron Schwartz wrote: Hi, Hiep Nguyen wrote: hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issue with Upgrade from 4.0.x to 5.0.x
David Campbell wrote: [EMAIL PROTECTED] wrote: I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log in a single master-slave environment. I've previously upgraded the slave to 5.0.22-log and restarted replication without issue. The current master is running on RH9 and the slave is running on CentOS 5, which is what I'm trying to upgrade the master to, also. After synchronizing the databases, I've run 'reset master' and 'reset slave' on their respective servers, then 'change master to...' on the slave, but 'show slave status' always displays: 'Slave_IO_Running: No' And here is the log entry from the slave: 071113 9:16:19 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 071113 9:17:35 [Note] Slave I/O thread killed while connecting to master grep deny /etc/hosts.deny Dave Dave: There are no uncommented entries in /etc/hosts.deny Baron: The all servers have a unique server-id in their respective my.cnf's When I try to connect directly from the slave to the new master, I get: ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.1xx.1xx.xx' (113) I'll make a super user to test... David
Re: Replication Issue with Upgrade from 4.0.x to 5.0.x
Dave: There are no uncommented entries in /etc/hosts.deny Baron: The all servers have a unique server-id in their respective my.cnf's When I try to connect directly from the slave to the new master, I get: ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.1xx.1xx.xx' (113) I'll make a super user to test... Duh. I needed to explicitly allow port 3306 on the new master. Thanks for the hints, guys. David
Re: Transactions and locking
(Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which duplicate key was hit on last insert?
On Nov 13, 2007, at 1:25 AM, yaya sirima wrote:
Hi,
CREATE TABLE Test (
COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
COL2VARCHAR(10) NOT NULL,
COL3VARCHAR(10) NOT NULL,
UNIQUE(COL2, COL3); --(not that)
FULLTEXT(col1,col2)
);
Try this property FULLTEXT
The columns here were used as an illustration of the two different
types of duplicate keys which might have been hit. We use UNIQUE
constraints to include binary, int's, etc...
thank you for the info however, it may be useful elsewhere.
-lev
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. Are you thinking that your theoretical knowledge of relational databases must hold the answer to your questions about MySQL? :-) I suggest you read the entire manual section on InnoDB and experiment. Set aside a day for it; there's a lot to learn there. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data
hi there,
i have a text file that i prepare:
insert into `sa2007` (`id`,`amount`,`state`) values
('','1.00','oh'),
('','2.00','il'),
('','4.00','ks')
how do i import this file to sa2007 table from the command line? i tried
via phymyadmin, but it doesn't work (300 seconds timeout).
thnx,
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 11:39 AM, Baron Schwartz [EMAIL PROTECTED] wrote: InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. Interesting, I didn't think that would work, but the manual does say it will: You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking allows you to lock the non-existence of something in your table. http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html There's another suggestion in the comments on that page: INSERT IGNORE and then check the number of rows affected. But, not portable to SQLite. - Perrin P.S. I enjoy your blog, Baron. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure mysql port
In my.cnf, you can specify a 'bind-address'. When used it will cause the listener to only be available to host on that same network ie. one of your database host's ip binding is 10.10.10.66/255.255.0.0 # this will list the server to respond only to hosts in the 10.10.x.x range, all other (including localhost!!) will be refused. bind-address=10.10.10.66 On Nov 13, 2007 4:53 AM, David Campbell [EMAIL PROTECTED] wrote: Kelly Opal wrote: Hi Is there any way to restrict access to the tcp port on mysql. I only want my 5 class C's to be able to access the port but it is a public server. Iptables Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql dump
Hello everyone, Few conceptual questions which I can't understand. If any one can please gimme a a quicky! Am I correct when I say that mysqldump' only works when the database is up and running? and if it is true can any one please tell me that does taking a dump when a database is running is ok. Also the whats the difference in usage of mysqldump and taking just the backup of the database. Regards Naufal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump
On Nov 13, 2007 2:11 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: Hello everyone, Few conceptual questions which I can't understand. If any one can please gimme a a quicky! Am I correct when I say that mysqldump' only works when the database is up and running? and if it is true can any one please tell me that does taking a dump when a database is running is ok. Also the whats the difference in usage of mysqldump and taking just the backup of the database. Regards Naufal Yes, mysqldump is just a specialized client for MySQL, it performs all of it's operations through a server. Whereas a raw file dump is, well, a raw file dump, mysqldump generates SQL scripts which will recreate your databases, tables and data when piped in to a simple command line client thus: mysqldump -h localhost -u user -p mydatabase mydatabase.sql then, it may be recreated mysql -h otherserver -u user -p databasewhichexists mydatabase.sql (command line examples are for *nix, but windows variants exist) - michael dykman -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data
On 11/13/07, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi there,
i have a text file that i prepare:
insert into `sa2007` (`id`,`amount`,`state`) values
('','1.00','oh'),
('','2.00','il'),
('','4.00','ks')
how do i import this file to sa2007 table from the command line? i tried
via phymyadmin, but it doesn't work (300 seconds timeout).
Try: mysql -uusername -ppassword database filename.sql
--
Check out the Dallas Music Wiki http://www.digitaldarkness.com
Re: mysql dump
So is it safe to take the dump while database is running. I mean is there any loss of data expected because of taking dump while a database is running. On Nov 13, 2007 2:26 PM, Michael Dykman [EMAIL PROTECTED] wrote: On Nov 13, 2007 2:11 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: Hello everyone, Few conceptual questions which I can't understand. If any one can please gimme a a quicky! Am I correct when I say that mysqldump' only works when the database is up and running? and if it is true can any one please tell me that does taking a dump when a database is running is ok. Also the whats the difference in usage of mysqldump and taking just the backup of the database. Regards Naufal Yes, mysqldump is just a specialized client for MySQL, it performs all of it's operations through a server. Whereas a raw file dump is, well, a raw file dump, mysqldump generates SQL scripts which will recreate your databases, tables and data when piped in to a simple command line client thus: mysqldump -h localhost -u user -p mydatabase mydatabase.sql then, it may be recreated mysql -h otherserver -u user -p databasewhichexists mydatabase.sql (command line examples are for *nix, but windows variants exist) - michael dykman -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump
No, but a table lock or two may be expected. This is to PREVENT data loss (which you were also worried about). The mysqldump process will most likely be quick and painless (quick being a relative term, depending on the amount of data in your database(s)). Craig On Nov 13, 2007 2:35 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: So is it safe to take the dump while database is running. I mean is there any loss of data expected because of taking dump while a database is running. On Nov 13, 2007 2:26 PM, Michael Dykman [EMAIL PROTECTED] wrote: On Nov 13, 2007 2:11 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: Hello everyone, Few conceptual questions which I can't understand. If any one can please gimme a a quicky! Am I correct when I say that mysqldump' only works when the database is up and running? and if it is true can any one please tell me that does taking a dump when a database is running is ok. Also the whats the difference in usage of mysqldump and taking just the backup of the database. Regards Naufal Yes, mysqldump is just a specialized client for MySQL, it performs all of it's operations through a server. Whereas a raw file dump is, well, a raw file dump, mysqldump generates SQL scripts which will recreate your databases, tables and data when piped in to a simple command line client thus: mysqldump -h localhost -u user -p mydatabase mydatabase.sql then, it may be recreated mysql -h otherserver -u user -p databasewhichexists mydatabase.sql (command line examples are for *nix, but windows variants exist) - michael dykman -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure mysql port
On a *nix box, it is also traditional to configure IPTABLES or similar to restrict TCP/UDP connections based on IP and/or adapter. It seems likely based on your description that the box has two network connections. Dave. On 11/13/07, Michael Dykman [EMAIL PROTECTED] wrote: In my.cnf, you can specify a 'bind-address'. When used it will cause the listener to only be available to host on that same network ie. one of your database host's ip binding is 10.10.10.66/255.255.0.0 # this will list the server to respond only to hosts in the 10.10.x.x range, all other (including localhost!!) will be refused. bind-address=10.10.10.66 On Nov 13, 2007 4:53 AM, David Campbell [EMAIL PROTECTED] wrote: Kelly Opal wrote: Hi Is there any way to restrict access to the tcp port on mysql. I only want my 5 class C's to be able to access the port but it is a public server. Iptables Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump
Thank you so much! On Nov 13, 2007 2:40 PM, Craig Huffstetler [EMAIL PROTECTED] wrote: No, but a table lock or two may be expected. This is to PREVENT data loss (which you were also worried about). The mysqldump process will most likely be quick and painless (quick being a relative term, depending on the amount of data in your database(s)). Craig On Nov 13, 2007 2:35 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: So is it safe to take the dump while database is running. I mean is there any loss of data expected because of taking dump while a database is running. On Nov 13, 2007 2:26 PM, Michael Dykman [EMAIL PROTECTED] wrote: On Nov 13, 2007 2:11 PM, Naufal Sheikh [EMAIL PROTECTED] wrote: Hello everyone, Few conceptual questions which I can't understand. If any one can please gimme a a quicky! Am I correct when I say that mysqldump' only works when the database is up and running? and if it is true can any one please tell me that does taking a dump when a database is running is ok. Also the whats the difference in usage of mysqldump and taking just the backup of the database. Regards Naufal Yes, mysqldump is just a specialized client for MySQL, it performs all of it's operations through a server. Whereas a raw file dump is, well, a raw file dump, mysqldump generates SQL scripts which will recreate your databases, tables and data when piped in to a simple command line client thus: mysqldump -h localhost -u user -p mydatabase mydatabase.sql then, it may be recreated mysql -h otherserver -u user -p databasewhichexists mydatabase.sql (command line examples are for *nix, but windows variants exist) - michael dykman -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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: Transactions and locking
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still find its own (same) MAX(id) value and then do an insert. Or any other process can still check for uniqueness and then fail with its insert. The insert of the first process may succeed guaranteed, but the second will fail at a point where it should not. (Actually, it should never fail when I found a new id value / found that my new value is unique.) I have tested the SELECT ... FOR UPDATE and the LOCK TABLES with autocommit = 0 thing. Both don't lock anything (at least not for reading by others which is what I need). May I now conclude that exclusive full table locking is not possible with InnoDB? Or is there another way that I don't know yet? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Not for me. This is what I was doing here. (FYI: MySQL 5.0.45-community-nt, Windows XP, mysql command line client, InnoDB tables) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:57 CE(S)T, Baron Schwartz wrote: It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; Okay, my fault, I didn't use the FOR UPDATE in the second connection. If I do (which is likely to be the case in an application because there, the same code is run concurrently), the second SELECT locks. (The same is true when I select MAX(id) instead of *.) If I don't, it still works. Okay, so we have some table locking, tested, working. Very nice. Thank you for this one. :) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Delete my last message. I just did it again and now it works, too. I have no idea what I did a couple of minutes ago, but it must have been wrong. Okay. Works, too. I was doubting that it was possible at all. Meanwhile, I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. Thank you for all your patience you had with me. I think my problems are now solved... I'll see it when I test my application the next time. ;) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 3:32 PM, Yves Goergen [EMAIL PROTECTED] wrote: I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. No, you just have to use FOR UPDATE and it will block. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
